PHP操作Excel – PHPExcel 基本用法详解

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

http://www.codeplex.com/PHPExcel

http://www.PHPexcel.net

开发包Tests目录有详细使用实例 支持中文,注意文件编码 文件保存为utf-8

写excel//Includeclass require_once('Classes/PHPExcel.PHP'); require_once('Classes/PHPExcel/Writer/Excel2007.PHP'); $objPHPExcel=newPHPExcel(); //Setproperties设置文件属性 $objPHPExcel->getProperties()->setCreator("MaartenBalliauw"); $objPHPExcel->getProperties()->setLastModifiedBy("MaartenBalliauw"); $objPHPExcel->getProperties()->setTitle("Office2007XLSXTestDocument"); $objPHPExcel->getProperties()->setSubject("Office2007XLSXTestDocument"); $objPHPExcel->getProperties()->setDescription("TestdocumentforOffice2007XLSX,generatedusingPHPclasses."); $objPHPExcel->getProperties()->setKeywords("office2007openxmlPHP"); $objPHPExcel->getProperties()->setCategory("Testresultfile"); //Addsomedata添加数据 $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setCellValue('A1','Hello');//可以指定位置 $objPHPExcel->getActiveSheet()->setCellValue('A2',true); $objPHPExcel->getActiveSheet()->setCellValue('A3',false); $objPHPExcel->getActiveSheet()->setCellValue('B2','world!'); $objPHPExcel->getActiveSheet()->setCellValue('B3',2); $objPHPExcel->getActiveSheet()->setCellValue('C1','Hello'); $objPHPExcel->getActiveSheet()->setCellValue('D2','world!'); //循环 for($i=1;$i<200;$i++){ $objPHPExcel->getActiveSheet()->setCellValue('A'.$i,$i); $objPHPExcel->getActiveSheet()->setCellValue('B'.$i,'Testvalue'); } //日期格式化 $objPHPExcel->getActiveSheet()->setCellValue('D1',time()); $objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH); //Addcomment添加注释 $objPHPExcel->getActiveSheet()->getComment('E11')->setAuthor('PHPExcel'); $objCommentRichText=$objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun('PHPExcel:'); $objCommentRichText->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun("\r\n"); $objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun('Totalamountonthecurrentinvoice,excludingVAT.'); //Addrich-textstring添加文字可设置样式 $objRichText=newPHPExcel_RichText($objPHPExcel->getActiveSheet()->getCell('A18')); $objRichText->createText('Thisinvoiceis'); $objPayable=$objRichText->createTextRun('payablewithinthirtydaysaftertheendofthemonth'); $objPayable->getFont()->setBold(true); $objPayable->getFont()->setItalic(true); $objPayable->getFont()->setColor(newPHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_DARKGREEN)); $objRichText->createText(',unlessspecifiedotherwiseontheinvoice.'); //Mergecells合并分离单元格 $objPHPExcel->getActiveSheet()->mergeCells('A18:E22'); $objPHPExcel->getActiveSheet()->unmergeCells('A18:E22'); //Protectcells保护单元格 $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);//Needstobesettotrueinordertoenableanyworksheetprotection! $objPHPExcel->getActiveSheet()->protectCells('A3:E13','PHPExcel'); //Setcellnumberformats数字格式化 $objPHPExcel->getActiveSheet()->getStyle('E4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE); $objPHPExcel->getActiveSheet()->duplicateStyle($objPHPExcel->getActiveSheet()->getStyle('E4'),'E5:E13'); //Setcolumnwidths设置列宽度 $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12); //Setfonts设置字体 $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); //Setalignments设置对齐 $objPHPExcel->getActiveSheet()->getStyle('D11')->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); $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setWrapText(true); //Setcolumnborders设置列边框 $objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getStyle('A10')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getStyle('E10')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK); $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK); //Setbordercolors设置边框颜色 $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'); //Setfills设置填充 $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080'); //Addahyperlinktothesheet添加链接 $objPHPExcel->getActiveSheet()->setCellValue('E26','www.PHPexcel.net'); $objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setUrl('http://www.PHPexcel.net'); $objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setTooltip('Navigatetowebsite'); $objPHPExcel->getActiveSheet()->getStyle('E26')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); //Addadrawingtotheworksheet添加图片 $objDrawing=newPHPExcel_Worksheet_Drawing(); $objDrawing->setName('logo'); $objDrawing->setDescription('logo'); $objDrawing->setPath('./images/officelogo.jpg'); $objDrawing->setHeight(36); $objDrawing->setCoordinates('B15'); $objDrawing->setOffsetX(110); $objDrawing->setRotation(25); $objDrawing->getShadow()->setVisible(true); $objDrawing->getShadow()->setDirection(45); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); //Playaroundwithinsertingandremovingrowsandcolumns $objPHPExcel->getActiveSheet()->insertNewRowBefore(6,10); $objPHPExcel->getActiveSheet()->removeRow(6,10); $objPHPExcel->getActiveSheet()->insertNewColumnBefore('E',5); $objPHPExcel->getActiveSheet()->removeColumn('E',5); //Addconditionalformatting $objConditional1=newPHPExcel_Style_Conditional(); $objConditional1->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS); $objConditional1->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_LESSTHAN); $objConditional1->setCondition('0'); $objConditional1->getStyle()->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED); $objConditional1->getStyle()->getFont()->setBold(true); //Setautofilter自动过滤 $objPHPExcel->getActiveSheet()->setAutoFilter('A1:C9'); //Hide"Phone"and"fax"column隐藏列 $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setVisible(false); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setVisible(false); //Setdocumentsecurity设置文档安全 $objPHPExcel->getSecurity()->setLockWindows(true); $objPHPExcel->getSecurity()->setLockStructure(true); $objPHPExcel->getSecurity()->setWorkbookPassword("PHPExcel"); //Setsheetsecurity设置工作表安全 $objPHPExcel->getActiveSheet()->getProtection()->setPassword('PHPExcel'); $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);//Thisshouldbeenabledinordertoenableanyofthefollowing! $objPHPExcel->getActiveSheet()->getProtection()->setSort(true); $objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true); $objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true); //Calculateddata计算 echo'ValueofB14[=COUNT(B2:B12)]:'.$objPHPExcel->getActiveSheet()->getCell('B14')->getCalculatedValue()."\r\n"; //Setoutlinelevels $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setOutlineLevel(1); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setVisible(false); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setCollapsed(true); //Freezepanes $objPHPExcel->getActiveSheet()->freezePane('A2'); //Rowstorepeatattop $objPHPExcel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1,1); //Setdatavalidation验证输入值 $objValidation=$objPHPExcel->getActiveSheet()->getCell('B3')->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('Inputerror'); $objValidation->setError('Numberisnotallowed!'); $objValidation->setPromptTitle('Allowedinput'); $objValidation->setPrompt('Onlynumbersbetween10and20areallowed.'); $objValidation->setFormula1(10); $objValidation->setFormula2(20); $objPHPExcel->getActiveSheet()->getCell('B3')->setDataValidation($objValidation); //Createanewworksheet,afterthedefaultsheet创建新的工作标签 $objPHPExcel->createSheet(); $objPHPExcel->setActiveSheetIndex(1); //Setheaderandfooter.Whennodifferentheadersforodd/evenareused,oddheaderisassumed.页眉页脚 $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&C&HPleasetreatthisdocumentasconfidential!'); $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B'.$objPHPExcel->getProperties()->getTitle().'&RPage&Pof&N'); //Setpageorientationandsize方向大小 $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4); //Renamesheet重命名工作表标签 $objPHPExcel->getActiveSheet()->setTitle('Simple'); //Setactivesheetindextothefirstsheet,soExcelopensthisasthefirstsheet $objPHPExcel->setActiveSheetIndex(0); //SaveExcel2007file保存 $objWriter=newPHPExcel_Writer_Excel2007($objPHPExcel); $objWriter->save(str_replace('.PHP','.xlsx',__FILE__)); //SaveExcel5file保存 require_once('Classes/PHPExcel/Writer/Excel5.PHP'); $objWriter=newPHPExcel_Writer_Excel5($objPHPExcel); $objWriter->save(str_replace('.PHP','.xls',__FILE__)); //1.6.2新版保存 require_once('Classes/PHPExcel/IOFactory.PHP'); $objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel2007'); $objWriter->save(str_replace('.PHP','.xls',__FILE__));读excel//Includeclass require_once('Classes/PHPExcel/Reader/Excel2007.PHP'); $objReader=newPHPExcel_Reader_Excel2007; $objPHPExcel=$objReader->load("05featuredemo.xlsx");读写csvrequire_once("05featuredemo.inc.PHP"); require_once('Classes/PHPExcel/Writer/CSV.PHP'); require_once('Classes/PHPExcel/Reader/CSV.PHP'); require_once('Classes/PHPExcel/Writer/Excel2007.PHP'); //WritetoCSVformat写 $objWriter=newPHPExcel_Writer_CSV($objPHPExcel); $objWriter->setDelimiter(';'); $objWriter->setEnclosure(''); $objWriter->setLineEnding("\r\n"); $objWriter->setSheetIndex(0); $objWriter->save(str_replace('.PHP','.csv',__FILE__)); //ReadfromCSVformat读 $objReader=newPHPExcel_Reader_CSV(); $objReader->setDelimiter(';'); $objReader->setEnclosure(''); $objReader->setLineEnding("\r\n"); $objReader->setSheetIndex(0); $objPHPExcelFromCSV=$objReader->load(str_replace('.PHP','.csv',__FILE__)); //WritetoExcel2007format $objWriter2007=newPHPExcel_Writer_Excel2007($objPHPExcelFromCSV); $objWriter2007->save(str_replace('.PHP','.xlsx',__FILE__));写htmlrequire_once("05featuredemo.inc.PHP"); require_once('Classes/PHPExcel/Writer/HTML.PHP'); //WritetoHTMLformat $objWriter=newPHPExcel_Writer_HTML($objPHPExcel); $objWriter->setSheetIndex(0); $objWriter->save(str_replace('.PHP','.htm',__FILE__));写pdfrequire_once("05featuredemo.inc.PHP"); require_once('Classes/PHPExcel/IOFactory.PHP'); //WritetoPDFformat $objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'PDF'); $objWriter->setSheetIndex(0); $objWriter->save(str_replace('.PHP','.pdf',__FILE__)); //Echomemorypeakusage echodate('H:i:s')."Peakmemoryusage:".(memory_get_peak_usage(true)/1024/1024)."MB\r\n";

版权声明：本文为CSDN博主「滴滴」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。