JXL 庫讀寫 Excel 完整實戰：導出導入 .xls 格式報表

JXL 庫簡介與適用場景

JXL (Java Excel API) 是經典的純 Java 操作 Excel 庫，僅支援 .xls (Excel 97-2003) 格式。優點：輕量、無原生依賴、API 簡單。缺點：不支援 .xlsx、大檔案效能差、停止維護。新專案建議用 Apache POI。

Maven 依賴

<dependency>
    <groupId>net.sourceforge.jexcelapi</groupId>
    <artifactId>jxl</artifactId>
    <version>2.6.12</version>
</dependency>

資料模型類

public class PersonVo {
    private String id;
    private String name;
    private String sex;
    private String age;
    // Getter/Setter/建構子/toString 省略
}

完整導出實現 (支援樣式、欄寬、分頁下載)

import jxl.*;
import jxl.format.*;
import jxl.write.*;
import java.io.*;
import java.util.*;

public class ExcelExporter {

    public void exportPersons(List persons, String filePath) throws IOException, WriteException {
        // 1. 建立樣式：標題欄置中、粗體、邊框
        WritableCellFormat headerFormat = new WritableCellFormat();
        headerFormat.setAlignment(Alignment.CENTRE);
        headerFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
        headerFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
        WritableFont headerFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
        headerFormat.setFont(headerFont);
        headerFormat.setBackground(Colour.GRAY_25);

        // 2. 資料列樣式：靠左、邊框
        WritableCellFormat dataFormat = new WritableCellFormat();
        dataFormat.setAlignment(Alignment.LEFT);
        dataFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
        dataFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
        dataFormat.setWrap(true); // 自動換行

        // 3. 建立工作簿與工作表
        File file = new File(filePath);
        try (WritableWorkbook workbook = Workbook.createWorkbook(file)) {
            WritableSheet sheet = workbook.createSheet("人員名單", 0);

            // 4. 設定欄寬
            sheet.setColumnView(0, 8);   // ID
            sheet.setColumnView(1, 20);  // 姓名
            sheet.setColumnView(2, 10);  // 性別
            sheet.setColumnView(3, 8);   // 年齡

            // 5. 寫入標題列
            String[] headers = {"ID", "姓名", "性別", "年齡"};
            for (int i = 0; i < headers.length; i++) {
                sheet.addCell(new Label(i, 0, headers[i], headerFormat));
            }

            // 6. 寫入資料列
            for (int row = 0; row < persons.size(); row++) {
                PersonVo p = persons.get(row);
                int dataRow = row + 1; // 第 0 列是標題
                sheet.addCell(new Label(0, dataRow, p.getId(), dataFormat));
                sheet.addCell(new Label(1, dataRow, p.getName(), dataFormat));
                sheet.addCell(new Label(2, dataRow, p.getSex(), dataFormat));
                sheet.addCell(new Label(3, dataRow, p.getAge(), dataFormat));
            }

            // 7. 必須呼叫 write() 才會寫入磁碟
            workbook.write();
        }
    }

    // Web 環境下載版本
    public void exportForDownload(HttpServletResponse response, List persons, String fileName) 
            throws IOException, WriteException {
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", 
            "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));

        try (OutputStream out = response.getOutputStream();
             WritableWorkbook workbook = Workbook.createWorkbook(out)) {
            // 同樣建立 sheet、寫入資料...
            workbook.write(); // 直接寫到 response output stream
        }
    }
}

完整導入實現 (支援多 Sheet、型別轉換、錯誤行跳過)

public class ExcelImporter {

    public List importPersons(String filePath) throws IOException, BiffException {
        List result = new ArrayList<>();
        File file = new File(filePath);

        try (Workbook workbook = Workbook.getWorkbook(file)) {
            // 取得第一個工作表 (索引 0)
            Sheet sheet = workbook.getSheet(0);
            int rows = sheet.getRows();
            int cols = sheet.getColumns();

            if (cols < 4) {
                throw new IllegalArgumentException("Excel 欄數不足，至少需要 4 欄");
            }

            // 從第 1 列開始 (跳過標題)
            for (int row = 1; row < rows; row++) {
                try {
                    Cell[] cells = sheet.getRow(row);
                    // 跳過空行
                    if (isEmptyRow(cells)) continue;

                    PersonVo person = new PersonVo();
                    person.setId(getCellString(cells, 0));
                    person.setName(getCellString(cells, 1));
                    person.setSex(getCellString(cells, 2));
                    person.setAge(getCellString(cells, 3));
                    result.add(person);
                } catch (Exception e) {
                    System.err.println("第 " + (row+1) + " 行解析失敗: " + e.getMessage());
                    // 記錄日誌，繼續處理下一行
                }
            }
        }
        return result;
    }

    private boolean isEmptyRow(Cell[] cells) {
        return Arrays.stream(cells).allMatch(c -> c.getContents().trim().isEmpty());
    }

    private String getCellString(Cell[] cells, int index) {
        if (index >= cells.length) return "";
        return cells[index].getContents().trim();
    }
}

關鍵技術點與避坑指南

1. JXL 日期處理

// 寫入日期
import jxl.write.DateTime;
import jxl.write.DateFormat;

WritableCellFormat dateFormat = new WritableCellFormat(new DateFormat("yyyy-MM-dd"));
sheet.addCell(new DateTime(col, row, new Date(), dateFormat));

// 讀取日期
import jxl.DateCell;
Cell cell = sheet.getCell(col, row);
if (cell.getType() == CellType.DATE) {
    DateCell dc = (DateCell) cell;
    Date date = dc.getDate(); // 取得 java.util.Date
}

2. 數值格式化

// 保留兩位小數
NumberFormat nf = new NumberFormat("#,##0.00");
WritableCellFormat numFormat = new WritableCellFormat(nf);
sheet.addCell(new Number(col, row, 12345.678, numFormat)); // 顯示 12,345.68

3. 合併儲存格

// 合併 (起始列, 起始行, 結束列, 結束行)
sheet.mergeCells(0, 0, 3, 0); // 第一行前 4 欄合併為標題
sheet.addCell(new Label(0, 0, "人員統計報表", titleFormat));

4. 大檔案記憶體問題

• JXL 讀取會將整個工作表載入記憶體
• 超過 5MB 或 1 萬行建議改用 Apache POI SXSSF (流式寫入) 或 EasyExcel
• 導出大量資料時分批 workbook.write() 並清理列表

JXL vs Apache POI vs EasyExcel 選型