Spreadsheet Open function is not Synchronous

[Spotty]

Have a simple application which loads an xls file and then extracts some data.   As the open method did not appear to be asynchronous and awaitable I called open and then saw the spreadsheet populated with no values being retrieved from the spreadsheet.   However when a manually called method by adding to a button and clicking it populated correctly.    Eventually tracked down the work workbook loaded event and putting code in there seemed to work but their was no inidcation on the method that the open was running asynchronously and perhaps the async/await pattern would be better or as an overload.

Imports Syncfusion.Windows.Forms.CellGrid

Imports Syncfusion.Windows.Forms.Spreadsheet.Helpers

Public Class Form1

    Dim x As New Dictionary(Of String, Object)

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Spreadsheet1.Open("C:\Test\Book1.xlsx")

    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click

        PopulateStyles() '<=  Here will reuslt in items populating

        If x.Count = 0 Then

            MsgBox("No Styles populated")

        End If

    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

        ApplyStyles(0)

    End Sub

    Sub PopulateStyles()

        If Spreadsheet1.Workbook.Worksheets.Count > 1 Then

            Dim source = Spreadsheet1.Workbook.Worksheets(0)

            x.Clear()

            For irow = 2 To 6

                Dim t = source.Range(irow, 1).Value

                x.Add(t, source.Range(irow, 1).CellStyle)

            Next

        End If

    End Sub

    Sub ApplyStyles(sheetidx As Integer)

        For isheet = 1 To 3

            Dim source = Spreadsheet1.Workbook.Worksheets(isheet)

            For irow = 2 To 10

                Dim t = source.Range(irow, 1).Value

                Try

                    Dim cs = x(t)

                    If cs IsNot Nothing Then

                        source.Range(irow, 1).CellStyle = CType(cs, Syncfusion.XlsIO.IStyle)

                    End If

                Catch ex As Exception

                End Try

            Next

        Next

    End Sub

End Class

Attachment: Book1_7ec067dc.zip

[Mohanraj Gunasekaran]

Hi Spotty, 

Thanks for using Syncfusion product. 

By default, Spreadsheet does not have the direct support for OpenAsync so, can you please share your Async implementation to open the .xlsx file. Also, we have prepare the sample based on your code part. If it is possible please make the changes in below attached sample to reproduce your reported scenario. It will be helpful us to provide the solution at the earliest. 

Sample link: http://www.syncfusion.com/downloads/support/directtrac/general/ze/VB-1178844855  

Regards, 

Mohanraj G 

[Spotty]

Hi

Here's an example of an implemention using Async/Await to handle the asynchronicity of the spreadsheet open functionality   If you look at the Form_Load you will notice that the Import (Which is simply a wrapper around existing Open method) only completes when the event is fired which sets the semaphore (TaskCompletionSource) to true.    

This results in the PopulateStyles() and  ApplyStyles(0) methods only getting called after the worksheet is loaded.     I'm working around the existing method implementation but this should be do-able to make the open method an asynchronous method.

        '//Used as a semaphore to identify when load is complete

Dim tcs As New TaskCompletionSource(Of Boolean)

        Public Async Function Import(name As String) As Task

            Me.spreadsheet.Open(name)

            Await tcs.Task

        End Function

        Public Async Sub Form1_Load() Handles Me.Load

   '//Handler required to trigger the semaphor event

            AddHandler Me.spreadsheet.WorkbookLoaded, AddressOf Spreadsheet_WorkbookLoaded

       '//Can now call the Import which calls the Open event 

   '   - only continues on when workbook is fully loaded

            Await Import("../../File/Book1.xlsx")

            PopulateStyles()

            ApplyStyles(0)

        End Sub

        Private Sub Spreadsheet_WorkbookLoaded(ByVal sender As Object, ByVal args As Syncfusion.Windows.Forms.Spreadsheet.Helpers.WorkbookLoadedEventArgs)

            tcs.SetResult(True) '//Sets the semaphore when workbook is loaded

        End Sub

[Mohanraj Gunasekaran]

Hi Spotty,  

Thanks for your update. 

We have planned to implemnt the OpenAsync method to handle the Spreadsheet.open functionality. So, we have logged the feature “Provide OpenAsync support to open the excel file” for this. It will be available on our next main release 2019 Volume1 which will be expected at first week of March 2019. 

Regards, 

Mohanraj G