Documentation/BASIC Guide/Rows and Columns

BASIC Guide

{{{parttitle}}}

[[{{{PrevPart}}}|< Previous Section ]]

[[{{{NextPart}}}|Next Section >]]

• The Language of Apache OpenOffice Basic

• Overview of a Basic Program
• Working With Variables
• Strings
• Numbers
• Boolean Values
• Date and Time
• Arrays
• Scope and Life Span of Variables
• Constants
• Operators
• Branching
• Loops
• Procedures and Functions
• Error Handling
• Other Instructions

• Runtime Library

• Conversion Functions
• Strings
• Date and Time
• Files and Directories
• Message and Input Boxes
• Other Functions

• Introduction to the API

• Universal Network Objects (UNO)
• Properties and Methods
• Modules, Services and Interfaces
• Tools for Working with UNO
• Overview of Central Interfaces

• Working with Documents

• The StarDesktop
• Styles and Templates

• Text Documents

• The Structure of Text Documents
• Editing Text Documents
• More than Just Text

• Spreadsheet Documents

• The Structure of Spreadsheets
  • Rows and Columns
  • Cells and Ranges
  • Formatting Spreadsheet Documents
• Editing Spreadsheet Documents

• Drawings and Presentations

• The Structure of Drawings
• Editing Drawing Objects
• Presentations

• Charts (Diagrams)

• Using Charts in Spreadsheets
• The Structure of Charts
• Chart Types

• Databases

• SQL: a Query Language
• Types of Database Access
• Data Sources
• Database Access

• Dialogs

• Working With Dialogs
• Properties
• Events
• Dialog Control Elements

• Forms

• Working With Forms
• Control Element Forms
• Database Forms

< Previous Page

Next Page >

Each sheet contains a list of its rows and columns. These are available through the Rows and Columns properties of the spreadsheet object and support the com.sun.star.table.TableColumns and/or com.sun.star.table.TableRows 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 FirstCol and FirstRow object variables.

Dim Doc As Object
Dim Sheet As Object
Dim FirstRow As Object
Dim FirstCol As Object

Doc = ThisComponent
Sheet = Doc.Sheets(0)

FirstCol = Sheet.Columns(0)
FirstRow = Sheet.Rows(0)

The column objects support the com.sun.star.table.TableColumn service that has the following properties:

Width (long)

width of a column in hundredths of a millimeter.

OptimalWidth (Boolean)

sets a column to its optimum width.

IsVisible (Boolean)

displays a column.

IsStartOfNewPage (Boolean)

when printing, creates a page break before a column.

The width of a column is only optimized when the OptimalWidth property is set to True. 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, OptimalWidth is more of a method than a property.

The row objects are based on the com.sun.star.table.TableRow service that has the following properties:

Height (long)

height of the row in 100ths of a millimeter.

OptimalHeight (Boolean)

sets the row to its optimum height.

IsVisible (Boolean)

displays the row.

IsStartOfNewPage (Boolean)

when printing, creates a page break before the row.

If the OptimalHeight property of a row is set to the True, 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 Height 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 = ThisComponent
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

The Rows and Columns lists can be accessed through an index in Apache OpenOffice Basic. The real API call is : Sheet.getColumns.getByIndex(1)

VBA : Unlike in VBA, the first column has the index 0 and not the index 1.

Inserting and Deleting Rows and Columns

The Rows and Columns 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 = ThisComponent
Sheet = Doc.Sheets(0)

Sheet.Columns.insertByIndex(3, 1)
Sheet.Columns.removeByIndex(5, 1)

This example uses the insertByIndex 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 removeByIndex 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 Rows object function in the same way as the methods shown for editing columns using the Columns object.

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