How to get Header / Footer parts from Excel Document

Question

I'm trying to get the header / footer parts from an excel document so that I can do something with their contents, however I cannot seem to get anything from them.

I thought this would be pretty simple... Consider this code:

using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(filePath, true))
{
    var headers = spreadsheet.GetPartsOfType<HeaderPart>().ToList();

    foreach (var header in headers)
    {
        //do something
    }
}

Even with a file that contains a header, headers will always be empty. I've tried drilling down into the workbook -> worksheets -> etc but i get nothing back. My testing excel file definitely has a header (headers are ghastly in excel!).

Annoyingly the api's for excel in openxml seem to be worse as in a docx you can get the header by calling:

using (WordprocessingDocument wordDoc = WordprocessingDocument.Open(filePath, true))
{
    MainDocumentPart documentPart = wordDoc.MainDocumentPart;

    var headerParts = wordDoc.MainDocumentPart.HeaderParts.ToList();

    foreach (var headerPart in headerParts)
    {
        //do something
    }
}

I've seen some people on google saying that I should query the worksheet's descendants (code from this link):

HeaderFooter hf = ws.Descendants<HeaderFooter>().FirstOrDefault();
if (hf != null)
{
//here you can add your code
//I just try to append here for demo
  hf = new HeaderFooter();
  ws.AppendChild<HeaderFooter>(hf);
}

But I cannot see any way of querying the workbook/sheet/anything with .Descendants and obviously none of the code examples on google show how they got ws ????.

Any ideas? Thanks

In VBA headers must be accessed via LeftHeader, CenterHeader and RightHeader and footers via LeftFooter, CenterFooter and RightFooter, perhaps you need something similar? — cybernetic.nomad

petelids petelids · Accepted Answer · 2019-03-26T11:02:07

HeaderFooter, as per your second example, is the correct way to read a Header or Footer from a Spreadsheet using OpenXML. The ws in your example refers to a Worksheet.

The following is an example that reads the HeaderFooter and dumps the InnerText to the console.

using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, false))
{
    WorkbookPart workbookPart = document.WorkbookPart;
    WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
    Worksheet ws = worksheetPart.Worksheet;

    HeaderFooter hf = ws.Descendants<HeaderFooter>().FirstOrDefault();

    if (hf != null)
    {
        Console.WriteLine(hf.InnerText);
    }
}

I would highly recommend that you read the documentation for the HeaderFooter element as it's more complex than you might imagine. The documentation can be found in section 18.3.1.46 of the Fifth Edition of the Ecma Office Open XML Part 1 - Fundamentals And Markup Language Reference which can be found here.

How to get Header / Footer parts from Excel Document

1 Answers