Compare data in Rows of 2 Excel Files to Check Similar Value

Question

Hi,

 

 I'm Trying to Check if 2 Excel Files Contain Same Data in Specific Rows. Here is What I've Done.

 

 

 
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using System.Data.SqlClient;
using System.IO;
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;

namespace DualityChecker
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

OleDbConnection objconn;

private void Form1_Load(object sender, EventArgs e)
{
status.Text = "Status: Select 2 Excel Sheets to Start Comparison !";
}



public string GetExcelSheets(string excelFileName)
{
Microsoft.Office.Interop.Excel.Application excelFileObject = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workBookObject = null;
workBookObject = excelFileObject.Workbooks.Open(excelFileName, 0, true, 5, "", "", false,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"",
true,
false,
0,
true,
false,
false);
Excel.Sheets sheets = workBookObject.Worksheets;

// get the first and only worksheet from the collection of worksheets
Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
MessageBox.Show(worksheet.Name);
return worksheet.Name;
}

private void btnFile1_Click(object sender, EventArgs e)
{
OpenFileDialog File1 = new OpenFileDialog();
File1.CheckFileExists = true;
File1.InitialDirectory = "Desktop";
File1.Filter = "Excel Files|*.xls;*.xlw;*.xlsx|All Files|*.*";
File1.Title = "Select A First Excel File";

if (File1.ShowDialog() == DialogResult.OK)
{
fn1.Text = File1.FileName;
}
else if (fn1.Text == "")
{
MessageBox.Show("Please Select a File !");
}

}

private void btnFile2_Click(object sender, EventArgs e)
{



OpenFileDialog File2 = new OpenFileDialog();
File2.CheckFileExists = true;
File2.InitialDirectory = "Desktop";
File2.Filter = "Excel Files|*.xls;*.xlw;*.xlsx|All Files|*.*";
File2.Title = "Select A First Excel File";

if (File2.ShowDialog() == DialogResult.OK)
{
fn2.Text = File2.FileName;
}
else if (fn2.Text == "")
{
MessageBox.Show("Please Select a File !");
}
}

private void btnCompare_Click(object sender, EventArgs e)
{
status.Text = "Status: Processing...";
string filename1 = fn1.Text;
string filename2 = fn2.Text;
string sheet1 = GetExcelSheets(filename1);
string sheet2 = GetExcelSheets(filename2);


objconn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filename1 + ";" + "Extended Properties=Excel 12.0;");
objconn.Open();
var Firstds = new DataSet();
var Secondds = new DataSet();
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + sheet1 + "$]", objconn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
objAdapter1.Fill(Firstds, "XLData");
DataTable dt1 = Firstds.Tables[0];
objconn.Close();

objconn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filename2 + ";" + "Extended Properties=Excel 12.0;");
objconn.Open();
objCmdSelect = new OleDbCommand("SELECT * FROM [" + sheet2 + "$]", objconn);
objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
objAdapter1.Fill(Firstds, "XLData");
DataTable dt2 = Secondds.Tables[0]; // Cannot Find Table, IndexOutRange Exception.
objconn.Close();

objconn.Open();
objCmdSelect = new OleDbCommand("Select * from [" + sheet1 + "$], [" + sheet2 + "$] where [" + sheet1 + "$].F1 == [" + sheet2 + "$].F1", objconn);
objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
DataSet objDataset1 = new DataSet();
DataTable dt3 = objDataset1.Tables[0];
objconn.Close();

DataRow[] rows1 = dt1.Select("", "F1 ASC");
DataRow[] rows2 = dt2.Select("", "F1 ASC");
DataRow[] rows3 = dt3.Select("", "F1 ASC");
dataGridView1.DataSource = dt3;
}
}
}

 

 

 

 

Now Problem is that IT Gives Me An Error That Cannot Find Table 0 Where i highlighted. Here is Example Sheet I'm Using.

 

External Link: http://s7.postimg.org/lfe3vrhrf/image.png