Paul Edward SanGabriel Pagente Paul Edward SanGabriel Pagente - 9 months ago 124 Question

Sum Up all the Data in a Column and Display it Below VB.Net and Excel

Good Afternoon to all

I populate data in Datagridview from mySQL like this.

Datagridview Data

the next thing I do is that I have an Export Button and if I Click that it will Export the Data from Datagridview in Excel like this

Extracted in Excel

My Question is How can I Find the Last Data in Column "Total" and Put the Sum below that? As of now the Image shows only two rows in excel but someday it will populate, I just want to Sum up all the data in the Column "Total" and Display the Output in below the last Data. I hope you help me. :(


by the way here is my code

If DataGridView1.Rows.Count = 0 Then
MsgBox("Nothing to Export")
Dim ExcelApp As Object, ExcelBook As Object
Dim ExcelSheet As Object
Dim i As Integer
Dim j As Integer
Dim exl As Excel.Application

Dim NewWorksheet As Microsoft.Office.Interop.Excel.Worksheet
Dim myRange As Microsoft.Office.Interop.Excel.Range
ExcelApp = CreateObject("Excel.Application")
ExcelBook = ExcelApp.WorkBooks.Add
ExcelSheet = ExcelBook.WorkSheets(1)

exl = New Excel.Application
exl.Visible = True
With ExcelSheet
For Each column As DataGridViewColumn In DataGridView1.Columns

.cells(1, column.Index + 1) = column.HeaderText
For i = 1 To Me.DataGridView1.RowCount
.cells(i + 1, 1) = Me.DataGridView1.Rows(i - 1).Cells("ItemCode").Value
For j = 1 To DataGridView1.Columns.Count - 1
.cells(i + 1, j + 1) = DataGridView1.Rows(i - 1).Cells(j).Value
.Cells(4, 13) = "Grand Total"
.Cells(4, 14).Formula = "=SUM(Sheet1!$J2:$J1048576)"
End With
NewWorksheet = DirectCast(ExcelBook.Sheets(1), Microsoft.Office.Interop.Excel.Worksheet)
myRange = NewWorksheet.Range("A:K")
myRange.Font.Bold = True
myRange.Font.Size = 9
myRange.Font.FontStyle = "Calibri"
ExcelApp.Visible = True
ExcelSheet = Nothing
ExcelBook = Nothing
ExcelApp = Nothing
End If


Excel coordinates work by (column, row)

Dim rowIndex As Integer = 1
For i = 1 To Me.DataGridView1.RowCount
   .cells(1, i + 1) = Me.DataGridView1.Rows(i - 1).Cells("ItemCode").Value
   rowIndex += 1
   For j = 1 To DataGridView1.Columns.Count - 1
      .cells(j + 1, i + 1) = DataGridView1.Rows(i - 1).Cells(j).Value
.Cells(9, rowIndex + 1) = "Grand Total"
.Cells(10, rowIndex + 1).Formula = "=SUM(Sheet1!J2:J" & rowIndex.ToString().Trim() & ")"