none
How to search particular text in DESTINATIONSHEET and find the difference value in the corresponding rows RRS feed

  • Question

  • How to search particular text in DESTINATIONSHEET and find the difference value in the corresponding rows

    I have two workbook (SOURCE AND DESTINATION SHEETS). First i have to find matching sheets in the both workbook.

    After matching worksheets, we have to take column C ("UID") of SOURCESHEET and search the value in DESTINATIONSHEET of column C ("UID").

    For example, consider value 101-ABC-201 and search in DESTINATIONSHEET (1.1 => Sheetname) of column C ("UID"), then if the value is exists in that column,
    then we need consider the entire row and check every cell and find the difference value of every cell.

    If any difference exists, then we need to highlight the color in SOURCESHEET..

    How to search the text in particular column of DESTINATIONSHEET...?

    Sample test file enclosed and highlighted some difference.

    https://1drv.ms/f/s!AiSRcgO5FUmNdbySD6gz4PwTbzk

    Test File


    Thursday, January 3, 2019 12:23 PM

Answers

  • Hi  Gani tpt,

    Thank you for posting here.

    According to your issue, Please try this code below.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using Excel = Microsoft.Office.Interop.Excel;
    
    namespace test2
    {
        class Program
        {
            static void Main(string[] args)
            {
                Excel.Application excel = new Excel.Application();
                excel.Visible = false;
                Excel.Workbook workbook1 = null;
                Excel.Workbook workbook2 = null;
                string path1 = @"DESTINATIONSHEET.xlsx";
                string path2 = @"SOURCESHEET.xlsx";
                workbook1 = excel.Workbooks.Open(path1);
                workbook2 = excel.Workbooks.Open(path2);
                Excel.Worksheet worksheet1 = (Excel.Worksheet)workbook1.Worksheets[1];
                Excel.Worksheet worksheet2 = (Excel.Worksheet)workbook2.Worksheets[1];
                int lastRow =worksheet1.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
                int lastcol = worksheet1.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Column;
                int lastRow1 = worksheet2.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
                int lastcol1 = worksheet2.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Column;
                int m = 0;
                int n = 0;
                for (int i = 1; i < lastRow; i++)
                {
                    for (int j = 1; j < lastcol; j++)
                    {
                        if (Convert.ToString(worksheet1.Cells[i, j].value) =="UID")
                        {
                            m = j;
                            n = i;
                            Console.WriteLine(j); 
                        }
                     
                    }
                }
                List<string> list1 = new List<string>();
                for (int i = n; i <= lastRow; i++)
                {
                    if (Convert.ToString(worksheet1.Cells[i, m].value) != null)
                    {
                        list1.Add(Convert.ToString(worksheet1.Cells[i, m].value));
                    }
                }
                int m1 = 0;
                int n1 = 0;
                for (int i = 1; i < lastRow1; i++)
                {
                    for (int j = 1; j < lastcol1; j++)
                    {
                        if (Convert.ToString(worksheet2.Cells[i, j].value) == "UID")
                        {
                            m1 = j;
                            n1 = i;
                            Console.WriteLine(j);
                        }
    
                    }
                }
                List<string> list2 = new List<string>();
                for (int i = n1; i <= lastRow1; i++)
                {
                    if (Convert.ToString(worksheet2.Cells[i, m1].value) != null)
                    {
                        list2.Add(Convert.ToString(worksheet2.Cells[i, m1].value));
                    }
                }
                List<string> list3 = new List<string>();
                int q;
                foreach (var item in list1)
                {
                    foreach (var item1 in list2)
                    {
                        if (item == item1)
                        {
                            list3.Add(item);
    
                        }
    
                    }
                }
                foreach (var item in list3)
                {
                    for (int i = 1; i <lastRow1; i++)
                    {
                        for (int j = 1; j <lastcol1; j++)
                        {
                            if (item == Convert.ToString(worksheet2.Cells[i, j].value))
                            {
                                q = i;
    
                                if (item == Convert.ToString(worksheet1.Cells[i, j].value))
                                {
    
                                    int k = i;
                                    for (int s = 1; s < lastcol1; s++)
                                    {
                                        if (worksheet1.Cells[k, s].value != null && worksheet2.Cells[q, s].value != null)
                                        {
                                            string test1 = Convert.ToString(worksheet1.Cells[k, s].value);
                                            string test2 = Convert.ToString(worksheet2.Cells[q, s].value);
                                            if (test1 != test2)
                                            {
                                                ((Excel.Range)worksheet1.Cells[k, s]).Interior.Color = 255;
                                                ((Excel.Range)worksheet2.Cells[q, s]).Interior.Color = 5296274;
                                            }
                                        }
    
                                    }
                                }
                            }
                        }
                    }
                }
                workbook1.Save();
                workbook1.Close();
                workbook2.Save();
                workbook2.Close();
    
            }
        }
    }
    

    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.

    • Marked as answer by Gani tpt Monday, January 7, 2019 9:32 AM
    Friday, January 4, 2019 10:13 AM
    Moderator
  • Hi,

    Check the following solution:

    using System.Drawing;
    using Spire.Xls;
    
    namespace test
    {
        class Program
        {
            static void Main(string[] args)
            {                  
                //Load DESTINATIONSHEET.xlsx
                Workbook destWorkbook = new Workbook();
                destWorkbook.LoadFromFile("DESTINATIONSHEET.xlsx");
                //Get the matched sheet "1.1"
                Worksheet destSheet = destWorkbook.Worksheets["1.1"];
                //Find the string
                CellRange destRange = destSheet.Columns[2].FindString("101-ABC-201", false, false);
                //Get the index of the row that contains the string
                int destRowIndex = destRange.Row - 1;
    
                //Load SOURCESHEET.xlsx
                Workbook sourceWorkbook = new Workbook();
                sourceWorkbook.LoadFromFile("SOURCESHEET.xlsx");
                ////Get the matched sheet "1.1"
                Worksheet sourceSheet = sourceWorkbook.Worksheets["1.1"];
                //Find the string
                CellRange sourceRange = sourceSheet.Columns[2].FindString("101-ABC-201", false, false);
                //Get the index of the row that contains the string
                int sourceRowIndex = sourceRange.Row - 1;
    
                //Compare and Highlight different values
                for (int i = 0; i < sourceSheet.Rows[sourceRowIndex].Cells.Count(); i++)
                {
                    if (sourceSheet.Rows[sourceRowIndex].Cells[i].Value != destSheet.Rows[destRowIndex].Cells[i].Value)
                    {
                        sourceSheet.Rows[sourceRowIndex].Cells[i].Style.Color = Color.Red;
                    }
                }
                             
                sourceWorkbook.SaveToFile("SOURCESHEET-1.xlsx", ExcelVersion.Version2013);
            }
        }
    }


    • Edited by Michael Brrr Monday, January 7, 2019 7:41 AM
    • Marked as answer by Gani tpt Monday, January 7, 2019 9:32 AM
    Monday, January 7, 2019 7:39 AM

