phpExcel

业务场景经常会遇到excel导出的需求，这时候引用phpExcel是不错的选择。针对有特殊显示需求的excel，我封装了一个导出的函数。

phpExcel下载传送门 http://phpexcel.codeplex.com/releases/view/119187

<?php
class Excel_model{
    public function __construct()
    {
        include_once('PHPExcel/IOFactory.php');
        include_once('PHPExcel.php');
    }

    public function demo(){
        $data = array(
            '0' => array(
                'cell' => 'A1', //标签位置
                'value' => 'hi', //填充内容 string或array格式
                'bg_color' => '00E0EEE0', //标签背景颜色
                'vertical_center' => true, //垂直居中
                'horizontal_center' => true, //水平居中
            ),
            '1' => array(
                'cell' => 'B2:C3', //标签位置
                'value' => array( //填充内容 string或array格式
                    array(
                        'text' => "hello\n",
                        'color' => '0076EE00'
                    ),
                    array(
                        'text' => "world",
                        'color' => '00EE4000'
                    ),
                ),
                'vertical_center' => true, //垂直居中
                'border' => true, //标签边框
            )
        );
        $this->export_data('test', $data);
    }

    public function export_data($file_name,$data){
        $objPHPExcel = new PHPExcel();
        foreach ($data as $key => $item){
            if (!isset($item['value']) || !isset($item['cell'])){
                continue;
            }
            if (preg_match('/^[A-Z]*[1-9][0-9]*$/',$item['cell'])){
                $merge_flag = 0;
                $cell = $item['cell'];
            } elseif(preg_match('/^[A-Z]*[1-9][0-9]*:[A-Z]*[1-9][0-9]*$/',$item['cell'])){
                $merge_flag = 1;
                $cell_array = explode(':',$item['cell']);
                $cell = $cell_array[0];
            } else{
                continue;
            }

            $objRichText = new PHPExcel_RichText();
            $wap_flag = 0;
            if (is_array($item['value'])){
                foreach($item['value'] as $value){
                    $objPayable = $objRichText->createTextRun($value['text']);
                    //字体颜色
                    if (isset($value['color']) && $value['color']){
                        $objPayable->getFont()->setColor( new PHPExcel_Style_Color($value['color'] ) );
                    }
                    //检测换行符
                    if (strpos($value['text'], "\n")){
                        $wap_flag = 1;
                    }
                }
            } else{
                $objRichText->createTextRun($item['value']);
                //检测换行符
                if (strpos($item['value'], "\n")){
                    $wap_flag = 1;
                }
            }
            $objPHPExcel->getActiveSheet()->getCell($cell)->setValue($objRichText);
            if ($wap_flag){
                $objPHPExcel->getActiveSheet()->getStyle($item['cell'])->getAlignment()->setWrapText(true);
            }

            //垂直居中
            if (isset($item['vertical_center']) && $item['vertical_center']){
                $objPHPExcel->getActiveSheet()->getStyle($item['cell'])->applyFromArray(array('alignment'=>array('vertical'=>PHPExcel_Style_Alignment::VERTICAL_CENTER)));
            }
            //水平居中
            if (isset($item['horizontal_center']) && $item['horizontal_center']){
                $objPHPExcel->getActiveSheet()->getStyle($item['cell'])->applyFromArray(array('alignment'=>array('horizontal'=>PHPExcel_Style_Alignment::HORIZONTAL_CENTER)));
            }

            //单元格合并
            if ($merge_flag){
                $objPHPExcel->setActiveSheetIndex(0)->mergeCells($item['cell']);
            }
            //边框
            if (isset($item['border']) && $item['border']){
                $objPHPExcel->getActiveSheet()->getStyle($item['cell'])->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                $objPHPExcel->getActiveSheet()->getStyle($item['cell'])->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                $objPHPExcel->getActiveSheet()->getStyle($item['cell'])->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                $objPHPExcel->getActiveSheet()->getStyle($item['cell'])->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

            }

            //标签颜色
            if (isset($item['bg_color']) && $item['bg_color']){
                $objPHPExcel->getActiveSheet()->getStyle($cell)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                $objPHPExcel->getActiveSheet()->getStyle($cell)->getFill()->getStartColor()->setARGB($item['bg_color']);
            }

        }
        
        $objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
        //发送标题强制用户下载文件
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename='.$file_name.'.xls');
        header('Cache-Control: max-age=0');
        $objWriter->save('php://output');
    }
}
?>