Add a footer to an existing OpenXML spreadsheet

Question

EDIT: There's a further detail I left out with my original post. The program is using a template stream rather than a concrete template for the ".Open" command. The template stream gets initialized with this code block:

public void Initialize(Stream templateStream)
{
    spreadsheet = SpreadsheetDocument.Open(templateStream, true);
}

I'm still researching this, but does anyone know the implications of using a stream for the HeaderFooter object in OpenXML?

I'm new to OpenXML and still in the process of reading and learning what I can of this massive SDK. I've inherited a C# MVC.NET program that uses OpenXML to display information on an Excel spreadsheet and all of that is working, but I now need to add a footer to the same spreadsheet and I'm hitting some brick walls in my OpenXML knowledge.

I put the footer information I wanted into the spreadsheet, opened it up with the Open XML SDK Productivity Tool and found this code under <.x:oddFooter(OddFooter):

 // Creates an OddFooter instance and adds its children.
        public OddFooter GenerateOddFooter()
        {
            OddFooter oddFooter1 = new OddFooter();
            oddFooter1.Text = "&L&\"Times New Roman,Regular\"Page &P of &N&C&\"Times New Roman,Regular\"Generated On: <Date/Time> Central&R&\"Times New Roman,Regular\"Report";
            return oddFooter1;
        }

And this code one level up under <>x:headerFooter(OddFooter):

// Creates an HeaderFooter instance and adds its children.
        public HeaderFooter GenerateHeaderFooter()
        {
            HeaderFooter headerFooter1 = new HeaderFooter();
            OddFooter oddFooter1 = new OddFooter();
            oddFooter1.Text = "&L&\"Times New Roman,Regular\"Page &P of &N&C&\"Times New Roman,Regular\"Generated On: <Date/Time> Central&R&\"Times New Roman,Regular\"Report";

            headerFooter1.Append(oddFooter1);
            return headerFooter1;
        }

Now I of course need to append the footer info somewhere, and this is where I'm stuck. In <>x:worksheet(Worksheet) I see this line of code:

worksheet1.Append(headerFooter1);

This looked easy enough, but when I looked back at the application code I found no worksheet object to append to. I thought I was close with the following line of code:

spreadsheet.WorkbookPart.Workbook.Append(headerFooter1);

but this yielded nothing. In the application I see a SpreadsheetDocument object and references to OpenXMLParts... do I need to get a spreadsheet part to append to? Or do I need to take a different approach with a Spreadsheet versus a worksheet object? Do I need to materialize the current worksheet and then append?

I have a feeling this has an easy solution, but as I said I'm still learning the SDK.

Thank you!

Seth Flowers Seth Flowers · Accepted Answer · 2012-05-10T19:21:58

For those who don't know about the productivity tool, it is included with the SDK and can be downloaded from https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=5124

On my 64 bit machine, the install path to the tool was: "C:\Program Files (x86)\Open XML SDK\V2.0\tool\OpenXmlSdkTool.exe"

You should be able to manually add a footer to a spreadsheet, and open it up with the tool and see the exact C# required to create the entire spreadsheet, including the footer. You can then remove the parts of the code that are unnecessary, like some of the styles that are automatically added.

Add a footer to an existing OpenXML spreadsheet

1 Answers