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

From Apache OpenOffice Wiki
Jump to: navigation, search
(Cell Properties)
Line 3: Line 3:
 
|ShowPrevPage=block
 
|ShowPrevPage=block
 
|PrevPage=Documentation/BASIC Guide/Spreadsheets
 
|PrevPage=Documentation/BASIC Guide/Spreadsheets
|NextPage=Documentation/BASIC Guide/Editing Spreadsheet Documents
+
|NextPage=Documentation/BASIC Guide/Rows and Columns
 
|spread=block
 
|spread=block
 
}}<!-- {{DISPLAYTITLE:The Structure of Spreadsheet Documents}} -->
 
}}<!-- {{DISPLAYTITLE:The Structure of Spreadsheet Documents}} -->
Line 61: Line 61:
 
The <tt>getByName</tt> and <tt>insertByName</tt> methods are from the <tt>com.sun.star.container.XnameContainer</tt> interface as described in [[Documentation/BASIC Guide/API Intro|Introduction to the API]].
 
The <tt>getByName</tt> and <tt>insertByName</tt> methods are from the <tt>com.sun.star.container.XnameContainer</tt> interface as described in [[Documentation/BASIC Guide/API Intro|Introduction to the API]].
  
== Rows and Columns ==
 
  
Each sheet contains a list of its rows and columns. These are available through the <tt>Rows</tt> and <tt>Columns</tt> properties of the spreadsheet object and support the <tt>com.sun.star.table.TableColumns</tt> and/or <tt>com.sun.star.table.TableRows</tt> services.
 
 
The following example creates two objects that reference the first row and the first column of a sheet and stores the references in the <tt>FirstCol</tt> and <tt>FirstRow</tt> object variables.
 
 
Dim Doc As Object
 
Dim Sheet As Object
 
Dim FirstRow As Object
 
Dim FirstCol As Object
 
 
Doc = StarDesktop.CurrentComponent
 
Sheet = Doc.Sheets(0)
 
 
FirstCol = Sheet.Columns(0)
 
FirstRow = Sheet.Rows(0)
 
 
The column objects support the <tt>com.sun.star.table.TableColumn</tt> service that has the following properties:
 
 
;<tt>Width (long)</tt>:width of a column in hundredths of a millimeter.
 
;<tt>OptimalWidth (Boolean)</tt>:sets a column to its optimum width.
 
;<tt>IsVisible (Boolean)</tt>:displays a column.
 
;<tt>IsStartOfNewPage (Boolean)</tt>:when printing, creates a page break before a column.
 
 
The width of a column is only optimized when the <tt>OptimalWidth</tt> property is set to <tt>True</tt>. If the width of an individual cell is changed, the width of the column that contains the cell is not changed. In terms of functionality, <tt>OptimalWidth</tt> is more of a method than a property.
 
 
The row objects are based on the <tt>com.sun.star.table.RowColumn</tt> service that has the following properties:
 
 
;<tt>Height (long)</tt>:height of the row in 100ths of a millimeter.
 
;<tt>OptimalHeight (Boolean)</tt>:sets the row to its optimum height.
 
;<tt>IsVisible (Boolean)</tt>:displays the row.
 
;<tt>IsStartOfNewPage (Boolean)</tt>:when printing, creates a page break before the row.
 
 
If the <tt>OptimalHeight</tt> property of a row is set to the <tt>True</tt>, the row height changes automatically when the height of a cell in the row is changed. Automatic optimization continues until the row is assigned an absolute height through the <tt>Height</tt> property.
 
 
The following example activates the automatic height optimization for the first five rows in the sheet and makes the second column invisible.
 
 
Dim Doc As Object
 
Dim Sheet As Object
 
Dim Row As Object
 
Dim Col As Object
 
Dim I As Integer
 
 
Doc = StarDesktop.CurrentComponent
 
Sheet = Doc.Sheets(0)
 
 
For I = 0 To 4
 
    Row = Sheet.Rows(I)
 
    Row.OptimalHeight = True
 
Next I
 
 
Col = Sheet.Columns(1)
 
Col.IsVisible = False
 
 
 
{{Documentation/Note|The <tt>Rows</tt> and <tt>Columns</tt> lists can be accessed through an index in {{OOo}} Basic. Unlike in VBA, the first column has the index 0 and not the index 1.}}
 
 
=== Inserting and Deleting Rows and Columns ===
 
 
The <tt>Rows</tt> and <tt>Columns</tt> objects of a sheet can access existing rows and columns as well as insert and delete them.
 
 
Dim Doc As Object
 
Dim Sheet As Object
 
Dim NewColumn As Object
 
 
Doc = StarDesktop.CurrentComponent
 
Sheet = Doc.Sheets(0)
 
 
Sheet.Columns.insertByIndex(3, 1)
 
