# Updating Excel sheet with information from a DataTable

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)
'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
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.