PHP操作EXCEL相关

phpExcel，操作excel很方便，尤其是可以方便的加入图片，支持jpg gif png格式。

下面是总结的几个使用方法

include ‘ PHPExcel . php’ ;
include ‘ PHPExcel / Writer / Excel2007 . php’ ;
//或者include ‘PHPExcel/Writer/Excel5.php’; 用于输出.xls的
创建一个 excel
$ objPHPExcel = new PHPExcel ( ) ;
保存 excel— 2007格式
$ objWriter = new PHPExcel_Writer_Excel2007 ( $ objPHPExcel ) ;
//或者$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 非2007格式
$ objWriter -> save (” xxx . xlsx” ) ;
直接输出到浏览器
$ objWriter = new PHPExcel_Writer_Excel5 ( $ objPHPExcel ) ;
header (” Pragma : public” ) ;
header (” Expires : 0″ ) ;
header (” Cache - Control : must - revalidate , post - check = 0 , pre - check = 0″ ) ;
header (” Content - Type : application / force - download” ) ;
header (” Content - Type : application / vnd . ms - execl” ) ;
header (” Content - Type : application / octet - stream” ) ;
header (” Content - Type : application / download” ) ; ;
header (’ Content - Disposition : attachment ; filename =” resume . xls”‘ ) ;
header (” Content - Transfer - Encoding : binary” ) ;
$ objWriter -> save (’ php : //output’);
——————————————————————————————————————–
设置 excel的属性：
创建人
$ objPHPExcel -> getProperties ( ) -> setCreator (” Maarten Balliauw” ) ;
最后修改人
$ objPHPExcel -> getProperties ( ) -> setLastModifiedBy (” Maarten Balliauw” ) ;
标题
$ objPHPExcel -> getProperties ( ) -> setTitle (” Office 2007 XLSX Test Document” ) ;
题目
$ objPHPExcel -> getProperties ( ) -> setSubject (” Office 2007 XLSX Test Document” ) ;
描述
$ objPHPExcel -> getProperties ( ) -> setDescription (” Test document for Office 2007 XLSX , generated using PHP classes .” ) ;
关键字
$ objPHPExcel -> getProperties ( ) -> setKeywords (” office 2007 openxml php” ) ;
种类
$ objPHPExcel -> getProperties ( ) -> setCategory (” Test result file” ) ;
——————————————————————————————————————–
设置当前的 sheet
$ objPHPExcel -> setActiveSheetIndex ( 0 ) ;
设置 sheet的 name
$ objPHPExcel -> getActiveSheet ( ) -> setTitle (’ Simple’ ) ;
设置单元格的值
$ objPHPExcel -> getActiveSheet ( ) -> setCellValue (’ A1′ , ‘ String’ ) ;
$ objPHPExcel -> getActiveSheet ( ) -> setCellValue (’ A2′ , 12 ) ;
$ objPHPExcel -> getActiveSheet ( ) -> setCellValue (’ A3′ , true ) ;
$ objPHPExcel -> getActiveSheet ( ) -> setCellValue (’ C5′ , ‘ = SUM ( C2 : C4 )’ ) ;
$ objPHPExcel -> getActiveSheet ( ) -> setCellValue (’ B8′ , ‘ = MIN ( B2 : C5 )’ ) ;
合并单元格
$ objPHPExcel -> getActiveSheet ( ) -> mergeCells (’ A18 : E22′ ) ;
分离单元格
$ objPHPExcel -> getActiveSheet ( ) -> unmergeCells (’ A28 : B28′ ) ;
保护 cell
$ objPHPExcel -> getActiveSheet ( ) -> getProtection ( ) -> setSheet ( true ) ; // Needs to be set to true in order to enable any worksheet protection!
$ objPHPExcel -> getActiveSheet ( ) -> protectCells (’ A3 : E13′ , ‘ PHPExcel’ ) ;
设置格式
// Set cell number formats
echo date (’ H : i : s’ ) . ” Set cell number formats \ n” ;
$ objPHPExcel -> getActiveSheet ( ) -> getStyle (’ E4′ ) -> getNumberFormat ( ) -> setFormatCode ( PHPExcel_Style_NumberFormat :: FORMAT_CURRENCY_EUR_SIMPLE ) ;
$ objPHPExcel -> getActiveSheet ( ) -> duplicateStyle ( $ objPHPExcel -> getActiveSheet ( ) -> getStyle (’ E4′ ) , ‘ E5 : E13′ ) ;
设置宽 width
// Set column widths
$ objPHPExcel -> getActiveSheet ( ) -> getColumnDimension (’ B’ ) -> setAutoSize ( true ) ;
$ objPHPExcel -> getActiveSheet ( ) -> getColumnDimension (’ D’ ) -> setWidth ( 12 ) ;
设置 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 ) ;
$ objPHPExcel -> getActiveSheet ( ) -> getStyle (’ E1′ ) -> getFont ( ) -> getColor ( ) -> setARGB ( PHPExcel_Style_Color :: COLOR_WHITE ) ;
$ objPHPExcel -> getActiveSheet ( ) -> getStyle (’ D13′ ) -> getFont ( ) -> setBold ( true ) ;
$ objPHPExcel -> getActiveSheet ( ) -> getStyle (’ E13′ ) -> getFont ( ) -> setBold ( true ) ;
设置 align
$ objPHPExcel -> getActiveSheet ( ) -> getStyle (’ D11′ ) -> getAlignment ( ) -> setHorizontal ( PHPExcel_Style_Alignment :: HORIZONTAL_RIGHT ) ;
$ objPHPExcel -> getActiveSheet ( ) -> getStyle (’ D12′ ) -> getAlignment ( ) -> setHorizontal ( PHPExcel_Style_Alignment :: HORIZONTAL_RIGHT ) ;
$ objPHPExcel -> getActiveSheet ( ) -> getStyle (’ D13′ ) -> getAlignment ( ) -> setHorizontal ( PHPExcel_Style_Alignment :: HORIZONTAL_RIGHT ) ;
$ objPHPExcel -> getActiveSheet ( ) -> getStyle (’ A18′ ) -> getAlignment ( ) -> setHorizontal ( PHPExcel_Style_Alignment :: HORIZONTAL_JUSTIFY ) ;
//垂直居中
$ objPHPExcel -> getActiveSheet ( ) -> getStyle (’ A18′ ) -> getAlignment ( ) -> setVertical ( PHPExcel_Style_Alignment :: VERTICAL_CENTER ) ;
设置 column的 border
$ objPHPExcel -> getActiveSheet ( ) -> getStyle (’ A4′ ) -> getBorders ( ) -> getTop ( ) -> setBorderStyle ( PHPExcel_Style_Border :: BORDER_THIN ) ;
$ objPHPExcel -> getActiveSheet ( ) -> getStyle (’ B4′ ) -> getBorders ( ) -> getTop ( ) -> setBorderStyle ( PHPExcel_Style_Border :: BORDER_THIN ) ;
$ objPHPExcel -> getActiveSheet ( ) -> getStyle (’ C4′ ) -> getBorders ( ) -> getTop ( ) -> setBorderStyle ( PHPExcel_Style_Border :: BORDER_THIN ) ;
$ objPHPExcel -> getActiveSheet ( ) -> getStyle (’ D4′ ) -> getBorders ( ) -> getTop ( ) -> setBorderStyle ( PHPExcel_Style_Border :: BORDER_THIN ) ;
$ objPHPExcel -> getActiveSheet ( ) -> getStyle (’ E4′ ) -> getBorders ( ) -> getTop ( ) -> setBorderStyle ( PHPExcel_Style_Border :: BORDER_THIN ) ;
设置 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 ( ) -> getTop ( ) -> getColor ( ) -> setARGB (’ FF993300′ ) ;
$ objPHPExcel -> getActiveSheet ( ) -> getStyle (’ E13′ ) -> 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′ ) ;
$ objPHPExcel -> getActiveSheet ( ) -> getStyle (’ B1′ ) -> getFill ( ) -> setFillType ( PHPExcel_Style_Fill :: FILL_SOLID ) ;
$ objPHPExcel -> getActiveSheet ( ) -> getStyle (’ B1′ ) -> getFill ( ) -> getStartColor ( ) -> setARGB (’ FF808080′ ) ;
加图片
$ objDrawing = new PHPExcel_Worksheet_Drawing ( ) ;
$ objDrawing -> setName (’ Logo’ ) ;
$ objDrawing -> setDescription (’ Logo’ ) ;
$ objDrawing -> setPath (’ . / images / officelogo . jpg’ ) ;
$ objDrawing -> setHeight ( 36 ) ;
$ objDrawing -> setWorksheet ( $ objPHPExcel -> getActiveSheet ( ) ) ;
$ objDrawing = new PHPExcel_Worksheet_Drawing ( ) ;
$ objDrawing -> setName (’ Paid’ ) ;
$ objDrawing -> setDescription (’ Paid’ ) ;
$ objDrawing -> setPath (’ . / images / paid . png’ ) ;
$ objDrawing -> setCoordinates (’ B15′ ) ;
$ objDrawing -> setOffsetX ( 110 ) ;
$ objDrawing -> setRotation ( 25 ) ;
$ objDrawing -> getShadow ( ) -> setVisible ( true ) ;
$ objDrawing -> getShadow ( ) -> setDirection ( 45 ) ;
$ objDrawing -> setWorksheet ( $ objPHPExcel -> getActiveSheet ( ) ) ;
在默认 sheet后，创建一个 worksheet
echo date (’ H : i : s’ ) . ” Create new Worksheet object \ n” ;
$ objPHPExcel -> createSheet ( ) ;

PHPEXCEL是一个国外的站点，地址：​​http://phpexcel.codeplex.com/​​