How do we highlight the cells in same excel template c#

Question

Currently i am using the defined template for maintaining our data.

For example, above mentioned screenshot is the Initial Template (DataXLFile.xlsx).

After processing some calculation, updating the values in the same template ((DataXLFile.xlsx).

When printing the excel in the same template, we have to Highlight the difference of the value in every cell if anything changes before and after calculation.

Also I have "n" number of columns to fulfill the requirement in both table. (anyhow both columns are same).

In some cases with this requirement, I just want to exclude some columns.

for example, i just want to exclude some of the columns like "M", "AA","CA",. (because, these are not required to highlight the columns)

How to exclude the columns while printing in excel.

pls. let me know if you need any more details.

Answer 1

To make a copy of a file is easy, just use the File.Copy Method directly.

So, the problem now is that the source of the data is two files, right?

I modified and organized the code. Please check if it suits you now.

    class Program  
    {  
        static Application application;  
        static Workbook workbook;  
        static Worksheet xlWorksheet;  
        public static object[,] GetData(string path,string rangeStart,string rangeEnd)  
        {  
             
            application = new Application();  
            workbook = application.Workbooks.Open(path);  
            Console.WriteLine(workbook.FullName);  
            xlWorksheet = (Worksheet)workbook.Sheets[1];  
  
            Range range1 = xlWorksheet.get_Range(rangeStart, rangeEnd);  
            object[,] valueArray = (object[,])range1.get_Value(XlRangeValueDataType.xlRangeValueDefault);  
  
  
            return valueArray;  
        }  
        public static List<Tuple<int, int>> Compare(object[,] valueArray, object[,] valueArray2)  
        {  
  
            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] == null || valueArray2[i, j].ToString() == null) continue;  
                    if (valueArray[i, j].ToString() != valueArray2[i, j].ToString())  
                    {  
                        res.Add(new Tuple<int, int>(i, j));  
                    }  
                }  
            }  
            return res;  
        }  
  
        public static void WriteToFile(object[,] data, List<Tuple<int, int>> cellsToChangeColor,string rangeStart,string rangeEnd)   
        {  
            Range range = xlWorksheet.get_Range(rangeStart, rangeEnd);  
            range.set_Value(XlRangeValueDataType.xlRangeValueDefault, data);  
            xlWorksheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange, range,  
           Type.Missing, XlYesNoGuess.xlYes, Type.Missing).Name = "TestTable";  
            xlWorksheet.ListObjects["TestTable"].TableStyle = "TableStyleMedium2";  
  
            List<char> columnToExclude = new List<char>() { 'D', 'H', 'I' };  
            foreach (var item in cellsToChangeColor)  
            {  
                char column = (char)(item.Item2 + 64);  
                if (columnToExclude.Contains(column)) continue;  
                string cell = (char)(item.Item2 + 64) + item.Item1.ToString();  
                range.Range[cell].Font.Color = ColorTranslator.ToOle(Color.Red);  
            }  
        }  
        static void Main(string[] args)  
        {  
            try  
            {  
                object[,] valueArray = GetData(@"D:\test\excel\test11_copy.xlsx", "A6", "O25");  
                object[,] valueArray2 = GetData(@"D:\test\excel\test11.xlsx", "A6", "O25");  
  
                List<Tuple<int, int>> res = Compare(valueArray, valueArray2);  
                WriteToFile(valueArray2, res, "A28", "O47");  
                Console.ReadLine();  
            }  
            catch (Exception e)  
            {  
                Console.WriteLine(e);  
            }  
            finally  
            {  
                workbook.Save();  
                workbook.Close();  
                application.Quit();  
            }  
        }  
    }  

Update:

If the column in the file exceeds 26, and the column name consists of two characters (like BA), the WriteToFile method needs to be modified to the following:

            List<string> columnToExclude = new List<string>() { "D", "H", "I" };  
            foreach (var item in cellsToChangeColor)  
            {  
                string column;  
                if (item.Item2 + 64 <= 90 )  
                {  
                    column = ((char)(item.Item2 + 64)).ToString();  
                }  
                else  
                {  
                    char a = 'A';  
                    column = (char)(a + item.Item2/26 - 1) + "" + (char)(a + item.Item2 % 26 - 1);  
                }                     
  
                if (columnToExclude.Contains(column)) continue;  
                string cell = column + item.Item1.ToString();  
                range.Range[cell].Font.Color = ColorTranslator.ToOle(Color.Red);  
            }  

Could the column exceed 702?
In that case, the column name will have three characters, and the code still needs to be modified to obtain the correct column name.

---

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.