封装导出excel函数

函数"/>

封装导出excel函数

/*** 导出excel* @param array  $data_array   数据* @param array  $export_title 标题* @param string $file_name    文件名* @throws Exception*/
function export2excel($data_array, $export_title, $file_name)
{// 处理数据$all_cnt = count($export_title);$col_array = get_export_col($all_cnt); // 列$key_array = array_keys($export_title);$title_array = $width_array = $field_array = []; // 初始化 标题 宽度 字段for ($i = 0; $i < $all_cnt; ++$i) {$title_array[$col_array[$i]] = $export_title[$key_array[$i]]['title'];$width_array[$col_array[$i]] = $export_title[$key_array[$i]]['width'];$field_array[$i] = $key_array[$i];}@set_time_limit(120);@ini_set('memory_limit', '1024M');// 加载excel库load_libraries('PHPExcel');  // 导出load_libraries('PHPExcel/IOFactory');// 创建一个处理对象实例$objExcel = new PHPExcel();// 设置文档基本属性$objExcel->getProperties()->setCreator(""); // 创建人$objExcel->getProperties()->setLastModifiedBy(""); // 最后修改人$objExcel->getProperties()->setTitle($file_name); // 标题$objExcel->getProperties()->setSubject($file_name); // 题目$objExcel->getProperties()->setDescription($file_name); // 描述$objExcel->getProperties()->setKeywords(""); // 关键字$objExcel->getProperties()->setCategory("信息表"); // 种类// 设置当前的sheet索引，用于后续的内容操作。// 一般只有在使用多个sheet的时候才需要显示调用。// 缺省情况下，PHPExcel会自动创建第一个sheet被设置SheetIndex=0$objExcel->setActiveSheetIndex(0);// 设置当前事项sheet的名称$objExcel->getActiveSheet()->setTitle($file_name . "信息表");// 设置宽度，这个值和EXCEL里的不同，不知道是什么单位，略小于EXCEL中的宽度foreach ($width_array as $col => $width) {$objExcel->getActiveSheet()->getColumnDimension($col)->setWidth($width);}//设置单元格的值$objExcel->getActiveSheet()->setCellValue('A1', $file_name . "信息表");//合并单元格$objExcel->getActiveSheet()->mergeCells('A1:' . end($col_array) . '1');// 设置样式$objStyleA1 = $objExcel->getActiveSheet()->getStyle('A1');$objStyleA1->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);$objFontA1 = $objStyleA1->getFont();$objFontA1->setSize(18);$objFontA1->setBold(true);// 水平/垂直居中$objExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);$objExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);// 设置边框样式$style_array = ['borders' => [// 设置全部边框'allborders' => ['style' => \PHPExcel_Style_Border::BORDER_THIN // 粗的是thick],],];$objExcel->getActiveSheet()->getStyle('A2:' . end($col_array) . (count($data_array) + 2))->applyFromArray($style_array);// 标题foreach ($title_array as $col => $title) {$objExcel->getActiveSheet()->setCellValueExplicit($col . '2', $title, PHPExcel_Cell_DataType::TYPE_STRING);}// 内容$row = 3; // 从三行开始foreach ($data_array as $k => $value) {foreach ($col_array as $kk => $col) {$objExcel->getActiveSheet()->setCellValueExplicit($col . $row, $value[$field_array[$kk]], PHPExcel_Cell_DataType::TYPE_STRING);}++$row;}//输出内容$filename = iconv("utf-8", "gb2312", $file_name) . date('YmdHmi') . ".xlsx";ob_end_clean();ob_start();header("Content-Type:application/force-dVownload");header("Content-Type:application/vnd.ms-execl");header("Content-Type:application/octet-stream");
//            header('Content-Type:application/download'); // 提示下载，而不是直接打开！header("Content-Type:application/force-download");header('Content-Type:application/vnd.ms-excel;');header('Content-type: text/csv; charset=utf-8');//重要header('Content-Disposition:attachment;filename="' . $filename . '"');header('Cache-Control: must-revalidate, post-check=0,pre-check=0');header('Expires:0');header('Pragma:public');$objWriter = IOFactory::createWriter($objExcel, 'Excel2007');$objWriter->save('php://output');exit;
}$data_array = [0 => ['elevator_number' => 'DT00020','elevator_brand' => '三菱电梯','elevator_model' => '直梯','region' => '四川省-成都市-金牛区','project_name' => '大成都联合','property' => NULL,'residential_quarters_name' => '皇冠湖壹号','building_name' => '1号楼','computer_room_name' => '测试','is_sensor' => '未绑定','xuhao' => 1,],1 => ['elevator_number' => 'DT00019','elevator_brand' => '三菱电梯','elevator_model' => '直梯','region' => '四川省-成都市-龙泉驿区','project_name' => '天赋龙庭','property' => NULL,'residential_quarters_name' => '天赋龙庭','building_name' => '1期1单元','computer_room_name' => '1号机房','is_sensor' => '未绑定','xuhao' => 2,],2 => ['elevator_number' => 'DT00018','elevator_brand' => '日立电梯','elevator_model' => '直梯','region' => '四川省-成都市-龙泉驿区','project_name' => '高科.绿水东城','property' => NULL,'residential_quarters_name' => '绿水东城','building_name' => 'B1栋','computer_room_name' => '一号机房','is_sensor' => '未绑定','xuhao' => 3,],3 => ['elevator_number' => 'DT00017','elevator_brand' => '日立电梯','elevator_model' => '直梯','region' => '四川省-成都市-龙泉驿区','project_name' => '天府逸家','property' => NULL,'residential_quarters_name' => '--','building_name' => '1栋1单元','computer_room_name' => '1号电梯机房','is_sensor' => '未绑定','xuhao' => 4,],4 => ['elevator_number' => 'DT00016','elevator_brand' => '三菱电梯','elevator_model' => '直梯','region' => '北京市-市辖区-海淀区','project_name' => '万柳亿城中心','property' => NULL,'residential_quarters_name' => '万柳亿城','building_name' => '第一公寓','computer_room_name' => '1号机房','is_sensor' => '未绑定','xuhao' => 5,],5 => ['elevator_number' => 'DT00015','elevator_brand' => '三菱电梯','elevator_model' => '扶梯','region' => '重庆市-市辖区-江北区','project_name' => '恒大中央广场','property' => NULL,'residential_quarters_name' => '恒大中央广场','building_name' => 'A座','computer_room_name' => 'A座机房','is_sensor' => '未绑定','xuhao' => 6,],6 => ['elevator_number' => 'DT00014','elevator_brand' => '三菱电梯','elevator_model' => '直梯','region' => '四川省-成都市-龙泉驿区','project_name' => '保利玫瑰花语','property' => NULL,'residential_quarters_name' => '保利玫瑰花语','building_name' => '1号楼','computer_room_name' => '第2单元机房','is_sensor' => '未绑定','xuhao' => 7,],7 => ['elevator_number' => 'DT00013','elevator_brand' => '日立电梯','elevator_model' => '直梯','region' => '重庆市-市辖区-涪陵区','project_name' => '金座大厦','property' => NULL,'residential_quarters_name' => '金座大厦','building_name' => '1号楼','computer_room_name' => '1号机房','is_sensor' => '未绑定','xuhao' => 8,],8 => ['elevator_number' => 'DT00012','elevator_brand' => '三菱电梯','elevator_model' => '直梯','region' => '北京市-市辖区-海淀区','project_name' => '万柳亿城中心','property' => NULL,'residential_quarters_name' => '万柳亿城','building_name' => '第一公寓','computer_room_name' => '1号机房','is_sensor' => '未绑定','xuhao' => 9,],9 => ['elevator_number' => 'DT00011','elevator_brand' => '三菱电梯','elevator_model' => '直梯','region' => '四川省-成都市-龙泉驿区','project_name' => '保利玫瑰花语','property' => NULL,'residential_quarters_name' => '保利玫瑰花语','building_name' => '1号楼','computer_room_name' => '第1单元机房','is_sensor' => '未绑定','xuhao' => 10,],10 => ['elevator_number' => 'DT00010','elevator_brand' => '三菱电梯','elevator_model' => '直梯','region' => '四川省-成都市-龙泉驿区','project_name' => '保利玫瑰花语','property' => NULL,'residential_quarters_name' => '保利玫瑰花语','building_name' => '1号楼','computer_room_name' => '第1单元机房','is_sensor' => '未绑定','xuhao' => 11,],11 => ['elevator_number' => 'DT00009','elevator_brand' => '日立电梯','elevator_model' => '直梯','region' => '四川省-成都市-龙泉驿区','project_name' => '世茂城一期','property' => NULL,'residential_quarters_name' => '世茂城一期','building_name' => '1号楼','computer_room_name' => '2单元机房','is_sensor' => '未绑定','xuhao' => 12,],12 => ['elevator_number' => 'DT00008','elevator_brand' => '日立电梯','elevator_model' => '直梯','region' => '四川省-成都市-龙泉驿区','project_name' => '世茂城一期','property' => NULL,'residential_quarters_name' => '世茂城一期','building_name' => '1号楼','computer_room_name' => '2单元机房','is_sensor' => '未绑定','xuhao' => 13,],13 => ['elevator_number' => 'DT00007','elevator_brand' => '三菱电梯','elevator_model' => '直梯','region' => '四川省-成都市-龙泉驿区','project_name' => '世茂城一期','property' => NULL,'residential_quarters_name' => '世茂城一期','building_name' => '1号楼','computer_room_name' => '1单元机房','is_sensor' => '未绑定','xuhao' => 14,],14 => ['elevator_number' => 'DT00006','elevator_brand' => '日立电梯','elevator_model' => '直梯','region' => '四川省-成都市-龙泉驿区','project_name' => '世茂城一期','property' => NULL,'residential_quarters_name' => '世茂城一期','building_name' => '1号楼','computer_room_name' => '1单元机房','is_sensor' => '未绑定','xuhao' => 15,],15 => ['elevator_number' => 'DT00005','elevator_brand' => '日立电梯','elevator_model' => '直梯','region' => '四川省-成都市-龙泉驿区','project_name' => '启迪之星(成都龙泉驿)','property' => NULL,'residential_quarters_name' => '总部壹号','building_name' => 'G2栋','computer_room_name' => '2号机房','is_sensor' => '未绑定','xuhao' => 16,],16 => ['elevator_number' => 'DT00004','elevator_brand' => '日立电梯','elevator_model' => '直梯','region' => '四川省-成都市-龙泉驿区','project_name' => '启迪之星(成都龙泉驿)','property' => NULL,'residential_quarters_name' => '总部壹号','building_name' => 'G2栋','computer_room_name' => '2号机房','is_sensor' => '未绑定','xuhao' => 17,],17 => ['elevator_number' => 'DT00003','elevator_brand' => '三菱电梯','elevator_model' => '直梯','region' => '四川省-成都市-龙泉驿区','project_name' => '启迪之星(成都龙泉驿)','property' => NULL,'residential_quarters_name' => '总部壹号','building_name' => 'G2栋','computer_room_name' => '1号机房','is_sensor' => '未绑定','xuhao' => 18,],18 => ['elevator_number' => 'DT00002','elevator_brand' => '三菱电梯','elevator_model' => '直梯','region' => '四川省-成都市-龙泉驿区','project_name' => '启迪之星(成都龙泉驿)','property' => NULL,'residential_quarters_name' => '总部壹号','building_name' => 'G2栋','computer_room_name' => '1号机房','is_sensor' => '未绑定','xuhao' => 19,],19 => ['elevator_number' => 'DT00001','elevator_brand' => '三菱电梯','elevator_model' => '直梯','region' => '四川省-成都市-龙泉驿区','project_name' => '天府逸家','property' => NULL,'residential_quarters_name' => '--','building_name' => '1栋1单元','computer_room_name' => '1号电梯机房','is_sensor' => '未绑定','xuhao' => 20,],
];$export_title = ['xuhao' => ['title' => '序号','width' => 10,],'elevator_number' => ['title' => '电梯编号','width' => 15,],'elevator_brand' => ['title' => '设备品牌','width' => 15,],'elevator_model' => ['title' => '类型','width' => 10,],'region' => ['title' => '所属区域','width' => 30,],'project_name' => ['title' => '所属项目','width' => 25,],'property' => ['title' => '所属物业','width' => 15,],'residential_quarters_name' => ['title' => '所属小区','width' => 18,],'building_name' => ['title' => '所属楼宇','width' => 16,],'computer_room_name' => ['title' => '所属机房','width' => 20,],'is_sensor' => ['title' => '传感器','width' => 15,],
];$file_name = '电梯设备信息';export2excel(data_array, export_title, file_name);