热门标签
热门文章
- 1C语言--编程将递增数列10、20、30、40、50、60、70、80、90、100保存到数组中，再从键盘输入一个整数，插入到该数列中，使之成为一个递增数列。_c语言递增数列
- 2【C++】设计类题目总结_c++毕业设计选题
- 3Python基础知识——字典：创建字典：七种方法_创建空字典
- 4Java自然语言处理工具包：实现文本处理和分析的工具_自然语言处理java插件
- 5Verilog学习之时序控制、语句块(1)
- 6CleanMyMac X2024最新免费激活码许可证号码
- 7【Android】【IDE】AndroidStudio3.5编译出现D8 Errors: Program type already present问题解决方案_d8: program type already present: layaair.game.bro
- 8在微信公众号中加入ChatGPT聊天的方法_chatgpt on wechat
- 9知道系统源码/知识问答系统源码/完整PC+手机端带功能强大后台管理系统_问答页源码
- 10CUDA_C_编程权指南 professional CUDA C++ programming chapter two CUDA编程模型 Programming Model_cuda c编程权威指南用的是什么软件
当前位置: article > 正文
java导出excel动态加载多sheet多复杂表头
作者：weixin_40725706 | 2024-05-15 14:17:40
赞
踩
java导出excel动态加载多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;
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
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;
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
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;
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
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;
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
实现类
@Override
public 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);
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
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));
}
}
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
- 250
- 251
- 252
- 253
- 254
- 255
- 256
- 257
- 258
- 259
- 260
- 261
- 262
- 263
- 264
- 265
- 266
- 267
- 268
- 269
- 270
- 271
- 272
- 273
- 274
- 275
- 276
- 277
- 278
- 279
- 280
- 281
- 282
- 283
- 284
- 285
- 286
- 287
- 288
- 289
- 290
- 291
- 292
- 293
- 294
- 295
- 296
- 297
- 298
- 299
- 300
- 301
- 302
- 303
- 304
- 305
- 306
- 307
- 308
- 309
- 310
- 311
- 312
- 313
- 314
- 315
- 316
- 317
- 318
- 319
- 320
- 321
- 322
- 323
- 324
- 325
- 326
- 327
- 328
- 329
- 330
- 331
- 332
- 333
- 334
- 335
- 336
- 337
- 338
- 339
- 340
- 341
- 342
- 343
- 344
- 345
- 346
- 347
- 348
- 349
- 350
- 351
- 352
- 353
- 354
- 355
- 356
- 357
- 358
- 359
- 360
- 361
- 362
- 363
- 364
- 365
- 366
- 367
- 368
- 369
- 370
- 371
- 372
- 373
- 374
- 375
- 376
- 377
- 378
- 379
- 380
- 381
- 382
- 383
- 384
- 385
- 386
- 387
- 388
- 389
- 390
- 391
- 392
- 393
- 394
- 395
- 396
- 397
- 398
- 399
- 400
- 401
- 402
- 403
- 404
- 405
- 406
- 407
- 408
- 409
- 410
- 411
- 412
- 413
- 414
- 415
- 416
- 417
- 418
- 419
- 420
- 421
- 422
- 423
- 424
- 425
- 426
- 427
- 428
- 429
- 430
- 431
- 432
- 433
- 434
- 435
- 436
- 437
- 438
- 439
- 440
- 441
- 442
- 443
- 444
- 445
- 446
- 447
- 448
- 449
- 450
- 451
- 452
- 453
- 454
- 455
- 456
- 457
- 458
- 459
- 460
- 461
- 462
- 463
- 464
- 465
- 466
- 467
- 468
- 469
- 470
- 471
- 472
- 473
- 474
- 475
- 476
- 477
- 478
- 479
- 480
- 481
- 482
- 483
- 484
- 485
- 486
- 487
- 488
- 489
- 490
- 491
- 492
- 493
- 494
- 495
- 496
- 497
- 498
- 499
- 500
- 501
- 502
- 503
- 504
- 505
- 506
- 507
- 508
- 509
- 510
- 511
- 512
- 513
- 514
- 515
- 516
- 517
业务工具方法
/**
* 类型
*/
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("网调", "省调", "长沙市", "湘潭市", "益阳市", "株洲市", "岳阳市", "常德市", "湘西州", "张家界市", "娄底市", "邵阳市", "怀化市", "衡阳市", "郴州市", "永州市");
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
实现效果
推荐阅读
相关标签
