使用PHPExcel操作Excel用法实例分析

PHPExcel下载地址：
http://www.codeplex.com/PHPExcel
http://www.phpexcel.net
开发包Tests目录有详细使用实例 支持中文,注意文件编码 文件保存为utf-8

1.header部分：

header("Content-Type:application/vnd.ms-excel");
header("Content-Disposition:attachment;filename=sample.xls"); 
header("Pragma:no-cache"); 
header("Expires:0");

2.写excel：

//Include class 
require_once(&＃39;Classes/PHPExcel.php&＃39;); 
require_once(&＃39;Classes/PHPExcel/Writer/Excel2007.php&＃39;); 
$objPHPExcel = new PHPExcel(); 
 
//Set properties 设置文件属性 
$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"); 
 
//Add some data 添加数据 
$objPHPExcel->setActiveSheetIndex(0); 
$objPHPExcel->getActiveSheet()->setCellValue(&＃39;A1&＃39;, &＃39;Hello&＃39;);//可以指定位置 
$objPHPExcel->getActiveSheet()->setCellValue(&＃39;A2&＃39;, true); 
$objPHPExcel->getActiveSheet()->setCellValue(&＃39;A3&＃39;, false); 
$objPHPExcel->getActiveSheet()->setCellValue(&＃39;B2&＃39;, &＃39;world!&＃39;); 
$objPHPExcel->getActiveSheet()->setCellValue(&＃39;B3&＃39;, 2); 
$objPHPExcel->getActiveSheet()->setCellValue(&＃39;C1&＃39;, &＃39;Hello&＃39;); 
$objPHPExcel->getActiveSheet()->setCellValue(&＃39;D2&＃39;, &＃39;world!&＃39;); 
 
//循环 
for($i = 1;$igetActiveSheet()->setCellValue(&＃39;A&＃39; . $i, $i); 
$objPHPExcel->getActiveSheet()->setCellValue(&＃39;B&＃39; . $i, &＃39;Test value&＃39;); 
} 
 
//日期格式化 
$objPHPExcel->getActiveSheet()->setCellValue(&＃39;D1&＃39;, time()); 
$objPHPExcel->getActiveSheet()->getStyle(&＃39;D1&＃39;)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH); 
 
//Add comment 添加注释 
$objPHPExcel->getActiveSheet()->getComment(&＃39;E11&＃39;)->setAuthor(&＃39;PHPExcel&＃39;); 
$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment(&＃39;E11&＃39;)->getText()->createTextRun(&＃39;PHPExcel:&＃39;); 
$objCommentRichText->getFont()->setBold(true); 
$objPHPExcel->getActiveSheet()->getComment(&＃39;E11&＃39;)->getText()->createTextRun("\r\n"); 
$objPHPExcel->getActiveSheet()->getComment(&＃39;E11&＃39;)->getText()->createTextRun(&＃39;Total amount on the current invoice, excluding VAT.&＃39;); 
 
//Add rich-text string 添加文字 可设置样式 
$objRichText = new PHPExcel_RichText( $objPHPExcel->getActiveSheet()->getCell(&＃39;A18&＃39;) ); 
$objRichText->createText(&＃39;This invoice is &＃39;); 
$objPayable = $objRichText->createTextRun(&＃39;payable within thirty days after the end of the month&＃39;); 
$objPayable->getFont()->setBold(true); 
$objPayable->getFont()->setItalic(true); 
$objPayable->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_DARKGREEN ) ); 
$objRichText->createText(&＃39;, unless specified otherwise on the invoice.&＃39;); 
 
//Merge cells 合并分离单元格 
$objPHPExcel->getActiveSheet()->mergeCells(&＃39;A18:E22&＃39;); 
$objPHPExcel->getActiveSheet()->unmergeCells(&＃39;A18:E22&＃39;); 
 
//Protect cells 保护单元格 
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);//Needs to be set to true in order to enable any worksheet protection! 
$objPHPExcel->getActiveSheet()->protectCells(&＃39;A3:E13&＃39;, &＃39;PHPExcel&＃39;); 
 
