Vb.net Question

How to Customize Excel using VB.Net

Hello Everyone Good Morning.

I have a Program in VB.Net that will Populate Data from Mysql into the Datagridview.

I have also a button called Export and It will Export Datagridview Data in Excel Format like this.

enter image description here

But my our Prof. likes this Format.
enter image description here

How can I achieve this?


  1. Put a Center Header

  2. Put a .00 at the End of the Number of a Number Column

  3. Find the Last Cell in a Column and Sum It.



I hope someone would help me.

Here is my code in Export

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

ExcelApp = CreateObject("Excel.Application")
ExcelBook = ExcelApp.WorkBooks.Add
ExcelSheet = ExcelBook.WorkSheets(1)
With ExcelSheet
For Each column As DataGridViewColumn In DataGridView1.Columns
.cells(1, column.Index + 1) = column.HeaderText
Next
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

Next


Next
End With
ExcelApp.Visible = True
ExcelSheet = Nothing
ExcelBook = Nothing
ExcelApp = Nothing

End If

Answer

Try this code :

If DataGridView1.Rows.Count = 0 Then
    MsgBox("Nothing to Export")
Else
    Dim ExcelApp As Object, ExcelBook As Object
    Dim ExcelSheet As Object
    Dim i As Integer
    Dim j As Integer
    Dim rowIndex As Integer = 1
    Dim total As Double = 0
    Dim indexTotal As Integer

    ExcelApp = CreateObject("Excel.Application")
    ExcelBook = ExcelApp.WorkBooks.Add
    ExcelSheet = ExcelBook.WorkSheets(1)
    With ExcelSheet

        With .Range(.Cells(rowIndex, 1), .Cells(rowIndex, DataGridView1.Columns.Count))
            .HorizontalAlignment = Excel.Constants.xlCenter
            .VerticalAlignment = Excel.Constants.xlCenter
            .MergeCells = True
            .Value = "PURCHASE REQUISITION"
            .Font.Bold = True
        End With

        rowIndex += 2

        For Each column As DataGridViewColumn In DataGridView1.Columns
            .cells(rowIndex, column.Index + 1) = column.HeaderText
        Next

        .Range(.Cells(rowIndex, 1), .Cells(rowIndex, DataGridView1.Columns.Count)).Font.Bold = True

        rowIndex += 1

        For i = 0 To Me.DataGridView1.RowCount - 1
            .cells(rowIndex, 1) = Me.DataGridView1.Rows(i).Cells("ItemCode").Value
            For j = 1 To DataGridView1.Columns.Count - 1
                If IsNumeric(DataGridView1.Rows(i).Cells(j).Value) Then
                    .cells(rowIndex, j + 1).NumberFormat = "#,##0.00"
                    .cells(rowIndex, j + 1) = DataGridView1.Rows(i).Cells(j).Value
                Else
                    .cells(rowIndex, j + 1) = DataGridView1.Rows(i).Cells(j).Value
                End If
                'You can test also by index for example : if j = indexofTotalColumn then
                If DataGridView1.Columns(j).Name = "Total" Then
                    total += DataGridView1.Rows(i).Cells(j).Value
                    indexTotal = j
                End If
            Next
            rowIndex += 1
        Next

        .cells(rowIndex, indexTotal) = "Grand Total"
        .cells(rowIndex, indexTotal + 1).NumberFormat = "#,##0.00"
        .cells(rowIndex, indexTotal + 1) = total 
        .Range(.Cells(rowIndex, indexTotal), .Cells(rowIndex, indexTotal + 1)).Font.Bold = True

    End With
    ExcelApp.Visible = True
    ExcelSheet = Nothing
    ExcelBook = Nothing
    ExcelApp = Nothing
End If