https://bugs.freedesktop.org/show_bug.cgi?id=45607
--- Comment #12 from pierre-yves samyn <[email protected]> 2012-02-04 23:43:44 PST --- Hello With my environnment(Intel 2 Duo 3.07GHz CPU, 4 GB RAM Win 7 64) the initial procedure runs 9.687 (for 200 lines added) Let me add a few tips 1. Display management Select each cell before filling it is useless. Remove this reduces to 9.47 2. Locking the document The pair of instructions lockControllers unLockControllers can locks the document during treatment to reduce to 6.47 3. Management recalculation As already said, inhibit automatic recalculation for the loop can reduce to 0.218 (2.979 for 2000 lines added) 4. Management of data type 4.1 NR (the first argument of the procedure show_number) receives the result of the ROW function. This is a number but it is unnecessary to convert to text to concatenate with the "." (it is also unnecessary to add the TRIM function). With 2000 lines added it can reduce the execution time from 2.979 to 2.948 and let the function itself faster. 4.2 Using the "integer" type for a variable that holds the line numbers can be "dangerous" as this type is limited to 32,767 while the number of lines is now 1,048,576 (error message "Inadmissible value or data type. Overflow ") Below is the code with these changes Regards Pierre-Yves '***************************************** option explicit Sub test Const END_ROW As Integer = 1999 Dim document As Object Dim dispatcher As Object document = ThisComponent.CurrentController.Frame dispatcher = createUnoService("com.sun.star.frame.DispatchHelper") Dim oDoc As Object Dim oSheet As Object Dim oRange As Object Dim oCell As Object dim fenDoc as Object oDoc = ThisComponent oSheet = oDoc.getCurrentController.ActiveSheet ' Select and clean the test cell range oRange = oSheet.getCellRangeByPosition(0, 0, 1, END_ROW) oDoc.CurrentController.select(oRange) dispatcher.executeDispatch(document, ".uno:ClearContents", "", 0, Array()) Dim fun As String fun = "=SHOW_NUMBER(ROW(); ISBLANK(INDIRECT(""B"" & ROW())))" Dim start_time As Long ' Fill the test cell range: ' column 1: formula for displaying the row number ' column 2: a constant string Dim row As Integer thisComponent.lockControllers thisComponent.enableAutomaticCalculation(false) start_time = GetSystemTicks() For row = 0 To END_ROW ' oRange = oSheet.getCellRangeByPosition(0, row, 0, row) ' oDoc.CurrentController.select(oRange) oCell = oSheet.getCellByPosition(0, row) oCell.setFormula(fun) oCell = oSheet.getCellByPosition(1, row) oCell.String = "abc" Next row oRange = oSheet.getCellRangeByPosition(0, 0, 0, 0) oDoc.CurrentController.select(oRange) 'oRange = oDoc.createInstance("com.sun.star.sheet.SheetCellRanges") 'oDoc.CurrentController.select(oRange) thisComponent.enableAutomaticCalculation(true) thisComponent.calculateAll thisComponent.unlockControllers Print "Test executed in " + (GetSystemTicks() - start_time)/1000 + " s" End Sub '************************************************************************** Function show_number(ByVal nr As Integer, ByVal empty As Boolean) As String show_number = "" If Not empty Then ' show_number = Trim(Str(nr)) + "." show_number = nr & "." End If End Function -- Configure bugmail: https://bugs.freedesktop.org/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the assignee for the bug. _______________________________________________ Libreoffice-bugs mailing list [email protected] http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
