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!
Hi,
If you have only one sheet in the excel, try the following code to add Header & Footer.
using System.Linq; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; namespace AddHeaderFooter_Excel { class Program { static void Main(string[] args) { using(SpreadsheetDocument excelDoc = SpreadsheetDocument.Open("Sample.xlsx",true)) { AddHeader(excelDoc); excelDoc.WorkbookPart.Workbook.Save(); } } private static void AddHeader(SpreadsheetDocument excelDoc) { Worksheet sheet = (excelDoc.WorkbookPart.WorksheetParts.ElementAt(0)).Worksheet; AddHeader(sheet); } private static void AddHeader(Worksheet sheet) { HeaderFooter headerFooter = new HeaderFooter(); OddHeader oddHeader = new OddHeader(); oddHeader.Text = "&C&\"Verdana,Bold\"&10brand new header !"; OddFooter oddFooter = new OddFooter(); oddFooter.Text = "&C&\"Algerian,Bold\"&9this is footer area"; headerFooter.Append(oddHeader); headerFooter.Append(oddFooter); sheet.Append(headerFooter); } } }
Open the modified excel file. By default it does not show header/footer.Click on Insert -> Header & Footer (I am using 2010). You will be able to see the applied Header & Footer to the worksheet.
Thanks for your reply... I think I found out the complication with this issue: the program is using a template stream rather than a concrete template. So where your code has:
using(SpreadsheetDocument excelDoc = SpreadsheetDocument.Open("Sample.xlsx",true)
This code has:
public void Initialize(Stream templateStream)
{
spreadsheet = SpreadsheetDocument.Open(templateStream, true);
}
I'm guessing that complicates things in terms of headers and footers... I'm not sure of the ramifications of this yet.
Even with your code it should work. But remember to 'save' & 'close' the spreadsheet object.
Thanks again for your help, I really appreciate it!
But something else must be going on. First off, I didn't tell you that I do have 2 sheets, not 1. The code as I entered it compiles fine and the spreadsheet generates, but when I open the Excel doc it displays "Excel found unreadable content in [file name]. Do you want to recover the contents?" When I recover the formatting of the 1st spreadsheet is off, though I do see data, and the 2nd sheet has disappeared entirely apart from its bottom tab. I don't see any sign of a header or footer anywhere. When I enable editing I see a notice that Excel has repaired or removed content. The screen doesn't contain any really useful content apart from that.
Does the 2 sheet issue change things?
ewomack Thanks again for your help, I really appreciate it! But something else must be going on. First off, I didn't tell you that I do have 2 sheets, not 1. The code as I entered it compiles fine and the spreadsheet generates, but when I open the Excel doc it displays "Excel found unreadable content in [file name]. Do you want to recover the contents?" When I recover the formatting of the 1st spreadsheet is off, though I do see data, and the 2nd sheet has disappeared entirely apart from its bottom tab. I don't see any sign of a header or footer anywhere. When I enable editing I see a notice that Excel has repaired or removed content. The screen doesn't contain any really useful content apart from that. Does the 2 sheet issue change things?
If you have more then one sheet. and want to add header and footer on both of the sheet.
I think you can loop through the WorksheetParts and add header and footer with same function.
And as far as ""Excel found unreadable content in [file name]" error is concern.
you can use Open XML productivity tool for checking which part of the spreadshet package causing this issue.
I have come across this issue many time and Productivity tool helps me in finding the cause of this problem.