Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
282 views
in Technique[技术] by (71.8m points)

c# - 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:

enter image description here

How can I dynamically create such a control?

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

UPDATE

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:

enter image description here

How have I gone astray?

UPDATE 2

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

enter image description here

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.

UPDATE 3

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?

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

UPDATE 4

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:

enter image description here

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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(??)

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

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

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

UPDATE

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...