how to highlight cells in excel from two data table using c#

Question

I have two data table.

I just want to compare the two data table of every cell value. if anything mismatch of the data table values, we have to highlight in datatable-1 and same has to be updated in excel.

How to do this..?

Answer 1

I wrote a piece of code to achieve this, and stored the result in a Table format:

 Microsoft.Office.Interop.Excel.Application application = new Microsoft.Office.Interop.Excel.Application();  
            Workbook workbook = application.Workbooks.Open(@"D:\test44\excel\test1.xlsx");  
            Worksheet xlWorksheet = (Worksheet)workbook.Sheets[1];  
  
            Range range1 = xlWorksheet.get_Range("B6", "F11");  
            object[,] valueArray = (object[,])range1.get_Value(XlRangeValueDataType.xlRangeValueDefault);  
  
            Range range2 = xlWorksheet.get_Range("H6", "L11");  
            object[,] valueArray2 = (object[,])range2.get_Value(XlRangeValueDataType.xlRangeValueDefault);  
  
            List<Tuple<int, int>> res = new List<Tuple<int, int>>();  
  
            for (int i = 1; i <= valueArray.GetLength(0); i++)  
            {  
                for (int j = 1; j <= valueArray.GetLength(1); j++)  
                {  
                    if (valueArray[i, j].ToString() != valueArray2[i, j].ToString())  
                    {  
                        res.Add(new Tuple<int, int>(i, j));  
                    }  
                }  
            }  
  
            string rangeStart = "B14";  
            string rangeEnd = "F19";  
           
            Range range = xlWorksheet.get_Range(rangeStart, rangeEnd);  
            range.set_Value(XlRangeValueDataType.xlRangeValueDefault, valueArray2);  
            xlWorksheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange, range,  
      Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "TestTable";  
            xlWorksheet.ListObjects["TestTable"].TableStyle = "TableStyleMedium3";  
             
            foreach (var item in res)  
            {  
                string cell = (char)(item.Item2 + 64) + item.Item1.ToString();  
                Console.WriteLine(cell);  
                range.Range[cell].Font.Color = ColorTranslator.ToOle(Color.Red);  
            }  
  
            workbook.Save();  
            workbook.Close();  
            application.Quit();  

For convenience, I wrote the range directly. In actual use, it needs to be passed to this method as a parameter.

It should be noted that I used range.Range[cell] in the code. The position of this "cell" is relative to the “range”object , not relative to the entire excel file. For example, if "cell" is "C3", it is actually "C16" of this sheet, which is the first highlighted cell in the picture.

And, if possible, I suggest that you also modify the initial table to the same format as the result table, so that you can use the following code to get their values without specifying the range.

            List<object[,]> tables = new List<object[,]>();  
            for (int i = 1; i <= xlWorksheet.ListObjects.Count; i++)  
            {  
                Range srcRow = xlWorksheet.ListObjects[i].Range;  
                object[,] valueArray = (object[,])srcRow.get_Value(  
    XlRangeValueDataType.xlRangeValueDefault);  
                tables.Add(valueArray);  
            }  

In this case, only one parameter is required to specify the range of the result table.

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.