Page 1 of 1

[Solved] LibreOffice Calc freeze panes with Python

PostPosted: Sat Oct 24, 2020 12:08 pm
by chris_muc
What is the proper way to freeze cells at some location (col, row)?

I tried with
Code: Select all   Expand viewCollapse view
XSCRIPTCONTEXT.getDocument().getCurrentController().getActiveSheet().freeze_panes(1,1)


but that does not work

Re: LibreOffice Calc freeze panes with Python

PostPosted: Sat Oct 24, 2020 1:35 pm
by Villeroy
chris_muc wrote:but that does not work
becauses it is guesswork.
For any kind of macro programming and add-in development, the MRI extension is mandatory. [Tutorial] Introduction into object inspection with MRI
Tools>AddIns>Mri (starts with the current document)
Dbl-click the line starting with CurrentController (or getCurrentController on the "Methods" tab)
Dbl-click the line starting with ActiveSheet (or getActiveSheet on the "Methods" tab)
there you find ... nothing
go one step back to the controller and you find the method you need. Dbl-click on it.
--------------------------
Generating documents by code is a no-go.
You can recreate the old freeze command in the "Windows" top menu from where it has been removed by some LibreOffice technocrat.
You can store frozen panes together with your spreadsheet templates, so each new document starts with the wanted window layout and thousands of customized formatting attributes.

Re: LibreOffice Calc freeze panes with Python

PostPosted: Sat Oct 24, 2020 2:06 pm
by chris_muc
Great!

thanks for the very valuable tips.

Now
Code: Select all   Expand viewCollapse view
XSCRIPTCONTEXT.getDocument().getCurrentController().freezeAtPosition(0,1)

works as expected.

Re: [Solved] LibreOffice Calc freeze panes with Python

PostPosted: Mon Oct 26, 2020 11:28 am
by chris_muc
just to follow up on this and for interested if you want to freeze rows not in current sheet but other you need to use Dispatcher.
For that I first recorded a BASIC macro and translated into python:

Code: Select all   Expand viewCollapse view
# coding: utf-8
from __future__ import unicode_literals


import uno
from com.sun.star.beans import PropertyValue

def create_instance(name, with_context=False):
    if with_context:
        instance = SM.createInstanceWithContext(name, CTX)
    else:
        instance = SM.createInstance(name)
    return instance

def call_dispatch(doc, url, args=()):
    frame = doc.getCurrentController().getFrame()
    dispatch = create_instance('com.sun.star.frame.DispatchHelper')
    dispatch.executeDispatch(frame, url, '', 0, args)
    return

CTX = uno.getComponentContext()
SM = CTX.getServiceManager()
doc = XSCRIPTCONTEXT.getDocument()

args = ['']
args[0] = PropertyValue()                 # Default constructor
args[0].Name = "Nr"
args[0].Value = 2

call_dispatch(doc, '.uno:JumpToTable',args)
call_dispatch(doc, '.uno:FreezePanesFirstRow')

# FROM BASIC Macro Record
#sub freeze1row
#dim document   as object
#dim dispatcher as object
#
#rem get access to the document
#document   = ThisComponent.CurrentController.Frame
#dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
#
#dim args1(0) as new com.sun.star.beans.PropertyValue
#args1(0).Name = "Nr"
#args1(0).Value = 2
#
#dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args1())
#
#dispatcher.executeDispatch(document, ".uno:FreezePanesFirstRow", "", 0, Array())
#
#end sub