none
How to find the matching or nearest value from specified excel range using C# RRS feed

  • Question

  • How to find the matching value from specified excel range using C#

    I have the excel table which contains some values.

    I want to get matching value or nearest value in the specified cell range

    For below screen shot example,

    I have the data from the range D6:I6 and D10:I10.

    In the above range, i want to find and get the matching or nearest value and its column name.

    If i pass the Input parameter value ==> 550

    output :
    then nearest value in the cell range(D6:I6 and D10:I10) is : 540 
                             column is : H

    Tuesday, March 10, 2020 11:13 AM

Answers

  • Hi Gani,

    This is my oversight, I modified the code and it should work now.

    static void Main(string[] args)
            {
                DoWork("11I", "13M");
    
            }
            public static void DoWork(string start, string end)
            {
                char[] startIndex = start.ToArray();
                char[] endIndex = end.ToArray();
    
            
                string strStartX = null;
                if (startIndex.Length > 2)
                {
                    for (int i = 0; i < startIndex.Length-1; i++)
                    {
                        strStartX += startIndex[i];
                    }
                }
                else
                {
                    strStartX = startIndex[0].ToString();
                }
                int startX = int.Parse(strStartX);
                string startY = startIndex[startIndex.Length-1].ToString();
               
                string strEndX = null;
                if (endIndex.Length > 2)
                {
                    for (int i = 0; i < endIndex.Length - 1; i++)
                    {
                        strEndX += endIndex[i];
                    }
                }
                else
                {
                    strEndX = endIndex[0].ToString();
                }
                int endX = int.Parse(strEndX);
                string endY = endIndex[endIndex.Length - 1].ToString(); ;
              
                Excel.Application xlApp = new Excel.Application();
                Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"D:\Test\Test1.xlsx");
                Excel.Worksheet sheet = xlWorkbook.ActiveSheet;
                object[,] data = sheet.Range[sheet.Cells[startX, startY], sheet.Cells[endX, endY]].Cells.Value2;
    
    
                List<int> list = new List<int>();
                foreach (var item in data)
                {
                    //In order to avoid empty cells in this range, if there is no empty cell, you can remove this judgment.
                    if (item!=null)
                    {
                        list.Add(int.Parse(item.ToString()));
                    }
                }
                Console.WriteLine("Enter a number:");
    
                bool isNumber = int.TryParse(Console.ReadLine(), out int num);
                int closest = list.OrderBy(item => Math.Abs(num - item)).First();
                Console.WriteLine(closest);
    
                // Since we already know the extent of the data in excel, we can construct a loop that determines the start and end points.
                int intStartY = (int)startIndex[startIndex.Length-1] - 64;
                int intEndY = (int)endIndex[endIndex.Length-1] - 64;
               
                for (int i = startX; i <= endX; i++)
                {
                    for (int j = intStartY; j <= intEndY; j++)
                        if ((sheet.Cells[i, j] as Range).Value.ToString() == closest.ToString())
                        {
                            int row = i;
                            // Convert it based on ascii table.
                            char column = (char)(j + 64);
                            Console.WriteLine(row + " " + column);
                        }
                }
    
                Console.ReadLine();
            }

    Edit: If possible, when entering the range, please put the number first and the letter after, otherwise you will get an error. I mean, use "6D" instead of "D6".

    Best Regards,

    Timon


    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.


    Friday, March 13, 2020 1:46 AM

