在列上添加特定的自动过滤器 [英] Adding a specific autofilter on a column

问题描述

我试图在列上设置一个过滤器。这是我在Interop中的方式：

  private void CheckMasterFile（string path）{
 var xlApp = new Excel.Application（）; 
 var xlWorkbook = xlApp.Workbooks.Open（path）; 
 Excel._Worksheet xlWorksheet = xlWorkbook.Sheets [1]; 
 
 foreach（项目中的var项目）{
 if（string.IsNullOrEmpty（project.ProjectID.Value））{
 continue; 
} 
 
 var xlRange = xlWorksheet.UsedRange; 
 if（xlWorksheet.AutoFilter！= null）{
 xlWorksheet.AutoFilterMode = false; 
} 
 xlRange.AutoFilter（Field：2，Criteria1：project.ProjectID.Value）; 
 var result = xlRange.SpecialCells（Excel.XlCellType.xlCellTypeVisible，Type.Missing）; //仅显示过滤值
 double sum = 0; 
 
 foreach（result.Rows中的Excel.Range行）{
 if（row.Cells [2，2] .Value2（）！= null）{
 if（！ NOT_ALLOWED_RUBRIQUES.Contains（（string）row.Cells [2,8] .Value2（）））{//检查是否允许rubrique 
 //完成方法
} 
} 
} 
} 
 xlWorkbook.Close（SaveChanges：false）; 
 xlApp.Quit（）; 
} 
  

但是，我可以在EPPlus中使用自动过滤器进行某些操作的唯一方法是启用或禁用它，不要过滤特定值的列。
这样的东西：

  sheet.Cells [A RANGE HERE]。AutoFilter = true; 
  

所以我想要实现的是将一个巨大的Excel文件过滤到一个特定的值上，那么只显示这些值的行。

解决方案

如果我明白你在问什么，那不是EPPlus的意图。这意味着生成excel文件作为端点。您尝试做的似乎实际上是使用Excel作为分析工具。

如果您真的要将一个过滤器应用于列的输出，那么您会吸收o手动执行，因为EPPlus不本地应用过滤器。所以这样的事情（不得不自己处理）。但是，在这种情况下，您的（发生器）仍然在您的实际执行分析 - 的最后一个 linq 查询：

  [TestMethod] 
 public void AutoFilter_Test（）
 {
 //http://stackoverflow.com/questions/32723483/adding-a -specific-autofilter-on-a-column 
 
 //抛出一些数据
 var datatable = new DataTable（tblData）; 
 datatable.Columns.AddRange（new [] {new DataColumn（Col1，typeof（int）），新的DataColumn（Col2，typeof（int）），新的DataColumn（Col3，typeof ））}）; （var i = 0; i< 10; i ++）
 
 $ b {
 var row = datatable.NewRow（）; row [0] = i; row [1] = i * 10; row [2] = Path.GetRandomFileName（）; 
 datatable.Rows.Add（row）; 
} 
 
 //创建一个测试文件
 var fi = new FileInfo（@c：\temp\autofilter.xlsx）; 
 if（fi.Exists）
 fi.Delete（）; 
 
使用（var pck = new ExcelPackage（fi））
 {
 var worksheet = pck.Workbook.Worksheets.Add（Sheet1）; 
 worksheet.Cells.LoadFromDataTable（datatable，true）; 
 
 var range = worksheet.Cells [A1：C10]; 
 range.AutoFilter = true; 
 
 pck.Save（）; 
} 
 
 //需要事先保存才能生成XML 
使用（var pck = new ExcelPackage（fi））
 {
 var worksheet = pck.Workbook.Worksheets.First（）; 
 
 //获取工作表的引用xml为正确的命名空间
 var xdoc = worksheet.WorksheetXml; 
 var nsm = new XmlNamespaceManager（xdoc.NameTable）; 
 nsm.AddNamespace（default，xdoc.DocumentElement.NamespaceURI）; 
 
 //创建过滤器本身
 var filter1 = xdoc.CreateNode（XmlNodeType.Element，filter，xdoc.DocumentElement.NamespaceURI）; 
 var att = xdoc.CreateAttribute（val）; 
 att.Value =40; 
 filter1.Attributes.Append（att）; 
 
 var filter2 = xdoc.CreateNode（XmlNodeType.Element，filter，xdoc.DocumentElement.NamespaceURI）; 
 att = xdoc.CreateAttribute（val）; 
 att.Value =50; 
 filter2.Attributes.Append（att）; 
 
 //向集合添加过滤器
 var filters = xdoc.CreateNode（XmlNodeType.Element，filters，xdoc.DocumentElement.NamespaceURI）; 
 filters.AppendChild（filter1）; 
 filters.AppendChild（filter2）; 
 
 //创建父筛选器容器
 var filterColumn = xdoc.CreateNode（XmlNodeType.Element，filterColumn，xdoc.DocumentElement.NamespaceURI）; 
 att = xdoc.CreateAttribute（colId）; 
 att.Value =1; 
 filterColumn.Attributes.Append（att）; 
 filterColumn.AppendChild（filters）; 
 
 //现在将其添加到autoFilters节点
 var autoFilter = xdoc.SelectSingleNode（/ default：worksheet / default：autoFilter，nsm）; 
 autoFilter.AppendChild（filterColumn）; 
 
 //必须根据条件手动隐藏行
 worksheet.Cells 
 .Where（cell => 
 cell.Address.StartsWith（B） 
&& cell.Value是double 
&&（double）cell.Value！= 40d 
&&（（double）cell.Value！= 50d）
 .Select（cell => cell.Start.Row）
 .ToList（）
 .ForEach（r => worksheet.Row（r）.Hidden = true）; 
 
 pck.Save（）; 
} 
} 
  

