poi实现表格合并单元格并设计样式

我哪会这个啊

已于 2022-06-10 17:24:23 修改

阅读量986

点赞数

分类专栏： easyexcel 和poi 文章标签： java

于 2022-06-10 11:33:49 首次发布

本文链接：https://blog.csdn.net/qq_45243783/article/details/125218218

版权

easyexcel 和poi 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1.添加依赖

 <!--xls03版-->
             <dependency>
                 <groupId>org.apache.poi</groupId>
                 <artifactId>poi</artifactId>
                 <version>3.9</version>
             </dependency>

             <!--xlsx(07)-->
             <dependency>
                 <groupId>org.apache.poi</groupId>
                 <artifactId>poi-ooxml</artifactId>
                 <version>3.9</version>
             </dependency>

2.controller层代码

/**
     * 年度能耗统计报表
     */
    @GetMapping("/export")
    public void exportEquipmentException(HttpServletResponse response, String year, BigDecimal electricityPrice,BigDecimal waterPrice) throws IOException, InterruptedException, ParseException {
        String fileName = "年度能耗统计.xlsx";
        String encode = URLEncoder.encode(fileName, "UTF-8");
        encode = encode.replaceAll("\\+", "%20");

        response.setCharacterEncoding("UTF-8");
        response.setHeader("ContentType", "application/octet-stream");
        response.setHeader("Content-Disposition", "attachment;fileName=" + encode);
        ServletOutputStream out = response.getOutputStream();
        Workbook wb = dieselGeneratorIncomingElectricityDataService.toExport(year,electricityPrice,waterPrice);
        wb.write(out);
        wb.close();
        out.close();
    }

3.业务层编码，代码业务含义见注释

