![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(153.6, 68%, 24%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBB%3C/text%3E%3C/svg%3E)
Windows Presentation Foundation
A part of the .NET Framework that provides a unified programming model for building line-of-business desktop applications on Windows.
2,678 questions
Your solution consists of 3 parts:
- Read data from source
- Process data (normalize, aggregate, remove duplicates, etc.)
- Transfer processed data to destination
SpreadsheetLight will be used in the first phase. Entity framework, or SQLiteCommand will be used for the third phase. The second phase, is the problem you are trying to solve in this question.
I assume reading the sheet or writing to db is not the main concern of the question, so I'll focus on aggregating duplicates. No matter where the data is coming from, define a model for your data which could be a C# model or a DataTable, then parse the input data to your model. Then you can simply use a LINQ GroupBy to remove the duplicate data, and replace those duplicates with sum.
Example 1 - Using a C# model to process data
Here is a very simple example to demonstrate how to use a simple C# class for modeling and processing data to remove duplicates and aggregate results using GroupBy:
using System;
using System.Collections.Generic;
using System.Linq;
namespace SampleConsoleApp
{
internal class Program
{
static void Main(string[] args)
{
var input = new List<DataItem> {
new DataItem(){ A= "Lorem", B = "Ipsum", Amount = 100 },
new DataItem(){ A= "Lorem", B = "Ipsum", Amount = 100 },
new DataItem(){ A = "Dolor", B = "Sit", Amount = 100 },
new DataItem(){ A = "Dolor", B = "Sit", Amount = 100 },
new DataItem(){ A = "Dolor", B = "Sit", Amount = 100 },
new DataItem(){ A = "Consectetuer", B = "Adipiscing", Amount = 100 },
};
var result = input.GroupBy(
x => new { A = x.A, B = x.B },
x => new { Amount = x.Amount })
.Select(g => new DataItem()
{
A = g.Key.A,
B = g.Key.B,
Amount = g.Sum(x => x.Amount)
})
.ToList();
foreach (var item in result)
{
Console.WriteLine($"A:{item.A}, B:{item.B}, Amount:{item.Amount}");
}
Console.ReadLine();
}
}
public class DataItem
{
public string A { get; set; }
public string B { get; set; }
public int Amount { get; set; }
}
}
Example 2 - Using a DataTable and model to process data
In this example, I've used a DataTable to model the data. The basics are the same as the other example and I've shown how to use a simple DataTable for modeling and processing data to remove duplicates and aggregate results using GroupBy:
using System;
using System.Data;
using System.Linq;
namespace SampleConsoleApp
{
internal class Program
{
static void Main(string[] args)
{
var input = new DataTable();
input.Columns.Add("A", typeof(string));
input.Columns.Add("B", typeof(string));
input.Columns.Add("Amount", typeof(int));
input.Rows.Add("Lorem", "Ipsum", 100);
input.Rows.Add("Lorem", "Ipsum", 100);
input.Rows.Add("Lorem", "Ipsum", 100);
input.Rows.Add("Dolor", "Sit", 100);
input.Rows.Add("Dolor", "Sit", 100);
input.Rows.Add("Consectetuer", "Adipiscing", 100);
var result = input.Clone();
input.Rows.Cast<DataRow>().GroupBy(
x => new { A = x.Field<string>("A"), B = x.Field<string>("B") },
x => new { Amount = x.Field<int>("Amount") })
.Select(g => new
{
A = g.Key.A,
B = g.Key.B,
Amount = g.Sum(x => x.Amount)
})
.ToList()
.ForEach(x =>
{
result.Rows.Add(x.A, x.B, x.Amount);
});
foreach (DataRow item in result.Rows)
{
Console.WriteLine($"A:{item["A"]}, B:{item["B"]}, Amount:{item["Amount"]}");
}
Console.ReadLine();
}
}
}
Look at the very similar approach that I used in both examples. And here is the result in both cases:
A:Lorem, B:Ipsum, Amount:200
A:Dolor, B:Sit, Amount:300
A:Consectetuer, B:Adipiscing, Amount:100
