CODEWITHSUNDEEP
vb.netprogress-barbackgroundworker
CaffeinatedMike
CaffeinatedMike

Reputation: 1607

How would I add a progress bar to display on form button click that shows the progress of form2 load

I have a Start form with a "View Records" button. On the Records Form Load event I have the loop that populates a datagridview on the Records Form. What I want to do is show a progress bar next to the "View Records" button on the Start form that shows the progress of the datagridview on the Records form when the user clicks the "View Records" button. Then, once the datagridview loop is successfully completed I want to bring up the Records form (but still leave Start form open as parent form, so the "View Record" form would be brought up by a ShowDialog). I have the simple code to show the progress bar on "View Records" button click. I'm reading around to find that maybe a background worker might be what I need, but I do not know how to work with it. Could someone help walk me through it and provide some code to help me along? Some info, the start form is called 'Start' and the View Records form is called 'Records.' The progress bar is name 'pb'. Thank you in advance to anyone who attempts to help!

Ok, here's the updated code with the error

Imports Office = Microsoft.Office.Core
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Start
Dim Records As New Records
Dim excel_app As Excel.Application
Dim workbook As Excel.Workbook
Dim sheet_name As String
Dim sheet As Excel.Worksheet
Dim exeDir As New IO.FileInfo(Reflection.Assembly.GetExecutingAssembly.FullName)
Dim xlPath = IO.Path.Combine(exeDir.DirectoryName, "Records.xlsx")

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    bw.RunWorkerAsync()

End Sub

Private Sub bw_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles bw.DoWork



    ' Get the Excel application object.
    excel_app = New Excel.Application

    ' Make Excel visible (optional).
    excel_app.Visible = False

    ' Open the workbook.
    workbook = excel_app.Workbooks.Open(xlPath)
    sheet_name = "2013"

    sheet = excel_app.Worksheets("2013")

    Dim ColumnCount, RowCount, TotalCellCount As Long
    ColumnCount = sheet.Range("A1").CurrentRegion.Columns.Count
    RowCount = sheet.Range("A1").CurrentRegion.Rows.Count

    Records.DataGridView1.ColumnCount = ColumnCount - 1
    Records.DataGridView1.RowCount = RowCount - 1
    Records.DataGridView1.ColumnHeadersVisible = True
    Records.DataGridView1.RowHeadersVisible = True
    TotalCellCount = Records.DataGridView1.ColumnCount * Records.DataGridView1.RowCount

    pb.Visible = True
    pb.Minimum = 0
    pb.Value = 0
    pb.Maximum = TotalCellCount

    Records.DataGridView1.RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.DisableResizing
    Records.DataGridView1.AllowUserToResizeColumns = False
    Records.DataGridView1.AllowUserToResizeRows = False
    Records.DataGridView1.ReadOnly = True


    Records.DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells

    'Loop through each column
    Dim cIndex As Integer = 0
    While cIndex < ColumnCount

        'Loop through and populate each row in column
        Dim rIndex As Integer = 0
        While rIndex < RowCount - 1
            If cIndex = 0 Then
                'Set row header titles
                Records.DataGridView1.Rows.Item(rIndex).HeaderCell.Value = sheet.Range("A1").Offset(rIndex + 1, cIndex).Value()

                Records.DataGridView1.Rows(rIndex).Cells(cIndex).Value = sheet.Range("A1").Offset(rIndex + 1, cIndex + 1).Value()
            End If
            If cIndex > 0 Then
                Records.DataGridView1.Rows(rIndex).Cells(cIndex).Value = sheet.Range("A1").Offset(rIndex + 1, cIndex + 1).Value()
            End If

            'Set column header title
            Records.DataGridView1.Columns(cIndex).HeaderText = sheet.Range("A1").Offset(0, cIndex + 1).Value

            'Change last cell (Result) color Red or Green to represent positive gain or negative loss
            If rIndex = RowCount - 2 Then
                If Records.DataGridView1.Rows(rIndex).Cells(cIndex).Value < 0 Then
                    Records.DataGridView1.Item(cIndex, rIndex).Style.BackColor = Color.Red
                    Records.DataGridView1.Item(cIndex, rIndex).Style.ForeColor = Color.White
                End If
                If Records.DataGridView1.Rows(rIndex).Cells(cIndex).Value > 0 Then
                    Records.DataGridView1.Item(cIndex, rIndex).Style.BackColor = Color.Green
                    Records.DataGridView1.Item(cIndex, rIndex).Style.ForeColor = Color.White
                End If
                If Records.DataGridView1.Rows(rIndex).Cells(cIndex).Value = 0 Then
                    Records.DataGridView1.Rows(rIndex).Cells(cIndex).Value = "Broke Even"
                End If

            End If

            'Update the progress bar after each cell is populated
            bw.ReportProgress((rIndex * cIndex) / TotalCellCount)

            rIndex = rIndex + 1

        End While

        'Format all cells in column as currency values
        Records.DataGridView1.Columns(cIndex).DefaultCellStyle.Format = "c"
        'Make column unsortable
        Records.DataGridView1.Columns(cIndex).SortMode = DataGridViewColumnSortMode.NotSortable
        'Resize all Row Headers so user can see Row Titles without resizing
        Records.DataGridView1.AutoResizeRowHeadersWidth(DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders)

        cIndex = cIndex + 1
    End While

    Records.DataGridView1.AutoResizeColumns()

End Sub

Private Sub bw_ProgressChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ProgressChangedEventArgs) Handles bw.ProgressChanged
    pb.Value = e.ProgressPercentage
End Sub

