您现在的位置：程式碼之家 >> 編程語言 > >> Java 程式設計 >> Java 綜合教學

Java 讀寫 Excel 完整工具類：基於 Apache POI 支持樣式、合併儲存格、公式計算

編輯：Java 綜合教學

ExcelUtil 核心功能

寫入：支持表頭樣式（粗體、邊框）、內容樣式、多 Sheet、自定義列寬
讀取：返回 List<Map<String, String>>，自動處理表頭、合併儲存格、多種數據類型（字符串、數字、布爾、公式）
合併儲存格：創建、取消、讀取合併區域值
兼容性：基於 POI ss.usermodel 接口，同時支持 .xls (HSSF) 與 .xlsx (XSSF) 只需換 Workbook 實現類

完整代碼

package pers.kangxu.datautils.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.*;
import java.util.*;

public class ExcelUtil {

    /** 導出 Excel */
    public static void writeExcel(String filePath, String sheetName, int sheetIndex,
                                  String[] fileHeader, List<String[]> datas) {
        Workbook wb = new HSSFWorkbook(); // .xls 格式；.xlsx 用 new XSSFWorkbook()
        Sheet sheet = wb.createSheet();
        wb.setSheetName(sheetIndex, sheetName);

        // 樣式準備
        Font headerFont = wb.createFont();
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        CellStyle headerStyle = wb.createCellStyle();
        headerStyle.setFont(headerFont);
        setBorders(headerStyle);

        CellStyle contentStyle = wb.createCellStyle();
        setBorders(contentStyle);

        // 寫表頭
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < fileHeader.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(fileHeader[i]);
            cell.setCellStyle(headerStyle);
        }

        // 寫數據
        for (int rowIdx = 0; rowIdx < datas.size(); rowIdx++) {
            Row row = sheet.createRow(rowIdx + 1);
            String[] rowData = datas.get(rowIdx);
            for (int colIdx = 0; colIdx < fileHeader.length; colIdx++) {
                Cell cell = row.createCell(colIdx);
                cell.setCellValue(rowData[colIdx]);
                cell.setCellStyle(contentStyle);
            }
        }

