Basically, I'm trying to update an excel sheet using a DataTable (The columns structure between the two are not the same) with respect to the following rules:

  • Whenever an item is matched (by the product code), update a few column values.
  • Whenever an item is missing in the DataTable, mark it as 'removed' (change one column in the sheet).
  • Whenever an item is missing in the sheet, add it using the DataTable.

Using the following code with 18k+ records in the DataTable, and 43k+ rows in the Excel sheet, it takes over 15 minutes to finish updating which is too long.

Here's my current code:

Private Sub UpdateSheet(productsDT As DataTable, sheetPath As String)
    'Indices variables are retrieved from another class.
    'The following is just for clarification:

    'pCodeColIndex = 1    | barCodeColIndex = 2 | dealerPriceColIndex = 3 | titleColIndex = 4
    'endDateColIndex = 5  | srpColIndex = 6     | statusColIndex = 7      | stockColIndex = 8
    'newStockColIndex = 9 | discColIndex = 12   | categColIndex = 13      | formatColIndex = 14
    'genreColIndex = 15   | priceColIndex = 16  | newPriceColIndex = 17   | newItemColIndex = 29
    'RemovedItemColIndex = 30

    Dim dummyXlApp As New Excel.Application
    Dim xlApp As New Excel.Application

    xlApp.Visible = False
    xlApp.ScreenUpdating = False
    xlApp.DisplayAlerts = False
    Dim xlWb As Excel.Workbook = Nothing
    Dim xlsheet As Excel.Worksheet = Nothing
    Dim xlRange As Excel.Range = Nothing

        xlWb = xlApp.Workbooks.Open(sheetPath)
        xlsheet = xlWb.Worksheets(1)

        With xlsheet
            Dim lastRow As Integer = .Cells(.Rows.Count, 1).End(Excel.XlDirection.xlUp).Row

            For i = 2 To lastRow
                Dim rngPCode As Excel.Range = .Cells(i, pCodeColIndex)
                Dim pCode As String = rngPCode.Value.ToString()
                Dim foundRow As DataRow = productsDT.Rows.Find(pCode)
                If foundRow Is Nothing Then
                    'Item doesn't exist in the DataTable, hence mark as removed.
                    Dim rngRemoved As Excel.Range = .Cells(i, RemovedItemColIndex)
                    rngRemoved.Value = 1
                    Dim rngStatus As Excel.Range = .Cells(i, statusColIndex)
                    Dim rngStock As Excel.Range = .Cells(i, stockColIndex)
                    Dim rngNewStock As Excel.Range = .Cells(i, newStockColIndex)
                    Dim rngPrice As Excel.Range = .Cells(i, priceColIndex)
                    Dim rngNewPrice As Excel.Range = .Cells(i, newPriceColIndex)

                    rngStatus.Value = foundRow.Item(6)
                    If Not String.IsNullOrEmpty(rngNewStock.Value) Then
                        rngStock.Value = rngNewStock.Value
                    End If
                    rngNewStock.Value = foundRow.Item(7)
                    If Not String.IsNullOrEmpty(rngNewPrice.Value) Then
                        rngPrice.Value = rngNewPrice.Value
                    End If
                    rngNewPrice.Value = foundRow.Item(8)

                End If

            For Each row As DataRow In productsDT.Rows
                Dim rngPCode As Excel.Range = .Cells(lastRow + 1, pCodeColIndex)
                Dim rngBarcode As Excel.Range = .Cells(lastRow + 1, barcodeColIndex)
                Dim rngDealerPrice As Excel.Range = .Cells(lastRow + 1, dealerPriceColIndex)
                Dim rngTitle As Excel.Range = .Cells(lastRow + 1, titleColIndex)
                Dim rngEndDate As Excel.Range = .Cells(lastRow + 1, endDateColIndex)
                Dim rngSRP As Excel.Range = .Cells(lastRow + 1, srpColIndex)
                Dim rngStatus As Excel.Range = .Cells(lastRow + 1, statusColIndex)
                Dim rngStock As Excel.Range = .Cells(lastRow + 1, stockColIndex)
                Dim rngPrice As Excel.Range = .Cells(lastRow + 1, priceColIndex)
                Dim rngDisc As Excel.Range = .Cells(lastRow + 1, discColIndex)
                Dim rngCateg As Excel.Range = .Cells(lastRow + 1, categColIndex)
                Dim rngFormat As Excel.Range = .Cells(lastRow + 1, formatColIndex)
                Dim rngGenre As Excel.Range = .Cells(lastRow + 1, genreColIndex)
                Dim rngNewItem As Excel.Range = .Cells(lastRow + 1, newItemColIndex)
                rngPCode.Value = row.Item(0)
                rngBarcode.Value = row.Item(1)
                lastRow += 1
        End With

    Catch ex As Exception When False
        'TODO: Report
        Catch ex As Exception

        End Try
    End Try
End Sub

Now, I know that I should minimize the number of times I make calls to a COM object (e.g 'Excel.Range`), and there's a very popular approach of creating a two-dimensional array, load all the values to it at once when retrieving data, and then set it to a range when saving values after modifications.

Well, I believe I could do that when retrieving the values. However, I probably won't be able to do that and set the values of all range at once because some of the columns values are formulas calculations.

Note: Although some of the columns include formulas, I didn't set the xlApp.Calculations to manual in this code, because the values changed by this code doesn't affect the calculations, and I've tried that anyway and it didn't make any difference.

Any suggestions of how to optimize this code to increase performance?

Side note: Although my code is in VB, any answer with C# code is welcomed.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.