Barra de progreso al exportar datos Excel

[jparada]

Estoy exportando datos a Excel, quiero poner una barra de progreso (de hecho nunca he utilizado ese control) pero como no hay un contador, no se me ocurre cómo implementar la barra de progreso

Este es el código que obtiene los datos y exporta a Excel

Code: Select all

oRs := oConn:Execute( "EXEC dbo.uspAnalisisVenta" )
     
n := oRs:RecordCount()
If oRs:Eof()
  MsgInfo( 'No hay información' )
  oRs:Close()
Endif
  
nSecs := Seconds()
With Object oExcel:ActiveWorkBook:ActiveSheet()
  :QueryTables:Add( oRs, :Range('A1')):Refresh()
EndWith    
nSecs := Seconds() - nSecs

*-- Convert Range to Table and Apply Table Style
oSheet:ListObjects:Add( xlSrcRange, oSheet:Range('A1'):CurrentRegion,, xlYes ):Name = 'tblVentas'
oSheet:ListObjects( 'tblVentas' ):TableStyle = 'TableStyleMedium15'

oRs:Close()
oConn:Close()

Alguna ayuda cómo puedo implementar la barra de progreso

Saludos,
Javier

[AUGE_OHR]

hi Javier

you do have

Code: Select all

   n := oRs:RecordCount()

now you can "calculate" to 100%

Code: Select all

   nEvery := INT(n/100)

in Loop you have to add a "Counter" and test it like this

Code: Select all

   With Object oExcel:ActiveWorkBook:ActiveSheet()
     :QueryTables:Add( oRs, :Range('A1')):Refresh()
      nCount++
      IF ( nCount % nEvery ) == 0
         nPosi := GetProperty( cForm, "ProgressBar_1", "Value" )
         SetProperty( cForm, "ProgressBar_1", "Value", nPosi + 1 )
         DO EVENTS 
      ENDIF
   EndWith

[SALINETAS24]

Estoy exportando datos a Excel, quiero poner una barra de progreso (de hecho nunca he utilizado ese control) pero como no hay un contador, no se me ocurre cómo implementar la barra de progreso

Este es el código que obtiene los datos y exporta a Excel
......

Hola Jorge, si no te quieres complicar mucho la vida puedes probar con esto...

Code: Select all

oRs := oConn:Execute( "EXEC dbo.uspAnalisisVenta" )
     
n := oRs:RecordCount()
If oRs:Eof()
  MsgInfo( 'No hay información' )
  oRs:Close()
Endif
  
nSecs := Seconds()
With Object oExcel:ActiveWorkBook:ActiveSheet()

  //---------------------------------------------------------------------
   WAIT WINDOW ("Procesando ..."+str(sECONDS()) NOWAIT  // le puede añadir lo que quieras, por ejemplo EL VALOR PROCESADO
  //---------------------------------------------------------------------
  
  :QueryTables:Add( oRs, :Range('A1')):Refresh()
EndWith    
//---------------------------------------------------------------------
Wait Clear   // Terminamos
//---------------------------------------------------------------------

nSecs := Seconds() - nSecs

*-- Convert Range to Table and Apply Table Style
oSheet:ListObjects:Add( xlSrcRange, oSheet:Range('A1'):CurrentRegion,, xlYes ):Name = 'tblVentas'
oSheet:ListObjects( 'tblVentas' ):TableStyle = 'TableStyleMedium15'

oRs:Close()
oConn:Close()

Saludos con una solución sencilla...,

[jparada]

Hi Jimmy,

Trying with your example, progressbar never advances.

What initial value should nCount have? I tried with Local nCount := 0

Could you please help a little more to see if we can get it to work.

Regards,
Javier

[SALINETAS24]

Hi Jimmy,

Trying with your example, progressbar never advances.

What initial value should nCount have? I tried with Local nCount := 0

Could you please help a little more to see if we can get it to work.

Regards,
Javier

Siempre y cuando la variable "n" contenga el numero de registros, <n := oRs:RecordCount()> y nCount vaya sumando dentro del bucle <nCount++>, prueba con esta linea.

Code: Select all

 SetProperty( cForm, "ProgressBar_1", "Value", (nCount/n)*100	) )

Salud y cerveza para todos....

[AUGE_OHR]

hi,

try Sample \SAMPLES\Controls\ProgressBar\PROGRESSBAR_2\

you need to "increase" Progressbar with

Code: Select all

   // read old Value
   nPosi := GetProperty( cForm, "ProgressBar_1", "Value" )
   // set new Value
   SetProperty( cForm, "ProgressBar_1", "Value", nPosi + 1 )

btw. you have to use "your" Name for FORM (cForm) and Progressbar

but i do not want to "move" Progressbar every time so "test" it with

Code: Select all

   nCount++
   IF ( nCount % nEvery ) == 0

as we have use

Code: Select all

   n := oRs:RecordCount()
   nEvery := INT(n/100)

Progressbar will only move when got next 1%

[jparada]

A menos que no les esté entendiendo, no hay bucle, no hay ciclo, la idea de Jimmy me parece puede ir más apegado a la solución pero no me funciona porque la barra no avanza.

Saludos,
Javier

[AUGE_OHR]

hi,

did Sample Sample \SAMPLES\Controls\ProgressBar\PROGRESSBAR_2\ work on your PC

my Sample might fail when have < 100 while

Code: Select all

   n := oRs:RecordCount()
   nEvery := INT(n/100)

give 0 with INT() when n < 100

[jparada]

Hi Jimmy, yes I realized it because precisely as tests the query obtained less than 100 records, at this moment I made real tests obtaining more than 5k records and the same thing the progressbar does not advance, it does not even paint, probably this solution is not for what I require, perhaps and not to complicate so much I should use a simple for next or do while enddo the problem is that it would not comply with the rule of those who say that everything can be done in programming... yes I know... I should be smarter)

Regards,
Javier

[mol]

Try to add line:
Do Events
In your loop