//Set cell number formats 数字格式化 
$objPHPExcel->getActiveSheet()->getStyle(&＃39;E4&＃39;)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE); 
$objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle(&＃39;E4&＃39;), &＃39;E5:E13&＃39; ); 
 
//Set column widths 设置列宽度 
$objPHPExcel->getActiveSheet()->getColumnDimension(&＃39;B&＃39;)->setAutoSize(true); 
$objPHPExcel->getActiveSheet()->getColumnDimension(&＃39;D&＃39;)->setWidth(12); 
 
//Set fonts 设置字体 
$objPHPExcel->getActiveSheet()->getStyle(&＃39;B1&＃39;)->getFont()->setName(&＃39;Candara&＃39;); 
$objPHPExcel->getActiveSheet()->getStyle(&＃39;B1&＃39;)->getFont()->setSize(20); 
$objPHPExcel->getActiveSheet()->getStyle(&＃39;B1&＃39;)->getFont()->setBold(true); 
$objPHPExcel->getActiveSheet()->getStyle(&＃39;B1&＃39;)->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE); 
$objPHPExcel->getActiveSheet()->getStyle(&＃39;B1&＃39;)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE); 
 
//Set alignments 设置对齐 
$objPHPExcel->getActiveSheet()->getStyle(&＃39;D11&＃39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); 
$objPHPExcel->getActiveSheet()->getStyle(&＃39;A18&＃39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY); 
$objPHPExcel->getActiveSheet()->getStyle(&＃39;A18&＃39;)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); 
$objPHPExcel->getActiveSheet()->getStyle(&＃39;A3&＃39;)->getAlignment()->setWrapText(true); 
 
//Set column borders 设置列边框 
$objPHPExcel->getActiveSheet()->getStyle(&＃39;A4&＃39;)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); 
$objPHPExcel->getActiveSheet()->getStyle(&＃39;A10&＃39;)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); 
$objPHPExcel->getActiveSheet()->getStyle(&＃39;E10&＃39;)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); 
$objPHPExcel->getActiveSheet()->getStyle(&＃39;D13&＃39;)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK); 
$objPHPExcel->getActiveSheet()->getStyle(&＃39;E13&＃39;)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK); 
 
//Set border colors 设置边框颜色 
$objPHPExcel->getActiveSheet()->getStyle(&＃39;D13&＃39;)->getBorders()->getLeft()->getColor()->setARGB(&＃39;FF993300&＃39;); 
$objPHPExcel->getActiveSheet()->getStyle(&＃39;D13&＃39;)->getBorders()->getTop()->getColor()->setARGB(&＃39;FF993300&＃39;); 
$objPHPExcel->getActiveSheet()->getStyle(&＃39;D13&＃39;)->getBorders()->getBottom()->getColor()->setARGB(&＃39;FF993300&＃39;); 
$objPHPExcel->getActiveSheet()->getStyle(&＃39;E13&＃39;)->getBorders()->getRight()->getColor()->setARGB(&＃39;FF993300&＃39;); 
 
//Set fills 设置填充 
$objPHPExcel->getActiveSheet()->getStyle(&＃39;A1&＃39;)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); 
$objPHPExcel->getActiveSheet()->getStyle(&＃39;A1&＃39;)->getFill()->getStartColor()->setARGB(&＃39;FF808080&＃39;); 
 
//Add a hyperlink to the sheet 添加链接 
$objPHPExcel->getActiveSheet()->setCellValue(&＃39;E26&＃39;, &＃39;www.phpexcel.net&＃39;); 
$objPHPExcel->getActiveSheet()->getCell(&＃39;E26&＃39;)->getHyperlink()->setUrl(&＃39;http://www.phpexcel.net&＃39;); 
$objPHPExcel->getActiveSheet()->getCell(&＃39;E26&＃39;)->getHyperlink()->setTooltip(&＃39;Navigate to website&＃39;); 
$objPHPExcel->getActiveSheet()->getStyle(&＃39;E26&＃39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); 
 
//Add a drawing to the worksheet 添加图片 
$objDrawing = new PHPExcel_Worksheet_Drawing(); 
$objDrawing->setName(&＃39;Logo&＃39;); 
$objDrawing->setDescription(&＃39;Logo&＃39;); 
$objDrawing->setPath(&＃39;./images/officelogo.jpg&＃39;); 
$objDrawing->setHeight(36); 
$objDrawing->setCoordinates(&＃39;B15&＃39;); 
$objDrawing->setOffsetX(110); 
$objDrawing->setRotation(25); 
$objDrawing->getShadow()->setVisible(true); 
$objDrawing->getShadow()->setDirection(45); 
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); 
 
