retrievedthinkphp

PHPExcelDeveloperDocumentationContentsPHPExcelDeveloperDocumentation11.
Contents22.
Prerequisites42.
1.
Softwarerequirements42.
2.
Installationinstructions42.
3.
Usefullinksandtools42.
3.
1.
OpenXML/SpreadsheetML42.
3.
2.
Frequentlyaskedquestions42.
3.
3.
Tutorials53.
Architecture63.
1.
Schematical63.
2.
Spreadsheetinmemory63.
3.
Readersandwriters64.
Creatingaspreadsheet84.
1.
ThePHPExcelclass84.
2.
Worksheets84.
3.
Accessingcells84.
3.
1.
Settingacellvaluebycoordinate84.
3.
2.
Retrievingacellbycoordinate84.
3.
3.
Settingacellvaluebycolumnandrow84.
3.
4.
Retrievingacellbycolumnandrow84.
3.
5.
Loopingcells84.
3.
6.
Usingvaluebinderstofacilitatedataentry104.
4.
PHPExcelrecipes104.
4.
1.
Settingaspreadsheet'smetadata104.
4.
2.
Settingaspreadsheet'sactivesheet114.
4.
3.
Writeadateintoacell114.
4.
4.
Writeaformulaintoacell124.
4.
5.
Explicitlysetacell'sdatatype124.
4.
6.
ChangeacellintoaclickableURL124.
4.
7.
Settingaworksheet'spageorientationandsize124.
4.
8.
Centerapagehorizontally/vertically124.
4.
9.
Settingtheprintheaderandfooterofaworksheet134.
4.
10.
Settingprintingbreaksonaroworcolumn144.
4.
11.
Show/hidegridlineswhenprinting144.
4.
12.
Settingrows/columnstorepeatattop/left144.
4.
13.
Specifyprintingarea144.
4.
14.
Formattingacell144.
4.
15.
Settingthedefaultstyleofaworksheet164.
4.
16.
Conditionalformattingacell164.
4.
17.
Addacommenttoacell164.
4.
18.
Applyautofiltertoarangeofcells174.
4.
19.
Settingsecurityonaspreadsheet174.
4.
20.
Settingdatavalidationonacell174.
4.
21.
Settingacolumn'swidth184.
4.
22.
Show/hideacolumn184.
4.
23.
Group/outlineacolumn184.
4.
24.
Settingarow'sheight194.
4.
25.
Show/hidearow194.
4.
26.
Group/outlinearow194.
4.
27.
Merge/unmergecells194.
4.
28.
Insertingrows/columns194.
4.
29.
Addadrawingtoaworksheet204.
4.
30.
Addrichtexttoacell204.
4.
31.
Defineanamedrange204.
4.
32.
Redirectoutputtoaclient'swebbrowser214.
4.
33.
Settingthedefaultcolumnwidth214.
4.
34.
Settingthedefaultrowheight224.
4.
35.
AddaGDdrawingtoaworksheet224.
4.
36.
Settingworksheetzoomlevel225.
Performingformulacalculations235.
1.
UsingthePHPExcelcalculationengine235.
2.
Knownlimitations245.
2.
1.
Operatorprecedence245.
2.
2.
X^N(Power)245.
2.
3.
Formulasinvolvingnumbersandtext246.
Readingandwritingtofile256.
1.
PHPExcel_IOFactory256.
1.
1.
CreatingPHPExcel_Reader_IReaderusingPHPExcel_IOFactory256.
1.
2.
CreatingPHPExcel_Writer_IWriterusingPHPExcel_IOFactory256.
2.
Excel2007(SpreadsheetML)fileformat256.
2.
1.
PHPExcel_Reader_Excel2007256.
2.
2.
PHPExcel_Writer_Excel2007266.
3.
Serializedfileformat276.
3.
1.
PHPExcel_Reader_Serialized276.
3.
2.
PHPExcel_Writer_Serialized276.
4.
Excel5(BIFF)fileformat276.
4.
1.
PHPExcel_Reader_Excel5286.
4.
2.
PHPExcel_Writer_Excel5286.
5.
CSV(CommaSeparatedValues)296.
5.
1.
PHPExcel_Reader_CSV296.
5.
2.
PHPExcel_Writer_CSV296.
6.
HTML306.
6.
1.
PHPExcel_Writer_HTML306.
7.
PDF326.
7.
1.
PHPExcel_Writer_PDF327.
Credits33AppendixA:ValidarraykeysforstyleapplyFromArray()34PrerequisitesSoftwarerequirementsThefollowingsoftwareisrequiredtodevelopusingPHPExcel:PHPversion5.
2ornewerPHPextensionphp_zipenabledInstallationinstructionsInstallationisquiteeasy:copythecontentsoftheClassesfoldertoanylocationinyourapplicationrequired.
Afterwards,makesureyoucanincludeallPHPExcelfiles.
Thiscanbeachievedbyrespectingabasefolderstructure,orbysettingthePHPincludepath,forexample:set_include_path(get_include_path().
PATH_SEPARATOR.
'/path/to/PHPExcel/');UsefullinksandtoolsTherearesomelinksandtoolswhichareveryusefulwhendevelopingusingPHPExcel.
PleaserefertothePHPExcelCodePlexpagesforanupdateversionofthelistbelow.
OpenXML/SpreadsheetMLFileformatdocumentationhttp://www.
ecma-international.
org/news/TC45_current_work/TC45_available_docs.
htmOpenXMLExplainede-bookhttp://openxmldeveloper.
org/articles/1970.
aspxMicrosoftOfficeCompatibilityPackforWord,Excel,andPowerPoint2007FileFormatshttp://www.
microsoft.
com/downloads/details.
aspxfamilyid=941b3470-3ae9-4aee-8f43-c6bb74cd1466&displaylang=enOpenXMLPackageExplorerhttp://www.
codeplex.
com/PackageExplorer/FrequentlyaskedquestionsTheup-to-dateF.
A.
Q.
pageforPHPExcelcanbefoundonhttp://www.
codeplex.
com/PHPExcel/Wiki/View.
aspxtitle=FAQ&referringTitle=Requirements.
Thereseemstobeaproblemwithcharacterencoding.
.
.
ItisnecessarytouseUTF-8encodingforalltextsinPHPExcel.
IfthescriptusesdifferentencodingthenitispossibletoconvertthetextswithPHP'siconv()function.
PHPcomplainsaboutZipArchivenotbeingfoundMakesureyoumeetallRequirements,especiallyphp_zipextensionshouldbeenabled.
Excel2007cannotopenthefilegeneratedbyPHPExcel_Writer_2007onWindows"Excelfoundunreadablecontentin'*.
xlsx'.
DoyouwanttorecoverthecontentsofthisworkbookIfyoutrustthesourceofthisworkbook,clickYes.
"Someversionsofthephp_zipextensiononWindowscontainanerrorwhencreatingZIPfiles.
Theversionthatcanbefoundonhttp://snaps.
php.
net/win32/php5.
2-win32-latest.
zipshouldworkatalltimes.
ProtectiononmyworksheetisnotworkingWhenyoumakeuseofanyoftheworksheetprotectionfeatures(e.
g.
cellrangeprotection,prohibitingdeletingrows,.
.
.
),makesureyouenableworksheetsecurity.
Thiscanforexamplebedonelikethis:$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);FeatureXisnotworkingwithPHPExcel_Writer_YNotallfeaturesofPHPExcelareimplementedintheReader/Writerclasses.
Thisismostlyduetounderlyinglibrariesnotsupportingaspecificfeatureornothavingimplementedaspecificfeature.
ForexampleautofilterisnotimplementedinPEARSpreadsheet_Excel_writer,whichisthebaseofourExcel5writer.
Formulasdon'tseemtobecalculatedinExcel2003usingcompatibilitypackThisisnormalbehaviourofthecompatibilitypack,Excel2007displaysthiscorrectly.
UsePHPExcel_Writer_Excel5ifyoureallyneedcalculatedvalues,orforcerecalculationinExcel2003.
Settingcolumnwidthisnot100%accurateTryingtosetcolumnwidth,Iexperienceoneproblem.
WhenIopenthefileinExcel,theactualwidthis0.
71pointslessthenitshouldbe.
LookslikeusingCalibriasthedefaultfontleadstothisproblem:http://support.
microsoft.
com/kb/214394Thiscanbefixed(whenneeded)byusinganotherdefaultfont:$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setName('ArialCyr');TutorialsFrenchPHPExceltutorialhttp://g-ernaelsten.
developpez.
com/tutoriels/excel2007/ArchitectureSchematicalSpreadsheetinmemoryPHPExcel'sarchitectureisbuiltinawaythatitcanserveasanin-memoryspreadsheet.
Thismeansthat,ifonewouldwanttocreateawebbasedviewofaspreadsheetwhichcommunicateswithPHPExcel'sobjectmodel,hewouldonlyhavetowritethefront-endcode.
Justlikedesktopspreadsheetsoftware,PHPExcelrepresentsaspreadsheetcontainingoneormoreworksheets,whichcontaincellswithdata,formulas,images,…ReadersandwritersOnitsown,PHPExceldoesnotprovidethefunctionalitytoreadfromorwritetoapersistedspreadsheet(ondiskorinadatabase).
Toprovidethatfunctionality,readersandwriterscanbeused.
Bydefault,thePHPExcelpackageprovidessomereadersandwriters,includingonefortheOpenXMLspreadsheetformat(a.
k.
a.
Excel2007fileformat).
Youarenotlimitedtothedefaultreadersandwriters,asyouarefreetoimplementthePHPExcel_Writer_IReaderandPHPExcel_Writer_IWriterinterfaceinacustomclass.
CreatingaspreadsheetThePHPExcelclassThePHPExcelclassisthecoreofPHPExcel.
Itcontainsreferencestothecontainedworksheets,documentsecuritysettingsanddocumentmetadata.
TosimplifythePHPExcelconcept:thePHPExcelclassrepresentsyourworkbook.
WorksheetsAworksheetisacollectionofcells,formula's,images,graphs,…Itholdsalldatayouwanttorepresentasaspreadsheetworksheet.
AccessingcellsAccessingcellsinaPHPExcelworksheetshouldbeprettystraightforward.
Thistopiclistssomeoftheoptionstoaccessacell.
SettingacellvaluebycoordinateSettingacellvaluebycoordinatecanbedoneusingtheworksheet'ssetCellValuemethod.
$objPHPExcel->getActiveSheet()->setCellValue('B8','Somevalue');RetrievingacellbycoordinateToretrievethevalueofacell,thecellshouldfirstberetrievedfromtheworksheetusingthegetCellmethod.
Acell'svaluecanbereadagainusingthefollowinglineofcode:$objPHPExcel->getActiveSheet()->getCell('B8')->getValue();Ifyouneedthecalculatedvalueofacell,usethefollowingcode.
Thisisfurtherexplainedin5Performingformulacalculations.
$objPHPExcel->getActiveSheet()->getCell('B8')->getCalculatedValue();SettingacellvaluebycolumnandrowSettingacellvaluebycoordinatecanbedoneusingtheworksheet'ssetCellValueByColumnAndRowmethod.
//SetcellB8$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1,8,'Somevalue');RetrievingacellbycolumnandrowToretrievethevalueofacell,thecellshouldfirstberetrievedfromtheworksheetusingthegetCellByColumnAndRowmethod.
Acell'svaluecanbereadagainusingthefollowinglineofcode://GetcellB8$objPHPExcel->getActiveSheet()->getCellByColumnAndRow(1,8)->getValue();Ifyouneedthecalculatedvalueofacell,usethefollowingcode.
Thisisfurtherexplainedin5Performingformulacalculations.
//GetcellB8$objPHPExcel->getActiveSheet()->getCellByColumnAndRow(1,8)->getCalculatedValue();LoopingcellsLoopingcellsusingiteratorsTheeasiestwaytoloopcellsisbyusingiterators.
Usingiterators,onecanuseforeachtoloopworksheets,rowsandcells.
Belowisanexamplewherewereadallthevaluesinaworksheetanddisplaytheminatable.
setReadDataOnly(true);$objPHPExcel=$objReader->load("test.
xlsx");$objWorksheet=$objPHPExcel->getActiveSheet();echo''.
"\n";foreach($objWorksheet->getRowIterator()as$row){echo''.
"\n";$cellIterator=$row->getCellIterator();$cellIterator->setIterateOnlyExistingCells(false);//Thisloopsallcells,//evenifitisnotset.
//Bydefault,onlycells//thataresetwillbe//iterated.
foreach($cellIteratoras$cell){echo''.
$cell->getValue().
''.
"\n";}echo''.
"\n";}echo''.
"\n";>Notethatwehavesetthecelliterator'ssetIterateOnlyExistingCells()tofalse.
Thismakestheiteratorloopallcells,eveniftheywerenotsetbefore.
Thecelliteratorwillreturnnullasthecellifitisnotsetintheworksheet.
Settingthecelliterator'ssetIterateOnlyExistingCells()tofalsewillloopallcellsintheworksheetthatcanbeavailableatthatmoment.
Thiswillcreatenewcellsifrequiredandincreasememoryusage!
Onlyuseitifitisintendedtoloopallcellsthatarepossiblyavailable.
LoopingcellsusingindexesOnecanusethepossibilitytoaccesscellvaluesbycolumnandrowindexlike(0,1)insteadof'A1'forreadingandwritingcellvaluesinloops.
Note:InPHPExcelcolumnindexis0-basedwhilerowindexis1-based.
Thatmeans'A1'~(0,1)Belowisanexamplewherewereadallthevaluesinaworksheetanddisplaytheminatable.
setReadDataOnly(true);$objPHPExcel=$objReader->load("test.
xlsx");$objWorksheet=$objPHPExcel->getActiveSheet();$highestRow=$objWorksheet->getHighestRow();//e.
g.
10$highestColumn=$objWorksheet->getHighestColumn();//e.
g'F'$highestColumnIndex=PHPExcel_Cell::columnIndexFromString($highestColumn);//e.
g.
5echo''.
"\n";for($row=1;$row'.
"\n";for($col=0;$col'.
$objWorksheet->getCellByColumnAndRow($col,$row)->getValue().
''.
"\n";}echo''.
"\n";}echo''.
"\n";>UsingvaluebinderstofacilitatedataentryInternally,PHPExcelusesadefaultPHPExcel_Cell_IValueBinderimplementation(PHPExcel_Cell_DefaultValueBinder)todeterminedatatypesofentereddatausingacell'ssetValue()method.
Optionally,thedefaultbehaviourofPHPExcelcanbemodified,allowingeasierdataentry.
Forexample,aPHPExcel_Cell_AdvancedValueBinderclassispresent.
Itautomaticallyconvertspercentagesanddatesenteredasstringstothecorrectformat,alsosettingthecell'sstyleinformation.
ThefollowingexampledemonstrateshowtosetthevaluebinderinPHPExcel:/**PHPExcel*/require_once'PHPExcel.
php';/**PHPExcel_Cell_AdvancedValueBinder*/require_once'PHPExcel/Cell/AdvancedValueBinder.
php';/**PHPExcel_IOFactory*/require_once'PHPExcel/IOFactory.
php';//SetvaluebinderPHPExcel_Cell::setValueBinder(newPHPExcel_Cell_AdvancedValueBinder());//CreatenewPHPExcelobject$objPHPExcel=newPHPExcel();//.
.
.
//Addsomedata,resemblingsomedifferentdatatypes$objPHPExcel->getActiveSheet()->setCellValue('A4','Percentagevalue:');$objPHPExcel->getActiveSheet()->setCellValue('B4','10%');//Convertsto0.
1andsetspercentagecellstyle$objPHPExcel->getActiveSheet()->setCellValue('A5','Date/timevalue:');$objPHPExcel->getActiveSheet()->setCellValue('B5','21December1983');//ConvertstodateandsetsdateformatcellstyleCreatingyourownvaluebinderiseasyWhenadvancedvaluebindingisrequred,youcanimplementthePHPExcel_Cell_IValueBinderinterfaceorextendthePHPExcel_Cell_DefaultValueBinderorPHPExcel_Cell_AdvancedValueBinderclasses.
PHPExcelrecipesThefollowingpagesofferyousomewidely-usedPHPExcelrecipes.
PleasenotethatthesedoNOToffercompletedocumentationonspecificPHPExcelAPIfunctions,butjustabumptogetyoustarted.
IfyouneedspecificAPIfunctions,pleaserefertotheAPIdocumentation.
Forexample,4.
3.
7Settingaworksheet'spageorientationandsizecoverssettingapageorientationtoA4.
Otherpaperformats,likeUSLetter,arenotcoveredinthisdocument,butinthePHPExcelAPIdocumentation.
Settingaspreadsheet'smetadataPHPExcelallowsaneasywaytosetaspreadsheet'smetadata,usingdocumentpropertyaccessors.
Spreadsheetmetadatacanbeusefulforfindingaspecificdocumentinafilerepositoryoradocumentmanagementsystem.
ForexampleMicrosoftSharepointusesdocumentmetadatatosearchforaspecificdocumentinitsdocumentlists.
Settingspreadsheetmetadataisdoneasfollows:$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");Settingaspreadsheet'sactivesheetThefollowinglineofcodesetstheactivesheetindextothefirstsheet:$objPHPExcel->setActiveSheetIndex(0);WriteadateintoacellInExcel,datesarestoredasnumericvaluescountingthenumberofdayselapsedsince1900-01-01.
Forexample,thedate'2008-12-31'isrepresentedas39813.
YoucanverifythisinMicrosoftOfficeExcelbyenteringthatdateinacellandafterwardschangingthenumberformatto'General'sothetruenumericvalueisrevealed.
Writingadatevalueinacellconsistsof2linesofcode.
Selectthemethodthatsuitsyouthebest.
Herearesomeexamples://MySQL-liketimestamp'2008-12-31'PHPExcel_Cell::setValueBinder(newPHPExcel_Cell_AdvancedValueBinder());$objPHPExcel->getActiveSheet()->setCellValue('D1','2008-12-31')$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH)//PHP-time(Unixtime)$time=gmmktime(0,0,0,12,31,2008);//int(1230681600)$objPHPExcel->getActiveSheet()->setCellValue('D1',PHPExcel_Shared_Date::PHPToExcel($time))$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH)//Excel-time$objPHPExcel->getActiveSheet()->setCellValue('D1',39813)$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH)Theabovemethodsforenteringadateallyieldthesameresult.
PHPExcel_Style_NumberFormatprovidesalotofpre-defineddateformats.
Notes:Seesection"Usingvaluebinderstofacilitatedataentry"tolearnmoreabouttheAdvancedValueBinderusedinthefirstexample.
InpreviousversionsofPHPExceluptoandincluding1.
6.
6,whenacellhadadate-likenumberformatcode,itwaspossibletoenteradatedirectlyusinganintegerPHP-timewithoutconvertingtoExceldateformat.
StartingwithPHPExcel1.
6.
7thisisnolongersupported.
Excelcanalsooperateina1904-basedcalendar(defaultforworkbookssavedonMac).
Normally,youdonothavetoworryaboutthiswhenusingPHPExcel.
WriteaformulaintoacellInsidetheExcelfile,formulasarealwaysstoredastheywouldappearinanEnglishversionofMicrosoftOfficeExcel.
ThisisirregardlessofwhichlanguageversionofMicrosoftOfficeExcelyoumayhaveusedtocreatetheExcelfile.
Therefore,whenyouwriteformulaswithPHPExcel,youmustalwaysuseEnglishformulas.
Thefollowingruleshold:Decimalseparatoris'.
'(period)Functionargumentseparatoris','(comma)Matrixrowseparatoris';'(semicolon)AlwaysuseEnglishfunctionnamesWhenthefinalworkbookisopenedbytheuser,MicrosoftOfficeExcelwilltakecareofdisplayingtheformulaaccordingtheapplicationslanguage.
Translationistakencareofbytheapplication!
Thefollowinglineofcodewritestheformula"=MIN(B2:C5)"intothecellB8.
Notethattheformulamuststartwith"="tomakePHPExcelrecognisethisasaformula.
$objPHPExcel->getActiveSheet()->setCellValue('B8','=MIN(B2:C5)');Acell'sformulacanbereadagainusingthefollowinglineofcode:$objPHPExcel->getActiveSheet()->getCell('B8')->getValue();Ifyouneedthecalculatedvalueofacell,usethefollowingcode.
Thisisfurtherexplainedin5Performingformulacalculations.
$objPHPExcel->getActiveSheet()->getCell('B8')->getCalculatedValue();Explicitlysetacell'sdatatypeYoucansetacell'sdatatypeexplicitlybyusingthecell'ssetValueExplicitmethod,orthesetCellValueExplicitmethodofaworksheet.
Here'sanexample:$objPHPExcel->getActiveSheet()->getCell('A1')->setValueExplicit('25',PHPExcel_Cell_DataType::TYPE_NUMERIC);ChangeacellintoaclickableURLYoucanmakeacellaclickableURLbysettingitshyperlinkproperty:$objPHPExcel->getActiveSheet()->setCellValue('E26','www.
phpexcel.
net');$objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setUrl('http://www.
phpexcel.
net');Ifyouwanttomakeahyperlinktoanotherworksheet/cell,usethefollowingcode:$objPHPExcel->getActiveSheet()->setCellValue('E26','www.
phpexcel.
net');$objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setUrl("sheet://'Sheetname'!
A1");Settingaworksheet'spageorientationandsizeSettingaworksheet'spageorientationandsizecanbedoneusingthefollowinglinesofcode:$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);Notethatthereareadditionalpagesettingsavailable.
PleaserefertotheAPIdocumentationforallpossibleoptions.
Centerapagehorizontally/verticallyTocenterapagehorizontally/vertically,youcanusethefollowingcode:$objPHPExcel->getActiveSheet()->getPageSetup()->setHorizontalCentered(true);$objPHPExcel->getActiveSheet()->getPageSetup()->setVerticalCentered(false);SettingtheprintheaderandfooterofaworksheetSettingaworksheet'sprintheaderandfootercanbedoneusingthefollowinglinesofcode:$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&C&HPleasetreatthisdocumentasconfidential!
');$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B'.
$objPHPExcel->getProperties()->getTitle().
'&RPage&Pof&N');Substitutionandformattingcodes(startingwith&)canbeusedinsideheadersandfooters.
Thereisnorequiredorderinwhichthesecodesmustappear.
ThefirstoccurrenceofthefollowingcodesturnstheformattingON,thesecondoccurrenceturnsitOFFagain:StrikethroughSuperscriptSubscriptSuperscriptandsubscriptcannotbothbeONatsametime.
Whichevercomesfirstwinsandtheotherisignored,whilethefirstisON.
ThefollowingcodesaresupportedbyExcel2007:&LCodefor"leftsection"(therearethreeheader/footerlocations,"left","center",and"right").
Whentwoormoreoccurrencesofthissectionmarkerexist,thecontentsfromallmarkersareconcatenated,intheorderofappearance,andplacedintotheleftsection.
&PCodefor"currentpage#"&NCodefor"totalpages"&fontsizeCodefor"textfontsize",wherefontsizeisafontsizeinpoints.
&KCodefor"textfontcolor"RGBColorisspecifiedasRRGGBBThemeColorisspecifedasTTSNNwhereTTisthethemecolorId,Siseither"+"or"-"ofthetint/shadevalue,NNisthetint/shadevalue.
&SCodefor"textstrikethrough"on/off&XCodefor"textsuperscript"on/off&YCodefor"textsubscript"on/off&CCodefor"centersection".
Whentwoormoreoccurrencesofthissectionmarkerexist,thecontentsfromallmarkersareconcatenated,intheorderofappearance,andplacedintothecentersection.
&DCodefor"date"&TCodefor"time"&GCodefor"pictureasbackground"Pleasemakesuretoaddtheimagetotheheader/footer:$objDrawing=newPHPExcel_Worksheet_HeaderFooterDrawing();$objDrawing->setName('PHPExcellogo');$objDrawing->setPath('.
/images/phpexcel_logo.
gif');$objDrawing->setHeight(36);$objPHPExcel->getActiveSheet()->getHeaderFooter()->addImage($objDrawing,PHPExcel_Worksheet_HeaderFooter::IMAGE_HEADER_LEFT);&UCodefor"textsingleunderline"&ECodefor"doubleunderline"&RCodefor"rightsection".
Whentwoormoreoccurrencesofthissectionmarkerexist,thecontentsfromallmarkersareconcatenated,intheorderofappearance,andplacedintotherightsection.
&ZCodefor"thisworkbook'sfilepath"&FCodefor"thisworkbook'sfilename"&ACodefor"sheettabname"&+Codeforaddtopage#&-Codeforsubtractfrompage#&"fontname,fonttype"Codefor"textfontname"and"textfonttype",wherefontnameandfonttypearestringsspecifyingthenameandtypeofthefont,separatedbyacomma.
Whenahyphenappearsinfontname,itmeans"nonespecified".
Bothoffontnameandfonttypecanbelocalizedvalues.
&"-,Bold"Codefor"boldfontstyle"&BCodefor"boldfontstyle"&"-,Regular"Codefor"regularfontstyle"&"-,Italic"Codefor"italicfontstyle"&ICodefor"italicfontstyle"&"-,BoldItalic"Codefor"bolditalicfontstyle"&OCodefor"outlinestyle"&HCodefor"shadowstyle"SettingprintingbreaksonaroworcolumnTosetaprintbreak,usethefollowingcode,whichsetsarowbreakonrow10.
$objPHPExcel->getActiveSheet()->setBreak('A10',PHPExcel_Worksheet::BREAK_ROW);ThefollowinglineofcodesetsaprintbreakoncolumnD:$objPHPExcel->getActiveSheet()->setBreak('D10',PHPExcel_Worksheet::BREAK_COLUMN);Show/hidegridlineswhenprintingToshow/hidegridlineswhenprinting,usethefollowingcode:$objPHPExcel->getActiveSheet()->setShowGridlines(true);Settingrows/columnstorepeatattop/leftPHPExcelcanrepeatspecificrows/cellsattop/leftofapage.
Thefollowingcodeisanexampleofhowtorepeatrow1to5oneachprintedpageofaspecificworksheet:$objPHPExcel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1,5);SpecifyprintingareaTospecifyaworksheet'sprintignarea,usethefollowingcode:$objPHPExcel->getActiveSheet()->getPageSetup()->setPrintArea('A1:E5');FormattingacellAcellcanbeformattedwithfont,border,fill,…styleinformation.
Forexample,onecansettheforegroundcolourofacelltored,alignedtotheright,andthebordertoblackandthickborderstyle.
Let'sdothatoncellB2:$objPHPExcel->getActiveSheet()->getStyle('B2')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);$objPHPExcel->getActiveSheet()->getStyle('B2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);$objPHPExcel->getActiveSheet()->getStyle('B2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);$objPHPExcel->getActiveSheet()->getStyle('B2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);$objPHPExcel->getActiveSheet()->getStyle('B2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);$objPHPExcel->getActiveSheet()->getStyle('B2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);$objPHPExcel->getActiveSheet()->getStyle('B2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);$objPHPExcel->getActiveSheet()->getStyle('B2')->getFill()->getStartColor()->setARGB('FF000000');Thereisalsoanalternativemannertosetstylesonacell.
Thefollowingcodesetsacell'sstyletofontbold,alignmentright,topborderthinandagradientfill:$objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray(array('font'=>array('bold'true),'alignment'=>array('horizontal'=>PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,),'borders'=>array('top'array('style'=>PHPExcel_Style_Border::BORDER_THIN)),'fill'=>array('type'PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,'rotation'=>90,'startcolor'=>array('argb'=>'FFA0A0A0'),'endcolor'=>array('argb'=>'FFFFFFFF'))));Ifyouwanttocopythestyletoothercells,youcanduplicatetheentirestyleobject:$objPHPExcel->getActiveSheet()->duplicateStyle($objPHPExcel->getActiveSheet()->getStyle('B2'),'B3:B7');Youcanalsoduplicateastylearrayintoarangeofcells.
Pleasenotethatthismethoddoesnotsupportconditionalformatting.
Thepreviouslycreatedexamplecanbeappliedtoacellrange(A3:E3)usingthefollowingcode:$objPHPExcel->getActiveSheet()->duplicateStyleArray(array('font'=>array('bold'true),'alignment'=>array('horizontal'=>PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,),'borders'=>array('top'array('style'=>PHPExcel_Style_Border::BORDER_THIN)),'fill'=>array('type'PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,'rotation'=>90,'startcolor'=>array('argb'=>'FFA0A0A0'),'endcolor'=>array('argb'=>'FFFFFFFF'))),'A3:E3');SettingthedefaultstyleofaworksheetItispossbiletosetthedefaultstyleofaworksheetwithfont,border,fill,…styleinformation.
Let'ssetthedefaultfonttoArialsize8:$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setName('Arial');$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(8);ConditionalformattingacellAcellcanbeformattedconditionally,basedonaspecificrule.
Forexample,onecansettheforegroundcolourofacelltoredifitsvalueisbelowzero,andtogreenifitsvalueiszeroormore.
Onecansetaconditionalstylerulesettoacellusingthefollowingcode:$objConditional1=newPHPExcel_Style_Conditional();$objConditional1->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS);$objConditional1->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_LESSTHAN);$objConditional1->addCondition('0');$objConditional1->getStyle()->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);$objConditional1->getStyle()->getFont()->setBold(true);$objConditional2=newPHPExcel_Style_Conditional();$objConditional2->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS);$objConditional2->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_GREATERTHANOREQUAL);$objConditional2->addCondition('0');$objConditional2->getStyle()->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_GREEN);$objConditional2->getStyle()->getFont()->setBold(true);$conditionalStyles=$objPHPExcel->getActiveSheet()->getStyle('B2')->getConditionalStyles();array_push($conditionalStyles,$objConditional1);array_push($conditionalStyles,$objConditional2);$objPHPExcel->getActiveSheet()->getStyle('B2')->setConditionalStyles($conditionalStyles);Ifyouwanttocopytherulesettoothercells,youcanduplicatethestyleobject:$objPHPExcel->getActiveSheet()->duplicateStyle($objPHPExcel->getActiveSheet()->getStyle('B2'),'B3:B7');AddacommenttoacellToaddacommenttoacell,usethefollowingcode.
TheexamplebelowaddsacommenttocellE11:$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.
');ApplyautofiltertoarangeofcellsToapplyanautofiltertoarangeofcells,usethefollowingcode:$objPHPExcel->getActiveSheet()->setAutoFilter('A1:C9');Makesurethatyoualwaysincludethecompletefilterrange!
Exceldoessupportsettingonlythecaptionrow,butthat'snotabestpractise.
.
.
SettingsecurityonaspreadsheetExceloffers3levelsof"protection":documentsecurity,sheetsecurityandcellsecurity.
Documentsecurityallowsyoutosetapasswordonacompletespreadsheet,allowingchangestobemadeonlywhenthatpasswordisentered.
Worksheetsecurityoffersothersecurityoptions:youcandisallowinsertingrowsonaspecificsheet,disallowsorting,…Cellsecurityofferstheoptiontolock/unlockacellaswellasshow/hidetheinternalformulaAnexampleonsettingdocumentsecurity:$objPHPExcel->getSecurity()->setLockWindows(true);$objPHPExcel->getSecurity()->setLockStructure(true);$objPHPExcel->getSecurity()->setWorkbookPassword("PHPExcel");Anexampleonsettingworksheetsecurity:$objPHPExcel->getActiveSheet()->getProtection()->setPassword('PHPExcel');$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);$objPHPExcel->getActiveSheet()->getProtection()->setSort(true);$objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);$objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);Anexampleonsettingcellsecurity:$objPHPExcel->getActiveSheet()->getStyle('B1')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);Makesureyouenableworksheetprotectionifyouneedanyoftheworksheetprotectionfeatures!
Thiscanbedoneusingthefollowingcode:$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);SettingdatavalidationonacellDatavalidationisapowerfulfeatureofExcel2007.
Itallowstospecifyaninputfilteronthedatathatcanbeinsertedinaspecificcell.
Thisfiltercanbearange(i.
e.
valuemustbebetween0and10),alist(i.
e.
valuemustbepickedfromalist),…Thefollowingpieceofcodeonlyallowsnumbersbetween10and20tobeenteredincellB3:$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);ThefollowingpieceofcodeonlyallowsanitempickedfromalistofdatatobeenteredincellB3:$objValidation=$objPHPExcel->getActiveSheet()->getCell('B5')->getDataValidation();$objValidation->setType(PHPExcel_Cell_DataValidation::TYPE_LIST);$objValidation->setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION);$objValidation->setAllowBlank(false);$objValidation->setShowInputMessage(true);$objValidation->setShowErrorMessage(true);$objValidation->setShowDropDown(true);$objValidation->setErrorTitle('Inputerror');$objValidation->setError('Valueisnotinlist.
');$objValidation->setPromptTitle('Pickfromlist');$objValidation->setPrompt('Pleasepickavaluefromthedrop-downlist.
');$objValidation->setFormula1('"ItemA,ItemB,ItemC"');$objPHPExcel->getActiveSheet()->getCell('B5')->setDataValidation($objValidation);Whenusingadatavalidationlist,makesureyouputthelistbetween"and"andthatyousplittheitemswithacomma(,).
Ifyouneeddatavalidationonmultiplecells,onecanclonetheruleset:$objPHPExcel->getActiveSheet()->getCell('B8')->setDataValidation(clone$objValidation);Settingacolumn'swidthAcolumn'swidthcanbesetusingthefollowingcode:$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);IfyouwantPHPExceltoperformanautomaticwidthcalculation,usethefollowingcode.
PHPExcelwillapproximatethecolumnwithtothewidthofthewidestcolumnvalue.
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);Show/hideacolumnTosetaworksheet'scolumnvisibility,youcanusethefollowingcode.
ThefirstlineexplicitlyshowsthecolumnC,thesecondlinehidescolumnD.
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setVisible(true);$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setVisible(false);Group/outlineacolumnTogroup/outlineacolumn,youcanusethefollowingcode:$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setOutlineLevel(1);Youcanalsocollapsethecolumn.
Notethatyoushouldalsosetthecolumninvisible,otherwisethecollapsewillnotbevisibleinExcel2007.
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setCollapsed(true);$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setVisible(false);Pleaserefertothepart"group/outlinearow"foracompleteexampleoncollapsing.
YoucaninstructPHPExceltoaddasummarytotheright(default),ortotheleft.
Thefollowingcodeaddsthesummarytotheleft:$objPHPExcel->getActiveSheet()->setShowSummaryRight(false);Settingarow'sheightArow'sheightcanbesetusingthefollowingcode:$objPHPExcel->getActiveSheet()->getRowDimension('10')->setRowHeight(100);Show/hidearowTosetaworksheet'srowvisibility,youcanusethefollowingcode.
Thefollowingexamplehidesrownumber10.
$objPHPExcel->getActiveSheet()->getRowDimension('10')->setVisible(false);Group/outlinearowTogroup/outlinearow,youcanusethefollowingcode:$objPHPExcel->getActiveSheet()->getRowDimension('5')->setOutlineLevel(1);Youcanalsocollapsetherow.
Notethatyoushouldalsosettherowinvisible,otherwisethecollapsewillnotbevisibleinExcel2007.
$objPHPExcel->getActiveSheet()->getRowDimension('5')->setCollapsed(true);$objPHPExcel->getActiveSheet()->getRowDimension('5')->setVisible(false);Here'sanexamplewhichcollapsesrows50to80:for($i=51;$igetActiveSheet()->setCellValue('A'.
$i,"FName$i");$objPHPExcel->getActiveSheet()->setCellValue('B'.
$i,"LName$i");$objPHPExcel->getActiveSheet()->setCellValue('C'.
$i,"PhoneNo$i");$objPHPExcel->getActiveSheet()->setCellValue('D'.
$i,"FaxNo$i");$objPHPExcel->getActiveSheet()->setCellValue('E'.
$i,true);$objPHPExcel->getActiveSheet()->getRowDimension($i)->setOutlineLevel(1);$objPHPExcel->getActiveSheet()->getRowDimension($i)->setVisible(false);}$objPHPExcel->getActiveSheet()->getRowDimension(81)->setCollapsed(true);YoucaninstructPHPExceltoaddasummarybelowthecollapsiblerows(default),orabove.
Thefollowingcodeaddsthesummaryabove:$objPHPExcel->getActiveSheet()->setShowSummaryBelow(false);Merge/unmergecellsIfyouhaveabigpieceofdatayouwanttodisplayinaworksheet,youcanmergetwoormorecellstogether,tobecomeonecell.
Thiscanbedoneusingthefollowingcode:$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');RemovingamergecanbedoneusingtheunmergeCellsmethod:$objPHPExcel->getActiveSheet()->unmergeCells('A18:E22');Insertingrows/columnsYoucaninsert/removerows/columnsataspecificposition.
Thefollowingcodeinserts2newrows,rightbeforerow7:$objPHPExcel->getActiveSheet()->insertNewRowBefore(7,2);AddadrawingtoaworksheetAdrawingisalwaysrepresentedasaseparateobject,whichcanbeaddedtoaworksheet.
Therefore,youmustfirstinstantiateanewPHPExcel_Worksheet_Drawing,andassignitspropertiesameaningfulvalue:$objDrawing=newPHPExcel_Worksheet_Drawing();$objDrawing->setName('Logo');$objDrawing->setDescription('Logo');$objDrawing->setPath('.
/images/officelogo.
jpg');$objDrawing->setHeight(36);Toaddtheabovedrawingtotheworksheet,usethefollowingsnippetofcode.
PHPExcelcreatesthelinkbetweenthedrawingandtheworksheet:$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());Youcansetnumerouspropertiesonadrawing,herearesomeexamples:$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);AddrichtexttoacellAddingrichtexttoacellcanbedoneusingPHPExcel_RichTextinstances.
Here'sanexample,whichcreatesthefollowingrichtextstring:Thisinvoiceispayablewithinthirtydaysaftertheendofthemonthunlessspecifiedotherwiseontheinvoice.
$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.
');DefineanamedrangePHPExcelsupportsthedefinitionofnamedranges.
Thesecanbedefinedusingthefollowingcode://Addsomedata$objPHPExcel->setActiveSheetIndex(0);$objPHPExcel->getActiveSheet()->setCellValue('A1','Firstname:');$objPHPExcel->getActiveSheet()->setCellValue('A2','Lastname:');$objPHPExcel->getActiveSheet()->setCellValue('B1','Maarten');$objPHPExcel->getActiveSheet()->setCellValue('B2','Balliauw');//Definenamedranges$objPHPExcel->addNamedRange(newPHPExcel_NamedRange('PersonFN',$objPHPExcel->getActiveSheet(),'B1'));$objPHPExcel->addNamedRange(newPHPExcel_NamedRange('PersonLN',$objPHPExcel->getActiveSheet(),'B2'));Optionally,afourthparametercanbepasseddefiningthenamedrangelocal(i.
e.
onlyusableonthecurrentworksheet).
Namedrangesareglobalbydefault.
Redirectoutputtoaclient'swebbrowserSometimes,onereallywantstooutputafiletoaclient'sbrowser,especiallywhencreatingspreadsheetson-the-fly.
Therearesomeeasystepsthatcanbefollowedtodothis:CreateyourPHPExcelspreadsheetOutputHTTPheadersforthetypeofdocumentyouwishtooutputUsethePHPExcel_Writer_*ofyourchoice,andsaveto"php://output"PHPExcel_Writer_Excel2007usestemporarystoragewhenwritingtophp://output.
Bydefault,temporaryfilesarestoredinthescript'sworkingdirectory.
Whenthereisnoaccess,itfallsbacktotheoperatingsystem'stemporaryfileslocation.
Thismaynotbesafeforunauthorizedviewing!
Dependingontheconfigurationofyouroperatingsystem,temporarystoragecanbereadbyanyoneusingthesametemporarystoragefolder.
Whenconfidentialityofyourdocumentisneeded,itisrecommendednottousephp://output.
HTTPheadersExampleofascriptredirectinganExcel2007filetotheclient'sbrowser:save('php://output');>ExampleofascriptredirectinganExcel5filetotheclient'sbrowser:save('php://output');>Caution:MakesurenottoincludeanyechostatementsoroutputanyothercontentsthantheExcelfile.
Thereshouldbenowhitespacebeforetheopeningtag(whichcanalsobeomittedtoavoidproblems).
MakesurethatyourscriptissavedwithoutaBOM(Byte-ordermark).
(Becausethiscountsasechoingoutput)SamethingsapplytoallincludedfilesFailingtofollowtheaboveguidelinesmayresultincorruptExcelfilesarrivingattheclientbrowser,and/orthatheaderscannotbesetbyPHP(resultinginwarningmessages).
SettingthedefaultcolumnwidthDefaultcolumnwidthcanbesetusingthefollowingcode:$objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(12);SettingthedefaultrowheightDefaultrowheightcanbesetusingthefollowingcode:$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);AddaGDdrawingtoaworksheetTheremightbeasituationwhereyouwanttogenerateanin-memoryimageusingGDandaddittoaPHPExcelworksheetwithoutfirsthavingtosavethisfiletoatemporarylocation.
Here'sanexamplewhichgeneratesanimageinmemoryandaddsittotheactiveworksheet://Generateanimage$gdImage=@imagecreatetruecolor(120,20)ordie('CannotInitializenewGDimagestream');$textColor=imagecolorallocate($gdImage,255,255,255);imagestring($gdImage,1,5,5,'CreatedwithPHPExcel',$textColor);//Addadrawingtotheworksheet$objDrawing=newPHPExcel_Worksheet_MemoryDrawing();$objDrawing->setName('Sampleimage');$objDrawing->setDescription('Sampleimage');$objDrawing->setImageResource($gdImage);$objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);$objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);$objDrawing->setHeight(36);$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());SettingworksheetzoomlevelTosetaworksheet'szoomlevel,thefollowingcodecanbeused:$objPHPExcel->getActiveSheet()->getSheetView()->setZoomScale(75);Notethatzoomlevelshouldbeinrange10–400.
PerformingformulacalculationsUsingthePHPExcelcalculationengineAsPHPExcelrepresentsanin-memoryspreadsheet,italsooffersformulacalculationcapabilities.
Acellcanbeofavaluetype(containinganumberortext),oraformulatype(containingaformulawhichcanbeevaluated).
Forexample,theformula"=SUM(A1:A10)"evaluatestothesumofvaluesinA1,A2,.
.
.
,A10.
Tocalculateaformula,youcancallthecellcontainingtheformula'smethodgetCalculatedValue(),forexample:$objPHPExcel->getActiveSheet()->getCell('E11')->getCalculatedValue();IfyouwritethefollowinglineofcodeintheinvoicedemoincludedwithPHPExcel,itevaluatestothevalue"64":AnothernicefeatureofPHPExcel'sformulaparser,isthatitcanautomaticallyadjustaformulawheninserting/removingrows/columns.
Here'sanexample:YouseethattheformulacontainedincellE11is"SUM(E4:E9)".
Now,whenIwritethefollowinglineofcode,twonewproductlinesareadded:$objPHPExcel->getActiveSheet()->insertNewRowBefore(7,2);DidyounoticeTheformulaintheformercellE11(nowE13,asIinserted2newrows),changedto"SUM(E4:E11)".
Also,theinsertedcellsduplicatestyleinformationofthepreviouscell,justlikeExcel'sbehaviour.
Notethatyoucanbothinsertrowsandcolumns.
KnownlimitationsTherearesomeknownlimitationstothePHPExcelcalculationengine.
MostofthemareduetothefactthatanExcelformulaisconvertedintoPHPcodebeforebeingexecuted.
ThismeansthatExcelformulacalculationissubjecttoPHP'slanguagecharacteristics.
OperatorprecedenceInExcel'+'winsover'&',justlike'*'winsover'+'inordinaryalgebra.
TheformerruleisnotwhatonefindsusingthecalculationengineshippedwithPHPExcel.
ReferenceforoperatorprecedenceinExcel:http://support.
microsoft.
com/kb/25189ReferenceforoperatorprecedenceinPHP:http://www.
php.
net/operatorsX^N(Power)Powersinaformulaenteredlike'2^3'don'twork.
UsingthePOWER()functiondoeswork:'=POWER(2;3)'.
ThisisduetothefactthatPHPtreats^asbitwiseXORinsteadofpower.
ReferenceforformulasinExcel:http://office.
microsoft.
com/en-us/excel/HP030561391033.
aspxReferenceforoperatorsinPHP:http://www.
php.
net/operatorsFormulasinvolvingnumbersandtextFormulasinvolvingnumbersandtextmayproduceunexpectedresultsorevenunreadablefilecontents.
Forexample,theformula'=3+"Hello"'isexpectedtoproduceanerrorinExcel(#VALUE!
).
DuetothefactthatPHPconverts"Hello"toanumericvalue(zero),theresultofthisformulaisevaluatedas3insteadofevaluatingasanerror.
ThisalsocausestheExceldocumentbeinggeneratedascontainingunreadablecontent.
ReferenceforthisbehaviourinPHP:http://be.
php.
net/manual/en/language.
types.
string.
php#language.
types.
string.
conversionReadingandwritingtofileAsyoualreadyknowfrompart3.
3Readersandwriters,readingandwritingtoapersistedstorageisnotpossibleusingthebasePHPExcelclasses.
Forthispurpose,PHPExcelprovidesreadersandwriters,whichareimplementationsofPHPExcel_Writer_IReaderandPHPExcel_Writer_IWriter.
PHPExcel_IOFactoryThePHPExcelAPIoffersmultiplemethodstocreateaPHPExcel_Writer_IReaderorPHPExcel_Writer_IWriterinstance:DirectcreationViaPHPExcel_IOFactoryAllexamplesunderneathdemonstratethedirectcreationmethod.
NotethatyoucanalsousethePHPExcel_IOFactoryclasstodothis.
CreatingPHPExcel_Reader_IReaderusingPHPExcel_IOFactoryThereare2methodsforreadinginafileintoPHPExcel:usingautomaticfiletyperesolvingorexplicitly.
AutomaticfiletyperesolvingchecksthedifferentPHPExcel_Reader_IReaderdistributedwithPHPExcel.
Ifoneofthemcanloadthespecifiedfilename,thefileisloadedusingthatPHPExcel_Reader_IReader.
ExplicitmoderequiresyoutospecifywhichPHPExcel_Reader_IReadershouldbeused.
YoucancreateaPHPExcel_Reader_IReaderinstanceusingPHPExcel_IOFactoryinautomaticfiletyperesolvingmodeusingthefollowingcodesample:$objPHPExcel=PHPExcel_IOFactory->load("05featuredemo.
xlsx");YoucancreateaPHPExcel_Reader_IReaderinstanceusingPHPExcel_IOFactoryinexplicitmodeusingthefollowingcodesample:$objReader=PHPExcel_IOFactory::createReader("Excel2007");$objPHPExcel=$objReader->load("05featuredemo.
xlsx");Notethatautomatictyperesolvingmodeisslightlyslowerthanexplicitmode.
CreatingPHPExcel_Writer_IWriterusingPHPExcel_IOFactoryYoucancreateaPHPExcel_Writer_IwriterinstanceusingPHPExcel_IOFactory:$objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,"Excel2007");$objWriter->save("05featuredemo.
xlsx");Excel2007(SpreadsheetML)fileformatExcel2007fileformatisthemainfileformatofPHPExcel.
Itallowsoutputtingthein-memoryspreadsheettoa.
xlsxfile.
PHPExcel_Reader_Excel2007ReadingaspreadsheetYoucanreada.
xlsxfileusingthefollowingcode:$objReader=newPHPExcel_Reader_Excel2007();$objPHPExcel=$objReader->load("05featuredemo.
xlsx");ReaddataonlyYoucansetantheoptionsetReadDataOnlyonthereader,toinstructthereadertoignorestyling,datavalidation,…andjustreadcelldata:$objReader=newPHPExcel_Reader_Excel2007();$objReader->setReadDataOnly(true);$objPHPExcel=$objReader->load("05featuredemo.
xlsx");ReadspecificsheetsonlyYoucansetantheoptionsetLoadSheetsOnlyonthereader,toinstructthereadertoonlyloadthesheetswithagivenname:$objReader=newPHPExcel_Reader_Excel2007();$objReader->setLoadSheetsOnly(array("Sheet1","Myspecialsheet"));$objPHPExcel=$objReader->load("05featuredemo.
xlsx");ReadspecificcellsonlyYoucansetantheoptionsetReadFilteronthereader,toinstructthereadertoonlyloadthecellswhichmatchagivenrule.
AreadfiltercanbeanyclasswhichimplementsPHPExcel_Reader_IReadFilter.
Bydefault,allcellsarereadusingthePHPExcel_Reader_DefaultReadFilter.
Thefollowingcodewillonlyreadrow1androws20–30ofanysheetintheExcelfile:classMyReadFilterimplementsPHPExcel_Reader_IReadFilter{publicfunctionreadCell($column,$row,$worksheetName=''){//Readtitlerowandrows20-30if($row==1||($row>=20&&$rowsetReadFilter(newMyReadFilter());$objPHPExcel=$objReader->load("06largescale.
xlsx");PHPExcel_Writer_Excel2007WritingaspreadsheetYoucanwritea.
xlsxfileusingthefollowingcode:$objWriter=newPHPExcel_Writer_Excel2007($objPHPExcel);$objWriter->save("05featuredemo.
xlsx");Formulapre-calculationBydefault,thiswriterpre-calculatesallformulasinthespreadsheet.
Thiscanbeslowonlargespreadsheets,andmaybeevenunwanted.
Youcanhoweverdisableformulapre-calculation:$objWriter=newPHPExcel_Writer_Excel2007($objPHPExcel);$objWriter->setPreCalculateFormulas(false);$objWriter->save("05featuredemo.
xlsx");Office2003compatibilitypackBecauseofabugintheOffice2003compatibilitypack,therecanbesomesmallissueswhenopeningExcel2007spreadsheets(mostlyrelatedtoformulacalculation).
YoucanenableOffice2003compatibilitywiththefollowingcode:$objWriter=newPHPExcel_Writer_Excel2007($objPHPExcel);$objWriter->setOffice2003Compatibility(true);$objWriter->save("05featuredemo.
xlsx");Office2003compatibilityshouldonlybeusedwhenneededOffice2003compatibilityoptionshouldonlybeusedwhenneeded.
ThisoptiondisablesseveralOffice2007fileformatoptions,resultinginalower-featuredOffice2007spreadsheetwhenthisoptionisused.
TemporarystorageWhenworkingwithlargespreadsheets,ausefulfeatureisdiskcaching.
Whendiskcachingisenabled,thiswriterusesfilebasedtemporarystorageinsteadofmemorybasedtemporarystoragewhereverpossible.
Bydefault,temporaryfilesarestoredinthescript'sworkingdirectory.
Whenthereisnoaccess,itfallsbacktotheoperatingsystem'stemporaryfileslocation.
Notethatdiskcachingdoesnotspeedupwriting,itonlylowersmemoryusagewhenneeded.
Temporarystoragemaynotbesafeforunauthorizedviewing!
Dependingontheconfigurationofyouroperatingsystem,temporarystoragecanbereadbyanyoneusingthesametemporarystoragefolder.
Whenconfidentialityofyourdocumentisneeded,itisrecommendednottousediskcaching.
Youcanenablediskcachingusingthefollowingcode:$objWriter=newPHPExcel_Writer_Excel2007($objPHPExcel);$objWriter->setUseDiskCaching(true);$objWriter->save("05featuredemo.
xlsx");Optionally,thelocationofthediskcachefoldercanbespecifiedusingthefollowingcode:$objWriter=newPHPExcel_Writer_Excel2007($objPHPExcel);$objWriter->setUseDiskCaching(true,"/home/phpexcel/diskcache");$objWriter->save("05featuredemo.
xlsx");SerializedfileformatSerializedfileformatisamannerofstoringaPHPExcelspreadsheettodisk,creatingafilecontainingaserializedPHPExcelinstance.
Itoffersafastandeasywaytostoreandreadaspreadsheet.
Serializedfileformatshouldnotbeusedasapersistentstoragemethod!
SinceserializedfilesmaynotbecompatibletroughdifferentPHPExcelversions,itisnotagoodideatousethisasapersistentstoragemethod.
Thinkoftheserializedfileformatasatemporarystorage,forexamplebetween2batchscriptsthatdependoneachother'soutput.
PHPExcel_Reader_SerializedReadingaspreadsheetYoucanreada.
phpxlfileusingthefollowingcode:$objReader=newPHPExcel_Reader_Serialized();$objPHPExcel=$objReader->load("05featuredemo.
phpxl");PHPExcel_Writer_SerializedWritingaspreadsheetYoucanwritea.
phpxlfileusingthefollowingcode:$objWriter=newPHPExcel_Writer_Serialized($objPHPExcel);$objWriter->save("05featuredemo.
phpxl");Excel5(BIFF)fileformatExcel5fileformatistheoldExcelfileformat,implementedinPHPExceltoprovideauniformmannertocreateboth.
xlsxand.
xlsfiles.
ItisbasicallyamodifiedversionofPEARSpreadsheet_Excel_Writer,andhasthesamelimitationsandfeaturesasthePEARlibrary.
Excel5fileformatwillnotbedevelopedanyfurther,itjustprovidesanadditionalfileformatforPHPExcel.
Excel5(BIFF)limitationsPleasenotethatBIFFfileformathassomelimitsregardingtostylingcellsandhandlinglargespreadsheetsviaPHP.
PHPExcel_Reader_Excel5ReadingaspreadsheetYoucanreada.
xlsfileusingthefollowingcode:$objReader=newPHPExcel_Reader_Excel5();$objPHPExcel=$objReader->load("05featuredemo.
xls");ReaddataonlyYoucansetantheoptionsetReadDataOnlyonthereader,toinstructthereadertoignorestyling,datavalidation,…andjustreadcelldata:$objReader=newPHPExcel_Reader_Excel5();$objReader->setReadDataOnly(true);$objPHPExcel=$objReader->load("05featuredemo.
xls");ReadspecificsheetsonlyYoucansetantheoptionsetLoadSheetsOnlyonthereader,toinstructthereadertoonlyloadthesheetswithagivenname:$objReader=newPHPExcel_Reader_Excel5();$objReader->setLoadSheetsOnly(array("Sheet1","Myspecialsheet"));$objPHPExcel=$objReader->load("05featuredemo.
xls");ReadspecificcellsonlyYoucansetantheoptionsetReadFilteronthereader,toinstructthereadertoonlyloadthecellswhichmatchagivenrule.
AreadfiltercanbeanyclasswhichimplementsPHPExcel_Reader_IReadFilter.
Bydefault,allcellsarereadusingthePHPExcel_Reader_DefaultReadFilter.
Thefollowingcodewillonlyreadrow1androws20–30ofanysheetintheExcelfile:classMyReadFilterimplementsPHPExcel_Reader_IReadFilter{publicfunctionreadCell($column,$row,$worksheetName=''){//Readtitlerowandrows20-30if($row==1||($row>=20&&$rowsetReadFilter(newMyReadFilter());$objPHPExcel=$objReader->load("06largescale.
xls");PHPExcel_Writer_Excel5WritingaspreadsheetYoucanwritea.
xlsfileusingthefollowingcode:$objWriter=newPHPExcel_Writer_Excel5($objPHPExcel);$objWriter->save("05featuredemo.
xls");TemporarystorageInternally,Excel5writerstorestemporaryfilesondisk.
Whenyourserveradministratordisabledfileaccessontheoperatingsystem'stemporarydirectory,youcanoverridethedefaultbyusingthefollowingcode:$objWriter=newPHPExcel_Writer_Excel5($objPHPExcel);$objWriter->setTempDir("C:/temp/");$objWriter->save("05featuredemo.
xls");CSV(CommaSeparatedValues)CSV(CommaSeparatedValues)areoftenusedasanimport/exportfileformatwithothersystems.
PHPExcelallowsreadingandwritingtoCSVfiles.
CSVlimitationsPleasenotethatCSVfileformathassomelimitsregardingtostylingcells,numberformatting,…PHPExcel_Reader_CSVReadingaCSVfileYoucanreada.
csvfileusingthefollowingcode:$objReader=newPHPExcel_Reader_CSV();$objPHPExcel=$objReader->load("05featuredemo.
csv");SettingCSVoptionsOften,CSVfilesarenotreally"commaseparated",orusesemicolon(;)asaseparator.
YoucaninstructPHPExcel_Reader_CSVsomeoptionsbeforereadingaCSVfile:$objReader=newPHPExcel_Reader_CSV();$objReader->setDelimiter(';');$objReader->setEnclosure('');$objReader->setLineEnding("\r\n");$objReader->setSheetIndex(0);$objPHPExcel=$objReader->load("05featuredemo.
csv");ReadaspecificworksheetCSVfilescanonlycontainoneworksheet.
Therefore,youcanspecifywhichsheettoreadfromCSV:$objReader->setSheetIndex(0);ReadintoexistingspreadsheetWhenworkingwithCSVfiles,itmightoccurthatyouwanttoimportCSVdataintoanexistingPHPExcelobject.
ThefollowingcodeloadsaCSVfileintoanexisting$objPHPExcelcontainingsomesheets,andimportsontothe6thsheet:$objReader=newPHPExcel_Reader_CSV();$objReader->setDelimiter(';');$objReader->setEnclosure('');$objReader->setLineEnding("\r\n");$objReader->setSheetIndex(5);$objReader->loadIntoExisting("05featuredemo.
csv",$objPHPExcel);PHPExcel_Writer_CSVWritingaCSVfileYoucanwritea.
csvfileusingthefollowingcode:$objWriter=newPHPExcel_Writer_CSV($objPHPExcel);$objWriter->save("05featuredemo.
csv");SettingCSVoptionsOften,CSVfilesarenotreally"commaseparated",orusesemicolon(;)asaseparator.
YoucaninstructPHPExcel_Writer_CSVsomeoptionsbeforewritingaCSVfile:$objWriter=newPHPExcel_Writer_CSV($objPHPExcel);$objWriter->setDelimiter(';');$objWriter->setEnclosure('');$objWriter->setLineEnding("\r\n");$objWriter->setSheetIndex(0);$objWriter->save("05featuredemo.
csv");WriteaspecificworksheetCSVfilescanonlycontainoneworksheet.
Therefore,youcanspecifywhichsheettowritetoCSV:$objWriter->setSheetIndex(0);Formulapre-calculationBydefault,thiswriterpre-calculatesallformulasinthespreadsheet.
Thiscanbeslowonlargespreadsheets,andmaybeevenunwanted.
Youcanhoweverdisableformulapre-calculation:$objWriter=newPHPExcel_Writer_CSV($objPHPExcel);$objWriter->setPreCalculateFormulas(false);$objWriter->save("05featuredemo.
csv");WritingUTF-8CSVfilesACSVfilecanbemarkedasUTF-8bywritingaBOMfileheader.
Thiscanbeenabledbyusingthefollowingcode:$objWriter=newPHPExcel_Writer_CSV($objPHPExcel);$objWriter->setUseBOM(true);$objWriter->save("05featuredemo.
csv");HTMLPHPExcelallowsyoutowriteaspreadsheetintoHTMLformat,forquickrepresentationofthedatainittoanyonewhodoesnothaveaspreadsheetapplicationontheirPC.
HTMLlimitationsPleasenotethatHTMLfileformathassomelimitsregardingtostylingcells,numberformatting,…PHPExcel_Writer_HTMLPleasenotethatPHPExcel_Writer_HTMLonlyoutputsthefirstworksheetbydefault.
WritingaspreadsheetYoucanwritea.
htmfileusingthefollowingcode:$objWriter=newPHPExcel_Writer_HTML($objPHPExcel);$objWriter->save("05featuredemo.
htm");WriteallworksheetsHTMLfilescancontainoneormoreworksheets.
IfyouwanttowriteallsheetsintoasingleHTMLfile,usethefollowingcode:$objWriter->writeAllSheets();WriteaspecificworksheetHTMLfilescancontainoneormoreworksheets.
Therefore,youcanspecifywhichsheettowritetoHTML:$objWriter->setSheetIndex(0);SettingtheimagesrootoftheHTMLfileTheremightbesituationswhereyouwanttoexplicitlysettheincludedimagesroot.
Forexample,onemightwanttoseeinsteadof.
Youcanusethefollowingcodetoachievethisresult:$objWriter->setImagesRoot('http://www.
example.
com');Formulapre-calculationBydefault,thiswriterpre-calculatesallformulasinthespreadsheet.
Thiscanbeslowonlargespreadsheets,andmaybeevenunwanted.
Youcanhoweverdisableformulapre-calculation:$objWriter=newPHPExcel_Writer_HTML($objPHPExcel);$objWriter->setPreCalculateFormulas(false);$objWriter->save("05featuredemo.
htm");EmbeddinggeneratedHTMLinawebpageTheremightbeasituationwhereyouwanttoembedthegeneratedHTMLinanexistingwebsite.
PHPExcel_Writer_HTMLprovidessupporttogenerateonlyspecificpartsoftheHTMLcode,whichallowsyoutousethesepartsinyourwebsite.
Supportedmethods:generateHTMLHeader()generateStyles()generateSheetData()generateHTMLFooter()Here'sanexamplewhichretrievesallpartsindependentlyandmergesthemintoaresultingHTMLpage:generateHTMLHeader();>generateStyles(false);//donotwriteand>-->generateSheetData();echo$objWriter->generateHTMLFooter();>WritingUTF-8HTMLfilesAHTMLfilecanbemarkedasUTF-8bywritingaBOMfileheader.
Thiscanbeenabledbyusingthefollowingcode:$objWriter=newPHPExcel_Writer_HTML($objPHPExcel);$objWriter->setUseBOM(true);$objWriter->save("05featuredemo.
htm");PDFPHPExcelallowsyoutowriteaspreadsheetintoPDFformat,forfastdistributionofrepresenteddata.
PDFlimitationsPleasenotethatPDFfileformathassomelimitsregardingtostylingcells,numberformatting,…PHPExcel_Writer_PDFPleasenotethatPHPExcel_Writer_PDFonlyoutputsthefirstworksheetbydefault.
WritingaspreadsheetYoucanwritea.
pdffileusingthefollowingcode:$objWriter=newPHPExcel_Writer_PDF($objPHPExcel);$objWriter->save("05featuredemo.
pdf");WriteallworksheetsPDFfilescancontainoneormoreworksheets.
IfyouwanttowriteallsheetsintoasinglePDFfile,usethefollowingcode:$objWriter->writeAllSheets();WriteaspecificworksheetPDFfilescancontainoneormoreworksheets.
Therefore,youcanspecifywhichsheettowritetoPDF:$objWriter->setSheetIndex(0);Formulapre-calculationBydefault,thiswriterpre-calculatesallformulasinthespreadsheet.
Thiscanbeslowonlargespreadsheets,andmaybeevenunwanted.
Youcanhoweverdisableformulapre-calculation:$objWriter=newPHPExcel_Writer_PDF($objPHPExcel);$objWriter->setPreCalculateFormulas(false);$objWriter->save("05featuredemo.
pdf");TemporarystorageInternally,PDFwriterstorestemporaryfilesondisk.
Whenyourserveradministratordisabledfileaccessontheoperatingsystem'stemporarydirectory,youcanoverridethedefaultbyusingthefollowingcode:$objWriter=newPHPExcel_Writer_PDF($objPHPExcel);$objWriter->setTempDir("C:/temp/");$objWriter->save("05featuredemo.
pdf");CreditsPleaserefertotheinternetpagehttp://www.
codeplex.
com/PHPExcel/Wiki/View.
aspxtitle=Credits&referringTitle=Homeforup-to-datecredits.
ValidarraykeysforstyleapplyFromArray()ThefollowingtableliststhevalidarraykeysforPHPExcel_StyleapplyFromArray()classes.
Ifthe"Mapstoproperty"columnmapsakeytoasetter,thevalueprovidedforthatkeywillbeapplieddirectly.
Ifthe"Mapstoproperty"columnmapsakeytoagetter,thevalueprovidedforthatkeywillbeappliedasanotherstylearray.
PHPExcel_StyleArraykey:Mapstoproperty:fillfontbordersalignmentnumberformatprotectiongetFill()getFont()getBorders()getAlignment()getNumberFormat()getProtection()PHPExcel_Style_FillArraykey:Mapstoproperty:typerotationstartcolorendcolorcolorsetFillType()setRotation()getStartColor()getEndColor()getStartColor()PHPExcel_Style_FontArraykey:Mapstoproperty:namebolditalicunderlinestrikecolorsizesuperScriptsubScriptsetName()setBold()setItalic()setUnderline()setStrikethrough()getColor()setSize()setSuperScript()setSubScript()PHPExcel_Style_BordersArraykey:Mapstoproperty:allbordersleftrighttopbottomdiagonalverticalhorizontaldiagonaldirectionoutlinegetLeft();getRight();getTop();getBottom()getLeft()getRight()getTop()getBottom()getDiagonal()getVertical()getHorizontal()setDiagonalDirection()setOutline()PHPExcel_Style_BorderArraykey:Mapstoproperty:stylecolorsetBorderStyle()getColor()PHPExcel_Style_AlignmentArraykey:Mapstoproperty:horizontalverticalrotationwrapshrinkToFitindentsetHorizontal()setVertical()setTextRotation()setWrapText()setShrinkToFit()setIndent()PHPExcel_Style_NumberFormatArraykey:Mapstoproperty:codesetFormatCode()PHPExcel_Style_ProtectionArraykey:Mapstoproperty:lockedhiddensetLocked()setHidden()