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

c# excel vba office-interop export-to-excel
KBP picture KBP · Feb 20, 2011 · Viewed 77.2k times · Source

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;

Answer

KBP picture KBP · Feb 21, 2011

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

Related questions

Running an Excel Macro via C#: Run a macro from one workbook on another?

I am looking to run a macro, let's call it Macro01 from WorkSheet01 on WorkSheet02. Using Microsoft.Office.Interop.Excel Namespace I have opened a WorkSheet01. public void Main_CodedStep() { // Object for missing (or optional) arguments. object oMissing = System.Reflection.…

Read More
Changing an Excel cell's backcolor using hex results in Excel displaying completely different color in the spreadsheet

So I am setting an Excel cell's Interior Color to a certain value, like below: worksheet.Cells[1, 1].Interior.Color = 0xF1DCDB; However, when I then open up the spreadsheet in Excel, I see that the color that came out is …

Read More
Call Excel Add-In function in macro

I am developing Add-in for Excel 2013 and I have created a function in Excel Add-In as below public string ExcelReturnString() { return "This is the string: hi"; } I have used below code to call the function, but it throws an error. …

Read More