如何使用C＃冻结顶行并在Excel Automation中应用过滤器

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.

我有自动化从C＃创建Excel文档。我试图冻结我的工作表的顶行并应用过滤器。如果选择“视图”>“冻结窗格”>“冻结顶行”，然后选择顶行“数据”>“筛选”，则与Excel 2010中的相同。我不知道如何应用过滤器，但以下是我尝试冻结顶行，它只是冻结整个工作表。有没有人能解决我的问题。数据过滤问题是我需要更多帮助的地方，所以如果有人有解决方案，请赐教。

Many thanks, KBP

非常感谢KBP

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

5 个解决方案

#1

I figured it out!

我想到了！

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

@ Jaime解冻顶行的解决方案非常完美。以下是我应用过滤器的解决方案：

Thanks, KBP

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

#2

Try this...

尝试这个...

workSheet.Activate();
workSheet.Application.ActiveWindow.SplitRow = 1;
workSheet.Application.ActiveWindow.FreezePanes = true;

#3

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.

可能有一些System.Reflection.Missing.Value需要与参数一起传递，但这是我转换出来的VB.Net代码。

#4

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

//路径是excel文件保存字符串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);

#5

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 to some number say 90.. then freeze row is getting applied to 43.

以下解决方案工作正常，但它冻结了工作表当前可见快照的第一行。例如：如果您当前的工作表可见快照是从第43行到某个数字，例如90 ..那么冻结行将应用于43。

If you want only the very first row of sheet (heading row) to be frozen, no matter the excel scroll position, then 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.

如果你只希望冻结第一行工作表（标题行），无论excel滚动位置如何，那么下面的解决方案对我有效。此代码将Excel工作表向上滚动到第1行。如果要在冻结之前返回上一个位置，则必须存储该位置。

worksheet.Application.ActiveWindow.ScrollRow = 1;
worksheet.Application.ActiveWindow.SplitRow = 1;
worksheet.Application.ActiveWindow.FreezePanes = true;

#1