Faster Way to Export Datagridview to Excel Using VB.Net

  • Thread starter Thread starter Mykel Sandoval
  • Start date Start date
M

Mykel Sandoval

Guest
Hi,

I have this below code used in exporting contents of datagridview to excel using VB.Net and works fine. However, when dealing with many records, example is 300 records, it takes time before the process of exporting will be finished.


Please advise me on how to speed things up, to export data from datagridview to excel in a faster manner than this one, or any codes that you can provide for easy exporting of data.


Your help will be greatly appreciated. Thank you in advance.


Private Sub bt_export_Click(sender As System.Object, e As System.EventArgs) Handles bt_export.Click

Try

If tb_count.Text = "0" Then
MessageBox.Show("Nothing to be exported as excel file. Export process is cancelled", "Export to Excel", MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
Try
Cursor.Current = Cursors.WaitCursor
ProgressBar1.Visible = True
lb_pcnt.Visible = True
lb_pcnt.Text = ProgressBar1.Value.ToString("p")
ProgressBar1.Minimum = 0
ProgressBar1.Maximum = Val(tb_count.Text)

Dim xlApp As Microsoft.Office.Interop.Excel.Application
Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim i As Integer
Dim j As Integer
Dim lMyArray(2, 1) As Long

xlApp = New Microsoft.Office.Interop.Excel.Application
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")

For i = 0 To DGV.RowCount - 1
For j = 0 To DGV.ColumnCount - 1
For k As Integer = 1 To DGV.Columns.Count
xlWorkSheet.Cells(1, k) = DGV.Columns(k - 1).HeaderText
xlWorkSheet.Cells(i + 2, j + 1) = DGV(j, i).Value.ToString()
Next
Next

ProgressBar1.Value += 1
lb_pcnt.Text = Format(ProgressBar1.Value / (tb_count.Text), "p")
Next
With xlWorkSheet.Range("A1", "BB1")
.Font.Bold = True
.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
End With
xlWorkSheet.Range("A1", "BB1").EntireColumn.AutoFit()
If lb_1.Text = "empl" Then
xlWorkSheet.SaveAs("C:\Expat Database\Output\Expat - Assignees_" & lb_time2.Text & ".xlsx")
ElseIf lb_1.Text = "empl2" Then
xlWorkSheet.SaveAs("C:\Expat Database\Output\Expat - Business Travelers_" & lb_time2.Text & ".xlsx")
Else
xlWorkSheet.SaveAs("C:\Expat Database\Output\Taxes of Expat - Assignees_" & lb_time2.Text & ".xlsx")
End If
xlWorkBook.Close()
xlApp.Quit()
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)

Catch ex As Exception

End Try

Dim res As MsgBoxResult

res = MsgBox("Process completed, Would you like to open file?", MsgBoxStyle.YesNo)

If (res = MsgBoxResult.Yes) Then
If lb_1.Text = "empl" Then
Process.Start("C:\Expat Database\Output\Expat - Assignees_" & lb_time2.Text & ".xlsx")
ElseIf lb_1.Text = "empl2" Then
Process.Start("C:\Expat Database\Output\Expat - Business Travelers_" & lb_time2.Text & ".xlsx")
Else
Process.Start("C:\Expat Database\Output\Taxes of Expat - Assignees_" & lb_time2.Text & ".xlsx")
End If

End If

ProgressBar1.Value = ProgressBar1.Minimum
lb_pcnt.Text = ProgressBar1.Value.ToString("p")
ProgressBar1.Visible = False
lb_pcnt.Visible = False

End If

Catch ex As Exception

MessageBox.Show("No connection.", "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

End Sub

Continue reading...
 
Back
Top