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