//Play around with inserting and removing rows and columns 
$objPHPExcel->getActiveSheet()->insertNewRowBefore(6, 10); 
$objPHPExcel->getActiveSheet()->removeRow(6, 10); 
$objPHPExcel->getActiveSheet()->insertNewColumnBefore(&＃39;E&＃39;, 5); 
$objPHPExcel->getActiveSheet()->removeColumn(&＃39;E&＃39;, 5); 
 
//Add conditional formatting 
$objConditional1 = new PHPExcel_Style_Conditional(); 
$objConditional1->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS); 
$objConditional1->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_LESSTHAN); 
$objConditional1->setCondition(&＃39;0&＃39;); 
$objConditional1->getStyle()->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED); 
$objConditional1->getStyle()->getFont()->setBold(true); 
 
//Set autofilter 自动过滤 
$objPHPExcel->getActiveSheet()->setAutoFilter(&＃39;A1:C9&＃39;); 
 
//Hide "Phone" and "fax" column 隐藏列 
$objPHPExcel->getActiveSheet()->getColumnDimension(&＃39;C&＃39;)->setVisible(false); 
$objPHPExcel->getActiveSheet()->getColumnDimension(&＃39;D&＃39;)->setVisible(false); 
 
//Set document security 设置文档安全 
$objPHPExcel->getSecurity()->setLockWindows(true); 
$objPHPExcel->getSecurity()->setLockStructure(true); 
$objPHPExcel->getSecurity()->setWorkbookPassword("PHPExcel"); 
 
//Set sheet security 设置工作表安全 
$objPHPExcel->getActiveSheet()->getProtection()->setPassword(&＃39;PHPExcel&＃39;); 
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
// This should be enabled in order to enable any of the following! 
$objPHPExcel->getActiveSheet()->getProtection()->setSort(true); 
$objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true); 
$objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true); 
 
//Calculated data 计算 
echo &＃39;Value of B14 [=COUNT(B2:B12)]: &＃39; . 
$objPHPExcel->getActiveSheet()->getCell(&＃39;B14&＃39;)->getCalculatedValue() . "\r\n"; 
 
//Set outline levels 
$objPHPExcel->getActiveSheet()->getColumnDimension(&＃39;E&＃39;)->setOutlineLevel(1); 
$objPHPExcel->getActiveSheet()->getColumnDimension(&＃39;E&＃39;)->setVisible(false); 
$objPHPExcel->getActiveSheet()->getColumnDimension(&＃39;E&＃39;)->setCollapsed(true); 
 
//Freeze panes 
$objPHPExcel->getActiveSheet()->freezePane(&＃39;A2&＃39;); 
 
//Rows to repeat at top 
$objPHPExcel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 1); 
 
//Set data validation 验证输入值 
$objValidation = $objPHPExcel->getActiveSheet()->getCell(&＃39;B3&＃39;)->getDataValidation(); 
$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_WHOLE ); 
$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_STOP );
$objValidation->setAllowBlank(true);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
$objValidation->setErrorTitle(&＃39;Input error&＃39;);
$objValidation->setError(&＃39;Number is not allowed!&＃39;);
$objValidation->setPromptTitle(&＃39;Allowed input&＃39;);
$objValidation->setPrompt(&＃39;Only numbers between 10 and 20 are allowed.&＃39;); 
$objValidation->setFormula1(10);
$objValidation->setFormula2(20);
$objPHPExcel->getActiveSheet()->getCell(&＃39;B3&＃39;)->setDataValidation($objValidation);
 
//Create a new worksheet, after the default sheet 创建新的工作标签
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(1);
 