Private Sub bw_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bw.RunWorkerCompleted
    If e.Error IsNot Nothing Then
        MessageBox.Show(e.Error.Message, "Background Worker Exception", MessageBoxButtons.OK, MessageBoxIcon.Error)
    Else
        If e.Cancelled Then
            'worker was cancelled
        Else
            'worker completed, open form2 here
            pb.Visible = False
            Records.ShowDialog()
            If (Records.DialogResult) Then
                ' Close the workbook.
                workbook.Close()

                ' Close the Excel server.
                excel_app.Quit()
            End If

        End If
    End If
End Sub

Am I doing this right? And how do I fixed the error?

Upvotes: 1

Views: 1654

Answers (1)

Jeff
Jeff

Reputation: 918

I would use a background worker on Form1 and then subscribe to its ProgressChanged event to update the progress bar. Then when the worker completes, you can pass that data to Form2 and open it.

Assuming you add a BackgroundWorker called bw to Form1

On the Button click event you need to start the worker by calling bw.RunWorkerAsync

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
  Setup()
  bw.RunWorkerAsync()

End Sub

Private Sub Setup()
' Get the Excel application object.
excel_app = New Excel.Application

' Make Excel visible (optional).
excel_app.Visible = False

' Open the workbook.
workbook = excel_app.Workbooks.Open(xlPath)
sheet_name = "2013"

sheet = excel_app.Worksheets("2013")

Dim ColumnCount, RowCount, TotalCellCount As Long
ColumnCount = sheet.Range("A1").CurrentRegion.Columns.Count
RowCount = sheet.Range("A1").CurrentRegion.Rows.Count

Records.DataGridView1.ColumnCount = ColumnCount - 1
Records.DataGridView1.RowCount = RowCount - 1
Records.DataGridView1.ColumnHeadersVisible = True
Records.DataGridView1.RowHeadersVisible = True
TotalCellCount = Records.DataGridView1.ColumnCount * Records.DataGridView1.RowCount

pb.Visible = True
pb.Minimum = 0
pb.Value = 0
pb.Maximum = TotalCellCount

Records.DataGridView1.RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.DisableResizing
Records.DataGridView1.AllowUserToResizeColumns = False
Records.DataGridView1.AllowUserToResizeRows = False
Records.DataGridView1.ReadOnly = True


Records.DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells

End Sub

Then in the bw.DoWork event you would run the code to fetch the data. Create a form level variable so you can access it in the DoWork and RunWorkerCompleted Events

Dim f as New RecordForm

Private Sub bw_DoWork(sender As System.Object, e As System.ComponentModel.DoWorkEventArgs) Handles bw.DoWork


'Loop through each column
Dim cIndex As Integer = 0
While cIndex < ColumnCount

    'Loop through and populate each row in column
    Dim rIndex As Integer = 0
    While rIndex < RowCount - 1
        If cIndex = 0 Then
            'Set row header titles
            Records.DataGridView1.Rows.Item(rIndex).HeaderCell.Value = sheet.Range("A1").Offset(rIndex + 1, cIndex).Value()

            Records.DataGridView1.Rows(rIndex).Cells(cIndex).Value = sheet.Range("A1").Offset(rIndex + 1, cIndex + 1).Value()
        End If
        If cIndex > 0 Then
            Records.DataGridView1.Rows(rIndex).Cells(cIndex).Value = sheet.Range("A1").Offset(rIndex + 1, cIndex + 1).Value()
        End If

        'Set column header title
        Records.DataGridView1.Columns(cIndex).HeaderText = sheet.Range("A1").Offset(0, cIndex + 1).Value

        'Change last cell (Result) color Red or Green to represent positive gain or negative loss
        If rIndex = RowCount - 2 Then
            If Records.DataGridView1.Rows(rIndex).Cells(cIndex).Value < 0 Then
                Records.DataGridView1.Item(cIndex, rIndex).Style.BackColor = Color.Red
                Records.DataGridView1.Item(cIndex, rIndex).Style.ForeColor = Color.White
            End If
            If Records.DataGridView1.Rows(rIndex).Cells(cIndex).Value > 0 Then
                Records.DataGridView1.Item(cIndex, rIndex).Style.BackColor = Color.Green
                Records.DataGridView1.Item(cIndex, rIndex).Style.ForeColor = Color.White
            End If
            If Records.DataGridView1.Rows(rIndex).Cells(cIndex).Value = 0 Then
                Records.DataGridView1.Rows(rIndex).Cells(cIndex).Value = "Broke Even"
            End If

        End If

        'Update the progress bar after each cell is populated
        bw.ReportProgress((rIndex * cIndex) / TotalCellCount)

        rIndex = rIndex + 1

    End While

    'Format all cells in column as currency values
    Records.DataGridView1.Columns(cIndex).DefaultCellStyle.Format = "c"
    'Make column unsortable
    Records.DataGridView1.Columns(cIndex).SortMode = DataGridViewColumnSortMode.NotSortable
    'Resize all Row Headers so user can see Row Titles without resizing
    Records.DataGridView1.AutoResizeRowHeadersWidth(DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders)

    cIndex = cIndex + 1
End While

Records.DataGridView1.AutoResizeColumns()
End Sub

Private Sub bw_ProgressChanged(sender As Object, e As System.ComponentModel.ProgressChangedEventArgs) Handles bw.ProgressChanged
  ProgressBar.Value = e.ProgressPercentage
End Sub

When the worker is finished it will fire its completed event. 

Private Sub bw_RunWorkerCompleted(sender As Object, e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bw.RunWorkerCompleted
  If e.Error IsNot Nothing Then
    MessageBox.Show(e.Error.Message, "Background Worker Exception", MessageBoxButtons.OK, MessageBoxIcon.Error)
  Else
    If e.Cancelled Then
      'worker was cancelled
    Else
      'worker finished. open Form2
      f.Show
    End If
  End If
End Sub

Upvotes: 1

Related Questions