Macro to Format Page - LibreOffice Calc

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
ezelorenzatti
Posts: 5
Joined: Sat Apr 11, 2020 9:45 pm

Macro to Format Page - LibreOffice Calc

Post by ezelorenzatti »

Hello

I'm trying to create a macro to make de content fit on the page

This topic explain exactly what i neet, but i not know how automate this.
https://ask.libreoffice.org/en/question ... c-problem/

I try record a macro, but when i run it the process is not executed correctly.

Record https://youtu.be/-4oKgHXAiM8

Run https://youtu.be/Yf-VeJE-_CI

I not sure if it is possible.

If anyone can help me I would be really grateful

Sorry for my bad english
LibreOffice 6.4 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Macro to Format Page - LibreOffice Calc

Post by Zizi64 »

The videos are blurry, I can not recognize the used functions during the usage the macro recorder in your video.

Otherwise the Macro Recorder has a very limited capability: you must WRITE your macros - if you want work efficiently with the macros.

My opinion:
all of the desired properties are available in a custom Page Style or in other feature of the AOO/LO Calc. You not need any macro to scaling the Calc document for the paper output.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
ezelorenzatti
Posts: 5
Joined: Sat Apr 11, 2020 9:45 pm

Re: Macro to Format Page - LibreOffice Calc

Post by ezelorenzatti »

Zizi64 wrote:The videos are blurry, I can not recognize the used functions during the usage the macro recorder in your video.

Otherwise the Macro Recorder has a very limited capability: you must WRITE your macros - if you want work efficiently with the macros.

My opinion:
all of the desired properties are available in a custom Page Style or in other feature of the AOO/LO Calc. You not need any macro to scaling the Calc document for the paper output.

Hi,

I believe that when you saw the videos they hadn't been fully converted.

I need to automate this process because I use Libreoffice to convert XLSX documents to PDF

I am converting using the following command

soffice --headless --nodefault --nofirststartwizard --nolockcheck --nologo --norestore --invisible --convert-to pdf --outdir pathToSaveFile pathToOriginalFile

This is de current generated file But i need
LibreOffice 6.4 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Macro to Format Page - LibreOffice Calc

Post by Zizi64 »

Please upload a prepared spreasdsheet file here (without sensitive data).
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
JeJe
Volunteer
Posts: 2763
Joined: Wed Mar 09, 2016 2:40 pm

Re: Macro to Format Page - LibreOffice Calc

Post by JeJe »

This is from Andrew Pitonyak's OpenOffice.org Macros Explained
13.15.2. Printing Calc documents
To perform special printing functions with a Writer document, a special object method is called. To perform
special printing functions with Calc documents, you must modify the document properties and page-style
properties and then use the standard print() method. For example, it is common for a Calc sheet to be too
large to fit on a single sheet of paper. To scale the sheet to fit on a specified number of pages, set the
ScaleToPages property to contain the number of pages that should contain the sheet. To simply scale the
page based on a percentage, use the PageScale property (see Listing 321).

Code: Select all

Listing 321. Print a spreadsheet at 25 percent; this is very small!
Sub PrintScaledSpreadsheet
Dim s$ 'Style name
Dim oStyle 'The current page style
REM Use the currently active sheet to obtain the page style.
REM In a Calc document, the current controller knows which sheet
REM is active.
s = ThisComponent.CurrentController.getActiveSheet().PageStyle
oStyle = ThisComponent.StyleFamilies.getByName("PageStyles").getByName(s)
REM oStyle.PageScale = 100 Default value is 100 (as in 100%)
REM oStyle.ScaleToPages = 0 Default value is 0, as in don't scale
oStyle.PageScale = 25 'Scale document to 25% (very very very small)
ThisComponent.Print(Array()) 'Print the document
End Sub


There are other examples in that document too

http://www.pitonyak.org/oo.php
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
ezelorenzatti
Posts: 5
Joined: Sat Apr 11, 2020 9:45 pm

Re: Macro to Format Page - LibreOffice Calc

Post by ezelorenzatti »

Zizi64 wrote:Please upload a prepared spreasdsheet file here (without sensitive data).
This is an example (sensitive data is replaced)
Extrato - Auto Posto Stalin - 66848888000134 - Citibank - 1.xlsx
Original XLSX File
(47.21 KiB) Downloaded 201 times
LibreOffice 6.4 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to Format Page - LibreOffice Calc

Post by Villeroy »

Open one of the files, the attached one for instance.
Adjust page layout, column widths etc.
Clear all cell contents.
Save the file as template (menu:File>Templates>Save As Template...).

Store the following macro code under "My Macros".
Adjust the constants on top of the modules:
cInDir is the input directory
cOutDir is the target directory for the PDF files
cTemplate is the name of the template as shown in the GUI when hitting Ctrl+Shift+N

