How do find min value based on parameter from excel range column by column using C#

BeUnique 2,112 Reputation points
2020-11-27T16:47:05.543+00:00

I have below data in my excel table. i am passing parameter value "9050".

43393-min-data.jpg

Here the value should search from column by column of every cell values.

for example,

Input value "9050", it will come near by column of "B" and data between 8942.16 and 9138.26

Here the minimum value of "8942" should be a result value of "9050".

Excel.Application xlApp = new Excel.Application();
Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Project\Test\testExcel.xlsx");
Worksheet sheet = xlWorkbook.ActiveSheet;
try
{
//search row wise cell, but need column wise cell
int startRow = 1;
char startColumn = 'A';
int endRow = 13;
char endColumn = 'D';
int ParamNum = 9050;

            int intStartY = (int)startColumn - 64;  
            int intEndY = (int)endColumn - 64;  

            List<double> data1 = new List<double>();  
            for (int j = intStartY; j <= intEndY; j++)  
            {  
                for (int i = startRow; i <= endRow; i++)  
                {  
                    if (sheet.Cells[i, j].Value != null)  
                    {  
                        data1.Add(Convert.ToDouble(sheet.Cells[i, j].Value));  
                        ?????????// How we have to check the minimum value of "9050"  
                        //9050 ==> this value will come in between of 8942.16 and 9138.26  
                        //So 8942.16 ==> Result and final value  
                    }  
                }  
            }  
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,602 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 114.4K Reputation points
    2020-11-27T17:06:43.677+00:00

    Check if this code needs adjustments after giving new details:

    double foundMinValue = -1;
    int foundColumn = -1;
    
    for (int j = intStartY; j <= intend && foundColumn < 0; j++)
    {
       for (int i = startRow; i < endRow && foundColumn < 0; i++)
       {
          if (sheet.Cells[i, j].Value == null) continue;
          if (sheet.Cells[i + 1, j].Value == null) continue;
    
          double v1 = Convert.ToDouble(sheet.Cells[i, j].Value);
          double v2 = Convert.ToDouble(sheet.Cells[i + 1, j].Value);
          double min = Math.Min( v1, v2 );
          double max = Math.Max( v1, v2 );
    
          if( ParamNum >= min && ParamNum <= max) 
          {
             foundColumn = j;
             foundMinValue = min;
          }
       }
    }
    

0 additional answers

Sort by: Most helpful