Display page numbers in a excel sheet generated using C#.NET

Question

Does anyone have an idea on how to include or input the page numbers in the excel sheet generated using C# code.

I use the libraries available in Microsoft.Office.Interop.Excel to generate the file.

However by default in the output i cannot see the page numbers. I know to enable this via

excel options (View --> Header and Footer ...) but i want to automate this via C#.

Is this possible, if yes kindly share the snippet for the same.

Thanks Constant Learner

Answer 1

If I don't know how to code something in Office, I record my action as a macro and then I look at the generated code in the built-in Visual Basic editor. This is the relevant code it generated for adding a footer with page numbers:

ActiveSheet.PageSetup.CenterFooter = "Page &P of &N"

LeftFooter and RightFooter are also available.

Answer 2

The problem I was having was entering the following, which is how excel displays this when adding it manually;

    ws.PageSetup.CenterFooter = "&[Pages]/&[Pages]";  // This did not work

This did not work, however the following did;

    ws.PageSetup.CenterFooter = "&P/&N"; // This worked correctly

I found the same when entering the filename and date.

    ws.PageSetup.LeftHeader = "&[File]";  // This did not work
    ws.PageSetup.RightHeader = "&[Date]";  // This did not work

    ws.PageSetup.LeftHeader = "&F"; // This worked correctly
    ws.PageSetup.RightHeader = "&D"; // This worked correctly

Hope this helps if you have tried the method I tried at first.