导出多个Excel文件的VB代码挂了 [英] VB code to export multiple Excel files is hanging
问题描述
我对 vb.net 比较陌生，但已经构建了以下代码以从数据表导出到多个 Excel 文件.
I am relatively new to vb.net, but have constructed the following code to export from a datatable to multiple Excel files.
不幸的是代码挂了.
有人能看出我的代码有什么明显的错误吗?
Can anyone see anything obviously wrong with my code?
关于如何解决这些类型的问题的任何提示也会很方便.
Also any tips on how to troubleshoot these types of issues would be handy.
代码如下:
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
'Initialize the objects before use
Dim dataAdapter As New SqlClient.SqlDataAdapter()
Dim dataSet As New DataSet
Dim command As New SqlClient.SqlCommand
Dim datatableMain As New System.Data.DataTable()
Dim connection As New SqlClient.SqlConnection
connection.ConnectionString = "server=inlt01\SQLEXPRESS; database=DaisyBilling; integrated security=yes"
command.Connection = connection
command.CommandType = CommandType.Text
connection.Open()
Using da As New SqlDataAdapter("SELECT * FROM [DaisyBilling].[dbo].[" + DaisyBillingForm.TextBox1.Text + "] ORDER BY [CustomerLookup]", connection)
da.Fill(datatableMain)
connection.Close()
Dim f As FolderBrowserDialog = New FolderBrowserDialog
Try
If f.ShowDialog() = DialogResult.OK Then
'This section help you if your language is not English.
System.Threading.Thread.CurrentThread.CurrentCulture = _
System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add(Type.Missing)
oSheet = oBook.Worksheets(1)
Dim dc As System.Data.DataColumn
Dim dr As System.Data.DataRow
Dim colIndex As Integer = 0
Dim rowIndex As Integer = 0
Dim dv As New DataView(datatableMain)
Dim distinctCustomers As System.Data.DataTable = dv.ToTable(True, "CustomerLookup")
For Each customer As DataRow In distinctCustomers.Rows
'MessageBox.Show("Exporting Customer... " & customer("CustomerLookup").ToString)
Dim customerRows() As DataRow = datatableMain.Select("CustomerLookup = '" & customer("CustomerLookup").ToString & "'")
For Each customerRow As DataRow In customerRows
'Export the Columns to excel file
For Each dc In datatableMain.Columns
colIndex = colIndex + 1
oSheet.Cells(1, colIndex) = dc.ColumnName
Next
'Export the rows to excel file
For Each dr In datatableMain.Rows
rowIndex = rowIndex + 1
colIndex = 0
For Each dc In datatableMain.Columns
colIndex = colIndex + 1
oSheet.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName).ToString()
Next
Next
'Set final path
Dim fileName As String = "\" + DaisyBillingForm.TextBox1.Text + "_" + DateTime.Now.ToString("ddMMyyyy") & "_" & DateTime.Now.ToString("HHmmss") + ".xls"
Dim finalPath = f.SelectedPath + fileName
'TextBox1.Text = finalPath
oSheet.Columns.AutoFit()
'Save file in final path
oBook.SaveAs(finalPath, XlFileFormat.xlWorkbookNormal, Type.Missing, _
Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, _
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
Next
Next
'Release the objects
ReleaseObject(oSheet)
oBook.Close(False, Type.Missing, Type.Missing)
ReleaseObject(oBook)
oExcel.Quit()
ReleaseObject(oExcel)
'Some time Office application does not quit after automation:
'so i am calling GC.Collect method.
GC.Collect()
MessageBox.Show("Export done successfully!")
End If
Catch ex As Exception
MessageBox.Show(ex.Message, "Warning", MessageBoxButtons.OK)
End Try
End Using
End Sub
非常感谢任何帮助.
更新:
当我执行 Break All 时，它显示它挂在这行代码上
When I do a Break All, it shows it hanging on this line of code
oSheet.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName).ToString()
oSheet.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName).ToString()
推荐答案
有关如何解决此类问题的任何提示都会很方便.
any tips on how to troubleshoot these types of issues would be handy.
我建议使用跟踪和调试来诊断程序中的问题.
I'd recommend using tracing and debugging to diagnose issues in your program.
追踪
使用 系统.Diagnostics.Debug.WriteLine 将程序状态写入输出窗口，例如写每一行的前后，写Excel文件的前后等等
Use System.Diagnostics.Debug.WriteLine to write program status to the output window, e.g. before and after you write each row, before and after you write the Excel file, etc.
调试
在调试器中逐步执行您的代码.设置断点并检查它们是否已到达以及程序状态(局部变量窗口)是否符合您的预期.如果您觉得它卡住了，您还可以使用菜单中的 Break All 来查找当前正在执行的内容.
Step through your code in the debugger. Set breakpoints and check that they are reached and the program state (local variables window) is as you expected it. You can also use Break All from the menu to find what is currently executing if you feel it is stuck.
结构
考虑将代码分解成更小的函数，以便更轻松地推理代码.
Consider breaking the code into smaller functions so that you can reason about the code more easily.
有人能看出我的代码有什么明显的错误吗?
Can anyone see anything obviously wrong with my code?
您确定要为每个客户保存一个 Excel 文件吗?
Are you sure you want to save an Excel file for each customer?
如果是这样，也许使用客户的姓名或 ID 作为文件名而不是当前时间，并考虑在每次保存后清除 Excel 工作表.
If so perhaps use the customer's name or id for the file name instead of the current time, and consider clearing the Excel sheet after each save.
不应在 UI 线程上运行长时间运行的操作，请考虑在线程池或后台工作线程上运行此操作并显示 进度条.
Long running actions should not be run on the UI thread, consider running this on the thread pool or a background worker thread and showing a progress bar.
我怀疑操作需要很长时间才能完成，但由于没有反馈(进度条等)，感觉像是挂了.
I suspect that the operation is taking a long time to complete, but as there is no feedback (progress bar etc.) it feels like it is hanging.
还要注意变量 colIndex 应该在每次迭代时设置为零，即
Also note that the variable colIndex should be set to zero for each iteration, i.e.
For Each customerRow As DataRow In customerRows
'Set colIndex to zero here
Dim colIndex As Integer = 0
'Export the Columns to excel file
For Each dc In datatableMain.Columns
colIndex = colIndex + 1
oSheet.Cells(1, colIndex) = dc.ColumnName
Next
这篇关于导出多个Excel文件的VB代码挂了的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
