Excel automation slow

General
1

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

What is

dim ioRecord as DatabaseRecord = IOdata(recordNumber)

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

3

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

4

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

5

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

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

8

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.

[code]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 ")[/code]

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 :slight_smile:

9

@Jan Verrept - 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

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

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.?