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 );
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart ();
workbookpart.Workbook = new Workbook ();
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart> ();
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" );
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 () );
Sheet sheet = new Sheet () { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart ( worksheetPart ), SheetId = 1, Name = "MySheet" };
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> ();
excelHeaderMethod ( spreadsheetDocument, sData, _headerColumns, excelHeader );
ForeachToExcel ( spreadsheetDocument, sData, _headerColumns, dataList, excelHeader );
definedName1.Text = "MySheet!" + oPrinterSetup.psPrintTitleRows;
definedNames1.Append ( definedName1 );
workbookpart.Workbook.Append ( definedNames1 );
workbookpart.Workbook.Save ();
spreadsheetDocument.Close ();
}
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.