KBP KBP - 3 months ago 10
C# Question

How to Freeze Top Row and Apply Filter in Excel Automation with C#

I have automation to create an Excel document from C#. I am trying to freeze the top row of my worksheet and apply filter. This is the same as in Excel 2010 if you select View > Freeze Panes > Freeze top row, and then after selecting top row Data > Filter. I do not have any idea how to apply the filter but the following is what I tried for freezing the top row and it just froze the entire worksheet. Does anyone have a solution to my problem. The data filter problem is where I need more help so if anyone has a solution to that please enlighten me.

Many thanks,
KBP

workSheet.Activate();
Excel.Range firstRow = (Excel.Range)workSheet.Rows[1];
firstRow.Activate();
firstRow.Select();
firstRow.Application.ActiveWindow.FreezePanes = true;

KBP KBP
Answer

I figured it out!

@Jaime's solution to freezing the top row worked perfectly. And the following is my solution to applying the filter:

Thanks, KBP

// Fix first row
workSheet.Activate();
workSheet.Application.ActiveWindow.SplitRow = 1;
workSheet.Application.ActiveWindow.FreezePanes = true;
// Now apply autofilter
Excel.Range firstRow = (Excel.Range)workSheet.Rows[1];
firstRow.AutoFilter(1, 
                    Type.Missing, 
                    Excel.XlAutoFilterOperator.xlAnd, 
                    Type.Missing, 
                    true);
Comments