import com.alibaba.excel.EasyExcel; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.enums.WriteDirectionEnum; import com.alibaba.excel.metadata.Head; import com.alibaba.excel.write.merge.AbstractMergeStrategy; import com.alibaba.excel.write.metadata.WriteSheet; import com.alibaba.excel.write.metadata.fill.FillConfig; import com.alibaba.excel.write.metadata.fill.FillWrapper; import com.alibaba.fastjson.JSON; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import io.swagger.models.auth.In; import lombok.Data; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.jeecg.JeecgWeeklyCloudApplication; import org.jeecg.common.api.vo.Result; import org.jeecg.common.util.DateUtils; import org.jeecg.modules.weekly.entity.WekEvaluate; import org.jeecg.modules.weekly.feign.SystemClient; import org.jeecg.modules.weekly.feign.model.SysUser; import org.jeecg.modules.weekly.service.IWekEvaluateService; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Value; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.test.context.junit4.SpringRunner; import java.io.*; import java.text.SimpleDateFormat; import java.time.LocalDate; import java.time.temporal.TemporalAdjusters; import java.util.*; import java.util.concurrent.atomic.AtomicBoolean; import java.util.concurrent.atomic.AtomicInteger; import java.util.concurrent.atomic.AtomicReference; import java.util.stream.Collectors; @RunWith(SpringRunner.class) @SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT, classes = JeecgWeeklyCloudApplication.class) public class WeeklyTest { @Value(value = "${jeecg.path.upload}") private String uploadpath; @Autowired private IWekEvaluateService wekEvaluateService; @Autowired private SystemClient systemClient; /** * 获取一年的周 */ @Test public void getWeekInYear() { List> res = new ArrayList<>(); //当前年 int curYear = DateUtils.getCurYear(); //统计一年有多少周 int yearWeekCount = 0; //统计一年有多少周 int seasonCount = 0; //一年12个月 for (int i = 1; i <= 12; i++) { //月 Map monthMap = new HashMap<>(); monthMap.put("titile", i + "月"); List> weekList = new ArrayList<>(); monthMap.put("children", weekList); res.add(monthMap); //季 if (i % 3 == 0) { seasonCount++; Map seasonMap = new HashMap<>(); seasonMap.put("titile", "第" + seasonCount + "季度"); seasonMap.put("column", "s" + seasonCount); res.add(seasonMap); } LocalDate date = LocalDate.of(curYear, i, 1); //获取当月第一天是星期几 int week = date.getDayOfWeek().getValue(); //这个月最后一天 date = date.with(TemporalAdjusters.lastDayOfMonth()); //获取这天是这个月的第几天 int dayOfMonth = date.getDayOfMonth(); //统计一个月有多少周 （TODO ：此处分周逻辑保持和周报获取周逻辑一致，定义周日在哪个月则设定本周属于哪个月） int monthWeekCount = 0; for (int j = 1; j <= dayOfMonth; j++) { LocalDate everyDay = LocalDate.of(curYear, i, j); int d = everyDay.getDayOfWeek().getValue(); //System.err.println("日期：" + everyDay + "-星期：" + d); if (d % 7 == 0) { monthWeekCount++; yearWeekCount++; String weekToDayStartStr = DateUtils.weekToDayStartStr(yearWeekCount); String weekToDayEndStr = DateUtils.weekToDayEndStr(yearWeekCount); System.out.println(i + "月：" + monthWeekCount + "周-No:" + yearWeekCount + "------" + weekToDayStartStr + "==" + weekToDayEndStr); //添加每个月的周 Map weekMap = new HashMap<>(); weekMap.put("title", i + "月第" + monthWeekCount + "周"); weekMap.put("column", "m" + i + "w" + monthWeekCount); weekMap.put("wstart", weekToDayStartStr); weekMap.put("wend", weekToDayEndStr); weekMap.put("wno", yearWeekCount); weekList.add(weekMap); } } } } @Test public void exportWeekly() { String roopath = System.getProperty("user.dir"); roopath = roopath.substring(0, roopath.indexOf("/lb-boot/") + 8); //模板路径 String templateFileName = roopath + File.separator + "export-template" + File.separator + "weekly" + File.separator + "depart_users_weekly.xlsx"; //生成文件路径 String fileName = roopath + File.separator + "export" + File.separator + "depart_users_weekly" + System.currentTimeMillis() + ".xlsx"; } @Test public void demo1() { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); Calendar cal = Calendar.getInstance(); // 设置每周的开始日期 cal.setFirstDayOfWeek(Calendar.MONDAY); cal.setMinimalDaysInFirstWeek(0); cal.set(Calendar.YEAR, 2023); cal.set(Calendar.WEEK_OF_YEAR, 1); cal.set(Calendar.DAY_OF_WEEK, cal.getFirstDayOfWeek()); String beginDate = sdf.format(cal.getTime()); cal.add(Calendar.DAY_OF_WEEK, 6); String endDate = sdf.format(cal.getTime()); System.out.println(2023 + "年第" + 1 + "周起止时间：" + beginDate + "=----" + endDate); } //测试周的计算逻辑 @Test public void demo2() { String sheet11 = DateUtils.getWeekInMonth(DateUtils.str2Date("2023-01-02", DateUtils.date_sdf.get())); String sheet12 = DateUtils.getWeekInMonth(DateUtils.str2Date("2023-01-09", DateUtils.date_sdf.get())); String sheet13 = DateUtils.getWeekInMonth(DateUtils.str2Date("2023-01-16", DateUtils.date_sdf.get())); String sheet14 = DateUtils.getWeekInMonth(DateUtils.str2Date("2023-01-23", DateUtils.date_sdf.get())); String sheet15 = DateUtils.getWeekInMonth(DateUtils.str2Date("2023-01-30", DateUtils.date_sdf.get())); String sheet1 = DateUtils.getWeekInMonth(DateUtils.str2Date("2023-11-27", DateUtils.date_sdf.get())); String sheet2 = DateUtils.getWeekInMonth(DateUtils.str2Date("2023-12-04", DateUtils.date_sdf.get())); String sheet3 = DateUtils.getWeekInMonth(DateUtils.str2Date("2023-12-11", DateUtils.date_sdf.get())); String sheet4 = DateUtils.getWeekInMonth(DateUtils.str2Date("2023-12-18", DateUtils.date_sdf.get())); String sheet5 = DateUtils.getWeekInMonth(DateUtils.str2Date("2023-12-25", DateUtils.date_sdf.get())); String sheet = DateUtils.getWeekInMonth(DateUtils.str2Date("2024-01-01", DateUtils.date_sdf.get())); System.out.println(sheet11); System.out.println(sheet12); System.out.println(sheet13); System.out.println(sheet14); System.out.println(sheet15); System.out.println(sheet1); System.out.println(sheet2); System.out.println(sheet3); System.out.println(sheet4); System.out.println(sheet5); System.out.println(sheet); } public String getWeekInMonth(Date date) { Calendar calendar = Calendar.getInstance(); calendar.setTime(date); calendar.setFirstDayOfWeek(Calendar.MONDAY); calendar.setMinimalDaysInFirstWeek(7); //获取当前日期所在周周日 int week = calendar.get(Calendar.WEEK_OF_MONTH); int month = calendar.get(Calendar.MONTH) + 1; int year = calendar.get(Calendar.YEAR); return year + "年" + month + "月第" + week + "周"; } private Map createStatisticsYearTitle() { Map root = new HashMap<>(); //title List> res = new ArrayList<>(); //周号对应colmun Map map = new HashMap<>(); //需要合并的行或列信息 TreeMap mergeSeason = new TreeMap<>(); TreeMap mergeMonth = new TreeMap<>(); //当前年 int curYear = DateUtils.getCurYear(); //统计一年有多少周 int yearWeekCount = 0; int sort = 0; //季 int seasonCount = 1; //一年12个月 for (int i = 1; i <= 12; i++) { LocalDate date = LocalDate.of(curYear, i, 1); //获取当月第一天是星期几 int week = date.getDayOfWeek().getValue(); //这个月最后一天 date = date.with(TemporalAdjusters.lastDayOfMonth()); //获取这天是这个月的第几天 int dayOfMonth = date.getDayOfMonth(); //统计一个月有多少周 （TODO ：此处分周逻辑保持和周报获取周逻辑一致，定义周日在哪个月则设定本周属于哪个月） int monthWeekCount = 0; for (int j = 1; j <= dayOfMonth; j++) { LocalDate everyDay = LocalDate.of(curYear, i, j); int d = everyDay.getDayOfWeek().getValue(); //System.err.println("日期：" + everyDay + "-星期：" + d); if (d % 7 == 0) { monthWeekCount++; yearWeekCount++; sort++; String weekToDayStartStr = DateUtils.weekToDayStartStr(yearWeekCount); String weekToDayEndStr = DateUtils.weekToDayEndStr(yearWeekCount); System.out.println(i + "月：" + monthWeekCount + "周-No:" + yearWeekCount + "------" + weekToDayStartStr + "==" + weekToDayEndStr); //添加每个月的周 Map weekMap = new HashMap<>(); weekMap.put("title", i + "月第" + monthWeekCount + "周"); weekMap.put("month", i + "月"); weekMap.put("week", "第" + monthWeekCount + "周"); weekMap.put("season", seasonCount + "季度"); weekMap.put("column", "{data.w" + (curYear * 100 + yearWeekCount) + "_1}"); weekMap.put("wstart", weekToDayStartStr); weekMap.put("wend", weekToDayEndStr); weekMap.put("wno", yearWeekCount); weekMap.put("type", "自评"); weekMap.put("sort", sort); //weekMap.put("curWeek", DateUtils.getCurWeek()); res.add(weekMap); sort++; Map weekMap2 = new HashMap<>(); weekMap2.put("title", i + "月第" + monthWeekCount + "周"); weekMap2.put("month", i + "月"); weekMap2.put("week", "第" + monthWeekCount + "周"); weekMap2.put("season", seasonCount + "季度"); weekMap2.put("column", "{data.w" + (curYear * 100 + yearWeekCount) + "_2}"); weekMap2.put("wstart", weekToDayStartStr); weekMap2.put("wend", weekToDayEndStr); weekMap2.put("wno", yearWeekCount); weekMap2.put("type", "考评"); weekMap2.put("sort", sort); res.add(weekMap2); map.put(yearWeekCount, "m" + i + "w" + monthWeekCount); } } sort++; //每个月加考评平均分 //月度 Map mon1 = new HashMap<>(); mon1.put("season", seasonCount + "季度"); mon1.put("week", "月度"); mon1.put("month", i + "月绩效"); mon1.put("column", "{data.m" + i + "_1}"); mon1.put("type", "月度"); mon1.put("sort", sort); res.add(mon1); sort++; //绩效等级 Map mon2 = new HashMap<>(); mon2.put("season", seasonCount + "季度"); mon2.put("week", "绩效等级"); mon2.put("month", i + "月绩效"); mon2.put("column", "{data.m" + i + "_2}"); mon2.put("type", "绩效等级"); mon2.put("sort", sort); res.add(mon2); sort++; //发放比例 Map mon3 = new HashMap<>(); mon3.put("season", seasonCount + "季度"); mon3.put("week", "发放比例"); mon3.put("month", i + "月绩效"); mon3.put("column", "{data.m" + i + "_3}"); mon3.put("type", "发放比例"); mon3.put("sort", sort); res.add(mon3); //季 if (i % 3 == 0) { sort++; //发放比例 Map s1 = new HashMap<>(); s1.put("season", seasonCount + "季度"); s1.put("week", "季度"); s1.put("month", seasonCount + "季度绩效"); s1.put("column", "{data.s" + seasonCount + "_1}"); s1.put("type", "季度"); s1.put("sort", sort); res.add(s1); sort++; //发放比例 Map s2 = new HashMap<>(); s2.put("season", seasonCount + "季度"); s2.put("week", "绩效等级"); s2.put("month", seasonCount + "季度绩效"); s2.put("column", "{data.s" + seasonCount + "_2}"); s2.put("type", "绩效等级"); s2.put("sort", sort); res.add(s2); sort++; //发放比例 Map s3 = new HashMap<>(); s3.put("season", seasonCount + "季度"); s3.put("week", "发放比例"); s3.put("month", seasonCount + "季度绩效"); s3.put("column", "{data.s" + seasonCount + "_3}"); s3.put("type", "发放比例"); s3.put("sort", sort); res.add(s3); seasonCount++; } } root.put("colmuns", res); root.put("config", map); System.err.println(res); System.err.println(JSON.toJSON(res)); //计算合并数据 //1.季度 Map>> seasonMap = res.stream().collect(Collectors.groupingBy(item -> item.get("season"))); seasonMap.forEach((season, list) -> { //季度 Integer s = Integer.parseInt(season.toString().replaceAll("\\D+", "")); int size = list.size(); mergeSeason.put(s, size); }); //2.月 List monMergeList = new ArrayList<>(); List wekMergeList = new ArrayList<>(); AtomicReference monMergeIndex = new AtomicReference<>(1); res.forEach(item -> { String month = (String) item.get("month"); MergeBean bean = new MergeBean(); bean.setCol(month); bean.setSort(monMergeIndex.get()); monMergeIndex.getAndSet(monMergeIndex.get() + 1); monMergeList.add(bean); String week = (String) item.get("week"); MergeBean bean2 = new MergeBean(); bean2.setCol(week); bean2.setSort(monMergeIndex.get()); monMergeIndex.getAndSet(monMergeIndex.get() + 1); wekMergeList.add(bean2); }); Map> monGroupList = monMergeList.stream().collect(Collectors.groupingBy( item -> item.getCol(), LinkedHashMap::new, Collectors.toList())); List mergeWeek = new ArrayList<>(); LinkedHashMap>> collect = res.stream().collect(Collectors.groupingBy(item -> item.get("month").toString(), LinkedHashMap::new, Collectors.toList())); collect.forEach((m, list) -> { LinkedHashMap>> week = list.stream().collect(Collectors.groupingBy(item -> item.get("week").toString(), LinkedHashMap::new, Collectors.toList())); week.forEach((w, weekList) -> { mergeWeek.add(weekList.size()); }); }); monGroupList.forEach((key, list) -> { mergeMonth.put(mergeMonth.size(), list.size()); }); root.put("mergeSeason", mergeSeason); root.put("mergeMonth", mergeMonth); root.put("mergeWeek", mergeWeek); return root; } @Test public void createYearWeekData() { Map statisticsYearTitle = createStatisticsYearTitle(); System.out.println(JSON.toJSON(statisticsYearTitle)); } @Test public void exportEvaTemplate() { String roopath = System.getProperty("user.dir"); roopath = roopath.substring(0, roopath.indexOf("/lb-boot/") + 8); //模板路径 String templateFileName = roopath + File.separator + "export-template" + File.separator + "weekly" + File.separator + "weeklyEva.xlsx"; //生成文件路径 String fileName = roopath + File.separator + "export" + File.separator + "weeklyEva" + System.currentTimeMillis() + ".xlsx"; String fileName2 = roopath + File.separator + "export" + File.separator + "weeklyRes" + System.currentTimeMillis() + ".xlsx"; Map res = createStatisticsYearTitle(); List> colmuns = new ArrayList<>(); if (res.containsKey("colmuns")) { colmuns = (List>) res.get("colmuns"); } Map config = new HashMap<>(); if (res.containsKey("config")) { config = (Map) res.get("config"); } TreeMap mergeSeason = new TreeMap<>(); if (res.containsKey("mergeSeason")) { mergeSeason = (TreeMap) res.get("mergeSeason"); } TreeMap mergeMonth = new TreeMap<>(); if (res.containsKey("mergeMonth")) { mergeMonth = (TreeMap) res.get("mergeMonth"); } List mergeWeek = new ArrayList<>(); if (res.containsKey("mergeWeek")) { mergeWeek = (List) res.get("mergeWeek"); } System.err.println("导出"); System.err.println(colmuns); File templateFile = new File(templateFileName); try { FileInputStream fileInputStream = new FileInputStream(templateFile); ByteArrayOutputStream bos = new ByteArrayOutputStream(); //原模板只有一个sheet，通过poi复制出需要的sheet个数的模板 XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream); //设置模板的第一个sheet的名称 // workbook.setSheetName(1, basisinfo.get("sheet").toString()); //写到流里 workbook.write(bos); byte[] bArray = bos.toByteArray(); InputStream is = new ByteArrayInputStream(bArray); //输出文件路径 ExcelWriter excelWriter = EasyExcel.write(fileName) .registerWriteHandler(new MyHandler(mergeSeason, mergeMonth, mergeWeek)) .withTemplate(is).build(); WriteSheet writeSheet = EasyExcel.writerSheet().build(); FillConfig horConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build(); horConfig.setForceNewRow(true); excelWriter.fill(new FillWrapper("colmuns", colmuns), horConfig, writeSheet); //构建模版的 序号 部门 项目 职务 Map basInfo = new HashMap<>(); basInfo.put("no", "{data.no}"); basInfo.put("depart", "{data.depart}"); basInfo.put("name", "{data.name}"); basInfo.put("post", "{data.post}"); basInfo.put("year", DateUtils.getCurYear()); excelWriter.fill(basInfo, writeSheet); // 关闭流 excelWriter.finish(); List> testData = createTestData(colmuns); List> data = createWeeklyEvaData(colmuns); exportEvaData(data, fileName, fileName2); } catch (Exception e) { e.printStackTrace(); } } /** * 创建绩效数据 * * @param colmuns * @return */ private List> createWeeklyEvaData(List> colmuns) { List> res = new ArrayList<>(); //查询当前年的所有记录 TODO 动态传入年份信息 int year = DateUtils.getCurYear(); QueryWrapper wekEvaluateQueryWrapper = new QueryWrapper<>(); wekEvaluateQueryWrapper.lambda().eq(WekEvaluate::getYear, year); List wekEvaluateList = wekEvaluateService.list(wekEvaluateQueryWrapper); if (wekEvaluateList.isEmpty()) { return res; } //按照个人分组组装数据 Map> wekEvaluategroup = wekEvaluateList.stream().collect(Collectors.groupingBy(item -> item.getZp())); AtomicInteger index = new AtomicInteger(1); //数据行数 wekEvaluategroup.forEach((user, evaluateList) -> { Map item = new HashMap<>(); item.put("no", index.get()); //序号 item.put("name", evaluateList.get(0).getZpName()); //姓名 //个人周报数据再按月分组 Map> monEvaMap = evaluateList.stream().collect(Collectors.groupingBy(month -> month.getMonth())); TreeMap> seasonScoreMap = new TreeMap<>(); for (int i = 1; i <= 4; i++) { List list = new ArrayList<>(); seasonScoreMap.put(i, list); } monEvaMap.forEach((mon, monList) -> { List monthScoreList = new ArrayList<>(); //个人周报数据再按周分组 Map> weekEvaMap = monList.stream().collect(Collectors.groupingBy(week -> week.getWeek())); weekEvaMap.forEach((w, wekList) -> { WekEvaluate eva = wekList.get(0); if (eva == null) { return; } String zpPlan = eva.getZpPlan(); String zpAmount = eva.getZpAmount(); String zpEffe = eva.getZpEffe(); String zpQuality = eva.getZpQuality(); if (StringUtils.isNotEmpty(zpPlan) && StringUtils.isNotEmpty(zpAmount) && StringUtils.isNotEmpty(zpEffe) && StringUtils.isNotEmpty(zpQuality)) { int score = calcEvaScore(zpPlan, zpAmount, zpEffe, zpQuality); //注意 此处生成key要和excel生成head中colnum一致 item.put("w" + eva.getYearWeek() + "_1", score); } String kpPlan = eva.getKpPlan(); String kpAmount = eva.getKpAmount(); String kpEffe = eva.getKpEffe(); String kpQuality = eva.getKpQuality(); if (StringUtils.isNotEmpty(kpPlan) && StringUtils.isNotEmpty(kpAmount) && StringUtils.isNotEmpty(kpEffe) && StringUtils.isNotEmpty(kpQuality)) { int score = calcEvaScore(kpPlan, kpAmount, kpEffe, kpQuality); //注意 此处生成key要和excel生成head中colnum一致 item.put("w" + eva.getYearWeek() + "_2", score); monthScoreList.add(score); } }); //月平均 double score = monthScoreList.stream().mapToInt(Integer::intValue) .average() .orElse(0); int avg = (int) Math.round(score); if(avg == 0){ item.put("m" + mon + "_1", ""); }else { item.put("m" + mon + "_1", avg); } //月绩效等级 String monLevel = "C"; String monPay = "100%"; if (avg == 0) { monLevel = ""; monPay = ""; } else if (avg >= 95) { monPay = "140%"; monLevel = "A"; } else if (avg <= 95 && avg >= 85) { monLevel = "B"; monPay = "110%"; } else if (avg <= 84 && avg >= 75) { monLevel = "C"; monPay = "100%"; } else if (avg <= 74 && avg >= 60) { monLevel = "D"; monPay = "90%"; } else if (avg <= 59) { monLevel = "E"; monPay = "不合格"; } item.put("m" + mon + "_2", monLevel); item.put("m" + mon + "_3", monPay); //根据季添加考评分 if (mon <= 3) { seasonScoreMap.get(1).add(avg); } else if (mon >= 4 && mon <= 6) { seasonScoreMap.get(2).add(avg); } else if (mon >= 7 && mon <= 9) { seasonScoreMap.get(3).add(avg); } else if (mon >= 10 && mon <= 12) { seasonScoreMap.get(4).add(avg); } }); seasonScoreMap.forEach((season, seasonScoreList) -> { //季平均 double score = seasonScoreList.stream().mapToInt(Integer::intValue) .average() .orElse(0); int avg = (int) Math.round(score); if(avg==0){ item.put("s" + season + "_1", ""); }else { item.put("s" + season + "_1", avg); } //季绩效等级 String seasonLevel = "C"; String seasonPay = "100%"; if (avg == 0) { seasonLevel = ""; seasonPay = ""; } else if (avg >= 95) { seasonLevel = "A"; seasonPay = "140%"; } else if (avg <= 95 && avg >= 85) { seasonLevel = "B"; seasonPay = "110%"; } else if (avg <= 84 && avg >= 75) { seasonLevel = "C"; seasonPay = "100%"; } else if (avg <= 74 && avg >= 60) { seasonLevel = "D"; seasonPay = "90%"; } else if (avg <= 59) { seasonLevel = "E"; seasonPay = "不合格"; } item.put("s" + season + "_2", seasonLevel); item.put("s" + season + "_3", seasonPay); }); res.add(item); index.getAndIncrement(); }); //对不存在的数据添加空数据，避免导出excel不渲染 for (int i = 0; i < res.size(); i++) { Map d = res.get(i); for (int j = 0; j < colmuns.size(); j++) { Map c = colmuns.get(j); String column = c.get("column").toString().replace("{data.", "").replace("}", ""); String colStr = ""; for (String key : d.keySet()) { colStr +=key; colStr +=","; } if(!colStr.contains(column)){ res.get(i).put(column, ""); } } } //补齐部门和职务数据 System.err.println("补齐数据"); System.err.println(colmuns); System.err.println(res); return res; } private int calcEvaScore(String a, String b, String c, String d) { int i = calScore(a, 1); int i1 = calScore(b, 1); int i2 = calScore(c, 1); int i3 = calScore(d, 2); return i + i1 + i2 + i3; } private int calScore(String kp, int type) { int score = 0; switch (kp) { case "A": score = 19 * type; break; case "B": score = 17 * type; break; case "C": score = 15 * type; break; case "D": score = 12 * type; break; case "E": score = 5 * type; break; } return score; } private void exportEvaData(List> data, String templateFileName, String exportPath) { File templateFile = new File(templateFileName); try { FileInputStream fileInputStream = new FileInputStream(templateFile); ByteArrayOutputStream bos = new ByteArrayOutputStream(); //原模板只有一个sheet，通过poi复制出需要的sheet个数的模板 XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream); //设置模板的第一个sheet的名称 // workbook.setSheetName(1, basisinfo.get("sheet").toString()); //写到流里 workbook.write(bos); byte[] bArray = bos.toByteArray(); InputStream is = new ByteArrayInputStream(bArray); //输出文件路径 ExcelWriter excelWriter = EasyExcel.write(templateFileName) .withTemplate(is).build(); WriteSheet writeSheet = EasyExcel.writerSheet().build(); FillConfig horConfig = new FillConfig(); horConfig.setForceNewRow(true); excelWriter.fill(new FillWrapper("data", data), horConfig, writeSheet); // 关闭流 excelWriter.finish(); } catch (Exception e) { e.printStackTrace(); } } public List> createTestData(List> colmuns) { List> list = new ArrayList<>(); for (int i = 0; i < 10; i++) { Map item = new HashMap<>(); for (int j = 0; j < colmuns.size(); j++) { String column = colmuns.get(j).get("column").toString(); column = column.replace("{", "").replace("}", "").replace("data.", ""); item.put(column, "测试" + i + "-" + j); } item.put("no", i); item.put("depart", "研发部门"); item.put("name", "张三" + i); item.put("post", "软件工程师" + i); list.add(item); } return list; } @Data public class MergeBean { private String col; private String alias; private Integer count; private Integer sort; } class MyHandler extends AbstractMergeStrategy { private Map mergeSeason; private Map mergeMonth; private List mergeWeek; public MyHandler(Map mergeSeason, Map mergeMonth, List mergeWeek) { this.mergeSeason = mergeSeason; this.mergeMonth = mergeMonth; this.mergeWeek = mergeWeek; } @Override protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) { Integer seasonStartRow = 1; AtomicReference seasonStartCol = new AtomicReference<>(4); AtomicReference seasonEndCol = new AtomicReference<>(0); mergeSeason.forEach((type, value) -> { //1.合并四季的title seasonEndCol.set(seasonStartCol.get() + (value - 1)); if (cell.getRowIndex() == seasonStartRow && cell.getColumnIndex() == seasonStartCol.get()) { CellRangeAddress cellAddresses = new CellRangeAddress(seasonStartRow, seasonStartRow, seasonStartCol.get(), seasonEndCol.get()); sheet.addMergedRegionUnsafe(cellAddresses); } seasonStartCol.set(seasonStartCol.get() + (value - 1) + 1); }); Integer monthStartRow = 2; AtomicReference monthStartCol = new AtomicReference<>(4); AtomicReference monthEndCol = new AtomicReference<>(0); mergeMonth.forEach((type, value) -> { //1.合并月的title monthEndCol.set(monthStartCol.get() + (value - 1)); if (cell.getRowIndex() == monthStartRow && cell.getColumnIndex() == monthStartCol.get()) { CellRangeAddress cellAddresses = new CellRangeAddress(monthStartRow, monthStartRow, monthStartCol.get(), monthEndCol.get()); sheet.addMergedRegionUnsafe(cellAddresses); } monthStartCol.set(monthStartCol.get() + (value - 1) + 1); }); Integer weekStartRow = 3; AtomicReference weekStartCol = new AtomicReference<>(4); AtomicReference weekEndCol = new AtomicReference<>(0); mergeWeek.forEach((value) -> { //1.合并周的title weekEndCol.set(weekStartCol.get() + (value - 1)); if (cell.getRowIndex() == weekStartRow && cell.getColumnIndex() == weekStartCol.get()) { if (value == 1) { CellRangeAddress cellAddresses = new CellRangeAddress(weekStartRow, weekStartRow + 1, weekStartCol.get(), weekEndCol.get()); sheet.addMergedRegionUnsafe(cellAddresses); } else { CellRangeAddress cellAddresses = new CellRangeAddress(weekStartRow, weekStartRow, weekStartCol.get(), weekEndCol.get()); sheet.addMergedRegionUnsafe(cellAddresses); } } weekStartCol.set(weekStartCol.get() + (value - 1) + 1); }); if (relativeRowIndex == null || relativeRowIndex == 0) { return; } } } }