[Solved] LibreOffice Calc freeze panes with Python

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
chris_muc
Posts: 3
Joined: Sat Oct 24, 2020 12:02 pm

[Solved] LibreOffice Calc freeze panes with Python

Post by chris_muc »

What is the proper way to freeze cells at some location (col, row)?

I tried with

Code: Select all

XSCRIPTCONTEXT.getDocument().getCurrentController().getActiveSheet().freeze_panes(1,1)
but that does not work
Last edited by Hagar Delest on Sat Oct 24, 2020 2:16 pm, edited 1 time in total.
Reason: tagged solved
LibreOffice 7.0 @ArchLinux
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: LibreOffice Calc freeze panes with Python

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
chris_muc
Posts: 3
Joined: Sat Oct 24, 2020 12:02 pm

Re: LibreOffice Calc freeze panes with Python

Post by chris_muc »

Great!

thanks for the very valuable tips.

Now

Code: Select all

XSCRIPTCONTEXT.getDocument().getCurrentController().freezeAtPosition(0,1)
works as expected.
LibreOffice 7.0 @ArchLinux
chris_muc
Posts: 3
Joined: Sat Oct 24, 2020 12:02 pm

Re: [Solved] LibreOffice Calc freeze panes with Python

Post 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

# 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
LibreOffice 7.0 @ArchLinux
Post Reply