none
Export to excel and progress bar RRS feed

  • Question

  • Hi all

    In a winform I export a DGV to an excel workbook. using  MyExcel As Excel.Application

    In the DGV I use a progress bar.

    I use this code to set progressbar properies 

     ExcelProgressBar.Minimum = 1
            ExcelProgressBar.Maximum = DGV.Rows.Count
            ExcelProgressBar.Value = 1
            ExcelProgressBar.Style = ProgressBarStyle.Continuous
            ExcelProgressBar.Step = 1
            ExcelProgressBar.Visible = True

    Then I loop thru DGV rows to fill Excel row and for each DGV row I Perfom a step(+1)  for the progressbar.

    At the end of DGV a set  MyExcel.Visible = True

    The strange thing is that the Excel program is displayed on the screen before the progress bar reachs grahically its maximun (even if it reachs the maximun value).

    How to avoid this behavior ? 

    Sunday, December 30, 2018 6:24 PM

Answers

  • Hi

    Happy New Year too - may all your applications be bug free!.

    *

    I have reported these issues to Developer Community to see if it invokes any response. See HERE


    Regards Les, Livingston, Scotland

    • Marked as answer by Claudio111 Wednesday, January 2, 2019 9:43 PM
    Monday, December 31, 2018 6:59 PM

