I am sending Excel files as memory stream, editing them and then send them back to the browser so they open in client office program (Microsoft Excel). In the editing process I add footer on all excel sheets in the workbook. The inserting of footers works as it should most of the time, but it fails if the Excel - file I edit has many "complicated" sheets with graphs etc in it. This is the code I use for inserting footer
// Adds footer to all sheets in the workbook except the one inserted by code
private static void AddFooterToAllSheets(SpreadsheetDocument spreadSheetDocument, string footerText, string sheetTitle)
{
var workbookPart = spreadSheetDocument.WorkbookPart;
var workbook = spreadSheetDocument.WorkbookPart.Workbook;
var sheetIndex = 0;
//variable worksheetpart is not used in code, but added here because of looping
foreach (var worksheetpart in workbook.WorkbookPart.WorksheetParts)
{
string sheetName = workbookPart.Workbook.Descendants<Sheet>().ElementAt(sheetIndex).Name;
if (sheetName.Equals(sheetTitle))
{
sheetIndex++;
continue;
}
InsertHeaderFooter(spreadSheetDocument, sheetName, footerText, HeaderType.AllFooter);
sheetIndex++;
}
}
public static void InsertHeaderFooter(SpreadsheetDocument document, string sheetName, string textToInsert, HeaderType type)
{
var wbPart = document.WorkbookPart;
// Find the sheet with the supplied name, and then use
// that Sheet object to retrieve a reference to
// the appropriate worksheet.
var theSheet = wbPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault();
if (theSheet == null)
{
return;
}
var wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
var ws = wsPart.Worksheet;
// Worksheet is nothing? You have a damaged workbook!
if (ws == null)
{
return;
}
// Retrieve a reference to the header/footer node, if it exists.
var hf = ws.Descendants<HeaderFooter>().FirstOrDefault();
if (hf == null)
{
hf = new HeaderFooter();
ws.AppendChild<HeaderFooter>(hf);
}
// The HeaderFooter node should be there, at this point!
if (hf != null)
{
// You've found the node. Now add the header or footer.
// Deal with the attributes first:
switch (type)
{
case HeaderType.EvenHeader:
case HeaderType.EvenFooter:
case HeaderType.OddHeader:
case HeaderType.OddFooter:
// Even or odd only? Add a differentOddEven attribute and set
// it to "1".
hf.DifferentOddEven = true;
break;
case HeaderType.FirstFooter:
case HeaderType.FirstHeader:
hf.DifferentFirst = true;
break;
}
switch (type)
{
// This code creates new header elements, even if they
// already exist. Either way, you end up with a
// "fresh" element.
case HeaderType.AllHeader:
hf.EvenHeader = new EvenHeader { Text = textToInsert };
hf.OddHeader = new OddHeader { Text = textToInsert };
break;
case HeaderType.AllFooter:
hf.EvenFooter = new EvenFooter { Text = textToInsert };
hf.OddFooter = new OddFooter { Text = textToInsert };
break;
case HeaderType.EvenFooter:
hf.EvenFooter = new EvenFooter { Text = textToInsert };
break;
case HeaderType.EvenHeader:
hf.EvenHeader = new EvenHeader { Text = textToInsert };
break;
case HeaderType.OddFooter:
hf.OddFooter = new OddFooter { Text = textToInsert };
break;
case HeaderType.OddHeader:
hf.OddHeader = new OddHeader { Text = textToInsert };
break;
case HeaderType.FirstHeader:
hf.FirstHeader = new FirstHeader { Text = textToInsert };
break;
case HeaderType.FirstFooter:
hf.FirstFooter = new FirstFooter { Text = textToInsert };
break;
}
}
ws.Save();
}
Trying to open the document gives me a message that the Excel-file is corrupt. The error I get in Open XML SDK 2.5 Productivity Tool when validating the corrupt file is this:
Error Node Type: Worskheet Error Part: /xl/worksheets/sheet2.xml Error Node Path: /x:worksheet[1] Related Node Type: HeaderFooter
Description: The element has unexpected child element 'http://schemas.openxmlformats.org/spreadsheetml/2006/main:headerFooter'.
