Spreadsheet common
Contents
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 )
Example: Setting currently selected cells to current date and time
[oobas]
sub subSetDate
oDoc = thisComponent
oSelection = oDoc.getCurrentSelection
nFormat = fnGetNumberFormatId(oDoc,"DD/MM/YY")
if HasUnoInterfaces(oSelection, "com.sun.star.lang.XServiceInfo") then
if oSelection.supportsService("com.sun.star.sheet.SheetCellRanges") then
'More than one range selected
subSetRanges(oSelection, vValue, nFormat)
elseif oSelection.supportsService("com.sun.star.table.CellRange") then
'Only one range but more than one cell
subSetRange(oSelection, now, nFormat)
elseif oSelection.supportsService("com.sun.star.sheet.SheetCell") then
'only one cell selected
subSetCell(oSelection, now, nFormat)
end if
end if
end sub
sub subSetRanges(oRanges, vValue, nFormat)
for i = 0 to oRanges.getCount -1
subSetRange(oRanges.getByIndex(i), vValue, nFormat)
next
end sub
sub subSetRange(oRange, vValue, nFormat)
for i = 0 to oRange.getColumns.getCount - 1
for j = 0 to oRange.getRows.getCount - 1
subSetCell(oRange.getCellByPosition(i,j), vValue, nFormat)
next
next
end sub
sub subSetCell(oCell, vValue, nFormat)
oCell.value=vValue
oCell.setPropertyValue("NumberFormat", nFormat)
end sub
function fnGetNumberFormatId(oDoc, sNumberFormat)
sCharLocale = oDoc.getPropertyValue("CharLocale")
nFormatId = oDoc.getNumberFormats.queryKey(sNumberFormat, sCharLocale, false)
if nFormatId = -1 then 'Not yet defined
nFormatId = oDoc.getNumberFormats.addNew(sNumberFormat, sCharLocale)
end if fnGetNumberFormatId = nFormatId end function