当前位置：首页 > ops >正文

Java后台生成多个Excel并用Zip打包下载

ops 2025/8/18 21:58:54

下面是一个完整的实现方案，可以生成多个Excel文件，将它们放入不同的目录结构中，然后打包成ZIP文件供用户下载。

1. 添加必要的依赖

首先确保你的项目中包含以下依赖（Maven示例）：

<!-- Apache POI for Excel -->
<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>5.2.3</version>
</dependency>
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.2.3</version>
</dependency><!-- Zip工具 -->
<dependency><groupId>org.apache.commons</groupId><artifactId>commons-compress</artifactId><version>1.21</version>
</dependency>

2. 实现代码

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.commons.compress.archivers.zip.ZipArchiveEntry;
import org.apache.commons.compress.archivers.zip.ZipArchiveOutputStream;import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.Map;public class ExcelZipExporter {public void exportMultipleExcelsAsZip(HttpServletResponse response) throws IOException {// 设置响应头response.setContentType("application/zip");response.setHeader("Content-Disposition", "attachment; filename=\"reports.zip\"");// 创建Excel文件集合，key为文件路径，value为工作簿Map<String, Workbook> excelFiles = new HashMap<>();// 生成第一个Excel - 放在根目录Workbook workbook1 = generateExcel("销售数据", new String[]{"产品", "数量", "金额"}, new Object[][]{{"产品A", 100, 5000},{"产品B", 150, 7500},{"产品C", 200, 10000}});excelFiles.put("sales_report.xlsx", workbook1);// 生成第二个Excel - 放在monthly目录Workbook workbook2 = generateExcel("月度统计", new String[]{"月份", "收入", "支出"}, new Object[][]{{"一月", 50000, 30000},{"二月", 55000, 32000},{"三月", 60000, 35000}});excelFiles.put("monthly/financial_report.xlsx", workbook2);// 生成第三个Excel - 放在yearly目录Workbook workbook3 = generateExcel("年度汇总", new String[]{"年份", "总收入", "总利润"}, new Object[][]{{"2020", 500000, 150000},{"2021", 550000, 180000},{"2022", 600000, 200000}});excelFiles.put("yearly/summary_report.xlsx", workbook3);// 将Excel文件打包成ZIP并输出到响应流try (OutputStream out = response.getOutputStream();ZipArchiveOutputStream zos = new ZipArchiveOutputStream(out)) {for (Map.Entry<String, Workbook> entry : excelFiles.entrySet()) {// 创建ZIP条目ZipArchiveEntry zipEntry = new ZipArchiveEntry(entry.getKey());zos.putArchiveEntry(zipEntry);// 将Excel工作簿写入ZIP条目ByteArrayOutputStream baos = new ByteArrayOutputStream();entry.getValue().write(baos);zos.write(baos.toByteArray());// 关闭当前条目zos.closeArchiveEntry();// 关闭工作簿entry.getValue().close();}// 完成ZIP文件zos.finish();}}/*** 生成Excel工作簿* @param sheetName 工作表名称* @param headers 表头* @param data 数据* @return 生成的Workbook对象*/private Workbook generateExcel(String sheetName, String[] headers, Object[][] data) {Workbook workbook = new XSSFWorkbook();Sheet sheet = workbook.createSheet(sheetName);// 创建表头行Row headerRow = sheet.createRow(0);for (int i = 0; i < headers.length; i++) {Cell cell = headerRow.createCell(i);cell.setCellValue(headers[i]);// 设置表头样式CellStyle headerStyle = workbook.createCellStyle();Font font = workbook.createFont();font.setBold(true);headerStyle.setFont(font);cell.setCellStyle(headerStyle);}// 填充数据for (int i = 0; i < data.length; i++) {Row row = sheet.createRow(i + 1);for (int j = 0; j < data[i].length; j++) {Object value = data[i][j];Cell cell = row.createCell(j);if (value instanceof String) {cell.setCellValue((String) value);} else if (value instanceof Number) {cell.setCellValue(((Number) value).doubleValue());}}}// 自动调整列宽for (int i = 0; i < headers.length; i++) {sheet.autoSizeColumn(i);}return workbook;}
}

3. 控制器调用示例

import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;import javax.servlet.http.HttpServletResponse;
import java.io.IOException;@RestController
@RequestMapping("/api/reports")
public class ReportController {private final ExcelZipExporter excelZipExporter;public ReportController(ExcelZipExporter excelZipExporter) {this.excelZipExporter = excelZipExporter;}@GetMapping("/download")public void downloadExcelReports(HttpServletResponse response) throws IOException {excelZipExporter.exportMultipleExcelsAsZip(response);}
}