question

GaniTPT avatar image
0 Votes"
GaniTPT asked GaniTPT commented

How to find the exact value from the range data table using LINQ C#

I have the below data table and i want to get the FinalValue based on the range.

For example, i have the parameter value "20".

this "20" will go and find from the data table and in where it will suite between "A" and "B" column of the data table.

85857-image.png


the above example table, parameter value "20" will come in between "A" of 11 and "B" of 25.

so the final value is "2500"

85837-image.png

How to write LINQ statement in C#


dotnet-csharp
image.png (3.0 KiB)
image.png (3.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Any update pls.

0 Votes 0 ·

1 Answer

TimonYang-MSFT avatar image
0 Votes"
TimonYang-MSFT answered GaniTPT commented

Please check the following code, I load the data in excel into a Datatable, then use linq to find the row where the parameter is located, and output the final value.

     static void Main(string[] args)
     {
         System.Data.DataTable dataTable = GetSheetDataAsDataTable(@"D:\test\excel\3.xlsx");
         int result =  GetFinalValue(dataTable, 11);
         Console.WriteLine(result);
    
         Console.WriteLine("Press any key to continue...");
         Console.ReadLine();
     }
    
     public static int GetFinalValue(System.Data.DataTable dataTable, int num) 
     {
         var row1 = (from row in dataTable.AsEnumerable()
                  where double.Parse(row["A"].ToString()) <= num && double.Parse(row["B"].ToString()) >= num
                  select row).First();
         return int.Parse(row1["FinalValue"].ToString());
     }
     public static System.Data.DataTable GetSheetDataAsDataTable(String filePath)
     {
         Application xlApp = new Excel.Application();
         Workbook xlBook = xlApp.Workbooks.Open(filePath);
         Worksheet xlSheet = (Worksheet)xlBook.Worksheets[1];
         Range xlRange = xlSheet.UsedRange;
         //Range xlRange = xlSheet.get_Range("A1", "C5");
         System.Data.DataTable dt = new System.Data.DataTable();
         try
         {
             DataRow row = null;
             for (int i = 1; i <= xlRange.Rows.Count; i++)
             {
                 if (i != 1)
                     row = dt.NewRow();
                 for (int j = 1; j <= xlRange.Columns.Count; j++)
                 {
                     if (i == 1)
                         dt.Columns.Add(((Range)xlRange.Cells[1, j]).Value.ToString(),typeof(string));
                     else
                         row[j - 1] = ((Range)(xlRange.Cells[i, j])).Value;
                 }
                 if (row != null)
                     dt.Rows.Add(row);
             }
         }
         catch (Exception ex)
         {
             Console.WriteLine(ex.Message);
         }
         finally
         {
             xlBook.Close();
             xlApp.Quit();
         }
         return dt;
     }

If there is more than one table in the excel file, you can use code like this to get the required range:

 Range xlRange = xlSheet.get_Range("A1", "C5");

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for your support.

I have posted one more related to this. can you give us the correct solution.

how-to-combine-two-column-values-and-find-the-dupl.html


0 Votes 0 ·

can you help us the below thread to proceed further..? I want exact solution.

how-to-combine-two-column-values-and-find-the-dupl.html



0 Votes 0 ·

@GaniTPT
I noticed that the thread you mentioned has accepted an answer. Is the problem solved?
Has the current problem been solved? Is the code right for you?

0 Votes 0 ·
Show more comments

Fantastic. It's working

0 Votes 0 ·