Poi数据导出到Excel示例

先上代码：

定义需要导出的实体类

@Data
public class Student implements Serializable {

    private Integer id;
    private String studentName;
    private Integer age;
    private LocalDate birth;
    private String desc;
    private Double weight;
    private Double height;

}

定义导出Excel的工具类

public class ExcelUtil {

    /**
     * 数据量过大时可通过此属性拆分多个sheet页
     */
    private static final int MAX_ROW_NUM = 5000;

    /**
     * 通过反射的方式导出Excel
     *
     * @param dataList
     * @param path
     * @param fields
     * @param <K>
     * @throws Exception
     */
    public static <K> void export(List<K> dataList, String path, Map<String, String> fields) throws Exception {
        if (dataList == null || dataList.size() == 0) {
            throw new Exception("dataList is empty");
        }
        if (fields == null || fields.isEmpty()) {
            throw new ExcelException("fields map is empty");
        }
//        int sheetSize = dataList.size() / MAX_ROW_NUM;
//        if (dataList.size() % MAX_ROW_NUM != 0) {
//            sheetSize += 1;
//        }
        XSSFWorkbook workbook = new XSSFWorkbook();
        String[] fieldNames = new String[fields.keySet().size()];
        String[] displayNames = new String[fields.keySet().size()];
        int i = 0;
        for (String fieldName : fields.keySet()) {
            fieldNames[i] = fieldName;
            displayNames[i] = fields.get(fieldName);
            i ++;
        }
        XSSFSheet sheet = workbook.createSheet();
        XSSFRow row = sheet.createRow(0);
        // 初始化列名
        for (int cellNum = 0; cellNum < displayNames.length; cellNum++) {
            row.createCell(cellNum).setCellValue(displayNames[cellNum]);
        }
        XSSFCell cell;
        K data;
        for (int j = 0; j < dataList.size(); j++) {
            row = sheet.createRow(j + 1);
            data = dataList.get(j);
            for (int cellNum = 0; cellNum < fieldNames.length; cellNum++) {
                cell = row.createCell(cellNum);
                // 通过反射的方式取值赋给单元格
                Field field = data.getClass().getDeclaredField(fieldNames[cellNum]);
                field.setAccessible(true);
                if (field.get(data) == null) {
                    cell.setCellValue("");
                } else if (field.getType() == String.class) {
                    cell.setCellValue((String) field.get(data));
                } else if (field.getType() == Integer.class) {
                    cell.setCellValue((Integer)field.get(data));
                } else if (field.getType() == Double.class) {
                    cell.setCellValue((Double)field.get(data));
                } else if (field.getType() == LocalDate.class) {
                    cell.setCellValue(((LocalDate)field.get(data)).format(DateTimeFormatter.ISO_DATE));
                }
            }
        }
        OutputStream out = null;
        try{
            out = new FileOutputStream(path);
            workbook.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (out != null) {
                out.close();
            }
        }
    }

}

定义测试类

@org.junit.Test
    public void test() throws Exception {
        Map<String, String> fields = new HashMap<>();
        fields.put("id", "编号");
        fields.put("studentName", "姓名");
        fields.put("age", "年龄");
        fields.put("birth", "生日");
        fields.put("desc", "简介");
        fields.put("weight", "体重");
        fields.put("height", "身高");

        List<Student> dataList = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            Student student = new Student();
            student.setId(i + 1);
            student.setStudentName("学生" + i);
            student.setAge(i + 20);
            student.setBirth(LocalDate.now());
            student.setHeight(175.67D);
            student.setWeight(120.66D);
            dataList.add(student);
        }
        String path = "D://student.xlsx";
        ExcelUtil.export(dataList, path, fields);
    }

查看效果

缺点：需要定义实体类

优点：使用反射的方式比较灵活，减少了很多获取值的硬编码

关于第一列标题的顺序也可以优化，目前就不做了，自己可以选择性优化一下。

另外如果数据量很大不要使用XSSFWorkBook ，建议使用SXSSFWorkBook。XSSFWorkBook对于内存的耗费特别严重，关于SXXFWorkBook的用法可以查看官方文档