package com.jilongda.manage;
|
|
import cn.afterturn.easypoi.excel.ExcelExportUtil;
|
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
|
import lombok.AllArgsConstructor;
|
import lombok.Data;
|
import lombok.NoArgsConstructor;
|
import org.apache.poi.ss.usermodel.Sheet;
|
import org.apache.poi.ss.usermodel.Workbook;
|
import org.apache.poi.ss.util.CellRangeAddress;
|
import org.aspectj.lang.annotation.Before;
|
import org.junit.jupiter.api.Test;
|
|
import java.io.FileOutputStream;
|
import java.io.IOException;
|
import java.time.LocalDateTime;
|
import java.time.ZoneId;
|
import java.util.*;
|
import java.util.concurrent.atomic.AtomicInteger;
|
|
/**
|
* @author xuanjinnan
|
* @date 2022/12/9 10:32
|
*/
|
public class MyComplexTemplateTest {
|
List<Traffic> traffics = new ArrayList<>();
|
|
@Test
|
public void myNestedLoopTest() throws IOException {
|
|
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);
|
//设置 id
|
AtomicInteger id = new AtomicInteger(1);
|
traffics.forEach(traffic -> traffic.setId(id.getAndIncrement()));
|
|
TemplateExportParams params = new TemplateExportParams(
|
"template/销售数据列表.xls");
|
// params.setColForEach(true);
|
Map<String,Object> value = new HashMap<>();
|
value.put("list",traffics);
|
|
Workbook book = ExcelExportUtil.exportExcel(params, value);
|
// (1)处理付款明细数据不显示；
|
// (2)处理合并单元格边框实线
|
// (3)处理共享单车和地铁没有边框实线
|
myRest(book);
|
FileOutputStream fos = new FileOutputStream("F:/workSpace/JiLongDa/manage/src/main/resources/template/销售数据列表.xls");
|
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{
|
public Traffic(String name, Date date, List<TrafficDetail> shareBikes, List<TrafficDetail> subways) {
|
this.name = name;
|
this.date = date;
|
this.shareBikes = shareBikes;
|
this.subways = subways;
|
}
|
|
private Integer id;
|
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;
|
|
}
|
|
}
|
