java实现把对象数组通过excel方式导出的功能
一、导入相关jar包，pom依赖如下：
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>RELEASE</version> </dependency>
二、开始撸代码
1.如果导出功能使用的比较多，可以将其做成一个工具类，对我下面贴出的代码进行改造
//结果返回的是写入的记录数（以下用的是自己业务场景数据）
public int downLoadToExcel(OutputStream outputStream,List<PaimaiMoneyVO> paimaiMoneyVOList) {
//文档对象 HSSFWorkbook wb = new HSSFWorkbook(); int rowNum = 0; Sheet sheet = wb.createSheet("excel的标题"); Row row0 = 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 (PaimaiMoneyVO paimaiMoneyVO : paimaiMoneyVOList) { Row row = 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 (IOException e) { LogEnum.LAW_WARE.error("流关闭异常！", e); } finally { if (outputStream != null) { try { outputStream.close(); } catch (IOException e) { LogEnum.LAW_WARE.error("流关闭异常！", e); } } } return rowNum - 1; }
2.“工具类”写好后，下面就开始使用它了，从上面的函数参数可以看到，我们需要传过去两个对象，一个是输出流OutPutStream，通过流的方式把excel想要到浏览器，
另外一个就是我们需要导出的对象数组，好了，不解释太多，看代码。（下面的方法写在action层，通过struts.xml配置访问即可实现下载）
public void exportBail(){ this.fileName = "excel文件名"; try { List<PaimaiMoneyVO> paimaiMoneyVOList = new ArrayList<>(); //下面是我的业务场景获取对象数组 if(paimaiMoneySearchParam!=null){ paimaiMoneySearchParam.setVendorId(WebHelper.getVenderId()); paimaiMoneySearchParam.setPageSize(Constants.AUCTION_WARE_PAGE_SIZE); paimaiMoneySearchParam.setPage(page); PaimaiMoneyDto paimaiMoneyDto = auctionWareService1.searchPopPaimaiMoneyList(paimaiMoneySearchParam); if(paimaiMoneyDto!=null){ int count = paimaiMoneyDto.getCount(); int totalPage = count/ Constants.AUCTION_WARE_PAGE_SIZE + (count% Constants.AUCTION_WARE_PAGE_SIZE > 0?1:0); for(int i=1;i<=totalPage;i++){ paimaiMoneySearchParam.setPage(i); PaimaiMoneyDto paimaiMoneyResultResult = auctionWareService1.searchPopPaimaiMoneyList(paimaiMoneySearchParam); if(paimaiMoneyResultResult!=null){ paimaiMoneyVOList.addAll(paimaiMoneyResultResult.getList()); } } } } OutputStream outputStream = response.getOutputStream(); response.reset();//清空输出流 //下面是对中文文件名的处理 response.setCharacterEncoding("UTF-8");//设置相应内容的编码格式 //解析浏览器 final String userAgent = request.getHeader("USER-AGENT").toLowerCase(); if(userAgent.contains("firefox")){ //火狐浏览器 fileName = new String(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");//定义输出类型 int rouNum = ensurePriceListToExcel(outputStream,paimaiMoneyVOList); LogEnum.LAW_WARE.info("【RiseAuctionAction.downLoadEnsurePriceExcel】导出成功,一共更新了{"+rouNum+"}条记录"); } catch (Exception e) { LogEnum.LAW_WARE.error("【RiseAuctionAction.downLoadEnsurePriceExcel】导出失败,error is {}", e); } }
三、拓展（详细的工具类开发）
如果你觉得上面写的太简单了，可以继续往下看，我把它整理出了“万能”的工具类，供大家参考。
package com.jd.pop.auction.util.excel; import com.jd.common.web.result.Result; import com.jd.pop.auction.util.excel.annotations.ExcelColumn; import com.jd.pop.auction.util.excel.annotations.ExcelMapping; import com.jd.pop.auction.util.excel.annotations.apt.ExcelColumnAPT; import com.jd.pop.auction.util.excel.annotations.apt.ExcelMappingAPT; import org.apache.log4j.Logger; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.util.CellRangeAddress; import java.io.IOException; import java.io.OutputStream; import java.lang.reflect.Field; import java.lang.reflect.InvocationTargetException; import java.util.Collection; import java.util.Iterator; import java.util.List; public class GenerateExcel { private final static Logger LOG = Logger.getLogger(GenerateExcel.class); private HSSFWorkbook workbook; private HSSFCellStyle headStyle; private HSSFFont headCellFont; private HSSFCellStyle theadStyle; private HSSFFont theadCellFont; private HSSFCellStyle tbodyStyle; private HSSFFont tbodyCellFont; private HSSFFont stringFont; private static final short COLUMN_WIDTH = 15; private static final short ROW_HEIGHT = 400; public GenerateExcel() { this.workbook = new HSSFWorkbook(); //标题 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); } public <T> Result export(List<String> titles, Field[] fields, Class clazz, Collection<T> dataset, OutputStream out, boolean pager) { Result result = new Result(false); if(pager){ }else{ HSSFSheet sheet = workbook.createSheet( "第一页"); sheet.setDefaultColumnWidth(COLUMN_WIDTH); sheet.setDefaultRowHeight(ROW_HEIGHT); //标题 for (int i = 0; i <titles.size(); i++) { HSSFRow titleRow = sheet.createRow(i); titleRow.setHeightInPoints(20f); sheet.addMergedRegion(new CellRangeAddress(i,i,0,fields.length-1)); HSSFCell titleCell =titleRow.createCell(0); titleCell.setCellValue(titles.get(i)); titleCell.setCellStyle(headStyle); } //列名 HSSFRow row = sheet.createRow(titles.size()); for (short i = 0; i < fields.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(theadStyle); if(fields[i].isAnnotationPresent(ExcelColumn.class)){ ExcelColumn an_1 = fields[i].getAnnotation(ExcelColumn.class); HSSFRichTextString text = new HSSFRichTextString(an_1.name()); cell.setCellValue(text); }else if(fields[i].isAnnotationPresent(ExcelMapping.class)){ ExcelMapping an_1 = fields[i].getAnnotation(ExcelMapping.class); HSSFRichTextString text = new HSSFRichTextString(an_1.name()); cell.setCellValue(text); } } Iterator<T> it = dataset.iterator(); int index = titles.size(); while (it.hasNext()) { index++; row = sheet.createRow(index); T t = (T) it.next(); for (short i = 0; i < fields.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(tbodyStyle); Field field = fields[i]; try { String textValue; if(field.isAnnotationPresent(ExcelMapping.class)){ textValue = new ExcelMappingAPT().getColumnValue(field,t,clazz); }else{ textValue = new ExcelColumnAPT().getColumnValue(field,t,clazz); } cell.setCellValue(textValue); } catch (NoSuchMethodException e) { String errorMsg = field.getName() +"字段，第"+ index+ "条数据， NoSuchMethodException 反射错误！"; LOG.error(errorMsg,e); result.addDefaultModel(errorMsg); return result; } catch (IllegalAccessException e) { String errorMsg = field.getName() +"字段，第"+ index+ "条数据， IllegalAccessException "; LOG.error(errorMsg,e); result.addDefaultModel(errorMsg); return result; } catch (InvocationTargetException e) { String errorMsg = field.getName() +"字段，第"+ index+ "条数据， InvocationTargetException "; LOG.error(errorMsg,e); result.addDefaultModel(errorMsg); return result; } } } } try { workbook.write(out); result.setSuccess(true); return result; } catch (IOException e) { String errorMsg = "将导出数据写入输出流失败!"; LOG.error("将导出数据写入输出流失败！ ",e); result.addDefaultModel(errorMsg); return result; }finally { try { out.close(); } catch (IOException e) { String errorMsg = "关闭输出流异常!"; LOG.error("关闭输出流异常！ ",e); result.addDefaultModel(errorMsg); return result; } } } }
public class ExportExcelUtils { private final static Logger LOG = Logger.getLogger(ExportExcelUtils.class); public static <T> Result export(List<String> titles,List<T> sourceList, OutputStream out, boolean pager){ Result result = new Result(false); if(CollectionUtils.isEmpty(sourceList)){ result.addDefaultModel("ExportExcelUtils's param sourceList is empty!"); LOG.error("ExportExcelUtils's param sourceList is empty!"); return result; } if( out == null){ LOG.error("ExportExcelUtils's param OutputStream is null!"); result.addDefaultModel("ExportExcelUtils's param OutputStream is null!"); return result; } Class clazz = null; Field[] fieldArr = null; try{ //得到需要转换的列名 clazz = sourceList.get(0).getClass(); Field[] fields = clazz.getDeclaredFields(); List<Field> fieldList = new ArrayList<Field>(); for(Field field:Arrays.asList(fields)){ field.setAccessible(true); if(field.isAnnotationPresent(ExcelColumn.class)){ fieldList.add(field); }else if(field.isAnnotationPresent(ExcelMapping.class)){ fieldList.add(field); } } if(CollectionUtils.isEmpty(fieldList)){ LOG.error("实体类中无需要导出的字段!"); result.addDefaultModel("实体类中无需要导出的字段！"); return result; } fieldArr = fieldList.toArray(new Field[fieldList.size()]); }catch(Exception e){ LOG.error("数据拼装异常!"); result.addDefaultModel("数据拼装异常!"); return result; } //生成excel GenerateExcel ge = new GenerateExcel(); return ge.export(titles,fieldArr,clazz,sourceList,out,false); } }
这一部分写的比较粗糙，但是实现的比较详细，仅供参考，大家可以稍微改造成为自己独有的utils，上面是实战，我们可以点这里看一下POI HSSF API组件的具体使用方法。
