PHPExcel

require_once '/PHPExcel/PHPExcel.php';

$excel_filePath​ = '/模板.xls';

$excel_filePath = iconv('UTF-8', 'GBK//IGNORE', $excel_filePath);​//转换中文字符

$objPHPExcel = new PHPExcel();

$excelType = PHPExcel_IOFactory::identify($excel_filePath);//获取excel版本

​$objReader = PHPExcel_IOFactory::createReader($excelType);//以$excelType版本读取文件

​$objPHPExcel = $objReader->load($excel_filePath); //加载excel文件

​$objPHPExcel->getSheetCount();//获取工作表数量

$sheet = $objPHPExcel->getSheet(0);//读取第一个工作表

$highestRow = $sheet->getHighestRow(); // 取得总行数  

$highestColumm = $sheet->getHighestColumn(); // 取得总列数

//设置文档基本属性    

$objPHPExcel->getProperties()->setCreator('aaa')//创建人

       ->setLastModifiedBy('bbb')//最后修改人

       ->setTitle('cccc')//标题

       ->setSubject('dddd')//题目

       ->setDescription('eeee')//描述

       ->setKeywords('ffff')//关键字

       ->setCategory('ggggg');//种类

//设置单元格的值

​$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'String');

​$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A2', 12);

​$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A3', true);

​$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C5', '=SUM(C2:C4)');

​$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B8', '=MIN(B2:C5)');​

​//设置宽width

​$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);

​$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);

//设置居中​

​$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()

   ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)//左右居中

   ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//上下居中

//设置自动换行

$objPHPExcel->getActiveSheet()->getStyle('D1')->getAlignment()

   ->setWrapText(true)​;

//合并单元格

$objPHPExcel->getActiveSheet()->mergeCells('A1:D1');​

//分离单元格

​$objPHPExcel->getActiveSheet()->unmergeCells('A28:B28');​

//设置格式

​$objPHPExcel->getActiveSheet()->getStyle('E4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);

​$objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('E4'), 'E5:E13' );
​//设置font

​$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');

​$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(20);

​$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);

​$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);

​$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);

//设置align

​$objPHPExcel->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

​$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);​

//设置column的border

​$objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

//批量配置

$styleArray = array(  

       'borders' => array(  

           'allborders' => array(  

               'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  

           ),  

       ),  

   );

$objPHPExcel->getActiveSheet()->getStyle('A5:N5')->applyFromArray($styleArray);​

​

//设置border的color

​$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->getColor()->setARGB('FF993300');

​$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getTop()->getColor()->setARGB('FF993300');

​$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');

​$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getRight()->getColor()->setARGB('FF993300');​

//设置填充颜色

​$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);

​$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080');

//从excel文件中读取图片

foreach($objPHPExcel->getSheet(0)->getDrawingCollection() as $drawing) {

        $xy = $drawing->getCoordinates();//获取图片的位置，位置是根据图片左上角的点在的位置决定

        $imageFileName = $drawing->getIndexedFilename();//获取图片的文件名

        $path = $tmp_dir.$xy.'_'.$imageFileName;//要保存的路径

        //for xlsx

        if($drawing instanceof PHPExcel_Worksheet_Drawing){

               $filename = $drawing->getPath();//获取图片的路径，版本不同，读取方式不同

                copy($filename, $path);//复制保存图片到新路径

        }else if($drawing instanceof PHPExcel_Worksheet_MemoryDrawing){//for xls

               $image = $drawing->getImageResource();//03版本获取图片源方式

               $renderingFunction = $drawing->getRenderingFunction();  

               switch ($renderingFunction) {

               case PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG:  

                   imagejpeg($image, $path);  

                   break;

               case PHPExcel_Worksheet_MemoryDrawing::RENDERING_GIF:  

                   imagegif($image, $path);  

                   break;  

               case PHPExcel_Worksheet_MemoryDrawing::RENDERING_PNG:  

                   imagegif($image, $path);  

                   break;  

               case PHPExcel_Worksheet_MemoryDrawing::RENDERING_DEFAULT:  

                   imagegif($image, $path);  

                   break;

           }

        }

​//加图片

 $objDrawing = new PHPExcel_Worksheet_Drawing();

$objDrawing->setName('Logo');

$objDrawing->setDescription('Logo');

$objDrawing->setPath($path);

$objDrawing->setCoordinates('B4');

$objDrawing->setOffsetX(10);//左上角X轴向右位移10

$objDrawing->setRotation(90);

$objDrawing->getShadow()->setVisible(true);

$objDrawing->getShadow()->setDirection(45);

$objDrawing->setHeight(100);

$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());