Any spread sheets document typically associated with an OpenDocument Spreadsheet/Office Open XML Workbook/Microsoft Excel file, using LibreOffice
Topics
About: UNO (Universal Network Objects)
About: LibreOffice
About: Apache OpenOffice
About: the Java programming language
About: C++
About: Microsoft .NET Framework
About: the Python programming language
About: LibreOffice Basic
About: Apache OpenOffice Basic
About: BeanShell
About: JavaScript
The table of contents of this article
- Starting Context
- Target Context
- Orientation
- Main Body
- 1: Undersanding the Mechanism and Its Implications
- 2: Getting the Page Styles of the Spread Sheets Document
- 3: Getting the Page Style of the Intended Sheet
- 4: Setting the Page Size to the Page Style
- 5: Creating a New Page Style and Assigning It to the Sheet
- 6: Executing a Sample Program
- 7: The Conclusion and Beyond
Starting Context
- The reader has basic knowledge on one of the UNO-supported programming languages (Java, C++, the Microsoft .NET Framework programming languages (C# and Visual Basic.NET, in most cases), Python, LibreOffice or Apache OpenOffice Basic, BeanShell, and JavaScript).
- The reader has knowledge on what UNO is and how it is related to LibreOffice or Apache OpenOffice.
- The reader has knowledge on basic elements of UNO and the terminology used for them in this series.
- The reader has knowledge on how to get a UNO objects context to any LibreOffice or Apache OpenOffice instance (there are some articles on how to do it for any external console Java, C++, C#, and Python programs; there is an article on how to do it for any external GUI Java program; some articles on how to do it for any external GUI C++, C#, and Python programs will come later).
- The reader has knowledge on how to access the intended document and how to store the document into a file, using UNO (the article on how to use LibreOffice or Apache OpenOffice as a files converter (the concept, a Java implementation, a C++ implementation, a C# implementation, a Python implementation, and a LibreOffice or Apache OpenOffice Basic implementation) contains a major part of the information).
Target Context
- The reader will know how to set the size of any page of any spread sheets document that is loaded in a LibreOffice or Apache OpenOffice instance, from his or her program.
Here are Hypothesizer 7, Objector 39A, and Objector 39B in front of a computer.
Orientation
Hypothesizer 7
In this article, we will know how to set the size of any page of any spread sheets document that is loaded in a LibreOffice or Apache OpenOffice instance, from our program.
Please note that I said "spread sheets document", not 'spread sheets document file'.
Objector 39A
I note that, but don't note your intension.
Hypothesizer 7
Sir, any file is a sequence of bits that is stored in a storage (for example, a hard disk); by 'document', I mean an object that represents a chunk of documentary information. . . . When you do 'File' -> 'New' -> 'Spreadsheet' in the LibreOffice menu, a new document is created, but it is not associated with any file at least yet; when you load a document file of a certain format into a LibreOffice instance, it becomes a document in the LibreOffice instance.
Objector 39A
So, you will talk about handling such an object loaded in a LibreOffice instance, not about directly writing a file?
Hypothesizer 7
Yes, I will, but my point is that the file format does not matter as long as the file can be loaded into a LibreOffice instance as a spread sheets document, because such documents are generic.
Objector 39A
"generic"?
Hypothesizer 7
Any of such documents is not of any specific file format: the file format is only about how the document is serialized when the document is stored in a storage.
Objector 39A
So, I can set the page size in the same way regardless of whether the document has been load from a Microsoft Excel binary file, an Office Open XML Workbook file, or whatever . . .
Hypothesizer 7
Yes. And you can store the document in your desired format, for example, the PDF.
Objector 39A
Ah.
Hypothesizer 7
Note that although I said "set the size of any page", all the pages in any spread sheet inevitably share the same size.
Objector 39B
So, you lied . . .
Hypothesizer 7
Did I, madam? I do not think so: you can really set the size of any page, although you cannot set the size independently from the other pages of the sheet.
Main Body
1: Undersanding the Mechanism and Its Implications
Hypothesizer 7
In fact, the page size is not set in the page itself or even the sheet itself, but in the page style that is assigned to the sheet.
Objector 39A
You sound like just playing a dull word game.
Hypothesizer 7
No, it is of a practical importance: the spread sheets document has some page styles, and one of them is assigned to each sheet, which means that a page style may be assigned to some multiple sheets.
Objector 39A
Ah, that means that I can't change the page sizes independently.
Hypothesizer 7
If you want to change the sizes of the pages of only a single sheet, you will have to create and setup a new page style and assign the new page style to the single sheet.
Objector 39A
Bothersome . . ., anyway, you will discuss how to do so, won't you?
Hypothesizer 7
Yes, in a later section.
2: Getting the Page Styles of the Spread Sheets Document
Hypothesizer 7
Let us get the page styles contained in the spread sheets document.
In fact, this is a piece of Java code that does that, where 'a_unoDocumentInXComponent' is the document UNO object.
@Java Source Code
import com.sun.star.container.XNameContainer;import com.sun.star.frame.XModel;import com.sun.star.lang.XComponent;import com.sun.star.sheet.XSpreadsheetDocument;import com.sun.star.style.XStyleFamiliesSupplier;import com.sun.star.uno.AnyConverter;import com.sun.star.uno.UnoRuntime;XComponent a_unoDocumentInXComponent;~XSpreadsheetDocument l_unoSpreadSheetsDocumentInXSpreadsheetDocument = (XSpreadsheetDocument) UnoRuntime.queryInterface (XSpreadsheetDocument.class, a_unoDocumentInXComponent);if (l_unoSpreadSheetsDocumentInXSpreadsheetDocument == null) {// The document is not any spread sheets document.}else {try {XModel l_unoSpreadSheetsDocumentInXModel = (XModel) UnoRuntime.queryInterface (XModel.class, l_unoSpreadSheetsDocumentInXSpreadsheetDocument);XStyleFamiliesSupplier l_unoSpreadSheetsDocumentInXStyleFamiliesSupplier = (XStyleFamiliesSupplier) UnoRuntime.queryInterface (XStyleFamiliesSupplier.class, l_unoSpreadSheetsDocumentInXSpreadsheetDocument);XNameContainer l_pageStylesInXNameContainer = (XNameContainer) AnyConverter.toObject (XNameContainer.class , l_unoSpreadSheetsDocumentInXStyleFamiliesSupplier.getStyleFamilies ().getByName ("PageStyles"));}}
Objector 39A
You have suddenly brought in "the document UNO object" without any explanation . . .
Hypothesizer 7
Actually, I have not: it is included in 'Starting Context', more specifically, here in a previous article.
Objector 39A
. . . Actually, I am not interested in all the page styles, but in the page style of a specific sheet.
Hypothesizer 7
We will know how to pick it up from all the page styles, in the next section.
Objector 39A
. . .
Hypothesizer 7
Although we can write the code in any UNO-supported programming language, I do not show it here.
Objector 39B
Why?
Hypothesizer 7
Because I have prepared only Java code for this article (probably, I will show code for some other programming languages later). Meanwhile, I hope that you will be able to derive your programming language version from the Java version.
Objector 39B
. . .
3: Getting the Page Style of the Intended Sheet
Hypothesizer 7
If you a priori know the name of the page style of the intended sheet, you can get the page style, like this, where 'l_pageStyleName' is the name.
@Java Source Code
import com.sun.star.beans.XPropertySet;import com.sun.star.style.XStyle;String l_pageStyleName = null;XPropertySet l_pageStyleInXPropertySet = null;~l_pageStyleInXPropertySet = UnoRuntime.queryInterface (XPropertySet.class, AnyConverter.toObject (XStyle.class, l_pageStylesInXNameContainer.getByName (l_pageStyleName)));
Objector 39A
Ah, for example, it is 'Default' in many cases for OpenDocument Spreadsheet files.
But if not?
Hypothesizer 7
You can get the name of the page style of the sheet of a specific sheet index, like this, where 'l_sheetIndex' is the sheet index (starting from '0').
@Java Source Code
import com.sun.star.container.XIndexAccess;import com.sun.star.sheet.XSpreadsheets2;int l_sheetIndex = 0;~XSpreadsheets2 l_unoSpreadSheetsInXSpreadsheets2 = (XSpreadsheets2) UnoRuntime.queryInterface (XSpreadsheets2.class, l_unoSpreadSheetsDocumentInXSpreadsheetDocument.getSheets ());XIndexAccess l_unoSpreadSheetsInXIndexAccess = (XIndexAccess) UnoRuntime.queryInterface (XIndexAccess.class, l_unoSpreadSheetsInXSpreadsheets2);int l_numberOfSheets = l_unoSpreadSheetsInXIndexAccess.getCount ();XPropertySet l_unoSpreadSheetInXPropertySet = (XPropertySet) UnoRuntime.queryInterface (XPropertySet.class, l_unoSpreadSheetsInXIndexAccess.getByIndex (l_sheetIndex));l_pageStyleName = (String) l_unoSpreadSheetInXPropertySet.getPropertyValue ("PageStyle");
Objector 39A
. . . Ah, so I access the sheet and get the page style name.
4: Setting the Page Size to the Page Style
Hypothesizer 7
The page size can be set to the page style, like this, where the size is the A4 portrait size, where the numbers are in hundredths of a millimeter.
@Java Source Code
import com.sun.star.awt.Size;Size i_pagesSize = new Size (21000, 29700);~l_pageStyleInXPropertySet.setPropertyValue ("Size", i_pagesSize);l_pageStyleInXPropertySet.setPropertyValue ("IsLandscape", Boolean.valueOf (false));
Objector 39A
Which means . .
Hypothesizer 7
'29700' means '297.00' mm.
Objector 39A
Of course.
5: Creating a New Page Style and Assigning It to the Sheet
Hypothesizer 7
If a new page style is required (because we do not want to change the sizes of the other sheets that share the page style), it can be created with a UNO dispatch command, '.uno:StyleNewByExample'.
Objector 39B
. . . "UNO dispatch command" is not included in 'Starting Context', I believe.
Hypothesizer 7
You are right; how to execute any UNO dispatch command in a programming language is described in one of these articles (in Java, in C++, in C#, in Python, and in LibreOffice or Apache OpenOffice Basic).
Objector 39B
. . . All right.
Hypothesizer 7
The new page style can be assigned to the sheet, like this, where 'l_newPageStyleName' is the name of the new page style.
@Java Source Code
String l_newPageStyleName;~l_unoSpreadSheetInXPropertySet.setPropertyValue ("PageStyle", l_newPageStyleName);
6: Executing a Sample Program
Hypothesizer 7
In fact, I have created a document tailor that sets a size to all the pages of the spread sheets document.
Objector 39A
"document tailor?"
Hypothesizer 7
Please refer to a subsection of a previous article.
Objector 39A
. . .
Hypothesizer 7
If you want to do a more sophisticated tailoring, you can tweak the document tailor.
Objector 39A
Where is your tailor?
Hypothesizer 7
My tailor class is 'theBiasPlanet.unoUtilities.documentsHandling.UnoSpreadSheetsDocumentSetPageStylesTailor' in the 'unoUtilitiesToBeDisclosed' project contained in this ZIP file.
In fact, the ZIP file is of the file conversion sample programs introduced in a previous artcile (the concept, a Java implementation, a C++ implementation, a C# implementation, and a Python implementation), and how to build the project and how to execute the programs are described in the previous article.
The specifications of the file conversions order CSV file for using any document tailor is described here in the previous article.
7: The Conclusion and Beyond
Hypothesizer 7
Now, we know how to set the size of any page of any spread sheets document that is loaded in a LibreOffice or Apache OpenOffice instance, from our program.
The spread sheets document may have been loaded from a OpenDocument Spreadsheet file, a Microsoft Excel file, or whatever file that the Calc program can load, and the document can be stored into any file that the Calc program can store into, for example, a PDF file.
We will see how to set the other properties of any page style (of any word processor document or of any spread sheets document) from our program, in some future articles.