Sheet.Columns.removeByIndex(5, 1)
 
 
This example uses the <tt>insertByIndex</tt> method to insert a new column into the fourth column position in the sheet (index 3 - numbering starts at 0). The second parameter specifies the number of columns to be inserted (in this example: one).
 
 
The <tt>removeByIndex</tt> method deletes the sixth column (index 5). Again, the second parameter specifies the number of columns that you want to delete.
 
 
The methods for inserting and deleting rows use the <tt>Rows</tt> object function in the same way as the methods shown for editing columns using the <tt>Columns</tt> object.
 
 
== Cells ==
 
 
A spreadsheet consists of a two-dimensional list containing cells. Each cell is defined by its X and Y-position with respect to the top left cell which has the position (0,0).
 
 
The following example creates an object that references the top left cell and inserts a text in the cell:
 
 
Dim Doc As Object
 
Dim Sheet As Object
 
Dim Cell As Object 
 
 
Doc = StarDesktop.CurrentComponent
 
Sheet = Doc.Sheets(0)
 
 
Cell = Sheet.getCellByPosition(0, 0)
 
Cell.String = "Test"
 
 
In addition to numerical coordinates, each cell in a sheet has a name, for example, the top left cell (0,0) of a spreadsheet is called <tt>A1</tt>. The letter <tt>A</tt> stands for the column and the number 1 for the row. It is important that the '''name''' and '''position''' of a cell are not confused because row counting for names begins with 1 but the counting for position begins with 0.
 
 
In {{OOo}}, a table cell can be empty or contain text, numbers, or formulas. The cell type is not determined by the content that is saved in the cell, but rather the object property which was used for its entry. Numbers can be inserted and called up with the <tt>Value</tt> property, text with the <tt>String</tt> property, and formulas with the <tt>Formula</tt> property.
 
 
Dim Doc As Object
 
Dim Sheet As Object
 
Dim Cell As Object 
 
 
Doc = StarDesktop.CurrentComponent
 
Sheet = Doc.Sheets(0)
 
 
Cell = Sheet.getCellByPosition(0, 0)
 
Cell.Value = 100
 
 
Cell = Sheet.getCellByPosition(0, 1)
 
Cell.String = "Test"
 
 
Cell = Sheet.getCellByPosition(0, 2)
 
Cell.Formula = "=A1"
 
 
The example inserts one number, one text, and one formula in the fields A1 to A3.
 
 
{{Documentation/Note|The <tt>Value</tt>, <tt>String</tt>, and <tt>Formula</tt> properties supersede the <tt>PutCell</tt> method for setting the values of a table cell.}}
 
 
{{OOo}} treats cell content that is entered using the <tt>String</tt> property as text, even if the content is a number. Numbers are left-aligned in the cell instead of right-aligned. You should also note the difference between text and numbers when you use formulas:
 
 
Dim Doc As Object
 
Dim Sheet As Object
 
Dim Cell As Object
 
 
Doc = StarDesktop.CurrentComponent
 
Sheet = Doc.Sheets(0)
 
 
Cell = Sheet.getCellByPosition(0, 0)
 
Cell.Value = 100
 
 
Cell = Sheet.getCellByPosition(0, 1)
 
Cell.String = 1000
 
 
Cell = Sheet.getCellByPosition(0, 2)
 
Cell.Formula = "=A1+A2"
 
 
MsgBox Cell.Value
 
 
Although cell A1 contains the value 100 and cell A2 contains the value 1000, the A1+A2 formula returns the value 100. This is because the contents of cell A2 were entered as a string and not as a number.
 
 
To check if the contents of a cell contains a number or a string, use the <tt>Type</tt> property:
 
 
Dim Doc As Object
 
Dim Sheet As Object
 
Dim Cell As Object
 
 
Doc = StarDesktop.CurrentComponent
 
Sheet = Doc.Sheets(0)
 
Cell = Sheet.getCellByPosition(1,1)
 
 
Cell.Value = 1000
 
 
Select Case Cell.Type
 
Case com.sun.star.table.CellContentType.EMPTY
 
    MsgBox "Content: Empty"
 
Case com.sun.star.table.CellContentType.VALUE
 
    MsgBox "Content: Value"
 
Case com.sun.star.table.CellContentType.TEXT
 
    MsgBox "Content: Text"
 
Case com.sun.star.table.CellContentType.FORMULA
 
    MsgBox "Content: Formula"
 
End Select
 
 
The <tt>Cell.Type</tt> property returns a value for the <tt>com.sun.star.table.CellContentType</tt> enumeration which identifies the contents type of a cell. The possible values are:
 
 
;<tt>EMPTY</tt>:no value
 
;<tt>VALUE</tt>:number
 
;<tt>TEXT</tt>:strings
 
;<tt>FORMULA</tt>:formula
 
 
=== Inserting, Deleting, Copying and Moving Cells ===
 
 
In addition to directly modifying cell content, {{OOo}} Calc also provides an interface that allows you to insert, delete, copy, or merge cells. The interface (<tt>com.sun.star.sheet.XRangeMovement</tt>) is available through the spreadsheet object and provides four methods for modifying cell content.
 
 
The <tt>insertCell</tt> method is used to insert cells into a sheet.
 
 
Dim Doc As Object
 
