Hi All, Here I am Posting a Application which compares two excel sheets to show the differences like which row has been updated, deleted and inserted.(Assume I have two excel files one generated yesterday and other one generated today with some updates, deletions and insertions ) to find out new changes here is the programCode:
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 CompareExcel
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void BtnBrws1_Click(object sender, EventArgs e)
{
OpenFileDialog DialogA = new OpenFileDialog();
DialogA.CheckFileExists = true;
//DialogA.InitialDirectory = "Desktop"
DialogA.Title = "Select a File";
if (DialogA.ShowDialog() == DialogResult.OK)
{
FileTxt1.Text = DialogA.FileName;
}
else if(FileTxt1.Text =="")
{
MessageBox.Show("Please Select a File");
}
}
private void BtnBrws2_Click(object sender, EventArgs e)
{
OpenFileDialog DialogB = new OpenFileDialog();
DialogB.CheckFileExists = true;
DialogB.Title = "Select a file";
if (DialogB.ShowDialog() == DialogResult.OK)
{
FileTxt2.Text = DialogB.FileName;
}
else if(FileTxt2.Text == "")
{
MessageBox.Show("Please Select a File");
}
}
private void BtnCmpr_Click(object sender, EventArgs e)
{
string filename1 = FileTxt1.Text;
string filename2 = FileTxt2.Text;
//MessageBox.Show(filename1);
//MessageBox.Show(filename2);
string file1_sheet = GetExcelSheets(filename1);
string file2_sheet = GetExcelSheets(filename2);
// Create connection string variable. Modify the "Data Source"
// parameter as appropriate for your environment.
String sConnectionString1 = "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + filename1 + ";" +
"Extended Properties=Excel 12.0;";
String sConnectionString2 = "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + filename2 + ";" +
"Extended Properties=Excel 12.0;";
// Create connection object by using the preceding connection string.
OleDbConnection objConn = new OleDbConnection(sConnectionString1);
// Open connection with the database.
objConn.Open();
// The code to follow uses a SQL SELECT command to display the data from the worksheet.
// Create new OleDbCommand to return data from worksheet.
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + file1_sheet + "$]", objConn);
// Create new OleDbDataAdapter that is used to build a DataSet
// based on the preceding SQL SELECT statement.
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
// Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect;
// Create new DataSet to hold information from the worksheet.
DataSet objDataset1 = new DataSet();
// Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "XLData");
DataTable dt1 = objDataset1.Tables[0];
//dt1.DefaultView.Sort = string.Format("{0} {1}", "id", "ASC");
//MessageBox.Show("" + GridView1.Rows.Count);
// Clean up objects.
objConn.Close();
objConn = new OleDbConnection(sConnectionString2);
// Open connection with the database.
objConn.Open();
// The code to follow uses a SQL SELECT command to display the data from the worksheet.
// Create new OleDbCommand to return data from worksheet.
objCmdSelect = new OleDbCommand("SELECT * FROM [" + file2_sheet + "$]", objConn);
// Create new OleDbDataAdapter that is used to build a DataSet
// based on the preceding SQL SELECT statement.
objAdapter1 = new OleDbDataAdapter();
// Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect;
// Create new DataSet to hold information from the worksheet.
objDataset1 = new DataSet();
// Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "XLData");
DataTable dt2 = objDataset1.Tables[0];
//dt2.DefaultView.Sort = string.Format("{0} {1}", "id", "ASC");
// Clean up objects.
objConn.Close();
//GridView1.DataSource = dt2;
//GridView1.DataBind();
DataRow[] rows1 = dt1.Select("", "id ASC");
DataRow[] rows2 = dt2.Select("", "id ASC");
DataRow datarow1, datarow2;
int i, j;
for (i = 0, j = 0; i < dt1.Rows.Count; i++)
{
datarow1 = rows1[i];
string column1 = datarow1[0].ToString().Trim();
datarow2 = rows2[j];
string column2 = datarow2[0].ToString().Trim();
if (column1.CompareTo(column2) == 0)
{
int n;
for (n = 1; n < datarow1.ItemArray.Length; n++)
{
string value1 = datarow1.ItemArray[n].ToString().Trim();
string value2 = datarow2.ItemArray[n].ToString().Trim();
if (value1.CompareTo(value2) != 0)
{
MessageBox.Show("Updated Row : " + column1);
break;
}
}
j++;
}
else if (column1.CompareTo(column2) < 0)
{
MessageBox.Show("Deleted Row : " + column1);
}
}
for (i = j; i < rows2.Length; i++)
{
datarow2 = rows2[i];
MessageBox.Show("Inserted Row :" + datarow2[0].ToString());
}
}
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 Form1_Load(object sender, EventArgs e)
{
}
}
}
