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

Creating a macro - Writing a Script - Using the API

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

Postby ikw_chen » Sat Jul 09, 2011 2:12 pm

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   Expand viewCollapse view
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
...
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic



Code: Select all   Expand viewCollapse view
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!
Last edited by TheGurkha on Sun Jul 10, 2011 5:47 pm, edited 2 times in total.
Reason: Tagged Solved, TheGurkha.
Openoffice 3.3 on WinXP
ikw_chen
 
Posts: 17
Joined: Tue Apr 12, 2011 4:03 pm

Re: How to disable screen update , change calculate mode on

Postby Zizi64 » Sun Jul 10, 2011 10:08 am

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   Expand viewCollapse view
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())
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.0 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7700
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to disable screen update , change calculate mode on

Postby Villeroy » Sun Jul 10, 2011 10:20 am

Found Calc equivalent to Excel's ScreenUpdating = False
Code: Select all   Expand viewCollapse view
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);
   
}
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26250
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to disable screen update , change calculate mode on

Postby B Marcelly » Sun Jul 10, 2011 3:17 pm

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   Expand viewCollapse view
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.
Bernard

OpenOffice.org 1.1.5 / Apache OpenOffice 4.1.1 / LibreOffice 5.0.5
MS-Windows 7 Home SP1
B Marcelly
Volunteer
 
Posts: 1160
Joined: Mon Oct 08, 2007 1:26 am
Location: France, Paris area

Re: How to disable screen update , change calculate mode on

Postby ikw_chen » Sun Jul 10, 2011 4:52 pm

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   Expand viewCollapse view
   XCalculatable xCalculatable = UnoRuntime.queryInterface(
      XCalculatable.class, oInitialTarget);
   xCalculatable.enableAutomaticCalculation(false);


Regards,
Openoffice 3.3 on WinXP
ikw_chen
 
Posts: 17
Joined: Tue Apr 12, 2011 4:03 pm

Re: How to disable screen update , change calculate mode on

Postby ikw_chen » Sun Jul 10, 2011 5:42 pm

Combine method propose by B Marcelly. It works well.

Thanks all so much.

Regards,
Openoffice 3.3 on WinXP
ikw_chen
 
Posts: 17
Joined: Tue Apr 12, 2011 4:03 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: polusha and 7 guests