Difference between revisions of "Spreadsheet common"

From Apache OpenOffice Wiki
Jump to: navigation, search
(→‎Insert, delete row)
Line 127: Line 127:
 
' remove 3 lines from the 7th line
 
' remove 3 lines from the 7th line
 
oSheet.getRows.removeByIndex( 6, 3 )
 
oSheet.getRows.removeByIndex( 6, 3 )
−
</code>
+
</source>
  
 
==Insert, delete column==
 
==Insert, delete column==
−
<code>[oobas]
+
<source lang="oobas">
 
' insert 2 columns into the fourth (D) column
 
' insert 2 columns into the fourth (D) column
 
oSheet.getColumns.insertByIndex( 3, 2 )
 
oSheet.getColumns.insertByIndex( 3, 2 )

Revision as of 13:49, 12 December 2008

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.xls" ), "_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=104557"
Personal tools