All replies

  • Please post the existing code you have that isn't working.

    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, March 10, 2020 1:38 PM
    Moderator
  • Thanks CoolDadTx..

    I haven't start the code.

    I am thinking, How to start the logic.?

    Either i have to go for each loop or go to LINQ Concept..

    little bit confused

    Tuesday, March 10, 2020 1:51 PM
  • Actually I believe you should use Excel interop. I'm not a guru on this but something along the following.

    1. Create the app object for Excel and open your spreadsheet and then get the worksheet that you want.
    2. Given the worksheet use the Cells property to select the range of values you want.
    3. From there you get a Range object that you can enumerate like you would with anything else. So you can search for the "closest" cell. Given the closest cell you'll have the column/row information you need.

    There might be a faster/easier way though.


    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, March 10, 2020 1:59 PM
    Moderator
  • Can we have any sample code which is related to fit my requirement..?
    Wednesday, March 11, 2020 12:44 AM
  • Hi Gani,

    Thank you for posting here.

    This task is easily accomplished using Microsoft.Office.Interop.Excel.

    You can try the following code to get it.

            static void Main(string[] args)
            {
                Excel.Application xlApp = new Excel.Application();
                Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"D:\Test\Test.xlsx");
                Excel.Worksheet sheet = xlWorkbook.ActiveSheet;
              
                object[,] data = sheet.Range[sheet.Cells[6, 2], sheet.Cells[8, 6]].Cells.Value2;
              
                List<int> list = new List<int>();
                foreach (var item in data)
                {
                    list.Add(int.Parse(item.ToString()));
                }
                Console.WriteLine("Enter a number:");
                
                bool isNumber = int.TryParse(Console.ReadLine(),out int num);
                int closest = list.OrderBy(item => Math.Abs(num - item)).First();
    
                Console.WriteLine(closest);
              
            }

    Hope this could be helpful.

    Best Regards,

    Timon


    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.


    Wednesday, March 11, 2020 2:05 AM
  • Really thanks..

    Is there chance to get corresponding column (closest value column name (like "D" or "E" or "F",..."I") also..?

    Wednesday, March 11, 2020 7:42 AM
  • Hi Gani,

    We can use the following code to get the exact position of that number, just need to convert the position of the number form to the letter form according to the ascii table.

                // Since we already know the extent of the data in excel, we can construct a loop that determines the start and end points.
                for (int i = 6; i <= 8; i++)
                {
                    for (int j = 2; j <= 6; j++)
                        if ((sheet.Cells[i, j] as Range).Value.ToString() == closest.ToString())
                        {
                            int row = i;
                            // Convert it based on ascii table.
                            char column = (char)(j+64);
                            Console.WriteLine(row + " " + column);
                        }
                }

    Best Regards,

    Timon


    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.

    Wednesday, March 11, 2020 8:29 AM
  • Thanks.

    How do we mention the range.

    for example,

    D6:I6 and D10:I10 ==> This is my range and it is dynamic .

    instead below [6,2] and [8,6]

      object[,] data = sheet.Range[sheet.Cells[6, 2], sheet.Cells[8, 6]].Cells.Value2;

    because, we can't hard code the value. it is dynamic and range will varying based on the condition..

    shall i use below

    object[,] data = sheet.Range["D6:D10","I6:I10"].Value2;


    • Edited by Gani tpt Wednesday, March 11, 2020 10:42 AM menton dynamic range
    Wednesday, March 11, 2020 10:12 AM
  • Getting error in the below line

    if ((sheet.Cells[i, j] as Range).Value.ToString() == closest.ToString())

    Range ==> Error : The type or namespace 'Range' could not be found.

    Wednesday, March 11, 2020 11:33 AM
  • D6:I6 and D10:I10 ==> This is my range and it is dynamic .

    How do we use to get column name...?

    Wednesday, March 11, 2020 11:54 AM
  • Hi Gani,

    1. Regarding the range problem, two coordinates are enough.

    For example, "D6: I6 and D10: I10" is equivalent to "D6: I10".

    2. Dynamic problems can be solved by passing parameters.

    3. Regarding Range error, please check did you forgot to add using?

    Here is the complete modified code.

           static void Main(string[] args)
            {
                DoWork("6B", "8F");
    
            }
            public static void DoWork(string start, string end)
            {
                char[] startIndex = start.ToArray();
                char[] endIndex = end.ToArray();
    
                int startX = int.Parse(startIndex[0].ToString());
                string startY = startIndex[1].ToString();
                int endX = int.Parse(endIndex[0].ToString());
                string endY = endIndex[1].ToString();
    
                Excel.Application xlApp = new Excel.Application();
                Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"D:\Test\Test.xlsx");
                Excel.Worksheet sheet = xlWorkbook.ActiveSheet;
                object[,] data = sheet.Range[sheet.Cells[startX, startY], sheet.Cells[endX, endY]].Cells.Value2;
    
    
                List<int> list = new List<int>();
                foreach (var item in data)
                {
                    list.Add(int.Parse(item.ToString()));
    
                }
                Console.WriteLine("Enter a number:");
    
                bool isNumber = int.TryParse(Console.ReadLine(), out int num);
                int closest = list.OrderBy(item => Math.Abs(num - item)).First();
                Console.WriteLine(closest);
    
                // Since we already know the extent of the data in excel, we can construct a loop that determines the start and end points.
                int intStartY = (int)startIndex[1] - 64;
                int intEndY = (int)endIndex[1] - 64;
               
                for (int i = startX; i <= endX; i++)
                {
                    for (int j = intStartY; j <= intEndY; j++)
                        if ((sheet.Cells[i, j] as Range).Value.ToString() == closest.ToString())
                        {
                            int row = i;
                            // Convert it based on ascii table.
                            char column = (char)(j + 64);
                            Console.WriteLine(row + " " + column);
                        }
                }
    
                Console.ReadLine();
            }

    See if it works for you.

    Best Regards,

    Timon


    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, March 12, 2020 2:17 AM
  • Thanks for your extraordinary reply.

    here some doubts.

    1. Regarding the range problem, two coordinates are enough.

    For example, "D6: I6 and D10: I10" is equivalent to "D6: I10".

    If i use "D6:I9" then i will work.

    If i use "D6:I10" then it shows error.

    because,  char[] endIndex = end.ToArray(); ==> It shows 3 array values like "1" and "0" and "I".

    but, it is not 3 array values. It suppose to be 2 values only such that " [0] ==> "10" and [1] ==> "I"

    So, i am getting wrong parameters as shown below. and also i am getting endY value is "0".

    endx value is suppose to be "10"

    endY value is suppose to be "I"

    In this case, How to pass value "10I" and handle it...??

    If the above is re-ordered , then my problem will be solved..


    • Edited by Gani tpt Thursday, March 12, 2020 12:18 PM "D6:I10"
    Thursday, March 12, 2020 10:18 AM
  • Hi Gani,

    This is my oversight, I modified the code and it should work now.

    static void Main(string[] args)
            {
                DoWork("11I", "13M");
    
            }
            public static void DoWork(string start, string end)
            {
                char[] startIndex = start.ToArray();
                char[] endIndex = end.ToArray();
    
            
                string strStartX = null;
                if (startIndex.Length > 2)
                {
                    for (int i = 0; i < startIndex.Length-1; i++)
                    {
                        strStartX += startIndex[i];
                    }
                }
                else
                {
                    strStartX = startIndex[0].ToString();
                }
                int startX = int.Parse(strStartX);
                string startY = startIndex[startIndex.Length-1].ToString();
               
                string strEndX = null;
                if (endIndex.Length > 2)
                {
                    for (int i = 0; i < endIndex.Length - 1; i++)
                    {
                        strEndX += endIndex[i];
                    }
                }
                else
                {
                    strEndX = endIndex[0].ToString();
                }
                int endX = int.Parse(strEndX);
                string endY = endIndex[endIndex.Length - 1].ToString(); ;
              
                Excel.Application xlApp = new Excel.Application();
                Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"D:\Test\Test1.xlsx");
                Excel.Worksheet sheet = xlWorkbook.ActiveSheet;
                object[,] data = sheet.Range[sheet.Cells[startX, startY], sheet.Cells[endX, endY]].Cells.Value2;
    
    
                List<int> list = new List<int>();
                foreach (var item in data)
                {
                    //In order to avoid empty cells in this range, if there is no empty cell, you can remove this judgment.
                    if (item!=null)
                    {
                        list.Add(int.Parse(item.ToString()));
                    }
                }
                Console.WriteLine("Enter a number:");
    
                bool isNumber = int.TryParse(Console.ReadLine(), out int num);
                int closest = list.OrderBy(item => Math.Abs(num - item)).First();
                Console.WriteLine(closest);
    
                // Since we already know the extent of the data in excel, we can construct a loop that determines the start and end points.
                int intStartY = (int)startIndex[startIndex.Length-1] - 64;
                int intEndY = (int)endIndex[endIndex.Length-1] - 64;
               
                for (int i = startX; i <= endX; i++)
                {
                    for (int j = intStartY; j <= intEndY; j++)
                        if ((sheet.Cells[i, j] as Range).Value.ToString() == closest.ToString())
                        {
                            int row = i;
                            // Convert it based on ascii table.
                            char column = (char)(j + 64);
                            Console.WriteLine(row + " " + column);
                        }
                }
    
                Console.ReadLine();
            }

    Edit: If possible, when entering the range, please put the number first and the letter after, otherwise you will get an error. I mean, use "6D" instead of "D6".

    Best Regards,

    Timon


    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.


    Friday, March 13, 2020 1:46 AM
  • Very good and excellent solution.

    I tried another way also.

    Instead of using merging value like "D6","10I", i have used individual value. like "D",6,"I",10.

    Anyhow you are rocking again.

    Thank you very much.

    Friday, March 13, 2020 8:52 AM
  • I'm glad that's helpful.

    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.

    Friday, March 13, 2020 9:34 AM