none
using c# to compare 2 worksheet, row by row RRS feed

  • Question

  • hi guys. i would like to ask, how could i compare 2 excel worksheets, through row by row, and comparing in a specific column. lets say, i have a excel source worksheet and a excel template worksheet. before i copy from source wksheet, i would like to check for duplicate between the 2 wksheet, using a specific column to compare. if no duplicate, then it would copy the whole row then paste on the template worksheet. 

    i have this code so far and its working well: 

     // open the source sheet
                sourceworkbook = app.Workbooks.Open(sourcestr1);
                Excel.Sheets excelSheets1 = sourceworkbook.Worksheets;
                sourceworksheet = (Excel.Worksheet)excelSheets1.get_Item(sourcesheet);

    // open the destination sheet
                destinationworkbook = app.Workbooks.Open(sourcestr2);
                Excel.Sheets excelSheets2 = destinationworkbook.Worksheets;
                destinationWorksheet = (Excel.Worksheet)excelSheets2.get_Item(destsheet);

     // Detect Last used Column 
                        int colcount1 = sourceworksheet.UsedRange.Columns.Count;
                        int rowcount1 = sourceworksheet.UsedRange.Rows.Count;

     // Detect Last used Row / Column 
                        int colcount2 = destinationWorksheet.UsedRange.Columns.Count;
                        int rowcount2 = destinationWorksheet.UsedRange.Rows.Count;

     

    //get the whole range/value of the worksheet 

    for (int rowCounter = 1; rowCounter <= rowcount2; rowCounter++)
                        {
                            for (int colCounter = 1; colCounter <= colcount2; colCounter++)
                             {
                                var cellVal = destinationWorksheet.UsedRange.Cells[rowCounter, colCounter];
                               var val = cellVal.Text;

                              }

                          }

    with all these, how do i proceed to compare the 2 worksheets? 

    • Moved by Kristin Xie Friday, February 26, 2016 6:50 AM Excel related
    Friday, February 26, 2016 12:45 AM

Answers

  • Hi willam,

    Based on your code, you get each cell value of destination worksheet, I don't find the better way to compare the data, I think you need to get source worksheet’s corresponding cell value and compare it (e.g. same value, format etc...) in the inner for loop, then copy data if need.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Tuesday, March 1, 2016 3:22 AM
    Moderator

All replies

  • i am using window vs 2015. excel 2010. window form application. i am not planning to use datagrid. many thanks in advanced. thank you for reading too. 
    • Edited by willam ryan Friday, February 26, 2016 12:52 AM
    Friday, February 26, 2016 12:46 AM
  • Hi willam,

    Based on your code, your case more related to Excel development, I will move your case to that forum for better support.

    Best regards,

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, February 26, 2016 6:49 AM
  • Re:  two worksheets & remove duplicates

    Excel has a built-in 'remove duplicates' utility.
    Combine the two sheets and then run the utility.

    '---
    Jim Cone
    Portland, Oregon USA

    • Edited by James Cone Monday, March 28, 2016 3:00 PM
    Saturday, February 27, 2016 3:54 AM
  • i only want to use C# program to do this. 
    Monday, February 29, 2016 1:34 AM
  • hi sorry. my concern is using c# and not excel function. 
    Monday, February 29, 2016 1:35 AM
  • Hi willam,

    Based on your code, you get each cell value of destination worksheet, I don't find the better way to compare the data, I think you need to get source worksheet’s corresponding cell value and compare it (e.g. same value, format etc...) in the inner for loop, then copy data if need.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Tuesday, March 1, 2016 3:22 AM
    Moderator