风火家人开发记要

PHPExcel 生成excel并合并单元格

• 2017年11月22日2018年07月27日
• by chenxue4076
• 7 years ago

使用Excel模板 第一行定义字段名字 如下 7个字段 demo.xlsx 线路 项目 监测照片数量 实际挑选数量 缺失 可补拍点位 客户名称 数据如下 //整理数组 按 design_no, building_no 合并数据 $need_list_array = array( array( ‘route_name’ => ‘route_name’, ‘building_name’ => ‘building_name’, ‘report_photo_num’ => 1, ‘success_count’ => 1, ‘lost_count’ => 0, ‘position’ => array(11111,222222,333333,44444,555555), ‘design_name’ =>’design_name’, ), array(….) ); 主要代码 包括合并单元格 以上使用的 CI（CodeIgniter）框架 //PHPExcel require_once APPPATH. ‘/third_party/phpexcel/PHPExcel.php’; // Create new PHPExcel object $objPHPExcel = PHPExcel_IOFactory::load(APPPATH.’/resource/demo.xlsx’); $objPHPExcel->setActiveSheetIndex(0); $objActSheet = $objPHPExcel->getActiveSheet(); $base_row = 2; $col_array = array( ‘route_name’ => 0, ‘building_name’ => 1, ‘report_photo_num’ => 2, ‘success_count’ => 3, ‘lost_count’ => 4, ‘position’ => 5, ‘design_name’ => 6, ); //$temp_arr = array(); if( ! empty($need_list_array)) { $key = 0; foreach ($need_list_array as $design_building => $item) { $this_count = count($item[‘position’]); $objActSheet->setCellValueByColumnAndRow($col_array[‘route_name’], $base_row + $key, $item[‘route_name’]); $objActSheet->setCellValueByColumnAndRow($col_array[‘building_name’], $base_row + $key, $item[‘building_name’]); $objActSheet->setCellValueByColumnAndRow($col_array[‘report_photo_num’], $base_row + $key, $item[‘report_photo_num’]); $objActSheet->setCellValueByColumnAndRow($col_array[‘success_count’], $base_row + $key, $item[‘success_count’]); $objActSheet->setCellValueByColumnAndRow($col_array[‘lost_count’], $base_row + $key, $item[‘lost_count’]); foreach ($item[‘position’] as $i => $item_position) { $objActSheet->setCellValueByColumnAndRow($col_array[‘position’], $base_row + $key + $i, $item[‘position’][$i]); } $objActSheet->setCellValueByColumnAndRow($col_array[‘design_name’], $base_row + $key, $item[‘design_name’]); //合并单元格 if($this_count > 1) { $objActSheet->mergeCellsByColumnAndRow($col_array[‘route_name’], $base_row + $key, $col_array[‘route_name’], $base_row + $key + $this_count – 1); $objActSheet->getStyleByColumnAndRow($col_array[‘route_name’], $base_row + $key)->applyFromArray( array( ‘alignment’ => array( /*’horizontal’ => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,*/ ‘vertical’ => PHPExcel_Style_Alignment::VERTICAL_CENTER))); $objActSheet->mergeCellsByColumnAndRow($col_array[‘building_name’], $base_row + $key, $col_array[‘building_name’], $base_row + $key + $this_count –