PHPOffice/PHPExcel生成省市区三级联动的excel表格
最近公司需要用到一个省市区三级联动的excel表格，但是数据都在数据库，又太多，人工不好制作，就让我这个phper来帮忙啦。
主要用到的是excel的定义名称，数据验证。其中数据验证的列表只能是一列或者一行，网上查了下说可以先用一行做数据验证，然后修改名称的定义范围，经过我尝试，效果不好。
然后我把*名称的东西放到了表格的“无人地带”，解决了问题，也算是耍了个小聪明吧。
效果图：
代码片段(php)：
public function actionTest2()
{
$subject = 'demo';
$title = ['省','市','区'];
$data = [
[
'name' => '湖北省',
'children' => [
[
'name' => '武汉市',
'children' => ['江夏区','洪山区','青山区','武昌区','汉口']
],
[
'name' => '宜昌市',
'children' => ['当阳市','夷陵区','庙前']
],
[
'name' => '荆州市',
'children' => ['荆州区','荆州城区']
]
]
],
[
'name' => '湖南省',
'children' => [
[
'name' => '长沙市',
'children' => ['长沙1','长沙2','长沙3','长沙4','长沙5','长沙6','长沙7']
],
[
'name' => '岳阳市',
'children' => ['岳阳市1','益阳市']
],
[
'name' => '常德市',
'children' => ['常德山庄1','常德山庄2','常德山庄3']
]
]
],
[
'name' => '广东省',
'children' => [
[
'name' => '广东市',
'children' => ['广东市1','广东市2','广东市3']
],
[
'name' => '深圳',
'children' => ['深圳1','深圳2','深圳3']
],
[
'name' => '佛山市',
'children' => ['佛山1','佛山2','佛山3','佛山4','佛山5']
]
]
],
];
$high = 0;
$objPHPExcel = new PHPExcel();
$titleRow = array('A1','B1','C1','D1','E1','F1','G1','H1','I1','J1','K1','L1','M1','N1','O1','P1','Q1','R1','S1','T1','U1','V1','W1','X1','Y1','Z1','AA1','AB1','AC1','AD1');
for($a = 0; $a < count($title); $a++){
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($titleRow[$a], $title[$a]);
}
$supportSheet = new PHPExcel_Worksheet($objPHPExcel, 'support'); //创建一个工作表
$objPHPExcel->addSheet($supportSheet); //插入工作表
$col = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
foreach ($data as $key=>$first){
$objPHPExcel->getSheetByName('support')->setCellValue($col[0].($key+1+$high),$first['name']);
$max = 0; //重置max
$secondNum = count($first['children']);
foreach ($first['children'] as $index=>$second){
$objPHPExcel->getSheetByName('support')->setCellValue($col[$index+1].($key+1+$high),$second['name']);
$thirdNum = count($second['children']);
if ($thirdNum > $max){
$max = $thirdNum;
}
foreach ($second['children'] as $id=>$third){
$objPHPExcel->getSheetByName('support')->setCellValue($col[$index+1].($key+1+$high+$id+1),$third);
}
//定义三级名称
$objPHPExcel->addNamedRange(
new PHPExcel_NamedRange(
$second['name'],
$objPHPExcel->getSheetByName('support'),
$col[$index+1].($key+1+$high+1).':'.$col[$index+1].($key+1+$high+1+$thirdNum-1)
)
);
}
//定义二级名称
$objPHPExcel->addNamedRange(
new PHPExcel_NamedRange(
$first['name'],
$objPHPExcel->getSheetByName('support'),
$col[1].($key+1+$high).':'.$col[1+$secondNum-1].($key+1+$high)
)
);
$high += $max;
}
//移花接木
foreach ($data as $var=>$content){
$objPHPExcel->getSheetByName('support')->setCellValue('UI'.($var+1),$content['name']);
}
//定义*名称
/*$total = count($data);
$str = '';
$count = 0;
$max = 0;
for ($i = 0;$i < $total;$i++){
$str .= $col[0].(1+$count+$i).',';
$secondCount = count($data[$i]['children']);
for ($j = 0;$j < $secondCount;$j++){
if (count($data[$i]['children'][$j]['children']) > $max){
$max = count($data[$i]['children'][$j]['children']);
}
}
$count += $max;
}
$str = rtrim($str,',');
$objPHPExcel->addNamedRange(
new PHPExcel_NamedRange(
'region',
$objPHPExcel->getSheetByName('support'),
$str
)
);*/
$total = count($data);
$objPHPExcel->addNamedRange(
new PHPExcel_NamedRange(
'region',
$objPHPExcel->getSheetByName('support'),
'UI1'.':'.'UI'.$total
)
);
//数据验证
for ($i = 2;$i < 10;$i++){
$objValidation = $objPHPExcel->getActiveSheet()->getCell('A'.$i)->getDataValidation();
$objValidation->setType(PHPExcel_Cell_DataValidation::TYPE_LIST );
$objValidation->setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
$objValidation->setAllowBlank(false);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
$objValidation->setShowDropDown(true);
$objValidation->setErrorTitle('输入错误');
$objValidation->setError('不在列表中的值');
$objValidation->setPromptTitle('请选择');
$objValidation->setPrompt('请从列表中选择一个值.');
$objValidation->setFormula1("=region");
$objValidation = $objPHPExcel->getActiveSheet()->getCell('B'.$i)->getDataValidation();
$objValidation->setType(PHPExcel_Cell_DataValidation::TYPE_LIST );
$objValidation->setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
$objValidation->setAllowBlank(false);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
$objValidation->setShowDropDown(true);
$objValidation->setErrorTitle('输入错误');
$objValidation->setError('不在列表中的值');
$objValidation->setPromptTitle('请选择');
$objValidation->setPrompt('请从列表中选择一个值.');
$objValidation->setFormula1('=INDIRECT($'.'A'.'$'.$i.')');
$objValidation = $objPHPExcel->getActiveSheet()->getCell('C'.$i)->getDataValidation();
$objValidation->setType(PHPExcel_Cell_DataValidation::TYPE_LIST );
$objValidation->setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
$objValidation->setAllowBlank(false);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
$objValidation->setShowDropDown(true);
$objValidation->setErrorTitle('输入错误');
$objValidation->setError('不在列表中的值');
$objValidation->setPromptTitle('请选择');
$objValidation->setPrompt('请从列表中选择一个值.');
$objValidation->setFormula1('=INDIRECT($'.'B'.'$'.$i.')');
}
$objPHPExcel->setActiveSheetIndex(0);
//输出表格
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename='.$subject.''.date('Ymd').'.xlsx');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
}