如何以编程方式创建Excel“排序/筛选组合框"? [英] How can I programmatically create an Excel "sort/Filter combobox"?

问题描述

我正在反向工程手动创建的电子表格以进行动态创建.大多数单元格都填充有简单数据，但是有一些排序/过滤器"下拉列表，如下所示:

I'm reverse-engineering a manually created spreadsheet for dynamic creation. Most of the cells are populated with simple data, but there are a couple that are "sort/Filter" dropdowns like so:

如何动态创建这样的控件?

How can I dynamically create such a control?

是否可以在Excel电子表格中查看源代码"，以查看生成这些控件可能需要哪种代码?

Is there a way to "view source" in the Excel spreadsheet to see what sort of code might be required to produce these controls?

适应MacroMark的代码，编译如下:

Adapting MacroMark's code, this compiles:

var rangeMonthYears = _xlSheet.Range[_xlSheet.Cells[7, 3], _xlSheet.Cells[7, 15]];
        object sortFilterCombobox = (object)rangeMonthYears.AutoFilter(1, System.Reflection.Missing.Value, XlAutoFilterOperator.xlAnd, System.Reflection.Missing.Value, true);
        _xlSheet.Cells["6", "C"] = sortFilterCombobox; //MonthLabel; 

...但是它崩溃了，向我展示了这个令人振奋的音符:

...but it crashes, presenting me with this upbraiding note:

我怎么误入歧途了?

要在下面的评论中回答MacroMarc，以下是旧版/模型电子表格中的过滤器控件的屏幕截图(我正在对其进行反向工程):

To answer MacroMarc in his comment below, here is a screen shot of the filter control in the legacy/model spreadsheet (which I'm reverse-engineering):

在这种情况下，我从列表中取消选择"11月"，因此您将其删除了.因此，用户选择的内容会影响下面各列的可见性.

In this case, I deselected "November" from the list, so that it was removed, as you can see. So what the user selects affects the visibility of the columns below.

所有这些花哨的内容都融入了旧的电子表格中，我现在正在考虑将其保存为模板，并根据需要简单地替换单元格内容.有什么理由不可行吗?

With all this fancy-pantsiness baked into the legacy spreadsheet, I'm now considering saving it as a template and simply replacing the cell contents as needed. Is there any reason why this would not be viable?

如果没有，要实现此目的，我是否应该保存现有的电子表格作为启用Excel宏的工作簿"?

If not, to accomplish this, should I save the existing spreadhseet "As Excel Macro-Enabled Workbook"?

我试图像这样修改MacroMarc的答案:

I tried adapting MacroMarc's answer like so:

Range monthYearCells = _xlSheet.Range[_xlSheet.Cells[COLUMN_HEADING_ROW, MONTH1_COL], _xlSheet.Cells[COLUMN_HEADING_ROW, MONTH13_COL]];
object monthFilter = (object)monthYearCells.AutoFilter(1, System.Reflection.Missing.Value, XlAutoFilterOperator.xlAnd, System.Reflection.Missing.Value, true);
var monthFilterCell = (Range)_xlSheet.Cells[6, 3];
monthFilterCell.Value = monthFilter;

...但是遇到了运行时异常:

...but got the runtime exception:

是引起问题的最后一行(将monthFilter分配给范围的Value属性)吗?如果是这样，我应该为monthFilter分配什么 ，或者该怎么做?

Is it the last line (assigning monthFilter to the range's Value property) that's causing the problem? If so, what should I assign monthFilter to, or what should I do with it?

推荐答案

筛选器是通过Excel表中的范围或Excel界面上的筛选器"按钮本地创建的.正如Joshua所说，您可以尝试在VBA中编写脚本，但是从您的问题标签看来，您正在使用C#和Excel-Interop(??)

Filters are created natively by the range being in an Excel table, or maybe by Filter button on Excel interface. As Joshua said you can try and script this in VBA, but it seems by your question tags that you are using C# and Excel-Interop(??)

尝试在C#代码中使用范围对象句柄并应用.Autofilter方法.

Try using the range object handle in your C# code and applying .Autofilter method.

object result = (object)oRange.AutoFilter(1, System.Reflection.Missing.Value,ExApp.XlAutoFilterOperator.xlAnd,System.Reflection.Missing.Value, true);

ExApp上方是Interop.Excel命名空间的别名.

where above ExApp is my alias for the Interop.Excel namespace.

关于开发人员"标签，请在Google上为您的Excel版本查看自定义功能区"选项.右键单击功能区，或者File-> Options--etc

As for Developer tab, look at the Customize Ribbon option on google for your Excel version. Rightclick ribbon maybe, or File-->Options--etc

更新

好吧，要修改数据透视字段的排序和过滤器属性，您需要抓住数据透视字段的句柄，例如，如果月"过滤器位于单元格D5中，则可以执行以下操作:

Ok so to amend the sort and filter properties of a pivotfield, you need to grab the handle to the pivotField, e.g if your 'Month' Filter was in cell D5, you could do something like:

Range oRange = oSheet.get_Range("D5", "D5");
PivotField pf = oRange.PivotField;
pf.AutoSort((int)XlSortOrder.xlDescending, "Month");   //this sorts in reverse order            
pf.PivotItems(2).Visible = false;   //this makes the second item deselected in filter

这篇关于如何以编程方式创建Excel“排序/筛选组合框"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！