easypoi 模板导出多 list 问题

• 1背景
• • 1.1 espoi 模板定义
  • 1.2 导出结果
  • 1.3发现共享单车的数据没有显示，手动操作取消共享单车的单元格合并
  • 1.4手动取消单元格合并后数据，正常再把框线画好
  • 1.5 代码操作：用 esaypoi 处理到1.2，用基础 poi 代码处理 1.3和 1.4
  • 1.6还需要注意一点共享单车和地铁 list 不能一个有数据一个没数据，否则会导致格式错误显示
  • 1.7 代码

1背景

• 没钱的小明去上海旅游，交通方式选择共享单车或者地铁，要统计每天小明两种交通方式费用数据如图：
  

1.1 espoi 模板定义

1.2 导出结果

1.3发现共享单车的数据没有显示，手动操作取消共享单车的单元格合并

1.4手动取消单元格合并后数据，正常再把框线画好

1.5 代码操作：用 esaypoi 处理到1.2，用基础 poi 代码处理 1.3和 1.4

		// (1)处理共享单车细数据不显示；// (2)处理合并单元格边框实线//(3)处理共享单车和地铁没有边框实线

private void myRest(Workbook book) {Sheet firstSheet = book.getSheetAt(0);List mergedRegions = firstSheet.getMergedRegions();List removeMergedRegionIndexList = new ArrayList<>();// (1)处理付款明细数据不显示；for (int i = 0; i < mergedRegions.size(); i++) {CellRangeAddress mergedRegion = mergedRegions.get(i);int firstColumn = mergedRegion.getFirstColumn();int lastColumn = mergedRegion.getLastColumn();int firstRow = mergedRegion.getFirstRow();if(firstRow >= 2 && firstColumn >= 3 && lastColumn <= 5){removeMergedRegionIndexList.add(i);}}firstSheet.removeMergedRegions(removeMergedRegionIndexList);// (2)处理合并单元格边框实线for (CellRangeAddress mergedRegion : mergedRegions) {RegionUtil.setBorderBottom(BorderStyle.THIN, mergedRegion, firstSheet);RegionUtil.setBorderTop(BorderStyle.THIN, mergedRegion, firstSheet);RegionUtil.setBorderLeft(BorderStyle.THIN, mergedRegion, firstSheet);RegionUtil.setBorderRight(BorderStyle.THIN, mergedRegion, firstSheet);}// (3)处理共享单车和地铁没有边框实线；略，自行处理}

1.6还需要注意一点共享单车和地铁 list 不能一个有数据一个没数据，否则会导致格式错误显示

1.7 代码

package cn.afterturn.easypoi.test.excel.template;import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.util.JSON;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.junit.Before;
import org.junit.Test;import java.io.FileOutputStream;
import java.io.IOException;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.*;/*** @author xuanjinnan* @date 2022/12/9 10:32*/
public class MyComplexTemplateTest {List traffics = new ArrayList<>();@Testpublic void myNestedLoopTest() throws IOException {TemplateExportParams params = new TemplateExportParams("doc/mynestedloop.xlsx");
//		params.setColForEach(true);Map value = new HashMap<>();value.put("list",traffics);Workbook book = ExcelExportUtil.exportExcel(params, JSON.parseJson(JSON.toJson(value),Map.class));// (1)处理付款明细数据不显示；// (2)处理合并单元格边框实线// (3)处理共享单车和地铁没有边框实线myRest(book);FileOutputStream fos = new FileOutputStream("D:/home/excel/ExcelExportTemplateMyNestedLoop.xlsx");book.write(fos);fos.close();}private void myRest(Workbook book) {Sheet firstSheet = book.getSheetAt(0);List mergedRegions = firstSheet.getMergedRegions();List removeMergedRegionIndexList = new ArrayList<>();// (1)处理付款明细数据不显示；for (int i = 0; i < mergedRegions.size(); i++) {CellRangeAddress mergedRegion = mergedRegions.get(i);int firstColumn = mergedRegion.getFirstColumn();int lastColumn = mergedRegion.getLastColumn();int firstRow = mergedRegion.getFirstRow();if(firstRow >= 2 && firstColumn >= 3 && lastColumn <= 5){removeMergedRegionIndexList.add(i);}}firstSheet.removeMergedRegions(removeMergedRegionIndexList);// (2)处理合并单元格边框实线for (CellRangeAddress mergedRegion : mergedRegions) {RegionUtil.setBorderBottom(BorderStyle.THIN, mergedRegion, firstSheet);RegionUtil.setBorderTop(BorderStyle.THIN, mergedRegion, firstSheet);RegionUtil.setBorderLeft(BorderStyle.THIN, mergedRegion, firstSheet);RegionUtil.setBorderRight(BorderStyle.THIN, mergedRegion, firstSheet);}// (3)处理共享单车和地铁没有边框实线；略，自行处理}@Data@AllArgsConstructor@NoArgsConstructorpublic static class Traffic{private String name;private Date date;private List shareBikes;private List subways;}@Data@AllArgsConstructor@NoArgsConstructorpublic static class TrafficDetail{private Integer number;private Integer cost;}@Beforepublic void testBefore() {Traffic traffic1 = new Traffic("小明", Date.from(LocalDateTime.now().minusDays(4).atZone(ZoneId.systemDefault()).toInstant()), new ArrayList<>(Arrays.asList(new TrafficDetail(1,3),new TrafficDetail(2,4),new TrafficDetail(3,4))), new ArrayList<>(Collections.singletonList(new TrafficDetail(1, 5))));Traffic traffic2 = new Traffic("小明", Date.from(LocalDateTime.now().minusDays(3).atZone(ZoneId.systemDefault()).toInstant()), new ArrayList<>(Collections.singletonList(new TrafficDetail(1, 3))), new ArrayList<>(Arrays.asList(new TrafficDetail(1,5),new TrafficDetail(2,6),new TrafficDetail(3,7))));Traffic traffic3 = new Traffic("小明", Date.from(LocalDateTime.now().minusDays(2).atZone(ZoneId.systemDefault()).toInstant()), new ArrayList<>(Collections.singletonList(new TrafficDetail(1, 3))), new ArrayList<>(Collections.singletonList(new TrafficDetail())));Traffic traffic4 = new Traffic("小明", Date.from(LocalDateTime.now().minusDays(1).atZone(ZoneId.systemDefault()).toInstant()), new ArrayList<>(Collections.singletonList(new TrafficDetail())), new ArrayList<>(Collections.singletonList(new TrafficDetail(1, 5))));traffics.add(traffic1);traffics.add(traffic2);traffics.add(traffic3);traffics.add(traffic4);}}