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 dummyXlApp.Quit() 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 Try 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 Else 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) productsDT.Rows.Remove(foundRow) End If Next Console.WriteLine(productsDT.Rows.Count) 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) ' ' 'etc lastRow += 1 Next End With xlWb.Save() Catch ex As Exception When False 'TODO: Report Finally Try xlWb.Close() xlApp.Quit() 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.