Dim Sheet As Object
 
Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress
 
 
Doc = StarDesktop.CurrentComponent
 
Sheet = Doc.Sheets(0)
 
 
CellRangeAddress.Sheet = 0
 
CellRangeAddress.StartColumn = 1
 
CellRangeAddress.StartRow = 1
 
CellRangeAddress.EndColumn = 2
 
CellRangeAddress.EndRow = 2
 
 
Sheet.insertCells(CellRangeAddress, com.sun.star.sheet.CellInsertMode.DOWN)
 
 
This example inserts a cells range that is two rows by two columns in size into the second column and row (each bear the number 1) of the first sheet (number 0) in the spreadsheet. Any existing values in the specified cell range are moved below the range.
 
 
To define the cell range that you want to insert, use the <tt>com.sun.star.table.CellRangeAddress</tt> structure. The following values are included in this structure:
 
 
;<tt>Sheet (short)</tt>:number of the sheet (numbering begins with 0).
 
;<tt>StartColumn (long)</tt>:first column in the cell range (numbering begins with 0).
 
;<tt>StartRow (long)</tt>:first row in the cell range (numbering begins with 0).
 
;<tt>EndColumn (long)</tt>:final column in the cell range (numbering begins with 0).
 
;<tt>EndRow (long)</tt>:final row in the cell range (numbering begins with 0).
 
 
The completed <tt>CellRangeAddress</tt> structure must be passed as the first parameter to the <tt>insertCells</tt> method. The second parameter of <tt>insertCells</tt> contains a value of the <tt>com.sun.star.sheet.CellInsertMode</tt> enumeration and defines what is to be done with the values that are located in front of the insert position. The <tt>CellInsertMode</tt> enumeration recognizes the following values:
 
 
;<tt>NONE</tt>:the current values remain in their present position.
 
;<tt>DOWN</tt>:the cells at and under the insert position are moved downwards.
 
;<tt>RIGHT</tt>:the cells at and to the right of the insert position are moved to the right.
 
;<tt>ROWS</tt>:the rows after the insert position are moved downwards.
 
;<tt>COLUMNS</tt>:the columns after the insert position are moved to the right.
 
 
The <tt>removeRange</tt> method is the counterpart to the <tt>insertCells</tt> method. This method deletes the range that is defined in the <tt>CellRangeAddress</tt> structure from the sheet.
 
 
Dim Doc As Object
 
Dim Sheet As Object
 
Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress
 
 
Doc = StarDesktop.CurrentComponent
 
Sheet = Doc.Sheets(0)
 
 
CellRangeAddress.Sheet = 0
 
CellRangeAddress.StartColumn = 1
 
CellRangeAddress.StartRow = 1
 
CellRangeAddress.EndColumn = 2
 
CellRangeAddress.EndRow = 2
 
 
Sheet.removeRange(CellRangeAddress, com.sun.star.sheet.CellDeleteMode.UP)
 
 
This example removes the <tt>B2:C3</tt> cell range from the sheet and then shifts the underlying cells up by two rows. The type of removal is defined by one of the following values from the <tt>com.sun.star.sheet.CellDeleteMode</tt> enumeration:
 
 
;<tt>NONE</tt>:the current values remain in their current position.
 
;<tt>UP</tt>:the cells at and below the insert position are moved upwards.
 
;<tt>LEFT</tt>:the cells at and to the right of the insert position are moved to the left.
 
;<tt>ROWS</tt>:the rows after the insert position are moved upwards.
 
;<tt>COLUMNS</tt>:the columns after the insert position are moved to the left.
 
 
The <tt>XRangeMovement</tt> interface provides two additional methods for moving (<tt>moveRange</tt>) or copying (<tt>copyRange</tt>) cell ranges. The following example moves the <tt>B2:C3</tt> range so that the range starts at position <tt>A6</tt>:
 
 
Dim Doc As Object
 
Dim Sheet As Object
 
Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress
 
Dim CellAddress As New com.sun.star.table.CellAddress
 
 
Doc = StarDesktop.CurrentComponent
 
Sheet = Doc.Sheets(0)
 
 
CellRangeAddress.Sheet = 0
 
CellRangeAddress.StartColumn = 1
 
CellRangeAddress.StartRow = 1
 
CellRangeAddress.EndColumn = 2
 
CellRangeAddress.EndRow = 2
 
 
CellAddress.Sheet = 0
 
CellAddress.Column = 0
 
CellAddress.Row = 5
 
 
Sheet.moveRange(CellAddress, CellRangeAddress)
 
 
In addition to the <tt>CellRangeAdress</tt> structure, the <tt>moveRange</tt> method expects a <tt>com.sun.star.table.CellAddress</tt> structure to define the origin of the move's target region. The <tt>CellAddress</tt> method provides the following values:
 
 
;<tt>Sheet (short)</tt>:number of the spreadsheet (numbering begins with 0).
 
;<tt>Column (long)</tt>:number of the addressed column (numbering begins with 0).
 
