java实现把对象数组通过excel方式导出的功能
一、导入相关jar包,pom依赖如下:
org.apache.poi poi RELEASE
二、开始撸代码
1.如果导出功能使用的比较多,可以将其做成一个工具类,对我下面贴出的代码进行改造
//结果返回的是写入的记录数(以下用的是自己业务场景数据) publicintdownLoadToExcel(OutputStreamoutputStream,ListpaimaiMoneyVOList){ //文档对象 HSSFWorkbookwb=newHSSFWorkbook(); introwNum=0; Sheetsheet=wb.createSheet("excel的标题"); Rowrow0=sheet.createRow(rowNum++); //因为场景不同,titil不同,可以在外面写成数组当参数传进来 row0.createCell(0).setCellValue("第一列属性名"); row0.createCell(1).setCellValue("第二列属性名"); row0.createCell(2).setCellValue("第三列属性名"); row0.createCell(3).setCellValue("第四列属性名"); row0.createCell(4).setCellValue("第五列属性名"); row0.createCell(5).setCellValue("第六列属性名"); if(paimaiMoneyVOList!=null&&paimaiMoneyVOList.size()>0){ for(PaimaiMoneyVOpaimaiMoneyVO:paimaiMoneyVOList){ Rowrow=sheet.createRow(rowNum++); row.createCell(0).setCellValue(paimaiMoneyVO.getPaimaiId()); row.createCell(1).setCellValue(paimaiMoneyVO.getTitle()); row.createCell(2).setCellValue(paimaiMoneyVO.getUsername()); row.createCell(3).setCellValue(paimaiMoneyVO.getMoney()+"元"); row.createCell(4).setCellValue("升价拍"); row.createCell(5).setCellValue(bidder); } } try{ wb.write(outputStream); LogEnum.LAW_WARE.info("表数据写入到excel表成功,一共写入了"+(rowNum-1)+"条数据"); outputStream.close(); }catch(IOExceptione){ LogEnum.LAW_WARE.error("流关闭异常!",e); }finally{ if(outputStream!=null){ try{ outputStream.close(); }catch(IOExceptione){ LogEnum.LAW_WARE.error("流关闭异常!",e); } } } returnrowNum-1; }
2.“工具类”写好后,下面就开始使用它了,从上面的函数参数可以看到,我们需要传过去两个对象,一个是输出流OutPutStream,通过流的方式把excel想要到浏览器,
另外一个就是我们需要导出的对象数组,好了,不解释太多,看代码。(下面的方法写在action层,通过struts.xml配置访问即可实现下载)
publicvoidexportBail(){ this.fileName="excel文件名"; try{ ListpaimaiMoneyVOList=newArrayList<>(); //下面是我的业务场景获取对象数组 if(paimaiMoneySearchParam!=null){ paimaiMoneySearchParam.setVendorId(WebHelper.getVenderId()); paimaiMoneySearchParam.setPageSize(Constants.AUCTION_WARE_PAGE_SIZE); paimaiMoneySearchParam.setPage(page); PaimaiMoneyDtopaimaiMoneyDto=auctionWareService1.searchPopPaimaiMoneyList(paimaiMoneySearchParam); if(paimaiMoneyDto!=null){ intcount=paimaiMoneyDto.getCount(); inttotalPage=count/Constants.AUCTION_WARE_PAGE_SIZE+(count%Constants.AUCTION_WARE_PAGE_SIZE>0?1:0); for(inti=1;i<=totalPage;i++){ paimaiMoneySearchParam.setPage(i); PaimaiMoneyDtopaimaiMoneyResultResult=auctionWareService1.searchPopPaimaiMoneyList(paimaiMoneySearchParam); if(paimaiMoneyResultResult!=null){ paimaiMoneyVOList.addAll(paimaiMoneyResultResult.getList()); } } } } OutputStreamoutputStream=response.getOutputStream(); response.reset();//清空输出流 //下面是对中文文件名的处理 response.setCharacterEncoding("UTF-8");//设置相应内容的编码格式 //解析浏览器 finalStringuserAgent=request.getHeader("USER-AGENT").toLowerCase(); if(userAgent.contains("firefox")){//火狐浏览器 fileName=newString(fileName.getBytes(),"ISO8859-1"); }else{ fileName=URLEncoder.encode(fileName,"UTF-8");//其他浏览器 fileName=fileName.Replace("+","%20");//encode后替换,解决空格问题(其中%20是空格在UTF-8下的编码,如果不这么写,浏览器会用+代替空格) } response.setHeader("Content-Disposition","attachment;filename="+fileName+".xls");//指定输出文件名 response.setContentType("application/msexcel");//定义输出类型 introuNum=ensurePriceListToExcel(outputStream,paimaiMoneyVOList); LogEnum.LAW_WARE.info("【RiseAuctionAction.downLoadEnsurePriceExcel】导出成功,一共更新了{"+rouNum+"}条记录"); }catch(Exceptione){ LogEnum.LAW_WARE.error("【RiseAuctionAction.downLoadEnsurePriceExcel】导出失败,erroris{}",e); } }
三、拓展(详细的工具类开发)
如果你觉得上面写的太简单了,可以继续往下看,我把它整理出了“万能”的工具类,供大家参考。
packagecom.jd.pop.auction.util.excel; importcom.jd.common.web.result.Result; importcom.jd.pop.auction.util.excel.annotations.ExcelColumn; importcom.jd.pop.auction.util.excel.annotations.ExcelMapping; importcom.jd.pop.auction.util.excel.annotations.apt.ExcelColumnAPT; importcom.jd.pop.auction.util.excel.annotations.apt.ExcelMappingAPT; importorg.apache.log4j.Logger; importorg.apache.poi.hssf.usermodel.*; importorg.apache.poi.hssf.util.HSSFColor; importorg.apache.poi.ss.util.CellRangeAddress; importjava.io.IOException; importjava.io.OutputStream; importjava.lang.reflect.Field; importjava.lang.reflect.InvocationTargetException; importjava.util.Collection; importjava.util.Iterator; importjava.util.List; publicclassGenerateExcel{ privatefinalstaticLoggerLOG=Logger.getLogger(GenerateExcel.class); privateHSSFWorkbookworkbook; privateHSSFCellStyleheadStyle; privateHSSFFontheadCellFont; privateHSSFCellStyletheadStyle; privateHSSFFonttheadCellFont; privateHSSFCellStyletbodyStyle; privateHSSFFonttbodyCellFont; privateHSSFFontstringFont; privatestaticfinalshortCOLUMN_WIDTH=15; privatestaticfinalshortROW_HEIGHT=400; publicGenerateExcel(){ this.workbook=newHSSFWorkbook(); //标题 this.headStyle=workbook.createCellStyle(); headStyle.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index); headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); //headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); //headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //headStyle.setWrapText(true); this.headCellFont=workbook.createFont(); headCellFont.setFontHeightInPoints((short)13); headCellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headStyle.setFont(headCellFont); this.theadStyle=workbook.createCellStyle(); theadStyle.setFillForegroundColor(HSSFColor.WHITE.index); theadStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); theadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); theadStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); theadStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); theadStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); theadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); theadCellFont=workbook.createFont(); theadCellFont.setColor(HSSFColor.BLACK.index); theadCellFont.setFontHeightInPoints((short)12); theadCellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); theadStyle.setFont(theadCellFont); tbodyStyle=workbook.createCellStyle(); tbodyStyle.setFillForegroundColor(HSSFColor.WHITE.index); tbodyStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); tbodyStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); tbodyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); tbodyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); tbodyStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); tbodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); tbodyStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); tbodyCellFont=workbook.createFont(); tbodyCellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); tbodyStyle.setFont(tbodyCellFont); stringFont=workbook.createFont(); stringFont.setColor(HSSFColor.BLACK.index); } publicResultexport(List titles,Field[]fields,Classclazz,Collection dataset,OutputStreamout,booleanpager){ Resultresult=newResult(false); if(pager){ }else{ HSSFSheetsheet=workbook.createSheet("第一页"); sheet.setDefaultColumnWidth(COLUMN_WIDTH); sheet.setDefaultRowHeight(ROW_HEIGHT); //标题 for(inti=0;i it=dataset.iterator(); intindex=titles.size(); while(it.hasNext()){ index++; row=sheet.createRow(index); Tt=(T)it.next(); for(shorti=0;i publicclassExportExcelUtils{ privatefinalstaticLoggerLOG=Logger.getLogger(ExportExcelUtils.class); publicstaticResultexport(List titles,List sourceList,OutputStreamout,booleanpager){ Resultresult=newResult(false); if(CollectionUtils.isEmpty(sourceList)){ result.addDefaultModel("ExportExcelUtils'sparamsourceListisempty!"); LOG.error("ExportExcelUtils'sparamsourceListisempty!"); returnresult; } if(out==null){ LOG.error("ExportExcelUtils'sparamOutputStreamisnull!"); result.addDefaultModel("ExportExcelUtils'sparamOutputStreamisnull!"); returnresult; } Classclazz=null; Field[]fieldArr=null; try{ //得到需要转换的列名 clazz=sourceList.get(0).getClass(); Field[]fields=clazz.getDeclaredFields(); List fieldList=newArrayList (); for(Fieldfield:Arrays.asList(fields)){ field.setAccessible(true); if(field.isAnnotationPresent(ExcelColumn.class)){ fieldList.add(field); }elseif(field.isAnnotationPresent(ExcelMapping.class)){ fieldList.add(field); } } if(CollectionUtils.isEmpty(fieldList)){ LOG.error("实体类中无需要导出的字段!"); result.addDefaultModel("实体类中无需要导出的字段!"); returnresult; } fieldArr=fieldList.toArray(newField[fieldList.size()]); }catch(Exceptione){ LOG.error("数据拼装异常!"); result.addDefaultModel("数据拼装异常!"); returnresult; } //生成excel GenerateExcelge=newGenerateExcel(); returnge.export(titles,fieldArr,clazz,sourceList,out,false); } } 这一部分写的比较粗糙,但是实现的比较详细,仅供参考,大家可以稍微改造成为自己独有的utils。
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,同时也希望多多支持毛票票!
声明:本文内容来源于网络,版权归原作者所有,内容由互联网用户自发贡献自行上传,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任。如果您发现有涉嫌版权的内容,欢迎发送邮件至:czq8825#qq.com(发邮件时,请将#更换为@)进行举报,并提供相关证据,一经查实,本站将立刻删除涉嫌侵权内容。