Can Excel + VB.NET be accelerated?

[ademontis]

Hi all,
I recently have been working on developing a CRM for my wife's beauty center, using VB.NET and managing data via Excel files.
All works fine, i could implement all functionalities, but the operations are way too slow... in order to open the weekly calendar it takes up to 10-12 seconds with 1 appointment per day in the week... to load the contact list (200 records) and show them in a datagridview it takes around 30-35 secs.
I was wondering if there is a way to make the operations quicker...
The typical VB/Excel interaction happens with this kind of structure:

Imports Microsoft.Office.Interop.Excel
Imports System.Data

Public Class Form1
    Private Sub LoadExcelData(filePath As String)
        Dim excelApp As New Application()
        Dim excelWorkbook As Workbook = excelApp.Workbooks.Open(filePath)
        Dim excelWorksheet As Worksheet = CType(excelWorkbook.Sheets(1), Worksheet)
        Dim range As Range = excelWorksheet.UsedRange

        Dim dt As New DataTable()
        dt.Columns.Add("client_name")
        dt.Columns.Add("birthdate_registration_date")
        dt.Columns.Add("init_height")
        dt.Columns.Add("init_weight")
        dt.Columns.Add("level")
        dt.Columns.Add("score")
        dt.Columns.Add("location")

        For i As Integer = 2 To range.Rows.Count
            Dim row As DataRow = dt.NewRow()
            row("client_name") = CType(range.Cells(i, 1), Range).Value
            row("birthdate_registration_date") = CType(range.Cells(i, 2), Range).Value
            row("init_height") = CType(range.Cells(i, 3), Range).Value
            row("init_weight") = CType(range.Cells(i, 4), Range).Value
            row("level") = CType(range.Cells(i, 5), Range).Value
            row("score") = CType(range.Cells(i, 6), Range).Value
            row("location") = CType(range.Cells(i, 7), Range).Value
            dt.Rows.Add(row)
        Next

        DataGridView1.DataSource = dt

        excelWorkbook.Close(False)
        excelApp.Quit()

        ReleaseObject(excelWorksheet)
        ReleaseObject(excelWorkbook)
        ReleaseObject(excelApp)
    End Sub

    Private Sub ReleaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class

[.paul.]

You could probably change this…

For i As Integer = 2 To range.Rows.Count
    Dim row As DataRow = dt.NewRow()
    row("client_name") = CType(range.Cells(i, 1), Range).Value
    row("birthdate_registration_date") = CType(range.Cells(i, 2), Range).Value
    row("init_height") = CType(range.Cells(i, 3), Range).Value
    row("init_weight") = CType(range.Cells(i, 4), Range).Value
    row("level") = CType(range.Cells(i, 5), Range).Value
    row("score") = CType(range.Cells(i, 6), Range).Value
    row("location") = CType(range.Cells(i, 7), Range).Value
    dt.Rows.Add(row)
Next

To this…

For i As Integer = 2 To range.Rows.Count
    dt.Rows.Add(New Object() {range.Cells(i, 1).Value, range.Cells(i, 2).Value, range.Cells(i, 3).Value, _
        range.Cells(i, 4).Value, range.Cells(i, 5).Value, range.Cells(i, 6).Value, range.Cells(i, 7).Value})
Next

[ademontis]

You could probably change this…

For i As Integer = 2 To range.Rows.Count
    Dim row As DataRow = dt.NewRow()
    row("client_name") = CType(range.Cells(i, 1), Range).Value
    row("birthdate_registration_date") = CType(range.Cells(i, 2), Range).Value
    row("init_height") = CType(range.Cells(i, 3), Range).Value
    row("init_weight") = CType(range.Cells(i, 4), Range).Value
    row("level") = CType(range.Cells(i, 5), Range).Value
    row("score") = CType(range.Cells(i, 6), Range).Value
    row("location") = CType(range.Cells(i, 7), Range).Value
    dt.Rows.Add(row)
Next

To this…

For i As Integer = 2 To range.Rows.Count
    dt.Rows.Add(New Object() {range.Cells(i, 1).Value, range.Cells(i, 2).Value, range.Cells(i, 3).Value, _
        range.Cells(i, 4).Value, range.Cells(i, 5).Value, range.Cells(i, 6).Value, range.Cells(i, 7).Value})
Next

Thank you Paul, i will give it a try today and post a feedback... I understand the second should be faster since it directly adds to the datatable skipping the datarow creation.

[Shaggy Hiker]

I don't have a great deal of experience working with Excel via .NET, but I do have the feeling that it is generally slower. Excel is primarily a spreadsheet program rather than a database program. You can use it as a database, after a fashion, but it isn't a database. That will likely cost you a bit. Still, there are likely ways to improve the performance.

You can query an Excel spreadsheet directly using ADO.NET, and that would almost certainly boost the performance quite a bit, though it would be considerably different from what you have written. It would avoid all the type conversion stuff, though, and improve the performance of the loop. It would look like the loop doesn't exist, but the loop would have to exist somewhere, even if you weren't the one to write it.

Beyond that, you might improve the performance even further by using an actual database for storage. The simplest one would be Access, but there are other options. Of course, if you aren't familiar with databases, that's a further level of learning.

[kareninstructor]

Although there are no VB.NET examples, ExcelMapper can be used with VB.NET, and it is lightning fast.