PHPExcel 功能十分强大,但是没有发现有详细的使用说明,经常需要各种百度查找,因此总结一下,希望对大家能有帮助。
读取excel表模板
1 2 3 4 5 6 7 8 | $phpexcel = new \moonland\phpexcel\Excel(); fileName = '***.xlsx' ; //excel模板的地址 $format = \PHPExcel_IOFactory::identify( $fileName ); $objectreader = \PHPExcel_IOFactory::createReader( $format ); $worksheet = $objectreader ->load( $fileName ); //获取第一个sheet $sheet = $worksheet ->getSheet(0); |
设置单元格值
1 2 3 4 5 6 7 8 9 10 11 12 | //可连续设置多个值 $sheet ->setCellValue( "A2" , "测试A2" ) ->setCellValue( "A3" , "测试A3" ); //方式2 $sheet ->setCellValueByColumnAndRow( 'A' ,2, "测试A2" ) ->setCellValueByColumnAndRow( 'A' ,3, "测试A3" ); //方式3,设置内容的数据类型 $sheet ->setCellValueExplicit( 'A10' , '100' ,\PHPExcel_Cell_DataType::TYPE_STRING); $sheet ->setCellValueExplicit( 'A11' , '100' ,\PHPExcel_Cell_DataType::TYPE_NUMERIC); |
设置单元格样式
设置字体样式
在getStyle之后调用getFont,然后可以设置字体各个样式
1 2 3 4 5 6 7 8 9 10 11 12 | //创建颜色对象,设置颜色像css那样简单的传个色值,需要传对象 $color = new \PHPExcel_Style_Color(); $color ->setRGB( 'FF0000' ); $sheet ->setCellValue( "A10" , "测试A10" ); $sheet ->getStyle( 'A10' ) ->getFont() ->setName( '微软雅黑' ) //设置字体 ->setSize(14) //设置字体大小 ->setColor( $color ) //设置字体颜色 ->setBold(true) //是否家加粗 ->setItalic(true); //是否斜体 |
上面设置颜色比较麻烦,需要传个对象,还可以通过下面的方式,直接传颜色色值
1 | $sheet ->getStyle( 'A10' )->getFont()->getColor()->setRGB( '0000FF' ); |
设置单元格背景色
1 2 3 4 5 6 7 8 | $color = new \PHPExcel_Style_Color(); $color ->setRGB( 'FF0000' ); $sheet ->setCellValue( "A10" , "测试A10" ); $sheet ->getStyle( 'A10' ) ->getFill() ->setFillType(\PHPExcel_Style_Fill::FILL_SOLID) //设置填充类型 ->setStartColor( $color ); |
还可设置为渐变背景色
1 2 3 4 5 6 7 8 9 10 11 12 | $start_color = new \PHPExcel_Style_Color(); $start_color ->setRGB( 'FF0000' ); $end_color = new \PHPExcel_Style_Color(); $end_color ->setRGB( '00FF00' ); $sheet ->setCellValue( "A10" , "测试A10" ); $sheet ->getStyle( 'A10' ) ->getFill() ->setFillType(\PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR) // 设置填充类型为渐变 ->setStartColor( $start_color ) ->setEndColor( $end_color ); |
设置行高、列宽
1 2 3 | $sheet ->setCellValue( "A10" , "测试A10" ); $sheet ->getRowDimension(10)->setRowHeight(120); //设置第10行的行高 $sheet ->getColumnDimension( 'A' )->setWidth(10); //设置A列的宽度 |
设置单元格边框
getAllBorders设置选中区域所有单元格的边框
1 2 3 4 5 | $color = new \PHPExcel_Style_Color(); $color ->setRGB( 'FF0000' ); $sheet ->getStyle( 'A15:D20' )->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_DASHDOT); //设置边框样式 $sheet ->getStyle( 'A15:D20' )->getBorders()->getAllBorders()->setColor( $color ); //设置边框颜色 |
如果只设置顶部边框,则使用getTop
1 2 3 4 5 | $color = new \PHPExcel_Style_Color(); $color ->setRGB( 'FF0000' ); $sheet ->getStyle( 'A25:D30' )->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_DASHDOT); $sheet ->getStyle( 'A25:D30' )->getBorders()->getTop()->setColor( $color ); //只设置顶部边框颜色 |
单元格对齐方式
1 2 3 4 | $sheet ->getStyle( 'A10' ) ->getAlignment() ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER) //设置水平对齐方式 ->setVertical(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //设置垂直对齐方式 |
合并、拆分单元格
1 2 | $sheet ->mergeCells( 'A5:D5' ); //合并单元格 $sheet ->unmergeCells( 'B7:D7' ); //拆分单元格 |
导出图片
导出网络图片之前需要先把图片下载到本地
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | $temp_pic = $this ->download( $image_url , '/图片存储路径' ); $local_pic_path = '/图片存储路径' . $temp_pic ; $objDrawing = new \PHPExcel_Worksheet_Drawing(); $objDrawing ->setPath( $local_pic_path ); // 设置图片宽度高度 $objDrawing ->setHeight(80); //照片高度 $objDrawing ->setWidth(80); //照片宽度 /*设置图片要插入的单元格*/ $objDrawing ->setCoordinates( $position . $row_no ); // 图片偏移距离 $objDrawing ->setOffsetX(20); $objDrawing ->setOffsetY(20); $objDrawing ->setWorksheet( $sheet ); private function download( $url , $path = 'images/' ){ $ch = curl_init(); curl_setopt( $ch , CURLOPT_URL, $url ); curl_setopt( $ch , CURLOPT_RETURNTRANSFER, 1); curl_setopt( $ch , CURLOPT_CONNECTTIMEOUT, 30); curl_setopt( $ch , CURLOPT_SSL_VERIFYPEER, false); // 信任任何证书 $file = curl_exec( $ch ); curl_close( $ch ); $filename = pathinfo ( $url , PATHINFO_BASENAME); $resource = fopen ( $path . $filename , 'a' ); fwrite( $resource , $file ); fclose( $resource ); return $filename ; } |
导出之后默认打开第一个sheet
默认情况下,打开导出的excel是默认打开最后一个sheet,我们可以进行设置,如设置为打开第一个sheet
1 | $worksheet ->setActiveSheetIndex(0); |
导出excel
1 2 3 4 5 6 7 8 9 10 | header( "Content-Type: application/force-download" ); header( "Content-Type: application/octet-stream" ); header( "Content-Type: application/download" ); header( 'Content-Disposition:inline;filename="filename.text"' ); header( "Content-Transfer-Encoding: binary" ); header( "Expires: Mon, 26 Jul 1997 05:00:00 GMT" ); header( "Last-Modified: " . gmdate ( "D, d M Y H:i:s" ) . " GMT" ); header( "Cache-Control: must-revalidate, post-check=0, pre-check=0" ); header( "Pragma: no-cache" ); $phpexcel ->writeFile( $worksheet ); |
PHPWord使用方法整理:https://www.shanhuxueyuan.com/news/detail/124.html
PHPPresentation(PPT)使用方法整理:https://www.shanhuxueyuan.com/news/detail/123.html