Revision as of 17:45, 16 May 2006

Common spreadsheet operations

See also working_with_documents

Current document

[oobas] Dim oDoc As Object oDoc = ThisComponent

Load existing document

[oobas] Dim oDoc As Object oDoc = StartDesktop.loadComponentFromURL( ConvertToURL( "/home/robert/abc.xls" ), "_blank", _

       0, Array() )

Document type check:

[oobas] Function IsSpreadsheetDocument( oDoc As Object ) As Boolean

  IsSpreadsheetDocument = oDoc.supportsService( "com.sun.star.sheet.SpreadsheetDocument" )

End Function

Working with sheets

Active sheet

[oobas] Function fnActiveSheet ThisComponent.getCurrentController.getActiveSheet end function

Number of sheets

[oobas] thisComponent.getSheets.getCount

Get sheet by index

[oobas] Dim oSheet As Object

oSheet = thisComponent.getSheets.getByIndex( 0 ) ' or in OOo BASIC oSheet = oDoc.Sheets( 0 )

Get sheet by name

[oobas] Dim oSheet As Object

oSheet = thisComponent.getSheets.getByName( "Sheet1" )

Iterate over all sheets

[oobas] 'iterate over all sheets Dim oSheet As Object Dim eSheets As Object eSheets = oDoc.getSheets.createEnumeration

While eSheets.hasMoreElements

       oSheet = eSheets.nextElement()

       ' here you can work your sheet
       MsgBox "Next sheet name is " & oSheet.getName & "."

Wend

Create new sheet

[oobas] ' create new sheet oDoc = thisComponent oSheet = oDoc.createInstance( "com.sun.star.sheet.Spreadsheet" ) oDoc.Sheets.insertByName( "new sheet", oSheet )

Working with rows, columns

Row, Column selection

[oobas] Dim oRow As Object Dim oColumn As Object

' get first row oRow = oSheet.getRows.getByIndex( 0 )

' get column B oColumn = oSheet.getColumns.getByIndex( 1 )

Column Properties

[oobas] ' column width (in 100ths of mm) oColumn.setPropertyValue("Width", 100)

' optimal width oColumn.setPropertyValue("OptimalWidth", True)

' hidden / visible oColumn.setPropertyValue("IsVisible", False)

' page break? oColumn.setPropertyValue("IsStartOfNewPage", False)

Row properties

[oobas] ' row height (in 100ths of mm) oRow.setPropertyValue("Height", 100)

' optimal height oRow.setPropertyValue("OptimalHeight", True)

' hidden / visible oRow.setPropertyValue("IsVisible", False)

' page break? oRow.setPropertyValue("IsStartOfNewPage", False)

Insert, delete row

[oobas] ' insert 2 rows at the 4th position (still counting from 0) oSheet.getRows.insertByIndex( 3, 2 )

' remove 3 lines from the 7th line oSheet.getRows.removeByIndex( 6, 3 )

Insert, delete column

[oobas] ' insert 2 columns into the fourth (D) column oSheet.getColumns.insertByIndex( 3, 2 )

' remove 3 columns from the G column oSheet.getColumns.removeByIndex( 6, 3 )

Working with cells

Cell object

[oobas] Dim oCell As Object

' select A1 (first number = column, second number = row) oCell = oSheet.getCellByPosition( 0, 0 )

'Activecell (error check in case the current selection isn't a cell) on error resume next oCell = ThisComponent.getCurrentSelection

'Get a named cell - in this case the name is "Date" oCell = thisComponent.NamedRanges.getByName("Date").getReferredCells Each cell can contain text, number or formula:

[oobas] ' modify text oCell.String = "This is A1 cell" ' Old form: oCell.setPropertyValue("String", "This is A1 cell" )

' modify number oCell.Value = 100 ' Old form: oCell.setPropertyValue("Value", 100)

'Setting a cell to a fixed date and time (format the cell for date and/or time as desired) oCell.Value = now ' Old form: oCell.setPropertyValue("Value", now)


' modify formula oCell.Formula = "=A2+A3" ' Old form: oCell.setPropertyValue("Formula", "=A2+A3")

Content type detection

[oobas] Select Case oCell.Type

       Case com.sun.star.table.CellContentType.EMPTY
               ' oCell is empty

       Case com.sun.star.table.CellContentType.VALUE
               ' oCell contains number

       Case com.sun.star.table.CellContentType.TEXT
               ' oCell contains string

       Case com.sun.star.table.CellContentType.FORMULA
               ' oCell contains formula

End Select

Cell range

CellRangeAddress is a simple structure with five elements:

• Sheet
• StartColumn
• StartRow
• EndColumn
• EndRow

CellRangeAddress is obviously used to modify more than one cell.

Insert cells

Following example insert cells at B2:C3 range. Any existing values in the specified range are moved downwards.

[oobas] Dim oRange As New com.sun.star.table.CellRangeAddress

' first sheet and B2:C3 range oRange.Sheet = 0 oRange.StartColumn = 1 oRange.StartRow = 1 oRange.EndColumn = 2 oRange.EndRow = 2

