基于POI的Excel导出

1、后端依赖

 1 <dependencies>
 2     <dependency>
 3         <groupId>org.apache.poi</groupId>
 4         <artifactId>poi</artifactId>
 5         <version>5.2.3</version>
 6     </dependency>
 7     <dependency>
 8         <groupId>org.apache.poi</groupId>
 9         <artifactId>poi-ooxml</artifactId>
10         <version>5.2.3</version>
11     </dependency>
12 </dependencies>

2、前端

1  exportExcel(rows) {
2        window.location.href  = Environment.getReqDomain() + "/xxx/xxx/xxx/excelExport?param01=" + rows.param01 + '&param02=' + params02;
3  },

3、controller层

1
2     @GetMapping("/excelExport")
3     public void exportExcel(ExcelExportParams params, HttpServletResponse response) {
4         try {
5             corpBillService.exportExcel(params,response);
6         } catch (Exception e) {
7             LOGGER.error("接入单位对账excel导出失败 , ", e);
8         }
9     }

4、service层

  1 　　@Override
  2     public void exportExcel(ExcelExportParams params, HttpServletResponse response) throws IOException {
  3         List<ExcelExportDto> list = xxxMapper.qryExcelExportByxxx(params.param01(), params.param02());
  4         
  5         String name = list.get(0)==null?"":list.get(0).getName();
  6         Integer totalNum = list.stream().mapToInt(ExcelExportDto::getSuccessNum).sum();
  7         // 创建工作簿
  8         Workbook workbook = new XSSFWorkbook();
  9         // 创建工作表
 10         Sheet sheet = workbook.createSheet("数据报表");
 11 
 12         // 设置默认行高和列宽行高20磅
 13         sheet.setDefaultRowHeightInPoints(20);
 14         for (int i = 0; i < 5; i++) {
 15             // 列宽14磅，256是因为单位转换
 16             sheet.setColumnWidth(i, 14 * 256);
 17         }
 18 
 19         // 设置表头样式
 20         CellStyle headerStyle = ExcelStyleUtil.createHeaderCellStyle(workbook);
 21         Row headerRow = sheet.createRow(0);
 22         String[] headers = {"月份", "单位名称", "业务系统","模板名称", "发送成功量"};
 23         for (int i = 0; i < headers.length; i++) {
 24             Cell cell = headerRow.createCell(i);
 25             cell.setCellStyle(headerStyle);
 26             cell.setCellValue(headers[i]);
 27         }
 28 
 29         // 填充数据并设置样式
 30         CellStyle dataCellStyle = ExcelStyleUtil.createDataCellStyle(workbook);
 31         for (int i = 0; i < list.size(); i++) {
 32             ExcelExportDto dto = list.get(i);
 33             Row row = sheet.createRow(i + 1);
 34             row.setHeightInPoints(20);
 35             // 月份
 36             row.createCell(0).setCellValue(dto.getMonth());
 37             // 单位名称
 38             row.createCell(1).setCellValue(dto.getCorpName());
 39             // 业务系统
 40             row.createCell(2).setCellValue(dto.getSystemName());
 41             // 模板名称
 42             row.createCell(3).setCellValue(dto.getTemplateName());
 43             // 发送成功量
 44             row.createCell(4).setCellValue(dto.getSendSuccessNum());
 45             // 设置每一格的数据样式
 46             for (int j = 0; j < 5; j++) {
 47                 row.getCell(j).setCellStyle(dataCellStyle);
 48             }
 49         }
 50 
 51         // 在末尾追加汇总行
 52         int lastRowNum = sheet.getLastRowNum();
 53         CellStyle sumCellStyle = ExcelStyleUtil.createSumCellStyle(workbook);
 54         Row sumRow = sheet.createRow(lastRowNum + 1);
 55 
 56 
 57         // 合并第二、三、四列
 58         CellRangeAddress mergedRegion = new CellRangeAddress(lastRowNum + 1, lastRowNum + 1, 1, 3);
 59         sheet.addMergedRegion(mergedRegion);
 60 
 61         // 填充汇总行的数据
 62         Cell cell = sumRow.createCell(0);
 63         cell.setCellValue("合计");
 64         cell.setCellStyle(sumCellStyle);
 65 
 66         // 为合并后的第二、三、四列添加数据
 67         Cell cell01 = sumRow.createCell(1);
 68         cell01.setCellValue("");
 69         cell01.setCellStyle(sumCellStyle);
 70         // 为合并后的第二、三、四列添加数据
 71         Cell cell02 = sumRow.createCell(2);
 72         cell02.setCellValue("");
 73         cell02.setCellStyle(sumCellStyle);
 74         // 为合并后的第二、三、四列添加数据
 75         Cell cell03 = sumRow.createCell(3);
 76         cell03.setCellValue("");
 77         cell03.setCellStyle(sumCellStyle);
 78         Cell cell04 = sumRow.createCell(4);
 79         cell04.setCellValue(totalNum);
 80         cell04.setCellStyle(sumCellStyle);
 81 
 82 
 83         // 创建输出流
 84         ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
 85 
 86         // 写入工作簿到输出流
 87         try {
 88             workbook.write(outputStream);
 89         } catch (IOException e) {
 90             throw new RuntimeException(e);
 91         } finally {
 92             workbook.close();
 93         }
 94 
 95         // 设置响应头
 96         response.setContentType("application/vnd.ms-excel");
 97         response.setCharacterEncoding("utf-8");
 98         String fileName = "接入单位对账-" + System.currentTimeMillis() + ".xlsx";
 99         try {
100             response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
101         } catch (UnsupportedEncodingException e) {
102             throw new RuntimeException("导出数据报表失败", e);
103         }
104 
105         // 获取输出流中的字节数组
106         byte[] bytes = outputStream.toByteArray();
107 
108         // 将字节数组写入响应流
109         ServletOutputStream servletOutputStream = response.getOutputStream();
110         servletOutputStream.write(bytes);
111         servletOutputStream.flush();
112         servletOutputStream.close();
113     }

5、service层import依赖

1 import org.apache.poi.ss.usermodel.*;
2 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
3 
4 import java.io.FileOutputStream;
5 import java.io.IOException;