[开发测试]PHP使用PhpSpreadsheet实现导出Excel时带下拉框列表(可支持联动)

最终效果

核心代码

<?php
// 需要的扩展
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\NamedRange;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
?>

class Excel extends Controller
{

	/**
     * @name: 导出下拉列表Excel
     * @author: Turbo
     * @Date: 2022-03-30 02:05:24
     */    
    public function exportselectexcel()
    {
        // 定义表头
        $header = [
            'A1' => '门店',
            'B1' => '公寓', 
            'C1' => '门牌号', 
            'D1' => '记账类型', 
            'E1' => '记账项目', 
            'F1' => '支出类型', 
            'G1' => '费用分类', 
            'H1' => '金额', 
            'I1' => '记账日期', 
            'J1' => '收款人', 
            'K1' => '收款人帐号', 
            'L1' => '开户支行',
            'M1' => '备注'
        ];

        // 下拉数据[这里模拟出来数据格式，实际情况从数据库获取数据并整理成下列数据格式]
        $oneData = [
            [
                'id' => 1,
                'title' => '我是A',
                'children' =>
                    [
                        [
                            'id' => 2,
                            'title' => '我是A的下级A1',
                            'children' =>
                                [
                                    [
                                        'id' => 3,
                                        'title' => '我是A1的下级A11'
                                    ],
                                    [
                                        'id' => 4,
                                        'title' => '我是A1的下级A12'
                                    ]
                                ]
                        ],
                        [
                            'id' => 5,
                            'title' => '我是A的下级A2',
                            'children' =>
                                [
                                    [
                                        'id' => 6,
                                        'title' => '我是A2的下级A21'
                                    ],
                                    [
                                        'id' => 7,
                                        'title' => '我是A2的下级A22'
                                    ]
                                ]
                        ]
                    ]
            ],
            [
                'id' => 1,
                'title' => '我是B',
                'children' =>
                    [
                        [
                            'id' => 2,
                            'title' => '我是B的下级B1',
                            'children' =>
                                [
                                    [
                                        'id' => 3,
                                        'title' => '我是B1的下级B11'
                                    ],
                                    [
                                        'id' => 4,
                                        'title' => '我是B1的下级B12'
                                    ]
                                ]
                        ],
                        [
                            'id' => 5,
                            'title' => '我是B的下级B2',
                            'children' =>
                                [
                                    [
                                        'id' => 6,
                                        'title' => '我是B2的下级B21'
                                    ],
                                    [
                                        'id' => 7,
                                        'title' => '我是B2的下级B22'
                                    ]
                                ]
                        ]
                    ]
            ],
        ];

        $keepType = '支出'; // 记账类别（默认只有支出）这里如果超出255字符的数据可采用"Sheet"赋值的方法(和下面这种联动的一样)

        // 实例化Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        $sheetMain = $spreadsheet->getsheet(0); // 设置主sheet
        $sheetMain->setTitle('Excel导出下拉框-示例'); // 设置sheet的名称
        $sheetMain->getPageSetup()->setHorizontalCentered(true);
        $sheetMain->getPageSetup()->setVerticalCentered(false);
        // 插入表头
        foreach ($header as $key => $value) {
            $sheetMain->setCellValue($key, $value);
        }
        $sheetOne = $spreadsheet->createSheet(1); // 创建第一级sheet
        $sheetTwo = $spreadsheet->createSheet(2); // 创建第二级sheet
        $sheetTwoCol = 0; // 属性sheet的列
        foreach ($oneData as $key => $value) {
            $row = 1; // 定义赋值开始的行数
            /* 
                这里采用 Coordinate::stringFromColumnIndex 获取对应的列数名
                Ps：顺序从“1”开始，所以获取列名时需要在原有数组索引上加“1”
            */
            $sheetOne->setCellValue(Coordinate::stringFromColumnIndex(bcadd($key, 1, 0)) . $row, $value['title']); // 所有数据
            if (!empty($value['children'])) { // 判断是否有子级
                foreach ($value['children'] as $value2) { // 第二级
                    $row2 = 1; // 属性sheet的第n行
                    $sheetOne->setCellValue(Coordinate::stringFromColumnIndex(bcadd($key, 1, 0)) . ++$row, $value2['title']);
                    $sheetTwo->setCellValue(Coordinate::stringFromColumnIndex(bcadd($sheetTwoCol, 1, 0)) . $row2, $value2['title']);
                    if (!empty($value2['children'])) { // 判断是否有子级
                        foreach ($value2['children'] as $value3) { // 第三级
                            $sheetTwo->setCellValue(Coordinate::stringFromColumnIndex(bcadd($sheetTwoCol, 1, 0)) . ++$row2, $value3['title']);
                        }
                        $spreadsheet->addNamedRange(new NamedRange($value2['title'], $sheetTwo, Coordinate::stringFromColumnIndex(bcadd($sheetTwoCol, 1, 0)) . '2:' . Coordinate::stringFromColumnIndex(bcadd($sheetTwoCol, 1, 0)) . $row2)); // 设置第二级和第三季联动
                    }
                    $sheetTwoCol++; // 属性sheet列+1
                }
                // 定义数据命名范围
                $spreadsheet->addNamedRange(new NamedRange($value['title'], $sheetOne, Coordinate::stringFromColumnIndex(bcadd($key, 1, 0)) . '2:' . Coordinate::stringFromColumnIndex(bcadd($key, 1, 0)) . $row));
            }
        }
        // 定义数据命名范围
        $spreadsheet->addNamedRange(new NamedRange('oneData', $sheetOne, 'A1:' . Coordinate::stringFromColumnIndex(bcadd(count($oneData), 1, 0)) . '1'));

        // 隐藏不显示的辅助工作表
        $sheetOne->setSheetState(Worksheet::SHEETSTATE_HIDDEN);
        $sheetTwo->setSheetState(Worksheet::SHEETSTATE_HIDDEN);
  

        $maxRows = 5; // 设置最大填充行数[不包含第一行]
        foreach (range(2, bcadd($maxRows, 1, 0)) as $row) {
            // 设置下拉
            $this->setValidation($sheetMain, "A${row}", '=oneData'); // 第一级下拉
            $this->setValidation($sheetMain, "B${row}", "=INDIRECT(A${row})"); // indirect间接的，以某个坐标为依托，联级 第二级下拉
            $this->setValidation($sheetMain, "C${row}", "=INDIRECT(B${row})"); // indirect间接的，以某个坐标为依托，联级 第三级下拉
            $this->setValidation($sheetMain, "D${row}", '"' . $keepType . '"'); // 记账类型
            $this->setInputRule($sheetMain, "H${row}", DataValidation::TYPE_DECIMAL); // 设置输入数字类型
            $this->setInputRule($sheetMain, "I${row}", DataValidation::TYPE_TIME); // 设置输入时间类型
        }
        
        // 调整sheet样式
        $styleArray = ['font' => ['bold' => true], 'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER]];
        $sheetMain->getStyle('A1:M1')->applyFromArray($styleArray);
        // 设置表格宽度（设置'auto'为自动宽度）
        $sheetMain->getColumnDimension('A')->setWidth(25);
        $sheetMain->getColumnDimension('B')->setWidth(25);
        $sheetMain->getColumnDimension('C')->setWidth(25);
        $sheetMain->getColumnDimension('D')->setWidth(20);
        $sheetMain->getColumnDimension('E')->setWidth(25);
        $sheetMain->getColumnDimension('F')->setWidth(25);
        $sheetMain->getColumnDimension('G')->setWidth(25);
        $sheetMain->getColumnDimension('H')->setWidth(20);
        $sheetMain->getColumnDimension('I')->setWidth(20);
        $sheetMain->getColumnDimension('J')->setWidth(18);
        $sheetMain->getColumnDimension('K')->setWidth(22);
        $sheetMain->getColumnDimension('L')->setWidth(25);
        $sheetMain->getColumnDimension('M')->setWidth(30);

        //定义文件名称，需要带有定义的后缀名
        $filename = date('YmdHis') . 'Excel下拉框-示例.xlsx';
        ob_end_clean(); //清除缓冲区,避免乱码
        //将输出重定向到客户端的web浏览器
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        //如果浏览器为IE9
        header('Cache-Control: max-age=1');
        //如果通过SSL向IE提供服务
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
        header('Cache-Control: cache, must-revalidate');//HTTP/1.1
        header('Pragma: public');//HTTP/1.0
        $writer = IOFactory ::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }
	/**
     * 设置某个单元格的下拉列表规则
     * @param Worksheet $sheet
     * @param [string] $cellPoint 单元格坐标. A1
     * @param [sting] $format 公式
     * @return void
     */
    private function setValidation(Worksheet $sheet, $cellPoint, $format)
    {
        $validation = $sheet->getCell($cellPoint)->getDataValidation();
        $validation->setType(DataValidation::TYPE_LIST);
        $validation->setErrorStyle(DataValidation::STYLE_INFORMATION);
        $validation->setAllowBlank(false);
        $validation->setShowInputMessage(true);
        $validation->setShowErrorMessage(true);
        $validation->setShowDropDown(true);
        $validation->setErrorTitle('输出错误');
        $validation->setError('值不在列表中');
        $validation->setPromptTitle('请选择');
        $validation->setPrompt('请从列表中选择一个值');
        $validation->setFormula1($format); // 使用某个公司 eg: '=format'
        // $validation->setFormula1('"A1,A2,A3"');//可以直接写列表，用逗号分隔,最多255字符
        // $objValidation->setFormula1('=INDIRECT(A8)');//indirect间接的，以某个坐标为依托，联级
    }

    /**
     * 设置某个单元格的输入规则
     * @param Worksheet $sheet
     * @param [string] $cellPoint 单元格坐标. A1
     * @param [sting] type 输入类型 DataValidation::TYPE_LIST
     * @return void
     */
    private function setInputRule(Worksheet $sheet, $cellPoint, $type)
    {
        $validation = $sheet->getCell($cellPoint)->getDataValidation();
        $validation->setType($type);
        $validation->setErrorStyle(DataValidation::STYLE_INFORMATION); //输错了的话显示的提示属于哪个级别
        $validation->setAllowBlank(false);
        $validation->setShowInputMessage(true);
        $validation->setShowErrorMessage(true);
        $validation->setErrorTitle('输入格式有误');
    }
}