PHPExcel教程-优快云博客

本文介绍PHPExcel库的基本使用方法，包括创建、保存Excel文件，设置单元格格式、图片等，并提供了详细的代码示例。

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

下载地址：http://www.codeplex.com/PHPExcel

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

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();

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

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/

PHP操作EXCEL相关