hutool导出自定义单元格

已于 2025-04-20 16:59:47 修改
阅读量544 收藏 10
点赞数 21
文章标签： java
于 2025-01-20 15:29:52 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/m18066975852/article/details/145263585
版权
*****2024年5月班列利润核算
线路名称线路站点箱型成本销售收入利润额发运柜量总销售利润
西安-马拉线路马拉舍维奇20GP2587.922600.0012.08112.08
40HQ36604200.005401540
西安-杜堡线路布达佩斯40HQ36606000.00234012340
马拉舍维奇20GP2587.922500.00-87.925-439.6
40HQ36603950.00290174930
杜伊斯堡20GP2587.923000.00412.082824.16
40HQ36605350.001690610140
汉堡/杜堡-西安国际港马拉舍维奇20GP00.00050
40HQ02600.002600410400
汉堡40HQ03200.00320026400
合计22403.763340010996.244435146.64
public void downloadList(int year, int month, HttpServletResponse response) throws Exception {
   String beginDate = DateUtils.getFisrtDayOfMonth(year,month);
   String endDate = DateUtils.getLastDayOfMonth(year,month);
   //1.以箱子信息表为基础表，查询条件：1.班次发车时间输入月份 2.订单状态已支付 3.线路标志isShow为1 按照线路+站点+箱型分组，排序，得到所有数据行数
   List<Map<String,Object>> containerList = containerService.downloadList(beginDate,endDate);
   //2.总共需要要查三套定价。收入定价+成本境内+成本境外定价
   //2-1.查数据库时间区间和查询区间有重合的所有定价规则，取果斯口岸数据，按照 线路+站点+箱型分组，取最大的价格 
   // 收入
   List<Map<String,Object>> srList = containerService.downloadListSr(beginDate,endDate);
   // 成本境内
   List<Map<String,Object>> cbjnList = containerService.downloadListCbjn(beginDate,endDate);
   // 成本境外
   List<Map<String,Object>> cbjwList = containerService.downloadListCbjw(beginDate,endDate);
   // 下载
   ExcelWriter writer = ExcelUtil.getBigWriter();
   ServletOutputStream out = null;
   String name = "中欧班列长安号" + year + "年" + month+"月班列利润核算";
   try {
      writer.merge(0, 0, 0, 7, name, false);
      // 头部样式
      CellStyle headerStyle = writer.createCellStyle();
      Font font = writer.createFont();
      font.setBold(true);
      font.setFontHeightInPoints((short) 18);
      headerStyle.setFont(font);
      // 垂直居中
      headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
      // 水平居中
      headerStyle.setAlignment(HorizontalAlignment.CENTER);
      writer.setStyle(headerStyle, 0, 0);
      // 高度
      writer.setRowHeight(0, 42);
      // 宽度
      writer.setColumnWidth(0, 30);
      writer.setColumnWidth(1, 20);
      writer.setColumnWidth(2, 15);
      writer.setColumnWidth(3, 15);
      writer.setColumnWidth(4, 15);
      writer.setColumnWidth(5, 15);
      writer.setColumnWidth(6, 15);
      writer.setColumnWidth(7, 15);
      //第一行 参数先cell，后row
      writer.writeCellValue(0,1, "线路名称");
      writer.writeCellValue(1,1, "线路站点");
      writer.writeCellValue(2,1, "箱型");
      writer.writeCellValue(3,1, "成本");
      writer.writeCellValue(4,1, "销售收入");
      writer.writeCellValue(5,1, "利润额");
      writer.writeCellValue(6,1, "发运柜量");
      writer.writeCellValue(7,1, "总销售利润");
      // 需要先给单元格写内容，后设置高度才会生效
      writer.setRowHeight(1, 42);
      // 最后一行
      String cell23 = TrainsEmailMainService.convertToColumnName(containerList.size() + 1, 3);
      String cell24 = TrainsEmailMainService.convertToColumnName(containerList.size() + 1, 4);
      String cell25 = TrainsEmailMainService.convertToColumnName(containerList.size() + 1, 5);
      String cell26 = TrainsEmailMainService.convertToColumnName(containerList.size() + 1, 6);
      String cell27 = TrainsEmailMainService.convertToColumnName(containerList.size() + 1, 7);
      writer.merge(containerList.size()+2, containerList.size()+2, 0, 1, "合计", false);
      writer.writeCellValue(2,containerList.size()+2,  "");
      writer.writeCellValue(3,containerList.size()+2,  new FormulaCellValue("SUM(D3:"+cell23+")"));
      writer.writeCellValue(4,containerList.size()+2,  new FormulaCellValue("SUM(E3:"+cell24+")"));
      writer.writeCellValue(5,containerList.size()+2,  new FormulaCellValue("SUM(F3:"+cell25+")"));
      writer.writeCellValue(6,containerList.size()+2,  new FormulaCellValue("SUM(G3:"+cell26+")"));
      writer.writeCellValue(7,containerList.size()+2,  new FormulaCellValue("SUM(H3:"+cell27+")"));
      List<Map<String, Object>> mergeLineList = Lists.newArrayList();
      List<Map<String, Object>> mergeStationList = Lists.newArrayList();
      String tempLineName = null;
      int tempLineIndex = -1;
      String tempStationName = null;
      int tempStationIndex = -1;
      //3.以第一步的查询结果为基准，循环匹配价格，填入成本和收入的价格
      int i = 1;
      for (Map<String, Object> map : containerList) {
         i++;
         String lineName = map.get("lineName").toString();
         String stationName = map.get("stationName").toString();
         String lineId = map.get("lineId").toString();
         String stationId = map.get("stationId").toString();
         String containerType = map.get("containerType").toString();
         String containerTypeName = ContainerTypeEnum.getNameByIndex(containerType);
         String goOrBack = map.get("goOrBack").toString();
         BigDecimal spaceNum = new BigDecimal(map.get("spaceNum").toString());
         //收入
         BigDecimal srDecimal = BigDecimal.ZERO;
         Map<String, Object> srMap = srList.stream().filter(item->
            item.get("lineId").toString().equals(lineId)
            && item.get("stationId").toString().equals(stationId)
            && item.get("containerType").toString().equals(containerType)
         ).findAny().orElse(null);
         if(ObjectUtil.isNotEmpty(srMap)){
            String currencyType = srMap.get("currencyType").toString();
            BigDecimal fee = new BigDecimal(srMap.get("fee").toString());
            if("1".equals(currencyType)){
               // 人民币，转美元
               String rate = RemitRateUtil.getDateRate2("1", (year+""), (month+""));
               if(StringUtils.isNotBlank(rate)){
                  fee = fee.divide(new BigDecimal(rate), 2, RoundingMode.HALF_DOWN);
               }
            }
            srDecimal = srDecimal.add(fee);
         }
         // 成本
         BigDecimal cbDecimal = BigDecimal.ZERO;
         // 成本境内
         Map<String, Object> cbjnMap = cbjnList.stream().filter(item->
               item.get("goOrBack").toString().equals(goOrBack)
               && item.get("containerType").toString().equals(containerType)
         ).findAny().orElse(null);
         if(ObjectUtil.isNotEmpty(cbjnMap)){
            String currencyType = cbjnMap.get("currencyType").toString();
            BigDecimal fee = new BigDecimal(cbjnMap.get("fee").toString());
            if("1".equals(currencyType)){
               // 人民币，转美元
               String rate = RemitRateUtil.getDateRate2("1", (year+""), (month+""));
               if(StringUtils.isNotBlank(rate)){
                  fee = fee.divide(new BigDecimal(rate), 2, RoundingMode.HALF_DOWN);
               }
            }
            cbDecimal = cbDecimal.add(fee);
         }
         // 成本境外
         Map<String, Object> cbjwMap = cbjwList.stream().filter(item->
               item.get("lineId").toString().equals(lineId)
               && item.get("stationId").toString().equals(stationId)
               && item.get("containerType").toString().equals(containerType)
         ).findAny().orElse(null);
         if(ObjectUtil.isNotEmpty(cbjwMap)){
            String currencyType = cbjwMap.get("currencyType").toString();
            BigDecimal fee = new BigDecimal(cbjwMap.get("fee").toString());
            if("1".equals(currencyType)){
               // 人民币，转美元
               String rate = RemitRateUtil.getDateRate2("1", (year+""), (month+""));
               if(StringUtils.isNotBlank(rate)){
                  fee = fee.divide(new BigDecimal(rate), 2, RoundingMode.HALF_DOWN);
               }
            }
            cbDecimal = cbDecimal.add(fee);
         }
         // 给excel塞值
         writer.writeCellValue(2,i, containerTypeName);
         writer.writeCellValue(3,i, cbDecimal);
         writer.writeCellValue(4,i, srDecimal);
         String cell3 = TrainsEmailMainService.convertToColumnName(i , 3);
         String cell4 = TrainsEmailMainService.convertToColumnName(i , 4);
         writer.writeCellValue(5,i, new FormulaCellValue("="+cell4+"-"+cell3));
         writer.writeCellValue(6,i, spaceNum);
         String cell5 = TrainsEmailMainService.convertToColumnName(i , 5);
         String cell6 = TrainsEmailMainService.convertToColumnName(i , 6);
         writer.writeCellValue(7,i, new FormulaCellValue("="+cell5+"*"+cell6));
         // 线路、站点 合并单元格准备数据，和上一个单元格数据相同的放在一个map
         if (tempLineName == null || !tempLineName.equals(lineName)) {
            // 如果tempLineName为空（第一次迭代）或者name值改变了
            if (tempLineIndex != -1) {
               // 存储前一个name的范围和值
               Map<String, Object> rangeMap = new HashMap<>();
               rangeMap.put("lineName", tempLineName);
               rangeMap.put("startIndex", tempLineIndex);
               rangeMap.put("endIndex", i - 1);
               mergeLineList.add(rangeMap);
            }
            // 更新currentName和startIndex
               tempLineName = lineName;
            tempLineIndex = i;
         }
         String lineStationName = lineName + ",," + stationName;
         if (tempStationName == null || !tempStationName.equals(lineStationName)) {
            // 如果tempStationName为空（第一次迭代）或者name值改变了
            if (tempStationIndex != -1) {
               // 存储前一个name的范围和值
               Map<String, Object> rangeMap = new HashMap<>();
               rangeMap.put("stationName", tempStationName.split(",,")[1]);
               rangeMap.put("startIndex", tempStationIndex);
               rangeMap.put("endIndex", i - 1);
               mergeStationList.add(rangeMap);
            }
            // 更新currentName和startIndex
            tempStationName = lineStationName;
            tempStationIndex = i;
         }
      }
      // 处理最后一个name的范围（循环结束时不会进入if条件）
      if (tempLineName != null) {
         Map<String, Object> rangeMap = new HashMap<>();
         rangeMap.put("lineName", tempLineName);
         rangeMap.put("startIndex", tempLineIndex);
         rangeMap.put("endIndex", containerList.size() + 1);
         mergeLineList.add(rangeMap);
      }
      if (tempStationName != null) {
         Map<String, Object> rangeMap = new HashMap<>();
         rangeMap.put("stationName", tempStationName.split(",,")[1]);
         rangeMap.put("startIndex", tempStationIndex);
         rangeMap.put("endIndex", containerList.size() + 1);
         mergeStationList.add(rangeMap);
      }
      for (Map<String, Object> map : mergeLineList) {
         String lineName = map.get("lineName").toString();
         int startIndex = Integer.parseInt(map.get("startIndex").toString());
         int endIndex = Integer.parseInt(map.get("endIndex").toString());
         if(startIndex == endIndex){
            writer.writeCellValue(0,startIndex, lineName);
         }else{
            writer.merge(startIndex, endIndex, 0, 0, lineName, false);
         }
            
      }
      for (Map<String, Object> map : mergeStationList) {
         String stationName = map.get("stationName").toString();
         int startIndex = Integer.parseInt(map.get("startIndex").toString());
         int endIndex = Integer.parseInt(map.get("endIndex").toString());
         if(startIndex == endIndex){
            writer.writeCellValue(1,startIndex, stationName);
         }else{
            writer.merge(startIndex, endIndex, 1, 1, stationName, false);
         }
      }
      // 设置单元格样式 黄色背景
      CellStyle yellowStyle = StyleUtil.createCellStyle(writer.getWorkbook());
      yellowStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
      yellowStyle.setAlignment(HorizontalAlignment.CENTER);
      yellowStyle.setVerticalAlignment(VerticalAlignment.CENTER);
      yellowStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
      yellowStyle.setBorderBottom(BorderStyle.THIN);
      yellowStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
      yellowStyle.setBorderLeft(BorderStyle.THIN);
      yellowStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
      yellowStyle.setBorderRight(BorderStyle.THIN);
      yellowStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
      yellowStyle.setBorderTop(BorderStyle.THIN);
      yellowStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
      // 设置单元格样式 粉色背景
      CellStyle pinkStyle = StyleUtil.createCellStyle(writer.getWorkbook());
      pinkStyle.setFillForegroundColor(IndexedColors.ROSE.getIndex());
      pinkStyle.setAlignment(HorizontalAlignment.CENTER);
      pinkStyle.setVerticalAlignment(VerticalAlignment.CENTER);
      pinkStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
      pinkStyle.setBorderBottom(BorderStyle.THIN);
      pinkStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
      pinkStyle.setBorderLeft(BorderStyle.THIN);
      pinkStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
      pinkStyle.setBorderRight(BorderStyle.THIN);
      pinkStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
      pinkStyle.setBorderTop(BorderStyle.THIN);
      pinkStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
      //DataFormat dataFormat = writer.getWorkbook().createDataFormat();
      //pinkStyle.setDataFormat(dataFormat.getFormat("#,##0.00"));
      
      // 设置样式
      for (int j = 0; j < containerList.size(); j++) {
         writer.getSheet().getRow(j+2).getCell(3).setCellStyle(pinkStyle);
         writer.getSheet().getRow(j+2).getCell(6).setCellStyle(yellowStyle);
      }
      writer.getSheet().getRow(containerList.size()+2).getCell(0).setCellStyle(yellowStyle);
      writer.getSheet().getRow(containerList.size()+2).getCell(1).setCellStyle(yellowStyle);
      writer.getSheet().getRow(containerList.size()+2).getCell(2).setCellStyle(yellowStyle);
      writer.getSheet().getRow(containerList.size()+2).getCell(3).setCellStyle(yellowStyle);
      writer.getSheet().getRow(containerList.size()+2).getCell(4).setCellStyle(yellowStyle);
      writer.getSheet().getRow(containerList.size()+2).getCell(5).setCellStyle(yellowStyle);
      writer.getSheet().getRow(containerList.size()+2).getCell(6).setCellStyle(yellowStyle);
      writer.getSheet().getRow(containerList.size()+2).getCell(7).setCellStyle(yellowStyle);
      
      //response为HttpServletResponse对象
      response.setContentType("application/vnd.ms-excel;charset=utf-8");
      response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(name+".xlsx", "UTF-8"));
      out = response.getOutputStream();
      writer.flush(out, true);
   } finally {
      //关闭输出Servlet流
      IoUtil.close(out);
      //关闭writer，释放内存
      writer.close();
   }
}
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值