The "AutoFilter" function in Excel Interop allows you to filter what data in the rows beneath a column display.
I need a similar function to allow the user to select which columns to hide. For example and specifically, if there is a set of columns 3..15 (C..0) going from "Sep 15" to "Sep 16" with the obvious months in between them ("Oct 15", etc.) I want the user to be able to select any subset of those columns to view. Those deselected would "disappear"
How can this be accomplished?
I have tried the following, but the second filter has no effect:
private void AddFilters()
{
Range column1Row6 = _xlSheet.Range[_xlSheet.Cells[6, 1], _xlSheet.Cells[6, 1]];
column1Row6.AutoFilter(1,
Type.Missing,
XlAutoFilterOperator.xlAnd,
Type.Missing,
true);
// Try to filter columns, too (not the contents, but which columns display)
Range columns3And4 = _xlSheet.Range[_xlSheet.Cells[6, 3], _xlSheet.Cells[6, 15]];
columns3And4.AutoFilter(1,
Type.Missing,
XlAutoFilterOperator.xlAnd,
Type.Missing,
true);
}
Is this possible?
UPDATE
Looking at some legacy code, it would seem that this code does it:
var fld = ((PivotField) pvt.PivotFields("Month"));
fld.Orientation = XlPivotFieldOrientation.xlColumnField;
fld.NumberFormat = "MMM yy";
Specifically, setting the orientation of the PivotField to xlColumnField causes the column to sport the sort/filter button that, when manipulated, conditionally shows/hides various columns. Does it determine which columns are showable/hideable based on the number format? That is to say, if the value is "Sep 15" or "Oct 16" it is a candidate for hiding/reshowing? I don't know, but I can't see anything else in the code that is more specifically setting the limits of the column filtering.
For a little more context:
var pch = _xlBook.PivotCaches();
pch.Add(XlPivotTableSourceType.xlDatabase, "PivotData!A1:H" + rowIdx).CreatePivotTable(_xlSheet.Cells[6, 1], "PivTab1", Type.Missing, Type.Missing);
var pvt = _xlSheet.PivotTables("PivTab1") as PivotTable;
if (pvt != null)
{
pvt.MergeLabels = true;
pvt.ErrorString = "";
pvt.DisplayErrorString = true;
var fld = ((PivotField) pvt.PivotFields("Description"));
fld.Orientation = XlPivotFieldOrientation.xlRowField;
fld = ((PivotField) pvt.PivotFields("Month"));
fld.Orientation = XlPivotFieldOrientation.xlColumnField;
fld.NumberFormat = "MMM yy";
. . .