How to read excel grouping by a specific column in EPPLUS

Question

I am using EPPlus 6 in my Blazor Server Application. I am uploading an excel and saving data into a database. I wonder if there is a way to group excel data based on Vendor ID.

Here is how I read excel;

var untrustedFileName = file.FileName;
var customerId = 0;

var trustedFileNameForDisplay =
WebUtility.HtmlEncode(untrustedFileName);

try
{
   var trustedFileNameForFileStorage = Path.GetRandomFileName();
   var path = Path.Combine(env.ContentRootPath,
              env.EnvironmentName, "unsafe_uploads",
              trustedFileNameForFileStorage);

   await using FileStream fs = new(path, FileMode.Create);
   await file.CopyToAsync(fs);

   logger.LogInformation("{FileName} saved at {Path}",
                        trustedFileNameForDisplay, path);
   //Get file
   var newfile = new FileInfo(file.FileName);
   var fileExtension = newfile.Extension;

   //Check if file is an Excel File
   if (fileExtension.Contains(".xls"))
   {
       using var ms = new MemoryStream();
       await file.OpenReadStream().CopyToAsync(ms);

      // If you use EPPlus in a noncommercial context
      // according to the Polyform Noncommercial license:
       ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
       using ExcelPackage package = new ExcelPackage(ms);
       var workSheet = package.Workbook.Worksheets["Order"];
       var totalRows = workSheet.Dimension.Rows;

       var orderList = new List<Order>();
       List<OrderDetail> orderDetailList = null;

       var k = 0;

       for (var i = 2; i <= totalRows; i++)
       {
             ...

Here is a sample excel I am going to group by Vendor ID.

For Vendor ID = 1, I will create 1 order with 4 items according to this excel.

Answer 1

Here is the LINQ I needed:

var dt = GetDataTableFromExcel(workSheet);
var query = dt.AsEnumerable().GroupBy(r => new { col1=r.Field<string>("Vendor ID"),col2= r.Field<string>("Customer ID") }).SelectMany(m=>m).OrderBy(o => o.Field<string>("Vendor ID"));