My current Task includes the programmatically generation of .xslx files with various functions like AutoFilter or displaying Grid lines on printing with Excel 2010.
However i fail to properly add the PrintOptions for Gridlines.
According to the MSDN Dokumentation, PrintOptions is a leaf of the Worksheet, yet the DocumentFormat.OpenXml.Spreadsheet.WorkSheet Namespace does not include a function to append the PrintOptions, and using the .Append() or .AppendChild() will result in a corrupted Spreadsheet.
Dim po = New PrintOptions With {.GridLines = True}
sheetPart.Worksheet.Append(po)
I also used the OpenXML Productivity Tool to compare my own created Spreadsheet vs the one from Excel 2010 and i noticed that the only difference between my Spreadsheet and Excels is that mine have a xml-namespace while the Excel ones does not.
May someone illuminate me what the correct way for inserting the PrintOptions into a Spreadsheet is? I spend days on those two lines now.
Apparently there is a order in which the elements have to be inserted.
If you have a PageSetup element in your Worksheet, this has to be appended AFTER the PrintOptions, otherwise you will get a corrupted Spreadsheet in Office 2010.
This is the correct way to insert if you want a spreadsheet in Landscape, FitToWidth and Gridlines :
Dim po = New PrintOptions With {.GridLines = True}
sheetPart.Worksheet.Append(po)
Dim ps = New PageSetup With {.Orientation = OrientationValues.Landscape}
Dim sp As New SheetProperties
sp.PageSetupProperties = New PageSetupProperties With {.FitToPage = True}
sheetPart.Worksheet.SheetProperties = sp
ps.FitToWidth = CUInt(1)
ps.FitToHeight = CUInt(0)
sheetPart.Worksheet.Append(ps)