Openxml excel: problem opening print dialog. File has frozen rows

See more:

C#

OpenXML

I hope somebody has come across the problem and has a solution.

I am able to successfully create an Excel file utilizing the DocumentFormat.OpenXml.dll

The problem is, if my code freezes the top x number of rows, later when I open the document in Excel and attempt to use the Print dialog I get one of those "Microsoft Excel has stopped working" dialog boxes.

If I unfreeze the rows; save the file; reopen it; the Print dialog works fine. I can even then manually freeze the rows; save the file; open it up in Excel; the Print dialog works fine.

I even opened up both files in the OpenXML Productivity tool and the code looks just the same in both.

So there must be something else wrong with what I'm doing.

If it helps, here is the function that does everything. The stylesheet and datasheet are populated elsewhere:

public void CreateSpreadsheetWorkbook ( string filepath, List<string []> dataList, List<string> excelHeader )
    {
    SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create ( filepath, SpreadsheetDocumentType.Workbook );    // Create a spreadsheet document by supplying the filepath.By default, AutoSave = true, Editable = true, and Type = xlsx.

    WorkbookPart            workbookpart        = spreadsheetDocument.AddWorkbookPart ();                                        // Add a WorkbookPart to the document.
    workbookpart.Workbook = new Workbook ();

    WorksheetPart           worksheetPart       = workbookpart.AddNewPart<WorksheetPart> ();                                     // Add a WorksheetPart to the WorkbookPart.
  //Worksheet               worksheet1          = new Worksheet     ();
    Worksheet               worksheet1          = new Worksheet     () { MCAttributes = new MarkupCompatibilityAttributes () { Ignorable = "x14ac" } };

    SheetData               sheetData1          = new SheetData     ();
    Columns                 columns1            = new Columns       ();


    worksheet1.AddNamespaceDeclaration ( "r",     "http://schemas.openxmlformats.org/officeDocument/2006/relationships" );
    worksheet1.AddNamespaceDeclaration ( "x",     "http://schemas.openxmlformats.org/spreadsheetml/2006/main"           );
    worksheet1.AddNamespaceDeclaration ( "x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"         );

    //
    // Freeze at row x...
    //
    string strFreezeRow = "";
    double numRows      = 0.00;

    if ( xlRegular_Heading != "" )
        {
        numRows      = 4;
        strFreezeRow = "A5";
        }
    else
        {
        numRows      = 1;
        strFreezeRow = "A2";
        }

    int             numSheetRows = numRecordCount + (int)numRows;

    SheetDimension          sheetDimension1         = new SheetDimension        () { Reference = "A1:" + strHighestColumn + numSheetRows.ToString() };
    SheetFormatProperties   sheetFormatProperties1  = new SheetFormatProperties () { DefaultRowHeight = 11.25D, DyDescent = 0.2D };
    sheetFormatProperties1.AddNamespaceDeclaration ( "x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" );


    SheetViews              sheetViews1             = new SheetViews            ();

    SheetView               sheetView1              = new SheetView             () { TabSelected = true, WorkbookViewId = (UInt32Value) 0U };
    Pane                    pane1                   = new Pane                  () { VerticalSplit = numRows, TopLeftCell = strFreezeRow, ActivePane = PaneValues.BottomLeft, State = PaneStateValues.Frozen };
    Selection               selection1              = new Selection             () { Pane = PaneValues.BottomLeft, ActiveCell = strFreezeRow, SequenceOfReferences = new ListValue<StringValue> () { InnerText = strFreezeRow } };

    sheetView1.Append  ( pane1      );
    sheetView1.Append  ( selection1 );
    sheetViews1.Append ( sheetView1 );

    uint x = 0;
    for ( x = 0; x < layoutList.Count; x++ )
        {
        uint numIndex = x + 1;

        Column column1 = new Column () { Min = numIndex, Max = numIndex, Width = layoutList [ (int) x ].numColumnWIDTH, Style = 2, CustomWidth = true };
        columns1.Append ( column1 );
        }

    worksheet1.Append ( sheetDimension1         );
    worksheet1.Append ( sheetViews1             );
    worksheet1.Append ( sheetFormatProperties1  );
    worksheet1.Append ( columns1                );
    worksheet1.Append ( sheetData1              );

    if ( xlRegular_Heading != "" )
        {
        MergeCells mergeCells1 = new MergeCells () { Count = (UInt32Value) 2U                   };
        MergeCell  mergeCell1  = new MergeCell  () { Reference = "A1:B1"                        };
        MergeCell  mergeCell2  = new MergeCell  () { Reference = "A2:" + strHighestColumn + "2" };

        mergeCells1.Append ( mergeCell1 );
        mergeCells1.Append ( mergeCell2 );

        worksheet1.Append  ( mergeCells1 );
        }

    worksheet1.Append ( pageMargins );
    worksheet1.Append ( pageSetup   );
    if ( oPrinterSetup.psLeftHeader != "" && oPrinterSetup.psCenterHeader != "" && oPrinterSetup.psRightHeader != "")
        worksheet1.Append ( AddHeader() );

    worksheetPart.Worksheet = worksheet1;

    Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets> ( new Sheets () );                                       // Add Sheets to the Workbook.
    Sheet  sheet  = new Sheet () { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart ( worksheetPart ), SheetId = 1, Name = "MySheet" };   // Append a new worksheet and associate it with the workbook.
    sheet.AddNamespaceDeclaration ( "r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships" );


    DefinedNames definedNames1 = new DefinedNames ();
    DefinedName  definedName1  = new DefinedName  () { Name = "_xlnm.Print_Titles", LocalSheetId = (UInt32Value) 0U };

    sheets.Append ( sheet );


    SheetData sData = worksheetPart.Worksheet.GetFirstChild<SheetData> ();                      // data for the sheet

    excelHeaderMethod ( spreadsheetDocument, sData, _headerColumns,           excelHeader );   // Export header
    ForeachToExcel    ( spreadsheetDocument, sData, _headerColumns, dataList, excelHeader );   // Export data content

    definedName1.Text = "MySheet!" + oPrinterSetup.psPrintTitleRows;
    definedNames1.Append ( definedName1 );

    workbookpart.Workbook.Append ( definedNames1 );

    workbookpart.Workbook.Save ();
    spreadsheetDocument.Close  (); // Close the document.
    }

What I have tried:

My first code set didn't have the various "AddNamespaceDeclaration()" function calls. I don't even know if they are needed or not. I found them in the file after modifying the files in the Excel GUI and then opening them up in the Productivity Tool so I figured they might be needed. I looked online for the "AddNamespaceDeclaration()" and all I found was an MSDN page that showed the function and its parameters. No reason for using it was given nor was there any examples of using it.

I also never had the SheetDimension or SheetFormatProperties before. Ditto from above for why I added them.