;<tt>Row (long)</tt>:number of the addressed row (numbering begins with 0).
 
 
The cell contents in the target range are always overwritten by the <tt>moveRange</tt> method. Unlike in the <tt>InsertCells</tt> method , a parameter for performing automatic moves is not provided in the <tt>removeRange</tt> method.
 
 
The <tt>copyRange</tt> method functions in the same way as the <tt>moveRange</tt> method, except that <tt>copyRange</tt> inserts a copy of the cell range instead of moving it.
 
 
 
{{Documentation/Note|In terms of their function, the {{OOo}} Basic <tt>insertCell</tt>, <tt>removeRange</tt>, and <tt>copyRange</tt> methods are comparable with the VBA <tt>Range.Insert</tt>, <tt>Range.Delete</tt> ,and <tt>Range.Copy</tt> methods. Whereas in VBA, the methods are applied to the corresponding <tt>Range</tt> object, in {{OOo}} Basic they are applied to the associated <tt>Sheet</tt> object.}}
 
 
== Formatting ==
 
 
A spreadsheet document provides properties and methods for formatting cells and pages.
 
 
=== Cell Properties ===
 
 
There are numerous options for formatting cells, such as specifying the font type and size for text. Each cell supports the <tt>com.sun.star.style.CharacterProperties</tt> and <tt>com.sun.star.style.ParagraphProperties</tt> services, the main properties of which are described in '''[[Documentation/BASIC Guide/Text Documents|Text Documents]]'''. Special cell formatting is handled by the <tt>com.sun.star.table.CellProperties</tt> service. The main properties of this service are described in the following sections.
 
 
You can apply all of the named properties to individual cells and to cell ranges.
 
 
 
{{Documentation/Note|The <tt>CellProperties</tt> object in the {{OOo}} API is comparable with the <tt>Interior</tt> object from VBA which also defines cell-specific properties.}}
 
 
=== Background Color and Shadows ===
 
 
The <tt>com.sun.star.table.CellProperties</tt> service provides the following properties for defining background colors and shadows:
 
 
;<tt>CellBackColor (Long)</tt>:background color of the table cell.
 
;<tt>IsCellBackgroundTransparent (Boolean)</tt>:sets the background color to transparent.
 
;<tt>ShadowFormat (struct)</tt>:specifies the shadow for cells (structure in accordance with <tt>com.sun.star.table.ShadowFormat</tt> ).
 
 
The <tt>com.sun.star.table.ShadowFormat</tt> structure and the detailed specifications for cell shadows have the following structure:
 
 
;<tt>Location (enum)</tt>:position of shadow (value from the <tt>com.sun.star.table.ShadowLocation</tt> structure).
 
;<tt>ShadowWidth (Short)</tt>:size of shadow in hundredths of a millimeter.
 
;<tt>IsTransparent (Boolean)</tt>:sets the shadow to transparent.
 
;<tt>Color (Long)</tt>:color of shadow.
 
 
The following example writes the number 1000 to the B2 cell, changes the background color to red using the <tt>CellBackColor</tt> property, and then creates a light gray shadow for the cell that is moved 1 mm to the left and down.
 
 
Dim Doc As Object
 
Dim Sheet As Object
 
Dim Cell As Object
 
Dim ShadowFormat As New com.sun.star.table.ShadowFormat
 
 
Doc = StarDesktop.CurrentComponent
 
Sheet = Doc.Sheets(0)
 
Cell = Sheet.getCellByPosition(1,1)
 
 
Cell.Value = 1000
 
 
Cell.CellBackColor = RGB(255, 0, 0)
 
 
ShadowFormat.Location = com.sun.star.table.ShadowLocation.BOTTOM_RIGHT
 
ShadowFormat.ShadowWidth = 100
 
ShadowFormat.Color = RGB(160, 160, 160)
 
 
Cell.ShadowFormat = ShadowFormat
 
 
=== Justification ===
 
 
{{OOo}} provides various functions that allow you to change the justification of a text in a table cell.
 
 
The following properties define the horizontal and vertical justification of a text:
 
 
;<tt>HoriJustify (enum)</tt>:horizontal justification of the text (value from <tt>com.sun.star.table.CellHoriJustify</tt>)
 
;<tt>VertJustify (enum)</tt>:vertical justification of the text (value from <tt>com.sun.star.table.CellVertJustify</tt>)
 
;<tt>Orientation (enum)</tt>:orientation of text (value in accordance with <tt>com.sun.star.table.CellOrientation</tt>)
 
;<tt>IsTextWrapped (Boolean)</tt>:permits automatic line breaks within the cell
 
;<tt>RotateAngle (Long)</tt>:angle of rotation of text in hundredths of a degree
 
 
The following example shows how you can "stack" the contents of a cell so that the individual characters are printed one under another in the top left corner of the cell. The characters are not rotated.
 
 
Dim Doc As Object
 
Dim Sheet As Object
 
Dim Cell As Object
 
 
Doc = StarDesktop.CurrentComponent
 
