This question is seeking advice from the experts. I have a LibreOffice spreadsheet that is largely based upon Basic macros, but have set up a Python/Latex subsystem that can produce formatted PDF reports by assembling various Latex templates, with such things as barcodes, qrcodes and other art/images. This is done by reading tex templates into Python Templates, passing to them a data dictionary (that would be loaded with spreadsheet data), writing them as temp files to disk, and then compiling them with Latex. What I need to do is connect the data from the spreadsheet to the subsystem. There is no need to return a value to the spreadsheet, just to produce the pdf report on disk (for now).

Python-Calc - Various scripts to deal with Calc PythonContainers - Python container components PyUNOServer - The PyUNOServer is a script that works as an XML server for OpenOffice.org Calc calcParser - calcParser is a small parser using the native SAX utilities within the OpenOffice.org python OoConfig - OoConfig is a Python extension that. The SDK provides a rich set of examples in different programming languages (Java, Python, C, LibreOffice 6.4 Basic, OLE) to illustrate the use of the API and demonstrate how to benefit from the included word processor, spreadsheet, presentation software. So let’s write our first line of code in Python, within a LibreOffice Calc Document. So after creating a new Calc document, go in the Macro manager (if you have installed the APSO extension: ALT+Shift+F11), then as explained earlier in this lesson you can your program either in calc document or in a library outside the document.


Firstly there is a need to pass the data via Basic to the Python subsystem. Secondly I am concerned about portability, ie I would like to simply pass a file that can be installed easily on another machine (spreadsheet and subsystem), that just works (on a machine that has xelatex of course). Thirdly there are various approaches:
1. Write an extension to LibreOffice calc
2. Launch a separate process by macro
3. Write a wrapper function for Python (is this possible?)
4. Are plugins possible (see 1 above?)
I had hoped that I could use LibreOffice's own built in PDF facilities to produce this output. However the difficulty is the production of barcodes, qrcodes, and other art, latex is much more flexible in this respect.
This initial item has about 15 data elements that need to be passed from the spreadsheet to Python, but I can forsee much more complex and elaborate reports in future. What would be your advice as to the best way to structure the system, and the best way to pass the data?
< Macros‎ Python Guide‎ Calc
English • ‎español • ‎italiano • ‎日本語



Return to Index

Sheets

In this Python Guide, we use the following convention:

  • sheet is a single spreadsheet within a document
  • workbook is a Calc document
Libreoffice


CAUTION:

Copy or import function msgbox from Useful functions

Names

  • Get all sheet names

Count all sheets in a workbook

Python Libreoffice Calc

Iterate over all sheets

Get sheets

  • Get active sheet from current document.
  • Get active sheet by index from current document
  • Get active sheet by name from current document

Activate

Send focus to any sheet

Change name

  • Change name visible to user
  • Change name visible only to code

CAUTION: This change is valid only thru current session. It’s better not to change this property. It's OK to use default names.

Set sheet visibility

  • Set property IsVisible to False to hide a sheet; and set it to True to show it.

Password management

  • Set password
  • Remove password

Check if sheet exists in workbook

Insert a blank new sheet

insertNewByName(NAME, POSITION)


Insert an existing sheet into workbook

insertByName(SHEET, POSITION)

Move position of a sheet within workbook

moveByName(NAME, NEW_POSITION)

Remove sheet from workbook

removeByName(NAME)

Replace with a blank sheet

replaceByName(NAME, SHEET)

CAUTION: Use wisely. This function replaces a sheet NAME with a clean sheet.

Copy sheet

  • Rename a sheet
Libreoffice

copyByName(SOURCE_NAME, NEW_NAME, POSITION)

CAUTION: If new name exists, it will copy the sheet with name SOURCE_NAME_2, and it will not get any error.

Python Open Libreoffice Calc

  • Copy into the same document
  • Copy (or import) from another document

importSheet(SOURCE, SOURCE_NAME, POSITION)

Colorize a tab sheet

CAUTION

Libreoffice Calc Python Script

Copy or import the function get_color from Useful functions

Python Libreoffice Calc

Retrieved from 'https://wiki.documentfoundation.org/index.php?title=Macros/Python_Guide/Calc/Calc_sheets&oldid=299778'