【easypoi 模板导出嵌套 list 问题】

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<CellRangeAddress> mergedRegions = firstSheet.getMergedRegions();
		List<Integer> removeMergedRegionIndexList = new ArrayList<>();

		// 处理共享单车详细数据不显示；
		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<Traffic> traffics = new ArrayList<>();

	@Test
	public void myNestedLoopTest() throws IOException {
		TemplateExportParams params = new TemplateExportParams(
				"doc/mynestedloop.xlsx");
//		params.setColForEach(true);
		Map<String,Object> 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<CellRangeAddress> mergedRegions = firstSheet.getMergedRegions();
		List<Integer> 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
	@NoArgsConstructor
	public static class Traffic{
		private String name;
		private Date date;
		private List<TrafficDetail> shareBikes;
		private List<TrafficDetail> subways;
	}
	@Data
	@AllArgsConstructor
	@NoArgsConstructor
	public static class TrafficDetail{
		private Integer number;
		private Integer cost;

	}
	@Before
	public 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);
	}

}