49

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;
ashleedawg
  • 20,365
  • 9
  • 72
  • 105
KBP
  • 1,500
  • 2
  • 14
  • 14

5 Answers5

74

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);
Seetharama
  • 129
  • 14
KBP
  • 1,500
  • 2
  • 14
  • 14
  • 1
    I actually later found that firstRow.Activate(); and firstRow.Select(); are not needed for this operation – KBP Feb 20 '11 at 23:51
  • 1
    Great. Don't forghet to accept an answer. Even when it's your own ;-) That way it's more clear to other people when they come accross this question. – Koen Feb 21 '11 at 10:54
  • I was looking for a way to turn ON the autofilter programmatically and was NOT passing parameters to the AutoFilter() Method... Using the following worked. Thanks worksheet.UsedRange.AutoFilter(1, Type.Missing, Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true); – user2163049 Aug 23 '18 at 17:03
38

Try this...

workSheet.Activate();
workSheet.Application.ActiveWindow.SplitRow = 1;
workSheet.Application.ActiveWindow.FreezePanes = true;
Jaime Oro
  • 9,899
  • 8
  • 31
  • 39
  • That worked for the freeze top row, but I still have no Idea how to apply the filter. Thanks Jaime. – KBP Feb 20 '11 at 22:38
  • 1
    Don't know how to do it, sorry. Maybe recording a macro in Excel and doing it "by hand" would be helpful... – Jaime Oro Feb 20 '11 at 22:43
7
workSheet.EnableAutoFilter = true; 
workSheet.Cells.AutoFilter(1); 

//Set the header-row bold
workSheet.Range["A1", "A1"].EntireRow.Font.Bold = true;  

//Adjust all columns
workSheet.Columns.AutoFit();

There could be some System.Reflection.Missing.Value that need to be passed with the arguments, but this was VB.Net code I've converted out of my mind.

Koen
  • 2,501
  • 1
  • 32
  • 43
  • 1
    Your solution was exactly what I needed. Thank you. To add a filter to every column you could use the same syntax that you used to set the header text to bold: worksheet.Range["A1","A1"].EntireRow.AutoFilter(1); – Smitty-Werben-Jager-Manjenson May 24 '18 at 19:40
3

The below solutions are working fine, but it is freezing the first row of the current visible snapshot of the sheet. For ex: If your current sheet visible snapshot is from row 43.. then freeze row is getting applied to 43.

If you want only the very first row of sheet (heading row) to be frozen, no matter the excel scroll position the below solution worked for me. This code scrolls up the excel sheet to row 1. You have to store the position if you want to go back to the previous position before freeze.

worksheet.Application.ActiveWindow.ScrollRow = 1;
worksheet.Application.ActiveWindow.SplitRow = 1;
worksheet.Application.ActiveWindow.FreezePanes = true;
san
  • 304
  • 4
  • 20
  • 1
    ScrollRow hint was ultimately helpful. Otherwise even if you specify SplitRow as 1 it splits by the visible first row which is a great problem. Scrolling to position 1 and after splitting by 1 does the magic! Thanks mate! – Onur Omer Aug 12 '18 at 18:27
2

//path were excel file is kept string ResultsFilePath = @"C:\Users\krakhil\Desktop\FolderName\FileNameWithoutExtension";

        Excel.Application ExcelApp = new Excel.Application();
        Excel.Workbook ExcelWorkbook = ExcelApp.Workbooks.Open(ResultsFilePath);
        ExcelApp.Visible = true;

        //Looping through all available sheets
        foreach (Excel.Worksheet ExcelWorksheet in ExcelWorkbook.Sheets)
        {                
            //Selecting the worksheet where we want to perform action
            ExcelWorksheet.Select(Type.Missing);

            //Making sure first row is selected - else split and freeze will happen
            //On the visible part and not from the top
            Excel.Range activeCell = ExcelWorksheet.Cells[1, 1];
            activeCell.Select();

            //Applying auto filter to Row 10
            activeCell = (Excel.Range)ExcelWorksheet.Rows[10];
            activeCell.AutoFilter(1,
                Type.Missing,
                Excel.XlAutoFilterOperator.xlAnd,
                Type.Missing,
                true);

            //Split the pane and freeze it
            ExcelWorksheet.Application.ActiveWindow.SplitRow = 10;
            ExcelWorksheet.Application.ActiveWindow.FreezePanes = true;

            //Auto fit all columns
            ExcelWorksheet.Columns.AutoFit();

            //Releasing range object
            Marshal.FinalReleaseComObject(activeCell);
        }

        //saving excel file using Interop
        ExcelWorkbook.Save();

        //closing file and releasing resources
        ExcelWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
        Marshal.FinalReleaseComObject(ExcelWorkbook);
        ExcelApp.Quit();
        Marshal.FinalReleaseComObject(ExcelApp);
KR Akhil
  • 877
  • 3
  • 15
  • 32