更快速地将数据从datagridview导出到excel VB.Net [英] Faster way to export data from datagridview to excel VB.Net
问题描述
我有以下代码用于使用VB.Net将datagridview的内容导出到excel并且工作正常。但是，在处理许多记录时，例如300条记录，在完成导出过程之前需要一段时间。
请告诉我如何加快速度，将数据从datagridview导出到excel的方式比这个更快，或者你可以提供的任何代码以便于导出数据。
我们将非常感谢您的帮助。提前谢谢。
Private Sub bt_export_Click（sender As System.Object，e As System.EventArgs）处理bt_export.Click
试试
如果tb_count.Text =0那么
MessageBox .Show（无法导出为excel文件。导出过程被取消，导出到Excel，MessageBoxButtons.OK，MessageBoxIcon.Error）
否则
尝试
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 =新的Microsoft.Office.Interop.Excel.Application
xlWorkBook = xlApp.Workbooks.Add（misValue）
xlWorkSheet = xlWorkBook .Sheets（sheet1）
对于i = 0到DGV.RowCount - 1
对于j = 0到DGV.ColumnCount - 1
对于k As Integer = 1到DGV.Columns.Count
xlWorkSheet.Cells（1，k）= DGV.Columns（k - 1）.HeaderText
xlWorkSheet.Cells（i + 2，j + 1）= DGV（j，i）.Value.ToString（）
下一页
下一页
ProgressBar1.Value + = 1
lb_pcnt.Text =格式（ProgressBar1.Value /（tb_count.Text），p）
下一页
使用xlWorkSheet.Range（A1，BB1）
.Font.Bold = True
.HorizontalAlignment = Excel。 XlVAlign.xlVAlignCenter
结束
xlWorkSheet.Range（A1，BB1）。EntireColumn.AutoFit（）
如果是lb_1。 Text =empl然后
xlWorkSheet.SaveAs（C：\ Expat Database\Output\Expat - Assignees_&lb_time2.Text&.xlsx）
ElseIf lb_1.Text =empl2然后
xlWorkShee t.SaveAs（C：\ Expat Database\Output\Expat - Business Travellers _&lb_time2.Text&.xlsx）
Else
xlWorkSheet。 SaveAs（C：\ Expat Database \Output \Taxes of Expat - Assignees_&lb_time2.Text&.xlsx）
结束如果
xlWorkBook。关闭（）
xlApp.Quit（）
releaseObject（xlApp）
releaseObject（xlWorkBook）
releaseObject（ xlWorkSheet）
Catch ex As Exception
结束尝试
Dim res As MsgBoxResult
res = MsgBox（流程已完成，你想打开文件吗？，MsgBoxStyle.YesNo）
如果（res = MsgBoxResult.Yes）那么
如果lb_1.Text =empl然后
Process.Start（C：\ Expat Database\Output\Expat - Assignees_&lb_time2.Text&.xlsx）
ElseIf lb_1.Text =empl2然后
Process.Start（C：\ Expat Database\Output\Expat - Business Travellers_&lb_time2.Text&.xlsx）
否则
Process.Start（C：\ Expat Database \Output \Taxes of Expat - Assignees_&lb_time2.Text&.xlsx）
结束如果
结束如果
ProgressBar1.Value = ProgressBar1.Minimum
lb_pcnt.Text = ProgressBar1.Value.ToString（p）
ProgressBar1.Visible = False
lb_pcnt.Visible = False
结束如果
Catch ex As Exception
MessageBox.Show（无连接。，身份验证错误，MessageBoxButtons.OK，MessageBoxIcon.Error）
结束尝试
结束Sub
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
推荐答案
您可以尝试使用Interop程序集替代方法甚至不需要安装Office（或Excel）：使用Open XML SDK。
请参阅我过去的答案，其中我引用了其他答案和一篇优秀的CodeProject文章（最后一个链接）：< a href =http://www.codeproject.com/Answers/587548/Howplustoplusaddplusmicrosoftplusexcelplus15-0plus#answer1>如何在MS Visual Studio 2010中添加Microsoft Reference excel 15.0对象库 [ ^ ]。
-SA
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
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
这篇关于更快速地将数据从datagridview导出到excel VB.Net的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
