Cannot add sheet views to worksheet

[John WickParticipant]

I am using Open XML to build up an Excel sheet and this work well so far. Now I am trying to freeze the first row and first column of all the sheets. From what i understood, i need to use a sheet view to my worksheets to be able to freeze panes. When i look up the worksheet, it does not contain a sheet view. and when i try to add it, my excel wont open; saying “we found a problem with some content in ‘filename.xlsx’. Do you want us to try to recover as much we can?…”. when i click on ‘yes’ i get these errors “Replaced Part: /xl/worksheets/sheet.xml part with XML error. Load error. Line 1, column 488.” for each sheet i have. Also, all the sheets get blank and still without the freeze panes.
This is the piece of code i added trying to add the sheetview:

Worksheet worksheet = new Worksheet();
worksheet.AddNamespaceDeclaration(“r”, “http://schemas.openxmlformats.org/officeDocument/2006/relationships”);

SheetDimension sheetDimension = new SheetDimension() { Reference = “A1:A3” };
SheetViews sheetViews = new SheetViews();
SheetView sheetView = new SheetView() { TabSelected = false, WorkbookViewId = 0U };

Selection selection = new Selection() { ActiveCell = “A1”, SequenceOfReferences = new ListValue<StringValue>() { InnerText = “A1” } };
sheetView.Append(selection);
sheetViews.Append(sheetView);
SheetFormatProperties sheetFormatProperties = new SheetFormatProperties() { DefaultRowHeight = 15D };
PageMargins pageMargins = new PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.7D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };

worksheet.Append(sheetDimension);
worksheet.Append(sheetViews);
worksheet.Append(sheetFormatProperties);
worksheet.Append(pageMargins);

worksheetPart.Worksheet = worksheet;

worksheetPart.Worksheet.Save();

Can you please help me with this issue?

[Jim SnyderParticipant]

What you are looking for is the Pane class. Vincent Tan describes it below:

Freezing is easier to understand than splitting For both freezing and splitting panes, you’ll be working with the Pane class. Specifically, you’ll be assigning values to the HorizontalSplit and VerticalSplit properties (it’s still these 2 properties for freezing, despite the name of the properties).
Now for freezing panes, you simply assign the number of left-most columns and top-most rows to the HorizontalSplit and VerticalSplit properties respectively. For example, if you want to freeze the first 3 columns, just assign 3 to HorizontalSplit. Splitting panes is a little more complicated. For splitting, the HorizontalSplit and VerticalSplit properties take on different values than with freezing. From the Open XML SDK documentation:
Horizontal position of the split, in twentieths of a point; 0 if none. If the pane is frozen, this value indicates the number of columns visible in the top pane.
and
Vertical position of the split, in twentieths of a point; 0 if none. If the pane is frozen, this value indicates the number of rows visible in the left pane.
From my investigations, it’s not a simple “find width/height in point units then multiply by 20”.

[John WickParticipant]

Hello Jim

I found that the pane class in found under the sheetview class, am I right?

Thing is, the sheetviews is always null by default and when I try to add a sheetviews to my worksheet, the excel document fails to open correctly (saying “we found a problem…..”).

are there specific ways to add sheetviews to worksheet?

Basically, Im facing the same issue as the posts below:

http://openxmldeveloper.org/discussions/development_tools/f/17/p/7739/163068.aspx

https://social.msdn.microsoft.com/Forums/office/en-US/e1c08add-b610-44c9-b60e-fa8ef6c24978/openxmlexcelc?forum=oxmlsdk

Do you have any idea what may be causing this?

[Jim SnyderParticipant]

I can offer debugging suggestions, but do not have Open XML loaded to be able to do what I will be telling you to do.

The Open XML SDK 2.5 has the ability to use reflection to expose the XML used in an Excel spreadsheet. Build the spreadsheet you are trying to build with Open XML and access it through the tool. What it will show is the proper order of the XML you are trying to create in Open XML. This would be the most likely problem causing the created Excel spreadsheet not to open. Below is a list from Vincent Tan’s book:

// OpenXML Sheet Object Attachment Order
<complexType name=”CT_Worksheet”>
<sequence>
<element name=”sheetPr”/>
<element name=”dimension”/>
<element name=”sheetViews”/>
<element name=”sheetFormatPr”/>
<element name=”cols”/>
<element name=”sheetData”/>
<element name=”sheetCalcPr”/>
<element name=”sheetProtection”/>
<element name=”protectedRanges”/>
<element name=”scenarios”/>
<element name=”autoFilter”/>
<element name=”sortState”/>
<element name=”dataConsolidate”/>
<element name=”customSheetViews”/>
<element name=”mergeCells”/>
<element name=”phoneticPr”/>
<element name=”conditionalFormatting”/>
<element name=”dataValidations”/>
<element name=”hyperlinks”/>
<element name=”printOptions”/>
<element name=”pageMargins”/>
<element name=”pageSetup”/>
<element name=”headerFooter”/>
<element name=”rowBreaks”/>
<element name=”colBreaks”/>
<element name=”customProperties”/>
<element name=”cellWatches”/>
<element name=”ignoredErrors”/>
<element name=”smartTags”/>
<element name=”drawing”/>
<element name=”legacyDrawing”/>
<element name=”legacyDrawingHF”/>
<element name=”picture”/>
<element name=”oleObjects”/>
<element name=”controls”/>
<element name=”webPublishItems”/>
<element name=”tableParts”/>
<element name=”extLst”/>
</sequence>
</complexType>

// OpenXML Workbook Object child xml order
FileSharing <x:fileSharing>
WorkbookProperties <x:workbookPr>
WorkbookProtection <x:workbookProtection>
BookViews <x:bookViews>
Sheets <x:sheets>
FunctionGroups <x:functionGroups>
ExternalReferences <x:externalReferences>
DefinedNames <x:definedNames>
CalculationProperties <x:calcPr>
OleSize <x:oleSize>
CustomWorkbookViews <x:customWorkbookViews>
PivotCaches <x:pivotCaches>
SmartTagProperties <x:smartTagPr>
SmartTagTypes <x:smartTagTypes>
WebPublishing <x:webPublishing>
FileRecoveryProperties <x:fileRecoveryPr>
WebPublishObjects <x:webPublishObjects>
WorkbookExtensionList <x:extLst>

It isn’t the only possibility. Others include not keeping table names unique, using sheet order instead of sheet id order, forgetting to attach sheets to sheetparts (etc) and others. I will do some research and try to find the order that sheetview fits into.

[Author]

Posts