java导出excel动态加载多sheet多复杂表头

2024-05-15 02:12
文章标签 java 动态 加载 excel 表头 导出 复杂 sheet

本文主要是介绍java导出excel动态加载多sheet多复杂表头，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

java导出excel动态加载多sheet多复杂表头

  • 实体
  • 实现类
  • sheet方法
  • 业务工具方法
  • 实现效果

实体

import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import lombok.ToString;
import lombok.experimental.Accessors;import java.io.Serializable;
import java.sql.Date;@Data
@Accessors(chain = true)
public class CurrentPlanCityTunePowerVo implements Serializable {private static final long serialVersionUID = 1L;private String dateTime;private Double output;private String id;private String areaName;private String type;
}

import lombok.Data;
import lombok.experimental.Accessors;import java.io.Serializable;
import java.util.List;
import java.util.Map;@Data
@Accessors(chain = true)
public class DayPlanArchiveHeadVo implements Serializable {private static final long serialVersionUID = 1L;private Map<String, String> rqjhMenuMap;private Map<String, List<String>> head1Map;private Map<String, List<String>> waterHead1Map;}


import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.experimental.Accessors;import java.io.Serializable;
import java.util.Date;
import java.util.List;@Data
@Accessors(chain = true)
public class DayPlanArchiveResultVo implements Serializable {private static final long serialVersionUID = 1L;private List<DayPlanArchiveVo> dayPlanArchiveVoList;private List<CurrentPlanCityTunePowerVo> currentPlanCityTunePowerVoList;}

import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.experimental.Accessors;
import java.io.Serializable;
import java.util.Date;@Data
@Accessors(chain = true)
public class DayPlanArchiveVo implements Serializable {private static final long serialVersionUID = 1L;private Date dateTime;private Double output;private String stationId;private String shortName;private String schedulingLevel;private String region;private String stationType;private String category;private String vol;private String tunePower;
}

实现类

