[Solved] How to disable screen update, change calculate mode

[ikw_chen]

Hi All,

When fill data to Calc cells use UNO API (Java), it's take a lot time to re-calculate all formula related to the updated cell.

Is there any way temporary disable the screen update & the change the calculate mode from automatic to manual like MS Excel does.

Code: Select all

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
...
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Code: Select all

XComponent xSpreadsheetComponent = xCompLoader.loadComponentFromURL(sLoadUrl, "_default", 0, loadProps);
XSpreadsheetDocument xDocument = (XSpreadsheetDocument)UnoRuntime.queryInterface(XSpreadsheetDocument.class,xSpreadsheetComponent);
		
XSpreadsheets xSheets = xDocument.getSheets();
Object oSheet = xSheets.getByName(sheet_Estimation);
XSpreadsheet xSheet = (XSpreadsheet)UnoRuntime.queryInterface(XSpreadsheet.class, oSheet);
			
rgColName = xSheet.getCellRangeByName("colName");

rgColName .getCellByPosition(x, y).setFormula("abc"); 

...

Thanks for advice!

[Zizi64]

Hi,

You can switch on/off the Autocalculation Option in Menu:
Tools - Cell Content - AutoCalculate

I have recorded this code by Macrorecorder:

Code: Select all

rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "AutomaticCalculation"
args1(0).Value = false

dispatcher.executeDispatch(document, ".uno:AutomaticCalculation", "", 0, args1())

[Villeroy]

Found Calc equivalent to Excel's ScreenUpdating = False

Code: Select all

import com.sun.star.sheet.XCalculatable;
import com.sun.star.uno.UnoRuntime;

static public void snippet(Object oInitialTarget)
{
	XCalculatable xCalculatable = UnoRuntime.queryInterface(
		XCalculatable.class, oInitialTarget);
	xCalculatable.enableAutomaticCalculation(false);
	
}

[B Marcelly]

Hi,
Freezing screen is done with method lockControllers() of the document's Model.
Freezing recalculations is done with method addActionLock() of the document.
With Basic it's simple:

Code: Select all

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

In Java you have to get the interfaces.
Lock/unlock and add/remove must work in pairs.

[ikw_chen]

Thanks all for your help .

I tried with code propose by Villeroy, the performance increased a bit (by change the XcalculateTable to Manual). But when I fill data to cell, it's still update on screen. I'm still looking for the method to freezing screen (in Java) as mentioned by B Marcelly

Freezing screen is done with method lockControllers() of the document's Model.
Freezing recalculations is done with method addActionLock() of the document.

Code: Select all

   XCalculatable xCalculatable = UnoRuntime.queryInterface(
      XCalculatable.class, oInitialTarget);
   xCalculatable.enableAutomaticCalculation(false);

Regards,

[ikw_chen]

Combine method propose by B Marcelly. It works well.

Thanks all so much.

Regards,