Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ASP.NET Excel Exception VB.NET , +
In Asp.net When working with multi threading via Excel Interop using the below code:
Public Class ImportService
Public Function ImportsFiles(ByVal files as list(Of String))
Dim rowEffect As Integer = 0
For j as Integer=0 to files.Count-1
dim fileAddress=files(j)
Dim app As New Application
Dim Wbook As Workbook
 
Try
    Wbook = app.Workbooks.Open(fileAddress, [ReadOnly]:=True)
    For i As Integer = 1 To Wbook.Sheets.Count
        If Not Wbook.Sheets(i).Name.ToString.ToLower.Contains("partial read of load profile") Then
            Dim con As New System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & fileAddress & "; Extended Properties=""Excel 12.0 Xml;HDR=No;IMEX=1"";")
            Dim cmd As New OleDbCommand
            Dim s As String = "SELECT * FROM [" & Wbook.Sheets(i).Name & "$]"
            cmd.CommandText = s
            cmd.Connection = con
            Dim da As New OleDbDataAdapter
            da.SelectCommand = cmd
            Dim dt As New System.Data.DataTable
            da.Fill(dt)
            Dim err As New Dictionary(Of String, String)
            Dim fileDate As String = Wbook.Sheets(i).Name.ToString.Trim.Replace(" ", "/")
            rowEffect += InsertLps(Path.GetFileName(fileAddress), fileDate, dt.Rows, err)
            dt.Dispose()
            da.Dispose()
            con.Dispose()
            cmd.Dispose()
        End If
        ReleaseCOMObject(Wbook.Sheets(i))
        Next
    Wbook.Close(SaveChanges:=False)
Catch ex As Exception
    summary.Add(fileAddress, ex.Message)
Finally
    GC.Collect()
    GC.WaitForPendingFinalizers()
    GC.Collect()
    GC.WaitForPendingFinalizers()
    If app.Workbooks IsNot Nothing Then
        For Each wb In app.Workbooks
            For Each ws In wb.Worksheets
                ReleaseCOMObject(ws)
            Next
            wb.Close(False)
            ReleaseCOMObject(wb)
        Next
    End If
    app.Workbooks.Close()
    app.Quit()
    ReleaseCOMObject(app)
    GC.WaitForFullGCComplete()
Next
End Function
End Class
An error occurs:
Transition into COM context 0x946d20 for this RuntimeCallableWrapper failed with the following error: The object invoked has disconnected from its clients. (Exception from HRESULT: 0x80010108 (RPC_E_DISCONNECTED)). This is typically because the COM context 0x946d20 where this RuntimeCallableWrapper was created has been disconnected or it is busy doing something else. Releasing the interfaces from the current COM context (COM context 0x946c68). This may cause corruption or data loss. To avoid this problem, please ensure that all COM contexts/apartments/threads stay alive and are available for context transition, until the application is completely done with the RuntimeCallableWrappers that represents COM components that live inside them.
i'm trying to work with Quartz.net and more triggers do job in background. when 2 trrigger start job by this code:
 
Public Class ImportJob
Implements IJob
Public Sub Execute(context As IJobExecutionContext) Implements IJob.Execute
    Dim importHelpers As New ImportFilesHelpers
    Dim exts As New List(Of String)
    exts.Add(".xls")
    Dim files = importHelpers.GetFiles(ImportFilesHelpers.BaseAssress, exts)
    Dim import = New ImportService()
    import.ImportsFiles(files)
End Sub
End Class
Following Error Acoures in This Line:
Dim fileDate As String = Wbook.Sheets(i).Name.ToString.Trim.Replace(" ", "/")
please solve my problem.
Posted 2-Oct-12 21:31pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

You can try locking all you want, but it's never going to work.
 
You cannot use Excel Automation on a background thread. It must be kept on the startup thread.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

i'm use synchlock like this:
Public Class ImportService
Dim thisLock As New Object
...
Public Function ImportsFiles(ByVal files as list(Of String))
 SyncLock thisLock
...
End SyncLock
End Function
End Class
but not work.why?
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Try locking the operation : http://stackoverflow.com/questions/915850/lock-statement-in-vb-net[^]
 
Excel files are not meant to be used in a multithreaded environment.
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 205
1 Sergey Alexandrovich Kryukov 200
2 Rob Philpott 100
3 jlopez788 99
4 Prakriti Goyal 97
0 OriginalGriff 6,837
1 Sergey Alexandrovich Kryukov 5,589
2 Maciej Los 3,479
3 Peter Leow 3,323
4 DamithSL 2,505


Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 3 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100