How can I programmatically create an Excel “sort/F

问题:

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?

回答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

回答2:

No, you cannot "view source" on an Excel spreadsheet. However, you can open up the VBA editor and see what code has been written into it. In modern versions of Excel, how to do so might not be so apparent. Up at the top of the Excel window, look for the small arrow, click it, and a drop-down will appear.

Toward the bottom of the list, you will see an option titled "more commands". Click there and the following window opens.

You can then filter the list of commands. Choose "developer tab" and on the right side you will see a command titled "Visual Basic". You also have other commands for recording macros, so add the entire list. As you can see from my first screenshot, I have already added these on my copy of Excel.

Please be advised that you may also need to adjust your security settings.

Now that you know how to open the VBA editor, and you have the macro commands, you can view the code that created the filter, or you could create a filter yourself and record a macro to see how it is done. The resulting macro will be in VBA and you can modify it however you would like.

First click the Quick Access Toolbar icon for "record macro", and then add the filter by selecting a cell in the top row of table you want to filter, and then click the data tab; click filter.

After recording your macro, you should see something in the VBA editor like this:

Sub Macro1()
'
' Macro1 Macro
'

'
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$D$6").AutoFilter Field:=3, Criteria1:="1023123"
    Selection.AutoFilter
End Sub

Now that we have covered the whole "open source" issue, here is the solution for the interop issue. I have taken your code, changed it, and tested it to reproduce the error and have discovered what the problem is. The procedure must be selecting only a part of the table or a completely empty area of the spreadsheet. When Excel tries to find the corners of the range to be filtered, it runs into a problem because there are no identifiable corners to find. Take this for example:

    Excel.Application app = new Excel.Application();
    app.Visible = true;
    Excel.Workbook wk = app.Workbooks.Add();
    Excel.Worksheet sh = wk.Sheets[1];
    sh.Cells[1, 1] = "col1";
    sh.Cells[1, 2] = "col2";
    sh.Cells[1, 3] = "col3";
    sh.Cells[1, 4] = "col4";
    sh.Cells[2, 1] = "data";
    sh.Cells[2, 2] = "data";
    sh.Cells[2, 3] = "data";
    sh.Cells[2, 4] = "data";
    sh.Cells[3, 1] = "data2";
    sh.Cells[3, 2] = "data2";
    sh.Cells[3, 3] = "data2";
    sh.Cells[3, 4] = "data2";

    //Excel.Range r1 = sh.Range[sh.Cells[1, 1], sh.Cells[3, 4]]; //this works
    Excel.Range r2 = sh.Range[sh.Cells[7, 3], sh.Cells[7, 15]]; //this fails
    //object sortFilterCombobox1 = (object)r1.AutoFilter(1, System.Reflection.Missing.Value, Excel.XlAutoFilterOperator.xlAnd, System.Reflection.Missing.Value, true);
    object sortFilterCombobox2 = (object)r2.AutoFilter(1, System.Reflection.Missing.Value, Excel.XlAutoFilterOperator.xlAnd, System.Reflection.Missing.Value, true);

In the screenshot below, I have highlighted the row that the code tries to put a filter on and it is empty. So, what is going on here is that the code is for some reason not referencing the correct area of the spreadsheet you are working with.

One way you can fix this is to reference the 1st cell in the top row instead of the entire row. As long as there are no gaps in the table, then Excel will find the entire table.

So try applying AutoFilter on this range instead of the whole range:

_xlSheet.Cells[7, 3]

Also, I should point out in case you are not aware, but when you are assigning the "sortFilterCombobox" to a cell, you are actually popping a boolean value into the cell. The AutoFilter function returns a boolean, not the filter object itself.