Problems inserting footer in Excel - OpenXML

Question

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'.

Answer 1

I had the same issue today, I found that in sheet1.xml I had "drawing" tag above "headerFoter", so in my case, the following code resolved the issue:

    var hf = ws.GetFirstChild<HeaderFooter>();
    if (hf == null)
    {
        hf = new HeaderFooter();

        var drawing = ws.GetFirstChild<Drawing>();
        if (drawing != null)
        {
            ws.InsertBefore(hf, drawing);
        }
        else
        {
            ws.AppendChild(hf);
        }
    }

Please pay attention to "InsertBefore" method (ws is Worksheet)

Answer 2

Try this

using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;
using System.Drawing;
using System.IO;
using System.Drawing.Imaging;
using System;

namespace WindowsFormsApplication2
{
    public class GeneratedClass
    {
        private static System.Collections.Generic.IDictionary<System.String, OpenXmlPart> UriPartDictionary = new System.Collections.Generic.Dictionary<System.String, OpenXmlPart>();
        private static System.Collections.Generic.IDictionary<System.String, DataPart> UriNewDataPartDictionary = new System.Collections.Generic.Dictionary<System.String, DataPart>();
        private static SpreadsheetDocument document;

        public static void ChangePackage(string filePath)
        {
            using (document = SpreadsheetDocument.Open(filePath, true))
            {
                ChangeParts();
            }
        }

        private static void ChangeParts()
        {
            //Stores the referrences to all the parts in a dictionary.
            BuildUriPartDictionary();
            //Adds new parts or new relationships.
            AddParts();
            //Changes the contents of the specified parts.
            ChangeCoreFilePropertiesPart1(((CoreFilePropertiesPart)UriPartDictionary["/docProps/core.xml"]));
            ChangeWorksheetPart1(((WorksheetPart)UriPartDictionary["/xl/worksheets/sheet1.xml"]));
        }

        /// <summary>
        /// Stores the references to all the parts in the package.
        /// They could be retrieved by their URIs later.
        /// </summary>
        private static void BuildUriPartDictionary()
        {
            System.Collections.Generic.Queue<OpenXmlPartContainer> queue = new System.Collections.Generic.Queue<OpenXmlPartContainer>();
            queue.Enqueue(document);
            while (queue.Count > 0)
            {
                foreach (var part in queue.Dequeue().Parts)
                {
                    if (!UriPartDictionary.Keys.Contains(part.OpenXmlPart.Uri.ToString()))
                    {
                        UriPartDictionary.Add(part.OpenXmlPart.Uri.ToString(), part.OpenXmlPart);
                        queue.Enqueue(part.OpenXmlPart);
                    }
                }
            }
        }

        /// <summary>
        /// Adds new parts or new relationship between parts.
        /// </summary>
        private static void AddParts()
        {
            //Generate new parts.
            VmlDrawingPart vmlDrawingPart1 = UriPartDictionary["/xl/worksheets/sheet1.xml"].AddNewPart<VmlDrawingPart>("rId2");
            GenerateVmlDrawingPart1Content(vmlDrawingPart1);

            ImagePart imagePart1 = vmlDrawingPart1.AddNewPart<ImagePart>("image/png", "rId1");
            GenerateImagePart1Content(imagePart1);

        }

        private static void GenerateVmlDrawingPart1Content(VmlDrawingPart vmlDrawingPart1)
        {
            System.Xml.XmlTextWriter writer = new System.Xml.XmlTextWriter(vmlDrawingPart1.GetStream(System.IO.FileMode.Create), System.Text.Encoding.UTF8);
            writer.WriteRaw("<xml xmlns:v=\"urn:schemas-microsoft-com:vml\"\r\n xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n xmlns:x=\"urn:schemas-microsoft-com:office:excel\">\r\n <o:shapelayout v:ext=\"edit\">\r\n  <o:idmap v:ext=\"edit\" data=\"1\"/>\r\n </o:shapelayout><v:shapetype id=\"_x0000_t75\" coordsize=\"21600,21600\" o:spt=\"75\"\r\n  o:preferrelative=\"t\" path=\"m@4@5l@4@11@9@11@9@5xe\" filled=\"f\" stroked=\"f\">\r\n  <v:stroke joinstyle=\"miter\"/>\r\n  <v:formulas>\r\n   <v:f eqn=\"if lineDrawn pixelLineWidth 0\"/>\r\n   <v:f eqn=\"sum @0 1 0\"/>\r\n   <v:f eqn=\"sum 0 0 @1\"/>\r\n   <v:f eqn=\"prod @2 1 2\"/>\r\n   <v:f eqn=\"prod @3 21600 pixelWidth\"/>\r\n   <v:f eqn=\"prod @3 21600 pixelHeight\"/>\r\n   <v:f eqn=\"sum @0 0 1\"/>\r\n   <v:f eqn=\"prod @6 1 2\"/>\r\n   <v:f eqn=\"prod @7 21600 pixelWidth\"/>\r\n   <v:f eqn=\"sum @8 21600 0\"/>\r\n   <v:f eqn=\"prod @7 21600 pixelHeight\"/>\r\n   <v:f eqn=\"sum @10 21600 0\"/>\r\n  </v:formulas>\r\n  <v:path o:extrusionok=\"f\" gradientshapeok=\"t\" o:connecttype=\"rect\"/>\r\n  <o:lock v:ext=\"edit\" aspectratio=\"t\"/>\r\n </v:shapetype><v:shape id=\"LH\" o:spid=\"_x0000_s1025\" type=\"#_x0000_t75\"\r\n  style=\';margin-left:0;margin-top:0;width:207pt;height:156pt;\r\n  z-index:1\'>\r\n  <v:imagedata o:relid=\"rId1\" o:title=\"WOPI\"/>\r\n  <o:lock v:ext=\"edit\" rotation=\"t\"/>\r\n </v:shape></xml>");
            writer.Flush();
            writer.Close();
        }

        private static void GenerateImagePart1Content(ImagePart imagePart1)
        {
            Image image = Image.FromFile(@"C:\Users\Administrator\Desktop\Capture.PNG");
            using (MemoryStream stream = new MemoryStream())
            {
                // Save image to stream.
                image.Save(stream, ImageFormat.Png);
                string imagePart1Data = Convert.ToBase64String(stream.ToArray());
                System.IO.Stream data = GetBinaryDataStream(imagePart1Data);
                imagePart1.FeedData(data);
                data.Close();
            }    

        }

        private static void ChangeCoreFilePropertiesPart1(CoreFilePropertiesPart coreFilePropertiesPart1)
        {
            var package = coreFilePropertiesPart1.OpenXmlPackage;
            package.PackageProperties.Modified = System.Xml.XmlConvert.ToDateTime("2015-07-30T03:03:22Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind);
        }

        private static void ChangeWorksheetPart1(WorksheetPart worksheetPart1)
        {
            Worksheet worksheet1 = worksheetPart1.Worksheet;

            HeaderFooter headerFooter1 = new HeaderFooter();
            OddHeader oddHeader1 = new OddHeader();
            oddHeader1.Text = "&L&G";

            headerFooter1.Append(oddHeader1);
            worksheet1.Append(headerFooter1);

            LegacyDrawingHeaderFooter legacyDrawingHeaderFooter1 = new LegacyDrawingHeaderFooter() { Id = "rId2" };
            worksheet1.Append(legacyDrawingHeaderFooter1);
        }

            private static System.IO.Stream GetBinaryDataStream(string base64String)
        {
            return new System.IO.MemoryStream(System.Convert.FromBase64String(base64String));
        }



    }
}

Took it from: Edward8520