none
Excel Open XML RowBreaks and Footer Images - XML file positioning RRS feed

  • Question

  • Using VS2012, C#, OpenXML 2.5, I created a test utility that adds a page break (Row Break) to a worksheet using (pseudo):

    objRB = New RowBreaks();
    objRowBreaks.Append(objRB);
    objRowBreaks.ManualBreakCount++;
    objRowBreaks.Count++;

    Works just fine. Then I added an image (manually) in my Excel page footer. Then I run the program that appends the Row Break again, and when I attempt to open the workbook afterwards, I get an error: "We found a problem with some content in 'YourSheet.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes."

    But nothing is recoverable and I end up with an empty workbook.

    After some analysis with the Open XML 2.5 Productivity Tool, I discovered that the problem is the positioning of the elements within the workbook's XML. When you manually add a page break to the same workbook and save it, the worksheet elements are ordered as follows:


    <rowBreaks count="1" manualBreakCount="1">
        <brk id="24" max="16383" man="1" />
    </rowBreaks>
    <legacyDrawingHF r:id="rId2" />

    just before the worksheet element close (</worksheet>) and all is well.

    However, when I programmatically add the row/page break (as described above), the XML elements are output with the RowBreaks at the end:

    <x:legacyDrawingHF r:id="rId2" />
    <x:rowBreaks count="1" manualBreakCount="1">
        <x:brk id="24" max="16383" man="1" />
    </x:rowBreaks>
    

    This causes the corruption error described above.  If I manually edit the XML to reverse the position of the RowBreaks and the LegacyDrawingHF, the workbook opens just fine.

    Finally, a question:  Does anyone know how to programmatically make sure that the RowBreaks element gets positioned before the LegacyDrawingHeaderFooter element?  Or know of any other way around this problem?

    Thanks.

    Thursday, April 28, 2016 7:07 PM

Answers

  • Hi Marak,

    How did you insert the row breaks and page footer with images using Open XML? Base on my understanding, we need to append the "legacyDrawingHF" element after the "RowBreaks".

    For example, there is the code that append this two elements for your reference:

      RowBreaks rowBreaks1 = new RowBreaks(){ Count = (UInt32Value)1U, ManualBreakCount = (UInt32Value)1U };
                Break break1 = new Break(){ Id = (UInt32Value)9U, Max = (UInt32Value)16383U, ManualPageBreak = true };
    
                rowBreaks1.Append(break1);
                LegacyDrawingHeaderFooter legacyDrawingHeaderFooter1 = new LegacyDrawingHeaderFooter(){ Id = "rId2" };
    
    
    worksheet1.Append(rowBreaks1);
    worksheet1.Append(legacyDrawingHeaderFooter1);
    
    

    You can also reflect the code using Open XML SDK 2.5 Productivity Tools. And if you still have the problem would mind sharing with us a code sample to narrow down this issue?

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, April 29, 2016 3:00 AM
    Moderator

All replies

  • Hi Marak,

    How did you insert the row breaks and page footer with images using Open XML? Base on my understanding, we need to append the "legacyDrawingHF" element after the "RowBreaks".

    For example, there is the code that append this two elements for your reference:

      RowBreaks rowBreaks1 = new RowBreaks(){ Count = (UInt32Value)1U, ManualBreakCount = (UInt32Value)1U };
                Break break1 = new Break(){ Id = (UInt32Value)9U, Max = (UInt32Value)16383U, ManualPageBreak = true };
    
                rowBreaks1.Append(break1);
                LegacyDrawingHeaderFooter legacyDrawingHeaderFooter1 = new LegacyDrawingHeaderFooter(){ Id = "rId2" };
    
    
    worksheet1.Append(rowBreaks1);
    worksheet1.Append(legacyDrawingHeaderFooter1);
    
    

    You can also reflect the code using Open XML SDK 2.5 Productivity Tools. And if you still have the problem would mind sharing with us a code sample to narrow down this issue?

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, April 29, 2016 3:00 AM
    Moderator
  • Fei,

    Thanks for pointing me to the "reflect code" option.  That got me started on the path to finding the ultimate solution.

    The problem with your common sense solution of inserting the RowBreaks before the HeaderFooter is that I am not in control of the HeaderFooter. It is already part of a template that is being modified. So, when my application starts, the Footer is already present.

    Solution: Identify the existing footer and use the worksheet's "InsertBefore" method to make sure my RowBreaks appear in the correct location.

    Example:

    ...
    // Make sure the RowBreaks always get inserted before
    //  existing Footer.
    LegacyDrawingHeaderFooter objHF = objWorksheetPart.Worksheet.Descendents<LegacyDrawingHeaderFooter>().FirstOrDefault();
    if (hf == null)
    {
        // There's no header/footer. Just append RowBreaks
        //   to the end.
        objWorksheetPart.Worksheet.Append(objRB);
    }
    else
    {
        // Make sure RowBreaks are inserted before
        //    Header/Footer.
        objWorksheetPart.Worksheet.InsertBefore(objRB, objHF);
    }
    ...
    
    Thanks again for your help.

    Wednesday, May 4, 2016 2:12 PM