Freeze Panes in OpenXml SDK 2.0 for Excel document

Question

I'm generating an Excel workbook using OpenXml and have been following the examples at http://msdn.microsoft.com/en-us/library/cc850837.aspx

It would be really useful if I could freeze the top panes, but I can't find a way to do this. I realise that I can do this if I use http://closedxml.codeplex.com/ but for now I'd like to stick to the OpenXml SDK

Any ideas?

Answer 1

I was trying to solve the same problem and ended up opening the Open XML SDK 2.0 Productivity Tool and using the Compare Files... feature to compare two spreadsheets, one with frozen panes and one without.

When I did that, I was led to code that looked basically like this:

WorkbookPart wbp = doc.WorkbookPart;
WorksheetPart wsp = wbp.WorksheetParts.First();

SheetViews sheetviews = wsp.Worksheet.GetFirstChild<SheetViews>();
SheetView sv = sheetviews.GetFirstChild<SheetView>();
Selection selection = sv.GetFirstChild<Selection>();
Pane pane = new Pane(){ VerticalSplit = 1D, TopLeftCell = "A2", ActivePane = PaneValues.BottomLeft, State = PaneStateValues.Frozen };
sv.InsertBefore(pane,selection);
selection.Pane = PaneValues.BottomLeft;

I added this to my program and it seemed to do the trick.

Answer 2

You can add the Selection as well:

WorkbookPart wbp = doc.WorkbookPart;
WorksheetPart wsp = wbp.WorksheetParts.First(); 

SheetViews sheetViews = wsp.Worksheet.GetFirstChild<SheetViews>();
SheetView sheetView = sheetViews.GetFirstChild<SheetView>();

Selection selection1 = new Selection() { Pane = PaneValues.BottomLeft };

Pane pane1 = new Pane() { VerticalSplit = 1D, TopLeftCell = "A2", ActivePane = PaneValues.BottomLeft, State = PaneStateValues.Frozen };

sheetView.Append(pane1);
sheetView.Append(selection1);