ClosedXML Excel按多列中的值过滤行 [英] ClosedXML Excel filter rows by values in multiple columns

问题描述

我有一个Excel文件，其中包含数千行和不超过"BP"的列. 我需要通过C和BP列中的特定值过滤所有这些行.

I have a Excel file with several thousand rows and columns up to "BP". I need to filter all of these rows by specific values in columns C and BP.

我按照下面的代码在ClosedXML中测试了过滤器功能.

I tested the filter functionality in ClosedXML as per the code below.

当我将过滤器应用于一列时，一切都很好，并且数据保存在新文件中.

When I apply a filter to one column all works well and the data is saved in the new file.

当我尝试应用两个过滤器时，执行的最后一个是应用的过滤器.

When I try to apply two filters, the last one executed is the one that is applied.

我试图将工作表用作范围/表，同样存在过滤问题.

I have tried to use the worksheet as a Range/Table, same filtering problem.

我最终创建了行"表达式，该表达式有效，但是第一行(标题)被过滤掉了.

I eventually created the "rows" expression, that works, but the 1st row (header) is filtered out.

public static void Filter(string source, string newFile)
    {
        using (var workbook = new XLWorkbook(source))
        {
            IXLWorksheet worksheet = workbook.Worksheet(1);

            int salesFoundCell = worksheet.FirstRow().Cells().First(c => c.Value.ToString() == "Sales Order Description").Address.ColumnNumber;
            int revenueFoundCell = worksheet.FirstRow().Cells().First(c => c.Value.ToString() == "Revenue recognition date").Address.ColumnNumber;

            //worksheet.RangeUsed().SetAutoFilter().Column(salesFoundCell).EqualTo("Equipment Sale");
            //worksheet.RangeUsed().SetAutoFilter().Column(revenueFoundCell).EqualTo("00.00.0000");             

            //var rows = worksheet.RowsUsed().Where(r => r.CellsUsed().Any(c => c.GetString().Contains("Equipment Sale")) && 
            //                                          r.CellsUsed().Any(c => c.GetString().Contains("00.00.0000")));

           Console.WriteLine(rows.Count());

            //workbook.SaveAs(newFile);             
        }
    }

我还尝试了ClosedXML Wiki上发布的方法，在该方法中，您将工作表另存为MemoryStream，重新应用过滤器，然后将其保存到新文件中.

I also tried the method posted on the ClosedXML wiki, where you save the worksheet as a MemoryStream, reapply the filter and then save it to a new file.

这是简短的版本:

public void Create(string filePath)
    {
        var wb = new XLWorkbook();
        IXLWorksheet ws;

        #region Multi Column
        String multiColumn = "Multi Column";
        ws = wb.Worksheets.Add(multiColumn);

        // Add filters
        ws.RangeUsed().SetAutoFilter().Column(2).EqualTo(3).Or.GreaterThan(4);
        ws.RangeUsed().SetAutoFilter().Column(3).Between("B", "D");

        // Sort the filtered list
        ws.AutoFilter.Sort(3);
        #endregion

        using (var ms = new MemoryStream())
        {
            wb.SaveAs(ms);

            var workbook = new XLWorkbook(ms);

            #region Multi Column 
            workbook.Worksheet(multiColumn).AutoFilter.Column(3).EqualTo("E");
            workbook.Worksheet(multiColumn).AutoFilter.Sort(3, XLSortOrder.Descending);
            #endregion

            workbook.SaveAs(filePath);
            ms.Close();
        }
    }

我经历了以下两个表达式的多次迭代:

I went through several iterations of the below two expressions:

worksheet.RangeUsed().SetAutoFilter().Column(salesFoundCell).EqualTo("Equipment Sale");
   worksheet.RangeUsed().SetAutoFilter().Column(revenueFoundCell).EqualTo("00.00.0000");

我尝试直接在列，范围，表上进行过滤，试图隐藏没有必需值的行.

I tried filtering directly on the columns, as a range, as a table, trying to hide the rows that did not have the required values.

所有这些过滤器要么基于一列过滤，要么根本不过滤.

All of it either filters based on one column or not at all.

"expression.AddFilter(一些值).AddFilter(一些其他值)；"没有帮助，因为我没有尝试在同一列上添加多个过滤器

The "expression.AddFilter(some value).AddFilter(some other value);" does not help as I am not trying to add multiple filters on the same column

和/或"功能在同一列上执行相同的多个过滤器.

The "And/Or" functionality does the same, multiple filters on the same column.

是否有人设法根据多列中的值进行过滤?

Has anyone managed to filter based on values in multiple columns?

任何建议都值得赞赏.

推荐答案

这是我的答案.我在同一个问题上挣扎了一段时间.

Here's my answer. I struggled with the same problem for a while.

关键是排序，必须在定义过滤器后执行.

The key is the sort, which has to be done after you define the filters.

var excelTable = TableRange.CreateTable();
excelTable.AutoFilter.Column(26).AddFilter("Filter 1");
excelTable.AutoFilter.Column(26).AddFilter("Filter 2");
excelTable.AutoFilter.Sort(1, XLSortOrder.Ascending);

这篇关于ClosedXML Excel按多列中的值过滤行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！