EasyExcel简单读写及使用模板导出复杂Excel

官网代码:填充Excel | Easy Excel

在wemedia服务导入依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.0-beta1</version>
</dependency>

1.读Excel

@Data
@AllArgsConstructor
@NoArgsConstructor
public class aaaa {
    private Integer id;
    @ExcelProperty("姓名")
    private String name;
    @ExcelProperty("城市")
    private String cs;
}

EasyExcel.read("D:\\aaa.xlsx", aaaa.class,new PageReadListener<aaaa>((list)->{
    for (aaaa aaaa1 : list){
        System.out.println(aaaa1);
    }
})).sheet().doRead();

2.写Excel

    private List<DemoData> data() {
        List<DemoData> list = ListUtils.newArrayList();
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + i);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }

最简单的写:

@Getter
@Setter
@EqualsAndHashCode
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}

代码:

    /**
     * 最简单的写
     * <p>
     * 1. 创建excel对应的实体对象 参照{@link DemoData}
     * <p>
     * 2. 直接写即可
     */
    @Test
    public void simpleWrite() {
        // 注意 simpleWrite在数据量不大的情况下可以使用（5000以内，具体也要看实际情况），数据量大参照 重复多次写入

        // 写法1 JDK8+
        // since: 3.0.0-beta1
        String fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, DemoData.class)
            .sheet("模板")
            .doWrite(() -> {
                // 分页查询数据
                return data();
            });

        // 写法2
        fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());

        // 写法3
        fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去写
        try (ExcelWriter excelWriter = EasyExcel.write(fileName, DemoData.class).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
            excelWriter.write(data(), writeSheet);
        }
    }

3.使用模板导出复杂Excel

添加excel模板文件:

导出:

@GetMapping("/export/use/template")
public void downloadUseTemplate(HttpServletResponse response) throws Exception {
    // 下载excel
    String fileName = "使用模板导出.xlsx";
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));

    // 加载模板
    InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream(fileName);
    // 输出到response
    ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
            .withTemplate(inputStream)
            .excelType(ExcelTypeEnum.XLSX).build();

    // 得到sheet
    WriteSheet writeSheet = EasyExcel.writerSheet().build();

    // 单个字段填充
    Map<String, Object> map = new HashMap<>();
    map.put("weekAdd",100);
    map.put("monthAdd",200);
    map.put("total",300);
    excelWriter.fill(map, writeSheet);

    // 列表竖向填充
    List<DetailDTO> details = new ArrayList<>();
    details.add(new DetailDTO(new SimpleDateFormat("yyyy-MM-dd").parse("2023-12-10"), 100));
    details.add(new DetailDTO(new SimpleDateFormat("yyyy-MM-dd").parse("2023-12-11"), 80));
    details.add(new DetailDTO(new SimpleDateFormat("yyyy-MM-dd").parse("2023-12-12"), 200));
    excelWriter.fill(new FillWrapper("details", details), writeSheet);

    // 列表横向填充
    FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
    excelWriter.fill(new FillWrapper("week", details),fillConfig, writeSheet);

    excelWriter.finish();

}
————————————————
版权声明：本文为CSDN博主「若鱼1919」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/goldenfish1919/article/details/134925322

真是xm代码:

 @Override
    public void exportBusinessData(HttpServletResponse response) {
        //获取30天的起始和终止时间
        LocalDateTime endTime = LocalDateTime.now();
        LocalDateTime starTime = endTime.minusDays(30).toLocalDate().atStartOfDay();
        //查询30天的数据
        BusinessDataVO businessDataVO = workSpaceService.businessData(starTime, endTime);
        List<ExcalBusinessDataVO> list = new ArrayList<>();
        LocalDate dayTime = endTime.minusDays(30).toLocalDate();
        //查询每一天的数据
        while (starTime.isBefore(endTime)) {
            LocalDateTime dayEndTime = starTime.plusHours(24).minusSeconds(1);
            BusinessDataVO dayVo = workSpaceService.businessData(starTime, dayEndTime);
            ExcalBusinessDataVO dateExcel = new ExcalBusinessDataVO();
            BeanUtils.copyProperties(dayVo, dateExcel);
            dateExcel.setDate(dayTime.toString());
            list.add(dateExcel);
            starTime = starTime.plusDays(1);
            dayTime = dayTime.plusDays(1);
        }
        try {
            //要填充的Excel模板的路径
            InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("templates/运营数据报表模板.xlsx");
            //获取工作簿对象（整个Excel文件）
            //根据模板获取工作簿
            ExcelWriter workBook = EasyExcel.write(response.getOutputStream(), ExcalBusinessDataVO.class)
                    .withTemplate(inputStream).build();
            //获取工作表
            WriteSheet sheet = EasyExcel.writerSheet().build();

            Map<String, String> fillData = new HashMap<>();
            fillData.put("turnoverCount", businessDataVO.getTurnover() + "");
            fillData.put("orderCompletionRateCount", businessDataVO.getOrderCompletionRate() + "");
            fillData.put("newUsersCount", businessDataVO.getNewUsers() + "");
            fillData.put("validOrder", businessDataVO.getValidOrderCount() + "");
            fillData.put("unitPriceCount", businessDataVO.getUnitPrice() + "");
            //执行数据填充
            log.info(list.toString());
            workBook.fill(list, sheet);
            workBook.fill(fillData, sheet);
            workBook.finish();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            throw new BaseException("");
        }
    }