更快速地将数据从datagridview导出到excel VB.Net

本文介绍了更快速地将数据从datagridview导出到excel VB.Net的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我有以下代码用于使用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（无法导出为ex​​cel文件。导出过程被取消，导出到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 .Horizo​​ntalAlignment = 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） 结束尝试 结束SubHi,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推荐答案Imports Excel = Microsoft.Office.Interop.ExcelPublic 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 IfEnd Sub 这篇关于更快速地将数据从datagridview导出到excel VB.Net的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！