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;
}

实现类


    @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);
        }
    }

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("网调", "省调", "长沙市", "湘潭市", "益阳市", "株洲市", "岳阳市", "常德市", "湘西州", "张家界市", "娄底市", "邵阳市", "怀化市", "衡阳市", "郴州市", "永州市");
    }

实现效果

在这里插入图片描述

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/626419.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

IPSSL证书：为特定IP地址通信数据保驾护航

IPSSL证书：为特定IP地址通信数据保驾护航

IPSSL证书&#xff0c;顾名思义&#xff0c;是专为特定IP地址设计的SSL证书。它不仅继承了传统SSL证书验证网站身份、加密数据传输的基本功能&#xff0c;还特别针对通过固定IP地址进行通信的场景提供了强化的安全保障。在IP地址直接绑定SSL证书的模式下&#xff0c;它能够确保…
阅读更多...
tomcat--目录结构和文件组成

tomcat--目录结构和文件组成

目录结构 目录说明bin服务启动&#xff0c;停止等相关程序和文件conf配置文件lib库目录logs日志记录webapps应用程序&#xff0c;应用部署目录workjsp编译后的结果文件&#xff0c;建议提前预热访问 /usr/local/apache-tomcat-8.5.100/work/Catalina/localhost/ROOT/org/apac…
阅读更多...
MySQL文档

MySQL文档

文档地址
阅读更多...
MyBatis-Plus核心功能详解：条件构造器、自定义SQL与Service接口

MyBatis-Plus核心功能详解：条件构造器、自定义SQL与Service接口

在Java的Web开发中&#xff0c;MyBatis-Plus作为MyBatis的增强工具&#xff0c;提供了许多实用的功能&#xff0c;极大地简化了数据库操作的开发过程。下面&#xff0c;我们将详细探讨MyBatis-Plus的三大核心功能&#xff1a;条件构造器、自定义SQL以及Service接口。 一、条件…
阅读更多...
【Vue】更换vue-element-admin左侧 logo

【Vue】更换vue-element-admin左侧 logo

准备&#xff1a;目标svg格式的 logo&#xff0c;并将目标logo命名为 vuejs-fill.svg替换路径&#xff1a;/icons 文件夹下&#xff0c;覆盖掉原本的 vuejs-fill.svg 原因&#xff1a;配置项的logo设置的是 vuejs-fill
阅读更多...
摊还分析

摊还分析

一、摊还分析 概念&#xff1a;是求数据结构中一个操作序列执行所有操作的平均时间&#xff0c;与平均复杂度不同&#xff0c;它不涉及输入概率&#xff0c;能够保证在最坏情况下操作的平均性能。 适用场景&#xff1a;用含 n 个操作的序列&#xff08;o1&#xff0c;o2&#x…
阅读更多...
【系统架构师】-选择题（十四）数据库基础

【系统架构师】-选择题（十四）数据库基础

1、某企业开发信息管理系统平台进行 E-R 图设计&#xff0c;人力部门定义的是员工实体具有属性&#xff1a;员工号、姓名、性别、出生日期、联系方式和部门,培训部门定义的培训师实体具有属性:培训师号&#xff0c;姓名和职称&#xff0c;其中职称{初级培训师&#xff0c;中级培…
阅读更多...
dnf手游攻略，新手入坑必备！

dnf手游攻略，新手入坑必备！

一、角色创建策略 在DNF手游中&#xff0c;角色创建是玩家初入游戏的首要步骤。为最大化游戏体验和收益&#xff0c;新手玩家通常建议创建三个角色&#xff1a;一个主账号和两个副账号。 主账号选择 主账号的选择应基于玩家个人的喜好和对职业的熟悉程度。无论选择哪个职业&a…
阅读更多...
Ubuntu 安装chrome和chromedriver

Ubuntu 安装chrome和chromedriver

1.安装包下载 百度网盘地址 2.更新软件包 sudo apt-get update 3.安装chrome sudo apt install ./google-chrome-stable_current_amd64.deb 4.检查是否安装成功 google-chrome --version 5.安装chrome-driver驱动&#xff0c;解压zip文件 unzip chromedriver_linux64.z…
阅读更多...
哪款骨传导耳机最值得入手？精选5款顶尖配置的骨传导耳机，闭眼入也不踩雷！

哪款骨传导耳机最值得入手？精选5款顶尖配置的骨传导耳机，闭眼入也不踩雷！

作为一名有着多年工作经验的数码博主&#xff0c;我见证了无数因盲目追求新颖而引发的听力问题。在此&#xff0c;我必须郑重提醒大家&#xff0c;虽然市面上充斥着众多声称能提供卓越音质和佩戴舒适度的骨传导耳机品牌&#xff0c;但它们之间存在大量劣质产品&#xff0c;这类…
阅读更多...
串联所有单词的子串 ---- 滑动窗口

串联所有单词的子串 ---- 滑动窗口

题目链接 题目: 分析: 我们上次做的题目, 是找到所有字符的异位词, 和这道题有些类似, 使用记录有效字符的个数找到子字符, 此题无非是把字符变成了字符串题目回顾 有一下几方面不同, 我们以示例1为例: 1. 哈希表 上次我们使用的是哈希数组, 因为数组的下标可以是字符, 现…
阅读更多...
生产透明化，交付无烦恼

生产透明化，交付无烦恼

生产进度总延误 质量把控总失守 计划赶不上变化 沟通不畅易误解 ...... 这些问题可能在一些工厂管理中几乎每天都在上演。 在如今快速变化的市场环境中&#xff0c;企业的生产效率和交付能力成为了衡量其竞争力的关键指标。而要实现高效、准确的生产和交付&#xff0c;透明化的…
阅读更多...
JVM调优-调优原则和原理分析

JVM调优-调优原则和原理分析

1.写在前面 对于JVM调优这个话题&#xff0c;可能大部分程序员都听过这个名词。 但是绝大多数程序员&#xff0c;都没有真真实实去干过&#xff0c;都没有真实的实践过。也不懂得如何调优&#xff1f;不知道要调成怎么样&#xff1f; 那今天咋们就对这个话题来展开描述一下&…
阅读更多...
“Linux”目录结构and配置网络

“Linux”目录结构and配置网络

了解完命令格式和vi、vim编辑器后&#xff0c;我们来认识一下目录的结构&#xff1a; 一、目录 &#xff08;1&#xff09;目录的特点 windows特点&#xff1a; Windows中有C、D、E盘&#xff0c;每个都是一个根系统 Linux特点&#xff1a; linux中只有一个根&#xff08;单…
阅读更多...
富在术数，不在劳身 财富的积累更多依赖于智慧和策略，而不是单纯的体力劳动 GPT-4o免费用

富在术数，不在劳身 财富的积累更多依赖于智慧和策略，而不是单纯的体力劳动 GPT-4o免费用

"富在术数&#xff0c;不在劳身"这句话的意思是财富的积累更多依赖于智慧和策略&#xff0c;而不是单纯的体力劳动。这句话强调了智慧和技巧在获取财富过程中的重要性&#xff0c;提示人们在追求财富时&#xff0c;应注重策略和方法的运用&#xff0c;而不仅仅依靠辛…
阅读更多...
【正点原子Linux连载】第四十一章 Linux wifi驱动实验 摘自【正点原子】ATK-DLRK3568嵌入式Linux驱动开发指南

【正点原子Linux连载】第四十一章 Linux wifi驱动实验 摘自【正点原子】ATK-DLRK3568嵌入式Linux驱动开发指南

1&#xff09;实验平台&#xff1a;正点原子ATK-DLRK3568开发板 2&#xff09;平台购买地址&#xff1a;https://detail.tmall.com/item.htm?id731866264428 3&#xff09;全套实验源码手册视频下载地址&#xff1a; http://www.openedv.com/docs/boards/xiaoxitongban 第四十…
阅读更多...
服务的war包已经丢在tomcat中但是还是没法访问，如何排查？

服务的war包已经丢在tomcat中但是还是没法访问，如何排查？

问题出现的现象是我已经将 XWiki 的 WAR 包放置在 Tomcat 的 webapps目录下但仍然无法访问&#xff0c;反思之后可以从下面以下几个方面来诊断和解决问题&#xff1a; 1. 确认 Tomcat 正在运行 首先&#xff0c;确保 Tomcat 服务正在正常运行。可以使用以下命令检查 Tomcat 的…
阅读更多...
嵌入式科普(16)c语言函数参数的传递方式

嵌入式科普(16)c语言函数参数的传递方式

目录 一、概述 二、C函数参数 2.1 一张图讲清 2.2 按数据类型分类&#xff1a; 2.2.1 基本数据类型参数&#xff1a; 2.2.2 数组参数&#xff1a; 2.2.3 结构体参数&#xff1a; 2.2.4 指针参数&#xff1a; 2.2.5 函数指针参数&#xff1a; 2.3 按传递方式分类&…
阅读更多...
nestjs封装一个响应体

nestjs封装一个响应体

封装一个DTO // response.dto.tsimport {CallHandler,ExecutionContext,Injectable,NestInterceptor, } from "nestjs/common"; import { FastifyReply } from "fastify"; import { Observable } from "rxjs"; import { map } from "rxjs/…
阅读更多...
电力系统潮流计算的计算机算法（一）——网络方程、功率方程和节点分类

电力系统潮流计算的计算机算法（一）——网络方程、功率方程和节点分类

本篇为本科课程《电力系统稳态分析》的笔记。 本篇为这一章的第一篇笔记。下一篇传送门。 实际中的大规模电力系统包含成百上千个节点、发电机组和负荷&#xff0c;网络是复杂的&#xff0c;需要建立复杂电力系统的同一潮流数学模型&#xff0c;借助计算机进行求解。 简介 …
阅读更多...
最新文章

Copyright @ 2022~2023