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
