Open XML Excel. Can't open Print dialog after creating file. File has frozen rows

Question

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

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 and reopen it, the Print dialog works fine. I can even then manually freeze the rows, save the file and 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"         );

    //
    // Setup Freeze and SheetDimensions data elements...
    //
    string strFreezeRow = "";
    double numRows      = 0.00;

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

    int             numSheetRows = numRecordCount + (int)numRows;

    SheetViews              sheetViews1             = new SheetViews            ();
    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" );

    if ( oPrinterSetup.psFreezeRows )
    {
        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         );

    if ( oPrinterSetup.psFreezeRows )
        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.
}

Answer 1

I fixed this by adding an empty WorkbookView to the Workbook object:

        workbookpart.Workbook.BookViews = new BookViews(new WorkbookView());

Excel must need a WorkbookView to generate the Print Preview.