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)：

针对对象之间的组合关系，导出不适用，

例如Employee extends User,User Extends Person，此时如果需要导出person.user.employee.name，则不能用此导出，如需要可私信。