您现在的位置：程式碼之家 >> 編程語言 > >> Java 程式設計 >> Java 相關知識

Java Web Excel 導出下載元件：基於 Apache POI 的通用工具類實作

編輯：Java 相關知識

需求背景與技術選型

專案需支援動態選欄導出 Excel、瀏覽器彈出下載。採用 Spring MVC + MyBatis 架構，選用 Apache POI (HSSF/XSSF) 實作，支援 .xls (Excel 97-2003) 與 .xlsx (Excel 2007+) 格式。

通用導出工具類 `ExportExcelUtil`

核心設計思路

泛型支援：<T> 適配任意實體類
反射映射：依欄位名動態呼叫 getXxx() 取值
表頭與資料樣式分離：標題行黑體置中、資料行宋體置中、邊框統一細線
自動欄寬：autoSizeColumn() 依內容調整
序號欄自動生成：第一欄固定為流水號

完整原始碼

package com.rixin.common.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.Collection;
import java.util.Iterator;

public class ExportExcelUtil {

  public static <T> HSSFWorkbook export(HttpServletResponse response,
                                        String fileName,
                                        String[] excelHeader,
                                        Collection<T> dataList) throws Exception {

    // 1. 設定回應標頭：觸發瀏覽器下載
    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-disposition",
        "attachment;filename=" + URLEncoder.encode(fileName + ".xls", "UTF-8"));

    // 2. 建立 Workbook、Sheet、樣式
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet(fileName);

    HSSFCellStyle titleStyle = createTitleStyle(wb);
    HSSFCellStyle dataStyle = createDataStyle(wb);

    // 3. 解析表頭陣列 "顯示名#欄位名"
    String[] titleArray = new String[excelHeader.length];
    String[] fieldArray = new String[excelHeader.length];
    for (int i = 0; i < excelHeader.length; i++) {
      String[] parts = excelHeader[i].split("#");
      titleArray[i] = parts[0];
      fieldArray[i] = parts[1];
    }

    // 4. 建立標題行（含序號欄）
    HSSFRow row = sheet.createRow(0);
    createCell(row, 0, "序號", titleStyle, sheet);
    for (int i = 0; i < titleArray.length; i++) {
      createCell(row, i + 1, titleArray[i], titleStyle, sheet);
    }

    // 5. 遍歷資料集，反射取值填入
    Iterator<T> it = dataList.iterator();
    int rowIndex = 0;
    while (it.hasNext()) {
      rowIndex++;
      row = sheet.createRow(rowIndex);
      createCell(row, 0, rowIndex, dataStyle, sheet); // 序號
      
      T entity = it.next();
      Class<?> clazz = entity.getClass();
      for (int i = 0; i < fieldArray.length; i++) {
        String fieldName = fieldArray[i];
        String getter = "get" + Character.toUpperCase(fieldName.charAt(0)) + fieldName.substring(1);
        try {
          Method method = clazz.getMethod(getter);
          Object value = method.invoke(entity);
          createCell(row, i + 1, value != null ? value.toString() : "", dataStyle, sheet);
        } catch (Exception e) {
          createCell(row, i + 1, "", dataStyle, sheet);
        }
      }
    }

    // 6. 輸出至回應流
    OutputStream os = response.getOutputStream();
    wb.write(os);
    wb.close();
    os.flush();
    os.close();
    return wb;
  }

  // --- 樣式工廠方法 ---
  private static HSSFCellStyle createTitleStyle(HSSFWorkbook wb) {
    HSSFCellStyle style = wb.createCellStyle();
    setBorders(style);
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    Font font = wb.createFont();
    font.setFontHeightInPoints((short) 15);
    font.setFontName("黑體");
    style.setFont(font);
    return style;
  }

  private static HSSFCellStyle createDataStyle(HSSFWorkbook wb) {
    HSSFCellStyle style = wb.createCellStyle();
    setBorders(style);
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    Font font = wb.createFont();
    font.setFontHeightInPoints((short) 12);
    font.setFontName("宋體");
    style.setFont(font);
    return style;
  }

  private static void setBorders(HSSFCellStyle style) {
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
  }

  private static void createCell(HSSFRow row, int colIndex, Object value,
                                 HSSFCellStyle style, HSSFSheet sheet) {
    HSSFCell cell = row.createCell(colIndex);
    cell.setCellValue(value.toString());
    cell.setCellStyle(style);
    sheet.autoSizeColumn(colIndex);
  }
}

Controller 整合範例

@Controller
@RequestMapping("/project")
public class ProjectController {

  @Autowired private ProjectService projectService;

  @GetMapping("/exportProject.do")
  public void exportProject(HttpServletResponse response,
                            @RequestParam String export) { // export="項目名稱#name,負責人#owner"
    String[] excelHeader = export.split(",");
    List<Project> list = projectService.getProjects(null);
    try {
      ExportExcelUtil.export(response, "專案清單", excelHeader, list);
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

前端 JSP 勾選欄位

<div class="am-input-group am-u-sm-4">
  <span class="am-input-group-label">
    <input type="checkbox" value="專案名稱#name" name="export" checked>
  </span>
  <input type="text" value="專案名稱" disabled>
</div>
<div class="am-input-group am-u-sm-4">
  <span class="am-input-group-label">
    <input type="checkbox" value="負責人#owner" name="export">
  </span>
  <input type="text" value="負責人" disabled>
</div>
<button onclick="submitExport()">導出 Excel

<script>
function submitExport() {
  var checked = $('input[name="export"]:checked').map(function() { return this.value; }).get().join(',');
  location.href = '${pageContext.request.contextPath}/project/exportProject.do?export=' + encodeURIComponent(checked);
}
</script>

進階擴充建議

需求	擴充方向
支援 `.xlsx` 大檔案	改用 `SXSSFWorkbook` (串流模式)，避免大數據 OOM
複雜樣式（合併儲存格、條件格式）	引入樣式配置 DSL 或模板檔案 `.xlsx` 做範本填充
日期/數字格式化	擴充 Header 格式：`顯示名#欄位名#formatPattern`
多 Sheet 導出	方法簽名改為 `Map<String, Collection<?>>`，Key 為 Sheet 名
異步大檔下載	Controller 返回任務 ID，前端輪詢 / 下載中心