Spreadsheet common

From Apache OpenOffice Wiki
Jump to: navigation, search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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
Retrieved from "https://wiki.openoffice.org/w/index.php?title=Spreadsheet_common&oldid=247788"
Personal tools