CODEWITHSUNDEEP
asp.netvb.netexcelgridview
vishal
vishal

Reputation: 11

how to maintain connection with excel with rapidly data fetching

i am making a website for trading, with trading feeds coming from a source in an excel sheet. I have to show data from the excel sheet in a gridview. When i make connection it will fail due to rapidly changing data; each cell in the sheet changes value 1-3 times per second. I am using an Ajax Timer of interval 100. Here is my code:

  Public Function RetrieveExcelData(ByVal excelSheetName As String, ByVal sheetNumber As Integer) As DataSet
        Dim objConn As OleDbConnection = Nothing
        Dim dt As System.Data.DataTable = Nothing
        Try
            ' Connection String.
            Dim connString As [String] = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Users\Vishal\Desktop\TESTING COLOURfor web1.xls;Extended Properties=Excel 8.0;"
            ' Create connection object by using the preceding connection string.
            objConn = New OleDbConnection(connString)
            ' Open connection with the database.
            objConn.Open()
            ' Get the data table containg the schema guid.
            dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
            If dt Is Nothing Then
                Return Nothing
            End If
            Dim excelSheets As [String]() = New [String](dt.Rows.Count - 1) {}
            Dim i As Integer = 0
            ' Add the sheet name to the string array.
            For Each row As DataRow In dt.Rows
                excelSheets(i) = row("TABLE_NAME").ToString()
                i += 1
                If i = sheetNumber Then
                    Exit For
                End If
            Next
            Dim excelCommand As New OleDbCommand("Select * from [" + excelSheets(sheetNumber - 1) & "]", objConn)
            Dim excelAdapter As New OleDbDataAdapter(excelCommand)
            Dim excelDataSet As New DataSet()
            excelAdapter.Fill(excelDataSet)
            Return excelDataSet
        Catch ex As OleDbException
            Throw ex
        Catch ex As Exception
            Throw ex
        Finally
            ' Clean up.
            If objConn IsNot Nothing Then
                objConn.Close()
                objConn.Dispose()
            End If
            If dt IsNot Nothing Then
                dt.Dispose()
            End If
        End Try
    End Function

Upvotes: 0

Views: 604

Answers (1)

Juliusz
Juliusz

Reputation: 2105

To be honest - I cannot see how it can work. You are trying to use Excel spreadsheet as a database to store and retrieve data in real-time, for which Excel was never intended or designed.

You have mentioned that the Excel gets data several times per second. What is the source of data? RTD component? Bloomberg API? I would try to avoid the middle step of storing data in a spreadsheet.

Upvotes: 2

Related Questions