All replies

  • Hi

    What exactly does 'I Perfom a step(+1) ' mean? If you are actually increasing the ProgressBar.Step then that would give strange behaviour. Maybe that isn't what you meant, in which case, consign this reply to the trash can in the sky :)


    Regards Les, Livingston, Scotland


    • Edited by leshay Sunday, December 30, 2018 6:54 PM
    Sunday, December 30, 2018 6:48 PM
  • Hi leshay

    This is my code

    private sub ExportDGVtoExcel

    -----


    ExRowindex = 2

    DGVRowIndex = 0

    For Each dr In DGV.Rows ExcelProgressBar.PerformStep() ExportNormalRow() ExrowIndex += 1 DGVrowindex += 1 Next MessageBox.Show("Esportazione in Excel conclusa !") myExcel.visible = true

    end sub

    Private Sub ExportNormalRow() ' scrive una riga normale della DGV su Excel ExcolIndex = 1 DGVcolindex = 0 For Each dc In DGV.Columns ' controlla se la colonna e' visibile If dc.Visible = False Then DGVcolindex += 1 Continue For End If Val = DGV.Rows(DGVrowindex).Cells(DGVcolindex).Value ' scrivi il valore della cella DGV nella cella Excel If TypeOf dc Is DataGridViewTextBoxColumn Then Val = Val.ToString ElseIf TypeOf dc Is DataGridViewCheckBoxColumn Then If CBool(Val) = True Then Val = "Si" Else Val = "No" End If End If MySheet.Cells(ExrowIndex, ExcolIndex) = Val ExcolIndex += 1 DGVcolindex += 1 Next End Sub

     

    So what can I do to have progressbar finish its graphics beforeExcel is opened ?

    Sunday, December 30, 2018 7:42 PM
  • Hi

    After many trials, this was the only way I could stop the effect you are seeing.

    I placed a Threading.Thread.Sleep(1) after the call to ExcelProgressBar.PerformStep. This did show the 'stepping' and was close to completed, but, slowed everything down dramatically, so may not be what you want.

    All other trials I tested, using your code and some of my own completely different code, all produced the effect. I would imagine there is some optimizing going on, but can't be sure. Or, the ProgressBar is queuing the steps and just can't keep up with the row manipulation.


    Regards Les, Livingston, Scotland


    • Edited by leshay Sunday, December 30, 2018 9:05 PM
    Sunday, December 30, 2018 8:56 PM
  • Are you saying you get the same behavior I get. ?
    Sunday, December 30, 2018 10:23 PM
  • Hi

    Yes, I saw the same effect. I tried with many different code methods and from small to large number of rows in a DGV. When using code based on the code you posted, I used DGV with rows up to 10000 to test and consistantly had the MessageBox open before the ProgressBar was close to completed.

    As I don't have Excel, I am only testing the code excluding the write to the Excel Dheet.

    Here is the code for the last version I tried:

    Option Strict On
    Option Explicit On
    Public Class Form1
      Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
        With DGV
          For i As Integer = 1 To 1500
    .Rows.Add(i, i + 2, {"True", "False"}(rand.Next(2)), i / 3)
          Next
        End With
        ProgressBar.Minimum = 0
        ProgressBar.Maximum = DGV.Rows.Count
        ProgressBar.Value = 0
        ProgressBar.Style = ProgressBarStyle.Continuous
        ProgressBar.Step = 1
        ProgressBar.Visible = True
      End Sub
      Private Sub ExportDGVtoExcel()
        ExportRows()
        MessageBox.Show(ProgressBar.Value.ToString)
      End Sub
      Private Sub ExportRows()
        For Each r As DataGridViewRow In DGV.Rows
          If r.Index = DGV.NewRowIndex Then Exit For
          For Each c As DataGridViewColumn In DGV.Columns
            If c.Visible = False Then
              Continue For
            End If
            Dim Val As Object = DGV(c.Index, r.Index).Value
            If TypeOf c Is DataGridViewTextBoxColumn Then
              Val = Val.ToString
            Else
              If TypeOf c Is DataGridViewCheckBoxColumn Then
                If CBool(Val) = True Then
                  Val = "Si"
                Else
                  Val = "No"
                End If
              End If
            End If
          Next
          ProgressBar.Value = r.Index + 1
          '  Threading.Thread.Sleep(1)
        Next
      End Sub
      Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        ExportDGVtoExcel()
        ProgressBar.Value = 1
      End Sub
    End Class


    Regards Les, Livingston, Scotland






    • Edited by leshay Sunday, December 30, 2018 11:00 PM
    Sunday, December 30, 2018 10:40 PM
  • using 

    MessageBox.Show("Esportazione in Excel conclusa !")
        myExcel.visible = true

    i.e. the MessageBox.Show before myExcel.visible = true , then I get the progress bar completed even if excel is show before the bar is full. What I wish is to have the progressbar completed and then show excel.

    Now I'm trying to understand the use of  BackGroudWorker Class to solve the problem.

    Sunday, December 30, 2018 11:37 PM
  • using 

    MessageBox.Show("Esportazione in Excel conclusa !")
        myExcel.visible = true

    i.e. the MessageBox.Show before myExcel.visible = true , then I get the progress bar completed even if excel is show before the bar is full. What I wish is to have the progressbar completed and then show excel.

    Now I'm trying to understand the use of  BackGroudWorker Class to solve the problem.

    Hi

    Yes, thast is exactly what I was seeing too.

    *

    Here is a small application which uses a BackGroundWorker and a ProgressBar updated from within the BGW thread. It may be of some help.

    ' Mod, BGW, Primes, Prime
    ' Form1 with BackGroundWorker1, ListBox1,
    ' Label1, Button1, ProgressBar1
    Option Strict On
    Option Explicit On
    Imports System.ComponentModel
    Public Class Form1
      Dim results As New List(Of String)
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    	SetUp()
      End Sub
      Private Sub SetUp()
    	ListBox1.Items.Clear()
    	results.Clear()
    
    	' add more or edit numbers to check if needed
    	ListBox1.Items.AddRange({512927377,
    15485867,
    941083919,
    373587887,
    961748941,
    512927381,
    899809363,
    797003413,
    961748953,
    373587883,
    654188429,
    141650939,
    353868013,
    533000401,
    941083987,
    160481227})
      End Sub
      Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    	ProgressBar1.Maximum = ListBox1.Items.Count
    	Select Case Button1.Text
    	  Case "Find Out"
    		If Not BackgroundWorker1.IsBusy Then
    		  SetUp()
    		  BackgroundWorker1.RunWorkerAsync()
    		  Button1.Text = "CANCEL"
    		End If
    	  Case Else
    		If BackgroundWorker1.IsBusy Then
    		  BackgroundWorker1.CancelAsync()
    		  Button1.Text = "Find Out"
    		End If
    	End Select
      End Sub
      Function GetInteger(s As String) As Integer
    	Dim v As Integer = 0
    	If Integer.TryParse(s, v) Then Return v
    	Return 0
      End Function
      Function GetPrime(s As String) As String
    	Dim v As Integer = GetInteger(s)
    	For i As Integer = 2 To v - 1
    	  If v Mod i = 0 Then
    		Return s & "   Not Prime, divisible by " & i.ToString
    	  End If
    	Next
    	Return s & "   Prime"
      End Function
      Private Sub BackgroundWorker1_DoWork(sender As Object, e As DoWorkEventArgs) Handles BackgroundWorker1.DoWork
    	With ListBox1
    	  For i As Integer = 0 To .Items.Count - 1
    		If BackgroundWorker1.CancellationPending Then
    		  e.Cancel = True
    		  Exit For
    		End If
    		BackgroundWorker1.ReportProgress(i + 1)
    		results.Add(GetPrime(.Items(i).ToString))
    	  Next
    	End With
      End Sub
      Private Sub BackgroundWorker1_RunWorkerCompleted(sender As Object, e As RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted
    	For i As Integer = 0 To results.Count - 1
    	  ListBox1.Items(i) = results(i)
    	Next
      End Sub
      Private Sub BackgroundWorker1_ProgressChanged(sender As Object, e As ProgressChangedEventArgs) Handles BackgroundWorker1.ProgressChanged
    	ProgressBar1.Value = e.ProgressPercentage
      End Sub
    End Class
    


    Regards Les, Livingston, Scotland

    Monday, December 31, 2018 12:00 AM
  • Hi,

    try the code:

        Public Sub ExportExcel(ByVal fileName As String, ByVal myDGV As DataGridView, ByVal ProgressBar1 As ProgressBar, ByVal Label3 As System.Windows.Forms.Label)
            Dim saveFileName As String = ""
            Dim saveDialog As SaveFileDialog = New SaveFileDialog()
            saveDialog.DefaultExt = "xlsx"
            saveDialog.Filter = "Excel文件|*.xlsx"
            saveDialog.FileName = fileName
            saveDialog.ShowDialog()
            saveFileName = saveDialog.FileName
            If saveFileName.IndexOf(":") < 0 Then Return
            Dim xlApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()
    
            If xlApp Is Nothing Then
                MessageBox.Show("Unable to create Excel object, maybe your machine does not have Excel installed")
                Return
            End If
    
            Dim workbooks As Microsoft.Office.Interop.Excel.Workbooks = xlApp.Workbooks
            Dim workbook As Microsoft.Office.Interop.Excel.Workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet)
            Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = CType(workbook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
            ProgressBar1.Maximum = myDGV.RowCount + 1
            ProgressBar1.Value = 1
            ProgressBar1.Visible = True
            Label3.Visible = True
    
            For i As Integer = 0 To myDGV.ColumnCount - 1
                worksheet.Cells(1, i + 1) = myDGV.Columns(i).HeaderText
            Next
    
            For r As Integer = 0 To myDGV.Rows.Count - 1
                ProgressBar1.Value = ProgressBar1.Value + 1
                Dim d As Double = ProgressBar1.Value / CDbl(ProgressBar1.Maximum)
                System.Math.Round(d, 2)
                Label3.Text = d.ToString("##.##%")
    
                For i As Integer = 0 To myDGV.ColumnCount - 1
                    worksheet.Cells(r + 2, i + 1) = myDGV.Rows(r).Cells(i).Value
                Next
    
                System.Windows.Forms.Application.DoEvents()
            Next
    
            worksheet.Columns.EntireColumn.AutoFit()
    
            If saveFileName <> "" Then
    
                Try
                    workbook.Saved = True
                    workbook.SaveCopyAs(saveFileName)
                Catch ex As Exception
                    MessageBox.Show("There was an error exporting the file and the file may be being opened!" & vbLf & ex.Message)
                End Try
            End If
    
            xlApp.Quit()
            GC.Collect()
            MessageBox.Show("Export success!", "prompt message", MessageBoxButtons.OK)
            ProgressBar1.Visible = False
            Label3.Visible = False
        End Sub

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 31, 2018 6:05 AM
  • Hi Alex

    with your code I get the same relsult. 

    The Excel is shown before the progressbar reach the end. I must say that I work on very few rows in DGV.

    Monday, December 31, 2018 9:29 AM
  • Hi Alex

    with your code I get the same relsult. 

    The Excel is shown before the progressbar reach the end. I must say that I work on very few rows in DGV.

    Claudio,

    " the Excel program is displayed on the screen before the progress bar reachs grahically its maximum"

    Its not exactly clear what you see and or expect to happen. You have not show all the code or a picture of what it looks like so we are still guessing a solution.

    However it seems to me your progress bar code is in a form that calls excel and performs then exits that function and back to the progress bar and form.

    So it seems that what you describe is what should happen? You should see the excel complete and then the progress bar finish?

    I mean what do you expect? Progress bar stops and shows 100 percent and then the excel fills and goes away? Is that what you want to happen?

    Did you try moving ExcelProgressBar.PerformStep() to after ExportNormalRow ? Or play with that? Add a .refresh to something?

    Some wont like it but as a last resort its possible placing application.doevents at the right spot will get the screen to update when you want. ie after performstep but before exportnormalrow. I am not sure where, experiment. Then you can either leave it or you will have a better idea exactly where it happens and proceed to rearrange things and put in some .refreshs maybe it will go how you want.

    :)

    Monday, December 31, 2018 11:01 AM
  • Some wont like it but as a last resort its possible placing application.doevents at the right spot will get the screen to update when you want. ie after performstep but before exportnormalrow. I am not sure where, experiment.

    Hi Tommy

    I already experimented with the controversial DoEvents and it didn't change the effect that Claudio is having. For whatever reason, the ProgressBar was still incrementing well after the when the MessageBox was shown.

    EDIT: there seems to be quite a lot of similar complaints seeking answers (Google ProgressBar sync). So it may not be a unique issue.


    Regards Les, Livingston, Scotland


    • Edited by leshay Monday, December 31, 2018 1:25 PM
    Monday, December 31, 2018 1:14 PM
  • Hi Tommy

    I already experimented with the controversial DoEvents and it didn't change the effect that Claudio is having. For whatever reason, the ProgressBar was still incrementing well after the when the MessageBox was shown.

    EDIT: there seems to be quite a lot of similar complaints seeking answers (Google ProgressBar sync). So it may not be a unique issue.


    Regards Les, Livingston, Scotland


    Hi Les,

    When I  try to use the examples the first problem is it wont accept the name "progressbar"?

    Then I just get more problems and of course I dont have the excel part etc.

    Without a reproducable example and understanding the exact problem perhaps a picture its hard to say much?

    :)


    Monday, December 31, 2018 2:01 PM
  • Hi Tommy

    I already experimented with the controversial DoEvents and it didn't change the effect that Claudio is having. For whatever reason, the ProgressBar was still incrementing well after the when the MessageBox was shown.

    EDIT: there seems to be quite a lot of similar complaints seeking answers (Google ProgressBar sync). So it may not be a unique issue.


    Regards Les, Livingston, Scotland


    Hi Les,

    When I  try to use the examples the first problem is it wont accept the name "progressbar"?

    Then I just get more problems and of course I dont have the excel part etc.

    Without a reproducable example and understanding the exact problem perhaps a picture its hard to say much?

    :)


    Hi Tommy

    Must be something wrong with your VB installation as all the code posted referencing ProgressBar1 do work OK (apart from the issue being discussed).

    Here again is an example. This example does work OK here and serves only to show the issue clearly.

    All it does is to fill a ListBox, and then in the BGW, step through the list and displays the ListBox item value and increments the PB (using either/or .PerformStep or BGW reportProgress).

    The code as posted fils the ListBox with 30000 items and when run, the Text displayed and the PB are close tofinishing together. BIT, reducing the ListBox item count in steps of 3000 will show more and more lag between the Text and the PB.

    ' Form1 with ListBox1, Label1
    ' Button1 and BackGroundWorker1
    Option Strict On
    Option Explicit On
    Public Class Form1
      Dim lst As New List(Of String)
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' run as is first time then
        ' reduce c to see PB lagging
        ' (reduce in steps of 3000)
        ' the PB lag will become more
        ' and more apparent
        Dim c As Integer = 30000
        With lst
          For i As Integer = 0 To c
            .Add(i.ToString)
          Next
        End With
        ListBox1.DataSource = lst
      End Sub
      Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        If BackgroundWorker1.IsBusy Then Exit Sub
        With ProgressBar1
          .Step = 1
          .Value = 0
          .Maximum = lst.Count
        End With
        BackgroundWorker1.RunWorkerAsync()
      End Sub
      Private Sub BackgroundWorker1_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
        Dim count As Integer = 0
        For Each s As String In lst
          count += 1
    
          ' -------------------------
          ' Both these show same PB lag
          Invoke(Sub() ProgressBar1.PerformStep())
          '   BackgroundWorker1.ReportProgress(count)
          ' -------------------------
    
          '  Threading.Thread.Sleep(2000)
          Invoke(Sub() Label1.Text = s)
        Next
      End Sub
      Private Sub BackgroundWorker1_ProgressChanged(sender As Object, e As System.ComponentModel.ProgressChangedEventArgs) Handles BackgroundWorker1.ProgressChanged
        ProgressBar1.Value = e.ProgressPercentage
      End Sub
    End Class


    Regards Les, Livingston, Scotland


    • Edited by leshay Monday, December 31, 2018 3:25 PM
    Monday, December 31, 2018 3:25 PM

  • Hi Tommy

    Must be something wrong with your VB installation as all the code posted referencing ProgressBar1 do work OK (apart from the issue being discussed).

    My vb installation is fine.

    The examples codes say  progressbar not progressbar1.

    Never mind. 

    Monday, December 31, 2018 4:39 PM
  • Hi Tommy

    I remember removing the 'Excel' prefix from Claudio posted code so maybe that was it.


    Regards Les, Livingston, Scotland

    Monday, December 31, 2018 4:48 PM
  • Hi Tommy

    This is my Code.

    In the Form I have the DGV and ProgressBar

    In the form I create an istance of ClasseExportDGVToExcel that creates the Excel Application

    Then with a button click in the form i call ExportToExcel method.

    Imports System.Windows.Forms
    Imports Microsoft.Office.Interop
    
    
    Public Class ClasseExportDGVToExcel
        Private MyExcel As Excel.Application
        Private MyBook As Excel.Workbook
        Private MySheet As Excel.Worksheet
    
        Private DGV As DataGridView
        Private FlagRaggruppa As Boolean
    
        Private dc As DataGridViewColumn
        Private dr As DataGridViewRow
        Private ExrowIndex As Integer
        Private ExcolIndex As Integer
        Private DGVrowindex As Integer
        Private DGVcolindex As Integer
        Private DGVNumOfColumn As Integer
        Private Val As String
    
        Private ExcelProgressBar As ProgressBar
        Public FlagExcelInstalled As Boolean = True
        Private ListColumnToSum As List(Of Integer)
    
        Public Sub New()
            MyExcel = New Excel.Application
            If MyExcel Is Nothing Then
                FlagExcelInstalled = False
            End If
        End Sub
    
        Public Sub ExportToExcel(ByRef pDGV As DataGridView,
                                 ByVal pPb As ProgressBar)
    
            DGV = pDGV
            ExcelProgressBar = pPb
            DGVNumOfColumn = DGV.Columns.Count
    
            'set ProgressBAR
            ExcelProgressBar.Minimum = 1
            ExcelProgressBar.Maximum = DGV.Rows.Count
            ExcelProgressBar.Value = 1
            ExcelProgressBar.Style = ProgressBarStyle.Continuous
            ExcelProgressBar.Step = 1
            ExcelProgressBar.Visible = True
    
            ' AGGIUNGI FILE EXCEL E FOGLIO
            MyBook = MyExcel.Workbooks.Add()
            MySheet = MyBook.ActiveSheet()
    
            ' Export the Columns Header
            ExcolIndex = 1
            For Each dc In DGV.Columns
                ' controlla se la colonna e' visibile
                If dc.Visible = True Then
                    MySheet.Cells(1, ExcolIndex) = dc.HeaderText
                    ExcolIndex += 1
                Else
                    Continue For
                End If
            Next
            ExrowIndex = 2
            DGVrowindex = 0
            For Each dr In DGV.Rows
                ExcelProgressBar.PerformStep()
    
                ExportNormalRow()
    
                ExrowIndex += 1
                DGVrowindex += 1
            Next
    
            '  MessageBox.Show("Esportazione in Excel conclusa !")
            ShowExcel()
            'release the objects
            ReleaseObject(MySheet)
            'MyBook.Close(False)
            ReleaseObject(MyBook)
            GC.Collect()
    
        End Sub
    
        Private Sub ShowExcel()
            MySheet.Columns.AutoFit()
    
            ExcelProgressBar.Visible = False
    
            MyExcel.Visible = True
            'Return control of Excel to the user.
            ' MyExcel.UserControl = True
        End Sub
        Private Sub ExportNormalRow()
            ' scrive una riga normale della DGV su Excel
            ExcolIndex = 1
            DGVcolindex = 0
            For Each dc In DGV.Columns
                ' controlla se la colonna e' visibile
                If dc.Visible = False Then
                    DGVcolindex += 1
                    Continue For
                End If
    
                Val = DGV.Rows(DGVrowindex).Cells(DGVcolindex).Value
                ' scrivi il valore della cella DGV nella cella Excel
                If TypeOf dc Is DataGridViewTextBoxColumn Then
                    Val = Val.ToString
                ElseIf TypeOf dc Is DataGridViewCheckBoxColumn Then
                    If CBool(Val) = True Then
                        Val = "Si"
                    Else
                        Val = "No"
                    End If
                End If
    
                ' controlla se la colonna e' visibile
    
                MySheet.Cells(ExrowIndex, ExcolIndex) = Val
                ExcolIndex += 1
                DGVcolindex += 1
            Next
        End Sub
    
        Private Sub ReleaseObject(ByVal pobj As Object)
            Try
                While (System.Runtime.InteropServices.Marshal.ReleaseComObject(pobj) > 0)
                End While
            Catch
            Finally
                pobj = Nothing
            End Try
        End Sub
    
        Public Sub ReleaseExcel()
            MyExcel.Quit()
            ReleaseObject(MyExcel)
            'some time Office application does Not quit after automation: so i am calling GC.Collect method.
            GC.Collect()
        End Sub
    End Class

    The result is that the Excel sheet is shown before the progressbar fill the bar.

    What I wish is to have the progressbar to be completed and only after that show the excel sheet.

    Monday, December 31, 2018 4:57 PM
  • Hi Tommy

    I remember removing the 'Excel' prefix from Claudio posted code so maybe that was it.


    Regards Les, Livingston, Scotland

    Les,

    Ok. I was just mentioning it. I forget now exactly.

    I dont understand the problem I guess I am just going to stay out.

    :)

    Monday, December 31, 2018 4:58 PM
  • Hi

    All my testing seems to show that the ProgressBar.Value is being updated correctly, only the graphical display of the Progress is lagging behind and only catches up once the incrementing stops.

    If there is real work being done (as per the Prime calculator I posted above) in the BGW, it seems the issue disappears (I suppose the PB has time toupdate the graphics in such a case)

    See this latest test:

    Option Strict On
    Option Explicit On
    ' Form1 with ListBox1, Label1
    ' Button1 and BackGroundWorker1
    Imports System.ComponentModel
    
    Public Class Form1
      Dim lst As New List(Of String)
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' the larger c is the smaller the
        ' proportion of 'remaining' PB gap
        ' there is. Somewhere above 100000 
        ' is close.
        Dim c As Integer = 1000
        With lst
          For i As Integer = 0 To c
            .Add(i.ToString)
          Next
        End With
        ListBox1.DataSource = lst
      End Sub
      Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        If BackgroundWorker1.IsBusy Then Exit Sub
        With ProgressBar1
          .Step = 1
          .Value = 0
          .Maximum = lst.Count
        End With
        BackgroundWorker1.RunWorkerAsync()
      End Sub
      Private Sub BackgroundWorker1_DoWork(sender As Object, e As DoWorkEventArgs) Handles BackgroundWorker1.DoWork
        Dim count As Integer = 0
        For Each s As String In lst
          count += 1
    
          ' -------------------------
          ' Both these show same PB lag
          Invoke(Sub() ProgressBar1.PerformStep())
          '   BackgroundWorker1.ReportProgress(count)
          ' -------------------------
    
          '  Threading.Thread.Sleep(2000)
          Invoke(Sub() Label1.Text = s)
        Next
      End Sub
      Private Sub BackgroundWorker1_ProgressChanged(sender As Object, e As ProgressChangedEventArgs) Handles BackgroundWorker1.ProgressChanged
        ProgressBar1.Value = e.ProgressPercentage
      End Sub
    
      Private Sub BackgroundWorker1_RunWorkerCompleted(sender As Object, e As RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted
        Do
          ' force error, STOP execution
          ' but do not quit application
          ProgressBar1.Value += 1
        Loop Until ProgressBar1.Value = ProgressBar1.Maximum
    
        ' here, the PB value HAS reached 
        ' the maximum value, BUT the graphics
        ' are lagging (as can be seen at the
        ' point of the exception) where the
        ' PB display is only a little way
        ' into the process.
        MessageBox.Show("Done!")
    
      End Sub
    End Class


    Regards Les, Livingston, Scotland



    • Edited by leshay Monday, December 31, 2018 5:46 PM
    Monday, December 31, 2018 5:23 PM
  • Hi Tommy and Ieshay

    whatever we do, the Progressbar is too slow in respect to other jobs. (even to a Label.text that shows a Progressbar.value

    I used Application.Doevent or Progressbar.Update or Progressbar.Refresh after Performstep, and also the Backgroundwork control

    The results are the always the same...too slow.

    The only thing to do is to send a message to user saying the excel sheet  is complete so, the progressbar can finish its graphics and then show excel sheet.

    This is my conclusion after a day of work ! :-(

    Do you agree ?

    Monday, December 31, 2018 5:34 PM
  • Anyway

    Happy New Year to ALL

    Monday, December 31, 2018 6:23 PM
  • Hi Tommy and Ieshay

    whatever we do, the Progressbar is too slow in respect to other jobs. (even to a Label.text that shows a Progressbar.value

    I used Application.Doevent or Progressbar.Update or Progressbar.Refresh after Performstep, and also the Backgroundwork control

    The results are the always the same...too slow.

    The only thing to do is to send a message to user saying the excel sheet  is complete so, the progressbar can finish its graphics and then show excel sheet.

    This is my conclusion after a day of work ! :-(

    Do you agree ?

    Hi

     A couple of things. This strange behaviour with a ProgressBar is new to me and I have been using them for a long time now. It is a possibility that some sort of glitch has been introduced with one of the VS updates and if so, may end up being fixed with a later update - in which case, presumably these issues will no longer be relevant.

    In the meantime, if I was faced with this and doing a task like yours, I would revert to removing the PB completely and just use a MessageBox (more or less what you seem to say).

    Here is the end code I would be using (note: I just put results in a ListBox instead of an Excel Sheet)

    Option Strict On
    Option Explicit On
    ' Form1 with empty DGV, ListBox1, Label1
    ' Button1 and BackGroundWorker1
    Imports System.ComponentModel
    Public Class Form1
      Dim lst As New List(Of String)
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        With DGV
          .Columns.Add(New DataGridViewTextBoxColumn)
          .Columns.Add(New DataGridViewCheckBoxColumn)
          .Columns.Add(New DataGridViewCheckBoxColumn)
          .Columns.Add(New DataGridViewTextBoxColumn)
        End With
      End Sub
      Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        If BackgroundWorker1.IsBusy Then Exit Sub
        Dim r As New Random
        ListBox1.Items.Clear()
        Dim c As Integer = 1000
        With DGV
          .Rows.Clear()
          For i As Integer = 0 To c
            .Rows.Add(i, {True, False}(r.Next(0, 2)), {True, False}(r.Next(0, 2)), i / 3)
          Next
        End With
        BackgroundWorker1.RunWorkerAsync()
      End Sub
      Private Sub BackgroundWorker1_DoWork(sender As Object, e As DoWorkEventArgs) Handles BackgroundWorker1.DoWork
        Dim count As Integer = 0
        For Each r As DataGridViewRow In DGV.Rows
          If Not r.Index = DGV.NewRowIndex Then
            ExportNormalRow(r.Index)
            Invoke(Sub() Label1.Text = r.Index.ToString)
          End If
        Next
      End Sub
      Private Sub BackgroundWorker1_RunWorkerCompleted(sender As Object, e As RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted
        MessageBox.Show("The Excel Sheet is complete!")
      End Sub
      Private Sub ExportNormalRow(DGVrowindex As Integer)
        Dim ExcolIndex As Integer = 1
        Dim DGVcolindex As Integer = 0
        For Each dc As DataGridViewColumn In DGV.Columns
          If dc.Visible = False Then
            DGVcolindex += 1
            Continue For
          End If
    
          Dim Val As Object = DGV(DGVcolindex, DGVrowindex).Value
          If TypeOf dc Is DataGridViewTextBoxColumn Then
            Val = Val.ToString
          ElseIf TypeOf dc Is DataGridViewCheckBoxColumn Then
            If CBool(Val) = True Then
              Val = "Si"
            Else
              Val = "No"
            End If
          End If
          '  MySheet.Cells(ExrowIndex, ExcolIndex) = Val()
          ' instead of an Excel Sheet, here
          ' I just use a ListBox to capture
          ' the values
          Invoke(Sub() ListBox1.Items.Add(Val))
          ExcolIndex += 1
          DGVcolindex += 1
        Next
      End Sub
    End Class


    Regards Les, Livingston, Scotland


    • Edited by leshay Monday, December 31, 2018 6:28 PM
    Monday, December 31, 2018 6:27 PM
  • Hi

    Happy New Year too - may all your applications be bug free!.

    *

    I have reported these issues to Developer Community to see if it invokes any response. See HERE


    Regards Les, Livingston, Scotland

    • Marked as answer by Claudio111 Wednesday, January 2, 2019 9:43 PM
    Monday, December 31, 2018 6:59 PM
  • Hi Tommy

    if I use just a Label to show rows count of % everything is ok. 

    The problem is the PB graphics. In my code Excel is shown as soon as the PB VALUE reachs its maximun value even if the graphics is not yet completed.

    Anyway I will use a massagebox.show before showing the excel.

    The PB lag is, in any case, something strange and I'm curious to find a way to stop execution until the PB graphics is completed. There should be a way. !!!

    Tuesday, January 1, 2019 9:39 AM
  • Thank you Les, iI'm curious to see a solution
    Tuesday, January 1, 2019 9:40 AM
  • Hi Tommy

    never mind. I will send a message to user so the PB can be completed while User read the message.

    In any case what is strange to me is that there is no way to stop statments execution until progress bar is completed. (of course without backgroudwork).

    Thank you to all for help me in  this problem.

    Claudio

    Wednesday, January 2, 2019 5:12 PM
  • Yes Tommy, your example reproduces the problem. I mean there is no way to get the messagebox only after pb graphics get 100%. If you try with 20 rows without backgroundwork , and you loop trhu rows sending a message at the end of loop, whell you can get the message when the pb is at 50/60% so the pb is very late in respect to the loop
    Wednesday, January 2, 2019 6:16 PM
  • I mean there is no way to get the messagebox only after pb graphics get 100%. 

    Use SetWindowTheme

        <DllImport("Uxtheme.dll", SetLastError:=True)>
        Public Shared Function SetWindowTheme(ByVal hWnd As IntPtr, ByVal pszSubAppName As String, ByVal pszSubIdList As String) As Integer
        End Function

     SetWindowTheme(ProgressBar1.Handle, "", "")

    Wednesday, January 2, 2019 6:44 PM