2

I'm trying to use an Excel file like a database. I've written this code to open my Excel document that I'll use to work

Private Sub Importa_XLS(ByVal fileData As String, ByVal dbVuoto As String, ByVal dbDest As String)

    Dim bw As BackgroundWorker = New BackgroundWorker
    bw.WorkerSupportsCancellation = True
    bw.WorkerReportsProgress = True
    If My.Computer.FileSystem.FileExists(dbDest) Then My.Computer.FileSystem.DeleteFile(dbDest)
    My.Computer.FileSystem.CopyFile(dbVuoto, dbDest)

    Dim capitoli As New cCapitoli
    Dim paragrafi As New cParagrafi
    Dim voci As New cVoci

    Dim fileStream As FileStream = New FileStream(fileData, FileMode.Open)
    Dim file(fileStream.Length) As Byte
    Dim percorso As String
    percorso = "C:\Users\User\Desktop\prova.xlsx"
    System.Threading.Thread.Sleep(3000)
    fileStream.Read(file, 0, fileStream.Length)

    fileStream.Close()

    Dim ExcelEngine As ExcelEngine = New ExcelEngine()
    Dim application As IApplication = ExcelEngine.Excel
    Dim workbook As IWorkbook = application.Workbooks.Open(New MemoryStream(file), ExcelOpenType.Automatic)
    bw.CancelAsync()
    Dim gecc As New Syncfusion.GridExcelConverter.GridExcelConverterControl

    For Each sheet As IWorksheet In workbook.Worksheets

        Dim grid As New GridModel
        bw.RunWorkerAsync(percorso)
        System.Threading.Thread.Sleep(3000)
        gecc.ExcelToGrid(sheet, grid)
        bw.CancelAsync()

and when the compiler executes this instruction gecc.ExcelToGrid(sheet, grid) I wait one minute and then Visual Studio shows me this error:

The CLR has been unable to transition from COM context 0xcad028 to COM context 0xcad0e0 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations.

I read online for a solution, so I tried to use a Backgroundworker, but the error message there's still here. Maybe I don't understand very well how to use Backgroundworker. Can anyone explain me where I'm doing wrong?

Matteo
  • 205
  • 7
  • 16
  • Do you expect this operation to take more than 60 seconds? – djv Sep 21 '17 at 14:09
  • @djv if I use an Access db it takes less than 60 seconds, so i thought, more or less, the waiting time were the same . I tried to disable the Context Switch Deadlock from the Exception settings, but after about 5 minutes I haven't any result. – Matteo Sep 21 '17 at 14:14
  • This third party control, `Syncfusion.GridExcelConverter.GridExcelConverterControl` (of which I know nothing about), is doing some COM stuff behind the scenes which may have errors, but they aren't handled properly / passed back to .NET. You may need to contact their customer support. – djv Sep 21 '17 at 14:27
  • I'm triying to execute the program without the control of the Context Switch. It's been 15 minutes, but nothing. I'll try to check your control. Thank you for the hint – Matteo Sep 21 '17 at 14:31
  • 1
    It's *your* control, the one you are using. Since you don't have the code, it's tough to debug what's actually going wrong. It's something in COM unfortunately. – djv Sep 21 '17 at 14:58

0 Answers0