失眠网 > Java 利用hutool工具实现导出excel并合并单元格

Java 利用hutool工具实现导出excel并合并单元格

时间：2020-01-09 16:21:18

controller层调用service，就一个核心方法，没错就下面这个代码就能实现了。前提是项目里面要引用hutool包。把我这个复制到项目里面然后改掉字段应该能直接跑起来的。用postman做测试。

<dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId></dependency>

/*** 导出排班** @param list数据集合* @param response response* @throws IOException 异常*/public void exportScheduling(List<SchedulingExportVo> list, HttpServletResponse response) throws IOException {//2.定义基础数据也就是表头List<String> rowHead = CollUtil.newArrayList("水库名称", "规模", "地区", "排班标题", "排班开始日期", "排班结束日期", "责任领导", "排班人员", "巡检人员", "巡检项目", "备注");//3.通过ExcelUtil.getBigWriter()创建Writer对象，BigExcelWriter用于大数据量的导出，不会引起溢出；ExcelWriter writer = ExcelUtil.getBigWriter();//设置内容字体Font font = writer.createFont();font.setBold(true);//font.setColor(Font.COLOR_RED);font.setItalic(true);//第二个参数表示是否忽略头部样式writer.getStyleSet().setFont(font, true);// 定义单元格背景色//StyleSet style = writer.getStyleSet();// 第二个参数表示是否也设置头部单元格背景//style.setBackgroundColor(IndexedColors.RED, false);//4.写入标题writer.writeHeadRow(rowHead);//定义启始行有几行要合并的就定义多少，我定义九个说明我有九个字段都需要合并单元格。int index1 = 1;int index2 = 1;int index3 = 1;int index4 = 1;int index5 = 1;int index6 = 1;int index7 = 1;int index8 = 1;int index9 = 1;// 后面要用到的临时对象List<List<Object>> rows = new LinkedList<>();List<Object> rowA;//按照水库名合并单元格 Map<Long, List<SchedulingExportVo>> idsMaps = list.stream().collect(Collectors.groupingBy(SchedulingExportVo::getId, LinkedHashMap::new, Collectors.toList()));for (Map.Entry<Long, List<SchedulingExportVo>> idsEntry : idsMaps.entrySet()) {List<SchedulingExportVo> listVo = idsEntry.getValue();if (listVo.size() == 1) {index1 = index1 + listVo.size();index2 = index2 + listVo.size();index3 = index3 + listVo.size();index4 = index4 + listVo.size();index5 = index5 + listVo.size();index6 = index6 + listVo.size();index7 = index7 + listVo.size();index8 = index8 + listVo.size();index9 = index9 + listVo.size();} else {//按照水库名合并单元格Map<String, List<SchedulingExportVo>> reservoirNameMaps = listVo.stream().collect(Collectors.groupingBy(SchedulingExportVo::getReservoirName, LinkedHashMap::new, Collectors.toList()));for (Map.Entry<String, List<SchedulingExportVo>> reservoirNameEntry : reservoirNameMaps.entrySet()) {List<SchedulingExportVo> reservoirNameValue = reservoirNameEntry.getValue();//根据数据条数设置合并单元格信息if (reservoirNameValue.size() == 1) {//一条数据不合并index1 = index1 + reservoirNameValue.size();} else {//规则编写writer.merge(index1, index1 + reservoirNameValue.size() - 1, 0, 0, null, true);index1 = index1 + reservoirNameValue.size();//按照规模合并单元格Map<String, List<SchedulingExportVo>> reservoirScaleMaps = listVo.stream().collect(Collectors.groupingBy(SchedulingExportVo::getReservoirScale, LinkedHashMap::new, Collectors.toList()));for (Map.Entry<String, List<SchedulingExportVo>> reservoirScaleEntry : reservoirScaleMaps.entrySet()) {List<SchedulingExportVo> reservoirScaleValue = reservoirScaleEntry.getValue();//根据数据条数设置合并单元格信息if (reservoirScaleValue.size() == 1) {//一条数据不合并index2 = index2 + reservoirScaleValue.size();} else {//规则编写writer.merge(index2, index2 + reservoirScaleValue.size() - 1, 1, 1, null, true);index2 = index2 + reservoirScaleValue.size();//按照地区合并单元格Map<String, List<SchedulingExportVo>> reservoirRegionMaps = listVo.stream().collect(Collectors.groupingBy(SchedulingExportVo::getReservoirRegion, LinkedHashMap::new, Collectors.toList()));for (Map.Entry<String, List<SchedulingExportVo>> reservoirRegionEntry : reservoirRegionMaps.entrySet()) {List<SchedulingExportVo> reservoirRegionValue = reservoirRegionEntry.getValue();if (reservoirRegionValue.size() == 1) {index3 = index3 + reservoirRegionValue.size();} else {//规则编写writer.merge(index3, index3 + reservoirRegionValue.size() - 1, 2, 2, null, true);index3 = index3 + reservoirRegionValue.size();//按照标题合并单元格Map<String, List<SchedulingExportVo>> titleMaps = listVo.stream().collect(Collectors.groupingBy(SchedulingExportVo::getTitle, LinkedHashMap::new, Collectors.toList()));for (Map.Entry<String, List<SchedulingExportVo>> titleEntry : titleMaps.entrySet()) {List<SchedulingExportVo> titleValue = titleEntry.getValue();if (titleValue.size() == 1) {index4 = index4 + titleValue.size();} else {//规则编写writer.merge(index4, index4 + titleValue.size() - 1, 3, 3, null, true);index4 = index4 + titleValue.size();//按照排班开始日期合并单元格Map<Date, List<SchedulingExportVo>> startDateMaps = listVo.stream().collect(Collectors.groupingBy(SchedulingExportVo::getStartDate, LinkedHashMap::new, Collectors.toList()));for (Map.Entry<Date, List<SchedulingExportVo>> startDateEntry : startDateMaps.entrySet()) {List<SchedulingExportVo> startDateValue = startDateEntry.getValue();if (startDateValue.size() == 1) {index5 = index5 + startDateValue.size();} else {//规则编写writer.merge(index5, index5 + startDateValue.size() - 1, 4, 4, null, true);index5 = index5 + startDateValue.size();//按照排班结束日期合并单元格Map<Date, List<SchedulingExportVo>> endDateMaps = listVo.stream().collect(Collectors.groupingBy(SchedulingExportVo::getEndDate, LinkedHashMap::new, Collectors.toList()));for (Map.Entry<Date, List<SchedulingExportVo>> endDateEntry : endDateMaps.entrySet()) {List<SchedulingExportVo> endDateValue = endDateEntry.getValue();if (endDateValue.size() == 1) {index6 = index6 + endDateValue.size();} else {//规则编写writer.merge(index6, index6 + endDateValue.size() - 1, 5, 5, null, true);index6 = index6 + endDateValue.size();//按照责任领导合并单元格Map<String, List<SchedulingExportVo>> personLiableMaps = listVo.stream().collect(Collectors.groupingBy(SchedulingExportVo::getPersonLiable, LinkedHashMap::new, Collectors.toList()));for (Map.Entry<String, List<SchedulingExportVo>> personLiableEntry : personLiableMaps.entrySet()) {List<SchedulingExportVo> personLiableValue = personLiableEntry.getValue();if (personLiableValue.size() == 1) {index7 = index7 + personLiableValue.size();} else {//规则编写writer.merge(index7, index7 + personLiableValue.size() - 1, 6, 6, null, true);index7 = index7 + personLiableValue.size();//按照排班人员合并单元格Map<String, List<SchedulingExportVo>> schedulingPersonMaps = listVo.stream().collect(Collectors.groupingBy(SchedulingExportVo::getSchedulingPerson, LinkedHashMap::new, Collectors.toList()));for (Map.Entry<String, List<SchedulingExportVo>> schedulingPersonEntry : schedulingPersonMaps.entrySet()) {List<SchedulingExportVo> schedulingPersonValue = schedulingPersonEntry.getValue();if (schedulingPersonValue.size() == 1) {index8 = index8 + schedulingPersonValue.size();} else {//规则编写writer.merge(index8, index8 + schedulingPersonValue.size() - 1, 7, 7, null, true);index8 = index8 + schedulingPersonValue.size();//按照备注合并单元格//若是备注数据未null值，就赋值一个空值listVo.stream().filter(a -> a.getRemark() == null).forEach(a -> a.setRemark(""));Map<String, List<SchedulingExportVo>> remarkMaps = listVo.stream().collect(Collectors.groupingBy(SchedulingExportVo::getRemark, LinkedHashMap::new, Collectors.toList()));for (Map.Entry<String, List<SchedulingExportVo>> remarkEntry : remarkMaps.entrySet()) {List<SchedulingExportVo> remarkValue = remarkEntry.getValue();if (remarkValue.size() != 1) {//规则编写writer.merge(index9, index9 + remarkValue.size() - 1, 10, 10, null, true);}index9 = index9 + remarkValue.size();}}}}}}}}}}}}}}}}}}//保存数据for (SchedulingExportVo schedulingExportVo : listVo) {rowA = CollUtil.newArrayList(schedulingExportVo.getReservoirName(),schedulingExportVo.getReservoirScale(),schedulingExportVo.getReservoirRegion(),schedulingExportVo.getTitle(),DateUtil.format(schedulingExportVo.getStartDate(), "yyyy-MM-dd"),DateUtil.format(schedulingExportVo.getEndDate(), "yyyy-MM-dd"),schedulingExportVo.getPersonLiable(),schedulingExportVo.getSchedulingPerson(),schedulingExportVo.getNickName(),schedulingExportVo.getInspectPositionNames(),schedulingExportVo.getRemark());rows.add(rowA);}}//导出数据// 一次性写出内容，使用默认样式，强制输出标题writer.write(rows, true);//response为HttpServletResponse对象response.setContentType("application/vnd.ms-excel;charset=utf-8");//test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码response.setHeader("Content-Disposition", "attachment;filename=filename.xlsx");writer.flush(response.getOutputStream(), true);//此处记得关闭输出Servlet流IoUtil.close(response.getOutputStream());}