question

GaniTPT avatar image
0 Votes"
GaniTPT asked GaniTPT edited

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

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

62153-image.png

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

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

62154-image.png

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.

62165-image.png

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.


dotnet-csharp
image.png (5.6 KiB)
image.png (5.6 KiB)
image.png (6.7 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pls. update when you are free.

0 Votes 0 ·

1 Answer

TimonYang-MSFT avatar image
1 Vote"
TimonYang-MSFT answered GaniTPT edited

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.


capture.png (27.6 KiB)
· 20
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You are great and thanks.
one small correction here. I am using only one defined template which is received from the client.
Instead of the below code, i just want to take copy of the template and compare after did some calculation

          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);

For example,
i am using "test1.xlsx" and it contains some data. (Here need to take the backup of the excel file (ex: test1_bkp.xlsx.)
after some business caluculation, we need to update the data for the same template.
now, we need to comparte the two xls file (test1_bkp.xlsx and test1.xlsx)..
If this has succeed then mine is over.

0 Votes 0 ·

can you help us in the last reply.??

we just want to know, how to use the copy of excel file and compare those two's.

Or

tell us, How to make duplicate copy of the excel file...?

0 Votes 0 ·

@GaniTPT
I want to confirm something.
At first there is only one table, you get the data of the table, modify some data, then put the modified data in the second excel file, and then perform the comparison, right?
We execute the above code for comparison only to know the location of the modified data, but if we modify the data manually now, the comparison becomes a redundant operation.

0 Votes 0 ·
Show more comments