PHP, 原创, ,

PHPExcel 生成excel并合并单元格

使用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 - 1);
            $objActSheet->getStyleByColumnAndRow($col_array['building_name'], $base_row + $key)->applyFromArray( array( 'alignment'    =>  array(   /*'horizontal'  =>  PHPExcel_Style_Alignment::HORIZONTAL_CENTER,*/    'vertical'  =>  PHPExcel_Style_Alignment::VERTICAL_CENTER)));
            $objActSheet->mergeCellsByColumnAndRow($col_array['report_photo_num'], $base_row + $key, $col_array['report_photo_num'], $base_row + $key + $this_count - 1);
            $objActSheet->getStyleByColumnAndRow($col_array['report_photo_num'], $base_row + $key)->applyFromArray( array( 'alignment'    =>  array(/*'horizontal'  =>  PHPExcel_Style_Alignment::HORIZONTAL_CENTER,*/    'vertical'  =>  PHPExcel_Style_Alignment::VERTICAL_CENTER)));
            $objActSheet->mergeCellsByColumnAndRow($col_array['success_count'], $base_row + $key, $col_array['success_count'], $base_row + $key + $this_count - 1);
            $objActSheet->getStyleByColumnAndRow($col_array['success_count'], $base_row + $key)->applyFromArray( array( 'alignment'    =>  array(   /*'horizontal'  =>  PHPExcel_Style_Alignment::HORIZONTAL_CENTER,*/    'vertical'  =>  PHPExcel_Style_Alignment::VERTICAL_CENTER)));
            $objActSheet->mergeCellsByColumnAndRow($col_array['lost_count'], $base_row + $key, $col_array['lost_count'], $base_row + $key + $this_count - 1);
            $objActSheet->getStyleByColumnAndRow($col_array['lost_count'], $base_row + $key)->applyFromArray( array( 'alignment'    =>  array(      /*'horizontal'  =>  PHPExcel_Style_Alignment::HORIZONTAL_CENTER,*/    'vertical'  =>  PHPExcel_Style_Alignment::VERTICAL_CENTER)));
            $objActSheet->mergeCellsByColumnAndRow($col_array['design_name'], $base_row + $key, $col_array['design_name'], $base_row + $key + $this_count - 1);
            $objActSheet->getStyleByColumnAndRow($col_array['design_name'], $base_row + $key)->applyFromArray( array( 'alignment'    =>  array(     /*'horizontal'  =>  PHPExcel_Style_Alignment::HORIZONTAL_CENTER,*/    'vertical'  =>  PHPExcel_Style_Alignment::VERTICAL_CENTER)));
        }
        $key = $key + $this_count;
    }
    for($col = 0; $col < 7; $col++) { //此方法似乎无效，宽度自动的
        $objActSheet->getColumnDimensionByColumn($col)->setAutoSize(true);
    }
}

存储或者 直接下载， 下面是存储

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
@ob_end_clean();//清除缓冲区,避免乱码
//$objWriter->save('php://output');
$path = FCPATH.'tmp'.DIRECTORY_SEPARATOR . 'excel'.date('Y-m-d') . DIRECTORY_SEPARATOR;
$this->makeFilePath($path); //创建文件夹
$objWriter->save( $path . $file_name);

创建文件夹的方法

private function makeFilePath($path = '')
{
    $path = str_replace(FCPATH, '', $path);
    $path = trim($path, DIRECTORY_SEPARATOR);
    $path_array = explode(DIRECTORY_SEPARATOR, $path);
    $path_new = FCPATH;
    foreach ($path_array as $key => $p) {
        if( ! empty($p)) {
            $path_new .= $p . DIRECTORY_SEPARATOR;
            if( ! is_dir($path_new)) {
                mkdir($path_new);
            }
        }
    }
}

生成后直接下载代码

// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="publish_customer_lost_tmp_'.$city_no.'_'.$publish_date.'_'.time().'.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
//header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Expires: 0'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
@ob_end_clean();//清除缓冲区,避免乱码
$objWriter->save('php://output');

其中最难处理的是合并单元格，如果多个字段要分别合并单元格 则此方法肯定是行不通的， 需要多个数组分别存储，目前想到的方法感觉不算最好 就先不更新上来了。 欢迎加好友讨论。

 

 

(44)

Related Post