CODEWITHSUNDEEP
c#excel
Anees Deen
Anees Deen

Reputation: 1403

How to compare two excel sheets and create new one in c#?

I am having two excel files.
sheet1 has 5 columns with values.
sheet2 has only one column.
sheet1 and sheet's first column has some unique values. I need an another excelsheet, where the new one has the 5 columns(matched unique values in both sheet1 and sheet2) How to do this using C#?
Any help would be appreciated.

Upvotes: 1

Views: 7689

Answers (1)

stefankmitph
stefankmitph

Reputation: 3306

You could do this by either using the Interop.Excel (Interop C# tutorial) or some alternative to Interop (f.e. ClosedXML). Read in the files and use LINQ to get the data you need. With IEnumerable.Except you can just take the elements from sheet1 that are not in sheet2 and put it into a new sheet and save it to an Excel-file. 

var workbook1 = new XLWorkbook(@"workbook1.xlsx");
var workbook2 = new XLWorkbook(@"workbook2.xlsx");
var worksheet1 = workbook.Worksheet("sheet1");
var worksheet2 = workbook.Worksheet("sheet2");

var listSheet1 = new List<IXLRow>(); // list of Rows
var listSheet2 = new List<IXLRow>();

// puts all UsedRows (including "headers") from sheet1 into a list of rows
using (var rows = worksheet1.RowsUsed())
{
  foreach (var row in rows)
  {
    listSheet1.Add(row);
  }
}

using(var rows = worksheet2.RowsUsed())
{
  foreach (var row in rows)
  {
    listSheet2.Add(row);
  }
}

IEqualityComparer comparer = new XLRowComparer(); // you have to implement your own comparer here. there's a lot of tutorials/samples out there

var uniqueIdList = listSheet1.Intersect(listSheet2, comparer).ToList(); // in this case I'd use intersect instead of except which returns the IDs provided in sheet1 and sheet2

// and now you just have to put the rows from uniqueIdList into a new worksheet and save it

I hope this was helpful...

Upvotes: 1

Related Questions