后台导出Excle(poi)

有时候导出数据量大的时候需要后台导出Excle,但是可能Excle的表头很多合并单元格或者其他复杂的表头时

推荐poi工具类去自定义表头格式(数行列定位表格填充所需的表头数据),感觉很方便,且支持导出大数据量。

代码示例:

    @ApiOperation("报表导出测试")
    @GetMapping("/exportTest")
    public void exportcorpList(HttpServletResponse response) {

        ExcelWriter writer = ExcelUtil.getWriter(true);
        ServletOutputStream out = null;
        try {

         /*   两个单元格以上需使用:writer.merge(列起, 列至, 行起, 行至, "内容", false);
            一个单元格不需合并使用:writer.writeCellValue(行数, 列数, "内容");
            注意点:无论是行还是列都是从0开始数!!*/
            writer.renameSheet("企业统计");
            //设置宽度
            writer.setColumnWidth(-1, 10);
            //填坑 配置表头
            String format2 = DateUtil.format(new Date(), "yyyy年MM月");
            writer.merge(0, 0, 0, 7, String.format("%s企业统计", format2), true);
            writer.merge(1, 3, 0, 1, "企业名称", false);
            writer.merge(1, 3, 2, 2, "评分", false);
            writer.merge(1, 1, 3, 7, "车辆销产总量", false);
            writer.merge(2, 3, 3, 3, "现有车辆总量", false);

            writer.merge(2, 2, 4, 5, "销售量", false);
            writer.writeCellValue(4, 3, "月初");
            writer.writeCellValue(5, 3, "月末");
            writer.merge(2, 2, 6, 7, "生产量", false);
            writer.writeCellValue(6, 3, "月初");
            writer.writeCellValue(7, 3, "月末");
            //造数据
            List<Map<String, String>> mapList = getData();
            //左侧合并::
            writer.merge(4, 4 + (mapList.size() > 0 ? mapList.size() - 1 : 0), 0, 0, "高风险", false);
            CellStyle cellStyle = writer.createCellStyle(0, 4 + (mapList.size() > 0 ? mapList.size() - 1 : 0));
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 背景色
            //填充数据:
            for (int i = 0; i < mapList.size(); i++) {
                final Map<String, String> map = mapList.get(i);
                writer.writeCellValue(1, 4 + i, map.get("qymc"));
                writer.writeCellValue(2, 4 + i, map.get("pf"));
                writer.writeCellValue(3, 4 + i, map.get("xyxlzl"));
                writer.writeCellValue(4, 4 + i, map.get("xsuc"));
                writer.writeCellValue(5, 4 + i, map.get("xsym"));
                writer.writeCellValue(6, 4 + i, map.get("scyc"));
                writer.writeCellValue(7, 4 + i, map.get("scym"));
            }
            String fileName = new String(("crop").getBytes(), "utf-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            out = response.getOutputStream();
            writer.flush(out, true);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭writer,释放内存
            writer.close();
            //关闭输出流
            IoUtil.close(out);
        }
    }

private List<Map<String, String>> getData() {
List<Map<String, String>> mapList = new ArrayList<>();
Map<String, String> map1 = new HashMap<>();
map1.put("qymc", "富士康");
map1.put("pf", "0.2");
map1.put("xyxlzl", "12");
map1.put("xsuc", "2");
map1.put("xsym", "2");
map1.put("scyc", "3");
map1.put("scym", "3");
Map<String, String> map2 = new HashMap<>();
map2.put("qymc", "捷达");
map2.put("pf", "0.4");
map2.put("clxczl", "17");
map2.put("xyxlzl", "13");
map2.put("xsuc", "1");
map2.put("xsym", "2");
map2.put("scyc", "4");
map2.put("scym", "5");
Map<String, String> map3 = new HashMap<>();
map3.put("qymc", "华为");
map3.put("pf", "0.1");
map3.put("clxczl", "235");
map3.put("xyxlzl", "125");
map3.put("xsuc", "14");
map3.put("xsym", "21");
map3.put("scyc", "42");
map3.put("scym", "51");
mapList.add(map1);
mapList.add(map2);
mapList.add(map3);
return mapList;
}

 

效果:

 

posted @ 2022-11-13 16:50  Simoo23  阅读(80)  评论(0编辑  收藏  举报