//Set header and footer. When no different headers for odd/even are used, odd header is assumed. 页眉页脚
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader(&＃39;&C&HPlease treat this document as confidential!&＃39;);
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter(&＃39;&L&B&＃39; . 
$objPHPExcel->getProperties()->getTitle() . &＃39;&RPage &P of &N&＃39;);
 
//Set page orientation and size 方向大小
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
 
//Rename sheet 重命名工作表标签 
$objPHPExcel->getActiveSheet()->setTitle(&＃39;Simple&＃39;);
 
//Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
 
//Save Excel 2007 file 保存 
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save(str_replace(&＃39;.php&＃39;, &＃39;.xlsx&＃39;, __FILE__));
 
//Save Excel 5 file 保存 
require_once(&＃39;Classes/PHPExcel/Writer/Excel5.php&＃39;); 
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 
$objWriter->save(str_replace(&＃39;.php&＃39;, &＃39;.xls&＃39;, __FILE__));
 
//1.6.2新版保存 
require_once(&＃39;Classes/PHPExcel/IOFactory.php&＃39;);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, &＃39;Excel2007&＃39;); 
$objWriter->save(str_replace(&＃39;.php&＃39;, &＃39;.xls&＃39;, __FILE__));

3.读excel

//Include class 
require_once(&＃39;Classes/PHPExcel/Reader/Excel2007.php&＃39;); 
$objReader = new PHPExcel_Reader_Excel2007; 
$objPHPExcel = $objReader->load("05featuredemo.xlsx");

4.读写csv

require_once("05featuredemo.inc.php");
require_once(&＃39;Classes/PHPExcel/Writer/CSV.php&＃39;);
require_once(&＃39;Classes/PHPExcel/Reader/CSV.php&＃39;);
require_once(&＃39;Classes/PHPExcel/Writer/Excel2007.php&＃39;); 
//Write to CSV format 写 
$objWriter = new PHPExcel_Writer_CSV($objPHPExcel);
$objWriter->setDelimiter(&＃39;;&＃39;); 
$objWriter->setEnclosure(&＃39;&＃39;); 
$objWriter->setLineEnding("\r\n"); 
$objWriter->setSheetIndex(0); 
$objWriter->save(str_replace(&＃39;.php&＃39;, &＃39;.csv&＃39;, __FILE__));
//Read from CSV format 读 
$objReader = new PHPExcel_Reader_CSV();
$objReader->setDelimiter(&＃39;;&＃39;); 
$objReader->setEnclosure(&＃39;&＃39;); 
$objReader->setLineEnding("\r\n");
$objReader->setSheetIndex(0); 
$objPHPExcelFromCSV = $objReader->load(str_replace(&＃39;.php&＃39;, &＃39;.csv&＃39;, __FILE__));
//Write to Excel2007 format 
$objWriter2007 = new PHPExcel_Writer_Excel2007($objPHPExcelFromCSV);
$objWriter2007->save(str_replace(&＃39;.php&＃39;, &＃39;.xlsx&＃39;, __FILE__));

5.写html

require_once("05featuredemo.inc.php"); 
require_once(&＃39;Classes/PHPExcel/Writer/HTML.php&＃39;);
//Write to HTML format 
$objWriter = new PHPExcel_Writer_HTML($objPHPExcel);
$objWriter->setSheetIndex(0); 
$objWriter->save(str_replace(&＃39;.php&＃39;, &＃39;.htm&＃39;, __FILE__));

6.写pdf

require_once("05featuredemo.inc.php");
require_once(&＃39;Classes/PHPExcel/IOFactory.php&＃39;);
//Write to PDF format
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, &＃39;PDF&＃39;);
$objWriter->setSheetIndex(0);
$objWriter->save(str_replace(&＃39;.php&＃39;, &＃39;.pdf&＃39;, __FILE__));
//Echo memory peak usage
echo date(&＃39;H:i:s&＃39;)." Peak memory usage: ".(memory_get_peak_usage(true) / 1024 / 1024)." MB\r\n";

以上就是使用PHPExcel操作Excel用法实例分析_php技巧的内容，更多相关内容请关注PHP中文网（www.php1.cn）！