如何在Java中从外部Excel文件按列名提取指定列数据？

使用JDK 1.8与Eclipse开发环境，需要读取外部.xlsx格式Excel文件中的数据，目前已实现统计行列数、输出所有单元格值的功能，但无法按列名（如ORDERS）提取某一列的所有值，寻求解决方法。

用户现有代码片段：

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class ExcelReader {

    public void readXLSX(String excelPath) throws IOException {
        try {
            FileInputStream fis = new FileInputStream(new File(excelPath));
            Workbook wb = new XSSFWorkbook(fis);
            for (Sheet sheet : wb) {
                int countRows = countTotalRows(sheet);
                System.out.println("countRows: " + countRows);
                int countCells = countTotalCells(sheet);
                System.out.println("countCells: " + countCells);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    private int countTotalRows(Sheet sheet) {
        return sheet.getLastRowNum() - sheet.getFirstRowNum() + 1;
    }

    private int countTotalCells(Sheet sheet) {
        List<Cell> listCells = new ArrayList<>(); 
        try {
            int firstRow = sheet.getFirstRowNum();
            int lastRow = sheet.getLastRowNum();
            for (int index = firstRow + 1; index <= lastRow; index++) {
                Row row = sheet.getRow(index);
                System.out.println();
                if (row != null) {
                    for (int cellIndex = row.getFirstCellNum(); cellIndex < row.getLastCellNum(); cellIndex++) {
                        Cell cell = row.getCell(cellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                        printCellValue(cell);
                        listCells.add(cell);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            return listCells.size();
        }
        return listCells.size();
    }

    private void printCellValue(Cell cell) {
        CellType cellType = cell.getCellTypeEnum().equals(CellType.FORMULA)
            ? cell.getCachedFormulaResultTypeEnum() : cell.getCellTypeEnum();
        if (cellType.equals(CellType.STRING)) {
            System.out.print(cell.getStringCellValue() + " | ");
        }
        if (cellType.equals(CellType.NUMERIC)) {
            if (DateUtil.isCellDateFormatted(cell)) {
                System.out.print(cell.getDateCellValue() + " | ");
            } else {
                System.out.print(cell.getNumericCellValue() + " | ");
            }
        }
        if (cellType.equals(CellType.BOOLEAN)) {
            System.out.print(cell.getBooleanCellValue() + " | ");
        }
    }
}

要实现按列名提取整列数据，核心是先建立列名与列索引的映射关系，再根据索引遍历每一行的对应单元格。以下是修改后的完整代码：

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelReader {

    public void readXLSX(String excelPath) throws IOException {
        try {
            FileInputStream fis = new FileInputStream(new File(excelPath));
            Workbook wb = new XSSFWorkbook(fis);
            for (Sheet sheet : wb) {
                int countRows = countTotalRows(sheet);
                System.out.println("countRows: " + countRows);
                int countCells = countTotalCells(sheet);
                System.out.println("countCells: " + countCells);

                // 按列名提取ORDERS列数据并输出
                List<String> ordersColumnData = getColumnDataByColumnName(sheet, "ORDERS");
                System.out.println("\nORDERS列所有数据：");
                for (String value : ordersColumnData) {
                    System.out.println(value);
                }
            }
            wb.close();
            fis.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    private int countTotalRows(Sheet sheet) {
        return sheet.getLastRowNum() - sheet.getFirstRowNum() + 1;
    }

    private int countTotalCells(Sheet sheet) {
        List<Cell> listCells = new ArrayList<>(); 
        try {
            int firstRow = sheet.getFirstRowNum();
            int lastRow = sheet.getLastRowNum();
            for (int index = firstRow + 1; index <= lastRow; index++) {
                Row row = sheet.getRow(index);
                System.out.println();
                if (row != null) {
                    for (int cellIndex = row.getFirstCellNum(); cellIndex < row.getLastCellNum(); cellIndex++) {
                        Cell cell = row.getCell(cellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                        printCellValue(cell);
                        listCells.add(cell);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            return listCells.size();
        }
        return listCells.size();
    }

    private void printCellValue(Cell cell) {
        CellType cellType = cell.getCellTypeEnum().equals(CellType.FORMULA)
            ? cell.getCachedFormulaResultTypeEnum() : cell.getCellTypeEnum();
        if (cellType.equals(CellType.STRING)) {
            System.out.print(cell.getStringCellValue() + " | ");
        }
        if (cellType.equals(CellType.NUMERIC)) {
            if (DateUtil.isCellDateFormatted(cell)) {
                System.out.print(cell.getDateCellValue() + " | ");
            } else {
                System.out.print(cell.getNumericCellValue() + " | ");
            }
        }
        if (cellType.equals(CellType.BOOLEAN)) {
            System.out.print(cell.getBooleanCellValue() + " | ");
        }
    }

    // 建立列名与列索引的映射
    private Map<String, Integer> getColumnIndexMap(Sheet sheet) {
        Map<String, Integer> columnMap = new HashMap<>();
        Row headerRow = sheet.getRow(sheet.getFirstRowNum());
        if (headerRow == null) return columnMap;

        for (int cellIndex = headerRow.getFirstCellNum(); cellIndex < headerRow.getLastCellNum(); cellIndex++) {
            Cell cell = headerRow.getCell(cellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            String columnName = cell.getStringCellValue().trim();
            columnMap.put(columnName, cellIndex);
        }
        return columnMap;
    }

    // 根据列名提取整列数据
    private List<String> getColumnDataByColumnName(Sheet sheet, String columnName) {
        List<String> columnData = new ArrayList<>();
        Map<String, Integer> columnMap = getColumnIndexMap(sheet);

        if (!columnMap.containsKey(columnName)) {
            System.out.println("未找到指定列名：" + columnName);
            return columnData;
        }

        int targetColumnIndex = columnMap.get(columnName);
        int firstRow = sheet.getFirstRowNum();
        int lastRow = sheet.getLastRowNum();

        for (int rowIndex = firstRow + 1; rowIndex <= lastRow; rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row == null) {
                columnData.add("");
                continue;
            }
            Cell cell = row.getCell(targetColumnIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            columnData.add(getCellStringValue(cell));
        }
        return columnData;
    }

    // 统一将单元格值转为字符串
    private String getCellStringValue(Cell cell) {
        CellType cellType = cell.getCellTypeEnum().equals(CellType.FORMULA)
                ? cell.getCachedFormulaResultTypeEnum() : cell.getCellTypeEnum();
        switch (cellType) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                return DateUtil.isCellDateFormatted(cell) 
                        ? cell.getDateCellValue().toString() 
                        : String.valueOf(cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return "";
        }
    }
}

1. getColumnIndexMap方法：读取Excel表头行，将每个列名与其对应的列索引存入HashMap，后续可通过列名快速定位列位置。
2. getColumnDataByColumnName方法：先通过列名从映射表中获取目标列索引，再遍历所有数据行，提取对应单元格的值并收集到列表中。
3. getCellStringValue方法：统一处理不同类型的单元格（字符串、数字、日期、布尔值），将其转为字符串格式，方便后续统一使用。

内容的提问来源于stack exchange，提问作者EmanueleAmbretti