import excel file in C# by grouping excel data

Question

Hi I get excel file to user like this: I need to import this data into JSON string. Row 1 and 2 belong to same transaction TrnName 'xxxx Name' with 2 different accounts. So accounts should go into JSON as: { ClientName: AAA Type: Distribution Method: wire Trn Name: xxxx Name Account#:id=5188;id=5189 } how can I do this JSON String. Need to group rows together based on Trn Name column. Any lead is helpful. TIA

Prathiba · Answer

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

Prasad Raveendran · Answer

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 } ]

Jignesh Kumar · Answer

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.