如何加速更新多个单元格的OpenOffice Calc宏？

Question

我有一个OpenOffice Calc宏（使用Basic编写），可以将活动工作表中的所有数字四舍五入到指定的小数位数。处理包含9000行的电子表格中的100行大约需要4秒。每行有35列，其中19列是数字列。

我该如何让它更快地执行？这是我为OpenOffice编写的第一个宏，所以可能有比我不知道的更快的方法。以下是我的代码：

Dim oFunction as Object 


' Round all Value cells (cells that do not contain Text or Functions) on the current sheet to as many decimal places as user requests.
Sub RoundUsedCells

Dim  oSheet As Object, oUsedRange as Object, oCursor as Object, oCell As Object
Dim row, column,lastRow, lastColumn
Dim places$, placesToRound
Dim roundedValue as Double

' Ask user how many decimal places to round
places$ = InputBox ("Round to how many places (leave blank to cancel)?")
if (places$ is Nothing or places$ = "") then
    ' Do nothing.
else
    placesToRound = CInt(places$)

    ' Get the currently active sheet.
    oSheet = thiscomponent.getcurrentcontroller.activesheet

    ' Create a one cell range at the origin,
    ' then create a cursor that allows us to extend the range to include all the "used" cells.
    ' The cursor will then allow us to find out how large is that used range.
    oUsedRange = oSheet.getCellRangeByPosition(0,0,0,0)
    oCursor = oSheet.createCursorByRange(oUsedRange)
    oCursor.GotoEndOfUsedArea(false)    

    ' Obtain the last rows and colums in the "used" range from the cursor.
    lastRow = oCursor.RangeAddress.EndRow  
    lastColumn = oCursor.RangeAddress.EndColumn 

    ' Loop through all cells from the origin (0,0) to the last used column and row.
    for row = 0 to lastRow
        if (row mod 50 = 0) then
            StatusBar "Rounding row " + (row + 1) + " of " + (lastRow + 1) + "..."
        endif
        for column = 0 to lastColumn
            oCell = oSheet.getCellByPosition(column, row)
            Select Case oCell.Type 
                Case com.sun.star.table.CellContentType.VALUE
                   ' Only round value cells. Skip over empty cells, formuls and text.
                   roundedValue = Round(oCell.Value, placesToRound)
                   if (roundedValue <> oCell.Value) then
                        oCell.Value = roundedValue
                   endif
                Case com.sun.star.table.CellContentType.EMPTY 
                Case com.sun.star.table.CellContentType.TEXT       
                Case com.sun.star.table.CellContentType.FORMULA

            End Select  
        next column
    next row
    StatusBar "Rounding complete."
endif
End Sub

' Obtain access to worksheet functions, such as the "round" function.
Sub InitRound 
    if (oFunction is Nothing) then
        oFunction = createUnoService("com.sun.star.sheet.FunctionAccess") 
    endif
End Sub 

' Round the value to the given number of places after the decimal.
Function Round(value, decimalPlaces) 
   InitRound() 
   Dim args( 1 to 2 ) As Variant 
   args(1) = value 
   args(2) = decimalPlaces 
   Round = oFunction.callFunction( "round", args() ) 
End Function

global vStatusBarText as string '=text that is been displayed on the statusbar
sub StatusBar(optional vNewText, optional vAddText) 'set or add text to the statusbar, nothing = clear&reset it.
   if isError(vNewText) then
      if isError(vAddText) then 'clear statusbar
         vStatusBarText=""
      else 'add text to the previous statusbar
         vStatusBarText=vStatusBarText & vAddText 
      endif
   else
      if isError(vAddText) then 'use new text
         vStatusBarText=vNewText
      else 'use new text and add the other text as well
         vStatusBarText=vNewText & vAddText
      endif
   endif
   vStatusBarText=right(vStatusBarText,int(ThisComponent.CurrentController.VisibleArea.Width/150)) 'Select last part that could be displayed.
   if isNull(ThisComponent.CurrentController) then exit sub 'Because the last XEventListener-event can't be written to the statusbar, because it's no longer there!
   if vStatusBarText="" then 'reset statusbar
      ThisComponent.CurrentController.StatusIndicator.Reset
   else 'change the text in the statusbar
      ThisComponent.CurrentController.StatusIndicator.Start(vStatusBarText,0)
   endif
end sub

更新：我重新编写了Round函数，不再调用Calc，速度提高了一倍。但仍然过慢。必须比每秒处理五十行的速度更快。

Function Round2(value, decimalPlaces) as Double
    Round2 = Int(value * 10 ^ decimalPlaces + 0.5) / 10 ^ decimalPlaces
   'Dim multiplier as Double, bigValue as Double
   'multiplier = 10 ^ decimalPlaces
   'bigValue = (value * multiplier) + 0.5
   'Round2 = CDbl( CLng(bigValue) ) / multiplier  
End Function

更新2:

找到了一种方法，在宏执行期间禁用自动更新和屏幕刷新，将速度提高了三倍（现在每秒可以处理200行）：

myDoc = ThisComponent
myDoc.lockControllers()
myDoc.addActionLock()
' --- modify your cells here ---
myDoc.removeActionLock()
myDoc.unlockControllers()

- Paul Chernoch

---

你真的需要四舍五入的数字，还是只想要它们以特定数量的小数点显示？如果是后者，那么只需要应用格式化（一次）即可... - twalberg

这个函数是比较两个工作表的工作流程的一部分。我正在粘贴来自 SQL Server 的两个查询（遗留和优化）的结果，并且我需要知道结果是否匹配。数字值通常在第十一或第十二小数位上有所不同，我不想将其标记为差异。因此，我正在强制四舍五入。我想我可以使比较模糊，而不实际更改值... - Paul Chernoch

我在考虑是不是重新计算引擎在干扰。有没有办法关闭和开启重新计算？ - Paul Chernoch

发现了如何禁用重新计算并添加到问题中。性能提高了很多。如果能再将速度提升一倍，我就满意了。 - Paul Chernoch

Answer 1

我的定时测试表明，您最大的问题是一次只访问一个单元格。我的测试表明，有两种方法可以显著增加此类操作的速度。

最有效的方法是使用某些本地工具来执行此操作；例如，将显示样式设置为以特定格式显示，如果可能的话，在指定点处舍入显示。

下一个最好的解决方案是分批获取数据并更新数据。您用于获取数据的方法将取决于数据的一致性。例如，getDataArray() 和 setDataArray() 可能比逐个查看单元格快100倍（至少）。这里有两种不同的可能方法：

getData() 将数字数据作为嵌套值序列（数组中的数组）获取。

getDataArray() 与 getData() 相同，但可能包含 String 或 Double。

- Andrew