/**
* 导出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);
for ($i = 0; $i < $all_cnt; ++$i) {
$k = $col_array[$i];
$val_array[$k] = $export_title[$key_array[$i]]['title'];
$cel_width_array[$k] = $export_title[$key_array[$i]]['width'];
$field_array[$i] = $key_array[$i];
$cel_array[$k] = 2;
}
@set_time_limit(120);
@ini_set('memory_limit', '1024M');
// 加载excel库(这里是CI加载)
load_libraries('PHPExcel'); // 导出
load_libraries('PHPExcel/IOFactory');
// 创建一个处理对象实例
$objExcel = new PHPExcel();
// 创建文件格式写入对象实例, uncomment
$objWriter = new PHPExcel_Writer_Excel2007($objExcel);
// 设置文档基本属性
$objProps = $objExcel->getProperties();
$objProps->setCreator("");//创建人
$objProps->setLastModifiedBy("");//最后修改人
$objProps->setTitle($file_name);//标题
$objProps->setSubject($file_name);//题目
$objProps->setDescription($file_name);//描述
$objProps->setKeywords("");//关键字
$objProps->setCategory("信息表");//种类
// 设置当前的sheet索引，用于后续的内容操作。
// 一般只有在使用多个sheet的时候才需要显示调用。
// 缺省情况下，PHPExcel会自动创建第一个sheet被设置SheetIndex=0
$objExcel->setActiveSheetIndex(0);
// 设置当前事项sheet的名称
$objExcel->getActiveSheet()->setTitle($file_name . "信息表");
//*************************************
// 设置宽度，这个值和EXCEL里的不同，不知道是什么单位，略小于EXCEL中的宽度
foreach ($cel_width_array as $k => $v) {
$objExcel->getActiveSheet()->getColumnDimension($k)->setWidth($v);
}
//设置单元格的值
$objExcel->getActiveSheet()->setCellValue('A1', $file_name . "信息表");
//合并单元格
$objExcel->getActiveSheet()->mergeCells('A1:' . $col_array[count($cel_width_array) - 1] . '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);
// 标题
foreach ($cel_array as $k => $v) {
$objExcel->getActiveSheet()->getStyle($k . $v)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // 设置居中对齐
// 设置边框样式
$objExcel->getActiveSheet()->getStyle($k . $v)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objExcel->getActiveSheet()->getStyle($k . $v)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objExcel->getActiveSheet()->getStyle($k . $v)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objExcel->getActiveSheet()->getStyle($k . $v)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objExcel->getActiveSheet()->setCellValue($k . $v, $val_array[$k]);
$objExcel->getActiveSheet()->setCellValueExplicit($k . $v, $val_array[$k], PHPExcel_Cell_DataType::TYPE_STRING);
// $objExcel->setCellValue($k.$n, iconv("GBK", "UTF-8", $val_array[$k]));
}
// 内容
foreach ($data_array as $k => $val) {
$n = $k + 3;
foreach ($col_array as $kk => $col) {
// 设置边框样式
$objExcel->getActiveSheet()->getStyle($col . $n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objExcel->getActiveSheet()->getStyle($col . $n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objExcel->getActiveSheet()->getStyle($col . $n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objExcel->getActiveSheet()->getStyle($col . $n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objExcel->getActiveSheet()->setCellValue($col . $n, $val[$field_array[$kk]]);
$objExcel->getActiveSheet()->setCellValueExplicit($col . $n, $val[$field_array[$kk]], PHPExcel_Cell_DataType::TYPE_STRING);
// $objExcel->setCellValue($k.$n, iconv("GBK", "UTF-8", $val[$field_array[$kk]]));
}
}
//*************************************
//输出内容
$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);