How can i compare two excel sheets in c#?

4 posts, 0 answers
  1. Hatice
    Hatice avatar
    6 posts
    Member since:
    Jun 2013

    Posted 17 Jul 2013 Link to this post

    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.
  2. Peter
    Admin
    Peter avatar
    1148 posts

    Posted 19 Jul 2013 Link to this post

    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
    TRY TELERIK'S NEWEST PRODUCT - EQATEC APPLICATION ANALYTICS for WINFORMS.
    Learn what features your users use (or don't use) in your application. Know your audience. Target it better. Develop wisely.
    Sign up for Free application insights >>
  3. UI for WinForms is Visual Studio 2017 Ready
  4. Hatice
    Hatice avatar
    6 posts
    Member since:
    Jun 2013

    Posted 04 Sep 2013 Link to this post

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

           
        }
    }



  5. Peter
    Admin
    Peter avatar
    1148 posts

    Posted 09 Sep 2013 Link to this post

    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
    TRY TELERIK'S NEWEST PRODUCT - EQATEC APPLICATION ANALYTICS for WINFORMS.
    Learn what features your users use (or don't use) in your application. Know your audience. Target it better. Develop wisely.
    Sign up for Free application insights >>
Back to Top