添加特定的自动筛选在列 [英] Adding a specific autofilter on a column

问题描述

我想设置一个列的筛选器。这是我做到了在互操作的方式：

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();
}

不过，我可以做自动筛选东西EPPlus的唯一方法就是启用或禁用它，而不是在一个特定的值进行过滤的柱。
事情是这样的：

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;

所以，我想在这里实现的是过滤了巨大的Excel在一个特定的值文件和然后只显示那些值的行。

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.

推荐答案

如果我理解你的要求，这不是EPPlus的意图。其目的是生成excel文件作为终点。你所要做的，似乎使用Excel作为分析工具，是真正

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.

如果你真的想过滤器应用于输出一列，然后那么你havetØ做手工，因为EPPlus本身不应用滤波器。因此，像这样（不得不面对这个自己）。但责任是对你（发电机）还是实际执行分析 - 在这种情况下，去年 LINQ 查询：

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();
    }
}

输出

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