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?

Favor

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? – Songwriter

Working Syntax is Worksheets.View.FreezePanes(r+1, c+1); where r -> no.of rows and c-> no. of columns to freeze. – Maltese

Answer 1

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.

Activism

At the current time, freezing top row would somehow disable horizontal scroll. – Wilke

Actually, based upon the (correct) comment by @digEmAll in this answer https://mcmap.net/q/366475/-freeze-panes-in-excel-using-c-and-epplus 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. – Commemoration

Answer 2

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

 worksheet.View.FreezePanes(2,1);

Pennyroyal

YES. sheet.View.FreezePanes(6, 1); froze the first 5 rows for me. – Qualm

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) – Mother

@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" – Orthogenic

@Orthogenic 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 – Mother

Answer 3

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

Insurance

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). – Lingonberry

Every open source plugin have some limitations or another. If you can suggest some better alternative then let me know – Insurance

I was just completing your information, which is very usefull, not criticizing the Framework. :D – Lingonberry

worksheetObject.View.FreezePanes(2,1) worked for my when freezing first row :) – Recriminate

Answer 4

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

Fillender

Answer 5

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

Ufo

I think OP specifically asked about EPPlus, not Spire.Xls. – Convulsion