Print problem with frozen rows

Home Forums Open-Xml-Sdk Print problem with frozen rows

This topic contains 3 replies, has 2 voices, and was last updated by  Eric White 8 years, 6 months ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #3411

    pgscannell
    Participant

    I’ve gotten the “Freeze” rows process to work. I have 4 rows of data to stay put while you scroll through the file. I also have rows 1-4 set to repeat on each page if in print preview.

    The problem is, when I open the Excel file and choose Print, I get a “Microsoft Excel has stopped working” popup error. So I have to “close the program”.

    If I reopen the file and unfreeze the rows, the print works.

    If i then re-freeze the rows while in Excel, the print still works.

    So that suggests that there is a problem in my code that creates the Excel file. I don’t know where it is. Here is the code that sets up the file. Maybe someone knows what I did wrong:

    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 ();
    SheetData sheetData1 = new SheetData ();
    Columns columns1 = new Columns ();

    //
    // Freeze at row x…
    //
    string strFreezeRow = “”;
    double numRows = 0.00;

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

    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 };

    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 ( sheetViews1 );
    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:L2” };

    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.

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

    Thanks in advance,
    Paul

    #3479

    Eric White
    Keymaster

    Hi,

    I have recorded a screen-cast that explains how I debug these types of problems:

    Screen-Cast: Debugging Open XML when the Office Client Crashes

    Please let me know how you get on.

    Cheers, Eric

    #3483

    pgscannell
    Participant

    Do you know if there is a version of “Package Editor Power Tool” for VS 2015? I liked how you diagnosed the issue in the screen-cast but I need the power tool app to do it with mine.

    Thanks,
    Paul

    #3484

    Eric White
    Keymaster

    The Open XML Package Editor Power Tool source code is available on GitHub:

    Open XML Package Editor Power Tool on GitHub

    There is a binary version, but apparently it does not work with 2015.

    Download: Binary version of Open XML Package Editor Power Tool that does not work with 2015.

    Alternatively, another great tool is the OOXML Tools Extension for Chrome. You can take the same approach using this tool.

    In the following screen-cast series, watch the OOXML Tools Extension for Chrome screen-cast:

    Introduction to Open XML

    I recommend watching the other screen-casts in this series as well.

    Cheers, Eric

    • This reply was modified 8 years, 6 months ago by  Eric White.
Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.