CODEWITHSUNDEEP
excelinteropformatfooter
SeaDrive
SeaDrive

Reputation: 4292

Excel Interop: Formatting Footers

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?

Upvotes: 5

Views: 12824

Answers (4)

Joe Erickson
Joe Erickson

Reputation: 7217

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*.

* Font codes appearing after any text or non-font codes will be ignored by SpreadsheetGear's printing engine.

Upvotes: 12

Dave Cousineau
Dave Cousineau

Reputation: 13168

Didn't see this mentioned; the &B &I and similar codes can also be turned off, similar to html codes. Ex:

PageSetup.LeftHeader = "&B&IBOLDITALIC&I BOLD&B NORMAL";

gives:

BOLDITALIC BOLD NORMAL

Upvotes: 1

ConcernedOfTunbridgeWells
ConcernedOfTunbridgeWells

Reputation: 66622

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.

Upvotes: 0

Adam Ralph
Adam Ralph

Reputation: 29956

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.

Upvotes: 9

Related Questions