locked
Ho do we find closest value from column wise of all cell instead of row wise of all cell using C# RRS feed

  • Question

  • How do we find closest value from column wise of all cell instead of row wise of all cell using c#.

    below is the code working find and it searches row wise. if we are using row wise, i may get some inappropriate values.

    so we thought  and need to change column wise. How do we change the below code.

    Application xlApp = new Excel.Application(); Workbook xlWorkbook = xlApp.Workbooks.Open(@"D:\test\excel\4.xlsx"); Worksheet sheet = xlWorkbook.ActiveSheet; try {

    //search row wise cell, but need column wise cell object[,] data = sheet.Range[sheet.Cells[startRow, startColumn.ToString()], sheet.Cells[endRow, endColumn.ToString()]].Cells.Value2; // checking row wise double closest = SearchArray(num, data); Console.WriteLine(closest); int intStartY = (int)startColumn - 64; int intEndY = (int)endColumn - 64; for (int j = intStartY; j <= intEndY; j++) { for (int i = startRow; i <= endRow; i++) { if (double.Parse(sheet.Cells[i, j].Value == null ? "0.0" : sheet.Cells[i, j].Value.ToString()) == closest) { int row = i; char column = (char)(j + 64); Console.WriteLine(row + " " + column); break; } } } } catch (Exception e) { Console.WriteLine(e); } finally { xlWorkbook.Close(); xlApp.Quit(); }





    • Edited by Gani tpt Wednesday, November 25, 2020 2:10 AM Title modified
    Monday, November 23, 2020 10:15 AM

Answers


  • Hi Gani tpt,
    I did some research about Worksheet.Range and can't find the way to order data via column wise.
    So I have a suggestion you can refer to.
    First, you can add the data of each column to a list (named list in my code). and then add the collection of all columns to the list(named data in my code). Finally traversing the collection will query data from each column.
    Note: you need to install EEPlus in first.
    Here is a test code example.

    static void Main(string[] args)
    {
        ExcelWorksheet sheet = GetWorksheetFromFile(@"C:\Users\Desktop\test.xlsx", "Test");
        List<List<object>> data = GetWorksheetColumns(sheet);
        int num = 2100;
        List<object> list = new List<object>();
        for (int i=0; i<data.Count;i++)
        {
            double closest = SearchArray(num, data[i].ToArray());
            list.Add(closest);
        }
        double Fclosest = SearchArray(num,list.ToArray());
        Console.WriteLine(Fclosest);
        Console.ReadKey();
    }
    
    public static ExcelWorksheet GetWorksheetFromFile(string path, string sheetName)
    {
        // If you use EPPlus in a noncommercial context
        // according to the Polyform Noncommercial license:
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
    
        FileStream fileStream = new FileStream(path, FileMode.Open, FileAccess.Read);
        ExcelPackage excel = new ExcelPackage(fileStream);
        ExcelWorksheet sheet = excel.Workbook.Worksheets[sheetName];
        fileStream.Close();
        return sheet;
    }
    public static List<List<object>> GetWorksheetColumns(ExcelWorksheet sheet)
    {
        try
        {
            List<List<object>> result = new List<List<object>>();
            int columnCount = 4;
            int rowcount = 4;
            for (int i = 1; i <= columnCount; i++)
            {
                List<object> col = new List<object>();
    
                for (int j = 1; j <= rowcount; j++)
                {
                    var cell = sheet.Cells[j, i];
    
                    col.Add(cell == null ? null : cell.Value);
    
                }
                result.Add(col);
            }
    
            return result;
        }
        catch
        {
            throw;
        }
    }
    
    public static double SearchArray(double inValToSearch_, object[] inArr_)
    {
        if (inArr_ == null || inArr_.Length == 0)
            return 0;
        List<double> list = new List<double>();
        foreach (var item in inArr_)
        {
            if (item != null)
            {
                list.Add(int.Parse(item.ToString()));
            }
        }
        return list.OrderBy(item => Math.Abs(inValToSearch_ - item)).First();
    }

    Hope these are helpful to you.
    Best Regards,
    Daniel Zhang

                        

    "Visual c#" forum will be migrating to a new home on Microsoft Q&A ! We invite you to post new questions in the "Developing Universal Windows apps" forum’s new home on Microsoft Q&A ! For more information, please refer to the sticky post.

    • Marked as answer by Gani tpt Tuesday, December 1, 2020 3:41 PM
    Friday, November 27, 2020 6:04 AM
  • Thanks..

    We got solution in another link.

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

    Thanks to @Viorel_

    • Marked as answer by Gani tpt Tuesday, December 1, 2020 3:40 PM
    Monday, November 30, 2020 7:55 AM

All replies

  • Any help...
    Monday, November 23, 2020 11:49 AM
  • Can anyone update this thread then that would be appreciated. We need this logic as soon as possible.

    we already done row wise concept. but, we are struggling to get column wise check closest values.

    Tuesday, November 24, 2020 5:51 AM
  • Greetings Gani.

    I'm not sure what you mean. Do you mean swapping the row and column loops, something like this?

                    for (int i = startRow; i <= endRow; i++) 
                    {
                        for (int j = intStartY; j <= intEndY; j++)
                        {
    
                            if (double.Parse(sheet.Cells[i, j].Value == null ? "0.0" : sheet.Cells[i, j].Value.ToString()) == closest)
                            {
                                int row = i;
                                char column = (char)(j + 64);
                                Console.WriteLine(row + " " + column);
                                break;
                            }
                        }
                    }

    Tuesday, November 24, 2020 6:00 AM
  • No.

    below is the excel data.

    Complete source code

    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 = 3; char startColumn = 'F'; int endRow = 6; char endColumn = 'J'; int num = 225; object[,] data = sheet.Range[sheet.Cells[startRow, startColumn.ToString()], sheet.Cells[endRow, endColumn.ToString()]].Cells.Value2; // checking row wise double closest = SearchArray(num, data); Console.WriteLine(closest); int intStartY = (int)startColumn - 64; int intEndY = (int)endColumn - 64; for (int j = intStartY; j <= intEndY; j++) { for (int i = startRow; i <= endRow; i++) { if (double.Parse(sheet.Cells[i, j].Value == null ? "0.0" : sheet.Cells[i, j].Value.ToString()) == closest) { int row = i; char column = (char)(j + 64); Console.WriteLine(row + " " + column); break; } } } } catch (Exception ex) { Console.WriteLine(e); } finally { xlWorkbook.Close(); xlApp.Quit(); }

    public static double SearchArray(double inValToSearch_, object[,] inArr_)
            {
                if (inArr_ == null || inArr_.Length == 0)
                    return 0;
                List<double> list = new List<double>();
                foreach (var item in inArr_)
                {
                    if (item != null)
                    {
                        list.Add(int.Parse(item.ToString()));
                    }
                }
                return list.OrderBy(item => Math.Abs(inValToSearch_ - item)).First();
            }


    the final closest value from the excel range is "200" and the result column is "G".

    It should check start from first "F" column (all the "F" column cells) and then go to "G" column like that.

    Note : current code is checking row wise like (first row and second row of every cell)




    • Edited by Gani tpt Tuesday, November 24, 2020 11:56 AM check start from first "F" column (all the "F" column cells)
    Tuesday, November 24, 2020 7:12 AM
  • let me know if you need any more details...
    • Edited by Gani tpt Tuesday, November 24, 2020 8:28 AM
    Tuesday, November 24, 2020 8:25 AM
  • any one can reply for the above logic..?
    Tuesday, November 24, 2020 1:05 PM
  • any update pls.
    Wednesday, November 25, 2020 2:10 AM
  • Hi Gani tpt,
    Ante Meridian has provided a code example that swapping the row and column loops.
    Have you tried it? And what problem did you encounter?
    Best Regards,
    Daniel Zhang


    "Visual c#" forum will be migrating to a new home on Microsoft Q&A ! We invite you to post new questions in the "Developing Universal Windows apps" forum’s new home on Microsoft Q&A ! For more information, please refer to the sticky post.

    Wednesday, November 25, 2020 7:32 AM
  • i am expecting not like that .

    i am expecting to find the closest value from excel range and this range position should start from the column wise instead of row wise.

    below is the code

    object[,] data is formed in an array using row wise range. but, i want to start column wise range.
    
    //search row wise cell, but need column wise cell
    object[,] data = sheet.Range[sheet.Cells[startRow, startColumn.ToString()], sheet.Cells[endRow, endColumn.ToString()]].Cells.Value2;// checking row wise
    double closest = SearchArray(num,data);
    Console.WriteLine(closest);


    It should check start from first "F" column (all the "F" column cells) and then go to "G" column like that.

    i thing we need to change the below lines only for handling array data through columnwise.

    But, i am not sure the whether it will work or not.

    sheet.Range[sheet.Cells[startRow,startColumn.ToString()], sheet.Cells[endRow,  endColumn.ToString()]].Cells.Value2;

    still struggling...




    • Edited by Gani tpt Wednesday, November 25, 2020 1:25 PM code formatted.
    Wednesday, November 25, 2020 10:40 AM
  • any update..
    Wednesday, November 25, 2020 4:47 PM
  • Hi Gani tpt,
    Based on your code, I made a test.
    My testExcle is below:


    And I am looking for the value closest to 2100.
    The test result:


    From the results, it is queried by column. Otherwise the result should be "1 D".
    Below is the changed code:

    char startColumn = 'A';
    int endRow = 4;
    char endColumn = 'D';
    int num = 2100;
    object[,] data=sheet.Range[sheet.Cells[startRow, startColumn.ToString()], sheet.Cells[endRow, endColumn.ToString()]].Cells.Value2;

    Best Regards,
    Daniel Zhang


    "Visual c#" forum will be migrating to a new home on Microsoft Q&A ! We invite you to post new questions in the "Developing Universal Windows apps" forum’s new home on Microsoft Q&A ! For more information, please refer to the sticky post.

    Thursday, November 26, 2020 6:31 AM
  • No..the closest value already available in "A" column. First it should traverse "A" column of all the cell, and then only it should go to next column.

    The Object[,] data order should be [column wise] array "1000"

                                                                                     "2000"

                                                                                      "3000"

                                                                                      "4000"

                                                                                      "100"

                                                                                      "200"

                                                                                      "300"

                                                                                      "400" etc.

    But, the current order is row wise object data "1000"

                                                                       "100"

                                                                       "10000"

                                                                       "10"

                                                                       "100000"

    after executing the code output as below.

    The above code order will be showing Row wise collection array.

    But, I want Column wise collection array.

    Thursday, November 26, 2020 9:11 AM
  • Pls. reply the above queries. i am struggling to get the exact logic..?
    Thursday, November 26, 2020 12:56 PM

  • Hi Gani tpt,
    I did some research about Worksheet.Range and can't find the way to order data via column wise.
    So I have a suggestion you can refer to.
    First, you can add the data of each column to a list (named list in my code). and then add the collection of all columns to the list(named data in my code). Finally traversing the collection will query data from each column.
    Note: you need to install EEPlus in first.
    Here is a test code example.

    static void Main(string[] args)
    {
        ExcelWorksheet sheet = GetWorksheetFromFile(@"C:\Users\Desktop\test.xlsx", "Test");
        List<List<object>> data = GetWorksheetColumns(sheet);
        int num = 2100;
        List<object> list = new List<object>();
        for (int i=0; i<data.Count;i++)
        {
            double closest = SearchArray(num, data[i].ToArray());
            list.Add(closest);
        }
        double Fclosest = SearchArray(num,list.ToArray());
        Console.WriteLine(Fclosest);
        Console.ReadKey();
    }
    
    public static ExcelWorksheet GetWorksheetFromFile(string path, string sheetName)
    {
        // If you use EPPlus in a noncommercial context
        // according to the Polyform Noncommercial license:
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
    
        FileStream fileStream = new FileStream(path, FileMode.Open, FileAccess.Read);
        ExcelPackage excel = new ExcelPackage(fileStream);
        ExcelWorksheet sheet = excel.Workbook.Worksheets[sheetName];
        fileStream.Close();
        return sheet;
    }
    public static List<List<object>> GetWorksheetColumns(ExcelWorksheet sheet)
    {
        try
        {
            List<List<object>> result = new List<List<object>>();
            int columnCount = 4;
            int rowcount = 4;
            for (int i = 1; i <= columnCount; i++)
            {
                List<object> col = new List<object>();
    
                for (int j = 1; j <= rowcount; j++)
                {
                    var cell = sheet.Cells[j, i];
    
                    col.Add(cell == null ? null : cell.Value);
    
                }
                result.Add(col);
            }
    
            return result;
        }
        catch
        {
            throw;
        }
    }
    
    public static double SearchArray(double inValToSearch_, object[] inArr_)
    {
        if (inArr_ == null || inArr_.Length == 0)
            return 0;
        List<double> list = new List<double>();
        foreach (var item in inArr_)
        {
            if (item != null)
            {
                list.Add(int.Parse(item.ToString()));
            }
        }
        return list.OrderBy(item => Math.Abs(inValToSearch_ - item)).First();
    }

    Hope these are helpful to you.
    Best Regards,
    Daniel Zhang

                        

    "Visual c#" forum will be migrating to a new home on Microsoft Q&A ! We invite you to post new questions in the "Developing Universal Windows apps" forum’s new home on Microsoft Q&A ! For more information, please refer to the sticky post.

    • Marked as answer by Gani tpt Tuesday, December 1, 2020 3:41 PM
    Friday, November 27, 2020 6:04 AM
  • In addition, i will explain my real time requirement.

    below is the real time sample data excel file from client.

    Input parameter : 420

    First i want to find the closest value from the above range which is mainly followed column by column.

    First i will start search "420" in "A" column of every cell.

    For example, (column A)

    420 with 1000

    420 with 2000

    420 with 3000

    420 with 4000

    The above all the case is false. because the closest value difference is so high.

    Next Will start to search Column "B" of every cell.

    420 with 100

    420 with 350 (Here 420 is nearly closest the value and break the loop and note the final value is "350").

    and the final column would be "B".

    like the above i need search code
    • Edited by Gani tpt Friday, November 27, 2020 10:21 AM
    Friday, November 27, 2020 8:02 AM
  • Or we can take minimum value in between the value "350" and "600". 

    because "420" will come in between of "350" and "600" in the list (without order)

    So we can consider "350" for the final nearest value. (not required here absolute or %).

    ???

    Friday, November 27, 2020 10:55 AM
  • In addition, i will explain my real time requirement.

    below is the real time sample data excel file from client.

    Input parameter : 420

    First i want to find the closest value from the above range which is mainly followed column by column.

    First i will start search "420" in "A" column of every cell.

    For example, (column A)

    420 with 1000

    420 with 2000

    420 with 3000

    420 with 4000

    The above all the case is false. because the closest value difference is so high.

    Next Will start to search Column "B" of every cell.

    420 with 100

    420 with 350 (Here 420 is nearly closest the value and break the loop and note the final value is "350").

    and the final column would be "B".

    like the above i need search code

    Why 350 from column B is better than 1000 from column A and better than 400 from column C?

    Friday, November 27, 2020 11:03 AM
  • Yes. your question is correct.

    First we need to identify the value "420" in between where it will come and placed.

    "420" will come after 350 and before 600.

    so, it means "420" will come in between 350 and 600.

    In this case, as per client requirement, they want minimum value of 350 and 600.

    once 350 has fixed, then no need to go for "400" value for next consideration in the list.

    Friday, November 27, 2020 12:22 PM
  • Yes. your question is correct.

    First we need to identify the value "420" in between where it will come and placed.

    "420" will come after 350 and before 600.

    so, it means "420" will come in between 350 and 600.

    In this case, as per client requirement, they want minimum value of 350 and 600.

    once 350 has fixed, then no need to go for "400" value for next consideration in the list.


    Is it possible to have a situation when 350 is the last value in some column and 600 is the first value in next column?


    Friday, November 27, 2020 12:41 PM
  • Thanks..

    We got solution in another link.

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

    Thanks to @Viorel_

    • Marked as answer by Gani tpt Tuesday, December 1, 2020 3:40 PM
    Monday, November 30, 2020 7:55 AM
  • Hi Gani tpt,
    I am glad you have solved your problem. And we hope you can mark it as an answer. By marking a post as answered, you help others find the answer faster.
    Best Regards,
    Daniel Zhang


    "Visual c#" forum will be migrating to a new home on Microsoft Q&A ! We invite you to post new questions in the "Developing Universal Windows apps" forum’s new home on Microsoft Q&A ! For more information, please refer to the sticky post.

    Monday, November 30, 2020 9:12 AM