How can I group-by some fields and sum other fields with epplus

Question

I have a list of Excel data and I want to run a query on it. I want epplus to identify replicated data in the first column and sum the values in the second column in replicate field. if this was my data:

 Name   Value
 Ali    12  
 Hasan  4
 Hasan  3
 Ali    3

I want to do this with epplus:

 Name   Value
 Ali    15  
 Hasan  7

I found Select multiple fields group by and sum but it doesn't do that with epplus. Can anyone help me?this is my code in epplus.

string fileman = Path.Combine(Path.GetTempPath(), "MyExceldata.xlsx");
using (ExcelPackage package = new ExcelPackage(new FileInfo(fileman)))
                {
                    ExcelWorksheet sheet = package.Workbook.Worksheets["Data"];
                    for (int i = 1; i <= sheet.Dimension.Rows, i++)
                    {
                        //column of name //--running my query in epplus
                        sheet.Cells[i,1].Value
                        //column of value //--running my query in epplus
                        sheet.Cells[i, 2].Value

                    }
                    //--running my query in epplus
                    package.Save();
                }

The linked answer is executing a LINQ query which would be happening after the data has been read out of the spreadsheet. You're not 100% clear in the question, but I presume that you are reading this data from a spreadsheet, and then want to process it? — Brendan Green, Mar 04 '19 at 21:41
@wahedfazeli See if this post help with what you are trying to do: https://stackoverflow.com/questions/33398255/create-advanced-filter/33412586#33412586 — Ernie S, Mar 13 '19 at 21:28

score 1 · Answer 1 · answered Mar 04 '19 at 21:55

1

The linked answer gives you what you need, but maybe we need to see it in the specific context that you are looking at this above.

Let's defined a class to hold your model:

class Item {
    public string Name {get;set;}
    public int Value {get;set;}
}

Then we'll get a list of sample data, per your question:

var items = new List<Item>{
    new Item { Name = "Ali", Value = 12 },
    new Item { Name = "Hasan", Value = 4 },
    new Item { Name = "Hasan", Value = 3 },
    new Item { Name = "Ali", Value = 3 }
};

The LINQ query that we can run does the following:

var grouped = items.GroupBy(g => g.Name)
    .Select(s => new Item
    { 
        Name = s.Key,
        Value = s.Sum(x => x.Value)
    });

That is:

Group all the items by the Name (as this is your unique identifier)
From the grouping, we'll select a new Item for each unique identifier
We'll sum the values of the items that belong to the group

If we inspect the content of grouped, it will contain:

Name        Value
----        -----
Ali         15
Hasan       7

answered Mar 04 '19 at 21:55

Brendan Green

11,676
5
44
76

But It isnt solved by Epplus.I want to know how to convert this codes with epplus codes. – wahed fazeli Mar 05 '19 at 03:17
I asked "I presume that you are reading this data from a spreadsheet, and then want to process it?", and you responded "yes". Do you want to add a formula or something along those lines into the spreadsheet? How is the data getting populated? Would it not be easier to group the data first, before writing it to the spreadsheet? – Brendan Green Mar 05 '19 at 03:18
I do all of my programming with epplus and I even program some other queries with epplus.I want to just add this query along with my other queries. – wahed fazeli Mar 05 '19 at 03:29
I really don't follow what it is that you are trying to do. Is the initial set of data created and populated into the spreadsheet by you? Can you update the question with the code that's currently being used? EEPlus is nothing more than a library that allows you to read and write Excel Spreadsheets, so when you talk about "queries", what do you mean? – Brendan Green Mar 05 '19 at 03:35
Do you mean if I add this codes(codes of Linq) along with codes of epplus,It doesnt make problem? – wahed fazeli Mar 05 '19 at 04:01
You need to show what "codes of epplus" means. Please update the question with the code that you are using, highlighting where you want this new table / data to be inserted into the spreadsheet – Brendan Green Mar 05 '19 at 04:03

How can I group-by some fields and sum other fields with epplus

1 Answers1