使用poi把Java对象转换成excel

1.导入pom文件

    <dependencies>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>3.17</version>
        </dependency>
    </dependencies>

2.引入转换工具类ExportExcelUtil

package com.hzh;、
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;

/**
 * @ClassName ExportExcelUtil
 * @description: 导出List<Object>数据到excel（最多可导出65535行）
 **/

public class ExportExcelUtil {

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

    }

    /***
     * 工作簿
     */
    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;

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

3.创建对象实体类Employee

package com.hzh;

public class Employee {

    private Integer name;

    private String clazz;

    private Integer year;

    private Integer month;

    private Integer day;

    private double salary;

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

    public Integer getName() {
        return name;
    }

    public void setName(Integer name) {
        this.name = name;
    }

    public String getClazz() {
        return clazz;
    }

    public void setClazz(String clazz) {
        this.clazz = clazz;
    }

    public Integer getYear() {
        return year;
    }

    public void setYear(Integer year) {
        this.year = year;
    }

    public Integer getMonth() {
        return month;
    }

    public void setMonth(Integer month) {
        this.month = month;
    }

    public Integer getDay() {
        return day;
    }

    public void setDay(Integer day) {
        this.day = day;
    }

    public double getSalary() {
        return salary;
    }

    public void setSalary(double salary) {
        this.salary = salary;
    }
}

4.启动主类CustomerExportTest

package com.hzh;

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


public class CustomerExportTest {

    public static void main(String[] args) {
        // 需要导出的数据
        List<Employee> staffs = new ArrayList<Employee>();
        for (int i = 0; i < 100; i++) {
            Employee staff = new Employee(i, i+"hh", 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", "薪资");
        // 文件路径地址和文件名
        HashMap<String, String> nameMap = new HashMap<>();
        nameMap.put("sheetName", "信息导出"); //excel里的sheet名字
        nameMap.put("excelName", "excel名字"); //导出的文件名字
        nameMap.put("filePath", "D:\\"); //导出的文件路径

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

测试结果