I need your help for an Excel automation via datatable.
I had attached a sample of the Excel file.
i had to send mail based on the value of a colomn:
for each reference cell i had to send an email like this:
"to: EMAIL PRO ( for this Reference Cell)
subject: “refrence cell”.
Please find the files for “code gestion” (for this reference cell) :
-name1 surname1 (for this reference cell)
-name2 surname2 (for this reference cell)
i think i had to sort the datatable by reference cell, do some other tricks, have you any idea to share with me?
sample.xlsx (23.9 KB)
Have a look to sort the datatable.
To Sort “Reference cell” column in ascending order :
InputDt = (InputDt.AsEnumerable.OrderBy(Function(x) x(“Reference cell”).ToString)).CopyToDataTable
To Sort “Reference cell” column in descending order :
InputDt = (InputDt.AsEnumerable.OrderByDescending(Function(x) x(“Reference cell”).ToString)).CopyToDataTable
Please refer to the following thread which will help you to understand how to sort the data in table based on 1 column or multiple columns and in different orders.
This thread will help you to sort a column or Multiple Columns of Data Table using some simple techniques.
Suppose if we have a Data Table having 2 columns as shown below.
1. To Sort data in Ascending Order based on Column “KeyWord” using Linq Query :
2. To Sort data in Descending Order based on Column “KeyWord” usin…
Sort a column in Ascending Order using LinQExpression :
InputDt = (InputDt.AsEnumerable.OrderBy(Function(x) x(“KeyWord”).ToString)).CopyToDataTable
@rohith.prabhu, it is easy to do the sort, but it how to send email by “Reference cell” ?
I’m not clear with your requirement. Can you please elaborate more on how do you want to send the email, what data needs to be extracted or where you’re facing issue.
@rohith.prabhu in the Excel sample there is a column named “Reference cell”.
i need to send 1 mail per group of “Reference cell”, eg (in the sample)
i will send 1 mail for “141” , in subject of the mail i put this Reference cell", and in the boy i will write all the “Pays de naissance” related to this “cell reference” here:
in the body of the mail :
For 420, in the body of the mail : :
for 230, in the body of the mail :
…and so on
To group the “Reference cell” and sort in ascending order :
InputDt= InputDT.AsEnumerable().GroupBy(Function(i) i(“ColumnName”).ToString).Select(Function(x)x.OrderBy(Function(y)y(“ColumnName”).ToString).Last).CopyToDataTable()
This will give you grouped result (single value) in ascending order.
Now you can loop over the
InputDT and send email.