import excel file in C# by grouping excel data

Thank you Prasad and Jignesh. I will try and get back to you.

I'm just trying to replicate your expected response. Please do let me know if there are any specific queries

First, make sure to install ClosedXML and Newtonsoft.Json:

dotnet add package ClosedXML
dotnet add package Newtonsoft.Json

Then, use the following code to read data from the Excel file, group it by the Trn Name column, and generate the required JSON output.

using System;
using System.Collections.Generic;
using System.Linq;
using ClosedXML.Excel;
using Newtonsoft.Json;

public class Transaction
{
    public string ClientName { get; set; }
    public string Type { get; set; }
    public string Method { get; set; }
    public string TrnName { get; set; }
    public string AccountId { get; set; }
}

public class Program
{
    public static void Main()
    {
        // Path to your Excel file
        string filePath = "path_to_your_excel_file.xlsx";

        // Load data from the Excel file
        var transactions = new List();
        using (var workbook = new XLWorkbook(filePath))
        {
            var worksheet = workbook.Worksheet(1); // Use the first worksheet
            var rows = worksheet.RangeUsed().RowsUsed().Skip(1); // Skip header row

            foreach (var row in rows)
            {
                transactions.Add(new Transaction
                {
                    ClientName = row.Cell(1).GetString(),
                    Type = row.Cell(2).GetString(),
                    Method = row.Cell(3).GetString(),
                    TrnName = row.Cell(4).GetString(),
                    AccountId = row.Cell(5).GetString()
                });
            }
        }

        // Group by TrnName and aggregate AccountIds
        var groupedData = transactions
            .GroupBy(t => new { t.ClientName, t.Type, t.Method, t.TrnName })
            .Select(g => new
            {
                g.Key.ClientName,
                g.Key.Type,
                g.Key.Method,
                TrnName = g.Key.TrnName,
                AccountIds = string.Join(";", g.Select(t => $"id={t.AccountId}"))
            });

        // Convert to JSON
        var json = JsonConvert.SerializeObject(groupedData, Formatting.Indented);
        Console.WriteLine(json);
    }
}

Sample Json Output

[
  {
    "ClientName": "AAA",
    "Type": "Distribution",
    "Method": "Wire",
    "TrnName": "XXX Name",
    "AccountIds": "id=5188;id=5189"
  },
  {
    "ClientName": "AAA",
    "Type": "Distribution",
    "Method": "Wire",
    "TrnName": "YYY Name",
    "AccountIds": "id=5016"
  },
  {
    "ClientName": "AAA",
    "Type": "Distribution",
    "Method": "Wire",
    "TrnName": "ZZZ",
    "AccountIds": "id=5016"
  }
]

Hello,

You need to create below 2 classes to convert your flat data to group by,

// Main class from where you will get data from Excelsheet,
public class Record
{
    public string ClientName { get; set; }
    public string Type { get; set; }
    public string Method { get; set; }
    public string TrnName { get; set; }
    public int AccountNo { get; set; }
}


// Define a class for grouped data
public class GroupedRecord
{
    public string ClientName { get; set; }
    public string Type { get; set; }
    public string Method { get; set; }
    public List Transactions { get; set; }
}

public class Transaction
{
    public string TrnName { get; set; }
    public int AccountNo { get; set; }
}

Now get data from Excel sheet, Here I have created sample data using above class,

// Step 1: Create a sample data structure based on your table
        var rawData = new List
        {
            new Record { ClientName = "AAA", Type = "Distribution", Method = "Wire", TrnName = "XXXX Name", AccountNo = 5188 },
            new Record { ClientName = "AAA", Type = "Distribution", Method = "Wire", TrnName = "XXXX Name", AccountNo = 5189 },
            new Record { ClientName = "AAA", Type = "Distribution", Method = "Wire", TrnName = "YYYY", AccountNo = 5016 },
            new Record { ClientName = "AAA", Type = "Distribution", Method = "Wire", TrnName = "ZZZZZ", AccountNo = 5016 }
        };

        // Step 2: Group data by ClientName, Type, and Method
        var groupedData = rawData
            .GroupBy(r => new { r.ClientName, r.Type, r.Method })
            .Select(group => new GroupedRecord
            {
                ClientName = group.Key.ClientName,
                Type = group.Key.Type,
                Method = group.Key.Method,
                Transactions = group.Select(g => new Transaction
                {
                    TrnName = g.TrnName,
                    AccountNo = g.AccountNo
                }).ToList()
            })
            .ToList();

        // Step 3: Serialize to JSON
        string json = JsonSerializer.Serialize(groupedData, new JsonSerializerOptions { WriteIndented = true });

You will receive the JSON in the format shown above, which you can utilize as needed.