Excel Interop: Formatting Footers

Question

I am creating an Excel Worksheet from C# using Microsoft.Office.Interop.Excel, and I have not been able to get the footers the way the User wants them.

1) How can I put footer text in BOLD? 2) How can I put the page number in the footer? (Putting @"Page @[Page]" in as text does not work.)

Possible? Not Possible?

Answer 1

1) worksheet.PageSetup.LeftFooter = "&B Bold text &B"

2) worksheet.PageSetup.CenterFooter = "Page &P"

A tip - open Excel and set up the footer you require via the UI, recording a macro of the actions you take. Then open the VBA editor. The generated VBA will give you clues for how to acheive the same thing via the API. This trick can be used in lots of scenarios when automating Excel.

Answer 2

On a meta level, you can find this sort of thing out by recording a macro and seeing what it does. For example, to do this set up, I recorded the macro and got this out:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 30/06/2009 by bloggsj
'

'
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "&""Arial,Bold""Page &P of &N"          '<== Et. Voila! 
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
    End With
End Sub

The recorded macro has lots of rubbish but we can see (Et. Voila) how Excel does it. From this one can figure out how to do it onesself.

Answer 3

The following codes are from the SpreadsheetGear for .NET help and are compatible with Excel:

• &P - the current page number.
• &N - the total number of pages.
• &B - use a bold font*.
• &I - use an italic font*.
• &U - use an underline font*.
• && - the '&' character.
• &D - the current date.
• &T - the current time.
• &F - workbook name.
• &A - worksheet name.
• &"FontName" - use the specified font name*.
• &N - use the specified font size*.