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

Java 讀取 Excel (JXL) 完整代碼範例與遷移建議

編輯：Java 相關知識

JXL 庫基本用法 (舊專案維護)

注意：JXL **已停止維護 (最後更新 2009)**，**僅支援 .xls (Excel 97-2003)**，**不支援 .xlsx**。新專案請直接使用 Apache POI。

Maven 依賴

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

完整讀取代碼

import jxl.*;
import jxl.read.biff.BiffException;
import java.io.File;
import java.io.IOException;
import java.util.*;

public class JxlReader {
    public static List<Map<String, String>> readXls(String filePath) 
            throws IOException, BiffException {
        
        List<Map<String, String>> result = new ArrayList<>();
        Workbook workbook = Workbook.getWorkbook(new File(filePath));
        
        // 取得第一個工作表
        Sheet sheet = workbook.getSheet(0);
        int rows = sheet.getRows();
        int cols = sheet.getColumns();
        
        if (rows <= 1) {
            workbook.close();
            return result; // 無資料
        }
        
        // 假設第一行是標題
        String[] headers = new String[cols];
        for (int c = 0; c < cols; c++) {
            Cell cell = sheet.getCell(c, 0);
            headers[c] = cell.getContents().trim();
        }
        
        // 從第二行開始讀取資料
        for (int r = 1; r < rows; r++) {
            Map<String, String> rowMap = new LinkedHashMap<>();
            for (int c = 0; c < cols; c++) {
                Cell cell = sheet.getCell(c, r);
                String value = cell.getContents().trim();
                rowMap.put(headers[c], value);
            }
            result.add(rowMap);
        }
        
        workbook.close(); // **必須關閉釋放資源**
        return result;
    }

    public static void main(String[] args) {
        try {
            List<Map<String, String>> data = readXls("F:/Center20130902update.xls");
            data.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

常見 Cell 型別處理

Cell cell = sheet.getCell(col, row);
CellType type = cell.getType();

switch (type) {
    case LABEL:
        String str = cell.getContents();
        break;
    case NUMBER:
        NumberCell nc = (NumberCell) cell;
        double val = nc.getValue(); // 數值
        String formatted = nc.getContents(); // 格式化後字串
        break;
    case DATE:
        DateCell dc = (DateCell) cell;
        Date date = dc.getDate(); // java.util.Date
        break;
    case BOOLEAN:
        BooleanCell bc = (BooleanCell) cell;
        boolean bool = bc.getValue();
        break;
    case FORMULA:
        FormulaCell fc = (FormulaCell) cell;
        String formula = fc.getFormula(); // 公式字串
        double result = fc.getValue(); // 計算結果
        break;
    case EMPTY:
    default:
        // 空儲存格
}

寫入 Excel 範例

public static void writeXls(String filePath, List<Map<String, String>> data) 
        throws IOException, WriteException {
    
    WritableWorkbook workbook = Workbook.createWorkbook(new File(filePath));
    WritableSheet sheet = workbook.createSheet("Sheet1", 0);
    
    // 標題行
    if (!data.isEmpty()) {
        int col = 0;
        for (String key : data.get(0).keySet()) {
            sheet.addCell(new Label(col++, 0, key));
        }
    }
    
    // 資料行
    for (int r = 0; r < data.size(); r++) {
        Map<String, String> row = data.get(r);
        int col = 0;
        for (String value : row.values()) {
            sheet.addCell(new Label(col++, r + 1, value));
        }
    }
    
    workbook.write();
    workbook.close();
}

遷移至 Apache POI (強烈推薦)

特性	JXL	Apache POI
支援格式	.xls only	.xls + .xlsx + .xlsm + OOXML
維護狀態	停止 (2009)	活躍、社區龐大
大檔案處理	全載入記憶體	SXSSF 流式寫入、XSSF 事件模型讀取
功能豐富度	基礎	樣式、公式、圖表、樞紐分析、資料驗證
效能	一般	優 (特別是 SXSSF)

POI 讀取 .xlsx 現代代碼

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.3.0</version>
</dependency>

public class PoiReader {
    public static List<Map<String, String>> readXlsx(String filePath) throws IOException {
        List<Map<String, String>> result = new ArrayList<>();
        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook wb = WorkbookFactory.create(fis)) { // 自動識別 .xls/.xlsx
            
            Sheet sheet = wb.getSheetAt(0);
            Row headerRow = sheet.getRow(0);
            if (headerRow == null) return result;
            
            int cols = headerRow.getLastCellNum();
            String[] headers = new String[cols];
            for (int c = 0; c < cols; c++) {
                headers[c] = getCellString(headerRow.getCell(c));
            }
            
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;
                Map<String, String> map = new LinkedHashMap<>();
                for (int c = 0; c < cols; c++) {
                    map.put(headers[c], getCellString(row.getCell(c)));
                }
                result.add(map);
            }
        }
        return result;
    }

    private static String getCellString(Cell cell) {
        if (cell == null) return "";
        return switch (cell.getCellType()) {
            case STRING -> cell.getStringCellValue();
            case NUMERIC -> DateUtil.isCellDateFormatted(cell) 
                ? cell.getDateCellValue().toString() 
                : String.valueOf(cell.getNumericCellValue());
            case BOOLEAN -> String.valueOf(cell.getBooleanCellValue());
            case FORMULA -> cell.getCellFormula();
            default -> "";
        };
    }
}

大檔案流式讀取 (SAX 模式，低記憶體)

// 僅 .xlsx 支援
public void readLargeXlsx(String filePath, Consumer<Row> rowHandler) throws Exception {
    try (InputStream is = new FileInputStream(filePath)) {
        XSSFReader reader = new XSSFReader(OPCPackage.open(is));
        XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) reader.getSheetsData();
        while (sheets.hasNext()) {
            try (InputStream sheetIs = sheets.next()) {
                XMLReader parser = XMLReaderFactory.createXMLReader();
                parser.setContentHandler(new XSSFSheetXMLHandler(
                    sheets.getStylesTable(), 
                    sheets.getSharedStringsTable(), 
                    new DataFormatter(), 
                    rowHandler::accept // 自定義行處理
                ));
                parser.parse(new InputSource(sheetIs));
            }
        }
    }
}