c# - Freezing columns in EPPlus (an Excel split function)

Question

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?

question from:https://stackoverflow.com/questions/18190918/freezing-columns-in-epplus-an-excel-split-function

Answer 1

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)