The Structure of Spreadsheet Documents
The document object of a spreadsheet is based on the com.sun.star.sheet.SpreadsheetDocument service. Each of these documents may contain several spreadsheets. In this guide, a table-based document or spreadsheet document is the entire document, whereas a spreadsheet (or sheet for short) is a sheet (table) in the document.
You can access the individual sheets of a spreadsheet document through the Sheets list.
The following examples show you how to access a sheet either through its number or its name.
Example 1: access by means of the number (numbering begins with 0)
Dim Doc As Object Dim Sheet As Object Doc = ThisComponent Sheet = Doc.Sheets (0)
|ThisComponent returns the currently active document.
Example 2: access by means of the name
Dim Doc As Object Dim Sheet As Object Doc = ThisComponent Sheet = Doc.Sheets.getByName("Sheet 1")
In the first example, the sheet is accessed by its number (counting begins at 0). In the second example, the sheet is accessed by its name and the getByName method.
The Sheet object that is obtained by the getByName method supports the com.sun.star.sheet.Spreadsheet service. In addition to providing several interfaces for editing the content, this service provides the following properties:
- IsVisible (Boolean)
- value True if the spreadsheet is visible.
- PageStyle (String)
- name of the page template for the spreadsheet.
A sheet provides methods
setName to read and modify its name. Basic can handle both methods like a property
Name. Here we rename the first sheet of the spreadsheet document.
Dim Doc As Object Dim Sheet As Object Doc = ThisComponent Sheet = Doc.Sheets(0) Sheet.Name = "First"
Creating and Deleting Sheets
The Sheets container of a spreadsheet document is also used to create and delete individual sheets. The following example uses the hasByName method to check if a sheet called MySheet exists. If it does, the method determines a corresponding object reference by using the getByName method and then saves the reference in a variable in Sheet. If the corresponding sheet does not exist, it is created by the createInstance call and inserted in the spreadsheet document by the insertByName method.
Dim Doc As Object Dim Sheet As Object Doc = ThisComponent If Doc.Sheets.hasByName("MySheet") Then Sheet = Doc.Sheets.getByName("MySheet") Else Sheet = Doc.createInstance("com.sun.star.sheet.Spreadsheet") Doc.Sheets.insertByName("MySheet", Sheet) End If
The interface com.sun.star.sheet.Spreadsheets provides a better method to create a new sheet:
It inserts a new sheet with the name specified by the first argument, at the position specified by the second argument.
Dim Doc As Object Doc = ThisComponent Doc.Sheets.insertNewByName("OtherSheet", 2)
The same interface provides methods
The com.sun.star.container.XNameContainer interface provides a method to remove a sheet of a given name:
Dim Doc As Object Doc = ThisComponent Doc.Sheets.removeByName("OtherSheet")
|Content on this page is licensed under the Public Documentation License (PDL).|