phpexcel 导出下载设置单元格的固定值

下载安装composer

其次 cmd切换到项目根目录 运行命令：

composer require phpoffice/phpexcel

代码实现  注意$n值开始行

public function downloadTemplate()
    {
        
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        //设置工作表标题名称
        $worksheet->setTitle('测试');

        //表头
        //设置单元格内容
        $worksheet->setCellValueByColumnAndRow(1, 1, ' 测试表');
        $worksheet->setCellValueByColumnAndRow(1, 2, '账号');
        $worksheet->setCellValueByColumnAndRow(2, 2, '密码');
        $worksheet->setCellValueByColumnAndRow(3, 2, '角色');
        //设置单元格下拉值
        $role =[['column'=>'C','select_options'=>['企业','乡镇','部门','管理员']]];
        $this->Set_up_the_cell_data($role,$worksheet);

        $worksheet->setCellValueByColumnAndRow(4, 2, '用户名称');
        $worksheet->setCellValueByColumnAndRow(5, 2, '手机号');
        $worksheet->setCellValueByColumnAndRow(6, 2, '上级乡镇(角色企业选择)');
        //查出所有乡镇
        $xz_all = db('user')->field('user_name')->where('role',3)->select();
        foreach ($xz_all as $item) {
            $xz[] =$item['user_name'];
        }
        //设置单元格下拉值
        $towns =[['column'=>'F','select_options'=>$xz]];
        $this->Set_up_the_cell_data($towns,$worksheet);
        //设置列宽
        $worksheet->getColumnDimension('A')->setWidth(20);
        $worksheet->getColumnDimension('B')->setWidth(20);
        $worksheet->getColumnDimension('C')->setWidth(20);
        $worksheet->getColumnDimension('D')->setWidth(20);
        $worksheet->getColumnDimension('E')->setWidth(20);
        $worksheet->getColumnDimension('F')->setWidth(20);
        $worksheet->getColumnDimension('G')->setWidth(20);

        //合并单元格
        $worksheet->mergeCells('A1:H1');

        $styleArray = [
            'font' => [
                'bold' => true
            ],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        //设置单元格样式
        $worksheet->getStyle('A1')->applyFromArray($styleArray)->getFont()->setSize(28);

        $worksheet->getStyle('A2:H2')->applyFromArray($styleArray)->getFont()->setSize(14);


        $filename = '测试表.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');

    }


    //设置某个单元格的固定值
//[['column'=>'C','select_options'=>['企业','乡镇','部门','管理员']]] 这是data格式column开始列 select_options里面是值
//worksheet 是实例类
    public function Set_up_the_cell_data($data,$worksheet)
    {
        //设置下拉框
        foreach($data as $spectial) {
            $optionsString = implode(',', $spectial['select_options']);
            $n = 3;//开始行
            // 我这里设置1000行，可自行设置
            while ($n < 1000) {
                $objValidation = $worksheet->getCell($spectial['column'] . (string)$n)->getDataValidation(); //这一句为要设置数据有效性的单元格
                $objValidation-> setType(\PHPExcel_Cell_DataValidation::TYPE_LIST)
                    -> setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_STOP)
                    ->setAllowBlank(true)
                    ->setShowInputMessage(true)
                    ->setShowErrorMessage(true)
                    ->setShowDropDown(true)
                    ->setErrorTitle('输入的值有误')
                    ->setError('您输入的值不在下拉框列表内.')
                    ->setPromptTitle('')
                    ->setPrompt('')
                    -> setOperator(\PHPExcel_Cell_DataValidation::OPERATOR_BETWEEN)
                    ->setFormula1('"' . $optionsString . '"');
                $n++;
            }
        }
    }

---

若是气质藏于身，岁月从不败佳人