| View previous topic :: View next topic |
| Author |
Message |
liam
Newbie
Joined: 10 Apr 2010
Posts: 3
|
 Posted: Sat Apr 10, 2010 3:08 am Post subject: Wriying arrays into a spreadsheet |
 |
|
If I have an array X(1000) and want to write its contents into a spreadsheet, say Sheet1, I would write the following
Dim i
For i=0 To 999
thisComponent.Sheets.GetByName("Sheet1").getCellRangeByName("A1:A10000").getCellByPosition(0,i).String=X(i)
Next i
I have noticed though, this is a sluggish way to do this. It can take seconds, when in reality it should be able to do this almost instantly.
Is there any quicker way? |
|
| Back to top |
|
 |
B Marcelly
Super User
Joined: 12 May 2004
Posts: 1414
Location: France
|
| Posted: Sat Apr 10, 2010 5:52 am Post subject: |
|
|
Hi,
- Don't suppose that Basic will optimize your code. Do it yourself.
- Learn API and read similar posts
| Code: | Dim mySheet As Object
Dim x As Long, t As Long, v(10000) As String
for x = 0 to 10000
v(x) = "Hello " & x
next
t = GetSystemTicks
ThisComponent.lockControllers
mySheet = ThisComponent.Sheets.getByName("Sheet1")
for x = 0 to 1000
mySheet.getCellByPosition(1, x).String = v(x)
next
ThisComponent.unlockControllers
MsgBox("Done in " & GetSystemTicks-t & " milliseconds") |
The writing loop takes around 180 millisec on my PC.
_________________
Bernard
OpenOffice.org 1.1.5 fr / OpenOffice.org 3.4.1 en-US + langpacks, MS-Windows XP Home SP3
This forum is unusable, use instead Apache OpenOffice forums |
|
| Back to top |
|
|
turtle47
Power User
Joined: 24 Aug 2008
Posts: 66
Location: Germany
|
| Posted: Sat Apr 10, 2010 6:54 am Post subject: |
|
|
The following code is slightly faster:
| Code: | Sub Main
Dim x As Long, t As Long, v(10000,0) As String
for x = 0 to 10000
v(x,0) = "Hello " & x
next
t = GetSystemTicks
oSheet1 = ThisComponent.sheets().getbyname("Tabelle1")
oSheet1.getCellrangeByName("A1:A10001").SetDataArray(v())
MsgBox("Done in " & GetSystemTicks-t & " milliseconds")
End Sub |
It takes around 110 millisec on my PC
_________________
My Extensions
Color2Rows and LastSession |
|
| Back to top |
|
|
B Marcelly
Super User
Joined: 12 May 2004
Posts: 1414
Location: France
|
| Posted: Sat Apr 10, 2010 9:14 am Post subject: |
|
|
| turtle47 wrote: | | The following code is slightly faster: |
Well, not slightly 
it is more than 10 times faster.
I also tried it, but made a mistake with the loop numbers ( in my above code, 1000 instead of 10 000) and erroneously found a similar value for both algorithms.
Well done 
Edit
I also found that your code runs in 109 millisec.
But if you change one line as : | Code: | | oSheet1.getCellrangeByName("A1:A10001").DataArray = v() |
then it runs in 234 millisec. Very strange.
_________________
Bernard
OpenOffice.org 1.1.5 fr / OpenOffice.org 3.4.1 en-US + langpacks, MS-Windows XP Home SP3
This forum is unusable, use instead Apache OpenOffice forums |
|
| Back to top |
|
|
turtle47
Power User
Joined: 24 Aug 2008
Posts: 66
Location: Germany
|
| Posted: Sat Apr 10, 2010 10:14 am Post subject: |
|
|
| Quote: | | I also tried it, but made a mistake with the loop numbers ( in my above code, 1000 instead of 10 000) |
Thanks for feedback.
I was somewhat blind on my eyes 
_________________
My Extensions
Color2Rows and LastSession |
|
| Back to top |
|
|
|
