后台导出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;
}
效果: