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

最新推荐文章于 2024-05-25 12:01:51 发布
最新推荐文章于 2024-05-25 12:01:51 发布
阅读量264 收藏 3
点赞数 11
文章标签： java excel
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43931108/article/details/138858970
版权

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

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

实现效果

在这里插入图片描述

开森鸭
关注
  • 11
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Java实现Excel多表头动态数据导出
xiantanglingfeng的博客
07-23 1万+
好久没时间写帖子了，由于工作需要，写一个基于JAVA实现的Excel多表头动态导出功能，首先可能这个叫法比较啰嗦，下面我们先看看什么是Excel多表头动态导出（效果图）： 它包含两部分：1、是表头，就像大家看到的样子它的表头是多行合并的（多表头）；2.内容部分，就是下方的数据数据内容。 两个内容都是有后台人员分别由两个接口返回的（也可以一个，但一个会显得格式冗余），因此他的表头数据是不固定的，可能有甲方要求，甲方、乙方、丙方要求等，是随机看后台数据的，所以我们就不能事先画好Excel模板进行....
Java导出数据到Excel文件中（支持多表头）
08-17
支持从数据库中查询到数据，以多级表头形式导入到Excel文件中。比如：一级表头：学号、姓名、各科成绩。二级表头：语文、数学、英语（二级表头在各科成绩下面）。
java excel 导出多个sheet
格林王子举哥
10-14 7365
第一步：需要一个通用的装载数据的实体类 package com.yfh.common.core.domain; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import java.io.Serializable; import java.util.List; import java.util.Map; /** * @author zdj * @version 1.0 *
Java导出Excel 复杂表头
one大白(●—●)的博客
03-12 1496
文章标题导出表格依赖 导出表格 导出表格的方式在我的理解有两种 一种是直接用代码控制表头==== 简单的表头 一种是直接使用模板直接添加数据就可以=== 复杂的表头 依赖 <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</version> </dependenc
Java导出Excel文件(多Sheet表头)
小达哥的博客
07-14 464
/** * 导出-- 查看筛选条件下：查看当前活动抽奖名单 * @param request * @param response * @throws Exception */ @RequestMapping("/download_lottery_list") public synchronized void download_lottery_list(@RequestParam(required = false) Integer id, .
java poi实现Excel多级表头导出（多级表头，复杂表头）
weixin_45620445的博客
08-12 4712
最近碰到一个导出，比较繁琐，也查询了许多博客，在其中一篇博客的基础上修改，实现了最终想要的效果。话不多说，直接上效果图。
Java实现excel动态表头及动态sheet
weixin_43241706的博客
06-11 4635
Java 动态表头 多sheet
【excel复杂一对多动态表头导出】
weixin_51572314的博客
10-20 345
POI构建一对多动态表头导出
java导出功能（多个sheet页数据导出）
热门推荐
虽千万人,吾往矣
09-05 1万+
自定义格式的标题，及对应的数据，每个sheet页的表头及数据都可以不同，具体根据实际业务去变更，可以仿照这个例子去编写，这里提供的是最基础的多sheet页导出，如果需要多行表头复杂业务的多sheet页导出可参考我另一篇复杂表头，多行表头导出，如果导出不确定行数列数跟随业务的增长而变动的导出参考另一篇不确定行数列数数据的工具类提示：以下是本篇文章正文内容，下面案例可供参考。
Java实现POI导出excel复杂表头
weixin_45659847的博客
02-16 3576
主方法加数据和excel表头布局 /** * 导出 * @param request * @param response * @throws IOException */ @RequestMapping("/exportMultipleItemStatisticsList.do") public void export(HttpServletRequest request, HttpServletResponse response) th.
java导出包含多个sheet的Excel代码示例
08-26
主要介绍了java导出包含多个sheet的Excel，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧
java导出excel复杂表头
02-20
java工程，源代码，可直接运行，需自己改造成web下载方式。
复杂多表头excel表格的导出及布局，支持拓展
02-22
1.用于多种业务的复杂多表头表格 2.可支持任意拓展表头后导出 3.简单易懂
java实现导出Excel多行表头复杂模板
12-20
java实现导出Excel多行表头复杂模板 一般我们都会选择poi来导出，选择一个比较好的ExcelUtils 但是对于初学者而言不了解poi的，还需从poi API文档去学习，如果是自学的话更好，如果是公司安排的任务，没有接触过有...
java学习和项目总结
2301_79390585的博客
05-24 628
JRE：JRE是Java 运行时环境，主要包括了两个运行需要的组件：JVM 和 Java 核心类库，如果不进行java开发只想运行就可以只下载JRE。而Java语言，它的代码会先通过javac编译成字节码，再通过jvm将字节码转换成机器码（0和1）执行，即解释运行和编译运行配合使用，所以是混合型。在程序运行之前，通过编译器将源程序编译成机器码可运行的二进制，以后执行这个程序时，就不用再进行编译了。JVM：JVM是java进行编译的虚拟机，是Java 能够跨平台运行的核心。特点：执行速度慢、效率低；
Java高级面试精粹：问题与解答集锦（一）
Layla_c的博客
05-23 1379
答案多态是Java中的一个核心概念，它允许不同类的对象对同一消息做出响应，但具体的行为会根据对象的实际类型而有所不同。重载（Overloading）：当多个方法具有相同的名称，但参数列表不同时，这称为方法重载。编译器根据方法调用时传递的参数类型和数量来确定调用哪个方法。重写（Overriding）：当子类有一个与父类同名、同参数列表的方法时，子类可以提供特定的实现来覆盖父类的方法。运行时，Java虚拟机（JVM）会根据对象的实际类型调用相应的方法，这个过程称为动态绑定或晚期绑定。答案。
数据访问与Spring Data JPA
最新发布
无远弗届
05-25 482
假设我们要处理一个用户表（users），可以创建一个User@Entity // 标识这是一个实体类 public class User {@Id // 标识这是主键字段 @GeneratedValue(strategy = GenerationType . AUTO) // 主键生成策略 private Long id;// Getter 和 Setter ... }@Entity // 标识这是一个实体类 public class User {
【java-数据结构19-队列的模拟实现】
2302_80519942的博客
05-21 777
（这个才是重点）同上，没有元素直接返回即可，只有一个元素（全部置空），普通情况（rear等于前驱，rear置为空），

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值