@Overridepublic void export(String date, String txtName, HttpServletResponse response) {/** 第一步，创建一个Workbook，对应一个Excel文件  */XSSFWorkbook wb = new XSSFWorkbook();try {//获取文件内容DayPlanArchiveResultVo dayPlanArchiveResultVo = this.listDayPlanArchive(new DayPlanArchiveDto().setTxtName(txtName));List<DayPlanArchiveVo> dayPlanArchiveVoList = dayPlanArchiveResultVo.getDayPlanArchiveVoList();double allTypeSum = Math.floor(dayPlanArchiveVoList.stream().mapToDouble(DayPlanArchiveVo::getOutput).sum() / 4);Map<String, Double> allTypeByTimeSumMap = dayPlanArchiveVoList.stream().collect(Collectors.groupingBy(vo -> SDF.format(vo.getDateTime()), TreeMap::new, Collectors.summingDouble(DayPlanArchiveVo::getOutput)));List<String> dateList = new ArrayList<>(allTypeByTimeSumMap.keySet());typeList().forEach(typeStr -> {/** 第二步，在Workbook中添加sheet,对应Excel文件中的sheet  */XSSFSheet sheet = wb.createSheet(typeStr);//往sheet录入数据if ("测试数据".equals(typeStr)) {addHyPowerSheet(typeStr, date, sheet, (List<DayPlanArchiveVo>) SerializationUtils.clone((Serializable) dayPlanArchiveVoList), allTypeSum, allTypeByTimeSumMap, dateList);} else if ("测试数据".equals(typeStr)) {addTunePowerSheet(typeStr, date, wb, sheet, dayPlanArchiveResultVo.getCurrentPlanCityTunePowerVoList(), allTypeSum, allTypeByTimeSumMap, dateList);} else {addSheet(typeStr, date, sheet, (List<DayPlanArchiveVo>) SerializationUtils.clone((Serializable) dayPlanArchiveVoList), allTypeSum, allTypeByTimeSumMap, dateList);}//设置样式居中XSSFCellStyle cellStyle = wb.createCellStyle();cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);for (int i = 0; i < sheet.getLastRowNum(); i++) {XSSFRow row = sheet.getRow(i);if (row != null) {for (int j = 0; j < row.getLastCellNum(); j++) {if (row.getCell(j) != null) {row.getCell(j).setCellStyle(cellStyle);}}}}});String fileName = txtName.substring(0, txtName.indexOf(".")) + ".xlsx";response.setContentType("application/octet-stream");// 可自行定义编码格式response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));//清除jsp编译html文件的空白,防止excel出现空行response.flushBuffer();OutputStream stream = response.getOutputStream();if (null != stream) {//写出wb.write(stream);wb.close();stream.close();}} catch (Exception e) {e.printStackTrace();} finally {IOUtils.closeQuietly(wb);}}

sheet方法

/*** sheet录入数据*/private void addSheet(String type, String date, XSSFSheet sheet, List<DayPlanArchiveVo> dayPlanArchiveVoList, double allTypeSum, Map<String, Double> allTypeByTimeSumMap, List<String> dateList) {//过滤出类型数据String typeName = type;if ("光伏".equals(typeName)) {typeName = "太阳能";}String finalTypeName = typeName;List<DayPlanArchiveVo> hyPowerList = dayPlanArchiveVoList.stream().filter(vo -> finalTypeName.equals(vo.getCategory())).collect(Collectors.toList());Map<String, String> rqjhMenuMap = rqjhMenu(type, date).getRqjhMenuMap();hyPowerList.forEach(vo -> rqjhMenuMap.entrySet().stream().filter(entry -> entry.getKey().equals(vo.getStationId())).findFirst().ifPresent(entry -> vo.setShortName(entry.getValue())));Map<String, Double> typeSumMap = hyPowerList.stream().collect(Collectors.groupingBy(vo -> SDF.format(vo.getDateTime()), TreeMap::new, Collectors.summingDouble(DayPlanArchiveVo::getOutput)));//集合转变成Map方便读取数据Map<String, Double> hyPowerMap = hyPowerList.stream().collect(Collectors.toMap(vo -> SDF.format(vo.getDateTime()) + vo.getShortName(), DayPlanArchiveVo::getOutput));Map<String, List<String>> head1Map = rqjhMenu(type, date).getHead1Map();List<String> sortRegionList = head1Map.keySet().stream().sorted(Comparator.comparingInt(sortList()::indexOf)).collect(Collectors.toList());//表头第一行List<String> header1StrList = new ArrayList<>();header1StrList.add("时间");header1StrList.add("全网");header1StrList.add(type);//表头第二行List<String> header2StrList = new ArrayList<>();header2StrList.add("");header2StrList.add("测试数据");header2StrList.add("测试数据");for (String s : sortRegionList) {header1StrList.add(s);List<String> stringList = head1Map.get(s);for (int i = 0; i < stringList.size(); i++) {header2StrList.add(stringList.get(i));header1StrList.add("");}//为地市合计header2StrList.add(s + "合计");}System.out.println("第一行表头赋值" + header1StrList);System.out.println("第二行表头赋值" + header2StrList);/** 第四步，创建标题 ,合并标题单元格 */// 行号int rowNum = 0;// 创建第一页的第一行，索引从0开始XSSFRow row0 = sheet.createRow(rowNum++);row0.setHeight((short) 600);// 设置行高//往第一行表头录入数据并合并单元格for (int i = 0; i < header1StrList.size(); i++) {XSSFCell c00 = row0.createCell(i);c00.setCellValue(header1StrList.get(i));//设置第一行表头样式//c00.setCellStyle(headerStyle);}// 合并单元格，参数依次为起始行，结束行,起始列，结束列（索引0开始）sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));int startCol = 3;int endCol;for (String s : sortRegionList) {List<String> stringList = head1Map.get(s);//标题合并单元格操作endCol = startCol + stringList.size();sheet.addMergedRegion(new CellRangeAddress(0, 0, startCol, endCol));System.out.println("第一行表头索引" + startCol + ":" + endCol);startCol = endCol + 1;}//第二行XSSFRow row2 = sheet.createRow(rowNum++);row2.setHeight((short) 700);for (int i = 0; i < header2StrList.size(); i++) {XSSFCell tempCell = row2.createCell(i);tempCell.setCellValue(header2StrList.get(i));if (i > 0) {sheet.setColumnWidth(i, header2StrList.get(i).getBytes(StandardCharsets.UTF_8).length * 256);}}Map<String, Double> statisticsMap = hyPowerList.stream().collect(Collectors.groupingBy(DayPlanArchiveVo::getShortName, Collectors.summingDouble(DayPlanArchiveVo::getOutput)));statisticsMap.replaceAll((key, value) -> Math.floor(value / 4));double currentTypeSum = Math.floor(hyPowerList.stream().mapToDouble(DayPlanArchiveVo::getOutput).sum() / 4);List<String> header3StrList = new ArrayList<>();header3StrList.add("合计");header3StrList.add(String.valueOf(allTypeSum));header3StrList.add(String.valueOf(currentTypeSum));for (String s : sortRegionList) {Double cityTotal = 0.0;List<String> stringList = head1Map.get(s);for (int i = 0; i < stringList.size(); i++) {Double stationTotal = statisticsMap.get(stringList.get(i));if (stationTotal == null) {header3StrList.add("");cityTotal += 0.0;} else {header3StrList.add(String.valueOf(stationTotal));cityTotal += stationTotal;}}//为地市合计统计header3StrList.add(String.valueOf(cityTotal));}//第三行XSSFRow row3 = sheet.createRow(rowNum++);row3.setHeight((short) 700);for (int i = 0; i < header3StrList.size(); i++) {XSSFCell tempCell = row3.createCell(i);if (i > 0) {if (StringUtils.isNotEmpty(header3StrList.get(i))) {tempCell.setCellValue(Double.parseDouble(header3StrList.get(i)));} else {tempCell.setCellValue(header3StrList.get(i));}} else {tempCell.setCellValue(header3StrList.get(i));}}//业务数据List<List<String>> itemList = new ArrayList<>();dateList.forEach(dateStr -> {List<String> voList = new ArrayList<>();voList.add(dateStr);voList.add(String.valueOf(allTypeByTimeSumMap.get(dateStr)));voList.add(String.valueOf(typeSumMap.get(dateStr) == null ? "0" : typeSumMap.get(dateStr)));for (String key : sortRegionList) {Double totalByTime = 0.0;List<String> head2List = head1Map.get(key);for (String shortName : head2List) {Double output = hyPowerMap.get(dateStr + shortName);if (output != null) {Double aDouble = Math.floor(hyPowerMap.get(dateStr + shortName));totalByTime += aDouble;voList.add(String.valueOf(aDouble));} else {voList.add("");}//System.out.println(shortName + aDouble);}voList.add(String.valueOf(totalByTime));//System.out.println(key + "合计" + totalByTime);}itemList.add(voList);});for (List<String> stringList : itemList) {//业务数据录入XSSFRow row = sheet.createRow(rowNum++);row3.setHeight((short) 700);for (int i = 0; i < stringList.size(); i++) {XSSFCell tempCell = row.createCell(i);if (i > 0) {if (StringUtils.isEmpty(stringList.get(i))) {tempCell.setCellValue(stringList.get(i));} else {tempCell.setCellValue(Double.parseDouble(stringList.get(i)));}} else {tempCell.setCellValue(stringList.get(i));}}}}/*** sheet录入数据*/private void addHyPowerSheet(String type, String date, XSSFSheet sheet, List<DayPlanArchiveVo> dayPlanArchiveVoList, double allTypeSum, Map<String, Double> allTypeByTimeSumMap, List<String> dateList) {List<DayPlanArchiveVo> hyPowerList = dayPlanArchiveVoList.stream().filter(vo -> type.equals(vo.getCategory())).collect(Collectors.toList());Map<String, String> rqjhMenuMap = rqjhMenu(type, date).getRqjhMenuMap();hyPowerList.forEach(vo -> rqjhMenuMap.entrySet().stream().filter(entry -> entry.getKey().equals(vo.getStationId())).findFirst().ifPresent(entry -> vo.setShortName(entry.getValue())));Map<String, Double> typeSumMap = hyPowerList.stream().collect(Collectors.groupingBy(vo -> SDF.format(vo.getDateTime()), TreeMap::new, Collectors.summingDouble(DayPlanArchiveVo::getOutput)));//集合转变成Map方便读取数据Map<String, Double> hyPowerMap = hyPowerList.stream().collect(Collectors.toMap(vo -> SDF.format(vo.getDateTime()) + vo.getShortName(), DayPlanArchiveVo::getOutput));Map<String, List<String>> head1Map = new HashMap<>(rqjhMenu(type, date).getWaterHead1Map());List<String> sortRegionList = head1Map.keySet().stream().sorted(Comparator.comparingInt(sortList()::indexOf)).collect(Collectors.toList());//表头第一行List<String> header1StrList = new ArrayList<>();header1StrList.add("时间");header1StrList.add("全网");header1StrList.add(type);//表头第二行List<String> header2StrList = new ArrayList<>();header2StrList.add("");header2StrList.add("测试数据");header2StrList.add("测试数据");for (String s : sortRegionList) {header1StrList.add(s);List<String> stringList = head1Map.get(s);for (int i = 0; i < stringList.size(); i++) {header2StrList.add(stringList.get(i));header1StrList.add("");}//为地市合计header2StrList.add(s + "合计");}System.out.println("第一行表头赋值" + header1StrList);System.out.println("第二行表头赋值" + header2StrList);/** 创建标题 ,合并标题单元格 */// 行号int rowNum = 0;// 创建第一页的第一行，索引从0开始XSSFRow row0 = sheet.createRow(rowNum++);row0.setHeight((short) 600);// 设置行高//往第一行表头录入数据并合并单元格for (int i = 0; i < header1StrList.size(); i++) {XSSFCell c00 = row0.createCell(i);c00.setCellValue(header1StrList.get(i));//设置第一行表头样式//c00.setCellStyle(headerStyle);}// 合并单元格，参数依次为起始行，结束行,起始列，结束列（索引0开始）sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));int startCol = 3;int endCol;for (String s : sortRegionList) {List<String> stringList = head1Map.get(s);//标题合并单元格操作endCol = startCol + stringList.size();sheet.addMergedRegion(new CellRangeAddress(0, 0, startCol, endCol));System.out.println("第一行表头索引" + startCol + ":" + endCol);startCol = endCol + 1;}//第二行XSSFRow row2 = sheet.createRow(rowNum++);row2.setHeight((short) 700);for (int i = 0; i < header2StrList.size(); i++) {XSSFCell tempCell = row2.createCell(i);tempCell.setCellValue(header2StrList.get(i));if (i > 0) {sheet.setColumnWidth(i, header2StrList.get(i).getBytes(StandardCharsets.UTF_8).length * 256);}}Map<String, Double> statisticsMap = hyPowerList.stream().collect(Collectors.groupingBy(DayPlanArchiveVo::getShortName, Collectors.summingDouble(DayPlanArchiveVo::getOutput)));statisticsMap.replaceAll((key, value) -> Math.floor(value / 4));double currentTypeSum = Math.floor(hyPowerList.stream().mapToDouble(DayPlanArchiveVo::getOutput).sum() / 4);List<String> header3StrList = new ArrayList<>();header3StrList.add("合计");header3StrList.add(String.valueOf(allTypeSum));header3StrList.add(String.valueOf(currentTypeSum));for (String s : sortRegionList) {Double cityTotal = 0.0;List<String> stringList = head1Map.get(s);for (int i = 0; i < stringList.size(); i++) {Double stationTotal = statisticsMap.get(stringList.get(i));if (stationTotal == null) {header3StrList.add("");cityTotal += 0.0;} else {header3StrList.add(String.valueOf(stationTotal));cityTotal += stationTotal;}}header3StrList.add(String.valueOf(cityTotal));}//第三行XSSFRow row3 = sheet.createRow(rowNum++);row3.setHeight((short) 700);for (int i = 0; i < header3StrList.size(); i++) {XSSFCell tempCell = row3.createCell(i);if (i > 0) {if (StringUtils.isNotEmpty(header3StrList.get(i))) {tempCell.setCellValue(Double.parseDouble(header3StrList.get(i)));} else {tempCell.setCellValue(header3StrList.get(i));}} else {tempCell.setCellValue(header3StrList.get(i));}}//业务数据List<List<String>> itemList = new ArrayList<>();dateList.forEach(dateStr -> {List<String> voList = new ArrayList<>();voList.add(dateStr);voList.add(String.valueOf(allTypeByTimeSumMap.get(dateStr)));voList.add(String.valueOf(typeSumMap.get(dateStr)));for (String key : sortRegionList) {Double totalByTime = 0.0;List<String> head2List = head1Map.get(key);for (String shortName : head2List) {Double output = hyPowerMap.get(dateStr + shortName);if (output != null) {Double aDouble = Math.floor(hyPowerMap.get(dateStr + shortName));totalByTime += aDouble;voList.add(String.valueOf(aDouble));} else {voList.add("");}//System.out.println(shortName + aDouble);}voList.add(String.valueOf(totalByTime));//System.out.println(key + "合计" + totalByTime);}itemList.add(voList);});for (List<String> stringList : itemList) {//业务数据录入XSSFRow row = sheet.createRow(rowNum++);row3.setHeight((short) 700);for (int i = 0; i < stringList.size(); i++) {XSSFCell tempCell = row.createCell(i);if (i > 0) {if (StringUtils.isEmpty(stringList.get(i))) {tempCell.setCellValue(stringList.get(i));} else {tempCell.setCellValue(Double.parseDouble(stringList.get(i)));}} else {tempCell.setCellValue(stringList.get(i));}}}}/*** sheet录入数据*/private void addTunePowerSheet(String type, String date, XSSFWorkbook wb, XSSFSheet sheet, List<CurrentPlanCityTunePowerVo> dayPlanArchiveVoList, double allTypeSum, Map<String, Double> allTypeByTimeSumMap, List<String> dateList) {List<CurrentPlanCityTunePowerVo> currentPlanCityTunePowerVoList = (List<CurrentPlanCityTunePowerVo>) SerializationUtils.clone((Serializable) dayPlanArchiveVoList);//过滤空List<CurrentPlanCityTunePowerVo> filterList = currentPlanCityTunePowerVoList.stream().filter(vo -> vo.getOutput() != null).collect(Collectors.toList());//集合转变成Map方便读取数据Map<String, Double> tunePowerMap = filterList.stream().collect(Collectors.toMap(vo -> vo.getDateTime() + vo.getAreaName() + vo.getType(), CurrentPlanCityTunePowerVo::getOutput));Map<String, List<String>> head1Map = dayPlanArchiveVoList.stream().filter(vo -> vo.getAreaName() != null).collect(Collectors.groupingBy(CurrentPlanCityTunePowerVo::getAreaName,Collectors.mapping(CurrentPlanCityTunePowerVo::getType, Collectors.collectingAndThen(Collectors.toList(), values -> values.stream().distinct().collect(Collectors.toList())))));List<String> sortRegionList = head1Map.keySet().stream().sorted(Comparator.comparingInt(sortList()::indexOf)).collect(Collectors.toList());//表头第一行List<String> header1StrList = new ArrayList<>();header1StrList.add("时间");header1StrList.add("全网");header1StrList.add(type);header1StrList.add("测试数据");header1StrList.add("测试数据");header1StrList.add("测试数据");//表头第二行List<String> header2StrList = new ArrayList<>();header2StrList.add("");header2StrList.add("测试数据");header2StrList.add("测试数据");header2StrList.add("测试数据");header2StrList.add("测试数据");header2StrList.add("测试数据");for (String s : sortRegionList) {header1StrList.add(s);List<String> stringList = head1Map.get(s);for (int i = 0; i < stringList.size(); i++) {header2StrList.add(s.substring(0, s.length() - 1) + stringList.get(i));header1StrList.add("");}//为地市合计header2StrList.add(s + "合计");}System.out.println("第一行表头赋值" + header1StrList);System.out.println("第二行表头赋值" + header2StrList);/** 创建标题 ,合并标题单元格 */// 行号int rowNum = 0;// 创建第一页的第一行，索引从0开始XSSFRow row0 = sheet.createRow(rowNum++);row0.setHeight((short) 600);// 设置行高//往第一行表头录入数据并合并单元格for (int i = 0; i < header1StrList.size(); i++) {XSSFCell c00 = row0.createCell(i);c00.setCellValue(header1StrList.get(i));}// 合并单元格，参数依次为起始行，结束行,起始列，结束列（索引0开始）sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));int startCol = 6;int endCol;for (String s : sortRegionList) {List<String> stringList = head1Map.get(s);//标题合并单元格操作endCol = startCol + stringList.size();sheet.addMergedRegion(new CellRangeAddress(0, 0, startCol, endCol));System.out.println("第一行表头索引" + startCol + ":" + endCol);startCol = endCol + 1;}//第二行XSSFRow row2 = sheet.createRow(rowNum++);row2.setHeight((short) 700);for (int i = 0; i < header2StrList.size(); i++) {XSSFCell tempCell = row2.createCell(i);tempCell.setCellValue(header2StrList.get(i));if (i > 0) {sheet.setColumnWidth(i, header2StrList.get(i).getBytes(StandardCharsets.UTF_8).length * 256);}}Map<String, Map<String, Double>> cityTypeMap = filterList.stream().filter(vo -> vo.getAreaName() != null).collect(Collectors.groupingBy(CurrentPlanCityTunePowerVo::getAreaName, TreeMap::new, Collectors.groupingBy(CurrentPlanCityTunePowerVo::getType, Collectors.summingDouble(CurrentPlanCityTunePowerVo::getOutput))));List<String> header3StrList = new ArrayList<>();header3StrList.add("合计");header3StrList.add(String.valueOf(allTypeSum));double currentTypeSum = Math.floor(filterList.stream().mapToDouble(CurrentPlanCityTunePowerVo::getOutput).sum() / 4);header3StrList.add(String.valueOf(currentTypeSum));Map<String, Double> perfectrueMap = filterList.stream().filter(vo -> vo.getAreaName() == null).collect(Collectors.groupingBy(CurrentPlanCityTunePowerVo::getType, Collectors.summingDouble(CurrentPlanCityTunePowerVo::getOutput)));header3StrList.add(String.valueOf(perfectrueMap.get("测试数据")));header3StrList.add(String.valueOf(perfectrueMap.get("测试数据")));header3StrList.add(String.valueOf(perfectrueMap.get("测试数据")));for (String s : sortRegionList) {Double cityTotal = 0.0;List<String> stringList = head1Map.get(s);for (int i = 0; i < stringList.size(); i++) {Double stationTotal = cityTypeMap.get(s).get(stringList.get(i));if (stationTotal == null) {header3StrList.add("0");cityTotal += 0.0;} else {header3StrList.add(String.valueOf(stationTotal));cityTotal += stationTotal;}}//地市(网调省调)合计header3StrList.add(String.valueOf(cityTotal));}//第三行XSSFRow row3 = sheet.createRow(rowNum++);row3.setHeight((short) 700);for (int i = 0; i < header3StrList.size(); i++) {XSSFCell tempCell = row3.createCell(i);if (i > 0) {if (StringUtils.isNotEmpty(header3StrList.get(i))) {tempCell.setCellValue(Double.parseDouble(header3StrList.get(i)));} else {tempCell.setCellValue(header3StrList.get(i));}} else {tempCell.setCellValue(header3StrList.get(i));}}//业务数据Map<String, Double> typeSumMap = filterList.stream().filter(vo -> vo.getAreaName() != null).collect(Collectors.groupingBy(CurrentPlanCityTunePowerVo::getDateTime, TreeMap::new, Collectors.summingDouble(CurrentPlanCityTunePowerVo::getOutput)));Map<String, Map<String, Double>> tunePreFectureMap = filterList.stream().filter(vo -> vo.getAreaName() == null).collect(Collectors.groupingBy(CurrentPlanCityTunePowerVo::getDateTime, TreeMap::new, Collectors.groupingBy(CurrentPlanCityTunePowerVo::getType, Collectors.summingDouble(CurrentPlanCityTunePowerVo::getOutput))));List<List<String>> itemList = new ArrayList<>();dateList.forEach(dateStr -> {List<String> voList = new ArrayList<>();voList.add(dateStr);voList.add(String.valueOf(allTypeByTimeSumMap.get(dateStr)));voList.add(String.valueOf(typeSumMap.get(dateStr)));voList.add(String.valueOf(tunePreFectureMap.get(dateStr).get("测试数据")));voList.add(String.valueOf(tunePreFectureMap.get(dateStr).get("测试数据")));voList.add(String.valueOf(tunePreFectureMap.get(dateStr).get("测试数据")));for (String key : sortRegionList) {Double totalByTime = 0.0;List<String> head2List = head1Map.get(key);for (String shortName : head2List) {Double output = tunePowerMap.get(dateStr + key + shortName);if (output != null) {Double aDouble = Math.floor(tunePowerMap.get(dateStr + key + shortName));totalByTime += aDouble;voList.add(String.valueOf(aDouble));} else {voList.add("");}}voList.add(String.valueOf(totalByTime));}itemList.add(voList);});for (List<String> stringList : itemList) {//业务数据录入XSSFRow row = sheet.createRow(rowNum++);row3.setHeight((short) 700);for (int i = 0; i < stringList.size(); i++) {XSSFCell tempCell = row.createCell(i);if (i > 0) {if (StringUtils.isEmpty(stringList.get(i))) {tempCell.setCellValue(stringList.get(i));} else {tempCell.setCellValue(Double.parseDouble(stringList.get(i)));}} else {tempCell.setCellValue(stringList.get(i));}}}}

业务工具方法

/*** 类型*/private List<String> typeList() {return Arrays.asList("水电", "风电", "光伏", "储能", "小火电", "地调发电");}/*** 获取电站名称集合*/private DayPlanArchiveHeadVo rqjhMenu(String type, String date) {DayPlanArchiveHeadVo dayPlanArchiveHeadVo = new DayPlanArchiveHeadVo();List<PowerStationInfo> powerStationInfoList = powerStationWhService.rqjhMenu(jsonObject);Map<String, List<String>> head1Map = powerStationInfoList.stream().collect(Collectors.groupingBy(PowerStationInfo::getDispatchingAgency,Collectors.mapping(PowerStationInfo::getName, Collectors.collectingAndThen(Collectors.toList(), values -> values.stream().distinct().collect(Collectors.toList())))));Map<String, String> rqjhMenuMap = powerStationInfoList.stream().collect(Collectors.toMap(PowerStationInfo::getId, PowerStationInfo::getName));dayPlanArchiveHeadVo.setHead1Map(head1Map);dayPlanArchiveHeadVo.setRqjhMenuMap(rqjhMenuMap);if ("水电".equals(type)) {Map<String, List<String>> waterHead1Map = new HashMap<>();waterHead1Map.putAll(powerStationInfoList.stream().collect(Collectors.groupingBy(PowerStationInfo::getDispatchingAgency,Collectors.mapping(PowerStationInfo::getName, Collectors.collectingAndThen(Collectors.toList(), values -> values.stream().distinct().collect(Collectors.toList()))))));dayPlanArchiveHeadVo.setWaterHead1Map(waterHead1Map);}return dayPlanArchiveHeadVo;}/*** 按照指定顺序排序*/private List<String> sortList() {return Arrays.asList("网调", "省调", "长沙市", "湘潭市", "益阳市", "株洲市", "岳阳市", "常德市", "湘西州", "张家界市", "娄底市", "邵阳市", "怀化市", "衡阳市", "郴州市", "永州市");}

实现效果

在这里插入图片描述

这篇关于java导出excel动态加载多sheet多复杂表头的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/990510。

相关文章

SpringBoot中HTTP连接池的配置与优化

SpringBoot中HTTP连接池的配置与优化

《SpringBoot中HTTP连接池的配置与优化》这篇文章主要为大家详细介绍了SpringBoot中HTTP连接池的配置与优化的相关知识,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一... 目录一、HTTP连接池的核心价值二、Spring Boot集成方案方案1：Apache HttpCl
阅读更多...
Spring Boot项目打包和运行的操作方法

Spring Boot项目打包和运行的操作方法

《SpringBoot项目打包和运行的操作方法》SpringBoot应用内嵌了Web服务器,所以基于SpringBoot开发的web应用也可以独立运行,无须部署到其他Web服务器中,下面以打包dem... 目录一、打包为JAR包并运行1.打包为可执行的 JAR 包2.运行 JAR 包二、打包为WAR包并运行
阅读更多...
Java进行日期解析与格式化的实现代码

Java进行日期解析与格式化的实现代码

《Java进行日期解析与格式化的实现代码》使用Java搭配ApacheCommonsLang3和Natty库,可以实现灵活高效的日期解析与格式化,本文将通过相关示例为大家讲讲具体的实践操作,需要的可以... 目录一、背景二、依赖介绍1. Apache Commons Lang32. Natty三、核心实现代
阅读更多...
Spring Boot 常用注解整理(最全收藏版)

Spring Boot 常用注解整理(最全收藏版)

《SpringBoot常用注解整理(最全收藏版)》本文系统整理了常用的Spring/SpringBoot注解,按照功能分类进行介绍,每个注解都会涵盖其含义、提供来源、应用场景以及代码示例,帮助开发... 目录Spring & Spring Boot 常用注解整理一、Spring Boot 核心注解二、Spr
阅读更多...
SpringBoot实现接口数据加解密的三种实战方案

SpringBoot实现接口数据加解密的三种实战方案

《SpringBoot实现接口数据加解密的三种实战方案》在金融支付、用户隐私信息传输等场景中,接口数据若以明文传输,极易被中间人攻击窃取,SpringBoot提供了多种优雅的加解密实现方案,本文将从原... 目录一、为什么需要接口数据加解密？二、核心加解密算法选择1. 对称加密（AES）2. 非对称加密（R
阅读更多...
详解如何在SpringBoot控制器中处理用户数据

详解如何在SpringBoot控制器中处理用户数据

《详解如何在SpringBoot控制器中处理用户数据》在SpringBoot应用开发中,控制器（Controller）扮演着至关重要的角色,它负责接收用户请求、处理数据并返回响应,本文将深入浅出地讲解... 目录一、获取请求参数1.1 获取查询参数1.2 获取路径参数二、处理表单提交2.1 处理表单数据三、
阅读更多...
java变量内存中存储的使用方式

java变量内存中存储的使用方式

《java变量内存中存储的使用方式》：本文主要介绍java变量内存中存储的使用方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录1、介绍2、变量的定义3、 变量的类型4、 变量的作用域5、 内存中的存储方式总结1、介绍在 Java 中，变量是用于存储程序中数据
阅读更多...
如何合理管控Java语言的异常

如何合理管控Java语言的异常

《如何合理管控Java语言的异常》：本文主要介绍如何合理管控Java语言的异常问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录1、介绍2、Thorwable类3、Error4、Exception类4.1、检查异常4.2、运行时异常5、处理方式5.1. 捕获异常
阅读更多...
Spring Boot集成SLF4j从基础到高级实践(最新推荐)

Spring Boot集成SLF4j从基础到高级实践(最新推荐)

《SpringBoot集成SLF4j从基础到高级实践(最新推荐)》SLF4j(SimpleLoggingFacadeforJava)是一个日志门面（Facade）,不是具体的日志实现,这篇文章主要介... 目录一、日志框架概述与SLF4j简介1.1 为什么需要日志框架1.2 主流日志框架对比1.3 SLF4
阅读更多...
Spring Boot集成Logback终极指南之从基础到高级配置实战指南

Spring Boot集成Logback终极指南之从基础到高级配置实战指南

《SpringBoot集成Logback终极指南之从基础到高级配置实战指南》Logback是一个可靠、通用且快速的Java日志框架,作为Log4j的继承者,由Log4j创始人设计,：本文主要介绍... 目录一、Logback简介与Spring Boot集成基础1.1 Logback是什么？1.2 Sprin
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2