Freeze panes in Excel using C# and EPPlus

Question

I want to freeze first 5 columns and three rows in excel. I have written the following code for that

Worksheets.View.FreezePanes(5, 5);

but it freezes columns in first 4 rows also. I want to freeze first 4 columns in excel except in first 4 rows. Is it possible to do?

Not sure I fully understand what you are trying to do. Are you saying you want to freeze from row 5 and down? So when you scroll right cells A1 thru D4 are not frozen? Is this something you can even do in Excel? — Ernie S, Jan 21 '15 at 13:35
Working Syntax is Worksheets.View.FreezePanes(r+1, c+1); where r -> no.of rows and c-> no. of columns to freeze. — Thamarai T, Sep 10 '19 at 07:11

score 64 · Accepted Answer · edited May 23 '17 at 11:33

64

The first value is for how many rows you want frozen, and the second is for how many columns you want frozen. Therefore, to freeze the first 3 rows and 5 columns you would need to call it as the following:

Worksheets.View.FreezePanes(3, 5);

You can also take a look at this SO question for more information on FreezePanes.

edited May 23 '17 at 11:33

Community

1
1

answered Jan 21 '15 at 15:06

Corey Adler

15,897
18
66
80

6

At the current time, freezing top row would somehow disable horizontal scroll. – Rozen Feb 15 '17 at 22:59
4

Actually, based upon the (correct) comment by @digEmAll in this answer https://stackoverflow.com/a/34432320/470014 that you are passing the location of the first non-frozen cell, you should actually use `Worksheet.View.FreezePanes(4, 6);` to answer the OP's question. – Caltor Jul 15 '19 at 14:56

score 45 · Answer 2 · answered Dec 23 '15 at 09:24

45

For me to freeze the first row following code worked. I am not sure what is logic there.

 worksheet.View.FreezePanes(2,1);

answered Dec 23 '15 at 09:24

CPW

503
5
7

2

YES. `sheet.View.FreezePanes(6, 1);` froze the first 5 rows for me. – CAD bloke Apr 07 '16 at 15:20
2

I'm assuming you have to add one to the number of frozen rows/columns because Excel works the same way. To freeze the first row in Excel, you have to select the first *two* rows (Don't ask me why) – Austin Adams May 27 '16 at 18:26
9

@AustinAdams: because (in excel as well as in epplus) you need to indicate the cell which represents the upper-left corner of the not-frozen area. Basically you're saying: "ok, this is the first upper-left cell of the scrollable area; all the cells above are considered columns headers, and all the cells on the left are considered rows headers" – digEmAll Aug 24 '16 at 13:41
@digEmAll wow, you're right! I just tried it, and in Excel, you can also freeze a row by selecting only the row below it, not both. I had no idea. Thanks for explaining – Austin Adams Aug 24 '16 at 14:17

vibs2006 · Answer 3 · 2022-04-20T16:30:44.070

16

From the ExcelWorksheet object, access the View property.

On the returned ExcelWorksheetView object, call the FreezePanes(row, column) method, passing the row and column of the first cell which is NOT frozen.

For example, to freeze the first complete two panes of Excel Worksheet, you would need to pass in the column (3,1) to the row parameter:

worksheetObject.View.FreezePanes(3, 1);

So to Freeze only first row completely you can now call worksheetObject.View.FreezePanes(2,1); only!

This is also mentioned in official Example of EPPlus.

Therefore to answer original question raised by @user2148124 the answer should be

worksheetObject.View.FreezePanes(3, 5);

edited Apr 20 '22 at 16:30

answered May 17 '17 at 10:06

vibs2006

6,028
3
40
40

1

And when entering FreezePanes(1,1) or one of the parameter to 0, Excel File will get Broken, telling us Excel that will try to repair it (and of course not getting Freeze to work). – Juan Ignacio Avendaño Huergo Apr 22 '19 at 12:20
Every open source plugin have some limitations or another. If you can suggest some better alternative then let me know – vibs2006 Apr 23 '19 at 03:48
I was just completing your information, which is very usefull, not criticizing the Framework. :D – Juan Ignacio Avendaño Huergo Apr 23 '19 at 07:18
worksheetObject.View.FreezePanes(2,1) worked for my when freezing first row :) – Techgeeks1 Mar 04 '20 at 12:35

score 2 · Answer 4 · answered Apr 06 '21 at 09:42

I know it's a long time since last post in the topic, but I was recently dealing with this problem and I found that way to get what I wanted (EPPlus v4.5.3):

public static void FreezeHeader(ExcelWorksheet sheet)
{
        var xdoc = sheet.WorksheetXml;

        var sheetViews = xdoc.GetElementsByTagName("sheetViews");
        var sheetViewNode = sheetViews[0].ChildNodes[0];

        var paneNode = xdoc.CreateNode(System.Xml.XmlNodeType.Element, "pane", xdoc.DocumentElement.NamespaceURI);

        var ySplit = xdoc.CreateAttribute("ySplit");
        ySplit.Value = "1";

        var topLeftCell = xdoc.CreateAttribute("topLeftCell");
        topLeftCell.Value = "A2";

        var activePane = xdoc.CreateAttribute("activePane");
        activePane.Value = "bottomLeft";

        var state = xdoc.CreateAttribute("state");
        state.Value = "frozen";

        paneNode.Attributes.Append(ySplit);
        paneNode.Attributes.Append(topLeftCell);
        paneNode.Attributes.Append(activePane);
        paneNode.Attributes.Append(state);

        sheetViewNode.AppendChild(paneNode);
}

I achieved this by comparing the xml of two Excel files (one with freezed header and another witouht).

Typically when creating a simple excel file, you get the following :

<sheetViews>
   <sheetView workbookViewId="0">
   </sheetView>
</sheetViews>

Now if you freeze the first row and examine the xml, you will see that :

<sheetViews>
<sheetView tabSelected="1" topLeftCell="Z1" zoomScale="85" zoomScaleNormal="85" workbookViewId="0">
    <pane ySplit="1" topLeftCell="A213" activePane="bottomLeft" state="frozen"/>
    <selection activeCell="O1" sqref="O1"/><selection pane="bottomLeft" activeCell="AD229" sqref="AD229"/>
</sheetView>

From that I deduced I had to add the "pane" node to the xml structue:

<pane ySplit="1" topLeftCell="A213" activePane="bottomLeft" state="frozen"/>

That's what the code I provide is doing :-)

score -5 · Answer 5 · answered Nov 02 '15 at 12:21

You can invoke sheet.FreezePanes(int rowIndex, int columnIndex) method to set freezing area.

Code Sample:

using System;
using Spire.Xls;
using System.Drawing;

namespace FreezePane
{
    class Program
    {
        static void Main(string[] args)
        {
            //Load File
            Workbook workbook = new Workbook();
            workbook.LoadFromFile
                (@"E:\Work\Documents\ExcelFiles\UserInfo.xlsx");
            Worksheet sheet = workbook.Worksheets[0];

            //Freeze Top Row
            sheet.FreezePanes(2, 1);

            //Save and Launch
            workbook.SaveToFile("FreezePane.xlsx", ExcelVersion.Version2010);
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

I think OP specifically asked about `EPPlus`, not `Spire.Xls`. — Scott Stafford, Nov 10 '15 at 14:39

Freeze panes in Excel using C# and EPPlus

5 Answers5