The below method compares 2 worksheets in the same workbook and displays the data which are similar.
void CompareExcel()
{
string filePath = @"F:\Book2.xlsx";
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
try
{
xlWorkBook = xlApp.Workbooks.Open(filePath, 0, true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", true, false, 0, true, 0);
Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)xlApp.Worksheets["Sheet1"];
Microsoft.Office.Interop.Excel.Worksheet worksheet2 = (Microsoft.Office.Interop.Excel.Worksheet)xlApp.Worksheets["Sheet2"];
int sheet1LastRowCount = worksheet1.UsedRange.Rows.Count;
int sheet2LastRowCount = worksheet2.UsedRange.Rows.Count;
for (int i = 2; i < sheet1LastRowCount; i++)
{
for (int j = 2; j < sheet2LastRowCount; j++)
{
if (worksheet1.Range["B" + i, "B" + i].Value2 == worksheet2.Range["B" + j, "B" + j].Value2)
{
Console.WriteLine(worksheet1.Range["A" + i, "A" + i].Value2);
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
xlApp.Quit();
}
}