/**
     * 年度能耗统计报表
     * @param year
     * @param electricityPrice
     * @param waterPrice
     * @return
     */
    @Override
    public Workbook toExport(String year, BigDecimal electricityPrice, BigDecimal waterPrice) throws ParseException {
        Workbook wb = new XSSFWorkbook();
        Sheet s = wb.createSheet();
        //设置单元格的大小，默认从第一行第一列开始扩容

        //设置通用配置
        CellStyle cs_field = wb.createCellStyle();
        cs_field.setAlignment(HorizontalAlignment.CENTER);
        cs_field.setBorderTop(BorderStyle.THIN);
        cs_field.setBorderBottom(BorderStyle.THIN);
        cs_field.setBorderLeft(BorderStyle.THIN);
        cs_field.setBorderRight(BorderStyle.THIN);

        //3.制作表头
        s.addMergedRegion(new CellRangeAddress(0, 0, 0, 11));
        //设置第一行，从1开始
        Row row_1 = s.createRow(0);
        //设置第一列，从1开始
        Cell cell_1_1 = row_1.createCell(0);
        //第一行第一列为异常设备导出信息
        cell_1_1.setCellValue("年度能耗统计报表");
        //创建一个样式
        CellStyle cs_title = wb.createCellStyle();
        cs_title.setAlignment(HorizontalAlignment.CENTER);
        cs_title.setVerticalAlignment(VerticalAlignment.CENTER);
        cell_1_1.setCellStyle(cs_title);

        //4.制作标题
        String a = "月份" + "\\" +"项目";
        String[] fields = {a, "峰", "平", "谷", "总电度","总电费","容量费","发电量","发电费","用水量","用水金额","合计"};
        Row row_2 = s.createRow(1);
        for (int i = 0; i < fields.length; i++) {
            Cell cell_2_temp = row_2.createCell(i);
            cell_2_temp.setCellValue(fields[i]);    //给这个单元格赋值
            cell_2_temp.setCellStyle(cs_field);
        }
        List<ElectricityConsumption> electricityConsumptionList = new ArrayList<>();
        List<String> yearMonthList = new ArrayList<>();
        for (int i = 1; i <= 12 ; i++) {
            if (i >= 10){
                String yearMonth = year + "-" + i;
                yearMonthList.add(yearMonth);
            }else if (i <= 9){
                String yearMonth = year + "-0"+i;  // 2022-05
                yearMonthList.add(yearMonth);
            }
        }

        for (String yearmonth : yearMonthList) {
            TimeZone timeZone = TimeZone.getTimeZone("GMT+8:00");
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat();
            simpleDateFormat.setTimeZone(timeZone);
            simpleDateFormat.applyPattern("yyyy-MM");
            Date parse = simpleDateFormat.parse(yearmonth);
            long time = parse.getTime(); // 获取传入时间毫秒值东八区
            Long monthStartTime = DateUtil.getMonthStartTime(time); // 当月开始时间
            Long monthEndTime = DateUtil.getMonthEndTime(time); // 当月结束时间
            List<HighVoltageAggregateVo> highVoltageAggregateVos = highVoltageIncomingElectricityDataBiz.selectByTime(monthStartTime, monthEndTime);

            ElectricityConsumption electricityConsumption = new ElectricityConsumption();
            List<HighVoltageAggregateVo> highVoltageAggregateVos1 = new ArrayList<>();
            List<HighVoltageAggregateVo> highVoltageAggregateVos2 = new ArrayList<>();
            List<HighVoltageAggregateVo> highVoltageAggregateVos3 = new ArrayList<>();

            if (CollectionUtil.isNotEmpty(highVoltageAggregateVos)){
                Map<Integer, List<HighVoltageAggregateVo>> collect = highVoltageAggregateVos.stream().collect(Collectors.groupingBy(highVoltageAggregateVo -> highVoltageAggregateVo.getType()));
                highVoltageAggregateVos1 = collect.get(1); // 峰
                highVoltageAggregateVos2 = collect.get(2); // 平
                highVoltageAggregateVos3 = collect.get(3); // 谷
                if (highVoltageAggregateVos1 == null){
                    electricityConsumption.setPeak(BigDecimal.ZERO); // 峰
                }else if (highVoltageAggregateVos1 != null){
                    electricityConsumption.setPeak(highVoltageAggregateVos1.get(0).getElectricitySum()); // 峰
                }

                if (highVoltageAggregateVos2 == null){
                    electricityConsumption.setFlat(BigDecimal.ZERO); // 平
                }else if (highVoltageAggregateVos2 != null){
                    electricityConsumption.setFlat(highVoltageAggregateVos2.get(0).getElectricitySum()); // 平
                }

                if (highVoltageAggregateVos3 == null){
                    electricityConsumption.setValley(BigDecimal.ZERO);
                } else if (highVoltageAggregateVos3 != null){
                    electricityConsumption.setValley(highVoltageAggregateVos3.get(0).getElectricitySum()); // 谷
                }
            }
            else if (CollectionUtil.isEmpty(highVoltageAggregateVos)){
                electricityConsumption.setPeak(BigDecimal.ZERO); // 峰
                electricityConsumption.setFlat(BigDecimal.ZERO); // 平
                electricityConsumption.setValley(BigDecimal.ZERO); // 谷
            }

            if (electricityConsumption.getPeak() == null){
                electricityConsumption.setPeak(BigDecimal.ZERO);
            }else if (electricityConsumption.getFlat() == null){
                electricityConsumption.setFlat(BigDecimal.ZERO);
            }else if (electricityConsumption.getValley() == null){
                electricityConsumption.setValley(BigDecimal.ZERO);
            }
            // 总电度
            electricityConsumption.setElectricDegreeTotal(electricityConsumption.getPeak().add(electricityConsumption.getFlat()).add(electricityConsumption.getValley()));
            // 总电费,保留两位小数点
            electricityConsumption.setElectricityCharge(electricityConsumption.getElectricDegreeTotal().multiply(electricityPrice).setScale(2, RoundingMode.HALF_UP));
            // 容量费
            ElectricityBill electricityBill = electricityBillBiz.selectOneByYearMonth(yearmonth);
            if (electricityBill == null){
                electricityConsumption.setCapacityCost(BigDecimal.ZERO);
            }else if (electricityBill != null){
                electricityConsumption.setCapacityCost(electricityBill.getCapacityCost());
            }

            // 发电量
            BigDecimal electricityCapacity = dieselGeneratorIncomingElectricityDataBiz.getElectricityCapacity(monthStartTime, monthEndTime);
            if (electricityCapacity == null){
                electricityCapacity = BigDecimal.ZERO;
            }
            electricityConsumption.setGeneratingCapacity(electricityCapacity);

            // 发电费
            if (electricityCapacity == null){
                electricityCapacity = BigDecimal.ZERO;
                electricityConsumption.setPowerGenerationFee(electricityCapacity.multiply(electricityPrice).setScale(2, RoundingMode.HALF_UP));
            }else if (electricityCapacity != null){
                electricityConsumption.setPowerGenerationFee(electricityCapacity.multiply(electricityPrice).setScale(2, RoundingMode.HALF_UP));
            }

            // 用水量
            BigDecimal waterConsumption = waterConsumptionBiz.getWaterConsumption(monthStartTime, monthEndTime);
            if (waterConsumption == null){
                waterConsumption = BigDecimal.ZERO;
            }
            electricityConsumption.setWaterConsumption(waterConsumption);

            // 用水金额
            if (waterConsumption == null){
                waterConsumption = BigDecimal.ZERO;
                electricityConsumption.setWaterCharge(waterConsumption.multiply(waterPrice).setScale(2,RoundingMode.HALF_UP));
            }else if (waterConsumption != null){
                electricityConsumption.setWaterCharge(waterConsumption.multiply(waterPrice).setScale(2,RoundingMode.HALF_UP));
            }

            // 合计 总电费+容量费+发电费+用水金额
            BigDecimal total = electricityConsumption.getElectricDegreeTotal().add(electricityConsumption.getCapacityCost()).add(electricityConsumption.getPowerGenerationFee()).add(electricityConsumption.getWaterCharge());
            electricityConsumption.setTotal(total);

            electricityConsumptionList.add(electricityConsumption);
        }
            for (int i = 0; i < 12 ; i++) {
                electricityConsumptionList.get(i).setId(""+(i+1));
            }

           this.fillTableData(electricityConsumptionList,cs_field,s);
            // 合计
           Row row_14 = s.createRow(14);
           Cell cell14_0 = row_14.createCell(0);
           cell14_0.setCellValue("合计");
           cell14_0.setCellStyle(cs_field);
           // 峰和
           BigDecimal peakSum = electricityConsumptionList.stream().map(electricityConsumption -> electricityConsumption.getPeak()).reduce(BigDecimal.ZERO, BigDecimal::add);
           if (peakSum.compareTo(BigDecimal.ZERO) == 0){
               Cell cell14_1 = row_14.createCell(1);
               cell14_1.setCellValue("");
               cell14_1.setCellStyle(cs_field);
           }else if (peakSum.compareTo(BigDecimal.ZERO) == 1){
               Cell cell14_1 = row_14.createCell(1);
               cell14_1.setCellValue(peakSum.toString());
               cell14_1.setCellStyle(cs_field);
           }
           // 平和
           BigDecimal flatSum = electricityConsumptionList.stream().map(electricityConsumption -> electricityConsumption.getFlat()).reduce(BigDecimal.ZERO, BigDecimal::add);
           if (flatSum.compareTo(BigDecimal.ZERO) == 0){
               Cell cell14_2 = row_14.createCell(2);
               cell14_2.setCellValue("");
               cell14_2.setCellStyle(cs_field);
           }else if (peakSum.compareTo(BigDecimal.ZERO) == 1){
               Cell cell14_2 = row_14.createCell(2);
               cell14_2.setCellValue(flatSum.toString());
               cell14_2.setCellStyle(cs_field);
           }

           // 谷和
           BigDecimal valleySum = electricityConsumptionList.stream().map(electricityConsumption -> electricityConsumption.getValley()).reduce(BigDecimal.ZERO, BigDecimal::add);
           if (valleySum.compareTo(BigDecimal.ZERO) == 0){
               Cell cell14_3 = row_14.createCell(3);
               cell14_3.setCellValue("");
               cell14_3.setCellStyle(cs_field);
           }else if (valleySum.compareTo(BigDecimal.ZERO) == 1){
               Cell cell14_3 = row_14.createCell(2);
               cell14_3.setCellValue(valleySum.toString());
               cell14_3.setCellStyle(cs_field);
           }

           //总电度和
           BigDecimal electricDegreeTotalSum = electricityConsumptionList.stream().map(electricityConsumption -> electricityConsumption.getElectricDegreeTotal()).reduce(BigDecimal.ZERO, BigDecimal::add);
           if (electricDegreeTotalSum.compareTo(BigDecimal.ZERO) == 0){
               Cell cell14_4 = row_14.createCell(4);
               cell14_4.setCellValue("");
               cell14_4.setCellStyle(cs_field);
           }else if (electricDegreeTotalSum.compareTo(BigDecimal.ZERO) == 1){
               Cell cell14_4 = row_14.createCell(2);
               cell14_4.setCellValue(electricDegreeTotalSum.toString());
               cell14_4.setCellStyle(cs_field);
           }

           // 总电费和
           BigDecimal electricitySum = electricityConsumptionList.stream().map(electricityConsumption -> electricityConsumption.getElectricityCharge()).reduce(BigDecimal.ZERO, BigDecimal::add);
           if (electricitySum.compareTo(BigDecimal.ZERO) == 0){
               Cell cell14_5 = row_14.createCell(5);
               cell14_5.setCellValue("");
               cell14_5.setCellStyle(cs_field);
           }else if (electricitySum.compareTo(BigDecimal.ZERO) == 1){
               Cell cell14_5 = row_14.createCell(5);
               cell14_5.setCellValue(electricitySum.toString());
               cell14_5.setCellStyle(cs_field);
           }

           // 容量费和
           BigDecimal capacityCostSum = electricityConsumptionList.stream().map(electricityConsumption -> electricityConsumption.getCapacityCost()).reduce(BigDecimal.ZERO, BigDecimal::add);
           if (capacityCostSum.compareTo(BigDecimal.ZERO) == 0){
               Cell cell14_6 = row_14.createCell(6);
               cell14_6.setCellValue("");
               cell14_6.setCellStyle(cs_field);
           }else if (capacityCostSum.compareTo(BigDecimal.ZERO) == 1){
               Cell cell14_6 = row_14.createCell(6);
               cell14_6.setCellValue(capacityCostSum.toString());
               cell14_6.setCellStyle(cs_field);
           }

            // 发电量和
           BigDecimal generatingCapacitySum = electricityConsumptionList.stream().map(electricityConsumption -> electricityConsumption.getGeneratingCapacity()).reduce(BigDecimal.ZERO, BigDecimal::add);
           if (generatingCapacitySum.compareTo(BigDecimal.ZERO) == 0 ){
               Cell cell14_7 = row_14.createCell(7);
               cell14_7.setCellValue("");
               cell14_7.setCellStyle(cs_field);
           }else if (generatingCapacitySum.compareTo(BigDecimal.ZERO) == 1){
               Cell cell14_7 = row_14.createCell(7);
               cell14_7.setCellValue(generatingCapacitySum.toString());
               cell14_7.setCellStyle(cs_field);
           }

           // 发电费和
           BigDecimal powerGenerationFeeSum = electricityConsumptionList.stream().map(electricityConsumption -> electricityConsumption.getPowerGenerationFee()).reduce(BigDecimal.ZERO, BigDecimal::add);
           if (powerGenerationFeeSum.compareTo(BigDecimal.ZERO) == 0 ){
               Cell cell14_8 = row_14.createCell(8);
               cell14_8.setCellValue("");
               cell14_8.setCellStyle(cs_field);
           }else if (powerGenerationFeeSum.compareTo(BigDecimal.ZERO) == 1){
               Cell cell14_8 = row_14.createCell(8);
               cell14_8.setCellValue(powerGenerationFeeSum.toString());
               cell14_8.setCellStyle(cs_field);
           }

           // 用水量和
           BigDecimal waterConsumptionSum = electricityConsumptionList.stream().map(electricityConsumption -> electricityConsumption.getWaterConsumption()).reduce(BigDecimal.ZERO, BigDecimal::add);
           if (waterConsumptionSum.compareTo(BigDecimal.ZERO) == 0 ){
               Cell cell14_9 = row_14.createCell(9);
               cell14_9.setCellValue("");
               cell14_9.setCellStyle(cs_field);
           }else if (waterConsumptionSum.compareTo(BigDecimal.ZERO) == 1){
               Cell cell14_9 = row_14.createCell(9);
               cell14_9.setCellValue(waterConsumptionSum.toString());
               cell14_9.setCellStyle(cs_field);
           }


           // 用水金额
           BigDecimal waterChargeSum = electricityConsumptionList.stream().map(electricityConsumption -> electricityConsumption.getWaterCharge()).reduce(BigDecimal.ZERO, BigDecimal::add);
           if (waterChargeSum.compareTo(BigDecimal.ZERO) == 0 ){
               Cell cell14_10 = row_14.createCell(10);
               cell14_10.setCellValue("");
               cell14_10.setCellStyle(cs_field);
           }else if (waterChargeSum.compareTo(BigDecimal.ZERO) == 1){
               Cell cell14_10 = row_14.createCell(10);
               cell14_10.setCellValue(waterChargeSum.toString());
               cell14_10.setCellStyle(cs_field);
           }

           // 合计
           BigDecimal totalSum = electricityConsumptionList.stream().map(electricityConsumption -> electricityConsumption.getTotal()).reduce(BigDecimal.ZERO, BigDecimal::add);
           if (totalSum.compareTo(BigDecimal.ZERO) == 0 ){
               Cell cell14_11 = row_14.createCell(11);
               cell14_11.setCellValue("");
               cell14_11.setCellStyle(cs_field);
           }else if (totalSum.compareTo(BigDecimal.ZERO) == 1){
               Cell cell14_11 = row_14.createCell(11);
               cell14_11.setCellValue(totalSum.toString());
               cell14_11.setCellStyle(cs_field);
           }


             //合并指定单元格
           s.addMergedRegion(new CellRangeAddress(15, 15, 1, 11));
           Row row_15 = s.createRow(15);
           Cell cell_15 = row_15.createCell(0);
           cell_15.setCellValue("成本合计");
           cell_15.setCellStyle(cs_field);

           Cell cell15_1 = row_15.createCell(1);
           if (totalSum.compareTo(BigDecimal.ZERO) == 0 ){
               cell15_1.setCellValue("");
               cell15_1.setCellStyle(cs_field);
           }else if (totalSum.compareTo(BigDecimal.ZERO) == 1){
               cell15_1.setCellValue(totalSum.toString());
               cell15_1.setCellStyle(cs_field);
           }


           // 设置合计单元格样式
            Cell cell = s.getRow(14).getCell(0);
            CellStyle cellStyle3 = wb.createCellStyle();
            Font font = wb.createFont();
            font.setColor((short) 10);
            font.setUnderline((byte) 1);
            cellStyle3.setFont(font);

            cellStyle3.setAlignment(HorizontalAlignment.CENTER);
            cellStyle3.setBorderTop(BorderStyle.THIN);
            cellStyle3.setBorderBottom(BorderStyle.THIN);
            cellStyle3.setBorderLeft(BorderStyle.THIN);
            cellStyle3.setBorderRight(BorderStyle.THIN);

            cell.setCellStyle(cellStyle3);

            // 设置成本总计单元格样式
            Cell cell1 = s.getRow(15).getCell(0);
            CellStyle cellStyle4 = wb.createCellStyle();
            Font font1 = wb.createFont();
            font1.setColor((short) 10);
            font1.setUnderline((byte) 1);
            cellStyle4.setFont(font1);
            cellStyle4.setAlignment(HorizontalAlignment.CENTER);
            cellStyle4.setBorderTop(BorderStyle.THIN);
            cellStyle4.setBorderBottom(BorderStyle.THIN);
            cellStyle4.setBorderLeft(BorderStyle.THIN);
            cellStyle4.setBorderRight(BorderStyle.THIN);
            cell1.setCellStyle(cellStyle4);


            // 消除合并单元格后不显示边框问题
           Cell cell15_2 = row_15.getCell(2);
           if (cell15_2 == null){
               row_15.createCell(2).setCellStyle(cs_field);
           }
           Cell cell15_3 = row_15.getCell(3);
           if (cell15_3 == null){
               row_15.createCell(3).setCellStyle(cs_field);
           }
           Cell cell15_4 = row_15.getCell(4);
           if (cell15_4 == null){
               row_15.createCell(4).setCellStyle(cs_field);
           }
           Cell cell15_5 = row_15.getCell(5);
           if (cell15_5 == null){
               row_15.createCell(5).setCellStyle(cs_field);
           }
           Cell cell15_6 = row_15.getCell(6);
           if (cell15_6 == null){
               row_15.createCell(6).setCellStyle(cs_field);
           }
           Cell cell15_7 = row_15.getCell(7);
           if (cell15_7 == null){
               row_15.createCell(7).setCellStyle(cs_field);
           }
           Cell cell15_8 = row_15.getCell(8);
           if (cell15_8 == null){
               row_15.createCell(8).setCellStyle(cs_field);
           }
           Cell cell15_9 = row_15.getCell(9);
           if (cell15_9 == null){
               row_15.createCell(9).setCellStyle(cs_field);
           }
           Cell cell15_10 = row_15.getCell(10);
           if (cell15_10 == null){
               row_15.createCell(10).setCellStyle(cs_field);
           }
           Cell cell15_11 = row_15.getCell(11);
           if (cell15_11 == null){
               row_15.createCell(11).setCellStyle(cs_field);
           }

           return wb;
    }


            /**
             * 填充表格数据
             */
   private void fillTableData(List<ElectricityConsumption> electricityConsumptionList, CellStyle cs_field, Sheet s) {
        int row_index = 0;
        for (ElectricityConsumption electricityConsumption : electricityConsumptionList) {
            int cell_index = 0;

            Row row_temp = s.createRow(2 + row_index++);

            //月份\项目
            Cell cell_data_1 = row_temp.createCell(0 + cell_index++);
            cell_data_1.setCellValue(electricityConsumption.getId());
            cell_data_1.setCellStyle(cs_field);


            // 峰
            Cell cell_data_2 = row_temp.createCell(cell_index++);
            if (electricityConsumption.getPeak().compareTo(BigDecimal.ZERO) == 0 ){
                cell_data_2.setCellValue("");
            }else if (electricityConsumption.getPeak().compareTo(BigDecimal.ZERO) == 1){
                cell_data_2.setCellValue(electricityConsumption.getPeak().toString());
            }
             cell_data_2.setCellStyle(cs_field);

            // 平
            Cell cell_data_3 = row_temp.createCell(cell_index++);
            if (electricityConsumption.getFlat().compareTo(BigDecimal.ZERO) == 0 ){
                cell_data_3.setCellValue("");
            }else if (electricityConsumption.getFlat().compareTo(BigDecimal.ZERO) == 1){
                cell_data_3.setCellValue(electricityConsumption.getFlat().toString());
            }
            cell_data_3.setCellStyle(cs_field);


            // 谷
            Cell cell_data_4 = row_temp.createCell(cell_index++);
            if (electricityConsumption.getValley().compareTo(BigDecimal.ZERO) == 0 ){
                cell_data_4.setCellValue("");
            }else if (electricityConsumption.getValley().compareTo(BigDecimal.ZERO) == 1){
                cell_data_4.setCellValue(electricityConsumption.getValley().toString());
            }
            cell_data_4.setCellStyle(cs_field);

            // 总电度
            Cell cell_data_5 = row_temp.createCell(cell_index++);
            if (electricityConsumption.getElectricDegreeTotal().compareTo(BigDecimal.ZERO) == 0 ){
                cell_data_5.setCellValue("");
            }else if (electricityConsumption.getElectricDegreeTotal().compareTo(BigDecimal.ZERO) == 1){
                cell_data_5.setCellValue(electricityConsumption.getElectricDegreeTotal().toString());
            }
            cell_data_5.setCellStyle(cs_field);

            // 总电费
            Cell cell_data_6 = row_temp.createCell(cell_index++);
            if (electricityConsumption.getElectricityCharge().compareTo(new BigDecimal("0.00")) == 0 ){
                cell_data_6.setCellValue("");
            }else if (electricityConsumption.getElectricityCharge().compareTo(new BigDecimal("0.00")) == 1){
                cell_data_6.setCellValue(electricityConsumption.getElectricityCharge().toString());
            }
            cell_data_6.setCellStyle(cs_field);

            // 容量费
            Cell cell_data_7 = row_temp.createCell(cell_index++);
            if (electricityConsumption.getCapacityCost().compareTo(BigDecimal.ZERO) == 0 ){
                cell_data_7.setCellValue("");
            }else if (electricityConsumption.getCapacityCost().compareTo(BigDecimal.ZERO) == 1){
                cell_data_7.setCellValue(electricityConsumption.getCapacityCost().toString());
            }
            cell_data_7.setCellStyle(cs_field);

            // 发电量
            Cell cell_data_8 = row_temp.createCell(cell_index++);
            if (electricityConsumption.getGeneratingCapacity().compareTo(new BigDecimal("0.00")) == 0 || electricityConsumption.getGeneratingCapacity() == null){
                cell_data_8.setCellValue("");
            }else if (electricityConsumption.getGeneratingCapacity().compareTo(BigDecimal.ZERO) == 1){
                cell_data_8.setCellValue(electricityConsumption.getGeneratingCapacity().toString());
            }
            cell_data_8.setCellStyle(cs_field);

            // 发电费
            Cell cell_data_9 = row_temp.createCell(cell_index++);
            if (electricityConsumption.getPowerGenerationFee().compareTo(new BigDecimal("0.00")) == 0 ){
                cell_data_9.setCellValue("");
            }else if (electricityConsumption.getPowerGenerationFee().compareTo(new BigDecimal("0.00")) == 1){
                cell_data_9.setCellValue(electricityConsumption.getPowerGenerationFee().toString());
            }
            cell_data_9.setCellStyle(cs_field);

            // 用水量
            Cell cell_data_10 = row_temp.createCell(cell_index++);
            if (electricityConsumption.getWaterConsumption().compareTo(new BigDecimal("0.00")) == 0 || electricityConsumption.getWaterConsumption() == null){
                cell_data_10.setCellValue("");
            }else if (electricityConsumption.getPowerGenerationFee().compareTo(new BigDecimal("0.00")) == 1){
                cell_data_9.setCellValue(electricityConsumption.getPowerGenerationFee().toString());
            }
            cell_data_10.setCellStyle(cs_field);

            // 用水金额
            Cell cell_data_11 = row_temp.createCell(cell_index++);
            if (electricityConsumption.getWaterCharge().compareTo(new BigDecimal("0.00")) == 0){
                cell_data_11.setCellValue("");
            }else if (electricityConsumption.getWaterCharge().compareTo(new BigDecimal("0.00")) == 1){
                cell_data_11.setCellValue(electricityConsumption.getWaterCharge().toString());
            }
            cell_data_11.setCellStyle(cs_field);

            // 合计
            Cell cell_data_12 = row_temp.createCell(cell_index++);
            if (electricityConsumption.getTotal().compareTo(new BigDecimal("0.00")) == 0){
                cell_data_12.setCellValue("");
            }else if (electricityConsumption.getWaterCharge().compareTo(new BigDecimal("0.00")) == 1){
                cell_data_12.setCellValue(electricityConsumption.getTotal().toString());
            }
            cell_data_12.setCellStyle(cs_field);

        }
    }

对应实体类

public class ElectricityConsumption {
    private String id;
    private BigDecimal peak; // 峰
    private BigDecimal flat; // 平
    private BigDecimal valley; // 谷
    private BigDecimal electricDegreeTotal; //总电度
    private BigDecimal electricityCharge; //总电费
    private BigDecimal capacityCost; // 容量费
    private BigDecimal generatingCapacity; // 发电量
    private BigDecimal powerGenerationFee; // 发电费
    private BigDecimal waterConsumption; // 用水量
    private BigDecimal waterCharge; // 用水金额
    private BigDecimal total; // 合计
    }