EasyExcel写文件的三种方式

最新推荐文章于 2024-07-22 15:17:15 发布

向秋而晚

最新推荐文章于 2024-07-22 15:17:15 发布

阅读量3.5w

点赞数 4

文章标签： java excel

本文链接：https://blog.csdn.net/qq_36233538/article/details/104948449

版权

第一种：创建实体对象

写入对象

@HeadRowHeight(20)	// 表头行高
@ColumnWidth(15)		// 表头行宽
public class VipReportDtoOut {

    @ExcelProperty(value = "日期", index = 0)
    private String reportdate;

    @ExcelProperty(value = "包月金额", index = 1)
    private BigDecimal amount;

    @ExcelProperty(value = "包月数量", index = 2)
    private Long num;
}

通过对象写入方式

	 Map<String, Object> params = parkingReportService.getUserVipReportData(in);
     List<VipReportDtoOut> data = (List<VipReportDtoOut>) params.get("reportList");
     response.setContentType("application/vnd.ms-excel");
     response.setCharacterEncoding("utf-8");
     response.setHeader("Content-disposition", "attachment;filename=demo.xlsx");
     EasyExcel.write(response.getOutputStream(), VipReportDtoOut.class).sheet().doWrite(data);

第二种：动态生成，不创建实体对象

		// 表头
        List<List<String>> heads = new ArrayList<List<String>>();

        StringBuffer sb = new StringBuffer("查询条件：");
		sb.append("时间段：" +  in.getStartDate()+ "至" + in.getEndDate());

        List<String> head0 = new ArrayList<String>();
        head0.add(sb.toString());
        head0.add("日期");
        List<String> head1 = new ArrayList<String>();
        head1.add(sb.toString());
        head1.add("包月金额");
        List<String> head2 = new ArrayList<String>();
        head2.add(sb.toString());
        head2.add("包月数量");
        heads.add(head0);
        heads.add(head1);
        heads.add(head2);

        // 表格数据
        List<List<Object>> dataList = new ArrayList<List<Object>>();
        Map<String, Object> params = parkingReportService.getUserVipReportData(in);
        List<VipReportDtoOut> report = (List<VipReportDtoOut>) params.get("reportList");		// 查询数据
        VipReportDtoOut totalPay = (VipReportDtoOut) params.get("totalPay");	// 合计
        report.add(totalPay);
        report.stream().forEach(c -> {
            List<Object> data = new ArrayList<Object>();
            data.add(c.getReportdate());
            data.add(c.getAmount());
            data.add(c.getNum());
            dataList.add(data);
        });

        EasyExcel.write(response.getOutputStream()).head(heads).sheet().doWrite(dataList);

第三种：填充Excel

创建excel模板

复杂的填充

		Map<String, Object> params = parkingReportService.getUserVipReportData(in);
        List<VipReportDtoOut> data = (List<VipReportDtoOut>) params.get("reportList");
        // 模板地址
        String  templateFileName = "template" + File.separator + "xls" + File.separator + "vipReport.xlsx";

        ExcelWriter excelWriter = null;
        try {
            excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate
                    (new ClassPathResource(templateFileName).getInputStream()).build();
        } catch (IOException e) {
            logger.error("导出文件异常", e);
        }

        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        
        // 填充集合 data
        excelWriter.fill(data, writeSheet);

        // 查询条件
        Map<String, Object> map = new HashMap<String, Object>();
        StringBuffer sb = new StringBuffer("");
		sb.append("时间段：" +  in.getStartDate()+ "至" + in.getEndDate());
        map.put("queryTerms", sb.toString());
		excelWriter.fill(map, writeSheet);

        // 合计部分
        VipReportDtoOut totalPay = (VipReportDtoOut) params.get("totalPay");
        List<List<String>> totalListList = new ArrayList<List<String>>();
        List<String> totalList = new ArrayList<String>();
        totalListList.add(totalList);
        totalList.add("合计：");
        totalList.add(totalPay.getAmount().toString());
        totalList.add(totalPay.getNum().toString());
        excelWriter.write(totalListList, writeSheet);

		// 关闭流
        excelWriter.finish();