Java 对象集合数据导出到Excel

转自https://blog.csdn.net/izhaomiao/article/details/49934767?spm=1001.2014.3001.5501

Java 对象集合数据导出到Excel

导出类

package test;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

/***
 * 导出List<Object>数据到excel（最多可导出65535行）
 * @author user
 */
public final class ExportExcel {

  /***
   * 构造方法
   */
  private ExportExcel() {

  }

  /***
   * 工作簿
   */
  private static HSSFWorkbook workbook;

  /***
   * sheet
   */
  private static HSSFSheet sheet;
 /***
   * 标题行开始位置
   */
  private static final int TITLE_START_POSITION = 0;

  /***
   * 时间行开始位置
   */
  private static final int DATEHEAD_START_POSITION = 1;

  /***
   * 表头行开始位置
   */
  private static final int HEAD_START_POSITION = 2;

  /***
   * 文本行开始位置
   */
  private static final int CONTENT_START_POSITION = 3;


  /**
   * 
   * @param dataList
   *        对象集合
   * @param titleMap
   *        表头信息（对象属性名称->要显示的标题值)[按顺序添加]
   * @param sheetName
   *        sheet名称和表头值
   */
  public static void excelExport(List<?> dataList, Map<String, String> titleMap, String sheetName) {
    // 初始化workbook
    initHSSFWorkbook(sheetName);
    // 标题行
    createTitleRow(titleMap, sheetName);
    // 时间行
    createDateHeadRow(titleMap);
    // 表头行
    createHeadRow(titleMap);
    // 文本行
    createContentRow(dataList, titleMap);
    //设置自动伸缩
    //autoSizeColumn(titleMap.size());
    // 写入处理结果
    try {
      //生成UUID文件名称
      UUID uuid = UUID.randomUUID();
      String filedisplay = uuid + ".xls";
      //如果web项目，1、设置下载框的弹出（设置response相关参数)；2、通过httpservletresponse.getOutputStream()获取
      OutputStream out = new FileOutputStream("D:\\" + filedisplay);
      workbook.write(out);
      out.close();
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }

  /***
   * 
   * @param sheetName
   *        sheetName
   */
  private static void initHSSFWorkbook(String sheetName) {
    workbook = new HSSFWorkbook();
    sheet = workbook.createSheet(sheetName);
  }

  /**
   * 生成标题（第零行创建）
   * @param titleMap 对象属性名称->表头显示名称
   * @param sheetName sheet名称
   */
  private static void createTitleRow(Map<String, String> titleMap, String sheetName) {
    CellRangeAddress titleRange = new CellRangeAddress(0, 0, 0, titleMap.size() - 1);
    sheet.addMergedRegion(titleRange);
    HSSFRow titleRow = sheet.createRow(TITLE_START_POSITION);
    HSSFCell titleCell = titleRow.createCell(0);
    titleCell.setCellValue(sheetName);
  }

  /**
   * 创建时间行（第一行创建）
   * @param titleMap 对象属性名称->表头显示名称
   */
  private static void createDateHeadRow(Map<String, String> titleMap) {
    CellRangeAddress dateRange = new CellRangeAddress(1, 1, 0, titleMap.size() - 1);
    sheet.addMergedRegion(dateRange);
    HSSFRow dateRow = sheet.createRow(DATEHEAD_START_POSITION);
    HSSFCell dateCell = dateRow.createCell(0);   
    dateCell.setCellValue(new SimpleDateFormat("yyyy年MM月dd日").format(new Date()));
  }

  /**
   * 创建表头行（第二行创建）
   * @param titleMap 对象属性名称->表头显示名称
   */
  private static void createHeadRow(Map<String, String> titleMap) {
    // 第1行创建
    HSSFRow headRow = sheet.createRow(HEAD_START_POSITION);
    int i = 0;
    for (String entry : titleMap.keySet()) {
      HSSFCell headCell = headRow.createCell(i);
      headCell.setCellValue(titleMap.get(entry));
      i++;
    }
  }

 /**
  * 
  * @param dataList 对象数据集合
  * @param titleMap 表头信息
  */
  private static void createContentRow(List<?> dataList, Map<String, String> titleMap) {
    try {
      int i=0;
      for (Object obj : dataList) {
        HSSFRow textRow = sheet.createRow(CONTENT_START_POSITION + i);
        int j = 0;
        for (String entry : titleMap.keySet()) {
          String method = "get" + entry.substring(0, 1).toUpperCase() + entry.substring(1);
          Method m = obj.getClass().getMethod(method, null);
          String value =   m.invoke(obj, null).toString();
          HSSFCell textcell = textRow.createCell(j);
          textcell.setCellValue(value);
          j++;
        }
        i++;
      }

    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
  /**
   * 自动伸缩列（如非必要，请勿打开此方法，耗内存）
   * @param size 列数
   */
  private static void autoSizeColumn(Integer size) { 
    for (int j = 0; j < size; j++) {
      sheet.autoSizeColumn(j);
    }
  }
}

测试代码

package test;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;


public class CustomerExportTest {

  /**
   * @param args
   */
  public static void main(String[] args) {

    /**模拟数据开始*/
    List<Employee> staffs = new ArrayList<Employee>();
    for (int i = 0; i < 65532; i++) {
      Employee staff = new Employee(i, i+"group", 1900+i, 12, 25, 2500+i);
      staffs.add(staff);
    }
    Map<String,String> titleMap = new LinkedHashMap<String,String>();
    titleMap.put("name", "姓名");
    titleMap.put("clazz", "组号");
    titleMap.put("year", "年份");
    titleMap.put("month", "月份");
    titleMap.put("day", "天");
    titleMap.put("salary", "薪资");
    String sheetName = "信息导出";
    /**模拟数据结束*/

    System.out.println("start导出");
    long start = System.currentTimeMillis();
    ExportExcel.excelExport(staffs, titleMap, sheetName);
    long end = System.currentTimeMillis();
    System.out.println("end导出");
    System.out.println("耗时："+(end-start)+"ms");
  }

}

Employee类

package test;


public class Employee {

  private Integer name;

  private String clazz;

  private Integer year;

  private Integer month;

  private Integer day;

  private double salary;

  public Employee() {
  };

  public Employee(Integer name, String clazz, Integer year, Integer month, Integer day,
      double salary) {
    super();
    this.name = name;
    this.clazz = clazz;
    this.year = year;
    this.month = month;
    this.day = day;
    this.salary = salary;
  }

  /**
   * @return name
   */
  public Integer getName() {
    return name;
  }

  /**
   * @return year
   */
  public Integer getYear() {
    return year;
  }

  /**
   * @return month
   */
  public Integer getMonth() {
    return month;
  }

  /**
   * @return day
   */
  public Integer getDay() {
    return day;
  }

  /**
   * @return salary
   */
  public double getSalary() {
    return salary;
  }

  /**
   * @param name
   *        set name
   */
  public void setName(Integer name) {
    this.name = name;
  }

  /**
   * @param year
   *        set year
   */
  public void setYear(Integer year) {
    this.year = year;
  }

  /**
   * @param month
   *        set month
   */
  public void setMonth(Integer month) {
    this.month = month;
  }

  /**
   * @param day
   *        set day
   */
  public void setDay(Integer day) {
    this.day = day;
  }

  /**
   * @param salary
   *        set salary
   */
  public void setSalary(double salary) {
    this.salary = salary;
  }

  /**
   * @return clazz
   */
  public String getClazz() {
    return clazz;
  }

  /**
   * @param clazz
   *        set clazz
   */
  public void setClazz(String clazz) {
    this.clazz = clazz;
  }

  @Override
  public String toString() {
    return "Employee [name=" + name + ", clazz=" + clazz + ", year=" + year + ", month=" + month
        + ", day=" + day + ", salary=" + salary + "]";
  }

}

效果如下（6万条数据用时5s)：

通过浏览器下载文件（实战项目代码）

 //导出表
    @RequestMapping(value="/exportExcel" , method= RequestMethod.POST)
    public CommonReturnMsg<List<OrderWarningVO>> exportRecord(@RequestBody  OrderWarningQueryParamVO orderWarningQueryParamVO,HttpServletRequest req, HttpServletResponse resp) throws IOException {
        // orderWarningQueryParamVO.setPageNo(0);
        List<OrderWarningVO> vos=orderWarningService.exportRecord(orderWarningQueryParamVO);

        log.info(orderWarningQueryParamVO.getDataType()+"-----------------------------------------");

        if(vos==null || vos.size()<=0){
            CommonReturnMsg commonReturnMsg=new CommonReturnMsg(vos);
            commonReturnMsg.setErrorMsg("无数据");
            return commonReturnMsg;
        }

        //List<OrderWarningVO> voList= (List<OrderWarningVO>) vos;
        //表头信息
        Map<String,String> titleMap = new LinkedHashMap<String,String>();
        titleMap.put("importTime","订单获取时间");
        titleMap.put("xxFactoryCode","xx工厂");
        titleMap.put("controllerName","计划员");
        titleMap.put("poNo","PO No");
        titleMap.put("custCode","客户名称");
        titleMap.put("type","类型");
        titleMap.put("factoryCode","客户工厂");
        titleMap.put("partCode","客户零件号");
        titleMap.put("xxMaterialId","xx物料号");
        titleMap.put("ph2","PH2");
        titleMap.put("arrivalTime","要求到货日期");
        titleMap.put("planArrivalTime","计划到货日期");
        titleMap.put("realArriveTime","实际到货日期");
        titleMap.put("arrivalNumber","要求发货数量");
        titleMap.put("realShipNumber","实际发货数量");
        titleMap.put("planedNumber","已创建计划数量");
        titleMap.put("dnWarning","DN发货异常");
        titleMap.put("department","责任组织");
        titleMap.put("entrepotNumber","中转库");
        titleMap.put("entrepotIntransNumber","中转库在途");
        titleMap.put("inStockNumber","xx外库");
        titleMap.put("inTransNumber","xx途中");
        titleMap.put("platformNumber","平台");
        titleMap.put("lineNumber","线边");
        titleMap.put("yesterdayUnreportedNumber","昨日Open");
        titleMap.put("todayUnreportedNumber","今日在产");
        titleMap.put("unplanedNumber","未计划");
        titleMap.put("remark","备注");
        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH时mm分");
        String sheetName = dateFormat.format(new Date());
        if(StringUtils.equals(orderWarningQueryParamVO.getDataType(),ORDER)){
            sheetName = "订单交付状态 " + sheetName+".xls";
        }else if(StringUtils.equals(orderWarningQueryParamVO.getDataType(),ORDERWARNING_TODAY)){
            sheetName = "当天-订单报警详情 " + sheetName+".xls";
        }else if(StringUtils.equals(orderWarningQueryParamVO.getDataType(),ORDERWARNING_YESTERDAY)){
            sheetName = "昨天-订单报警详情 " + sheetName+".xls";
        }
        resp.setContentType("application/vnd.ms-excel;charset=UTF-8");
 				//火狐浏览器自己会对URL进行一次URL转码所以区别处理
        if (req.getHeader("user-agent").toLowerCase().indexOf("firefox") > -1) {           
            resp.setHeader("Content-Disposition", "attachment;filename="   + new String(sheetName.getBytes("utf-8"), "ISO-8859-1"));
        } else {
            resp.setHeader("Content-Disposition", "attachment;filename="   + URLEncoder.encode(sheetName,"utf-8"));
        }
        OutputStream out=resp.getOutputStream();
        ExportExcel exportExcel=new ExportExcel();
        exportExcel.excelExport(vos, titleMap, sheetName,out);
        return new CommonReturnMsg<>(vos);
    }

/**
     *
     * @param dataList 对象集合
     * @param titleMap 表头信息（对象属性名称->要显示的标题值)[按顺序添加]
     * @param sheetName sheet名称和表头值
     */
    public   void excelExport(List<?> dataList, Map<String, String> titleMap, String sheetName,OutputStream out) {
        initHSSFWorkbook(sheetName);
        //createTitleRow(titleMap, sheetName);
        createHeadRow(titleMap);
        createContentRow(dataList, titleMap);
        //autoSizeColumn(titleMap.size());
        try {
            workbook.write(out);
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

注意：使用swagger测试文件导出到浏览器下载文件名乱码，但是前端直接请求下载文件是正常的