easypoi自定义表头、多个sheet导出

想要效果

1、自定义表头

2、学校根据老师合并单元格,老师根据学生合并单元格。三层嵌套导出

3、生成多个sheet

代码

public static void exportTest(){
        List<ExcelExportEntity> entityList = new ArrayList<>();

        //自定所有表头
        ExcelExportEntity schoolName = new ExcelExportEntity("学校名称", "schoolName");
        ExcelExportEntity teacherName = new ExcelExportEntity("老师名称", "teacherName");
        ExcelExportEntity subjectName = new ExcelExportEntity("课程", "subjectName");
        ExcelExportEntity studentName = new ExcelExportEntity("学生名称", "studentName");
        ExcelExportEntity age = new ExcelExportEntity("年龄", "age");
        ExcelExportEntity time = new ExcelExportEntity("入学时间", "time");
        time.setFormat("yyyy-MM-dd");
        time.setWidth(30);

        //一对多的数据所以要合并单元格
        schoolName.setNeedMerge(true);
        teacherName.setNeedMerge(true);
        subjectName.setNeedMerge(true);
        entityList.add(schoolName);

        //老师合并
        ExcelExportEntity teacher = new ExcelExportEntity(null, "teacher");
        List<ExcelExportEntity> temp = new ArrayList<>();
        temp.add(teacherName);
        temp.add(subjectName);
        teacher.setList(temp);

        //学生合并-三层嵌套的sortNum无效,可以先排序再add到temp2,达到sortNum效果
        ExcelExportEntity students = new ExcelExportEntity(null, "students");
        List<ExcelExportEntity> temp2 = new ArrayList<>();
        temp2.add(studentName);
        temp2.add(age);
        temp2.add(time);
        students.setList(temp2);

        temp.add(students);
        entityList.add(teacher);
        
        //构建数据
        List<Map<String, Object>> dataList = new ArrayList<>();
        for (int i = 0; i < 2; i++){
            Map<String, Object> userEntityMap = new HashMap<>();
            userEntityMap.put("schoolName", "学校" + i);

            List<Map<String, Object>> dataList2 = new ArrayList<>();
            for (int j = 0; j < 2; j++){
                Map<String, Object> userEntityMap2 = new HashMap<>();
                userEntityMap2.put("teacherName", "老师" + j);
                userEntityMap2.put("subjectName", j);
                List<Map<String, Object>> dataList3 = new ArrayList<>();
                for (int k = 0; k < 3; k++){
                    Map<String, Object> userEntityMap3 = new HashMap<>();
                    userEntityMap3.put("studentName", "学生" + k);
                    userEntityMap3.put("age", k);
                    userEntityMap3.put("time", new Date(System.currentTimeMillis() + k));
                    dataList3.add(userEntityMap3);
                }
                userEntityMap2.put("students", dataList3);
                dataList2.add(userEntityMap2);
            }
            userEntityMap.put("teacher", dataList2);
            dataList.add(userEntityMap);
        }

        //模拟sheet2数据
        List<Map<String, Object>> dataList2 = new ArrayList<>(dataList);
        //模拟sheet3数据
        List<Map<String, Object>> dataList3 = new ArrayList<>(dataList);

        //sheet1
        ExportParams params1 = new ExportParams();
        params1.setType(ExcelType.HSSF);
        params1.setSheetName("用户1");
        params1.setTitle("信息表1");
        Workbook workbook = ExcelExportUtil.exportExcel(params1, entityList, dataList);

        //sheet2
        ExportParams params2 = new ExportParams();
        params2.setType(ExcelType.HSSF);
        params2.setSheetName("用户2");
        params2.setTitle("信息表2");
        new ExcelExportService().createSheetForMap(workbook, params2, entityList, dataList2);

        //sheet3
        ExportParams params3 = new ExportParams();
        params3.setType(ExcelType.HSSF);
        params3.setSheetName("用户3");
        params3.setTitle("信息表3");
        new ExcelExportService().createSheetForMap(workbook, params3, entityList, dataList3);
        
        try {
            FileOutputStream fos = new FileOutputStream("C:\\Users\\u\\Desktop\\合并导出测试.xls");
            workbook.write(fos);
            fos.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

导出效果

总结

1、三层嵌套的width、sortNum设置没有效果,需要自己调整样式

     width调整:

参考:java用POI设置Excel的列宽_sheet.setcolumnwidth-CSDN博客

//遍历每个sheet
Sheet sheetAt = workbook.getSheetAt(0);
//设置第一列长度是30
sheetAt.setColumnWidth(0, 30 * 256 + 184);
  • 11
    点赞
  • 8
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值