Monday, February 13, 2012

Freeze the top row


If you are using the Open XML SDK 2.0 to write Excel file you can freeze the top row so that the column header don’t move as the user scrolls down. This can be done with the snippet below. Kudos to this post for explaining how to do this. I verified that it works when using the SAX like writer called OpenXmlWriter. Not only does it work, but it DOES appear to load the entire Excel file into memory which defeats the reason for using OpenXmlWriter in the first place. I just called it after I closed the OpenXmlWriter. Unfortunately, this won’t work for me or anyone else that is writing giant Excel files.

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;

The good news is that this can be solved another way. First You could (I think) use the OpenXmlWriter to write the SheetViews tag, Select tag, and Pane tag. However, it is a bit of work and I was looking for an easy win. What I decided to do was to use an Excel file as a template which already had the first row frozen. There are some tricks to using a template, so check out here to learn more.  Then when I wrote my data to it I automatically had the first row frozen. No coding needed.

No comments: