c# - How can I programmatically create an Excel "sort/Filter combobox"?

Question

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?

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:

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

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:

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?

Answer 1

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