Macro to Format Page - LibreOffice Calc
-
- Posts: 5
- Joined: Sat Apr 11, 2020 9:45 pm
Macro to Format Page - LibreOffice Calc
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
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
Re: Macro to Format Page - LibreOffice Calc
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.
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.
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.
-
- Posts: 5
- Joined: Sat Apr 11, 2020 9:45 pm
Re: Macro to Format Page - LibreOffice Calc
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
Re: Macro to Format Page - LibreOffice Calc
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.
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.
Re: Macro to Format Page - LibreOffice Calc
This is from Andrew Pitonyak's OpenOffice.org Macros Explained
There are other examples in that document too
http://www.pitonyak.org/oo.php
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
http://www.pitonyak.org/oo.php
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
-
- Posts: 5
- Joined: Sat Apr 11, 2020 9:45 pm
Re: Macro to Format Page - LibreOffice Calc
This is an example (sensitive data is replaced)Zizi64 wrote:Please upload a prepared spreasdsheet file here (without sensitive data).
LibreOffice 6.4 on Windows 10
Re: Macro to Format Page - LibreOffice Calc
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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 5
- Joined: Sat Apr 11, 2020 9:45 pm
Re: Macro to Format Page - LibreOffice Calc
Hi !!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).
There are other examples in that document tooCode: 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
http://www.pitonyak.org/oo.php
This script scale the document with fixed value, but i will try. Tks !!
LibreOffice 6.4 on Windows 10
Re: Macro to Format Page - LibreOffice Calc
Read the code and change the value!ezelorenzatti wrote: This script scale the document with fixed value, but i will try. Tks !!
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: Macro to Format Page - LibreOffice Calc
He wants some dynamic scaling, I'm afraid.RoryOF wrote:Read the code and change the value!ezelorenzatti wrote: This script scale the document with fixed value, but i will try. Tks !!
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice