I have a compare button which starts a background worker.
Private Sub btnCompare_Click(sender As Object, e As EventArgs) Handles btnCompare.Click
m_ProgressBar = New ProgressBar
m_ProgressBar.Show()
m_ProgressBar.txtBlockMainProgress.Dispatcher.BeginInvoke(Sub()
m_ProgressBar.txtBlockMainProgress.Text = "Comparing excel file(s)... Please wait. This might take a while."
End Sub)
Me.backgroundWorker = New BackgroundWorker
Me.backgroundWorker.WorkerReportsProgress = True
Me.backgroundWorker.WorkerSupportsCancellation = True
AddHandler Me.backgroundWorker.DoWork, AddressOf worker_DoWork
AddHandler Me.backgroundWorker.ProgressChanged, AddressOf worker_ProgressChanged
AddHandler Me.backgroundWorker.RunWorkerCompleted, AddressOf worker_RunWorkerCompleted
Me.backgroundWorker.RunWorkerAsync()
TaskbarItemInfo.ProgressState = Shell.TaskbarItemProgressState.Normal
End Sub
The DoWork Event of my backgroundworker initialize a class Compare that has a method named Compare which accept a backgroundworker as parameter
Private Sub worker_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs)
blnCompareDone = True
objExcelCompare = New Compare
With objExcelCompare
.SetThreshold = dblThreshold
.CompareToBestMatchData = blnBestMatchFlg
.CompareMerge = blnCompareMerge
.CompareTextWrap = blnCompareTextWrap
.CompareTextAlign = blnCompareTextAlign
.CompareOrientation = blnCompareOrientation
.CompareBorder = blnCompareBorder
.CompareBackColor = blnCompareBackColor
.CompareFont = blnCompareFont
.NoOfPages = intNoOfPages
.Page_Location_1 = objLocation_1
.Page_Location_2 = objLocation_2
.RemovedColumn = objRemoveCol
.RemovedRow = objRemoveRow
.AddedColumn = objAddCol
.AddedRow = objAddRow
.DataChange = objChangeData
.Compare(objWorksheet_1, objWorksheet_2, Me.backgroundWorker, e)
objEquivalentColumns = .EquivalentColumns
objEquivalentRows = .EquivalentRows
objValueResult_1 = .ValueResult_1
objValueResult_2 = .ValueResult_2
objFormatResult = .FormatResult
End With
End Sub
Inside the compare method i use ReportProgress to update my UI after comparing each page of excel file. There are total of 2 pages so the updating of progress bar will be 50% then 100%.
Public Sub Compare(ByRef p_objWorkSheet_1 As Worksheet, ByRef p_objWorkSheet_2 As Worksheet, ByRef p_backgroundWorker As BackgroundWorker, ByRef e As System.ComponentModel.DoWorkEventArgs)
If p_objWorkSheet_1 Is Nothing OrElse p_objWorkSheet_2 Is Nothing Then
'Error when no instance on either worksheets was found
Throw New Exception("No instances of worksheet is found.")
Exit Sub
End If
'********************Start of Comparison*********************
objExcelData_1 = New Dictionary(Of Integer, Dictionary(Of Tuple(Of Integer, Integer), Range))
objExcelData_2 = New Dictionary(Of Integer, Dictionary(Of Tuple(Of Integer, Integer), Range))
objEquivalentColumns = New Dictionary(Of Integer, Dictionary(Of Integer, Integer))
objEquivalentRows = New Dictionary(Of Integer, Dictionary(Of Integer, Integer))
objValueResult_1 = New Dictionary(Of Integer, Dictionary(Of Tuple(Of Integer, Integer), List(Of ValueError)))
objValueResult_2 = New Dictionary(Of Integer, Dictionary(Of Tuple(Of Integer, Integer), List(Of ValueError)))
objFormatResult = New Dictionary(Of Integer, Dictionary(Of Tuple(Of Integer, Integer), List(Of FormatError)))
'Loop through all pages
For w_intCtr_1 As Integer = 1 To intNoOfPages
If p_backgroundWorker.CancellationPending = True Then
e.Cancel = True
Return
End If
Dim w_intCurrentStep As Integer = 1
GetExcelData(p_objWorkSheet_1, objExcelData_1, objLocation_1, w_intCtr_1)
GetExcelData(p_objWorkSheet_2, objExcelData_2, objLocation_2, w_intCtr_1)
If objExcelData_1 Is Nothing OrElse objExcelData_2 Is Nothing Then
'No data to compare
Exit Sub
End If
objCompareByData = New Compare_Data
'Compare value of excelsheets
With objCompareByData
'Set threshold, excel data, and location of page to compare
.SetThreshold = dblThreshold
.CompareToBestMatchData = blnBestMatchFlg
.SetExcelData_1 = objExcelData_1(w_intCtr_1)
.SetLocation_1 = objLocation_1(w_intCtr_1)
.SetExcelData_2 = objExcelData_2(w_intCtr_1)
.SetLocation_2 = objLocation_2(w_intCtr_1)
If objRemoveCol Is Nothing = False AndAlso objRemoveCol.ContainsKey(w_intCtr_1) Then
.SetRemovedColumn = objRemoveCol(w_intCtr_1)
End If
If objRemoveRow Is Nothing = False AndAlso objRemoveRow.ContainsKey(w_intCtr_1) Then
.SetRemovedRow = objRemoveRow(w_intCtr_1)
End If
If objAddCol Is Nothing = False AndAlso objAddCol.ContainsKey(w_intCtr_1) Then
.SetAddedColumn = objAddCol(w_intCtr_1)
End If
If objAddRow Is Nothing = False AndAlso objAddRow.ContainsKey(w_intCtr_1) Then
.SetAddedRow = objAddRow(w_intCtr_1)
End If
If objChangeData Is Nothing = False AndAlso objChangeData.ContainsKey(w_intCtr_1) Then
.SetDataChange = objChangeData(w_intCtr_1)
End If
If p_backgroundWorker.CancellationPending = True Then
e.Cancel = True
Return
End If
'Proceed to compare
.Compare()
objEquivalentColumns.Add(w_intCtr_1, .EquivalentColumns)
objEquivalentRows.Add(w_intCtr_1, .EquivalentRows)
objValueResult_1.Add(w_intCtr_1, .ExcelData_Result_1)
objValueResult_2.Add(w_intCtr_1, .ExcelData_Result_2)
End With
If blnCompareMerge OrElse blnCompareTextWrap OrElse blnCompareTextAlign OrElse blnCompareOrientation OrElse blnCompareBorder OrElse blnCompareBackColor OrElse blnCompareFont Then
objCompareByFormat = New Compare_Format
'Compare format of excelsheets
With objCompareByFormat
'Set excel data to compare
.SetExcelFormat_1 = objExcelData_1(w_intCtr_1)
.SetExcelFormat_2 = objExcelData_2(w_intCtr_1)
'Set equivalent columns of page retrieved from comparing values of both excel sheets
'Set equivalent rows of page retrieved from comparing values of both excel sheets
If objEquivalentColumns Is Nothing = False AndAlso objEquivalentColumns.ContainsKey(w_intCtr_1) Then
.SetEquivalentColumns = objEquivalentColumns(w_intCtr_1)
End If
If objEquivalentRows Is Nothing = False AndAlso objEquivalentRows.ContainsKey(w_intCtr_1) Then
.SetEquivalentRows = objEquivalentRows(w_intCtr_1)
End If
.CompareMerge = blnCompareMerge
.CompareTextWrap = blnCompareTextWrap
.CompareTextAlign = blnCompareTextAlign
.CompareOrientation = blnCompareOrientation
.CompareBorder = blnCompareBorder
.CompareBackColor = blnCompareBackColor
.CompareFont = blnCompareFont
If p_backgroundWorker.CancellationPending = True Then
e.Cancel = True
Return
End If
.Compare()
'Set comparison result of page to collection
objFormatResult.Add(w_intCtr_1, .ExcelFormat_Result)
End With
End If
If p_backgroundWorker.CancellationPending = True Then
e.Cancel = True
Return
End If
'Set result to excel sheets
AddValueResultToWorkSheet(p_objWorkSheet_1, p_objWorkSheet_2, w_intCtr_1)
If p_backgroundWorker.CancellationPending = True Then
e.Cancel = True
Return
End If
'Set result to excel sheets
AddFormatResultToWorkSheet(p_objWorkSheet_2, w_intCtr_1)
p_backgroundWorker.ReportProgress((100 / intNoOfPages) * w_intCtr_1, (100 / intNoOfPages) * w_intCtr_1 & "% Completed " & w_intCtr_1 & " out of " & intNoOfPages & " pages")
Thread.Sleep(3000)
Next
End Sub
It works fine if i put Thread.Sleep(3000) after every ReportProgress. This is my ProgressChanged event handler
Private Sub worker_ProgressChanged(sender As Object, e As ProgressChangedEventArgs)
m_ProgressBar.pbStatusMain.Dispatcher.BeginInvoke(Sub()
m_ProgressBar.pbStatusMain.IsIndeterminate = False
m_ProgressBar.pbStatusMain.Value = e.ProgressPercentage
End Sub)
m_ProgressBar.txtBlockMainProgress.Dispatcher.BeginInvoke(Sub()
m_ProgressBar.txtBlockMainProgress.Text = e.UserState
End Sub)
TaskbarItemInfo.ProgressValue = e.ProgressPercentage / 100
End Sub
I wonder why sometimes it work and sometimes it doesnt. Based on my research the UI thread is being flooded with message causing it to be unresponsive or I used ReportProgress to frequent which causing the UI thread to ignore the next request. What am i doing wrong? Why the changes doesnt apply in my UI?
