Spreadsheet common
Contents
Common spreadsheet operations
See also working_with_documents
Current document
Dim oDoc As Object oDoc = ThisComponent
Load existing document
Dim oDoc As Object oDoc = StartDesktop.loadComponentFromURL( ConvertToURL( "/home/robert/abc.ods" ), "_blank", _ 0, Array() )
Document type check:
Function IsSpreadsheetDocument( oDoc As Object ) As Boolean IsSpreadsheetDocument = oDoc.supportsService( "com.sun.star.sheet.SpreadsheetDocument" ) End Function
Working with sheets
Active sheet
Function fnActiveSheet ThisComponent.getCurrentController.getActiveSheet end function
Number of sheets
thisComponent.getSheets.getCount
Get sheet by index
Dim oSheet As Object oSheet = thisComponent.getSheets.getByIndex( 0 ) ' or in OOo BASIC oSheet = oDoc.Sheets( 0 )
Get sheet by name
Dim oSheet As Object oSheet = thisComponent.getSheets.getByName( "Sheet1" )
Iterate over all sheets
'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
' 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
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
' 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
' 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
' 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
' 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
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:
' 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
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.
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.
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.
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:
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:
oCell = oRange.getCellByPosition( 0, 0 )
The cell position is counted from the upper left corner of the range, not of the sheet.
Content deletion
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
' 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
' 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
' 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
' 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.
' wrapped text oCell.setPropertyValue( "IsTextWrapped", True ) ' vertical text oCell.setPropertyValue( "RotateAngle", 90 )
Example: Setting currently selected cells to current date and time
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