导出合并单元格工具类
1.main方法调用
public static void main(String[] args) {
List<GroupExport<ScoreDemo, String>> groupExports = new ArrayList<>();
GroupExport<ScoreDemo, String> build = GroupExport.<ScoreDemo, String>builder()
//对省进行分组
.groupColumn(ScoreDemo::getProvince)
.calculation(demoData -> {
//当前省数据进行求和
String sum = demoData.stream().map(x -> new BigDecimal(x.getScore())).reduce(BigDecimal::add).get().toString();
//设置省总成绩和 这里全部设置 和设置一次都行
for (ScoreDemo demoDatum : demoData) {
demoDatum.setProvinceScore(sum);
}
})
//需要合并的列
.columns(new int[]{0, 7})
.build();
GroupExport<ScoreDemo, String> build2 = GroupExport.<ScoreDemo, String>builder()
//对市进行分组
.groupColumn(ScoreDemo::getCity)
.calculation(demoData -> {
//当前市数据进行求和
String sum = demoData.stream().map(x -> new BigDecimal(x.getScore())).reduce(BigDecimal::add).get().toString();
//设置省总成绩和 这里全部设置 和设置一次都行
for (ScoreDemo demoDatum : demoData) {
demoDatum.setCityScore(sum);
}
})
//需要合并的列
.columns(new int[]{1, 6})
.build();
GroupExport<ScoreDemo, String> build3 = GroupExport.<ScoreDemo, String>builder()
.groupColumn(ScoreDemo::getArea)
.calculation(demoData -> {
String sum = demoData.stream().map(x -> new BigDecimal(x.getScore())).reduce(BigDecimal::add).get().toString();
for (ScoreDemo demoDatum : demoData) {
demoDatum.setAreaScore(sum);
}
})
.columns(new int[]{2, 5})
.build();
//添加进集合
groupExports.add(build);
groupExports.add(build2);
groupExports.add(build3);
//构造数据
List<ScoreDemo> data = mockData();
long start = System.currentTimeMillis();
//导出
ExportGroupUtil.exportGroup(data, groupExports,"省市区成绩单",ScoreDemo.class);
System.out.println(System.currentTimeMillis()-start);
}
private static List<ScoreDemo> mockData() {
Random random = new Random();
List<ScoreDemo> list = Lists.newArrayList();
//数据过大会栈溢出
for (int x = 0; x < 10; x++) {
for (int i = 0; i < 20; i++) {
ScoreDemo e = new ScoreDemo();
if(i < 6){
e.setProvince(x+"A");
if(i < 2){
e.setCity("D");
e.setArea("E");
}else{
e.setCity("F");
if(i == 2){
e.setArea("G");
}else{
e.setArea("H");
}
}
}else if(i < 10){
e.setProvince(x+"B");
if(i < 8){
e.setCity("I");
e.setArea("J");
}else{
e.setCity("K");
if(i < 9){
e.setArea("R");
}else{
e.setArea("M");
}
}
}else{
e.setProvince(x+"C");
if(i < 18){
e.setCity("XX");
e.setArea("XJ");
}else{
e.setCity("XK");
if(i < 19){
e.setArea("XR");
}else{
e.setArea("XM");
}
}
}
e.setName("张三"+i);
e.setScore(random.nextInt(20)+"");
list.add(e);
}
}
return list;
}
2.工具类
package com.ruijie.substitute.controller.common; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.write.builder.ExcelWriterBuilder; import com.alibaba.excel.write.merge.OnceAbsoluteMergeStrategy; import java.lang.reflect.Field; import java.util.*; import java.util.function.Function; import java.util.stream.Collector; import java.util.stream.Collectors; /** * @menu 导出并合并单元格工具类 * @BelongsProject: substitute-resource * @BelongsPackage: com.ruijie.substitute.controller.common * @Author: [email protected] * @CreateTime: 2022-11-22 09:27 * @Version: 1.0 */ public class ExportGroupUtil { /** * * 分组导出数据 * * @author [email protected] * @date 2022/11/22 09:33:24 * @param data 需要导出的数据 排不排序都行 内部会对数据分组排序 * @param groupFunctions 分组函数 GroupExport需写入 分组字段 业务逻辑 合并的列索引 * @param filePath 导出文件路径 * @param clz 导出的实体类class */ public static <T,R extends Comparable> void exportGroup(List<T> data, List<GroupExport<T,R>> groupFunctions, String filePath,Class<T> clz) { if (groupFunctions.isEmpty()) { //不分组的数据 EasyExcel.write(filePath+".xlsx",clz).sheet("模板").doWrite(data); return; } Function<T, R> groupColumn = groupFunctions.get(0).getGroupColumn(); //数据排序 Comparator<T> comparing = Comparator.comparing(groupColumn); if (groupFunctions.size() > 1) { for (int i = 1; i < groupFunctions.size(); i++) { comparing = comparing.thenComparing(groupFunctions.get(i).getGroupColumn()); } } data.sort(comparing); //不分组的数据 EasyExcel.write(filePath+"（未合并单元格）.xlsx",clz).sheet("模板").doWrite(data); //数据分组 Collector groupCollector = Collectors.groupingBy(groupFunctions.get(groupFunctions.size()-1).getGroupColumn(), LinkedHashMap::new, Collectors.toList()); for (int i = groupFunctions.size() - 2; i >= 0; i--) { groupCollector = Collectors.groupingBy(groupFunctions.get(i).getGroupColumn(), LinkedHashMap::new, groupCollector); } //分组结果 Map<String,Object> groupMap = (Map<String,Object>) data.stream().collect(groupCollector); //开始行 表头占了多少行 start就写几 int start = getHeadLen(clz); // 方法2 自定义合并单元格策略 //合并策略 List<OnceAbsoluteMergeStrategy> list = new LinkedList<>(); //分组数据map处理 dataProcessing(groupMap,groupFunctions,0,start,list); // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭 ExcelWriterBuilder write = EasyExcel.write(filePath + ".xlsx",clz); for (OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy : list) { write.registerWriteHandler(onceAbsoluteMergeStrategy); } write.sheet("模板").doWrite(data); } /** * * 递归调用数据处理逻辑 * * @author [email protected] * @date 2022/11/22 09:30:37 * @param map 数据分组后map * @param groupFunctions 分组处理逻辑 这里存放外部写的业务逻辑 通过 groupFunctionsIndex 来顺序获取逻辑 * @param groupFunctionsIndex 分组处理逻辑对应的下标 * @param startIndex 起始行 用来控制数据 * @param strategy 存放分组策略 后续导出使用此集合里面的策略 完成单元格合并 * @return java.util.List<T> */ private static <T,R extends Comparable> List<T> dataProcessing(Map<String,Object> map, List<GroupExport<T, R>> groupFunctions,int groupFunctionsIndex , int startIndex, List<OnceAbsoluteMergeStrategy> strategy) { List<T> list = new LinkedList<>(); for (Map.Entry<String,Object> x : map.entrySet()) { //获得数据value Object value = x.getValue(); //如果数据不是最小单位 if(value instanceof Map){ //处理数据 并 返回最小数据集合 List<T> data = dataProcessing((Map<String, Object>) value, groupFunctions, groupFunctionsIndex + 1, startIndex, strategy); GroupExport<T, R> trGroupExport = groupFunctions.get(groupFunctionsIndex); trGroupExport.getCalculation().accept(data); //拿出哪些列需要合并 int[] columns = trGroupExport.getColumns(); //增加合并单元格策略 if(data.size() > 1){ for (int column : columns) { strategy.add(new OnceAbsoluteMergeStrategy(startIndex, startIndex + data.size() - 1, column, column)); } } startIndex += data.size() ; list.addAll(data); }else if(value instanceof List){ List<T> minimumUnitList = (List<T>) value; //已经是最小单位了 GroupExport<T, R> trGroupExport = groupFunctions.get(groupFunctionsIndex); //处理业务逻辑 trGroupExport.getCalculation().accept(minimumUnitList); //拿出哪些列需要合并 int[] columns = trGroupExport.getColumns(); //增加合并单元格策略 if (minimumUnitList.size() > 1) { for (int column : columns) { strategy.add(new OnceAbsoluteMergeStrategy(startIndex, startIndex + minimumUnitList.size() - 1, column, column)); } } startIndex += minimumUnitList.size() ; list.addAll(minimumUnitList); } } return list; } /** * * 获取导出表头行高 主要是用来判断表头占了几行 得到后续合并单元格的起始位置 * * @author [email protected] * @date 2022/11/21 14:23:03 * @param clz 导出实体类class * @return int */ private static <T> int getHeadLen(Class<T> clz) { int height = 0; Field[] declaredFields = clz.getDeclaredFields(); for (Field declaredField : declaredFields) { ExcelProperty annotation = declaredField.getAnnotation(ExcelProperty.class); if(annotation != null){ String[] value = annotation.value(); if(value != null && value.length > height){ height = value.length; } } } return height; } }
3.相关实体
package com.ruijie.substitute.controller.common; import lombok.Builder; import lombok.Getter; import java.util.List; import java.util.function.Consumer; import java.util.function.Function; /** * @menu 分组导出实体类 * @BelongsProject: substitute-resource * @BelongsPackage: com.ruijie.substitute.controller.common * @Author: [email protected] * @CreateTime: 2022-11-18 17:02 * @Version: 1.0 */ @Builder @Getter public final class GroupExport<T, R> { /** * 按照什么来分组 */ private Function<T, R> groupColumn; /** * 对这些分组数据需要处理的逻辑 */ private Consumer<List<T>> calculation; /** * 涉及到需要合并的列 对应excel中的索引位置 从0 开始 */ private int[] columns; }
4实体类
package com.ruijie.substitute.controller.common; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.write.style.HeadStyle; import com.alibaba.excel.enums.poi.FillPatternTypeEnum; import lombok.EqualsAndHashCode; import lombok.Getter; import lombok.Setter; import lombok.ToString; /** * @menu 备货产品转单率 * @BelongsProject: substitute-resource * @BelongsPackage: com.ruijie.substitute.controller.common * @Author: [email protected] * @CreateTime: 2022-11-16 15:54 * @Version: 1.0 */ @Setter @Getter @EqualsAndHashCode @ToString @HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 22) public class ScoreDemo { @ExcelProperty(index = 0, value = {"项目基础信息", "省"}) private String province; @ExcelProperty(index = 1, value ={"项目基础信息", "市"}) private String city; @ExcelProperty(index = 2, value = {"项目基础信息", "区"}) private String area; @ExcelProperty(index = 3, value = {"项目基础信息", "名称"}) private String name; @ExcelProperty(index = 4, value = {"项目基础信息", "成绩"}) private String score; @ExcelProperty(index = 5, value = {"项目基础信息", "区总成绩"}) private String areaScore; @ExcelProperty(index = 6, value = {"项目基础信息", "市总成绩"}) private String cityScore; @ExcelProperty(index = 7, value = {"项目基础信息", "省总成绩"}) private String provinceScore; }
标签：int,List,单元格,导出,value,EsayExcel,import,com,groupFunctions From： https://www.cnblogs.com/yexuba/p/16914213.html