在vb.net中读取Excel时出现Windows错误消息 [英] Windows error messages while reading excel in vb.net

问题描述

早上好，

我有一个功能，可以在vb.net中打开约40个Excel电子表格.它将一一打开它们，然后提取其中的数据并将其写入SQL表.

尽管运行缓慢，但它运行完美.但是，我只能推测，由于该过程非常缓慢，因此在OS(Windows 7)上似乎excel书籍已停止响应，然后在屏幕上弹出一个对话框，表明应用程序已停止响应.关闭excel书并放置对象后，它又消失了.

我已经检查了代码，无论我在哪里看，它似乎都是很标准的.我将withevents子句添加到excel应用程序类中，以尝试查看是否有帮助.

有没有办法抑制这些消息?

Good day,

I have a function that opens about 40 excel spreadsheet in vb.net. It opens them one by one then extracts the data in them and writes it to a SQL table.

It works perfectly, although slow. But, I can only surmise that because the process is so slow it seems to the OS (windows 7) that the excel book has stopped responding and then pops a dialog on the screen to say that the application has stopped responding. It goes away again once the excel book is closed and the object are disposed.

I have checked the code and it seems to be pretty standard where ever I look. I added the withevents clause to the excel application class to try and see if that won''t help.

Is there a way to suppress these messages?

''globel excel vars
Dim WithEvents xlsLookup As Excel.Application
Dim xlsWorkbook As Excel.Workbook
Dim xlsSheet As Excel.Worksheet

''Create excel objects
xlsLookup = New Excel.ApplicationClass
xlsWorkbook = xlsLookup.Workbooks.Open(ShippingHistoryFilename)
xlsSheet = xlsWorkbook.Worksheets(1)

''reading happens here...

Finally
            ''Remove the table from the data set
            If MAIN.data.IsTableLoaded("ShippingHistory") = True Then MAIN.data.DS.Tables.Remove("ShippingHistory")
            If Not IsNothing(xlsWorkbook) Then
                xlsWorkbook.Close()
                xlsLookup.Quit()
                releaseObject(xlsLookup)
                releaseObject(xlsWorkbook)
                releaseObject(xlsSheet)
            End If
        End Try

推荐答案

我在 vb.net Windows7上进行了搜索，但没有响应，而且我得到的几乎所有点击都似乎表明解决方案是将长时间运行的代码放在单独的线程上.

I did a search on vb.net windows7 not responding and almost all the hits I got seemed to indicate that the solution was to have the long-running code on a separate thread.

感谢您提供信息.

我已经尝试过，但是仍然收到消息.这可能是由于其他原因导致的，因为我在实际表单上的进度栏和文本框上遇到了各种交叉线程错误.

不知道数据行和数据表对象会发生什么.

我会试一试并反馈进度.

Hi,

Thanks for your info.

I have tried, but still getting the message. It might be caused by something else though as I am getting all sorts of cross threading errors to the progress bars and textboxes on the actual form.

not sure what will happen with the data row and data table objects.

I will play around a bit and feed back progress.

Dim StufThread As New System.Threading.Thread(AddressOf Me.Import_StuffPack) ''Import all the stuffinglist excel docs 
Dim PickThread As New System.Threading.Thread(AddressOf Me.Import_Packlist)'' import all the packinglist excel docs

If ShippingHistory_Update() = True Then
       StufThread.Start()
       ''PickThread.Start() '' Will implement later
        End If

替换此代码

Replace this code

If Not IsNothing(xlsWorkbook) Then
  xlsWorkbook.Close()
  xlsLookup.Quit()
  releaseObject(xlsLookup)  
  releaseObject(xlsWorkbook) 
  releaseObject(xlsSheet) 
End If

与此

with this

releaseComObject(xlsSheet)
xlsWorkbook.Close(False)
releaseComObject(xlsWorkbook)
xlsLookup.Quit()
releaseComObject(xlsLookup)
'optional
GC.Collect()
GC.WaitForPendingFinalizers()

'<a href="http://support.microsoft.com/kb/317109">http://support.microsoft.com/kb/317109</a>[<a href="http://support.microsoft.com/kb/317109" target="_blank" title="New Window">^</a>]
Private Sub releaseComObject(ByVal o As Object)
  Try
    System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
  Catch
  Finally
    o = Nothing
  End Try
End Sub

1)注意释放对象引用的顺序.

2)绝对不建议使用全局变量，尤其是在使用COM引用和调试时.您应该打开Excel，使用它并释放它.与管理COM引用相比，性能影响最小.

3)启动一个新的进程线程会引起各种各样的麻烦，因为它没有用户界面线程的概念，只能通过调用进行通信.多线程是一个很深的主题，在典型的.net应用程序中很少能获得很大的性能提升.

1) Note the sequence of releasing object references.

2) Globals are never recommended, especially with COM references and debugging. You should open Excel, use it and release it. The performance hit is minimal compared to managing COM references.

3) Starting a new process thread will cause all sorts of trouble because it will have no concept of your user interface thread and can only communicate via invokes. Multithreading is a deep subject and rarely offers much of a performance gain in typical .net applications.

这篇关于在vb.net中读取Excel时出现Windows错误消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！