VB.NET code for storing purchase transactions in Excel

Question

I have written a VB.NET program to store purchase transactions into an Excel file. The code runs without any errors and functions as expected, but it takes a significant amount of time to complete, especially when processing transactions.

Public Sub Purchase()
Dim excelApp As New Microsoft.Office.Interop.Excel.Application()
 Dim workbook As Workbook = Nothing
 Dim worksheet As Worksheet = Nothing
 Dim nextRow As Integer
 ' Determine which sheet to use based on the transaction type
 Dim sheetName As String = "Purchase"
 'Purchase
 Try
 workbook = excelApp.Workbooks.Open(excelPath)
 worksheet = CType(workbook.Sheets(sheetName), Worksheet)
 nextRow = worksheet.Cells(worksheet.Rows.Count, 1).End(XlDirection.xlUp).Row + 1
 For Each item In dgPurchaseDetails.Items
 Dim dataRow = TryCast(item, PurchaseDetail)
 If dataRow IsNot Nothing Then
 worksheet.Cells(nextRow, 1).Value = nextRow - 1 ' S.No
 worksheet.Cells(nextRow, 2).Value = cmbSupplierName.SelectedItem.ToString().Trim()
 worksheet.Cells(nextRow, 3).Value = txtInvoiceNumber.Text.Trim()
 worksheet.Cells(nextRow, 4).Value = dataRow.Product
 worksheet.Cells(nextRow, 5).Value = dataRow.Unit
 worksheet.Cells(nextRow, 6).Value = dataRow.Price
 worksheet.Cells(nextRow, 7).Value = dataRow.Total
 worksheet.Cells(nextRow, 8).Value = dataRow.Discount
 worksheet.Cells(nextRow, 9).Value = dataRow.GST
 worksheet.Cells(nextRow, 10).Value = dataRow.FTotal
 nextRow += 1
 End If
 Next
 Dim bTotal As Decimal
 Dim roundedTotal As Decimal
 Dim fractionalPart As Decimal = bTotal - Math.Floor(bTotal)
 If fractionalPart < 0.5D Then
 roundedTotal = Math.Floor(bTotal)
 Else
 roundedTotal = Math.Ceiling(bTotal)
 End If
 worksheet.Cells(nextRow - 1, 11).Value = roundedTotal
 worksheet.Cells(nextRow - 1, 12).Value = dpPurchaseDate.Text
 worksheet.Cells(nextRow - 1, 13).Value = DateTime.Now.ToShortDateString()
 workbook.Save()
 Catch ex As Exception
 MessageBox.Show("An error occurred: " & ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error)
 End Try
 'Day book
 Try
 If TabControl.SelectedItem IsNot Nothing Then
 ' Get the selected TabItem
 Dim selectedTab As TabItem = CType(TabControl.SelectedItem, TabItem)
 ' Get the Header text of the selected tab
 Dim selectedTabText As String = selectedTab.Header.ToString()
 ' Extract specific part (e.g., before "-")
 Dim Tabb As String = selectedTabText.Split("-"c)(0)
 worksheet = CType(workbook.Sheets("Daybook"), Worksheet)
 Dim row As Integer = worksheet.Cells(worksheet.Rows.Count, 1).End(XlDirection.xlUp).Row + 1
 Dim SNo As Integer = 1 ' Default value if this is the first entry
 If row > 2 Then
 SNo = Convert.ToInt32(worksheet.Cells(row - 1, 1).Value) + 1
 End If
 worksheet.Cells(row, 1).Value = SNo
 worksheet.Cells(row, 2).Value = Tabb
 worksheet.Cells(row, 3).Value = cmbSupplierName.Text
 worksheet.Cells(row, 4).Value = txtInvoiceNumber.Text
 worksheet.Cells(row, 5).Value = txtbtotal.Text
 worksheet.Cells(row, 6).Value = dpPurchaseDate.Text
 worksheet.Cells(row, 7).Value = Date.Today()
 workbook.Save()
 End If
 Catch ex As Exception
 MessageBox.Show("An error occurred: " & ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error)
 End Try
 'update stock
 Try
 worksheet = CType(workbook.Sheets("Stock"), Worksheet)
 Dim lastRow As Integer = worksheet.Cells(worksheet.Rows.Count, 1).End(XlDirection.xlUp).Row
 Dim datagrid As DataGrid = dgPurchaseDetails
 For Each item In datagrid.Items
 Dim dataRow = TryCast(item, PurchaseDetail)
 If dataRow Is Nothing Then Continue For
 Dim stockItem As String = dataRow.Product?.Trim()
 Dim quantityStr As String = dataRow.Unit
 Dim quantity As Decimal
 If String.IsNullOrEmpty(quantityStr) OrElse Not Decimal.TryParse(New String(quantityStr.Where(AddressOf Char.IsDigit).ToArray()), quantity) Then
 MessageBox.Show($"Invalid quantity format: {quantityStr}", "Error", MessageBoxButton.OK, MessageBoxImage.Error)
 Return
 End If
 Dim stockFound As Boolean = False
 For row As Integer = 2 To lastRow
 Dim currentItem As String = Convert.ToString(worksheet.Cells(row, 1).Value)?.Trim()
 If Not String.IsNullOrEmpty(currentItem) AndAlso currentItem.Equals(stockItem, StringComparison.OrdinalIgnoreCase) Then
 Dim existingQty As Decimal
 Decimal.TryParse(Convert.ToString(worksheet.Cells(row, 2).Value), existingQty)
 worksheet.Cells(row, 2).Value = existingQty + quantity
 worksheet.Cells(row, 3).value = dataRow.Total
 worksheet.Cells(row, 4).Value = Math.Round(dataRow.FTotal / quantity, 2) ' Update price per unit
 stockFound = True
 Exit For
 End If
 Next
 If Not stockFound Then
 lastRow += 1
 worksheet.Cells(lastRow, 1).Value = stockItem
 worksheet.Cells(lastRow, 2).Value = quantity
 worksheet.Cells(lastRow, 3).value = dataRow.Total
 worksheet.Cells(lastRow, 4).Value = Math.Round(dataRow.FTotal / quantity, 2)
 End If
 Next
 workbook.Save()
 Catch ex As Exception
 MessageBox.Show($"An error occurred: {ex.Message}", "Error", MessageBoxButton.OK, MessageBoxImage.Error)
 End Try
 Dim balanceValue As Double
 If Not Double.TryParse(txtnbalance.Text, balanceValue) Then
 MessageBox.Show("Please enter a valid numeric balance.", "Validation Error", MessageBoxButton.OK, MessageBoxImage.Warning)
 Exit Sub
 End If
 Try
 ' Validate input
 Dim searchName As String = cmbSupplierName.SelectedItem?.ToString()?.Trim()
 If String.IsNullOrEmpty(searchName) Then
 MessageBox.Show("Please select a name.", "Validation Error", MessageBoxButton.OK, MessageBoxImage.Warning)
 Exit Sub
 End If
 ' Initialize Excel application and open workbook
 excelApp = New Microsoft.Office.Interop.Excel.Application()
 workbook = excelApp.Workbooks.Open(excelPath)
 worksheet = CType(workbook.Sheets("Creditors"), Worksheet)
 ' Find the last used row in column 1 (A)
 Dim totalRows As Long = worksheet.Cells(worksheet.Rows.Count, 1).End(XlDirection.xlUp).Row
 Dim foundValue As Boolean = False
 ' Iterate through rows to find the match
 For row As Integer = 2 To totalRows
 If String.Equals(Convert.ToString(worksheet.Cells(row, 1).Value)?.Trim(), searchName, StringComparison.OrdinalIgnoreCase) Then
 worksheet.Cells(row, 2).Value = balanceValue ' Update balance
 foundValue = True
 Exit For
 End If
 Next
 ' Notify if the name was not found
 If Not foundValue Then
 MessageBox.Show($"{searchName} not found in the {sheetName} sheet.", "Information", MessageBoxButton.OK, MessageBoxImage.Information)
 Else
 ' Save the workbook
 workbook.Save()
 End If
 Catch ex As Exception
 MessageBox.Show("Error updating data: " & ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error)
 Finally
 ' Clean up
 If workbook IsNot Nothing Then workbook.Close(False)
 If excelApp IsNot Nothing Then excelApp.Quit()
 ReleaseObject(worksheet)
 ReleaseObject(workbook)
 ReleaseObject(excelApp)
 End Try
 End Sub