Monday, February 22, 2010

Compare Two Excel Sheets Programatically in C#

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 program

Code:

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)
{

}
}
}

8 comments:

  1. here wt is FileTxt1.Text = DialogA.FileName; ??
    Means where that filetxt1 exists??

    ReplyDelete
  2. Thanks A lot for this code....it really saved a lot of time for meeee............

    ReplyDelete
  3. WHY DON'T THIS CODE WORK? i GET ERROR "Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))"

    ReplyDelete
  4. This code is poorly written, but shows some promise. I wouldn't recommend any one useing it as it is.

    ReplyDelete
  5. The code works fine but whats the use of this code if a new file isn't created with the output.

    ReplyDelete
  6. My requirement if I must be able to generate a new excel file based on the differences of the two sheets, where the newly added records should be displayed as new and duplicates must be deletd.

    ReplyDelete
  7. good and nice but i need to save comparision file too so please let me know how to do this

    ReplyDelete