CODEWITHSUNDEEP
vb.net
Soumya
Soumya

Reputation: 1

How Can I comapre Two excel sheet using C# With the help of VBA code that I have below?

Dim LastRow_1 as Integer
Dim LastCol_1 as Integer
Dim Data_1 as Range
Dim LastRow_2 as Integer
Dim LastCol_2 as Integer
Dim Data_2 as Range

Dim Sh_1 as Worksheet
Dim Sh_2 as Worksheet

Dim X As Long
Dim Y As Long
Dim C_1 as Range
Dim C_2 as Range

Set Sh_1  = Activeworkbook.Sheets("Sheet1")
Set Sh_2  = Activeworkbook.Sheets("Sheet2")

LastRow_1 = Sh_1.Range("A65535").End(xlup).Row
LastCol_1 = Sh_1.Range("IV1").End(xltoLeft).Column
Set Data_1 = Sh_1.range("A1").Resize(LastRow_1,LastCol_1)

LastRow_2 = Sh_2.Range("A65535").End(xlup).Row
LastCol_2 = Sh_2.Range("IV1").End(xltoLeft).Column
Set Data_2 = Sh_2.range("A1").Resize(LastRow_2,LastCol_2)

For Each C_1 in Data_1
   For Each C_2 In Data_2
       If C_2 = C_1 then
         'found a cell on sheet2 that matched cell in sheet1
         'now do what you need to do
       End if
   Next C_2
Next C_1

Upvotes: 0

Views: 1046

Answers (2)

beginner
beginner

Reputation: 11

select the content of the worksheet to a datatable and you may use the DataRelations object to compare the two data tables. You may also use merging of the two data tables to identify the differences.

Upvotes: 1

Hans Olsson
Hans Olsson

Reputation: 55049

You can do something like this:

Excel.Application app = new Excel.Application();
app.Visible = true;
Excel.Workbooks workbooks = m_app.Workbooks;
Excel.Workbook workbook = workbooks.Open(fileName);
workbook.Activate();
Excel.Sheets sheets = workbook.Sheets;
Excel.Worksheet sheet = sheets(1);
Excel.Range range = sheet.UsedRange;

// values will be an multi dimensional array of objects
object values = range.Value;

Marshal.ReleaseComObject(range);
Marshal.ReleaseComObject(sheet);
Marshal.ReleaseComObject(sheets);
workbook.Close(false);
Marshal.ReleaseComObject(workbook);
workbooks.Close();
Marshal.ReleaseComObject(workbooks);
app.Quit();
Marshal.ReleaseComObject(app);

That'll read in one sheet, then just call the same code for the other sheet and then you can compare values as:

AreEqual(values(1,1), values2(1, 1));

Where AreEqual is a method you write that compares things properly accounting for nulls and datatypes etc.

Btw, I wrote the code above as C# as that what's said in the question title, but I see that the tag is VB.Net but if you want it as VB.Net just use some converter to convert it automatically, like http://www.developerfusion.com/tools/convert/vb-to-csharp/ or similar.

Upvotes: 0

Related Questions