none
How to compare 2 Excel sheets in c# RRS feed

  • Question

  • Hi,

    I want to compare 2 excel sheet and save the results(differences) to the another file.

                                                           

    Sheet 1
    A B C D E
    aa bb cc dd ee
             
    1 2 4 5 7
    1 3 5 6 8


    Sheet 2
    E B A C D
    ee bb aa cc dd
             
    7 4 1 4 5
    8 3 1 55 6

    As i have select 2 headings on each sheets. You can say make this heading primarily and then search.

    As i have to compare in the way that A and aa from 1st sheet and then find the same 2 values(A and aa) from the another sheet.

    if found then compare the values from both sheets.

    and give the result(differences on the sheet).

    Like on Sheet1, i select B and bb column, then i search on sheet2, if i found then i start comparing the value. According to screenshot. AS sheet1 has 2 and 3 and sheet2 has 4 and 3. here 2 and 4 are different so give the headings of the difference value and then both values.


    like in RESULT

    B      bb      2(Sheet1)      4(Sheet2)

    And so one.

    Please help on this. i am really thankful to you for your help.

      


    Monday, May 20, 2019 8:00 PM

Answers

  • Hi adwikcool,

    I have modified my code according to your advice, you could try the following code.

        class Program
        {
            static void Main(string[] args)
            {
                DataTable sheet1 = datatable("D:\\Test1\\test.xlsx", "Sheet1");
                DataTable sheet2 = datatable("D:\\Test1\\test.xlsx", "Sheet2");
                DataTable table = new DataTable();
                table.Columns.Add("FirstHeading", typeof(string));
                table.Columns.Add("SecondHeading", typeof(string));
                table.Columns.Add("ValueinSheet1", typeof(int));
                table.Columns.Add("ValueinSheet2", typeof(int));
                string test1 = null;
                string test2 = null;
                foreach (DataColumn column in sheet1.Columns)
                {
                    string a = column.ColumnName;
                    foreach (DataColumn column1 in sheet2.Columns)
                    {
                        string b = column.ColumnName;
                        if (a == b)
                        {
                            List<object> lst1 = (from d in sheet1.AsEnumerable() select d.Field<object>(a)).ToList();
                            List<object> lst2 = (from d in sheet2.AsEnumerable() select d.Field<object>(a)).ToList();
                            for (int i = 0; i < lst1.Count; i++)
                            {
                                for (int j = i; j < lst2.Count; j++)
                                {
                                    if ((i == j) && (!lst1.SequenceEqual(lst2)))
                                    {
                                        
                                        if (lst1[i].ToString() != lst2[j].ToString())
                                        {
                                            test1 = lst1[i].ToString();
                                            test2 = lst2[i].ToString();
                                            string name1 = sheet1.Rows[0][a].ToString();
                                            string name2 = sheet2.Rows[0][a].ToString();
                                            if(name1==name2)
                                            {
                                                table.Rows.Add(a, name1,test1, test2);
                                            }
    
                                         
                                        }
    
    
                                    }
                                }
                            }
    
    
                        }
    
                    }
                }
    
                DataView dv = new DataView(table);
                table = dv.ToTable(true);
    
                var lines = new List<string>();
    
                string[] columnNames = table.Columns.Cast<DataColumn>().
                                                  Select(column => column.ColumnName).
                                                  ToArray();
    
                var header = string.Join(",", columnNames);
                lines.Add(header);
    
                var valueLines = table.AsEnumerable()
                                   .Select(row => string.Join(",", row.ItemArray));
                lines.AddRange(valueLines);
    
                File.WriteAllLines("D:\\excel.csv", lines);
                Console.WriteLine("success");
                Console.ReadKey();
            }
            public static DataTable datatable(string path, string sheetName)
            {
                DataTable dt = new DataTable();
                string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;MAXSCANROWS=0'";
                using (OleDbConnection conn = new OleDbConnection(connectionString))
                {
                    using (OleDbCommand comm = new OleDbCommand())
                    {
                        comm.CommandText = "Select * from [" + sheetName + "$]";
                        comm.Connection = conn;
                        using (OleDbDataAdapter da = new OleDbDataAdapter())
                        {
                            da.SelectCommand = comm;
                            da.Fill(dt);
                            return dt;
                        }
                    }
    
                }
            }
        }

    Result:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 29, 2019 3:29 AM
    Moderator

