Poi数据导出到Excel示例

蔚落 2022-01-31 04:27 383阅读 0赞

先上代码：

定义需要导出的实体类

@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);
        }

查看效果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dZQTE5OTM_size_16_color_FFFFFF_t_70][]

缺点：需要定义实体类

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

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

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dZQTE5OTM_size_16_color_FFFFFF_t_70]: /images/20220131/d2fcd491d4e8424f9567ace4bf71529d.png