Sheet = Doc.Sheets(0)
 
Cell = Sheet.getCellByPosition(1,1)
 
 
Cell.Value = 1000
 
 
Cell.HoriJustify = com.sun.star.table.CellHoriJustify.LEFT
 
Cell.VertJustify = com.sun.star.table.CellVertJustify.TOP
 
Cell.Orientation = com.sun.star.table.CellOrientation.STACKED
 
 
=== Number, Date and Text Format ===
 
 
{{OOo}} provides a whole range of predefined date and time formats. Each of these formats has an internal number that is used to assign the format to cells using the <tt>NumberFormat</tt> property. {{OOo}} provides the <tt>queryKey</tt> and <tt>addNew</tt> methods so that you can access existing number formats as well as create your own number formats. The methods are accessed through the following object call:
 
 
NumberFormats = Doc.NumberFormats
 
 
A format is specified using a format string that is structured in a similar way to the format function of {{OOo}} Basic. However there is one major difference: whereas the command format expects English abbreviations and decimal points or characters as thousands separators, the country-specified abbreviations must be used for the structure of a command format for the <tt>NumberFormats</tt> object.
 
 
The following example formats the B2 cell so that numbers are displayed with three decimal places and use commas as a thousands separator.
 
 
Dim Doc As Object
 
Dim Sheet As Object
 
Dim Cell As Object
 
Dim NumberFormats As Object
 
Dim NumberFormatString As String
 
Dim NumberFormatId As Long
 
Dim LocalSettings As New com.sun.star.lang.Locale
 
 
Doc = StarDesktop.CurrentComponent
 
Sheet = Doc.Sheets(0)
 
Cell = Sheet.getCellByPosition(1,1)
 
 
Cell.Value = 23400.3523565
 
 
LocalSettings.Language = "en"
 
LocalSettings.Country = "us"
 
 
NumberFormats = Doc.NumberFormats
 
NumberFormatString = "#,##0.000"
 
 
NumberFormatId = NumberFormats.queryKey(NumberFormatString, LocalSettings, True)
 
If NumberFormatId = -1 Then
 
    NumberFormatId = NumberFormats.addNew(NumberFormatString, LocalSettings)
 
End If
 
 
MsgBox NumberFormatId
 
Cell.NumberFormat = NumberFormatId
 
 
The '''Format Cells''' dialog in {{OOo}} Calc provides an overview of the different formatting options for cells.
 
 
=== Page Properties ===
 
 
Page properties are the formatting options that position document content on a page as well as visual elements that are repeated page after page. These include
 
 
* Paper formats
 
* Page margins
 
* Headers and footers.
 
 
The procedure for defining page formats differs from other forms of formatting. Whereas cell, paragraph, and character element can be directly, page formats can also be defined and indirectly applied using page styles. For example, headers or footers are added to the page style.
 
 
The following sections describe the main formatting options for spreadsheet pages. Many of the styles that are described are also available for text documents. The page properties that are valid for both types of documents are defined in the <tt>com.sun.star.style.PageProperties</tt> service. The page properties that only apply to spreadsheet documents are defined in the <tt>com.sun.star.sheet.TablePageStyle</tt> service.
 
 
 
{{Documentation/Note|The page properties (page margins, borders, and so on) for a Microsoft Office document are defined by means of a <tt>PageSetup</tt> object at the <tt>Worksheet</tt> object (Excel) or <tt>Document</tt> object (Word) level. In {{OOo}}, these properties are defined using a page style which in turn is linked to the associated document.}}
 
 
=== Page Background ===
 
 
The <tt>com.sun.star.style.PageProperties</tt> service defines the following properties of a pages background:
 
 
;<tt>BackColor (long)</tt>:color of background
 
;<tt>BackGraphicURL (String)</tt>:URL of the background graphics that you want to use
 
;<tt>BackGraphicFilter (String)</tt>:name of the filter for interpreting the background graphics
 
;<tt>BackGraphicLocation (Enum)</tt>:position of the background graphics (value according to enumeration)
 
;<tt>BackTransparent (Boolean)</tt>:makes the background transparent
 
 
=== Page Format ===
 
 
The page format is defined using the following properties of the <tt>com.sun.star.style.PageProperties</tt> service:
 
 
;<tt>IsLandscape (Boolean)</tt>:landscape format
 
;<tt>Width (long)</tt>:width of page in hundredths of a millimeter
 
;<tt>Height (long)</tt>:height of page in hundredths of a millimeter
 
;<tt>PrinterPaperTray (String)</tt>:name of the printer paper tray that you want to use
 
 
The following example sets the page size of the "Default" page style to the DIN A5 landscape format (height 14.8 cm, width 21 cm):
 
 
Dim Doc As Object
 
Dim Sheet As Object
 
Dim StyleFamilies As Object
 
Dim PageStyles As Object
 
Dim DefPage As Object
 
 
Doc = StarDesktop.CurrentComponent
 
StyleFamilies = Doc.StyleFamilies
 
