Web 系統常需導出多筆資料為 Excel，若逐個下載體驗極差。本文示範 **AEAI DP 低代碼平台** 實作「一次導出多 Excel 並打包 ZIP 下載」的完整流程，核心邏輯可移植至 Spring Boot、Servlet 等標準 Java 專案。
前端按鈕 → Handler 組裝資料 → FileExportHelper.exportFile() 產生多個 Excel →
ZipHelper.doZip() 壓縮 → ZipHelper.downZip() 串流下載 → ZipHelper.deleteZip() 清理暫存
<a href="javascript:exportExcel()" class="btn btn-primary">
<i class="fa fa-download"></i> 導出 Excel (ZIP)
</a>
<script>
function exportExcel() {
// 提交表單或 AJAX 呼叫 Handler action
document.forms[0].action = "EmployeeManageListHandler?action=exportExcelFile";
document.forms[0].submit();
}
</script>
public void exportExcelFile() throws Exception {
// 1. 查詢資料 (分頁/條件)
List list = employeeDAO.findAll();
// 2. 組裝導出資料結構 (對應 FTL 模板變數)
Map root = new HashMap<>();
root.put("employeeList", list);
root.put("exportTime", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
// 3. 呼叫平台 FileExportHelper 產生 Excel (可產生多個 Sheet/檔案)
String templatePath = "template/EmployeeInfos.ftl"; // Freemarker 模板
String exportDir = getRealPath("/export/temp"); // 暫存目錄
String zipName = "EmployeeExport_" + System.currentTimeMillis() + ".zip";
// 產生單一 Excel (平台會依模板迭代產生)
FileExportHelper.exportFile(templatePath, root, exportDir, "EmployeeList.xls");
// 4. 壓縮、下載、清理
ZipHelper.doZip(exportDir, zipName); // 將目錄下所有檔案打包
ZipHelper.downZip(response, exportDir, zipName); // 串流輸出至瀏覽器
ZipHelper.deleteZip(exportDir, zipName); // 刪除暫存 ZIP 釋放空間
}
<?xml version="1.0" encoding="UTF-8"?>
<workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" ...>
<styles>...</styles>
<worksheet name="員工名單">
<table>
<row><cell><data ss:Type="String">員工編號</data></cell>
<cell><data ss:Type="String">姓名</data></cell>
<cell><data ss:Type="String">部門</data></cell>
<cell><data ss:Type="String">職稱</data></cell>
<cell><data ss:Type="String">入職日期</data></cell></row>
<#list employeeList as emp>
<row>
<cell><data ss:Type="String">${emp.id!""}</data></cell>
<cell><data ss:Type="String">${emp.name!""}</data></cell>
<cell><data ss:Type="String">${emp.deptName!""}</data></cell>
<cell><data ss:Type="String">${emp.title!""}</data></cell>
<cell><data ss:Type="String">${emp.hireDate?string("yyyy-MM-dd")}</data></cell>
</row>
</table>
</worksheet>
</workbook>
public class FileExportHelper {
public static void exportFile(String templatePath, Map root,
String outputDir, String fileName) throws Exception {
Configuration cfg = new Configuration(Configuration.VERSION_2_3_31);
cfg.setDirectoryForTemplateLoading(new File(templatePath).getParentFile());
cfg.setDefaultEncoding("UTF-8");
Template template = cfg.getTemplate(new File(templatePath).getName());
File outDir = new File(outputDir);
if (!outDir.exists()) outDir.mkdirs();
File outFile = new File(outDir, fileName);
try (Writer writer = new BufferedWriter(new OutputStreamWriter(
new FileOutputStream(outFile), "UTF-8"))) {
template.process(root, writer);
}
}
}
public class ZipHelper {
/** 將目錄下所有檔案壓縮為 ZIP (不含目錄本身) */
public static void doZip(String sourceDir, String zipFileName) throws IOException {
File src = new File(sourceDir);
File zipFile = new File(sourceDir, zipFileName);
try (FileOutputStream fos = new FileOutputStream(zipFile);
ZipOutputStream zos = new ZipOutputStream(new BufferedOutputStream(fos))) {
zipDirectory(src, src, zos);
}
}
private static void zipDirectory(File root, File current, ZipOutputStream zos) throws IOException {
for (File file : current.listFiles()) {
if (file.isDirectory()) {
zipDirectory(root, file, zos);
} else {
String entryName = root.toPath().relativize(file.toPath()).toString();
try (FileInputStream fis = new FileInputStream(file);
BufferedInputStream bis = new BufferedInputStream(fis)) {
ZipEntry entry = new ZipEntry(entryName);
zos.putNextEntry(entry);
byte[] buffer = new byte[8192];
int len;
while ((len = bis.read(buffer)) > 0) {
zos.write(buffer, 0, len);
}
zos.closeEntry();
}
}
}
}
/** 串流下載 ZIP 至瀏覽器 (不載入整個檔案到記憶體) */
public static void downZip(HttpServletResponse response, String dir, String zipName) throws IOException {
File zipFile = new File(dir, zipName);
response.setContentType("application/zip");
response.setHeader("Content-Disposition", "attachment; filename=\"" + zipName + "\"");
response.setContentLengthLong(zipFile.length());
try (FileInputStream fis = new FileInputStream(zipFile);
BufferedInputStream bis = new BufferedInputStream(fis);
ServletOutputStream sos = response.getOutputStream()) {
byte[] buffer = new byte[8192];
int len;
while ((len = bis.read(buffer)) > 0) {
sos.write(buffer, 0, len);
}
sos.flush();
}
}
/** 刪除暫存 ZIP 檔案 */
public static void deleteZip(String dir, String zipName) {
new File(dir, zipName).delete();
// 可選：刪除整個暫存目錄
// FileUtils.deleteDirectory(new File(dir));
}
}
| 優化點 | 做法 | 效果 |
|---|---|---|
| 大資料量導出 | 使用 SXSSF (POI 流式寫入) 或分批產生多個 Excel 再打包 | 避免 OOM，記憶體恆定 |
| ZIP 壓縮 | ZipOutputStream 串流寫入，不將整個 ZIP 載入記憶體 | 支援 GB 級打包 |
| 下載傳輸 | ServletOutputStream 串流輸出，設定 Content-Length | 瀏覽器顯示進度、支援斷點續傳 |
| 暫存清理 | 下載完成後立即刪除暫存檔，或排程清理過期目錄 | 防止磁碟爆滿 |
| 非同步處理 | 大檔案導出改為異步任務 (前端輪詢/下載連結)，避免阻塞請求執行緒 | 提升併發承載 |
@GetMapping("/export/employees.zip")
public void exportEmployees(HttpServletResponse response) throws Exception {
String tempDir = System.getProperty("java.io.tmpdir") + "/export_" + UUID.randomUUID();
String zipName = "employees.zip";
try {
// 1. 查詢資料 (分頁游標避免載入全部)
// 2. 產生 Excel (SXSSFWorkbook)
// 3. 壓縮
ZipHelper.doZip(tempDir, zipName);
// 4. 下載
ZipHelper.downZip(response, tempDir, zipName);
} finally {
// 5. 清理
ZipHelper.deleteZip(tempDir, zipName);
FileUtils.deleteDirectory(new File(tempDir));
}
}
**「資料查詢 → 模板渲染 Excel → 串流壓縮 ZIP → 串流下載 → 即時清理」** 是導出大量 Excel 的標準最佳實踐。AEAI DP 平台將此流程封裝為 `FileExportHelper`/`ZipHelper`，標準 Java 專案可直接複製 `ZipHelper` 核心邏輯，搭配 Apache POI (SXSSF) + Freemarker/Thymeleaf 實現同等功能。
