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?

Scorify

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.

Bott

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. – Bott

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. – Amiens

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

Cirrate

Order of operations matter. You need to add the SheetViews to the Worksheet before adding the SheetData. – Corsetti

Answer 3

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

Childbearing

Answer 4

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.

Tavares

Answer 5

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

Lovely

Answer 6

Large Data OpenDocument Writer Example

After Much Trial and Error, and using this Example I found the SheetViews Goes inside the Worksheet at the top. In My case to write large amounts of data I use a streaming and writer approach. I place this here because it's where Google landed me for others.

Freeze First Column Example

            using (var workbook = SpreadsheetDocument.Create(uniqueFilePath, SpreadsheetDocumentType.Workbook))
            {
                List<OpenXmlAttribute> attributeList;
                OpenXmlWriter writer;
                workbook.AddWorkbookPart();
                var workSheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
                writer = OpenXmlWriter.Create(workSheetPart);
                /* <Worksheet> */
                writer.WriteStartElement(new Worksheet());

                /*<SheetViews>*/
                writer.WriteStartElement(new SheetViews());

                var tabSelectedAtt    = new OpenXmlAttribute("tabSelected", null, 1.ToString());
                var workBookViewIdAtt = new OpenXmlAttribute("workbookViewId", null, 0.ToString());

                writer.WriteStartElement(new SheetView(), new List <OpenXmlAttribute>()
                {
                    tabSelectedAtt, workBookViewIdAtt
                });

                var xSplitAtt      = new OpenXmlAttribute("xSplit", null, 1.ToString());
                var topLeftCellAtt = new OpenXmlAttribute("topLeftCell", null, "B1");
                var activePane     = new OpenXmlAttribute("activePane", null, "topRight");
                var state          = new OpenXmlAttribute("state", null, "frozen");

                writer.WriteStartElement(new Pane(), new List <OpenXmlAttribute>()
                {
                    xSplitAtt, topLeftCellAtt, activePane, state
                });
                writer.WriteEndElement();
                writer.WriteEndElement();
                writer.WriteEndElement();
                /*</SheetViews>*/


                /* <Columns> */
                writer.WriteStartElement(new Columns());
                int columnsCounter = 1;
                var columnData = reader.GetColumnSchema();
                foreach (var c in columnData) {
                    /* <Column> */
                    attributeList = new List<OpenXmlAttribute>();
                    attributeList.Add(new OpenXmlAttribute("min", null, columnsCounter.ToString()));
                    attributeList.Add(new OpenXmlAttribute("max", null, columnsCounter.ToString()));
                    attributeList.Add(new OpenXmlAttribute("width", null, ((150 /*Width*/ / 6) + 2).ToString())); //attributeList.Add(new OpenXmlAttribute("width", null, ((col.Width / 6) + 2).ToString()));                            
                    writer.WriteStartElement(new DocumentFormat.OpenXml.Spreadsheet.Column(), attributeList);
                    writer.WriteEndElement();                            
                    columnsCounter++;
                    /* </Column> */
                }
                writer.WriteEndElement();
                /* </Columns> */

                /* <SheetData> */
                writer.WriteStartElement(new SheetData());

                //...

Freeze First Row Example

using (var workbook = SpreadsheetDocument.Create(uniqueFilePath, SpreadsheetDocumentType.Workbook)) { List attributeList; OpenXmlWriter writer; workbook.AddWorkbookPart(); var workSheetPart = workbook.WorkbookPart.AddNewPart(); writer = OpenXmlWriter.Create(workSheetPart); /* */ writer.WriteStartElement(new Worksheet());

/*<SheetViews>*/
writer.WriteStartElement(new SheetViews());

var tabSelectedAtt    = new OpenXmlAttribute("tabSelected", null, 1.ToString());
var workBookViewIdAtt = new OpenXmlAttribute("workbookViewId", null, 0.ToString());

writer.WriteStartElement(new SheetView(), new List <OpenXmlAttribute>()
{
    tabSelectedAtt, workBookViewIdAtt
});

var xSplitAtt      = new OpenXmlAttribute("ySplit", null, 1.ToString());
var topLeftCellAtt = new OpenXmlAttribute("topLeftCell", null, "A2");
var activePane     = new OpenXmlAttribute("activePane", null, "bottomLeft");
var state          = new OpenXmlAttribute("state", null, "frozen");



writer.WriteStartElement(new Pane(), new List <OpenXmlAttribute>()
{
    xSplitAtt, topLeftCellAtt, activePane, state
});
writer.WriteEndElement();
writer.WriteEndElement();
writer.WriteEndElement();

/*</SheetViews>*/


/* <Columns> */
writer.WriteStartElement(new Columns());
int columnsCounter = 1;
var columnData = reader.GetColumnSchema();
foreach (var c in columnData) {
    /* <Column> */
    attributeList = new List<OpenXmlAttribute>();
    attributeList.Add(new OpenXmlAttribute("min", null, columnsCounter.ToString()));
    attributeList.Add(new OpenXmlAttribute("max", null, columnsCounter.ToString()));
    attributeList.Add(new OpenXmlAttribute("width", null, ((150 /*Width*/ / 6) + 2).ToString())); //attributeList.Add(new OpenXmlAttribute("width", null, ((col.Width / 6) + 2).ToString()));                            
    writer.WriteStartElement(new DocumentFormat.OpenXml.Spreadsheet.Column(), attributeList);
    writer.WriteEndElement();                            
    columnsCounter++;
    /* </Column> */
}
writer.WriteEndElement();
/* </Columns> */

/* <SheetData> */
writer.WriteStartElement(new SheetData());

Exclamatory

Answer 7

My case: I needed more frozen rows. Let me explain below two things.

A8 is where I froze panel, so VerticalSplit = 7 is the amount of lines "above" that are shown. If I place VerticalSplit = 4 then lines 1-4 are shown and lines 5-7 are hidden. If I place VerticalSplit > 7 then throws error.

ActiveCell = "A8" is what cell will be selected when user first open this generated file.

Regarding PaneValues I didn't pay too much attention to it.

var pane = new Pane
{
    VerticalSplit = 7,
    TopLeftCell = "A8",
    ActivePane = PaneValues.BottomLeft,
    State = PaneStateValues.Frozen,
};
sheetView.Append(pane);

var selection = new Selection
{
    Pane = PaneValues.BottomLeft,
    ActiveCell = "A8",
    SequenceOfReferences = new ListValue<StringValue> { InnerText = "A8" }
};
sheetView.Append(selection);

Poler