PageStyles = StyleFamilies.getByName("PageStyles")
 
DefPage = PageStyles.getByName("Default")
 
 
DefPage.IsLandscape = True
 
DefPage.Width = 21000
 
DefPage.Height = 14800
 
 
=== Page Margin, Border, and Shadow ===
 
 
The <tt>com.sun.star.style.PageProperties</tt> service provides the following properties for adjusting page margins as well as borders and shadows:
 
 
;<tt>LeftMargin (long)</tt>:width of the left hand page margin in hundredths of a millimeter
 
;<tt>RightMargin (long)</tt>:width of the right hand page margin in hundredths of a millimeter.
 
;<tt>TopMargin (long)</tt>:width of the top page margin in hundredths of a millimeter
 
;<tt>BottomMargin (long)</tt>:width of the bottom page margin in hundredths of a millimeter
 
;<tt>LeftBorder (struct)</tt>:specifications for left-hand line of page border <tt>(com.sun.star.table.BorderLine</tt> structure)
 
;<tt>RightBorder (struct)</tt>:specifications for right-hand line of page border (<tt>(com.sun.star.table.BorderLine</tt> structure)
 
;<tt>TopBorder (struct)</tt>:specifications for top line of page border (<tt>(com.sun.star.table.BorderLine</tt> structure)
 
;<tt>BottomBorder (struct)</tt>:specifications for bottom line of page border (<tt>(com.sun.star.table.BorderLine</tt> structure)
 
;<tt>LeftBorderDistance (long)</tt>:distance between left-hand page border and page content in hundredths of a millimeter
 
;<tt>RightBorderDistance (long)</tt>:distance between right-hand page border and page content in hundredths of a millimeter
 
;<tt>TopBorderDistance (long)</tt>:distance between top page border and page content in hundredths of a millimeter
 
;<tt>BottomBorderDistance (long)</tt>:distance between bottom page border and page content in hundredths of a millimeter
 
;<tt>ShadowFormat (struct)</tt>:specifications for shadow of content area of page (<tt>(com.sun.star.table.ShadowFormat</tt> structure)
 
 
The following example sets the left and right-hand borders of the "Default" page style to 1 centimeter.
 
 
Dim Doc As Object
 
Dim Sheet As Object
 
Dim StyleFamilies As Object
 
Dim PageStyles As Object
 
Dim DefPage As Object
 
 
Doc = StarDesktop.CurrentComponent
 
StyleFamilies = Doc.StyleFamilies
 
PageStyles = StyleFamilies.getByName("PageStyles")
 
DefPage = PageStyles.getByName("Default")
 
 
DefPage.LeftMargin = 1000
 
DefPage.RightMargin = 1000
 
 
=== Headers and Footers ===
 
 
The headers and footers of a document form part of the page properties and are defined using the <tt>com.sun.star.style.PageProperties</tt> service. The properties for formatting headers are:
 
 
;<tt>HeaderIsOn (Boolean)</tt>:header is activated
 
;<tt>HeaderLeftMargin (long)</tt>:distance between header and left-hand page margin in hundredths of a millimeter.
 
;<tt>HeaderRightMargin (long)</tt>:distance between header and right-hand page margin in hundredths of a millimeter
 
;<tt>HeaderBodyDistance (long)</tt>:distance between header and main body of document in hundredths of a millimeter
 
;<tt>HeaderHeight (long)</tt>:height of header in hundredths of a millimeter
 
;<tt>HeaderIsDynamicHeight (Boolean)</tt>:height of header is automatically adapted to content
 
;<tt>HeaderLeftBorder (struct)</tt>:details of the left-hand border of frame around header (<tt>com.sun.star.table.BorderLine</tt> structure)
 
;<tt>HeaderRightBorder (struct)</tt>:details of the right-hand border of frame around header (<tt>com.sun.star.table.BorderLine</tt> structure)
 
;<tt>HeaderTopBorder (struct)</tt>:details of the top line of the border around header (<tt>com.sun.star.table.BorderLine</tt> structure)
 
;<tt>HeaderBottomBorder (struct)</tt>:details of the bottom line of the border around header (<tt>com.sun.star.table.BorderLine</tt> structure)
 
;<tt>HeaderLeftBorderDistance (long)</tt>:distance between left-hand border and content of header in hundredths of a millimeter
 
;<tt>HeaderRightBorderDistance (long)</tt>:distance between right-hand border and content of header in hundredths of a millimeter
 
;<tt>HeaderTopBorderDistance (long)</tt>:distance between top border and content of header in hundredths of a millimeter
 
;<tt>HeaderBottomBorderDistance (long)</tt>:distance between bottom border and content of header in hundredths of a millimeter
 
;<tt>HeaderIsShared (Boolean)</tt>:headers on even and odd pages have the same content (refer to <tt>HeaderText</tt> , <tt>HeaderTextLeft</tt>, and <tt>HeaderTextRight</tt> )
 
;<tt>HeaderBackColor (long)</tt>:background color of header
 
;<tt>HeaderBackGraphicURL (String)</tt>:URL of the background graphics that you want to use
 
;<tt>HeaderBackGraphicFilter (String)</tt>:name of the filter for interpreting the background graphics for the header
 
;<tt>HeaderBackGraphicLocation (Enum)</tt>:position of the background graphics for the header (value according to <tt>com.sun.star.style.GraphicLocation</tt> enumeration)
 
;<tt>HeaderBackTransparent (Boolean)</tt>:shows the background of the header as transparent
 
;<tt>HeaderShadowFormat (struct)</tt>:details of shadow of header (<tt>com.sun.star.table.ShadowFormat</tt> structure)
 
 
The properties for formatting footers are:
 
 
;<tt>FooterIsOn (Boolean)</tt>:footer is activated
 
;<tt>FooterLeftMargin (long)</tt>:distance between footer and left-hand page margin in hundredths of a millimeter
 
;<tt>FooterRightMargin (long)</tt>:distance between footer and right-hand page margin in hundredths of a millimeter
 
;<tt>FooterBodyDistance (long)</tt>:distance between footer and main body of document in hundredths of a millimeter
 
;<tt>FooterHeight (long)</tt>:height of footer in hundredths of a millimeter
 
;<tt>FooterIsDynamicHeight (Boolean)</tt>:height of footer is adapted automatically to the content
 
;<tt>FooterLeftBorder (struct)</tt>:details of left-hand line of border around footer (<tt>com.sun.star.table.BorderLine</tt> structure)
 
;<tt>FooterRightBorder (struct)</tt>:details of right-hand line of border around footer (<tt>com.sun.star.table.BorderLine</tt> structure)
 
;<tt>FooterTopBorder (struct)</tt>:details of top line of border around footer (<tt>com.sun.star.table.BorderLine</tt> structure)
 
;<tt>FooterBottomBorder (struct)</tt>:details of bottom line of border around footer (<tt>com.sun.star.table.BorderLine</tt> structure)
 
;<tt>FooterLeftBorderDistance (long)</tt>:distance between left-hand border and content of footer in hundredths of a millimeter
 
;<tt>FooterRightBorderDistance (long)</tt>:distance between right-hand border and content of footer in hundredths of a millimeter
 
;<tt>FooterTopBorderDistance (long)</tt>:distance between top border and content of footer in hundredths of a millimeter
 
;<tt>FooterBottomBorderDistance (long)</tt>:distance between bottom border and content of footer in hundredths of a millimeter
 
;<tt>FooterIsShared (Boolean)</tt>:the footers on the even and odd pages have the same content (refer to <tt>FooterText</tt>, <tt>FooterTextLeft</tt>, and <tt>FooterTextRight</tt> ).
 
;<tt>FooterBackColor (long)</tt>:background color of footer
 
;<tt>FooterBackGraphicURL (String)</tt>:URL of the background graphics that you want to use
 
;<tt>FooterBackGraphicFilter (String)</tt>:name of the filter for interpreting the background graphics for the footer
 
;<tt>FooterBackGraphicLocation (Enum)</tt>:position of background graphics for the footer (value according to <tt>com.sun.star.style.GraphicLocation</tt> enumeration)
 
;<tt>FooterBackTransparent (Boolean) </tt>:shows the background of the footer as transparent
 
;<tt>FooterShadowFormat (struct)</tt>:details of shadow of footer (<tt>com.sun.star.table.ShadowFormat</tt> structure)
 
 
=== Changing the Text of Headers and Footers ===
 
 
The content of headers and footers in a spreadsheet is accessed through the following properties:
 
 
;<tt>LeftPageHeaderContent (Object)</tt>:content of headers for even pages (<tt>com.sun.star.sheet.HeaderFooterContent</tt> service)
 
;<tt>RightPageHeaderContent (Object)</tt>:content of headers for odd pages (<tt>com.sun.star.sheet.HeaderFooterContent</tt> service)
 
;<tt>LeftPageFooterContent (Object)</tt>:content of footers for even pages (<tt>com.sun.star.sheet.HeaderFooterContent</tt> service)
 
;<tt>RightPageFooterContent (Object)</tt>:content of footers for odd pages (<tt>com.sun.star.sheet.HeaderFooterContent</tt> service)
 
 
If you do not need to distinguish between headers or footers for odd and even pages (the <tt>FooterIsShared</tt> property is <tt>False</tt>), then set the properties for headers and footers on odd pages.
 
 
All the named objects return an object that supports the <tt>com.sun.star.sheet.HeaderFooterContent</tt> service. By means of the (non-genuine) properties <tt>LeftText</tt>, <tt>CenterText</tt>, and <tt>RightText</tt>, this service provides three text elements for the headers and footers of {{OOo}} Calc.
 
 
The following example writes the "Just a Test." value in the left-hand text field of the header from the "Default" template.
 
 
Dim Doc As Object
 
Dim Sheet As Object
 
Dim StyleFamilies As Object
 
Dim PageStyles As Object
 
Dim DefPage As Object
 
Dim HText As Object
 
Dim HContent As Object
 
Doc = StarDesktop.CurrentComponent
 
StyleFamilies = Doc.StyleFamilies
 
PageStyles = StyleFamilies.getByName("PageStyles")
 
DefPage = PageStyles.getByName("Default")
 
 
DefPage.HeaderIsOn = True
 
HContent = DefPage.RightPageHeaderContent
 
HText = HContent.LeftText
 
HText.String = "Just a Test."
 
DefPage.RightPageHeaderContent = HContent
 
 
Note the last line in the example: Once the text is changed, the <tt>TextContent</tt> object must be assigned to the header again so that the change is effective.
 
 
Another mechanism for changing the text of headers and footers is available for text documents ({{OOo}} Writer) because these consist of a single block of text. The following properties are defined in the <tt>com.sun.star.style.PageProperties</tt> service:
 
 
;<tt>HeaderText (Object) </tt>:text object with content of the header (<tt>com.sun.star.text.XText</tt> service)
 
;<tt>HeaderTextLeft (Object)</tt>:text object with content of headers on left-hand pages (<tt>com.sun.star.text.XText</tt> service)
 
;<tt>HeaderTextRight (Object)</tt>:text object with content of headers on right-hand pages (<tt>com.sun.star.text.XText</tt> service)
 
;<tt>FooterText (Object)</tt>:text object with content of the footer (<tt>com.sun.star.text.XText</tt> service)
 
;<tt>FooterTextLeft (Object)</tt>:text object with content of footers on left-hand pages (<tt>com.sun.star.text.XText</tt> service)
 
;<tt>FooterTextRight (Object)</tt>:text object with content of footers on right-hand pages (<tt>com.sun.star.text.XText</tt> service)
 
 
The following example creates a header in the "Default" page style for text documents and adds the text "Just a Test" to the header.
 
 
Dim Doc As Object
 
Dim Sheet As Object
 
Dim StyleFamilies As Object
 
Dim PageStyles As Object
 
Dim DefPage As Object
 
Dim HText As Object
 
 
Doc = StarDesktop.CurrentComponent
 
StyleFamilies = Doc.StyleFamilies
 
PageStyles = StyleFamilies.getByName("PageStyles")
 
DefPage = PageStyles.getByName("Default")
 
 
DefPage.HeaderIsOn = True
 
HText = DefPage.HeaderText
 
 
HText.String = "Just a Test."
 
 
In this instance, access is provided directly through the <tt>HeaderText</tt> property of the page style rather than the <tt>HeaderFooterContent</tt> object.
 
 
=== Centering (Spreadsheets Only) ===
 
 
The <tt>com.sun.star.sheet.TablePageStyle</tt> service is only used in {{OOo}} Calc page styles and allows cell ranges that you want to printed to be centered on the page. This service provides the following properties:
 
 
;<tt>CenterHorizontally (Boolean)</tt>:table content is centered horizontally
 
;<tt>CenterVertically (Boolean)</tt>:table content is centered vertically
 
 
=== Definition of Elements to be Printed (Spreadsheets Only) ===
 
 
When you format sheets, you can define whether page elements are visible. For this purpose, the <tt>com.sun.star.sheet.TablePageStyle</tt> service provides the following properties:
 
 
;<tt>PrintAnnotations (Boolean)</tt>:prints cell comments
 
;<tt>PrintGrid (Boolean)</tt>:prints the cell gridlines
 
;<tt>PrintHeaders (Boolean)</tt>:prints the row and column headings
 
;<tt>PrintCharts (Boolean)</tt>:prints charts contained in a sheet
 
;<tt>PrintObjects (Boolean)</tt>:prints embedded objects
 
;<tt>PrintDrawing (Boolean)</tt>:prints draw objects
 
;<tt>PrintDownFirst (Boolean)</tt>:if the contents of a sheet extend across several pages, they are first printed in vertically descending order, and then down the right-hand side.
 
;<tt>PrintFormulas (Boolean)</tt>:prints the formulas instead of the calculated values
 
;<tt>PrintZeroValues (Boolean)</tt>:prints the zero values
 
  
  
 
{{PDL1}}
 
{{PDL1}}

Revision as of 08:52, 5 October 2007


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.

Template:Documentation/Note

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 = StarDesktop.CurrentComponent
Sheet  = Doc. Sheets (0)

Example 2: access by means of the name

Dim Doc As Object
Dim Sheet As Object

Doc = StarDesktop.CurrentComponent
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)
the spreadsheet is visible.
PageStyle (String)
name of the page template for the spreadsheet.

Creating, Deleting and Renaming Sheets

The Sheets list for a spreadsheet document is also used to create, delete, and rename 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 = StarDesktop.CurrentComponent
Sheet = Doc.Sheets(0)

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 getByName and insertByName methods are from the com.sun.star.container.XnameContainer interface as described in Introduction to the API.



Content on this page is licensed under the Public Documentation License (PDL).
Personal tools