x'tian x'tian - 1 month ago 8
Javascript Question

Asp.net display sum of columns total in gridview footer

I have a program that uploads an excel file. After uploading the value of excel file will be transferred in gridview.

What I want to do is to get the total value per column and will be placed in footer.Does anyone can help me with this? Thank you.

Here's what I have so far.

Protected Sub Button1_Click(sender As Object, e As System.EventArgs)Handles Button1.Click
Dim fl As String = FileUpload1.PostedFile.FileName
GridView1.DataSource = OpenExcelFile(fl)
GridView1.DataBind()
savetoDB(OpenExcelFile(fl))
End Sub

Protected Function OpenExcelFile(ByVal fileName As String) As Object
Dim dataTable As New System.Data.DataTable()
Dim exl As String = FileUpload1.PostedFile.FileName
Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & exl & ";Extended Properties=" & """Excel 12.0 Xml;HDR=YES;"""
Dim adapter As New OleDbDataAdapter("SELECT [NAME],[FIL],[ENG],[SCI],[MTH]", connectionString)
adapter.Fill(dataTable)
Return dataTable
End Function

Protected Sub savetoDB(dt As System.Data.DataTable)
Dim cmd As New SqlCommand
Dim con As New SqlConnection("myconnectionhere")
Dim dt2 As New System.Data.DataTable
dt2 = OpenExcelFile(FileUpload1.PostedFile.FileName)

Using bulk As SqlBulkCopy = New SqlBulkCopy(con)
bulk.DestinationTableName = "studnt_table"
bulk.ColumnMappings.Add("[NAME]", "st_name")
bulk.ColumnMappings.Add("[FIL]", "sbj_fil")
bulk.ColumnMappings.Add("[ENG]", "sbj_eng")
bulk.ColumnMappings.Add("[SCI]", "sbj_sci")
bulk.ColumnMappings.Add("[MTH]", "sbj_mth")
con.Open()
bulk.WriteToServer(dt2)
con.Close()
con.Close()
End Using
End Sub


And here is my front end



here is the sample layout I want to achieve. I want to get the sum of the columns in line and display the total in footer. Does anyone knows how can I achieve this? Any help would be much appreciated. Thank you/

enter image description here

Answer

Try this. I've only added sample for 1 column to get an idea. Extend the OpenExcelFile after adatapter.Fill(dataTable) IsNull Check

adatapter.Fill(dataTable)
Dim sumRow As DataRow
Dim Col1Tot As Integer = 0
For Each row1 As DataRow In dataTable.Rows  
   If Not row1.IsNull("FIL") Then 
     Col1Tot =  Col1Tot + Convert.toDecimal(row1("FIL"))
End If 
Next row1
'Do the remaining columns in same way

'Finally add it to the Datatable as a new row
sumRow =  dataTable.NewRow()
sumRow("FIL") = Col1Tot 
dataTable.Rows.Add(sumRow)
return dataTable;

Let us know your findings.

Comments