oSheet.insertCells( oRange, com.sun.star.sheet.CellInsertMode.DOWN ) Possible values for the second argument are:

• NONE
• DOWN the cells are moved downwards
• RIGHT the cells are moved to the right
• ROWS the rows are moved downwards
• COLUMNS the columns are moved to the right

Remove cells

Following example remove cells at B2:C3 range. Any existing values in the specified range are moved upwards.

[oobas] Dim oRange As New com.sun.star.table.CellRangeAddress

' first sheet and B2:C3 range oRange.Sheet = 0 oRange.StartColumn = 1 oRange.StartRow = 1 oRange.EndColumn = 2 oRange.EndRow = 2

oSheet.removeCells( oRange, com.sun.star.sheet.CellInsertMode.UP ) Possible values for the second argument are:

• NONE
• UP the cells are moved upwards
• LEFT the cells are moved to the left
• ROWS the rows are moved upwards
• COLUMNS the columns are moved to the left

Move, copy cells

CellAddress is a new object, which will helps you identify the cell address and contains three elements:

• Sheet
• Column
• Row

Following example will move the A2:B3 cells to the A1.

[oobas] oRange As New com.sun.star.table.CellRangeAddress

' A2:B3 on the first sheet oRange.Sheet = 0 oRange.StartColumn = 1 oRange.StartRow = 1 oRange.EndColumn = 2 oRange.EndRow = 2

Dim oAddress As New com.sun.star.table.CellAddress

' A1 on the first sheet oAddress.Sheet = 0 oAddress.Column = 0 oAddress.Row = 0

' move the selected range to new position oSheet.moveRange( oAddress, oRange )

' copy the selected range to new position oSheet.copyRange( oAddress, oRange )

Range selection by name

You can create range address with cell names:

[oobas] oRange = oSheet.getCellRangeByName( "B2:C3" ) You can select cells from the range in the same way as from the sheet. Following example returns cell B2:

[oobas] oCell = oRange.getCellByPosition( 0, 0 ) The cell position is counted from the upper left corner of the range, not of the sheet.

Content deletion

[oobas] Dim nFlags As Long

oRange = oSheet.getCellRangeByName( "B2:C3" )

nFlags = com.sun.star.sheet.CellFlags.STRING + _

       com.sun.star.sheet.CellFlags.STYLES

oRange.clearContents( nFlags ) Available flags listed below:

• VALUE numerical value
• DATETIME date or time
• STRING strings
• ANNOTATION comments
• FORMULA formulas
• HARDATTR direct formatting of cells
• STYLES indirect formatting
• OBJECTS drawing objects connected to cells
• EDITATTR formatting that applies to parts of the cells

Formatting

The following examples can be applied to either a Cell or a RangeAddress.

See working_with_styles#number_formats for information on changing number formats.

Background color, shadows

Shadow is defined in the structure com.sun.star.table.ShadowFormat, which has four elements.

• Color shadow color
• Location shadow location
• ShadowWidth shadow width
• IsTransparent shadow transparency

Possible values for Location are:

• NONE
• TOP_LEFT
• TOP_RIGHT
• BOTTOM_LEFT
• BOTTOM_RIGHT

[oobas] ' cell background color oCell.setPropertyValue( "CellBackColor", RGB( 0, 0, 0 ) )

' cell background transparency oCell.setPropertyValue( "IsCellBackgroundTransparent", False )

' cell shadow Dim oShadow As New com.sun.star.table.ShadowFormat

oShadow.Location = com.sun.star.table.ShadowLocation.BOTTOM_RIGHT oShadow.Color = RGB(255, 255, 0) oShadow.ShadowWidth = 50 oShadow.IsTransparent = False

oCell.setPropertyValue( "ShadowFormat", oShadow )

Justification

Horizontal

Each cell contains property HoriJustify with possible values (com.sun.star.table.CellHoriJustify):

• STANDARD
• LEFT
• CENTER
• RIGHT
• BLOCK
• REPEAT

[oobas] ' horizontal justification - right oCell.setPropertyValue( "HoriJustify", com.sun.star.table.CellHoriJustify.RIGHT )

Vertical

Each cell contains property VertJustify with possible values (com.sun.star.table.CellVertJustify):

• STANDARD
• TOP
• CENTER
• BOTTOM

[oobas] ' vertical justification - center oCell.setPropertyValue( "VertJustify", com.sun.star.table.CellVertJustify.CENTER )

Orientation

Each cell contains property Orientation with possible values (com.sun.star.table.CellOrientation):

• STANDARD
• TOPBOTTOM
• BOTTOMTOP
• STACKED

[oobas] ' orientation - STACKED oCell.setPropertyValue( "Orientation", com.sun.star.table.CellOrientation.STACKED )

Wrapping, rotation

Each cell contains property IsTextWrapped (Boolean) and RotateAngle (Long) for better justification.

[oobas] ' wrapped text oCell.setPropertyValue( "IsTextWrapped", True )

' vertical text oCell.setPropertyValue( "RotateAngle", 90 )