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

  • Question

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


    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 } } }



    • Edited by Gani tpt Friday, November 27, 2020 4:50 PM
    Friday, November 27, 2020 4:45 PM

Answers

  • If the data will be sorted, then you have to check for when the cell value is greater than the input value, and then back up one row.

     if (sheet.Cells[i, j].Value > ParamNum)
    {
        foundRow = i - 1;
    }

    This is basically the Vlookup function, but I don't know if you can access Excel functions from C#.

    • Marked as answer by Gani tpt Tuesday, December 1, 2020 3:40 PM
    Friday, November 27, 2020 5:13 PM