OUTPUT

I'm trying to set a filter on a column. This is the way I did it in Interop:

private void CheckMasterFile(string path) {
    var xlApp = new Excel.Application();
    var xlWorkbook = xlApp.Workbooks.Open(path);
    Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];

    foreach (var project in projects) {
        if (string.IsNullOrEmpty(project.ProjectID.Value)) {
            continue;
        }

        var xlRange = xlWorksheet.UsedRange;
        if (xlWorksheet.AutoFilter != null) {
            xlWorksheet.AutoFilterMode = false;
        }
        xlRange.AutoFilter(Field: 2, Criteria1: project.ProjectID.Value);
        var result = xlRange.SpecialCells(Excel.XlCellType.xlCellTypeVisible, Type.Missing);//only shows filtered values
        double sum = 0;

        foreach (Excel.Range row in result.Rows) {
            if (row.Cells[2, 2].Value2() != null) {
                if (!NOT_ALLOWED_RUBRIQUES.Contains((string)row.Cells[2, 8].Value2())) {//check if rubrique is allowed or not
                    //finish method
                }
            }
        }
    }
    xlWorkbook.Close(SaveChanges: false);
    xlApp.Quit();
}

But the only way I can do something with autofilter in EPPlus is to enable or disable it, not to filter a column on a specific value. Something like this:

sheet.Cells["A RANGE HERE"].AutoFilter = true;

So what I'm trying to achieve here is to filter a huge Excel file on a specific value and then only show the rows with those values.

解决方案

If I understand what you are asking, that is not the intent of EPPlus. It is meant to generate the excel file as the endpoint. What you are trying to do seems to be actually using Excel as your analysis tool.

If you truly want to apply a filter to a column for output then then you havet o do it manually since EPPlus does not natively apply filters. So something like this (had to deal with this myself). But the onus is still on you (the generator) to actually perform the analysis - that last linq query in this case:

[TestMethod]
public void AutoFilter_Test()
{
    //http://stackoverflow.com/questions/32723483/adding-a-specific-autofilter-on-a-column

    //Throw in some data
    var datatable = new DataTable("tblData");
    datatable.Columns.AddRange(new[] { new DataColumn("Col1", typeof(int)), new DataColumn("Col2", typeof(int)), new DataColumn("Col3", typeof(object)) });

    for (var i = 0; i < 10; i++)
    {
        var row = datatable.NewRow(); row[0] = i; row[1] = i * 10;row[2] = Path.GetRandomFileName();
        datatable.Rows.Add(row);
    }

    //Create a test file
    var fi = new FileInfo(@"c:\temp\autofilter.xlsx");
    if (fi.Exists)
        fi.Delete();

    using (var pck = new ExcelPackage(fi))
    {
        var worksheet = pck.Workbook.Worksheets.Add("Sheet1");
        worksheet.Cells.LoadFromDataTable(datatable, true);

        var range = worksheet.Cells["A1:C10"];
        range.AutoFilter = true;

        pck.Save();
    }

    //Needed prior save in order for the XML to be generated
    using (var pck = new ExcelPackage(fi))
    {
        var worksheet = pck.Workbook.Worksheets.First();

        //Get reference to the worksheet xml for proper namespace
        var xdoc = worksheet.WorksheetXml;
        var nsm = new XmlNamespaceManager(xdoc.NameTable);
        nsm.AddNamespace("default", xdoc.DocumentElement.NamespaceURI);

        //Create the filters themselves
        var filter1 = xdoc.CreateNode(XmlNodeType.Element, "filter", xdoc.DocumentElement.NamespaceURI);
        var att = xdoc.CreateAttribute("val");
        att.Value = "40";
        filter1.Attributes.Append(att);

        var filter2 = xdoc.CreateNode(XmlNodeType.Element, "filter", xdoc.DocumentElement.NamespaceURI);
        att = xdoc.CreateAttribute("val");
        att.Value = "50";
        filter2.Attributes.Append(att);

        //Add filters to the collection
        var filters = xdoc.CreateNode(XmlNodeType.Element, "filters", xdoc.DocumentElement.NamespaceURI);
        filters.AppendChild(filter1);
        filters.AppendChild(filter2);

        //Create the parent filter container
        var filterColumn = xdoc.CreateNode(XmlNodeType.Element, "filterColumn", xdoc.DocumentElement.NamespaceURI);
        att = xdoc.CreateAttribute("colId");
        att.Value = "1";
        filterColumn.Attributes.Append(att);
        filterColumn.AppendChild(filters);

        //Now add it to the autoFilters node
        var autoFilter = xdoc.SelectSingleNode("/default:worksheet/default:autoFilter", nsm);
        autoFilter.AppendChild(filterColumn);

        //Have to manually hide rows based on criteria
        worksheet.Cells
            .Where(cell =>
                cell.Address.StartsWith("B") 
                && cell.Value is double 
                && (double) cell.Value != 40d 
                && (double) cell.Value != 50d)
            .Select(cell => cell.Start.Row)
            .ToList()
            .ForEach(r => worksheet.Row(r).Hidden = true);

        pck.Save();
    }
}

OUTPUT

这篇关于在列上添加特定的自动过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！