Excel automation slow

  1. 2 weeks ago

    The simplified code below takes a long time to execute
    Up to 0.6 seconds for a single iteration. That is 10 minutes in total for a 1000 lines.
    It used to be real fast :-/

         
            dim lastRecordNumber as Integer = 999
            for recordNumber as Integer = 0 to lastRecordNumber
            
            if  (recordNumber= 0) OR (recordNumber mod 10) = 0  OR (recordNumber= lastRecordNumber) then
              system.debuglog("Exporting record "+str(recordNumber)+"/"+Str(lastRecordNumber))
            end if
            
            dim ioRecord as DatabaseRecord = IOdata(recordNumber)
            dim rowNumber as Integer = recordNumber+6 // Keep a Header of 6 lines
            dim currentRow as ExcelRange = app.excel.Rows(rowNumber) // a Global instance of ExcelApplication
            
           currentRow.Columns("A") = "aValueForColumnA"
           currentRow.Columns("B") = "aValueForColumnB"
           currentRow.Columns("C") = "aValueForColumnC"
           currentRow.Columns("D") = "aValueForColumnD"
            
          next recordNumber
    
  2. Jeff T

    Jun 8 Midlands of England, Europe

    What is

    dim ioRecord as DatabaseRecord = IOdata(recordNumber)

    doing?
    How fast does it go if that line is commented out?

  3. Dave S

    Jun 8 San Diego, California USA

    not to mention that system.debuglog is REALLY slow (see discussion on recent topic)

  4. Neil B

    Jun 8 Pre-Release Testers

    Try turning off screenupdating and setting calculation to manual. That should help speed things up.

  5. Paul B

    Jun 8 Pre-Release Testers, Xojo Pro Europe (UK, Somerset)

    When I do any work with Excel I only make it visible at the end of the processing, see if that helps.

  6. Hi, thx for all the advice.
    However changed my code to the one below.
    Remains very slow (+50 seconds to write only 100 excel rows with 4 columns)

    app.excel.Visible = FALSE app.excel.calculation = office.xlCalculationManual system.debuglog("Start exporting records ") for recordNumber as Integer = 0 to 99 dim rowNumber as Integer = recordNumber+6 dim currentRow as ExcelRange = app.excel.Rows(rowNumber) currentRow.columns("A") = "Kolom A" currentRow.columns("B") = "Kolom B" currentRow.columns("C") = "Kolom C" currentRow.columns("D") = "Kolom D" next recordNumber system.debuglog("End exporting records ")

  7. Neil B

    Jun 11 Pre-Release Testers

    May be time for a faster computer. That runs in .5 seconds on my machine.

  8. Eugene D

    Jun 11 Pre-Release Testers, Xojo Pro Canada scispec.ca

    Hello Jan,

    Neil may be correct that the computer might be slow, or another possibility is that accessing data from the database might be slow. I modified your program and the below code populates 1000 rows with 4 columns and it takes about 10 seconds on my computer.

    Dim Excel as new ExcelApplication
    Excel.Visible = True
    Excel.Workbooks.Add
    
    system.debuglog("Start exporting records ")
    for recordNumber as Integer = 0 to 1000
      
      dim rowNumber as Integer = recordNumber+6
      
      Excel.Cells(rowNumber, 1)="Kolom A" //A = 1
      Excel.Cells(rowNumber, 2)="Kolom B" //B = 2
      Excel.Cells(rowNumber, 3)="Kolom C" //C = 3
      Excel.Cells(rowNumber, 4)="Kolom D" //D = 4
      
    next recordNumber
    system.debuglog("End exporting records ")

    There may be ways to improve the speed of data records in the program. Can you run my code and let me know the speed to finish populating 1000 rows?

    Thanks :)

  9. Joost R

    Jun 11 Pre-Release Testers, Xojo Pro The Netherlands

    @Jan V - you could also consider using Einhugur Excelwriter / Excelreader plugin .
    No need for having Excel installed on the same machine and, as far as I know, you can just try these plugins for free.

  10. Neil B

    Jun 11 Pre-Release Testers
    Edited 2 weeks ago

    @Eugene D Can you run my code and let me know the speed to finish populating 1000 rows?

    On my computer it takes 4.38 seconds. That's about 1 second faster than Jan's code.

  11. The problem seems to be located in some unrelated part of the program.
    List.cellbackgroundpaint-event keeps firing costantly :-/

    commented out the event and everything was fast again (less then 2 sec for 1000 rows, that's more like it).

  12. last week

    Dave S

    Jun 12 San Diego, California USA

    or the control is attempting to do a complete redraw each time you add a row, when in fact you don't need it to redraw until you have finished populating it.?

or Sign Up to reply!