是否有“姐妹”功能为“自动过滤器”在Excel Interop中可用于列而不是行？ [英] Is there a "sister" function to "AutoFilter" in Excel Interop that works for columns instead of rows?

问题描述

Excel Interop中的AutoFilter功能允许您过滤列中显示的行中的哪些数据。

我需要一个类似的功能，选择要隐藏的列。例如，具体来说，如果从9月15日到9月16日之间有一组列3..15（C..0），它们之间有明显的月份（10月15日等）希望用户能够选择要查看的那些列的任何子集。那些被取消选择的人将会消失

如何实现？

我尝试过以下操作，但是第二个过滤器没有影响：

  private void AddFilters（）
 {
范围column1Row6 = _xlSheet.Range [_xlSheet.Cells [6，1]，_xlSheet.Cells [6，1]]; 
 column1Row6.AutoFilter（1，
 Type.Missing，
 XlAutoFilterOperator.xlAnd，
 Type.Missing，
 true）; 
 
 //尝试过滤列（不是内容，但哪些列显示）
范围columns3And4 = _xlSheet.Range [_xlSheet.Cells [6，3]，_xlSheet.Cells [ 6，15]]; 
 columns3And4.AutoFilter（1，
 Type.Missing，
 XlAutoFilterOperator.xlAnd，
 Type.Missing，
 true）; 
} 
  

可以吗？

更新

看一些遗留代码，似乎这段代码是这样的：

  var fld =（（PivotField）pvt.PivotFields（Month））; 
 fld.Orientation = XlPivotFieldOrientation.xlColumnField; 
 fld.NumberFormat =MMM yy; 
  

具体来说，将PivotField的方向设置为xlColumnField会使列运行排序/过滤器按钮当被操纵时，有条件地显示/隐藏各个列。是否根据数字格式确定哪些列是可显示/可隐藏的？也就是说，如果价值是9月15日或10月16日，它是隐藏/重新开始的候选人？我不知道，但是在代码中看不到任何其他更具体的设置列过滤的限制。

对于更多的上下文：

  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）作为数据透视表; 
 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; 
。 。 。 
  

解决方案

我不认为有这样的功能，类似的可以使用数据透视表实现。

我仍然有工作要做到这一切看起来不错，但我确实发现了如何创建一个列过滤器，基于答案

虽然你看不到它，有一列201509以及201510，并且可以单独或集体地过滤以显示。

现在以获取剩余的数据以显示它应该...

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";
    . . .

解决方案

I don't think there is such functionality, but similar can be achieved using PivotTables.

I still have work to do on getting it all to look decent, but I did find out how to create a column filter, based on an answer here.

With the following code:

var pch = _xlBook.PivotCaches();
// TODO: Make range dynamic
Range sourceData = _xlBook.Worksheets["PivotData"].Range["A1:G318"];

PivotCache pc = pch.Create(XlPivotTableSourceType.xlDatabase, sourceData);
PivotTable pvt = pc.CreatePivotTable(_xlPivotTableSheet.Range["A8"], "PivotTable");

pvt.PivotFields("Description").Orientation = XlPivotFieldOrientation.xlRowField;
pvt.PivotFields("MonthYr").Orientation = XlPivotFieldOrientation.xlColumnField;

...I can take the source data from one sheet ("PivotData") and slap it on another sheet (_xlPivotTableSheet). The part that gives me the "filter a set of columns" functionality is the last line above. That leaves me with the following:

Though you cannot see it, there is a column "201509" as well as the "201510", and they can be filtered to display or not, individually or collectively.

Now to get the rest of the data to display as it should...

这篇关于是否有“姐妹”功能为“自动过滤器”在Excel Interop中可用于列而不是行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！