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