如何使用PhpSpreadsheet读取Xlsx文件中的下拉列表值

云上老周

2026-8-17

解决PhpSpreadsheet读取Excel下拉菜单选项的问题

首先要明确Excel下拉菜单（数据验证）的选项分为两种存储形式，需针对性解析：

静态直接定义的选项列表，格式为带双引号的逗号分隔字符串
引用单元格区域的动态选项，格式为单元格范围表达式

以下是可直接运行的完整代码：

$inputFileName = 'file.xlsx';
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);
$sheet = $spreadsheet->getSheet(2);

$cell = $sheet->getCell('F3');
$validation = $cell->getDataValidation();

// 仅处理列表类型的数据验证
if ($validation->getType() === \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST) {
    $formula1 = $validation->getFormula1();
    $options = [];

    // 处理静态选项列表
    if (str_starts_with($formula1, '"')) {
        $optionsStr = trim($formula1, '"');
        $options = explode(',', $optionsStr);
    } 
    // 处理单元格区域引用的动态选项
    else {
        $rangeParts = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::splitRange($formula1);
        foreach ($rangeParts as $range) {
            [$startCell, $endCell] = $range;
            $cellRefs = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::extractAllCellReferencesInRange($startCell, $endCell);
            
            foreach ($cellRefs as $cellRef) {
                // 处理跨工作表引用
                if (str_contains($cellRef, '!')) {
                    [$sheetName, $cellAddr] = explode('!', $cellRef);
                    $targetSheet = $spreadsheet->getSheetByName(ltrim($sheetName, "'"));
                    $options[] = $targetSheet->getCell($cellAddr)->getValue();
                } else {
                    $options[] = $sheet->getCell($cellRef)->getValue();
                }
            }
        }
    }

    // 输出获取到的下拉选项
    print_r($options);
}