PHPEXCEL 下拉框功能

这个拓展的下拉框分两种，一种比较简单的，像“是”或“否”的，这种，直接定义就好了，但是这种只适合下拉框选项少的，多的不行。

另外一种是属于量大的，像一个下拉框有几十个，几百个的，就需要先把数据存在另外一个表格里面，然后再关联读取。

以下直接上代码：

//导入模板
  public function mould()
  {
        $title = "货品批量导入表格";
        $cellName = [
            '单品名称', '单品编码', '单品规格', '单品单位',
            '网购地址', '单品类别', '固定资产', '专属机构',
            '供应商', '总部成本', '校区成本', '供应商报价',
            '学员价', '普通价', '小店成本价', '最低转卖价',
        ];//表头及字段名

        $branch = M('branch') -> where(['branch_name'=>['NEQ','']]) -> getField('branch_name',true);
        $supplier = M('supplier') -> where(['status'=>1,'supplier_name'=>['NEQ','']]) ->getField('supplier_name',true);
        $goods_type = M('goods_type') -> where(['pid'=>0]) -> getField('type_name',true);


        $spectials = [
//          ['column'=>'F','select_options'=>$goods_type],//货品类型
            ['column'=>'G','select_options'=>['是','否']],//固定资产
//          ['column'=>'H','select_options'=>$branch],//专属机构
//          ['column'=>'I','select_options'=>$supplier],//供应商
        ];

        //引入核心文件
        vendor("PHPExcel.PHPExcel");
        $objPHPExcel = new PHPExcel()；
        //定义配置
        $topNumber = 2;//表头有几行占用
        $xlsTitle = iconv('utf-8', 'gb2312', $title);//文件名称
        $fileName = $title.date('_Ymd');//文件名称
        $cellKey = array(
            'A','B','C','D','E','F','G','H','I','J','K','L','M', 'N','O','P'
        );
        //----------------------------------主表------------------------------------//
        //处理表头标题
        $objPHPExcel->getActiveSheet()->mergeCells('A1:'.$cellKey[count($cellName)-1].'1');//合并单元格（如果要拆分单元格是需要先合并再拆分的，否则程序会报错）
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1',$title);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        //处理表头
        foreach ($cellName as $k=>$v)
        {
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellKey[$k].$topNumber, $v);//设置表头数据
            $objPHPExcel->setActiveSheetIndex(0)->freezePane('A3');//冻结窗口
            $objPHPExcel->getActiveSheet()->getStyle($cellKey[$k].$topNumber)->getFont()->setBold(true);//设置是否加粗
            $objPHPExcel->getActiveSheet()->getStyle($cellKey[$k].$topNumber)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//垂直居中
            $objPHPExcel->getActiveSheet()->getColumnDimension($cellKey[$k])->setWidth(15);//设置列宽度
        }

      //----------------------------------主表------------------------------------//

     //----------------------------------数据源-----------------------------------//
      $objPHPExcel->createSheet();
      $subObject = $objPHPExcel->getSheet(1);
      $subObject->setTitle('data');
      foreach ($branch as $key => $value){
          $subObject->setCellValue('A'.$key,$value);
      }
      foreach ($supplier as $key => $value){
          $subObject->setCellValue('B'.$key,$value);
      }
     foreach ($goods_type as $key => $value){
          $subObject->setCellValue('C'.$key,$value);
     }

     $subObject->getColumnDimension('A')->setWidth(30);
     $subObject->getColumnDimension('B')->setWidth(30);
     $subObject->getColumnDimension('C')->setWidth(30);
     $subObject->getProtection()->setSheet(true);
     $subObject->protectCells('A1:C1000','admin123');
     //----------------------------------数据源-----------------------------------//


    //---------------------------------设置下拉框-----------------------------------//
        //-------简单的固定值--------//
        $objActSheet = $objPHPExcel->getActiveSheet();
        foreach($spectials as $spectial)
        {
            $optionsString = null;
            $optionsString = implode(',', $spectial['select_options']);

            $n = 0;
            $num = 500;
            // 我这里设置1000行，可自行设置
            while($n < $num) {
                $objValidation = $objActSheet->getCell($spectial['column'].(string)$n)->getDataValidation(); //这一句为要设置数据有效性的单元格
                // $objValidation的各项设置参数可详见phpexcel文件，
                // 目录大概为/.../phpoffice/phpexcel/Classes/PHPExcel/Writer/Excel5/Worksheet.php
                // 2767行 方法名：writeDataValidity
                $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++;
            }
        }
        //-------简单的固定值--------//

       //-----------复杂的-------------//
        $n = 0;
        while ($n<$num)
        {
            $objActSheet->getCell('Z'.(string)$n)->getDataValidation()
                -> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
                -> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
                -> setAllowBlank(false)
                -> setShowInputMessage(true)
                -> setShowErrorMessage(true)
                -> setShowDropDown(true)
                -> setErrorTitle('输入的值有误')
                -> setError('您输入的值不在下拉框列表内.')
                -> setPromptTitle('专属机构')
                -> setFormula1('data!$A$1:$A$'.count($branch));
            $n++;
        }

       $n = 0;
        while ($n<$num)
        {
            $objActSheet->getCell('I'.(string)$n)->getDataValidation()
                -> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
                -> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
                -> setAllowBlank(false)
                -> setShowInputMessage(true)
                -> setShowErrorMessage(true)
                -> setShowDropDown(true)
                -> setErrorTitle('输入的值有误')
                -> setError('您输入的值不在下拉框列表内.')
                -> setPromptTitle('供应商')
                -> setFormula1('data!$B$1:$B$'.count($supplier));
            $n++;
        }

        $n = 0;
        while ($n<$num)
        {
            $objActSheet->getCell('F'.(string)$n)->getDataValidation()
                -> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
                -> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
                -> setAllowBlank(false)
                -> setShowInputMessage(true)
                -> setShowErrorMessage(true)
                -> setShowDropDown(true)
                -> setErrorTitle('输入的值有误')
                -> setError('您输入的值不在下拉框列表内.')
                -> setPromptTitle('供应商')
                -> setFormula1('data!$C$1:$C$'.count($goods_type));
            $n++;
        }


        //-----------复杂的-------------//


       //导出execl
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
        header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

参考：

https://www.cnblogs.com/heijinli/p/11044211.html

https://blog.csdn.net/qq_41277856/article/details/103634056

https://blog.csdn.net/lxw1844912514/article/details/100026623