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 see spreadsheet headers and footers in Excel, open the File menu, select Page Setup, and select the Header/Footer tab. |
To format a header or footer in script, include formatting codes within the SetContent() call, for example:
using SoftArtisans.OfficeWriter.ExcelWriter; public class HeaderFooterDemo : System.Web.UI.Page { protected void Page_Load(object sender, System.EventArgs e) { ExcelApplication xla = new ExcelApplication(); Workbook wb = xla.Create(); Worksheet sheet1 = wb.Worksheets[0]; PageSetup pgsetup = sheet1.PageSetup; HeaderFooterSection leftHeader = pgsetup.GetHeader(HeaderFooterSection.Section.Left); leftHeader.SetContent("Document name: &F"); HeaderFooterSection centerHeader = pgsetup.GetHeader(HeaderFooterSection.Section.Center); centerHeader.SetContent("Basic Report"); HeaderFooterSection rightHeader = pgsetup.GetHeader(HeaderFooterSection.Section.Right); rightHeader.SetContent("Generated by OfficeWriter"); HeaderFooterSection leftFooter = pgsetup.GetFooter(HeaderFooterSection.Section.Left); leftFooter.SetContent("Created on date &D"); HeaderFooterSection centerFooter = pgsetup.GetFooter(HeaderFooterSection.Section.Center); centerFooter.SetContent("Page &P of &N"); HeaderFooterSection rightFooter = pgsetup.GetFooter(HeaderFooterSection.Section.Right); rightFooter.SetContent("Create at time &T"); } } |
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. |
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: