39

I've been working a lot with EPPlus to generate Excel files for the number of exports that my project requires me to do. Most of the exports that they want tend to match up perfectly with exports that they have already in their legacy system. One of them, however, they want different. They want it to look exactly like one of the exports from the legacy system after they've done some typical and specific edits.

Some of the edits that they do, though, make each row a lot longer than they want it to be, so they want to keep some of the column information locked in place on the screen while the rest of the columns can be scrolled as normal (i.e. Excel's split function). I've tried locking the columns with ws.Column(6).Style.Locked = true, but that doesn't seem to work. I've also tried setting a cell range's Locked property to true but that also hasn't worked.

How can I freeze the columns in place?

Corey Adler
  • 15,897
  • 18
  • 66
  • 80

2 Answers2

67

It turns out that EPPlus has a built-in function for doing that on the Worksheet object itself called FreezePanes. This function has 2 parameters, both of which are int: Row and Column. Doing this will freeze whatever rows or columns you wish to have locked in place while viewing the worksheet.

One of the examples on the EPPlus website uses it, although it's not the main focus of the example/ That example can be found here.

There is one gotcha with this function that you should know about, though: The number that you use for the row or column parameter is actually the first column that is NOT frozen in place. In other words, if you want the first 5 columns to be frozen you would have to make the following call:

ws.View.FreezePanes(1,6) (Where 6 is the first column that is not frozen)

Corey Adler
  • 15,897
  • 18
  • 66
  • 80
  • IronMan84: I want to freeze first 5 columns in my excel header and i write the code below: xlWorkSheet.View.FreezePanes(1, 6); Instead of freezing 5 columns for first row only it is freezing 5 columns for all rows. Any idea how i can achieve my desired result ?? – Zeeshan Ajmal May 18 '15 at 06:08
  • IronMan84: Is there any way to specify range for freezing rows and columns instead of just giving starting indexes ? – Zeeshan Ajmal May 18 '15 at 06:17
  • Excel does not allow you to freeze ranges of cells, only columns or rows along top and left sides of the spreadsheet. However, you can use the Split function to divide the worksheet into sections which can be navigated using scrollbars. See http://classroom.synonym.com/freeze-selection-excel-11819.html for more details. – David Keaveny Apr 11 '16 at 03:23
  • @DavidKeaveny That's why I put the words (An Excel split function) in the title. I only called it Freeze because that was the function call in EPPlus. – Corey Adler Apr 13 '16 at 14:40
15

I realized that when using @IronMan84's suggestion, if you're only interested in the row but not the column. You can use

ws.View.FreezePanes(3, 1); // (Freeze Row 2 and no column)
Tonto
  • 2,900
  • 3
  • 25
  • 34
  • This is correct. Attempting to set it to zero causes Excel to report an error that the document is broken. Add 2 to however many rows and/or columns you want to freeze. – jocull Sep 27 '17 at 18:48
  • Sorry, that is wrong -- add **1** to the number of rows/columns! – jocull Sep 27 '17 at 19:23