| Control | Category | Description | | --------------- | ------------- | --------------------- | | &L | Justification | Left | | &C | | Center | | &R | | Right | | &P | Information | Page number | | &N | | Total number of pages | | &D | | Date | | &T | | Time | | &F | | File name | | &A | | Worksheet name | | &Z | | Workbook path | | &fontsize | Font | Font size | | &"font,style" | | Font name and style | | &U | | Single underline | | &E | | Double underline | | &S | | Strikethrough | | &X | | Superscript | | &Y | | Subscript |
Text in headers and footers can be justified (aligned) to the left, center and right by prefixing the text with the control characters &L, &C and &R.
For example (with ASCII art representation of the results):
@code worksheet_set_header(worksheet, "&LHello");
| | | Hello | | | worksheet_set_header(worksheet, "&CHello");
| | | Hello | | |
worksheet_set_header(worksheet, "&RHello");
| | | Hello | | | @endcode For simple text, if you do not specify any justification the text will be centered. However, you must prefix the text with `&C` if you specify a font name or any other formatting: @code worksheet_set_header(worksheet, "Hello");
| | | Hello | | |
@endcode
You can have text in each of the justification regions:
@code worksheet_set_header(worksheet, "&LCiao&CBello&RCielo");
| | | Ciao Bello Cielo | | | @endcode The information control characters act as variables that Excel will update as the workbook or worksheet changes. Times and dates are in the users default format: @code worksheet_set_header(worksheet, "&CPage &P of &N");
| | | Page 1 of 6 | | |
worksheet_set_header(worksheet, "&CUpdated at &T");
| Updated at 12:30 PM | | |
@endcode
You can specify the font size of a section of the text by prefixing it with the control character &n where n is the font size:
@code worksheet_set_header(worksheet1, "&C&30Hello Big"); worksheet_set_header(worksheet2, "&C&10Hello Small");
@endcode
You can specify the font of a section of the text by prefixing it with the control sequence &"font,style" where fontname is a font name such as Windows font descriptions: "Regular", "Italic", "Bold" or "Bold Italic": "Courier New" or "Times New Roman" and style is one of the standard
@code worksheet_set_header(worksheet1, "&C&\"Courier New,Italic\"Hello"); worksheet_set_header(worksheet2, "&C&\"Courier New,Bold Italic\"Hello"); worksheet_set_header(worksheet3, "&C&\"Times New Roman,Regular\"Hello");
@endcode
It is possible to combine all of these features together to create sophisticated headers and footers. As an aid to setting up complicated headers and footers you can record a page set-up as a macro in Excel and look at the format strings that VBA produces. Remember however that VBA uses two double quotes "" to indicate a single double quote. For the last example above the equivalent VBA code looks like this:
@code .LeftHeader = "" .CenterHeader = "&""Times New Roman,Regular""Hello" .RightHeader = ""
@endcode
Alternatively you can inspect the header and footer strings in an Excel file by unzipping it and grepping the XML sub-files. The following shows how to do that using libxml's xmllint to format the XML for clarity:
@code
$ unzip myfile.xlsm -d myfile $ xmllint --format find myfile -name "*.xml" | xargs | egrep "Header|Footer"
<headerFooter scaleWithDoc="0"> <oddHeader>&L&P</oddHeader> </headerFooter>
@endcode
Note that in this case you need to unescape the Html. In the above example the header string would be &L&P.
To include a single literal ampersand & in a header or footer you should use a double ampersand &&:
@code worksheet_set_header(worksheet, "&CCuriouser && Curiouser - Attorneys at Law"); @endcode
Note, the header or footer string must be less than 255 characters. Strings longer than this will not be written.
@brief Set the printed page header caption.
@param worksheet Pointer to a lxw_worksheet instance to be updated. @param string The header string.
@return A #lxw_error code.
Headers and footers are generated using a string which is a combination of plain text and control characters.
The available control character are: