Java JDBC 實現 Excel/CSV 導入導出完整方案：從資料庫到檔案的雙向流暢傳輸

架構概覽

核心模組：

• DBQueryData：封裝資料庫連線參數與查詢條件
• ConnectionManager：單例模式管理 JDBC 連線（MySQL）
• DBHelper：執行查詢/批次插入，將 ResultSet 轉為 DBList（欄位名 + 資料矩陣）
• ReadExcel / CSVUtils：Apache POI 解析 .xls/.xlsx/.csv，轉為物件清單寫入資料庫
• Controller：Spring MVC 端點，處理 Multipart 上傳與檔案流下載

關鍵實作細節

1. 查詢結果統一封裝（DBList）

public class DBList {
    private List rowTitle;      // 欄位名
    private List> valueList; // 資料列
    private int rowCount, colCount;
    // getter/setter 省略
}

2. Excel 導出（支援超 65535 列自動分 Sheet）

public static void exportToExcel(HttpServletRequest req, HttpServletResponse resp, DBQueryData q) {
    DBList data = new DBHelper().search(q);
    Workbook wb = createWorkbook(data, q.getTableName());

    String fileName = q.getTableName() + ".xls";
    // 瀏覽器編碼相容
    String ua = req.getHeader("User-Agent");
    boolean isChrome = ua != null && ua.contains("Chrome");
    try {
        fileName = isChrome ? URLEncoder.encode(fileName, "UTF-8")
                            : new String(fileName.getBytes("GB2312"), "ISO8859-1");
    } catch (UnsupportedEncodingException ignore) {}

    resp.setHeader("Content-Disposition", "attachment; filename=" + fileName);
    resp.setContentType("application/vnd.ms-excel");
    wb.write(resp.getOutputStream());
}

private static Workbook createWorkbook(DBList data, String name) {
    Workbook wb = new HSSFWorkbook(); // .xls 格式，若需 .xlsx 改 XSSFWorkbook
    int totalRows = data.getRowCount();
    if (totalRows > 65535) {
        int sheets = (totalRows + 65534) / 65535;
        for (int i = 0; i < sheets; i++) {
            Sheet sheet = wb.createSheet(name + (i + 1));
            writeSheet(sheet, data, i * 65535, Math.min((i + 1) * 65535, totalRows));
        }
    } else {
        Sheet sheet = wb.createSheet(name);
        writeSheet(sheet, data, 0, totalRows);
    }
    return wb;
}

3. Excel 導入（POI 區分 .xls / .xlsx，統一轉為物件清單）

• 解析首行作為欄位名
• 逐儲存格根據 CellType 轉換：字串、數值（含日期/科學記號）、布林、空值
• 組裝 List> 交給 DBHelper.SQLInsert() 批次入庫

4. CSV 導入導出（輕量、跨平台、支援 BOM 防中文亂碼）

// 導出關鍵：寫入 UTF-8 BOM (EF BB BF)
outputStream.write(new byte[]{(byte)0xEF, (byte)0xBB, (byte)0xBF});
outputStream.write(csvContent.getBytes("UTF-8"));

// 導入：BufferedReader 逐行讀取，split(",") 簡單解析
// 注意：正式專案建議使用 Apache Commons CSV 或 OpenCSV 處理引號、逗號轉義

Controller 整合範例

@PostMapping("/upload")
public Map> upload(HttpServletRequest req, DBQueryData q) throws IOException {
    MultipartFile file = ((MultipartHttpServletRequest) req).getFile("file");
    String ext = FilenameUtils.getExtension(file.getOriginalFilename()).toLowerCase();
    InputStream is = file.getInputStream();

    if ("xls".equals(ext) || "xlsx".equals(ext)) {
        return ReadExcel.readExcel(is, file.getOriginalFilename(), q);
    } else if ("csv".equals(ext)) {
        return CSVUtils.importCsv(is, q);
    }
    throw new IllegalArgumentException("不支援的檔案類型");
}

@GetMapping("/export/excel")
public void exportExcel(HttpServletRequest req, HttpServletResponse resp, DBQueryData q) {
    exportToExcel(req, resp, q);
}

@GetMapping("/export/csv")
public void exportCsv(HttpServletResponse resp, DBQueryData q) throws Exception {
    CSVUtils.exportCsv(resp, q);
}

生產環境避坑指南

技術債與重構建議

1. 移除 ConnectionManager 單例，改用 DataSource (HikariCP) + Spring 事務管理
2. 抽象 FileParser 介面，支援策略模式擴充新格式 (JSON, XML, Parquet)
3. 導入異步處理：大檔案上傳回傳任務 ID，前端輪詢進度，避免 HTTP 超時
4. 資料驗證層：JSR 303 Bean Validation 註解於導入 DTO，錯誤資料寫入錯誤表供人工複核

總結

JDBC + POI + CSV 組合構建了資料庫與試算表雙向互通的基礎通道。掌握流式處理、編碼相容、批次寫入優化，即可勝任絕大多數企業級匯入匯出需求。