How can i compare two excel sheets in c#?

[Hatice]

Hi,
i have two excel document and one of them is new and the other one is old. i must compare theese and add new sheets to the other excel with c#.

I want to compare two excel files in c#, i learned how can import and export from excel to datagrid and from datagrid to excel. but i must learn excel sheets import to datagrid or excel row by row.

how can i do and where can i start i dont know?

thanks.

[Peter]

Hi Hatice,

Thank you for your interest in RadControls.

We does not offer a component which offers a such comparison. In order to access the exported cells/rows you can try to use the ExcelCellFormatting event which fires for every exported cell:

Private Sub exporter_ExcelCellFormatting(sender As Object, e As ExcelCellFormattingEventArgs)
    Dim row As GridViewRowInfo= TryCast(e.GridCellInfo.RowInfo, GridViewRowInfo)
    If row.Index =... Then

    End If
End Sub

For more details please, read this article: Export to Excel.

I hope this helps. Regards,
Peter
Telerik

[Hatice]

Hi,
My question is not about the component. it is also about with sql. i know how can i import excels in c# form, but i could'nt compair them.
i cant do thes in excel documents, it must be in c#, automaticly.

theese are codes,

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 WindowsFormsApplication8
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog DialogA = new OpenFileDialog();
            DialogA.CheckFileExists = true;
            DialogA.Title = "Select a File";

            DialogA.ShowDialog();
            textBox1.Text = DialogA.FileName;
            /*
            if (DialogA.ShowDialog() == DialogResult.OK)
            {
                textBox1.Text = DialogA.FileName;
            }
            else if (textBox1.Text == "")
            {
                MessageBox.Show("please select a file");
            }
             */
        }

        private void button2_Click(object sender, EventArgs e)
        {
            OpenFileDialog DiaologB = new OpenFileDialog();
            DiaologB.CheckFileExists = true;
            DiaologB.Title = "select a file";

            DiaologB.ShowDialog();
            textBox2.Text = DiaologB.FileName;

        }
        


        private void button3_Click(object sender, EventArgs e)
        {
            string filename1 = textBox1.Text;
            string filename2 = textBox2.Text;



            string file1_sheet = GetExcelSheets(filename1);
            string file2_sheet = GetExcelSheets(filename2);



            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;";

            OleDbConnection objConn = new OleDbConnection(sConnectionString1);
            objConn.Open();

            OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + file1_sheet + "$]", objConn);
            OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

            objAdapter1.SelectCommand = objCmdSelect;
            DataSet objDataset1 = new DataSet();
            objAdapter1.Fill(objDataset1, "XLData");

            DataTable dt1 = objDataset1.Tables[0];

            objConn.Close();
            objConn = new OleDbConnection(sConnectionString2);


            objConn.Open();
            objCmdSelect = new OleDbCommand("SELECT * FROM [" + file2_sheet + "$]", objConn);

            objAdapter1 = new OleDbDataAdapter();
            objAdapter1.SelectCommand = objCmdSelect;

            objDataset1 = new DataSet();
            objAdapter1.Fill(objDataset1, "XLData");

            DataTable dt2 = objDataset1.Tables[0];
            objConn.Close();

            objConn.Open();
            objCmdSelect = new OleDbCommand("Select * from [" + file1_sheet + "$], [" + file2_sheet + "$] where [" + file1_sheet + "$].barkod == [" + file2_sheet + "$].barkod", objConn);
            objAdapter1 = new OleDbDataAdapter();
            objAdapter1.SelectCommand = objCmdSelect;
            objDataset1 = new DataSet();
            objAdapter1.Fill(objDataset1, "XLData");  // " OleDbException was unhandled " this error is giving
            DataTable dt3 = objDataset1.Tables[0];
            objConn.Close();


            DataRow[] rows1 = dt1.Select("", "barkod ASC");
            DataRow[] rows2 = dt2.Select("", "barkod ASC");
            DataRow[] rows3 = dt3.Select("", "barkod ASC");

            dataGridView1.DataSource = dt3;

            //DataRow datarow1, datarow2;
        }
        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;
        }

       
    }
}

[Peter]

Hello,

As I mentioned we do not offer a component which offers a such comparison. I do not know whether this is related to Telerik controls and I would like to suggest to refer to specialized sites for SQL like: http://www.mssqltips.com/ or stackoverflow.

I hope this helps.

Regards,
Peter
Telerik