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?

Steve · Accepted Answer · 2011-07-10T14:22:11.167

12

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.

edited Jul 10 '11 at 14:22

answered Jun 23 '11 at 02:14

Steve

543
1
6
14

2

I was using the code above and was generating an exception. Turns out that I had not made an actual selection in the spreadsheet I was using, so the selection object was a null reference. Selecting "A2" got me through. – Steve Jan 02 '12 at 07:23
2

how are you creating your worksheet? I am creating mine in memorystream and when I go to freeze a row, using the above code I get sheetviews null exception. – eaglei22 Sep 06 '17 at 14:24

score 7 · Answer 2 · answered Apr 12 '12 at 18:52

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);

Order of operations matter. You need to add the SheetViews to the Worksheet before adding the SheetData. — Garry English, Oct 27 '21 at 00:01

joeshmoe301 · Answer 3 · 2018-08-29T15:11:06.097

When I used the code provided in the other answers, I kept receiving a null error for the SheetViews. I used the SDK Productivity Tools to view the code for an excel document with a frozen pane, which helped me create the below code. Instead of using the GetFirstChild method, I had to create new instances of the SheetViews and SheetView classes and append them.

Here is the code.

WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();

WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();

SheetViews sheetViews = new SheetViews();
SheetView sheetView = new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U };
Pane pane = new Pane() { ActivePane = PaneValues.BottomLeft, State = PaneStateValues.Frozen, TopLeftCell = "A2", VerticalSplit = 1D };
Selection selection = new Selection() { Pane = PaneValues.BottomLeft };
sheetView.Append(pane);
sheetView.Append(selection);
sheetViews.Append(sheetView);
worksheetPart.Worksheet.Append(sheetViews);

One extra note is that when creating the SheetView, you must include the TabSelected and WorkbookViewId values, otherwise you will receive an error when opening the file about "We found a problem with some content in...."

Also, for anyone who wants to freeze the first column, instead of the first row, here is an example.

var sheetViews = new SheetViews();
var sheetView = new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U };
var pane = new Pane() { ActivePane = PaneValues.TopRight, HorizontalSplit = 1D, State = PaneStateValues.Frozen, TopLeftCell = "B1" };
var selection = new Selection() { Pane = PaneValues.TopRight };
sheetView.Append(pane);
sheetView.Append(selection);
sheetViews.Append(sheetView);

score 0 · Answer 4 · 2021-02-03T12:45:02.237

Feedback from 03/02/2021 :

You just have to add in your generation class the content of the Excel file, this :

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

This line allows indeed, to freeze all the shutters of the upper line...

Code of a spreadsheet without the fixed shutters :

Code of a spreadsheet with fixed shutters :

I share this feedback because I also searched for a while before finding out how to do it.

score 0 · Answer 5 · edited Dec 07 '22 at 13:39

MemoryStream documentStream = new ();
    SpreadsheetDocument document = SpreadsheetDocument.Create(documentStream, SpreadsheetDocumentType.Workbook);
    WorkbookPart workbookPart = document.AddWorkbookPart();
    workbookPart.Workbook = new Workbook();

    WorkbookStylesPart stylePart = workbookPart.AddNewPart<WorkbookStylesPart>();
    stylePart.Stylesheet = new ScrapBuyplanStyleSheet().GenerateStyleSheet();
    stylePart.Stylesheet.Save();
    
    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
    worksheetPart.Worksheet = new Worksheet();
    
    // Freeze Panes
    SheetViews sheetViews = new ();
    SheetView sheetView = new () {TabSelected = true, WorkbookViewId = (UInt32Value) 0U};
    Pane pane = new ()
    {
        ActivePane = PaneValues.TopRight,
        State = PaneStateValues.Frozen, 
        TopLeftCell = "B1", 
        //VerticalSplit = 1D,
        HorizontalSplit = 1D
    };
    Selection selection = new () {Pane = PaneValues.TopRight};
    sheetView.Append(pane);
    sheetView.Append(selection);
    sheetViews.Append(sheetView);
    worksheetPart.Worksheet.Append(sheetViews);

Solution to null pointer when creating pane is create the worksheet before adding the pane

Freeze Panes in OpenXml SDK 2.0 for Excel document

5 Answers5

Linked