导出 exl 按照已存在的固定格式

最新推荐文章于 2024-11-05 14:54:33 发布

wangchuanjian

最新推荐文章于 2024-11-05 14:54:33 发布

阅读量630

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/wangchuanjian/article/details/43450785

本文介绍了一种使用Java和Excel进行数据报告自动化的方法。通过读取模板文件并根据指定的数据填充，实现批量生成带有汇总和小计的报表。具体包括如何复制模板行、设置单元格值及公式等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

//获取已经存在exl 的地址根据存在的exl格式创建显示数据（其实显示成想要的格式就是复制固定的格式然后填充数据，看到了什么位置会发生变化重新复制一行填充数据）

HSSFWorkbook workbook=new HSSFWorkbook(request.getSession(true).getServletContext().getResourceAsStream("/template/distributerCollect.xls"));

private void getDistributeReport(HSSFWorkbook workbook,

       Map<String, Object> filterMap,List<DistributerReportDto> list){
       HSSFSheet sheet = workbook.getSheetAt(0);
       String beginDate = map.get("beginDate");
       String endDate = map.get("endDate");
       int rowNum = list.size();
       int index = 4;
        int c=5;
       //判断是否是相同的
       String isSameName="null";
       //记录小计位置
       List<Integer> subtotal=new ArrayList<Integer>();
       for(int i=0;i < list.size(); i++){
           DistributerReportDto distributerDto = list.get(i);
           if(!distributerDto.getSupinstno().equals(isSameName)&&!isSameName.equals("null")){
               //复制要写入数据的那一列
               ExcelUtils.copyRow(workbook, sheet, index+1, index);
               HSSFRow row = sheet.getRow(index);
               String[] excelTitle = {"A","B","C","D","E","F","G","H","I","J","K","L"};
               for(int a = 3;a<row.getPhysicalNumberOfCells();a++){
                   if(a == 10){
                       continue;
                   }
                   String titleChar = excelTitle[a];
                   HSSFCell cell = row.getCell(a);
                   if(a==3){
                       cell.setCellValue(isSameName);
                   }
                   if(a==4){
                       cell.setCellValue(beginDate);
                   }
                   if(a==5){
                       cell.setCellValue(endDate);
                   }
                   if(a>5){
                   String fromula = rowNum == 0 ? "0" : "SUM("+titleChar+""+c+":"+titleChar+""+(index)+")";
                   cell.setCellFormula(fromula);
                   }
               }
               subtotal.add(index+1);//记录小计位置（记录的写值的位置要+1）
               index++;
               c=index+1;//记录小计开始的位置
           }
           ExcelUtils.copyRow(workbook, sheet, 3, index);
           HSSFRow row = sheet.getRow(index);
           Object[] obj = this.objArray(distributerDto);
           ExcelUtils.setdefaultRow(row, obj);
           isSameName=distributerDto.getSupinstno();
           index++;
           //获得最后一个小计
           if(list.size()-1==i){
               HSSFRow lastRowSum = sheet.getRow(index);
               String[] excelTitle = {"A","B","C","D","E","F","G","H","I","J","K","L"};
               for(int a = 3;a<lastRowSum.getPhysicalNumberOfCells();a++){
                   if(a == 10){
                       continue;
                   }
                   String titleChar = excelTitle[a];
                   HSSFCell cell = lastRowSum.getCell(a);
                   if(a==3){
                       cell.setCellValue(isSameName);
                   }
                   if(a==4){
                       cell.setCellValue(beginDate);
                   }
                   if(a==5){
                       cell.setCellValue(endDate);
                   }
                   if(a>5){
                       String fromula = rowNum == 0 ? "0" : "SUM("+titleChar+""+c+":"+titleChar+""+(index)+")";
                       cell.setCellFormula(fromula);
                   }
               }
           }
       }
       //写入汇总行
       int lastRowNum = index+1;
       HSSFRow lastRow = sheet.getRow(lastRowNum);
       String[] excelTitle = {"A","B","C","D","E","F","G","H","I","J","K","L"};
       for(int i = 4;i<lastRow.getPhysicalNumberOfCells();i++){
           if(i == 10){
               continue;
           }
           String titleChar = excelTitle[i];
           HSSFCell cell = lastRow.getCell(i);
           if(i==4){
               cell.setCellValue(beginDate);
           }
           if(i==5){
               cell.setCellValue(endDate);
           }
           if(i>5){
           //SUM("+titleChar+"4:"+titleChar+""+(lastRowNum-2)+")
           String toalSum="SUM("+titleChar+""+(index)+":"+titleChar+""+(index)+")";
           for(int j=0;j<subtotal.size();j++){
               //所以小计的和为总计
               toalSum=toalSum+"+"+"SUM("+titleChar+""+subtotal.get(j)+":"+titleChar+""+(subtotal.get(j))+")";
           }
           String fromula = rowNum == 0 ? "0" : toalSum;
           cell.setCellFormula(fromula);
           }
       }
       sheet.shiftRows(4, lastRowNum, -1);
   }