Faster way to export data from datagridview to excel VB.Net

Question

0.00/5 (No votes)

See more:

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

Posted 11-Nov-14 13:50pm

Member 11039558

Updated 3-Dec-18 18:28pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sergey Alexandrovich Kryukov · Answer 1 · 2014-11-11T15:00:00

Instead of using Interop assembly, you can try an alternative method which doesn't even require Office (or Excel) installation: use Open XML SDK.
Please see my past answer where I referenced other answers and a good CodeProject article (the last link): How to add microsoft excel 15.0 object library from Add Reference in MS Visual Studio 2010[^].

—SA

Suroj Karmacharya · Answer 2 · 2018-12-03T18:29:00

Imports Excel = Microsoft.Office.Interop.Excel

Public Sub SaveGridToExcel(ByVal DGV As DataGridView)
    If DGV.Rows.Count > 0 Then
        Dim filename As String = ""
        Dim SV As SaveFileDialog = New SaveFileDialog()
        SV.Filter = "EXCEL FILES|*.xlsx;*.xls"
        Dim result As DialogResult = SV.ShowDialog()

        If result = DialogResult.OK Then
            filename = SV.FileName
            Dim multiselect As Boolean = DGV.MultiSelect
            DGV.MultiSelect = True
            DGV.SelectAll()
            DGV.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
            Clipboard.SetDataObject(DGV.GetClipboardContent())
            Dim results = System.Convert.ToString(Clipboard.GetData(DataFormats.Text))
            DGV.ClearSelection()
            DGV.MultiSelect = multiselect
            Dim XCELAPP As Microsoft.Office.Interop.Excel.Application = Nothing
            Dim XWORKBOOK As Microsoft.Office.Interop.Excel.Workbook = Nothing
            Dim XSHEET As Microsoft.Office.Interop.Excel.Worksheet = Nothing
            Dim misValue As Object = System.Reflection.Missing.Value
            XCELAPP = New Excel.Application()
            XWORKBOOK = XCELAPP.Workbooks.Add(misValue)
            XCELAPP.DisplayAlerts = False
            XCELAPP.Visible = False
            XSHEET = XWORKBOOK.ActiveSheet
            XSHEET.Paste()
            XWORKBOOK.SaveAs(filename, Excel.XlFileFormat.xlOpenXMLWorkbook)
            XWORKBOOK.Close(False)
            XCELAPP.Quit()

            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(XSHEET)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(XWORKBOOK)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(XCELAPP)
            Catch
            End Try
        End If
    End If
End Sub

Faster way to export data from datagridview to excel VB.Net

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0