今天接到一个这样的任务,导出这种格式的数据表这个表信息。每次碰到Excel的导入或者导出的问题都十分头疼,但是归根结底就是伪造数据结构,循环渲染。下面我先把完整的介绍一下操作流程:
这是之前开发的一个课程表,点击添加按钮填写的课程信息:
这是一个会员列表:
点击详细,弹出一系列操作框,我们仅展示试听课预约功能:
下图弹窗展示的就是课程信息,在周课表中添加的课程:
下面我说一下思路,通过课表上课时间的ID (
cac_id
)进行排序,那么为什么要排序呢,如果我们不排序的话,数据是紊乱的,会影响我们之后把数据填入到 excel 中。
不排序的话是这样的:
正确的是这样的:
排序后查询出来的数据如下:
array(4) {
[0] => array(25) {
["id"] => string(1) "3"
["is_sign"] => string(1) "0"
["origin"] => string(1) "0"
["created_at"] => string(19) "2019-01-11 15:34:53"
["height"] => string(5) "172cm"
["c_area"] => string(1) "3"
["cost"] => string(1) "0"
["course_cost"] => string(1) "0"
["cac_id"] => string(2) "30"
["assign_date"] => string(10) "2019-01-13"
["crm_week"] => string(1) "0"
["course"] => string(6) "数学"
["class"] => string(13) "测试班级3"
["teacher"] => string(9) "武老师"
["customer_id"] => string(2) "64"
["customer_name"] => string(7) "导出2"
["counselor"] => string(2) "47"
["channel"] => string(1) "3"
["birthday"] => string(10) "1994-12-15"
["parents"] => string(98) "[{"parent_role":"\u7238\u7238","parent_name":"\u554a\u5b9e\u6253\u5b9e","parent_phone":"1863222"}]"
["c_type"] => string(12) "网路渠道"
["channel_name"] => string(6) "快手"
["user_login"] => string(7) "店长2"
["start_time"] => string(5) "13:10"
["end_time"] => string(5) "15:50"
}
[1] => array(25) {
["id"] => string(1) "1"
["is_sign"] => string(1) "0"
["origin"] => string(1) "0"
["created_at"] => string(19) "2019-01-11 15:34:03"
["height"] => string(5) "172cm"
["c_area"] => string(1) "1"
["cost"] => string(1) "0"
["course_cost"] => string(1) "0"
["cac_id"] => string(2) "29"
["assign_date"] => string(10) "2019-01-13"
["crm_week"] => string(1) "0"
["course"] => string(6) "语文"
["class"] => string(13) "测试班级4"
["teacher"] => string(9) "武老师"
["customer_id"] => string(2) "64"
["customer_name"] => string(7) "导出2"
["counselor"] => string(2) "47"
["channel"] => string(1) "3"
["birthday"] => string(10) "1994-12-15"
["parents"] => string(98) "[{"parent_role":"\u7238\u7238","parent_name":"\u554a\u5b9e\u6253\u5b9e","parent_phone":"1863222"}]"
["c_type"] => string(12) "网路渠道"
["channel_name"] => string(6) "快手"
["user_login"] => string(7) "店长2"
["start_time"] => string(4) "9:10"
["end_time"] => string(5) "10:50"
}
[2] => array(25) {
["id"] => string(1) "2"
["is_sign"] => string(1) "0"
["origin"] => string(1) "0"
["created_at"] => string(19) "2019-01-11 15:34:28"
["height"] => string(5) "172cm"
["c_area"] => string(1) "2"
["cost"] => string(1) "0"
["course_cost"] => string(1) "0"
["cac_id"] => string(2) "29"
["assign_date"] => string(10) "2019-01-13"
["crm_week"] => string(1) "0"
["course"] => string(6) "语文"
["class"] => string(13) "测试班级4"
["teacher"] => string(9) "武老师"
["customer_id"] => string(2) "63"
["customer_name"] => string(7) "导出1"
["counselor"] => string(2) "47"
["channel"] => string(1) "3"
["birthday"] => string(10) "1994-12-15"
["parents"] => string(81) "[{"parent_role":"\u7238\u7238","parent_name":"sdaaaaa","parent_phone":"1863222"}]"
["c_type"] => string(12) "网路渠道"
["channel_name"] => string(6) "快手"
["user_login"] => string(7) "店长2"
["start_time"] => string(4) "9:10"
["end_time"] => string(5) "10:50"
}
[3] => array(25) {
["id"] => string(1) "4"
["is_sign"] => string(1) "0"
["origin"] => string(1) "0"
["created_at"] => string(19) "2019-01-11 15:38:25"
["height"] => string(5) "172cm"
["c_area"] => string(9) "路北区"
["cost"] => string(1) "0"
["course_cost"] => string(1) "0"
["cac_id"] => string(2) "29"
["assign_date"] => string(10) "2019-01-13"
["crm_week"] => string(1) "0"
["course"] => string(6) "语文"
["class"] => string(13) "测试班级4"
["teacher"] => string(9) "武老师"
["customer_id"] => string(1) "1"
["customer_name"] => string(9) "mason"
["counselor"] => string(2) "48"
["channel"] => string(1) "4"
["birthday"] => string(10) "1994-12-15"
["parents"] => string(102) "[{"parent_role":"\u7238\u7238","parent_name":"\u554a\u5b9e\u6253\u5b9e","parent_phone":"13803159382"}]"
["c_type"] => string(12) "网路渠道"
["channel_name"] => string(12) "大众点评"
["user_login"] => string(7) "员工2"
["start_time"] => string(4) "9:10"
["end_time"] => string(5) "10:50"
}
}
然后我们分析需求,我们发现三段数据格式一样,那么我们就提取其中一段,再将它分为三部分,很明显,③是我们查询出来的数据,可是查询出来的数据就是一个一维数组,那么问题来了 ,我们怎么才能打造成现在这样的格式呢?我们根据 cac_id
来进行区分,我们知道相同的 cac_id
连续排列,所以我们只需要每个 cac_id
第一次出现的位置的上面构造出 ① 和 ② 的数据,初步分析我们构造出来的数组中会出现 7个(就是:A B C D E F G 单元格的数据)
元素,那么 ② 的数据很容易,我们可以直接写死,然后压入到每组学生信息的前面,那么 ① 怎么办呢?也很简单,我们只需要伪造一个字符串,循环的时候判断如果是字符串类型我们就合并单元格,填入相关信息:
代码实现:
$excel = I("excel");
if (!empty($excel)) {
// 防止查询数量过大造成服务器负载,限制最大导出数量10000条, $count 就是我们数据的条目
if ($count <= 10000) {
//整合数组
$new_temp_arr = []; // 储存伪造数据
$temp_first_str = []; // 临时存放数组
foreach ($audition_book_list as $value) {
// 判断如果第一次出现就在前面压入一个 日期(上图所示①) 和 表头(上图所示②)
if (!in_array($value['cac_id'], $temp_first_str)) {
$new_temp_arr[] = date("Y年m月d日", strtotime($value['assign_date'])) . ' ' . $week[$value['crm_week']] . ' ' . $value['start_time'] . '--' . $value['end_time'];
$new_temp_arr[] = [
'customer_name' => '名字',
'birthday' => '年龄',
'height' => '身高',
'c_area' => '地区',
'cost' => '已收费用',
'course_cost' => '课程费用',
'remark' => '备注'
];
}
// 用来储存 cac_id 保证上面代码可执行
$temp_first_str[] = $value['cac_id'];
// 构造上图 ③ 所示数据
$new_temp_arr[] = [
'customer_name' => $value['customer_name'],
'birthday' => $value['birthday'],
'height' => $value['height'],
'c_area' => $value['c_area'],
'cost' => $value['cost'],
'course_cost' => $value['course_cost'],
'remark' => ''
];
}
// excel 表名
$xlsName = "试听预约数据表";
$expCellName = array(
array('customer_name', '名字'),
array('birthday', '年龄'),
array('height', '身高'),
array('c_area', '地区'),
array('cost', '已收费用'),
array('course_cost', '课程费用'),
array('remark', '备注')
);
$this->export_excel($xlsName, $expCellName, $new_temp_arr);
} else {
$this->error('最多可导出1000条!');
}
}
public function export_excel($expTitle, $expCellName, $expTableData)
{
// excel标题
$xlsTitle = iconv('utf-8', 'gb2312', $expTitle);
// 文件名称
$fileName = $xlsTitle . '-' . date('YmdHis');
// 单元格数量
$cellNum = count($expCellName);
// 数据量
$dataNum = count($expTableData);
// 导入表格类
vendor("PHPExcel.PHPExcel");
// 实例化
$objPHPExcel = new \PHPExcel();
// 获得当前活动sheet的活动对象
$obj_Sheet = $objPHPExcel->getActiveSheet();
// 设置当前活动Sheet名称
$obj_Sheet->setTitle('试听预约数据表');
// 单元格
$cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G');
// 设置表格内容垂直水平居中
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
// 设置 A4 纸张
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(\PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
// 设置行高
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(60);
// 隐藏网格线
$objPHPExcel->getActiveSheet()->setShowGridlines(false);
// 设置列宽
foreach ($cellName as $value) {
$objPHPExcel->getActiveSheet()->getColumnDimension($value)->setWidth(20);
}
// 设置边框样式
$styleThinBlackBorderOutline = array(
'borders' => array(
'allborders' => array( //设置全部边框
'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
),
),
);
// 填充边框
$objPHPExcel->getActiveSheet()->getStyle("A1:G1")->applyFromArray($styleThinBlackBorderOutline);
// 合并A1-G1单元格
$objPHPExcel->getActiveSheet()->mergeCells('A1:G1');
$objPHPExcel->getActiveSheet()->setCellValue('A1', '试听预约数据表');
// A1-G1 表头填充(实现填充,背景填充)
$objPHPExcel->getActiveSheet()->getStyle('A1:G1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
// $objPHPExcel->getActiveSheet()->getStyle('A1:G1')->getFill()->getStartColor()->setRGB('4A90E2');
// 设置字体
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true)->setSize(18);
// 设置内容区域(行高、自动换行)
for ($i = 0; $i < $dataNum; $i++) {
if (is_string($expTableData[$i])) {
$objPHPExcel->getActiveSheet()->mergeCells('A' . ($i + 2) . ':' . 'G' . ($i + 2));
$objPHPExcel->getActiveSheet()->getRowDimension(($i + 2))->setRowHeight(30);
$objPHPExcel->getActiveSheet()->setCellValue('A' . ($i + 2), $expTableData[$i]);
} else {
for ($j = 0; $j < $cellNum; $j++) {
$objPHPExcel->getActiveSheet()->getRowDimension(($i + 2))->setRowHeight(30);
$objPHPExcel->getActiveSheet()->setCellValue($cellName[$j] . ($i + 2), $expTableData[$i][$expCellName[$j][0]]);
$objPHPExcel->getActiveSheet()->getStyle($cellName[$j] . ($i + 2))->applyFromArray($styleThinBlackBorderOutline);
}
}
}
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="文件名.xls"');
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
}
最后保存如下:
大功告成: