Eric White's Blog

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