Setting the print header and footer of a worksheet
Setting a worksheet’s print header and footer can be done using the following lines of code:
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&C&HPlease treat this document as confidential!');
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');
Substitution and formatting codes (starting with &) can be used inside headers and footers. There is no required order in which these codes must appear.
The first occurrence of the following codes turns the formatting ON, the second occurrence turns it OFF again:
» Strikethrough
» Superscript
» Subscript
Superscript and subscript cannot both be ON at same time. Whichever comes first wins and the other is ignored, while the first is ON.
The following codes are supported by Excel2007:
&L
Code for "left section" (there are three header / footer locations, "left", "center", and "right"). When two or more occurrences of this section marker exist, the contents from all markers are concatenated, in the order of appearance, and placed into the left section.
&P
Code for "current page #"
&N
Code for "total pages"
&font size
Code for "text font size", where font size is a font size in points.
&K
Code for "text font color"
» RGB Color is specified as RRGGBB
» Theme Color is specifed as TTSNN where TT is the theme color Id, S is either "+" or "-" of the tint/shade value, NN is the tint/shade value.
&S
Code for "text strikethrough" on / off
&X
Code for "text super script" on / off
&Y
Code for "text subscript" on / off
&C
Code for "center section". When two or more occurrences of this section marker exist, the contents from all markers are concatenated, in the order of appearance, and placed into the center section.
&D
Code for "date"
&T
Code for "time"
&G
Code for "picture as background"
Please make sure to add the image to the header/footer:
$objDrawing = new PHPExcel_Worksheet_HeaderFooterDrawing();
$objDrawing->setName('PHPExcel logo');
$objDrawing->setPath('./images/phpexcel_logo.gif');
$objDrawing->setHeight(36);
$objPHPExcel->getActiveSheet()->getHeaderFooter()->addImage($objDrawing, PHPExcel_Worksheet_HeaderFooter::IMAGE_HEADER_LEFT);
&U
Code for "text single underline"
&E
Code for "double underline"
&R
Code for "right section". When two or more occurrences of this section marker exist, the contents from all markers are concatenated, in the order of appearance, and placed into the right section.
&Z
Code for "this workbook's file path"
&F
Code for "this workbook's file name"
&A
Code for "sheet tab name"
&+
Code for add to page #
&-
Code for subtract from page #
&"font name,font type"
Code for "text font name" and "text font type", where font name and font type are strings specifying the name and type of the font, separated by a comma. When a hyphen appears in font name, it means "none specified". Both of font name and font type can be localized values.
&"-,Bold"
Code for "bold font style"
&B
Code for "bold font style"
&"-,Regular"
Code for "regular font style"
&"-,Italic"
Code for "italic font style"
&I
Code for "italic font style"
&"-,Bold Italic"
Code for "bold italic font style"
&O
Code for "outline style"
&H
Code for "shadow style"
i Tip
The above table of codes may seem overwhelming first time you are trying to figure out how to write some header or footer. Luckily, there is an easier way. Let Microsoft Office Excel do the work for you.
For example, create in Microsoft Office Excel an xlsx file where you insert the header and footer as desired using the programs own interface. Save file as test.xlsx. Now, take that file and read off the values using PHPExcel as follows:
That reveals the codes for the even/odd header and footer. Experienced users may find it easier to rename test.xlsx to test.zip, unzip it, and inspect directly the contents of the relevant xl/worksheets/sheetX.xml to find the codes for header/footer.
