OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Question about Macros and efficiency

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
arla
Newbie
Newbie


Joined: 19 Mar 2007
Posts: 2

PostPosted: Mon Mar 19, 2007 11:54 am    Post subject: Question about Macros and efficiency Reply with quote

So I recently had to switch some work stuff I had been doing out of Excel and into OO, while I generally like OO the speed of the macro that I came up with seems abominal when compared to how fast excel was doing it.

Unfortunately I did find it quite hard to find instructions on how to write this, so I may have used horribly inefficient procedures, or maybe it's just something in OpenOffice Basic that makes it so.

This is the macro

Code:

sub Main
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
dim cellValue
dim oSheet1
dim oSheet2
dim oDoc
dim rowcount as Integer
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame

oDoc=ThisComponent
oSheet1 = oDoc.getSheets().getByIndex(0)
oSheet2 = oDoc.getSheets().getByIndex(1)

for rowcount = 0 to 65536


rem Get value for risk type
cellValue = oSheet1.getCellByPosition(8, rowcount - 1).getString()
rem change D2 value to be the contents of sheet1(I,rowcount)
oSheet2.getCellByPosition(3,1).setString(cellValue)

rem Get value for Vehicle type
cellValue = oSheet1.getCellByPosition(9, rowcount - 1).getString()
rem change D2 value to be the contents of sheet1(I,rowcount)
oSheet2.getCellByPosition(4,1).setValue(cellValue)

rem Get value for Year
cellValue = oSheet1.getCellByPosition(1, rowcount - 1).getString()
rem change D2 value to be the contents of sheet1(I,rowcount)
oSheet2.getCellByPosition(5,1).setValue(cellValue)

rem Get value for Size
cellValue = oSheet1.getCellByPosition(10, rowcount - 1).getString()
rem change D2 value to be the contents of sheet1(I,rowcount)
oSheet2.getCellByPosition(6,1).setValue(cellValue)

rem Get value for Performance
cellValue = oSheet1.getCellByPosition(14, rowcount - 1).getString()
rem change D2 value to be the contents of sheet1(I,rowcount)
oSheet2.getCellByPosition(7,1).setValue(cellValue)

rem Get value for Shell
rem I is 8, row is already setup
cellValue = oSheet1.getCellByPosition(12, rowcount - 1).getString()
rem change D2 value to be the contents of sheet1(I,rowcount)
oSheet2.getCellByPosition(8,1).setString(cellValue)


Rem Copy Stat Code
cellValue = oSheet2.getCellByPosition(9,1).getString()
rem Set BO rowcount to the Stat Code
oSheet1.getCellByPosition(66,rowcount-1).setString(cellValue)


next rowcount

end sub


Basically all it's doing is copying 5 values from sheet 1 to sheet 2, sheet 2 has a number of vlookup calculations on it, which work out a value, and then that value is copied from sheet 2 back into sheet 1.

The only thing I could see that might be useful would be stopping OpenOffice from calculating until the "Rem Copy Stat Code" statement, let it recalc there and then not any longer. However, on a speed side, Excel was running a similar macro for 65k rows in about 10 minutes, I left OO to run for a few hours and it had still only processed about 6k rows... any help greatly appriciated (because I'm a complete newbie when it comes to using macros in OO).
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Mar 19, 2007 1:23 pm    Post subject: Reply with quote

Search this forum for postings of TerryE. In general it is very unefficient to get/set spreadsheet cells one by one. Use getDataArray and set DataArray getFormulaArray instead.
Using the spreadsheet-functions is by far the fastest way to go:
Code:

oDoc = Stardesktop.loadComponentFromURL("private:factory/scalc,"_blank",0,Array())
oSheet = oDoc.sheets.getbyindex(0)
oSheet.getCellByPosition(0, 0).setFormula("=RAND()")
oSheet.getCellByPosition(1, 0).setFormula("=$A1*PI()")
oDragDown = oSheet.getCellRangeByposition(0,0,1,65535)
oDragDown.fillSeries(com.sun.star.sheet.FillDirection.TO_BOTTOM,com.sun.star.sheet.FillMode.SIMPLE,0,0,0)
oDragDown.setDataArray(oDragDown.getDataArray())

Less than a minute.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
arla
Newbie
Newbie


Joined: 19 Mar 2007
Posts: 2

PostPosted: Wed Mar 21, 2007 2:38 pm    Post subject: Reply with quote

Yes, but that really doesn't help at all with what I'm trying to do.

What I need is to copy 5 values from one sheet to another, let the second sheet calculate, and then copy a single value back, and then repeat this 65k times.

While I could write code to do it, by the time I've done that I may as well write it in C# and not even bother using OpenOffice at all.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Mar 21, 2007 3:05 pm    Post subject: Reply with quote

I don't know what you are calculating exactly, but I'm 90% shure it can be done in any spreadsheet using formulae alone.
The amount of data and your mention of "a number of vlookup calculations" suggest a database solution.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group