        // 輸出文件
        try (FileOutputStream out = new FileOutputStream(filePath)) {
            FileUtil.createFile(new File(filePath)); // 確保目錄存在
            wb.write(out);
        } catch (IOException e) {
            throw new RuntimeException("Excel 導出失敗", e);
        }
    }

    private static void setBorders(CellStyle style) {
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
    }

    /** 讀取 Excel → List<Map<鍵,值>> */
    public static List<Map<String, String>> readExcel(String filePath, int sheetIndex) {
        List<Map<String, String>> result = new ArrayList<>();
        List<String> headers = new ArrayList<>();

        try (FileInputStream in = new FileInputStream(filePath);
             Workbook wb = new HSSFWorkbook(in)) { // .xlsx 用 XSSFWorkbook

            Sheet sheet = wb.getSheetAt(sheetIndex);
            Iterator<Row> rows = sheet.rowIterator();
            int rowNum = 0;

            while (rows.hasNext()) {
                Row row = rows.next();
                Iterator<Cell> cells = row.cellIterator();
                Map<String, String> rowMap = new LinkedHashMap<>();
                int colIdx = 0;

                if (rowNum == 0) { // 表頭行
                    while (cells.hasNext()) {
                        Cell cell = cells.next();
                        if (isContainMergeCell(sheet)) cancelMergeCell(sheet);
                        headers.add(getStringCellValue(cell));
                    }
                } else { // 數據行
                    while (cells.hasNext()) {
                        Cell cell = cells.next();
                        if (isContainMergeCell(sheet)) cancelMergeCell(sheet);
                        // 處理重複表頭
                        headers = ListUtil.changeSameVal(headers);
                        String key = headers.get(colIdx++);
                        rowMap.put(key, getStringCellValue(cell));
                    }
                    result.add(rowMap);
                }
                rowNum++;
            }
        } catch (IOException e) {
            throw new RuntimeException("Excel 讀取失敗", e);
        }
        return result;
    }

    /** 合併儲存格 */
    public static int mergeCell(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        if (sheet == null) return -1;
        return sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
    }

    /** 取消合併並填充值 */
    public static void cancelMergeCell(Sheet sheet) {
        int mergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < mergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            String val = getMergeCellValue(sheet, range.getFirstRow(), range.getLastRow());
            sheet.removeMergedRegion(i);
            for (int r = range.getFirstRow(); r <= range.getLastRow(); r++) {
                for (int c = range.getFirstColumn(); c <= range.getLastColumn(); c++) {
                    sheet.getRow(r).getCell(c).setCellValue(val);
                }
            }
        }
    }

    /** 判斷是否為合併儲存格 */
    public static boolean isMergeCell(Sheet sheet, int row, int col) {
        for (CellRangeAddress range : getMergedRegions(sheet)) {
            if (row >= range.getFirstRow() && row <= range.getLastRow()
                    && col >= range.getFirstColumn() && col <= range.getLastColumn()) {
                return true;
            }
        }
        return false;
    }

    private static List<CellRangeAddress> getMergedRegions(Sheet sheet) {
        List<CellRangeAddress> list = new ArrayList<>();
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            list.add(sheet.getMergedRegion(i));
        }
        return list;
    }

    public static boolean isContainMergeCell(Sheet sheet) {
        return sheet != null && sheet.getNumMergedRegions() > 0;
    }

    /** 獲取合併區域左上角儲存格的值 */
    public static String getMergeCellValue(Sheet sheet, int firstRow, int lastRow) {
        for (CellRangeAddress range : getMergedRegions(sheet)) {
            if (firstRow >= range.getFirstRow() && firstRow <= range.getLastRow()
                    && lastRow >= range.getFirstColumn() && lastRow <= range.getLastColumn()) {
                Row r = sheet.getRow(range.getFirstRow());
                Cell c = r.getCell(range.getFirstColumn());
                return getStringCellValue(c);
            }
        }
        return null;
    }

    /** 統一獲取儲存格字符串值（支持數字、布爾、公式、日期） */
    public static String getStringCellValue(Cell cell) {
        if (cell == null) return "";
        switch (cell.getCellType()) {
            case STRING:
                return cell.getRichStringCellValue().getString().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString(); // 可自定義格式
                }
                return String.valueOf(cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
                CellValue cv = evaluator.evaluate(cell);
                return String.valueOf(cv.getNumberValue());
            default:
                return "";
        }
    }
}

依賴的輔助類

// FileUtil.createFile - 確保父目錄存在並創建文件
public class FileUtil {
    public static boolean createFile(File file) {
        File parent = file.getParentFile();
        if (parent != null && !parent.exists()) parent.mkdirs();
        try { return file.createNewFile(); } catch (IOException e) { return false; }
    }
}

// ListUtil.changeSameVal - 處理重複表頭，如 "Name","Name" → "Name","Name_1"
public class ListUtil {
    public static List<String> changeSameVal(List<String> list) {
        Map<String, Integer> count = new HashMap<>();
        for (int i = 0; i < list.size(); i++) {
            String val = list.get(i);
            int c = count.getOrDefault(val, 0);
            if (c > 0) list.set(i, val + "_" + c);
            count.put(val, c + 1);
        }
        return list;
    }
}

調用示例

public class ExcelUtilTester {
    public static void main(String[] args) {
        // 寫入
        List<String[]> data = new ArrayList<>();
        data.add(new String[]{"張三", "25", "工程師"});
        data.add(new String[]{"李四", "30", "架構師"});
        ExcelUtil.writeExcel("D:/test/employees.xls", "員工表", 0,
                new String[]{"姓名", "年齡", "職位"}, data);

        // 讀取
        List<Map<String, String>> list = ExcelUtil.readExcel("D:/test/employees.xls", 0);
        list.forEach(System.out::println);
        // 輸出: {姓名=張三, 年齡=25, 職位=工程師} ...
    }
}