none
Not able to read merged cells in the worksheet using C#.net RRS feed

  • Question

  • I am working with worksheet comparisons.

    In my worksheet, some of the cells had been merged.

    when reading every cell it's working fine. But, when the cells are merged then it is not reading the exact cell value.

    It's showing some other value which is not available in the excel.

    How to check or handle merged cells..?

    see my complete source code 

     

    strRangeToCheck = "A10:JB10";

    var r = XLWS1.Range[strRangeToCheck]; lastLine = Math.Max( XLWS1.Range[strRangeToCheck].Rows.Count, XLWS2.Range[strRangeToCheck].Rows.Count); for (int row = r.Row; row < r.Row + lastLine; row++) { int lastCol = Math.Max( XLWS1.Range[strRangeToCheck].Columns.Count, XLWS2.Range[strRangeToCheck].Columns.Count); for (int column = r.Column; column < r.Column + lastCol; column++) { string currRange = null; string PrevRange = null; currRange = XLWS1.Cells[row, column].Merged; PrevRange = XLWS2.Cells[row, column].Merged; string Currval = null; string Prevval = null; //int? value = new double?(); Currval = Convert.ToString(XLWS1.Cells[row, column].Value); Prevval = Convert.ToString(XLWS2.Cells[row, column].Value); // Check for if the object is null. if ((Currval == null) || (Prevval == null)) { } else { if (Currval != Prevval) { ((Excel.Range)XLWS1.Cells[row, column]).Interior.Color = 255; ((Excel.Range)XLWS2.Cells[row, column]).Interior.Color = 5296274; } } } }


    Thursday, January 3, 2019 4:42 AM

Answers

  • Thanks for your reply..

    The above mentioned code is working. Some of the columns are hidden in the excel. 

    So, the code read hidden column values.

    it's working.

    XLWS1 --> Worksheet1

    XLWS2 --> Worksheet2

    Final code is below.

    strRangeToCheck = "A10:JB10";
    var r = XLWS1.Range[strRangeToCheck];
    
                                    lastLine = Math.Max(
                                          XLWS1.Range[strRangeToCheck].Rows.Count,
                                          XLWS2.Range[strRangeToCheck].Rows.Count);
    
                                    for (int row = r.Row; row < r.Row + lastLine; row++)
                                    {
                                        int lastCol = Math.Max(
                                            XLWS1.Range[strRangeToCheck].Columns.Count,
                                            XLWS2.Range[strRangeToCheck].Columns.Count);
    
                                        for (int column = r.Column; column < r.Column + lastCol; column++)
                                        {
                                            
                                            string Currval = null;
                                            string Prevval = null;
                                          
    
                                            Currval = Convert.ToString(XLWS1.Cells[row, column].Value);
                                            Prevval = Convert.ToString(XLWS2.Cells[row, column].Value);
    
                                            // Check for if the object is null.
                                            if ((Currval == null) || (Prevval == null))
                                            {
    
                                            }
                                            else
                                            {
                                                if (Currval != Prevval)
                                                {
                                                    ((Excel.Range)XLWS1.Cells[row, column]).Interior.Color = 255;
                                                    ((Excel.Range)XLWS2.Cells[row, column]).Interior.Color = 5296274;
                                                }
                                            }
    
                                        }
    
                                    }

    • Marked as answer by Gani tpt Thursday, January 3, 2019 6:16 AM
    Thursday, January 3, 2019 6:16 AM

All replies

  • Hi Gani tpt,

    According to your description and related code. I am not sure what is XLWS1, is it a 3rd-party object or others?

    Best regards,

    Zhanglong


    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.

    Thursday, January 3, 2019 6:07 AM
    Moderator
  • Thanks for your reply..

    The above mentioned code is working. Some of the columns are hidden in the excel. 

    So, the code read hidden column values.

    it's working.

    XLWS1 --> Worksheet1

    XLWS2 --> Worksheet2

    Final code is below.

    strRangeToCheck = "A10:JB10";
    var r = XLWS1.Range[strRangeToCheck];
    
                                    lastLine = Math.Max(
                                          XLWS1.Range[strRangeToCheck].Rows.Count,
                                          XLWS2.Range[strRangeToCheck].Rows.Count);
    
                                    for (int row = r.Row; row < r.Row + lastLine; row++)
                                    {
                                        int lastCol = Math.Max(
                                            XLWS1.Range[strRangeToCheck].Columns.Count,
                                            XLWS2.Range[strRangeToCheck].Columns.Count);
    
                                        for (int column = r.Column; column < r.Column + lastCol; column++)
                                        {
                                            
                                            string Currval = null;
                                            string Prevval = null;
                                          
    
                                            Currval = Convert.ToString(XLWS1.Cells[row, column].Value);
                                            Prevval = Convert.ToString(XLWS2.Cells[row, column].Value);
    
                                            // Check for if the object is null.
                                            if ((Currval == null) || (Prevval == null))
                                            {
    
                                            }
                                            else
                                            {
                                                if (Currval != Prevval)
                                                {
                                                    ((Excel.Range)XLWS1.Cells[row, column]).Interior.Color = 255;
                                                    ((Excel.Range)XLWS2.Cells[row, column]).Interior.Color = 5296274;
                                                }
                                            }
    
                                        }
    
                                    }

    • Marked as answer by Gani tpt Thursday, January 3, 2019 6:16 AM
    Thursday, January 3, 2019 6:16 AM