前言：
最近有遇到过这样的项目需求，需要导出 excel 模板的时候在模板中内置好需要联动的下拉框选择功能，这样在进行excel填写的时候可以进行选择内置的内容。
这里就是进行联动选择的，省、市、区三级进行联动，根据前边选择框中选择的条件联动后续的选择框。
功能实现
具体的实现功能如下：
<?php
namespace app\design\controller;
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\Writer\Xlsx;
use think\Controller;
class Testexcel extends Controller
{
/**
* 下载excel模板
* @throws \PhpOffice\PhpSpreadsheet\Exception
* @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
*/
public function exportExcel()
{
//定义一个excel的header表头
$header = ['A1' => '序号', 'B1' => '姓名', 'C1' => '证件号', 'D1' => '张数', 'E1' => '证件类型', 'F1' => '省', 'G1' => '市', 'H1' => '区/镇', 'I1' => '类别', 'J1' => '设计人', 'K1' => '完成时间', 'L1' => '备注'];
//需要填充的联动数据
$major = [
[
'id' => 1,
'title' => '河南省',
'children' =>
[
[
'id' => 2,
'title' => '郑州市',
'children' =>
[
[
'id' => 3,
'title' => '郑东新区'
],
[
'id' => 4,
'title' => '金水区'
]
]
],
[
'id' => 5,
'title' => '信阳市',
'children' =>
[
[
'id' => 6,
'title' => '浉河区'
],
[
'id' => 7,
'title' => '平桥区'
]
]
]
]
],
[
'id' => 8,
'title' => '江苏省',
'children' =>
[
[
'id' => 9,
'title' => '南京市',
'children' =>
[
[
'id' => 10,
'title' => '秦淮区'
],
[
'id' => 11,
'title' => '玄武区'
]
]
],
[
'id' => 12,
'title' => '苏州市',
'children' =>
[
[
'id' => 13,
'title' => '姑苏区'
],
[
'id' => 14,
'title' => '吴中区'
]
]
]
]
]
];
//类别
$typeStr = '是,否';
//证件类型
$biaoduanStr = '身份证,驾照,护照,学生证';
//excel中A-Z单元格
$letters = range('A', 'Z');
//实例化Spreadsheet对象
$spreadsheet = new Spreadsheet();
$sheetMain = $spreadsheet -> getsheet(0);//主sheet
$sheetMain -> setTitle('人员信息');//设置sheet的名称
$sheetMain -> getPageSetup() -> setHorizontalCentered(true);
$sheetMain -> getPageSetup() -> setVerticalCentered(false);
foreach ($header as $key => $value) {
$sheetMain -> setCellValue($key, $value);
}
$sheetMajor = $spreadsheet -> createSheet(1); //省市sheet
$sheetProp = $spreadsheet -> createSheet(2); //市区sheet
$sheetPropCol = 0;//属性sheet的列
foreach ($major as $key => $value) {//省,一个省一列，第一行为市，第二行为区/镇
$row = 1; // 专业和子专业sheet的第n行
$sheetMajor -> setCellValue($letters[$key] . $row, $value['title']);//所有
if (!empty($value['children'])) {
foreach ($value['children'] as $value2) {//市
$row2 = 1;//属性sheet的第n行
$sheetMajor -> setCellValue($letters[$key] . ++$row, $value2['title']);
$sheetProp -> setCellValue($letters[$sheetPropCol] . $row2, $value2['title']);
if (!empty($value2['children'])) {
foreach ($value2['children'] as $value3) {//区/镇
$sheetProp -> setCellValue($letters[$sheetPropCol] . ++$row2, $value3['title']);
}
$spreadsheet -> addNamedRange(new NamedRange($value2['title'], $sheetProp, $letters[$sheetPropCol] . '2:' . $letters[$sheetPropCol] . $row2));//设置市=>区/镇联动
}
$sheetPropCol++;//属性sheet列+1
}
$spreadsheet -> addNamedRange(new NamedRange($value['title'], $sheetMajor, $letters[$key] . '2:' . $letters[$key] . $row));
}
}
$spreadsheet -> addNamedRange(new NamedRange('major', $sheetMajor, 'A1:' . $letters[count($major)] . '1'));
$sheetMajor -> setSheetState(Worksheet::SHEETSTATE_HIDDEN);//隐藏省市sheet
$sheetProp -> setSheetState(Worksheet::SHEETSTATE_HIDDEN);//隐藏市区sheet
$maxRows = 200;//模板填充行数
foreach (range(2, $maxRows) as $row) {
//200行内列数E、F、G、H、I设置成可以下拉选择
$this -> setValidation($sheetMain, "E${row}", '"' . $biaoduanStr . '"');
$this -> setValidation($sheetMain, "F${row}", '=major');
$this -> setValidation($sheetMain, "G${row}", "=INDIRECT(F${row})");//indirect间接的，以某个坐标为依托，联级
$this -> setValidation($sheetMain, "H${row}", "=INDIRECT(G${row})");//indirect间接的，以某个坐标为依托，联级
$this -> setValidation($sheetMain, "I${row}", '"' . $typeStr . '"');
$this -> setInputRule($sheetMain, "A${row}", DataValidation::TYPE_DECIMAL);//设置输入数字类型
$this -> setInputRule($sheetMain, "D${row}", DataValidation::TYPE_DECIMAL);//设置输入数字类型
$this -> setInputRule($sheetMain, "K${row}", DataValidation::TYPE_TIME);//设置输入时间类型
}
//调整sheet样式
$styleArray = ['font' => ['bold' => true], 'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER]];
$sheetMain -> getStyle('A1:L1') -> applyFromArray($styleArray);
$sheetMain -> getColumnDimension('B') -> setWidth(25);
$sheetMain -> getColumnDimension('C') -> setWidth(25);
$sheetMain -> getColumnDimension('E') -> setWidth(20);
$sheetMain -> getColumnDimension('F') -> setWidth(20);
$sheetMain -> getColumnDimension('G') -> setWidth(20);
$sheetMain -> getColumnDimension('H') -> setWidth(20);
$sheetMain -> getColumnDimension('K') -> setWidth(20);
//$sheetMain->getColumnDimension('H')->setAutoSize(true); // 自动宽度，列表无用
//定义文件名称，需要带有定义的后缀名
$filename = 'test.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
*/
protected 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);
// $validation->setFormula1('=major');//使用某个公式
// $validation->setFormula1('"A1,A2,A3"');//可以直接写列表，用逗号分隔,最多255字符
// $objValidation->setFormula1('=INDIRECT(F5)');//indirect间接的，以某个坐标为依托，联级
}
/**
* 设置某个单元格的输入规则
* @param Worksheet $sheet
* @param [string] $cellPoint 单元格坐标. A1
* @param [sting] type 输入类型 DataValidation::TYPE_LIST
* @return void
*/
protected 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('输入格式有误');
}
}
导出的 excel 模板如下：
**
注意：
目前测试的phpspreadsheet版本为1.11，后续在实际项目中经过测试，1.2版本以上的此方法不生效，联动下拉不起作用，为了方便起见，默认安装1.11版本的。
composer require phpoffice/phpspreadsheet ^1.11
**
扫一扫
817
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
