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

}
}
}

7 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