OpenXML SpreadsheetDocument Header and Footer

Question

I have the following code for WordprocessingDocument. I am able to edit the document with OpenXMl and remove then add back the header and footer with the updated company information.

using (WordprocessingDocument wdDoc = WordprocessingDocument.Open(filestream, true))
{
    // Get the main document part
    MainDocumentPart mainDocumentPart = wdDoc.MainDocumentPart;

    // Delete the existing header and footer parts
    mainDocumentPart.DeleteParts(mainDocumentPart.HeaderParts);
    mainDocumentPart.DeleteParts(mainDocumentPart.FooterParts);

    // Create a new header part
    HeaderPart headerPart = mainDocumentPart.AddNewPart<HeaderPart>();
    headerPart.Header = header;

    // Create header content
    // Code not included

    // Create a new footer part
    FooterPart footerPart = mainDocumentPart.AddNewPart<FooterPart>();

    // Create footer content
    // Code not included

    foreach (var section in sections)
    {
         // Delete existing references to headers and footers
         section.RemoveAllChildren<HeaderReference>();
         section.RemoveAllChildren<FooterReference>();

         // Create the new header and footer reference node
         section.PrependChild<HeaderReference>(new HeaderReference() { Id = headerPartId });
         section.PrependChild<FooterReference>(new FooterReference() { Id = footerPartId });

         PageMargin pageMargin = new PageMargin()
             {
                Top = 0,
                Right = (UInt32Value)504U,
                Bottom = 504,
                Left = (UInt32Value)504U,
                Header = (UInt32Value)360U,
                Footer = (UInt32Value)360U,
                Gutter = (UInt32Value)0U
             };

         section.Append(pageMargin);
     }

     mainDocumentPart.Document.Save();
     wdDoc.Close();
}

I am trying to do the same for SpreadsheetDocument, but I can't figure out how to remove/delete the existing header and footer. I see the DeletePart as part of the OpenXmlPartContainer which is part of OpenXmlPart : OpenXmlPartContainer. The spreadsheets have header and footers, but I don't see how to access them.

using (SpreadsheetDocument workbook = SpreadsheetDocument.Open(filestream, true))
{
     // Get the spreadsheet document parts
     WorkbookPart wbPart = workbook.WorkbookPart;
     WorksheetPart wsPart = wbPart.WorksheetParts.First();

     // Delete existing header
     Header header = wsPart.Worksheet.Descendants<Header>().FirstOrDefault();

     if (header != null)
     {
         wsPart.DeleteParts<Header>(header);
     }

     // Delete existing footer
     Footer footer = wsPart.Worksheet.Descendants<Footer>().FirstOrDefault();

     if (footer != null)
     {
         wsPart.DeleteParts<Footer>(footer);
     }
}

Answer 1

Check this code. I just tested it and it works on spreadsheets.

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Linq;

namespace ExcelTest
{
    class Program
    {
        static void Main(string[] args)
        {
            string inputPath = @"C:\PATH\Input.xlsx";
            string outputPath = @"C:\PATH\Output.xlsx";

            using (SpreadsheetDocument workbook = SpreadsheetDocument.Open(inputPath, true))
            {
                WorkbookPart workbookPart = workbook.WorkbookPart;
                WorksheetPart worksheetPart = workbookPart.WorksheetParts.FirstOrDefault();
                Worksheet worksheet = worksheetPart.Worksheet;
                HeaderFooter header_footer = worksheet.Descendants<HeaderFooter>().FirstOrDefault();

                if (header_footer != null)
                {
                    var header = header_footer.FirstChild;
                    if (header != null)
                    {
                        header.Remove();
                    }

                    var footer = header_footer.LastChild;
                    if (footer != null)
                    {
                        footer.Remove();
                    }

                    workbook.SaveAs(outputPath);
                }
            }
        }
    }
}