All replies

  • Need to do this with C#. Without the use of any MACROS.
    Monday, May 20, 2019 8:04 PM
  • Hi adwikcool,

    Thank you for posting here.

    Based on your description, you want to compare 2 excel sheet and save the results(differences) to the another file.

    You could try the following code.

     static void Main(string[] args)
            {
                DataTable sheet1 = datatable("D:\\test.xlsx", "Sheet1");
                DataTable sheet2 = datatable("D:\\test.xlsx", "Sheet2");
                DataTable table = new DataTable();
                table.Columns.Add("FirstName", typeof(string));
                table.Columns.Add("SecondName", typeof(string));
                table.Columns.Add("Sheet1Number", typeof(int));
                table.Columns.Add("Sheet2Number", typeof(int));
                for (int i = 0; i < sheet1.Rows.Count; i++)
                {
                    string a1 = sheet1.Rows[i]["A"].ToString();
                    string a2= sheet2.Rows[i]["A"].ToString();
                    string b1 = sheet1.Rows[i]["B"].ToString();
                    string b2 = sheet2.Rows[i]["B"].ToString();
                    string c1 = sheet1.Rows[i]["C"].ToString();
                    string c2 = sheet2.Rows[i]["C"].ToString();
                    string d1 = sheet1.Rows[i]["D"].ToString();
                    string d2 = sheet2.Rows[i]["D"].ToString();
                    string e1 = sheet1.Rows[i]["E"].ToString();
                    string e2 = sheet2.Rows[i]["E"].ToString();
                    if(a1!=a2)
                    {
                        Console.WriteLine(a1+"**"+a2);
                        table.Rows.Add("A", "aa", Convert.ToInt32(a1), Convert.ToInt32(a2));
                    }
                    if (b1 != b2)
                    {
                        Console.WriteLine(b1 + "**" + b2);
                        table.Rows.Add("B", "bb", Convert.ToInt32(b1), Convert.ToInt32(b2));
                    }
                    if (c1 != c2)
                    {
                        Console.WriteLine(a1 + "**" + a2);
                        table.Rows.Add("C", "cc", Convert.ToInt32(c1), Convert.ToInt32(c2));
                    }
                    if (d1 != d2)
                    {
                        Console.WriteLine(a1 + "**" + a2);
                        table.Rows.Add("D", "dd", Convert.ToInt32(d1), Convert.ToInt32(d2));
                    }
                    if (e1 != e2)
                    {
                        Console.WriteLine(a1 + "**" + a2);
                        table.Rows.Add("E", "ee", Convert.ToInt32(e1), Convert.ToInt32(e2));
                    }
    
    
                }
    
                var lines = new List<string>();
    
                string[] columnNames = table.Columns.Cast<DataColumn>().
                                                  Select(column => column.ColumnName).
                                                  ToArray();
    
                var header = string.Join(",", columnNames);
                lines.Add(header);
    
                var valueLines = table.AsEnumerable()
                                   .Select(row => string.Join(",", row.ItemArray));
                lines.AddRange(valueLines);
    
                File.WriteAllLines("D:\\excel.csv", lines);
                Console.WriteLine("success");
                Console.ReadKey();
            }
            public static DataTable datatable(string path,string sheetName)
            {
                DataTable dt = new DataTable();
                string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;MAXSCANROWS=0'";
                    using (OleDbConnection conn = new OleDbConnection(connectionString))
                    {
                    using (OleDbCommand comm = new OleDbCommand())
                    {
                        //sheetName = "Sheet1";
                        comm.CommandText = "Select * from [" + sheetName + "$]";
                        comm.Connection = conn;
                        using (OleDbDataAdapter da = new OleDbDataAdapter())
                        {
                            da.SelectCommand = comm;
                            da.Fill(dt);
                            return dt;
                        }
                    }
    
                }
            }

    Result:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 21, 2019 5:58 AM
    Moderator
  • Thanks Jack.

    I am really thankful to you that you helped. 

    But there is one thing.

    You are putting manually data on the sheet.

    it should read data of sheet automatically not manually.


     

    string a1 = sheet1.Rows[i]["A"].ToString(); string a2= sheet2.Rows[i]["A"].ToString(); string b1 = sheet1.Rows[i]["B"].ToString(); string b2 = sheet2.Rows[i]["B"].ToString(); string c1 = sheet1.Rows[i]["C"].ToString(); string c2 = sheet2.Rows[i]["C"].ToString(); string d1 = sheet1.Rows[i]["D"].ToString(); string d2 = sheet2.Rows[i]["D"].ToString(); string e1 = sheet1.Rows[i]["E"].ToString(); string e2 = sheet2.Rows[i]["E"].ToString(); if(a1!=a2) { Console.WriteLine(a1+"**"+a2); table.Rows.Add("A", "aa", Convert.ToInt32(a1), Convert.ToInt32(a2)); } if (b1 != b2) { Console.WriteLine(b1 + "**" + b2); table.Rows.Add("B", "bb", Convert.ToInt32(b1), Convert.ToInt32(b2)); } if (c1 != c2) { Console.WriteLine(a1 + "**" + a2); table.Rows.Add("C", "cc", Convert.ToInt32(c1), Convert.ToInt32(c2)); } if (d1 != d2) { Console.WriteLine(a1 + "**" + a2); table.Rows.Add("D", "dd", Convert.ToInt32(d1), Convert.ToInt32(d2)); } if (e1 != e2) { Console.WriteLine(a1 + "**" + a2); table.Rows.Add("E", "ee", Convert.ToInt32(e1), Convert.ToInt32(e2)); }

    on this code.

    Heading of sheets are always different. and maybe on this case there is 5 columns. but on another sheet there is

    7 columns.

    You are adding A to sheet 1 then sheet 2. then compare the with specific value.

    Values of heading are always different. Like on one excel sheet it is A And aa

    May be on new excel sheet there is X and xx. then how can i add, i mean it should read data of sheet automatically.

    Example.

    case1:

    Sheet1

    ABCDE
    aabbccddee

    Sheet2

    ABCDE F g
    aabbccddee ff gg

    In this i have to give different data as well as data which is not in sheet1.

    case2:

    Sheet1

    XYZWM N O P
    aabbccddee ff gg hh

    1 2 3 6 4 6 6 4

    2 5 6 4 6 6 4 6

    SHEET2:

    XYZWM N O P Q
    aaffccddee ff gg ll hh

    1 2 4 6 4 6 6 4 6

    2 5 7 4 6 6 4 6 2

    Here Heading Y is on both the sheets but 2nd heading value is different(ff and bb) so it will not go futher to check the value.

    And Same for P(it have hh value in sheet1 and ll in sheet 2). so no need to check for value

    i mean no need to check for 4 and 6.

    Same for Y.

    Thanks in advance.

    Tuesday, May 21, 2019 9:14 PM
  • Hi adwikcool,

    Thanks for the feedback.

    According to your advice, I have modified my code.

    You could try the following code.

     static void Main(string[] args)
            {
                DataTable sheet1 = datatable("D:\\test.xlsx", "Sheet1");
                DataTable sheet2 = datatable("D:\\test.xlsx", "Sheet2");
                DataTable table = new DataTable();
                table.Columns.Add("FirstName", typeof(string));
                table.Columns.Add("Sheet1Number", typeof(int));
                table.Columns.Add("Sheet2Number", typeof(int));
                string test1 = null;
                string test2 = null;
                foreach (DataColumn column in sheet1.Columns)
                {
                    string  a=column.ColumnName;
                    foreach (DataColumn column1 in sheet2.Columns)
                    {
                        string b = column.ColumnName;
                        if(a==b)
                        {
                            List<object> lst1 = (from d in sheet1.AsEnumerable() select d.Field<object>(a)).ToList();
                            List<object> lst2 = (from d in sheet2.AsEnumerable() select d.Field<object>(a)).ToList();
                            for (int i = 0; i < lst1.Count; i++)
                            {
                                for (int j = i; j < lst2.Count; j++)
                                {
                                    if((i==j)&&(!lst1.SequenceEqual(lst2)))
                                    {
                                        if(lst1[i].ToString() != lst2[j].ToString())
                                        {
                                            test1 = lst1[i].ToString();
                                            test2= lst2[i].ToString();
                                            table.Rows.Add(a, test1, test2);
                                        }
                                        
    
                                    }
                                }
                            }
    
                           
                        }
    
                    }
                }
               
                DataView dv = new DataView(table);
                table = dv.ToTable(true);
    
                var lines = new List<string>();
    
                string[] columnNames = table.Columns.Cast<DataColumn>().
                                                  Select(column => column.ColumnName).
                                                  ToArray();
    
                var header = string.Join(",", columnNames);
                lines.Add(header);
    
                var valueLines = table.AsEnumerable()
                                   .Select(row => string.Join(",", row.ItemArray));
                lines.AddRange(valueLines);
    
                File.WriteAllLines("D:\\excel.csv", lines);
                Console.WriteLine("success");
                Console.ReadKey();
            }
            public static DataTable datatable(string path, string sheetName)
            {
                DataTable dt = new DataTable();
                string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;MAXSCANROWS=0'";
                using (OleDbConnection conn = new OleDbConnection(connectionString))
                {
                    using (OleDbCommand comm = new OleDbCommand())
                    {
                        //sheetName = "Sheet1";
                        comm.CommandText = "Select * from [" + sheetName + "$]";
                        comm.Connection = conn;
                        using (OleDbDataAdapter da = new OleDbDataAdapter())
                        {
                            da.SelectCommand = comm;
                            da.Fill(dt);
                            return dt;
                        }
                    }
    
                }
            }

    Result:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 22, 2019 8:09 AM
    Moderator
  • Thank again Jack for you help.

    Its working fine for the 1st column name. but its not working with 2nd column name. 

    I mean. If A from Sheet1 are same to Sheet2. then it will not check for aa and aa. it ignores that value.

    For example:

    Sheet1:

    A B C D E
    aa ff cc dd

    ee

    A B C D E
    aa bb cc dd ee

    Here in case of B.B and B are same. but ff and bb are not same. if i run on this it will ignore that and give the only different value.

    and There is also not a SecondName Column is not there on the result.CSV

    Thanks in advance.

    Wednesday, May 22, 2019 12:42 PM
  • and one more thing Jack, 

    i have checked on your code. you are taking the column name

    string b = column.ColumnName;

     Like this. 

    Is it possible to give the Exact column number instead taking the columnName?

    Wednesday, May 22, 2019 12:45 PM
  • Hi Jack,

    Any update on this?

    Thanks.

    Friday, May 24, 2019 5:38 AM
  • Hi adwikcool,

    Thanks for the feedback.

    I think my code has solved your problem. I want to know what your current question is.

    >>There is also not a SecondName Column is not there on the result.CSV

    I only want to let you know the difference between sheet1 and sheet2.

    >>Is it possible to give the Exact column number instead taking the columnName?

    The reason that I take the columnName is that we need find the same column name between sheet1 and sheet2, then we could compare the value in that column. Because the column name order in sheet1 may be different with sheet2, I don't use the exact column number.

    Hope my explanation could be helpful.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 24, 2019 6:46 AM
    Moderator
  • Hi Jack, 

    Thanks for helping me.

    Yes your code gives the difference between both the sheets.

    But i need the difference as well as SecondName(2nd heading comparison) too.

    Thats why i mentioned on my first message all the requirements where i stuck and didnot found any results.

    So please help on this.

    Thanks again for your help.

    Monday, May 27, 2019 4:12 AM
  • Your first code was perfect but you were adding all the values maunally.

    But it should be add automatically because there are many values as i dont add it manully and these values are changes everytime..

    Monday, May 27, 2019 4:15 AM
  • Hi adwikcool,

    >>Your first code was perfect but you were adding all the values manually.

    Yes, you are right. According to your advice, I modified code to add all the values automatically. Therefore, I am not sure what you want now, if allowed, you can make a video or gif to express the meaning.

    Best Regards,

    Jack



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, May 28, 2019 7:36 AM
    Moderator
  • Hi Jack,

    Thanks for helping.

    Sheet 1
    A B C D E
    aa bb cc dd ee
             
    1 2 4 5 7
    1 3 5 6 8


    Sheet 2
    E B A C D
    ee bb bb cc dd
             
    7 4 1 4 5
    8 3 1 55

    6

    RESULT Sheet

    FirstHeading   SecondHeading   ValueinSheet1  ValueinSheet2

    B                             bb                           2                              4

    These are the sheets.

    i have 2 headings on the basis of that comparison starts.

    As in case of A, Headings in Sheet1 are A and aa and Heading in sheet2 are A and bb. as A and A are same but aa and bb are not same so it will not check for further values(A'Columns Value).

    In Case of B, Headings in Sheet 2 are B and bb and headings in sheet2 are B and bb. B and B are same and bb and bb are same. now it will check for the difference in the vlaues(B'Coumns value).

    2 .....with 4 not same so it will reflect on the result sheet.(firstHeading = B and SecondHeading=bb and ValuesinSheet1 = 2 and valueinSheet2 = 4).

    3 and 3 are same. 

    Now you understand what is the input?

    i want  to do all this automatically. On your 1st code. you added 2nd value Manually. i need to put all these automatically. As i have many values so i cannot able to add manually.

    Let me know if you still dont get the input.

    Thanks again.

    Tuesday, May 28, 2019 7:47 PM
  • Hi adwikcool,

    I have modified my code according to your advice, you could try the following code.

        class Program
        {
            static void Main(string[] args)
            {
                DataTable sheet1 = datatable("D:\\Test1\\test.xlsx", "Sheet1");
                DataTable sheet2 = datatable("D:\\Test1\\test.xlsx", "Sheet2");
                DataTable table = new DataTable();
                table.Columns.Add("FirstHeading", typeof(string));
                table.Columns.Add("SecondHeading", typeof(string));
                table.Columns.Add("ValueinSheet1", typeof(int));
                table.Columns.Add("ValueinSheet2", typeof(int));
                string test1 = null;
                string test2 = null;
                foreach (DataColumn column in sheet1.Columns)
                {
                    string a = column.ColumnName;
                    foreach (DataColumn column1 in sheet2.Columns)
                    {
                        string b = column.ColumnName;
                        if (a == b)
                        {
                            List<object> lst1 = (from d in sheet1.AsEnumerable() select d.Field<object>(a)).ToList();
                            List<object> lst2 = (from d in sheet2.AsEnumerable() select d.Field<object>(a)).ToList();
                            for (int i = 0; i < lst1.Count; i++)
                            {
                                for (int j = i; j < lst2.Count; j++)
                                {
                                    if ((i == j) && (!lst1.SequenceEqual(lst2)))
                                    {
                                        
                                        if (lst1[i].ToString() != lst2[j].ToString())
                                        {
                                            test1 = lst1[i].ToString();
                                            test2 = lst2[i].ToString();
                                            string name1 = sheet1.Rows[0][a].ToString();
                                            string name2 = sheet2.Rows[0][a].ToString();
                                            if(name1==name2)
                                            {
                                                table.Rows.Add(a, name1,test1, test2);
                                            }
    
                                         
                                        }
    
    
                                    }
                                }
                            }
    
    
                        }
    
                    }
                }
    
                DataView dv = new DataView(table);
                table = dv.ToTable(true);
    
                var lines = new List<string>();
    
                string[] columnNames = table.Columns.Cast<DataColumn>().
                                                  Select(column => column.ColumnName).
                                                  ToArray();
    
                var header = string.Join(",", columnNames);
                lines.Add(header);
    
                var valueLines = table.AsEnumerable()
                                   .Select(row => string.Join(",", row.ItemArray));
                lines.AddRange(valueLines);
    
                File.WriteAllLines("D:\\excel.csv", lines);
                Console.WriteLine("success");
                Console.ReadKey();
            }
            public static DataTable datatable(string path, string sheetName)
            {
                DataTable dt = new DataTable();
                string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;MAXSCANROWS=0'";
                using (OleDbConnection conn = new OleDbConnection(connectionString))
                {
                    using (OleDbCommand comm = new OleDbCommand())
                    {
                        comm.CommandText = "Select * from [" + sheetName + "$]";
                        comm.Connection = conn;
                        using (OleDbDataAdapter da = new OleDbDataAdapter())
                        {
                            da.SelectCommand = comm;
                            da.Fill(dt);
                            return dt;
                        }
                    }
    
                }
            }
        }

    Result:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 29, 2019 3:29 AM
    Moderator