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。

来源：http://www.cnblogs.com/xiaoming0601/p/6628860.html