[Solved] LibreOffice Calc freeze panes with Python

Keyboard macros or custom scripts

[Solved] LibreOffice Calc freeze panes with Python

Postby chris_muc » Sat Oct 24, 2020 12:08 pm

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
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
chris_muc
 
Posts: 3
Joined: Sat Oct 24, 2020 12:02 pm

Re: LibreOffice Calc freeze panes with Python

Postby Villeroy » Sat Oct 24, 2020 1:35 pm

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
User avatar
Villeroy
Volunteer
 
Posts: 29891
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: LibreOffice Calc freeze panes with Python

Postby chris_muc » Sat Oct 24, 2020 2:06 pm

Great!

thanks for the very valuable tips.

Now
Code: Select all   Expand viewCollapse view
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

Postby chris_muc » Mon Oct 26, 2020 11:28 am

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
LibreOffice 7.0 @ArchLinux
chris_muc
 
Posts: 3
Joined: Sat Oct 24, 2020 12:02 pm


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 1 guest