创建高级过滤器 [英] Create advanced filter

问题描述

我正在尝试使用C＃在Excel中创建一个高级过滤器，以将唯一数据从一张纸复制到另一张纸，至少在Excel中，如果我使用这样的Interop，则可以得到它：

I am trying to create an advanced filter in Excel from C# to copy unique data from one sheet to another, at least I get it in Excel and if I use Interop like this :

   Excel.Range rang = sheet2.get_Range("A2");  
   Excel.Range oRng = sheet.get_Range("I2", "I" + (lst.Count + 1));


    oRng.AdvancedFilter(Excel.XlFilterAction.xlFilterCopy, CriteriaRange: Type.Missing,
                        CopyToRange: rang, Unique: true);

工作正常，但我使用EPPlus来完成所有应用程序，如果可以，效果会更好

Works fine but I'm doing all my application with EPPlus and it will be better if I can do the same without Interop.

那么，有可能吗？

推荐答案

由于高级过滤器是一项excel函数，因此您需要完整的Excel DOM才能访问它。 Epplus没有它-它只是生成了XML以供excel，然后可以应用它的解释，可以这么说。

Since Advanced Filter is an excel function you need the full Excel DOM to access it. Epplus doesnt have that - it just generated the XML to feed to excel which will then apply its "interpretation", so to speak.

但是由于您具有的能力，所以。 NET，您可以通过查询单元格存储并使用 .distinct（） linq 来完成相同的操作$ c>获得唯一列表。唯一的麻烦是您必须创建自己的 IEquitableComparer 。会做到这一点：

But since you have the power of .NET at your disposal, you can use linq fairly easily to do the same thing by querying the cell store and using .distinct() to get the unique list. The only wrinkle is you have to create your own IEquitableComparer. This will do it:

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

    var rnd = new Random();
    for (var i = 0; i < 10; i++)
    {
        var row = datatable.NewRow();
        row[0] = rnd.Next(1, 3) ;row[1] = i%2 == 0 ? "even": "odd";
        datatable.Rows.Add(row);
    }

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

    using (var pck = new ExcelPackage(fi))
    {
        //Load the random data
        var workbook = pck.Workbook;
        var worksheet = workbook.Worksheets.Add("data");
        worksheet.Cells.LoadFromDataTable(datatable, true);

        //Cells only contains references to cells with actual data
        var rows = worksheet.Cells
            .GroupBy(cell => cell.Start.Row)
            .Skip(1) //Exclude header
            .Select(cg => cg.Select(c => c.Value).ToArray())
            .Distinct(new ArrayComparer())
            .ToArray();

        //Copy the data to the new sheet
        var worksheet2 = workbook.Worksheets.Add("Distinct");
        worksheet2.Cells.LoadFromArrays(rows);

        pck.Save();
    }

}


public class ArrayComparer: IEqualityComparer<object[]>
{
    public bool Equals(object[] x, object[] y)
    {
        return !x.Where((o, i) => !o.Equals(y[i])).Any();
    }

    public int GetHashCode(object[] obj)
    {
        //Based on Jon Skeet Stack Overflow Post
        unchecked
        {
            return obj.Aggregate((int) 2166136261, (acc, next) => acc*16777619 ^ next.GetHashCode());
        }
    }
}

这篇关于创建高级过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！