To create and format spreadsheet headers and footers, use the HeaderFooterSection object, which can be retrieved using the PageSetup.GetHeader(HeaderFooterSection.Section) and PageSetup.GetFooter(HeaderFooterSection.Section) methods.
To format a header or footer in script, include formatting codes within the SetContent() call, for example:
Formatting Codes for Headers and Footers |
|
---|---|
&E |
Turns double-underline on or off. |
&X |
Turns superscript on or off. |
&Y |
Turns subscript on or off. |
&B |
Turns bold on or off. |
&I |
Turns italic on or off. |
&U |
Turns underline on or off. |
&S |
Turns strikethrough on or off. |
&D |
Prints the current date. |
&T |
Prints the current time. |
&F |
Prints the name of the document. |
&A |
Prints the name of the worksheet. |
&P |
Prints the page number. |
&P+number |
Prints the page number plus the specified number. |
&P-number |
Prints the page number minus the specified number. |
&& |
Prints a single ampersand. |
&"font name" |
Prints the characters that follow in the specified font. |
&nn |
Prints the characters that follow in the specified font size. Use a two digit number to specify a size in points. |
&N |
Prints the total number of pages in the document. |
Formatting Headers and Footers in Microsoft Excel
When opening an existing Excel spreadsheet, ExcelWriter will preserve the header and footer formatting created in Excel. However, if you change the header/footer text in your ExcelWriter script and do not include Formatting Codes, the formatting created in Excel will be overwritten and default formatting will be applied. To preserve header/footer formatting created in Excel, do not change header/footer text in script.
To format spreadsheet headers and footers in Excel and preserve the formatting in ExcelWriter:
- In Microsoft Excel, open the File menu and select Page Setup...
- Select the Header/Footer tab.
- Select a header and/or footer from the drop-down list.
Or, create a custom header/footer.
- Save the spreadsheet.
- In an ExcelWriter script, open the spreadsheet containing headers/footers. Use either ExcelTemplate.Open or ExcelApplication.Open to open the spreadsheet.
- Modify the spreadsheet in script. Do not change the text of headers and footers; if you do, the original formatting will be overwritten and default formatting will be applied.