Sum_Multiple Data_Rows_LINQ

[Balachander_Pandian]

Hi Expert,
I have an excel file that contains columns of Total Amount , Total value and Total tax. I wish to sum all the columns values and the result of values will be appeared in the each columns in the end line. The expected output is attached in the output sheet in the sample input file. How can achieve this by LINQ ?
Sum.xlsx (10.3 KB)

[lrtetala (Lakshman Reddy)]

Hi @Balachander_Pandian

TotalAmount = dt.AsEnumerable().Sum(Function(row) Convert.ToDouble(row("Total Amount")))
TotalValue = dt.AsEnumerable().Sum(Function(row) Convert.ToDouble(row("Total Value")))
TotalTax = dt.AsEnumerable().Sum(Function(row) Convert.ToDouble(row("Total Tax")))

Add Data Row activity

{"","","",TotalAmount.ToString,TotalValue.ToString,TotalTax.ToString}

Regards,

[vrdabberu (Varunraj Dabberu)]

Hi @Balachander_Pandian

=> Read Range Workbook
Output → Inputdt

=> Use the below code in Invoke Code:

Dim summaryRow = Inputdt.Clone().Rows.Add()

summaryRow("Vendor Name") = ""
summaryRow("Branch") = ""
summaryRow("Total Amount") = Inputdt.AsEnumerable().Sum(Function(row) Convert.ToDouble(row("Total Amount")))
summaryRow("Total Value") = Inputdt.AsEnumerable().Sum(Function(row) Convert.ToDouble(row("Total Value")))
summaryRow("Total Tax") = Inputdt.AsEnumerable().Sum(Function(row) Convert.ToDouble(row("Total Tax")))

Inputdt.Rows.Add(summaryRow)

Invoke Code Arguments:
Argument Name: Inputdt
Direction: In/Out
Type: DataTable

=> Write Range Workbook Inputdt back to excel to new sheet.

Regards

[Sagar1 (Sagar Das)]

Hi @Balachander_Pandian ,

You may try this one LINQ code, to achieve your requirement in one line.

(From row In dtInput.AsEnumerable.Concat({dtInput.NewRow})
Let rowIndex = dtInput.Rows.IndexOf(row)
Let Sum_TotalAmount = dtInput.AsEnumerable.Sum(Function(RowItem) CDbl(RowItem("Total Amount").ToString))
Let Sum_TotalValue = dtInput.AsEnumerable.Sum(Function(RowItem) CDbl(RowItem("Total Value").ToString))
Let Sum_TotalTax = dtInput.AsEnumerable.Sum(Function(RowItem) CDbl(RowItem("Total Tax").ToString))
Select dtOutput.Rows.Add(If(rowIndex <>-1,
	row.itemarray,
	{"","","",Sum_TotalAmount, Sum_TotalValue, Sum_TotalTax}))
).CopyToDataTable

Find below the image.

Thanks,
Sagar