PHP使用PhpSpreadsheet实现导出Excel时带下拉框列表 (可支持三级联动)
因项目需要导出Excel表 需要支持下拉 且 还需要支持三级联动功能
目前应为PHPExcel 不在维护,固采用 PhpSpreadsheet
效果如图:
第一步:首先 使用composer 获取PhpSpreadsheet
我这里PHP 版本 7.4 命令如下:
composer require phpoffice/phpspreadsheet
第二步:新建php 文件 代码如下 可根据实际情况 自己改动:
-
-
-
require './vendor/autoload.php';
-
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\Cell\Coordinate;
-
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
-
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
-
-
-
// 定义表头
-
$header = [
-
['col' => 'A', 'title' => '姓名', 'field' => 'name', 'width' => 20, 'id_down_type' => 0, 'is_text' => 1],
-
['col' => 'B', 'title' => '证件号', 'field' => 'id_card', 'width' => 30, 'id_down_type' => 0, 'is_text' => 1],
-
['col' => 'C', 'title' => '性别', 'field' => 'gender', 'width' => 20, 'id_down_type' => 1, 'is_text' => 1, 'count' => 2, 'sub_table_name' => 'sex'],
-
['col' => 'D', 'title' => '联系方式', 'field' => 'phone', 'width' => 20, 'id_down_type' => 0, 'is_text' => 1],
-
['col' => 'E', 'title' => '门店', 'field' => 'mendiao', 'width' => 30, 'id_down_type' => 2, 'is_text' => 0],
-
['col' => 'F', 'title' => '公寓', 'field' => 'gongyu', 'width' => 30, 'id_down_type' => 2, 'is_text' => 0],
-
['col' => 'G', 'title' => '门牌号', 'field' => 'menpai', 'width' => 30, 'id_down_type' => 2, 'is_text' => 0],
-
];
-
-
// 下拉数据[这里模拟出来数据格式,实际情况从数据库获取数据并整理成下列数据格式]
-
$oneData = [
-
[
-
'id' => 1,
-
'title' => '我是A',
-
'text'=>'我是A_1',
-
'children' =>
-
[
-
[
-
'id' => 2,
-
'title' => '我是A的下级A1',
-
'text'=>'我是A的下级A1_2',
-
'children' =>
-
[
-
[
-
'id' => 3,
-
'title' => '我是A1的下级A11',
-
'text'=>'我是A1的下级A11_3',
-
],
-
[
-
'id' => 4,
-
'title' => '我是A1的下级A12',
-
'text'=>'我是A1的下级A12_4',
-
]
-
]
-
],
-
[
-
'id' => 5,
-
'title' => '我是A的下级A2',
-
'text'=>'我是A的下级A2_5',
-
'children' =>
-
[
-
[
-
'id' => 6,
-
'title' => '我是A2的下级A21',
-
'text'=>'我是A2的下级A21_6',
-
],
-
[
-
'id' => 7,
-
'title' => '我是A2的下级A22',
-
'text'=>'我是A2的下级A22_7',
-
]
-
]
-
]
-
]
-
],
-
[
-
'id' => 8,
-
'title' => '我是B',
-
'text'=>'我是B_8',
-
'children' =>
-
[
-
[
-
'id' => 9,
-
'title' => '我是B的下级B1',
-
'text'=>'我是B的下级B1_9',
-
'children' =>
-
[
-
[
-
'id' => 10,
-
'title' => '我是B1的下级B11',
-
'text'=>'我是B1的下级B11_10',
-
],
-
[
-
'id' => 11,
-
'title' => '我是B1的下级B12',
-
'text'=>'我是B1的下级B12_11',
-
]
-
]
-
],
-
[
-
'id' => 12,
-
'title' => '我是B的下级B2',
-
'text' => '我是B的下级B2_12',
-
'children' =>
-
[
-
[
-
'id' => 13,
-
'title' => '我是B2的下级B21',
-
'text' => '我是B2的下级B21_13',
-
],
-
[
-
'id' => 14,
-
'title' => '我是B2的下级B22',
-
'text' => '我是B2的下级B22_14'
-
]
-
]
-
]
-
]
-
],
-
];
-
-
//实例化Spreadsheet对象
-
$spreadsheet = new Spreadsheet();
-
-
$sex_list = [
-
['id' => 1, 'text' => '男'],
-
['id' => 2, 'text' => '女'],
-
];
-
$sub_table_num = 1; //子表顺序 默认1
-
//创建子表下拉定义 用于下拉
-
$sub_table_info_arr = [
-
['title' => 'sex', 'index' => $sub_table_num, 'data' => $sex_list],
-
];
-
$sub_table_num ;
-
-
//创建下拉分表数据
-
create_excel_sub_table($spreadsheet, $sub_table_info_arr);
-
-
-
//创建下拉联动分表数据
-
$liandong_name = 'region'; // 这里的region为分表名称
-
create_excel_drop_down($spreadsheet, $sub_table_num, $liandong_name, $oneData); //无限极oneData 里面结构为[['text'=>'部门1_00001','children'=>[['text'=>'部门1子集1_00001']]]
-
-
-
// 初始化表头
-
$spreadsheet->setActiveSheetIndex(0);
-
$spreadsheet->getActiveSheet()->setTitle('导入模板');
-
-
foreach ($header as $key => $value) {
-
-
//$spreadsheet->getActiveSheet()->setCellValueByColumnAndRow($key, 1, $value['title']);
-
$spreadsheet->setActiveSheetIndex(0)->setCellValue($value['col'] . "1", $value['title']);
-
$spreadsheet->getActiveSheet()->getStyle($value['col'] . "1", $value['title'])->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED); //设置红色文字
-
-
// 设置每列宽度
-
$spreadsheet->getActiveSheet()->getColumnDimension($value['col'])->setWidth($value['width']);
-
//设置单元格格式为文本
-
if ($value['is_text'] == 1) {
-
$spreadsheet->getActiveSheet()->getStyle($value['col'])->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_TEXT);
-
}
-
}
-
$objActSheet = $spreadsheet->getActiveSheet();
-
-
// 输出下拉菜单
-
for ($i = 2; $i < 2000; $i ) {
-
-
foreach ($header as $key1 => $value1) {
-
//$this->set_select_cell($objActSheet,'A','id_type',$key,$i);
-
if ($value1['id_down_type'] == 1) {
-
//单纯下拉
-
set_select_cell($objActSheet, $value1['col'], $value1['sub_table_name'], $value1['count'], $value1['title'], $i);
-
} elseif ($value1['id_down_type'] == 2) {
-
//联动下拉
-
if ($value1['col'] == 'A' || $value1['col'] == 'E' || $value1['col'] == 'AC') {
-
switch ($value1['col']) {
-
case 'E':
-
$fit_col = ['F','G'];
-
break;
-
case 'AC':
-
//这里为关联 如AC 列 关联AD AE 这个
-
$fit_col = ['AD', 'AE'];
-
break;
-
}
-
$mian_col = $value1['col'];
-
//处理联动下拉
-
set_drop_down_select_cell($objActSheet, $mian_col, $fit_col, $value1['title'], $i,$liandong_name);
-
}
-
}
-
}
-
}
-
$path = "./";
-
$subject = "导出模板".date('YmdHis');
-
$ext = ".xlsx"; //后缀
-
$export = $path . $subject . $ext;
-
$writer = new Xlsx($spreadsheet);
-
//保存文件
-
$writer->save($export);
-
//关闭连接,销毁变量
-
$spreadsheet->disconnectWorksheets();
-
-
unset($spreadsheet);
-
var_dump($export);
-
die;
-
-
-
/**
-
* 创建Excel 分表
-
* @param obpe Excel 对象
-
* @param sub_table_info_arr 需要需要创建的分表二维数组 key title_name 分表表名标题 key index 分表顺序 key data 数据源 且数据源必须为 id text 键值对
-
* @param write_data 写入的数据 二维 包含 id text 两个键值
-
*/
-
function create_excel_sub_table($obpe, $sub_table_info_arr)
-
{
-
if (!empty($sub_table_info_arr)) {
-
foreach ($sub_table_info_arr as $key => $value) {
-
$obpe->createSheet();
-
-
$obpe->setActiveSheetIndex($value['index']); //设置序号
-
$obpe->getActiveSheet()->setTitle($value['title']); //设置表名
-
$obpe->getActiveSheet()->setSheetState(Worksheet::SHEETSTATE_HIDDEN); //隐藏
-
-
-
//密码
-
$obpe->getActiveSheet()->getProtection()->setPassword('PHPExcel');
-
$obpe->getActiveSheet()->getProtection()->setSheet(true);// This should be enabled inorder to enable any of thefollowing!
-
$obpe->getActiveSheet()->getProtection()->setSort(true);
-
$obpe->getActiveSheet()->getProtection()->setInsertRows(true);
-
$obpe->getActiveSheet()->getProtection()->setFormatCells(true);
-
-
-
$key = 1;
-
if (!empty($value['data'])) {
-
foreach ($value['data'] as $k => $v) {
-
$str = $v['text'] . '_' . $v['id'];
-
$obpe->getActiveSheet()->setCellValue('A' . $key, $str);
-
$key ;
-
}
-
}
-
}
-
}
-
}
-
-
/**
-
* 生成Excel 列
-
* @param int $num 需要的列数
-
* @return array
-
*/
-
function getExcelIndexRow($num=1){
-
$r = [];
-
$idx = ['idx' => 0];
-
for ($i = 0; $i < $num; $i ) {
-
$r[] = getNextIdx($idx);
-
}
-
unset($idx);
-
return $r;
-
}
-
-
-
function getNextIdx(&$idx) {
-
// 先转换26进制
-
$num26 = base_convert($idx['idx'], 10, 26);
-
-
$result = '';
-
// 对每一位进行字符转换
-
for ($i = 0; $i < strlen(strval($num26)); $i ) {
-
if ($i == 0 && strlen(strval($num26)) != 1) {
-
if (is_numeric($num26[$i])) {
-
$result.= chr(ord($num26[$i]) 16);
-
} else {
-
$result.= chr(ord($num26[$i]) - 23);
-
}
-
} else {
-
if (is_numeric($num26[$i])) {
-
$result.= chr(ord($num26[$i]) 17);
-
} else {
-
$result.= chr(ord($num26[$i]) - 22);
-
}
-
}
-
}
-
$idx['idx'] = $idx['idx'] 1;
-
return $result;
-
}
-
-
/**
-
*生成Excel 联动下拉
-
* @param obpe Excel对象句柄
-
* @param sub_table_num 分表序号
-
* @param table_name 分表名称
-
* @param data_source 需要处理的数据源
-
*/
-
function create_excel_drop_down($obpe, $sub_table_num, $table_name, $data_source)
-
{
-
//***********************
-
$obpe->createSheet();
-
$obpe->setActiveSheetIndex($sub_table_num);
-
$obpe->getActiveSheet()->setTitle($table_name);
-
$obpe->getActiveSheet()->setSheetState(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::SHEETSTATE_HIDDEN); //隐藏
-
-
//密码
-
$obpe->getActiveSheet()->getProtection()->setPassword('PHPExcel');
-
$obpe->getActiveSheet()->getProtection()->setSheet(true);// This should be enabled inorder to enable any of thefollowing!
-
$obpe->getActiveSheet()->getProtection()->setSort(true);
-
$obpe->getActiveSheet()->getProtection()->setInsertRows(true);
-
$obpe->getActiveSheet()->getProtection()->setFormatCells(true);
-
-
$key = 1;
-
//$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', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ', 'BA'];
-
//这里定义1000个下拉 若超过 1000个下拉 异常输出
-
$col = getExcelIndexRow(1000);
-
$high = 0;
-
-
//数据源格式必须按照以下格式规整 要不然数据处理出错
-
/*$re = [['name'=>'湖北省','children'=>[['name'=>"武汉市",'children' =>['江夏区','洪山区','青山区','武昌区']],['name'=>"宜昌市",'children' =>['江夏区1','洪山区1','青山区1','武昌区1']],['name'=>"荆州市",'children' =>['江夏区2','洪山区2','青山区2','武昌区2']]]],
-
['name'=>'湖南省','children'=>[['name'=>"长沙市",'children' =>['长沙市','长沙市1','长沙市3','长沙市6']],['name'=>"岳阳市",'children' =>['岳阳市4','岳阳市3','岳阳市2','岳阳市1']],[ 'name'=>"常德市",'children' =>['常德市1','常德市2','常德市3','常德市4']]]],];*/
-
foreach ($data_source as $k => $v) {
-
$str = $v['text'];
-
$obpe->getActiveSheet()->setCellValue($col[0] . ($key $high), $str);
-
$max = 0; // 重置max
-
$secondNum = count($v['children']);
-
if (!empty($v['children'])) {
-
foreach ($v['children'] as $index => $sendcond) {
-
$obpe->getActiveSheet()->setCellValue($col[$index 1] . ($key $high), $sendcond['text']);
-
//这里需要处理 若没有
-
if (isset($sendcond['children']) && $sendcond['children']) {
-
$thirdNum = count($sendcond['children']);
-
} else {
-
$thirdNum = 0;
-
}
-
-
if ($thirdNum > $max) {
-
$max = $thirdNum;
-
}
-
if (!empty($sendcond['children'])) {
-
foreach ($sendcond['children'] as $id => $third) {
-
//$obpe->getActiveSheet()->setCellValue($col[$index 1].($key $high $id 1),$third);
-
$obpe->getActiveSheet()->setCellValue($col[$index 1] . ($key $high $id 1), $third['text']);
-
}
-
//定义三级名称
-
$obpe->addNamedRange(
-
new NamedRange(
-
$sendcond['text'],
-
$obpe->getSheetByName($table_name),
-
$col[$index 1] . ($key $high 1) . ':' . $col[$index 1] . ($key $high 1 $thirdNum - 1)
-
)
-
);
-
}
-
}
-
//定义二级
-
$obpe->addNamedRange(
-
new NamedRange(
-
$v['text'],
-
$obpe->getSheetByName($table_name),
-
$col[1] . ($key $high) . ':' . $col[$secondNum] . ($key $high)
-
)
-
);
-
}
-
-
$high = $max;
-
$key ;
-
-
}
-
-
//移花
-
foreach ($data_source as $var => $cont) {
-
$obpe->getSheetByName($table_name)->setCellValue('UI' . ($var 1), $cont['text']);
-
}
-
$total = count($data_source);
-
$n = 1;
-
$obpe->addNamedRange(
-
new NamedRange(
-
$table_name,
-
$obpe->getSheetByName($table_name),
-
"=\$UI\${$n}:\$UI\${$total}"
-
)
-
);
-
}
-
-
-
/**
-
*设置Excel 下拉 2020年8月4日16:02:09
-
* @param objActSheet Excel对象句柄
-
* @param col 操作的列
-
* @param table_name Excel电子薄分表下拉名称
-
*/
-
function set_select_cell($objActSheet, $col = '', $table_name, $key = '', $setPromptTitle, $i = '', $setErrorTitle = '输入的值有误', $setError = '您输入的值不在下拉列表内')
-
{
-
$objValidation = $objActSheet->getCell("{$col}" . $i)->getDataValidation();
-
$objValidation->setType(DataValidation::TYPE_LIST)
-
// ->setErrorStyle(DataValidation::STYLE_INFORMATION)
-
->setErrorStyle(DataValidation::STYLE_STOP)
-
->setAllowBlank(true)
-
->setShowInputMessage(true)
-
->setShowErrorMessage(true)
-
->setShowDropDown(true)
-
->setErrorTitle('输入的值有误')
-
->setError('您输入的值不在下拉列表内')
-
->setPromptTitle("{$setPromptTitle}(在列表内选择)")
-
->setPrompt('请从列表中选择一个值')
-
->setFormula1($table_name . '!$A$1:$A$' . $key);
-
}
-
-
/**
-
* 设置联动下拉的Excel 渲染 2020年8月8日20:23:39
-
* @param objActSheet Excel对象句柄
-
* @param mian_col 联动下拉第一列
-
* @param fit_col 数组 子下拉节点 setFormula1 依次为下拉第一列名 后续依次关联
-
* @param setPromptTitle 中文字段映射名称
-
*/
-
function set_drop_down_select_cell($objActSheet, $main_col, $fit_col, $setPromptTitle, $i,$main_table_name='region')
-
{
-
$indirect_list_arr = $fit_col; //从主下拉节点开始计算
-
array_unshift($indirect_list_arr, $main_col);
-
//定义主表名
-
-
//处理主下拉
-
$objValidation = $objActSheet->getCell($main_col . $i)->getDataValidation();
-
$objValidation->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST)
-
->setErrorStyle(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_STOP)
-
->setAllowBlank(false)
-
->setShowInputMessage(true)
-
->setShowErrorMessage(true)
-
->setShowDropDown(true)
-
->setErrorTitle('输入的值有误')
-
->setError('您输入的值不在下拉列表内')
-
->setPromptTitle("{$setPromptTitle}(在列表内选择)")
-
//->setPromptTitle("type_list(在列表内选择)")
-
->setPrompt('请从列表中选择一个值')
-
->setFormula1("={$main_table_name}");
-
//处理分节点下拉
-
if (!empty($fit_col)) {
-
foreach ($fit_col as $key => $value) {
-
$objValidation = $objActSheet->getCell($value . $i)->getDataValidation();
-
$objValidation->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST)
-
->setErrorStyle(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_STOP)
-
->setAllowBlank(false)
-
->setShowInputMessage(true)
-
->setShowErrorMessage(true)
-
->setShowDropDown(true)
-
->setErrorTitle('输入的值有误')
-
->setError('您输入的值不在下拉列表内')
-
->setPromptTitle('请选择')
-
->setPrompt('请从列表中选择一个值')
-
->setFormula1('=INDIRECT($' . $indirect_list_arr[$key] . $i . ')');
-
}
-
}
-
return true;
-
}
步骤三: 运行php 我这里使用命令行
php index.php
查看即可
就是如此简单
这篇好文章是转载于:学新通技术网
- 版权申明: 本站部分内容来自互联网,仅供学习及演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,请提供相关证据及您的身份证明,我们将在收到邮件后48小时内删除。
- 本站站名: 学新通技术网
- 本文地址: /boutique/detail/tanhgfeggb
系列文章
更多
同类精品
更多
-
photoshop保存的图片太大微信发不了怎么办
PHP中文网 06-15 -
Android 11 保存文件到外部存储,并分享文件
Luke 10-12 -
word里面弄一个表格后上面的标题会跑到下面怎么办
PHP中文网 06-20 -
《学习通》视频自动暂停处理方法
HelloWorld317 07-05 -
photoshop扩展功能面板显示灰色怎么办
PHP中文网 06-14 -
微信公众号没有声音提示怎么办
PHP中文网 03-31 -
excel下划线不显示怎么办
PHP中文网 06-23 -
excel打印预览压线压字怎么办
PHP中文网 06-22 -
怎样阻止微信小程序自动打开
PHP中文网 06-13 -
TikTok加速器哪个好免费的TK加速器推荐
TK小达人 10-01