| View previous topic :: View next topic |
| Author |
Message |
arla
Newbie
Joined: 19 Mar 2007
Posts: 2
|
 Posted: Mon Mar 19, 2007 11:54 am Post subject: Question about Macros and efficiency |
 |
|
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 |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Mon Mar 19, 2007 1:23 pm Post subject: |
|
|
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 http://forum.openoffice.org |
|
| Back to top |
|
|
arla
Newbie
Joined: 19 Mar 2007
Posts: 2
|
| Posted: Wed Mar 21, 2007 2:38 pm Post subject: |
|
|
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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Wed Mar 21, 2007 3:05 pm Post subject: |
|
|
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 http://forum.openoffice.org |
|
| Back to top |
|
|
|
|
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
|
