使用Linq在Epplus工作表中选择分组的最大值和最小值 [英] Selecting grouped max and min in a Epplus worksheet with Linq

问题描述

我有一个如下所示的Epplus Excel工作表.

I have an Epplus Excel Worksheet that looks like this.

我要按组"列(在A列中)分组，然后选择最大值和最小值.我的group by子句中的某些内容不太正确，并且出现错误:Cannot apply indexing with [] to ExcelRangeBase.

I want to group by the Group column (in column A), and then select the max and the min. Something in my group by clause isn't quite right and I'm getting an error: Cannot apply indexing with [] to ExcelRangeBase.

var maxMinGrouped = from cells in _dataSheet.Cells["A:H"]
                    group cells by cells["A:A"].Value into g //pull "Group" column into group
                    select new
                    {
                        Group = cells["A:A"].Value,
                        MaxDailyIndex = g.Max(c => c.Value),
                        MinDailyIndex = g.Min(c => c.Value)
                    };

我要实现的SQL.

SELECT Group
    ,MAX(Col_E) AS MaxDailyIndex
    ,MIN(Col_E) AS MinDailyIndex
FROM Worksheet
GROUP BY Group

推荐答案

请记住，单元格集合是2x2矩阵，因此您需要先将单元格分组为行，然后才能分组为行.我对linq的查询表示法感到不满意，但以下是使用点表示法的方法:

Remember that the cells collection is a 2x2 matrix so you will need to group cells into rows before you can group into groups of row. I am way rusty on the query notation of linq but here is how to do it using the dot notation:

[TestMethod]
public void Grouped_Row_Test()
{
    //http://stackoverflow.com/questions/30466257/selecting-grouped-max-and-min-in-a-epplus-worksheet-with-linq

    var existingFile = new FileInfo(@"c:\temp\Grouped.xlsx");
    using (var pck = new ExcelPackage(existingFile))
    {
        var _dataSheet = pck.Workbook.Worksheets.First();

        //Group cells by row
        var rowcellgroups = _dataSheet
            .Cells["A:H"]
            .GroupBy(c => c.Start.Row);

        //Now group rows the column A; Skip the first row since it has the header
        var groups = rowcellgroups
            .Skip(1)
            .GroupBy(rcg => rcg.First().Value);

        //Reproject the groups for the min/max values; Column E = 5
        var maxMinGrouped = groups
            .Select(g => new
            {
                Group = g.Key,
                MaxDailyIndex = g.Select(o => o.First(rc => rc.Start.Column == 5)).Max(rc => rc.Value),
                MinDailyIndex = g.Select(o => o.First(rc => rc.Start.Column == 5)).Min(rc => rc.Value)
            });

        maxMinGrouped
            .ToList()
            .ForEach(mmg => Console.WriteLine("{{Group: \"{0}\", Min={1}, Max={2}}}", mmg.Group, mmg.MinDailyIndex, mmg.MaxDailyIndex));
    }
}

在我向您的excel表中添加一些随机数据后，哪个会在控制台中给出:

Which gives this in the console after I threw in some random data to your excel table:

{Group: "3", Min=0, Max=88}
{Group: "4", Min=6, Max=99}

这篇关于使用Linq在Epplus工作表中选择分组的最大值和最小值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！