All replies

  • Hello,

    Are the other threads related? If so you should mention them for those who want to assist have a background on what you are doing.

    Thread 1, thread 2.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, January 3, 2019 12:53 PM
    Moderator
  • Thanks.

    This is different thread and we are now for the excel related works.


    • Edited by Gani tpt Friday, January 4, 2019 12:07 AM
    Thursday, January 3, 2019 1:18 PM
  • So that means you are using a totally different approach to read data? If so please provide current source code to what you have written so far.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, January 3, 2019 1:37 PM
    Moderator
  • Hi  Gani tpt,

    Thank you for posting here.

    According to your issue, Please try this code below.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using Excel = Microsoft.Office.Interop.Excel;
    
    namespace test2
    {
        class Program
        {
            static void Main(string[] args)
            {
                Excel.Application excel = new Excel.Application();
                excel.Visible = false;
                Excel.Workbook workbook1 = null;
                Excel.Workbook workbook2 = null;
                string path1 = @"DESTINATIONSHEET.xlsx";
                string path2 = @"SOURCESHEET.xlsx";
                workbook1 = excel.Workbooks.Open(path1);
                workbook2 = excel.Workbooks.Open(path2);
                Excel.Worksheet worksheet1 = (Excel.Worksheet)workbook1.Worksheets[1];
                Excel.Worksheet worksheet2 = (Excel.Worksheet)workbook2.Worksheets[1];
                int lastRow =worksheet1.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
                int lastcol = worksheet1.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Column;
                int lastRow1 = worksheet2.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
                int lastcol1 = worksheet2.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Column;
                int m = 0;
                int n = 0;
                for (int i = 1; i < lastRow; i++)
                {
                    for (int j = 1; j < lastcol; j++)
                    {
                        if (Convert.ToString(worksheet1.Cells[i, j].value) =="UID")
                        {
                            m = j;
                            n = i;
                            Console.WriteLine(j); 
                        }
                     
                    }
                }
                List<string> list1 = new List<string>();
                for (int i = n; i <= lastRow; i++)
                {
                    if (Convert.ToString(worksheet1.Cells[i, m].value) != null)
                    {
                        list1.Add(Convert.ToString(worksheet1.Cells[i, m].value));
                    }
                }
                int m1 = 0;
                int n1 = 0;
                for (int i = 1; i < lastRow1; i++)
                {
                    for (int j = 1; j < lastcol1; j++)
                    {
                        if (Convert.ToString(worksheet2.Cells[i, j].value) == "UID")
                        {
                            m1 = j;
                            n1 = i;
                            Console.WriteLine(j);
                        }
    
                    }
                }
                List<string> list2 = new List<string>();
                for (int i = n1; i <= lastRow1; i++)
                {
                    if (Convert.ToString(worksheet2.Cells[i, m1].value) != null)
                    {
                        list2.Add(Convert.ToString(worksheet2.Cells[i, m1].value));
                    }
                }
                List<string> list3 = new List<string>();
                int q;
                foreach (var item in list1)
                {
                    foreach (var item1 in list2)
                    {
                        if (item == item1)
                        {
                            list3.Add(item);
    
                        }
    
                    }
                }
                foreach (var item in list3)
                {
                    for (int i = 1; i <lastRow1; i++)
                    {
                        for (int j = 1; j <lastcol1; j++)
                        {
                            if (item == Convert.ToString(worksheet2.Cells[i, j].value))
                            {
                                q = i;
    
                                if (item == Convert.ToString(worksheet1.Cells[i, j].value))
                                {
    
                                    int k = i;
                                    for (int s = 1; s < lastcol1; s++)
                                    {
                                        if (worksheet1.Cells[k, s].value != null && worksheet2.Cells[q, s].value != null)
                                        {
                                            string test1 = Convert.ToString(worksheet1.Cells[k, s].value);
                                            string test2 = Convert.ToString(worksheet2.Cells[q, s].value);
                                            if (test1 != test2)
                                            {
                                                ((Excel.Range)worksheet1.Cells[k, s]).Interior.Color = 255;
                                                ((Excel.Range)worksheet2.Cells[q, s]).Interior.Color = 5296274;
                                            }
                                        }
    
                                    }
                                }
                            }
                        }
                    }
                }
                workbook1.Save();
                workbook1.Close();
                workbook2.Save();
                workbook2.Close();
    
            }
        }
    }
    

    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.

    • Marked as answer by Gani tpt Monday, January 7, 2019 9:32 AM
    Friday, January 4, 2019 10:13 AM
    Moderator
  • Hi,

    Check the following solution:

    using System.Drawing;
    using Spire.Xls;
    
    namespace test
    {
        class Program
        {
            static void Main(string[] args)
            {                  
                //Load DESTINATIONSHEET.xlsx
                Workbook destWorkbook = new Workbook();
                destWorkbook.LoadFromFile("DESTINATIONSHEET.xlsx");
                //Get the matched sheet "1.1"
                Worksheet destSheet = destWorkbook.Worksheets["1.1"];
                //Find the string
                CellRange destRange = destSheet.Columns[2].FindString("101-ABC-201", false, false);
                //Get the index of the row that contains the string
                int destRowIndex = destRange.Row - 1;
    
                //Load SOURCESHEET.xlsx
                Workbook sourceWorkbook = new Workbook();
                sourceWorkbook.LoadFromFile("SOURCESHEET.xlsx");
                ////Get the matched sheet "1.1"
                Worksheet sourceSheet = sourceWorkbook.Worksheets["1.1"];
                //Find the string
                CellRange sourceRange = sourceSheet.Columns[2].FindString("101-ABC-201", false, false);
                //Get the index of the row that contains the string
                int sourceRowIndex = sourceRange.Row - 1;
    
                //Compare and Highlight different values
                for (int i = 0; i < sourceSheet.Rows[sourceRowIndex].Cells.Count(); i++)
                {
                    if (sourceSheet.Rows[sourceRowIndex].Cells[i].Value != destSheet.Rows[destRowIndex].Cells[i].Value)
                    {
                        sourceSheet.Rows[sourceRowIndex].Cells[i].Style.Color = Color.Red;
                    }
                }
                             
                sourceWorkbook.SaveToFile("SOURCESHEET-1.xlsx", ExcelVersion.Version2013);
            }
        }
    }


    • Edited by Michael Brrr Monday, January 7, 2019 7:41 AM
    • Marked as answer by Gani tpt Monday, January 7, 2019 9:32 AM
    Monday, January 7, 2019 7:39 AM
  • Thanks. The answers seems good and rock..
    Monday, January 7, 2019 9:33 AM
  • Thanks...it's good..
    Monday, January 7, 2019 9:34 AM