Difference between revisions of "Documentation/BASIC Guide/Structure of Spreadsheets"

From Apache OpenOffice Wiki
Jump to: navigation, search
m (Spreadsheets)
(5 intermediate revisions by one other user not shown)
Line 10: Line 10:
 
The document object of a spreadsheet is based on the <idl>com.sun.star.sheet.SpreadsheetDocument</idl> 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.
 
The document object of a spreadsheet is based on the <idl>com.sun.star.sheet.SpreadsheetDocument</idl> 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.
  
{{Documentation/Note|Different terminology for spreadsheets and their content is used in VBA and {{OOo}} Basic. Whereas the document object in VBA is called a Workbook and its individual pages Worksheets, they are called SpreadsheetDocument and Sheet in {{OOo}} Basic.}}
+
{{Documentation/VBAnote|Different terminology for spreadsheets and their content is used in VBA and {{OOo}} Basic. Whereas the document object in VBA is called a Workbook and its individual pages Worksheets, they are called SpreadsheetDocument and Sheet in {{OOo}} Basic.}}
  
 
== Spreadsheets ==
 
== Spreadsheets ==
Line 28: Line 28:
 
</source>
 
</source>
  
{{Documentation/Note|<tt>ThisComponent</tt> returns the last previously active document.}}
+
{{Note|<tt>ThisComponent</tt> returns the currently active document.
 +
 
 +
The expression <code>Doc.Sheets(0)</code> is a Basic simplification of the API call : <code>Doc.getSheets.getByIndex(0)</code>}}
  
 
'''Example 2: access by means of the name'''
 
'''Example 2: access by means of the name'''
Line 44: Line 46:
 
The <tt>Sheet</tt> object that is obtained by the <tt>getByName</tt> method supports the <idl>com.sun.star.sheet.Spreadsheet</idl> service. In addition to providing several interfaces for editing the content, this service provides the following properties:  
 
The <tt>Sheet</tt> object that is obtained by the <tt>getByName</tt> method supports the <idl>com.sun.star.sheet.Spreadsheet</idl> service. In addition to providing several interfaces for editing the content, this service provides the following properties:  
  
;<tt>IsVisible (Boolean)</tt>:the spreadsheet is visible.
+
;<tt>IsVisible (Boolean)</tt>:value True if the spreadsheet is visible.
 
;<tt>PageStyle (String)</tt>:name of the page template for the spreadsheet.
 
;<tt>PageStyle (String)</tt>:name of the page template for the spreadsheet.
  
=== Creating, Deleting and Renaming Sheets ===
+
=== Renaming Sheets ===
  
The <tt>Sheets</tt> list for a <tt>spreadsheet</tt> document is also used to create, delete, and rename individual sheets. The following example uses the <tt>hasByName</tt> method to check if a sheet called '''MySheet''' exists. If it does, the method determines a corresponding object reference by using the <tt>getByName</tt> method and then saves the reference in a variable in <tt>Sheet</tt>. If the corresponding sheet does not exist, it is created by the <tt>createInstance</tt> call and inserted in the spreadsheet document by the <tt>insertByName</tt> method.
+
A sheet provides methods <code>getName</code> and <code>setName</code> to read and modify its name. Basic can handle both methods like a property <code>Name</code>. Here we rename the first sheet of the spreadsheet document.
  
 
<source lang="oobas">
 
<source lang="oobas">
 
Dim Doc As Object
 
Dim Doc As Object
 
Dim Sheet As Object
 
Dim Sheet As Object
 
+
 
Doc = ThisComponent
 
Doc = ThisComponent
 
Sheet = Doc.Sheets(0)
 
Sheet = Doc.Sheets(0)
 +
Sheet.Name = "First"
 +
</source>
 +
 +
 +
=== Creating and Deleting Sheets ===
 +
 +
The <tt>Sheets</tt> container of a <tt>spreadsheet</tt> document is also used to create and delete individual sheets. The following example uses the <tt>hasByName</tt> method to check if a sheet called '''MySheet''' exists. If it does, the method determines a corresponding object reference by using the <tt>getByName</tt> method and then saves the reference in a variable in <tt>Sheet</tt>. If the corresponding sheet does not exist, it is created by the <tt>createInstance</tt> call and inserted in the spreadsheet document by the <tt>insertByName</tt> method.
 +
 +
<source lang="oobas">
 +
Dim Doc As Object
 +
Dim Sheet As Object
 +
 +
Doc = ThisComponent
  
 
If Doc.Sheets.hasByName("MySheet") Then
 
If Doc.Sheets.hasByName("MySheet") Then
Line 66: Line 81:
 
</source>
 
</source>
  
The <tt>getByName</tt> and <tt>insertByName</tt> methods are from the <idl>com.sun.star.container.XNameContainer</idl> interface as described in [[Documentation/BASIC Guide/API Intro|Introduction to the API]].
+
The <tt>hasByName</tt>, <tt>getByName</tt> and <tt>insertByName</tt> methods are obtained from the <idl>com.sun.star.container.XNameContainer</idl> interface as described in [[Documentation/BASIC Guide/API Intro|Introduction to the API]].
 +
 
 +
The interface  <idl>com.sun.star.sheet.Spreadsheets</idl> provides a better method to create a new sheet: <code>insertNewByName</code>.
 +
It inserts a new sheet with the name specified by the first argument, at the position specified by the second argument.
 +
 
 +
<source lang="oobas">
 +
Dim Doc As Object
 +
 +
Doc = ThisComponent
 +
 
 +
Doc.Sheets.insertNewByName("OtherSheet", 2)
 +
</source>
 +
 
 +
The same interface provides methods <code>moveByName</code> and <code>copyByName</code>.
 +
 
 +
The <idl>com.sun.star.container.XNameContainer</idl> interface provides a method to remove a sheet of a given name:
 +
 
 +
<source lang="oobas">
 +
Dim Doc As Object
 +
 +
Doc = ThisComponent
 +
 
 +
Doc.Sheets.removeByName("OtherSheet")
 +
</source>
  
 
   
 
   
 
{{InterWiki Languages BasicGuide|articletitle=Documentation/BASIC Guide/Structure of Spreadsheets}}
 
{{InterWiki Languages BasicGuide|articletitle=Documentation/BASIC Guide/Structure of Spreadsheets}}
 
{{PDL1}}
 
{{PDL1}}

Revision as of 20:54, 2 July 2018


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.

Documentation note.png VBA : Different terminology for spreadsheets and their content is used in VBA and Apache OpenOffice Basic. Whereas the document object in VBA is called a Workbook and its individual pages Worksheets, they are called SpreadsheetDocument and Sheet in Apache OpenOffice Basic.


Spreadsheets

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)
Documentation note.png ThisComponent returns the currently active document.

The expression Doc.Sheets(0) is a Basic simplification of the API call : Doc.getSheets.getByIndex(0)

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.

Renaming Sheets

A sheet provides methods getName and 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 hasByName, getByName and insertByName methods are obtained from the com.sun.star.container.XNameContainer interface as described in Introduction to the API.

The interface com.sun.star.sheet.Spreadsheets provides a better method to create a new sheet: insertNewByName. 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 moveByName and copyByName.

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).
Personal tools