Run the Main routine. It will open each *.xlsx in cInDir with a new empty document from the template, paste all values (text and numbers) from the first sheet of the xlsx into the first sheet of the template, save the new file as pdf, close both files and continue with the next xlsx.
This way you dump raw data from a crazy file format into a perfectly valid Open Document template before generating the PDF print out.

Code: Select all

REM  *****  BASIC  *****

Const cInPath = "/tmp/test/"
Const cOutPath = "/tmp/test/output/"
Const cRegion = "My Templates"
Const cTemplate = "FOO"
Const cPattern = "*.xlsx"
Const cType = "scalc"
Const cFilter = "calc_pdf_Export"
Const cOutSuffix = ".pdf"

Sub Main()
REM calling: getOpenTemplate, getUsedRange
Dim	ca as new com.sun.star.table.CellAddress
dim args(0) As New com.sun.star.beans.PropertyValue
	args(0).Name = "FilterName"
	args(0).Value = cFilter
	sFile = dir(cInPath & cPattern)
	while len(sFile)>0
		sURL = ConvertToURL(cInPath & sFile)
		oNew = getOpenTemplate(sRegion:=cRegion, sType:=cType, sTemplate:=cTemplate
		oDoc = StarDesktop.loadComponentFromURL(sURL, "_blank", 0, Array())
		sh = oDoc.Sheets.getByIndex(0)
		urg = getUsedRange(sh)
		ra = urg.getRangeAddress()
		a() = urg.getDataArray()
		sh = oNew.Sheets.getByIndex(0)
		rg = sh.getCellRangeByPosition(ra.StartColumn, ra.StartRow, ra.EndColumn, ra.EndRow)
		rg.setDataArray(a())
		sOut = ConvertToURL(cOutPath & sFile) & cOutSuffix
		oNew.storeToURL(sOut, args())
		oDoc.close(false)
		oNew.close(false)
		sFile = dir()
	wend
End sub

Function getUsedRange(oSheet)
Dim oCursor
	oCursor = oSheet.createCursor()
	oCursor.gotoStartOfUsedArea(False)
	oCursor.gotoEndOfUsedArea(True)
	getUsedRange = oCursor
End Function

function getOpenTemplate(sRegion$, sType$, sTemplate$)
REM sRegion "My Templates" or any other category name
REM sType: "swriter", "scalc", "simpress", sdraw"
dim p(0 to 2) as new com.sun.star.beans.PropertyValue
p(0).Name = "AsTemplate"
p(0).Value = True
p(1).Name = "TemplateName"
p(1).Value = sTemplate
p(2).Name = "TemplateRegionName"
p(2).Value = sRegion
getOpenTemplate=StarDesktop.loadComponentFromURL("private:factory/"&sType, 0, "_default", p)
end Function
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
ezelorenzatti
Posts: 5
Joined: Sat Apr 11, 2020 9:45 pm

Re: Macro to Format Page - LibreOffice Calc

Post by ezelorenzatti »

JeJe wrote:This is from Andrew Pitonyak's OpenOffice.org Macros Explained
13.15.2. Printing Calc documents
To perform special printing functions with a Writer document, a special object method is called. To perform
special printing functions with Calc documents, you must modify the document properties and page-style
properties and then use the standard print() method. For example, it is common for a Calc sheet to be too
large to fit on a single sheet of paper. To scale the sheet to fit on a specified number of pages, set the
ScaleToPages property to contain the number of pages that should contain the sheet. To simply scale the
page based on a percentage, use the PageScale property (see Listing 321).

Code: Select all

Listing 321. Print a spreadsheet at 25 percent; this is very small!
Sub PrintScaledSpreadsheet
Dim s$ 'Style name
Dim oStyle 'The current page style
REM Use the currently active sheet to obtain the page style.
REM In a Calc document, the current controller knows which sheet
REM is active.
s = ThisComponent.CurrentController.getActiveSheet().PageStyle
oStyle = ThisComponent.StyleFamilies.getByName("PageStyles").getByName(s)
REM oStyle.PageScale = 100 Default value is 100 (as in 100%)
REM oStyle.ScaleToPages = 0 Default value is 0, as in don't scale
oStyle.PageScale = 25 'Scale document to 25% (very very very small)
ThisComponent.Print(Array()) 'Print the document
End Sub


There are other examples in that document too

http://www.pitonyak.org/oo.php
Hi !!

This script scale the document with fixed value, but i will try. Tks !!
LibreOffice 6.4 on Windows 10
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Macro to Format Page - LibreOffice Calc

Post by RoryOF »

ezelorenzatti wrote: This script scale the document with fixed value, but i will try. Tks !!
Read the code and change the value!
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to Format Page - LibreOffice Calc

Post by Villeroy »

RoryOF wrote:
ezelorenzatti wrote: This script scale the document with fixed value, but i will try. Tks !!
Read the code and change the value!
He wants some dynamic scaling, I'm afraid.
This should be easy to do with my template solution where you can calculate the scaling from the range address.
You can set dynamic scaling on the "Sheet" tab of the page format dialog. It can be saved with a template.
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
Post Reply