Difference between revisions of "Spreadsheet common"

From Apache OpenOffice Wiki
Jump to: navigation, search
(→‎Insert, delete row)
(7 intermediate revisions by 5 users not shown)
Line 3: Line 3:
  
 
==Current document==
 
==Current document==
−
<code>[oobas]
+
<source lang="oobas">
 
Dim oDoc As Object
 
Dim oDoc As Object
 
oDoc = ThisComponent
 
oDoc = ThisComponent
−
</code>
+
</source>
  
 
==Load existing document==
 
==Load existing document==
−
<code>[oobas]
+
<source lang="oobas">
 
Dim oDoc As Object
 
Dim oDoc As Object
 
oDoc = StartDesktop.loadComponentFromURL( ConvertToURL( "/home/robert/abc.xls" ), "_blank", _
 
oDoc = StartDesktop.loadComponentFromURL( ConvertToURL( "/home/robert/abc.xls" ), "_blank", _
 
         0, Array() )
 
         0, Array() )
−
</code>
+
</source>
 
Document type check:  
 
Document type check:  
  
−
<code>[oobas]
+
<source lang="oobas">
 
Function IsSpreadsheetDocument( oDoc As Object ) As Boolean
 
Function IsSpreadsheetDocument( oDoc As Object ) As Boolean
 
   IsSpreadsheetDocument = oDoc.supportsService( "com.sun.star.sheet.SpreadsheetDocument" )
 
   IsSpreadsheetDocument = oDoc.supportsService( "com.sun.star.sheet.SpreadsheetDocument" )
 
End Function
 
End Function
−
</code>
+
</source>
  
 
=Working with sheets=
 
=Working with sheets=
  
 
==Active sheet==
 
==Active sheet==
−
<code>[oobas]
+
<source lang="oobas">
 
Function fnActiveSheet
 
Function fnActiveSheet
 
ThisComponent.getCurrentController.getActiveSheet
 
ThisComponent.getCurrentController.getActiveSheet
 
end function
 
end function
−
</code>
+
</source>
  
 
==Number of sheets==
 
==Number of sheets==
−
<code>[oobas]
+
<source lang="oobas">
 
thisComponent.getSheets.getCount
 
thisComponent.getSheets.getCount
−
</code>
+
</source>
  
 
==Get sheet by index==
 
==Get sheet by index==
−
<code>[oobas]
+
<source lang="oobas">
 
Dim oSheet As Object
 
Dim oSheet As Object
 
   
 
   
Line 43: Line 43:
 
' or in OOo BASIC
 
' or in OOo BASIC
 
oSheet = oDoc.Sheets( 0 )
 
oSheet = oDoc.Sheets( 0 )
−
</code>
+
</source>
  
 
==Get sheet by name==
 
==Get sheet by name==
−
<code>[oobas]
+
<source lang="oobas">
 
Dim oSheet As Object
 
Dim oSheet As Object
 
   
 
   
 
oSheet = thisComponent.getSheets.getByName( "Sheet1" )
 
oSheet = thisComponent.getSheets.getByName( "Sheet1" )
−
</code>
+
</source>
  
 
==Iterate over all sheets==
 
==Iterate over all sheets==
−
<code>[oobas]
+
<source lang="oobas">
 
'iterate over all sheets
 
'iterate over all sheets
 
Dim oSheet As Object
 
Dim oSheet As Object
Line 65: Line 65:
 
         MsgBox "Next sheet name is " & oSheet.getName & "."
 
         MsgBox "Next sheet name is " & oSheet.getName & "."
 
Wend
 
Wend
−
</code>
+
</source>
  
 
==Create new sheet==
 
==Create new sheet==
−
<code>[oobas]
+
<source lang="oobas">
 
' create new sheet
 
' create new sheet
 
oDoc = thisComponent
 
oDoc = thisComponent
 
oSheet = oDoc.createInstance( "com.sun.star.sheet.Spreadsheet" )
 
oSheet = oDoc.createInstance( "com.sun.star.sheet.Spreadsheet" )
 
oDoc.Sheets.insertByName( "new sheet", oSheet )
 
oDoc.Sheets.insertByName( "new sheet", oSheet )
−
</code>
+
</source>
  
 
=Working with rows, columns=
 
=Working with rows, columns=
  
 
==Row, Column selection==
 
==Row, Column selection==
−
<code>[oobas]
+
<source lang="oobas">
 
Dim oRow As Object
 
Dim oRow As Object
 
Dim oColumn As Object
 
Dim oColumn As Object
Line 87: Line 87:
 
' get column B
 
' get column B
 
oColumn = oSheet.getColumns.getByIndex( 1 )
 
oColumn = oSheet.getColumns.getByIndex( 1 )
−
</code>
+
</source>
  
 
==Column Properties==
 
==Column Properties==
−
<code>[oobas]
+
<source lang="oobas">
 
' column width (in 100ths of mm)
 
' column width (in 100ths of mm)
 
oColumn.setPropertyValue("Width", 100)
 
oColumn.setPropertyValue("Width", 100)
Line 102: Line 102:
 
' page break?
 
' page break?
 
oColumn.setPropertyValue("IsStartOfNewPage", False)
 
oColumn.setPropertyValue("IsStartOfNewPage", False)
−
</code>
+
</source>
  
 
==Row properties==
 
==Row properties==
−
<code>[oobas]
+
<source lang="oobas">
 
' row height (in 100ths of mm)
 
' row height (in 100ths of mm)
 
oRow.setPropertyValue("Height", 100)
 
oRow.setPropertyValue("Height", 100)
Line 118: Line 118:
 
oRow.setPropertyValue("IsStartOfNewPage", False)
 
oRow.setPropertyValue("IsStartOfNewPage", False)
 
   
 
   
−
</code>
+
</source>
  
 
==Insert, delete row==
 
==Insert, delete row==
−
<code>[oobas]
+
<source lang="oobas">
 
' insert 2 rows at the 4th position (still counting from 0)
 
' insert 2 rows at the 4th position (still counting from 0)
 
oSheet.getRows.insertByIndex( 3, 2 )
 
oSheet.getRows.insertByIndex( 3, 2 )
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 )
Line 136: Line 136:
 
' remove 3 columns from the G column
 
' remove 3 columns from the G column
 
oSheet.getColumns.removeByIndex( 6, 3 )
 
oSheet.getColumns.removeByIndex( 6, 3 )
−
</code>
+
</source>
  
 
=Working with cells=
 
=Working with cells=
  
 
==Cell object==
 
==Cell object==
−
<code>[oobas]
+
<source lang="oobas">
 
Dim oCell As Object
 
Dim oCell As Object
 
   
 
   
Line 148: Line 148:
 
   
 
   
 
'Activecell (error check in case the current selection isn't a cell)
 
'Activecell (error check in case the current selection isn't a cell)
−
on error resume next
+
'on error resume next
 
oCell = ThisComponent.getCurrentSelection
 
oCell = ThisComponent.getCurrentSelection
 
   
 
   
 
'Get a named cell - in this case the name is "Date"
 
'Get a named cell - in this case the name is "Date"
 
oCell = thisComponent.NamedRanges.getByName("Date").getReferredCells
 
oCell = thisComponent.NamedRanges.getByName("Date").getReferredCells
−
</code>
+
</source>
 
Each cell can contain text, number or formula:  
 
Each cell can contain text, number or formula:  
  
−
<code>[oobas]
+
<source lang="oobas">
 
' modify text
 
' modify text
 
oCell.String = "This is A1 cell"
 
oCell.String = "This is A1 cell"
Line 173: Line 173:
 
oCell.Formula = "=A2+A3"
 
oCell.Formula = "=A2+A3"
 
' Old form: oCell.setPropertyValue("Formula", "=A2+A3")
 
' Old form: oCell.setPropertyValue("Formula", "=A2+A3")
−
</code>
+
</source>
  
 
==Content type detection==
 
==Content type detection==
−
<code>[oobas]
+
<source lang="oobas">
 
Select Case oCell.Type
 
Select Case oCell.Type
 
         Case com.sun.star.table.CellContentType.EMPTY
 
         Case com.sun.star.table.CellContentType.EMPTY
Line 190: Line 190:
 
                 ' oCell contains formula
 
                 ' oCell contains formula
 
End Select
 
End Select
−
</code>
+
</source>
  
 
=Cell range=
 
=Cell range=
Line 205: Line 205:
 
Following example insert cells at B2:C3 range. Any existing values in the specified range are moved downwards.  
 
Following example insert cells at B2:C3 range. Any existing values in the specified range are moved downwards.  
  
−
<code>[oobas]
+
<source lang="oobas">
 
Dim oRange As New com.sun.star.table.CellRangeAddress
 
Dim oRange As New com.sun.star.table.CellRangeAddress
 
   
 
   
Line 216: Line 216:
 
   
 
   
 
oSheet.insertCells( oRange, com.sun.star.sheet.CellInsertMode.DOWN )
 
oSheet.insertCells( oRange, com.sun.star.sheet.CellInsertMode.DOWN )
−
</code>
+
</source>
 
Possible values for the second argument are:  
 
Possible values for the second argument are:  
 
*NONE  
 
*NONE  
Line 227: Line 227:
 
Following example remove cells at B2:C3 range. Any existing values in the specified range are moved upwards.  
 
Following example remove cells at B2:C3 range. Any existing values in the specified range are moved upwards.  
  
−
<code>[oobas]
+
<source lang="oobas">
 
Dim oRange As New com.sun.star.table.CellRangeAddress
 
Dim oRange As New com.sun.star.table.CellRangeAddress
 
   
 
   
Line 238: Line 238:
 
   
 
   
 
oSheet.removeCells( oRange, com.sun.star.sheet.CellInsertMode.UP )
 
oSheet.removeCells( oRange, com.sun.star.sheet.CellInsertMode.UP )
−
</code>
+
</source>
 
Possible values for the second argument are:  
 
Possible values for the second argument are:  
 
*NONE  
 
*NONE  
Line 255: Line 255:
 
Following example will move the A2:B3 cells to the A1.  
 
Following example will move the A2:B3 cells to the A1.  
  
−
<code>[oobas]
+
<source lang="oobas">
 
oRange As New com.sun.star.table.CellRangeAddress
 
oRange As New com.sun.star.table.CellRangeAddress
 
   
 
   
Line 277: Line 277:
 
' copy the selected range to new position
 
' copy the selected range to new position
 
oSheet.copyRange( oAddress, oRange )
 
oSheet.copyRange( oAddress, oRange )
−
</code>
+
</source>
  
 
==Range selection by name==
 
==Range selection by name==
 
You can create range address with cell names:  
 
You can create range address with cell names:  
  
−
<code>[oobas]
+
<source lang="oobas">
 
oRange = oSheet.getCellRangeByName( "B2:C3" )
 
oRange = oSheet.getCellRangeByName( "B2:C3" )
−
</code>
+
</source>
 
You can select cells from the range in the same way as from the sheet. Following example returns cell B2:  
 
You can select cells from the range in the same way as from the sheet. Following example returns cell B2:  
  
−
<code>[oobas]
+
<source lang="oobas">
 
oCell = oRange.getCellByPosition( 0, 0 )
 
oCell = oRange.getCellByPosition( 0, 0 )
−
</code>
+
</source>
 
The cell position is counted from the upper left corner of the range, not of the sheet.  
 
The cell position is counted from the upper left corner of the range, not of the sheet.  
  
 
==Content deletion==
 
==Content deletion==
−
<code>[oobas]
+
<source lang="oobas">
 
Dim nFlags As Long
 
Dim nFlags As Long
 
   
 
   
Line 302: Line 302:
 
   
 
   
 
oRange.clearContents( nFlags )
 
oRange.clearContents( nFlags )
−
</code>
+
</source>
 
Available flags listed below:  
 
Available flags listed below:  
  
Line 335: Line 335:
 
*BOTTOM_RIGHT  
 
*BOTTOM_RIGHT  
  
−
<code>[oobas]
+
<source lang="oobas">
 
' cell background color
 
' cell background color
 
oCell.setPropertyValue( "CellBackColor", RGB( 0, 0, 0 ) )
 
oCell.setPropertyValue( "CellBackColor", RGB( 0, 0, 0 ) )
Line 351: Line 351:
 
          
 
          
 
oCell.setPropertyValue( "ShadowFormat", oShadow )
 
oCell.setPropertyValue( "ShadowFormat", oShadow )
−
</code>
+
</source>
  
 
==Justification==
 
==Justification==
Line 364: Line 364:
 
*REPEAT  
 
*REPEAT  
  
−
<code>[oobas]
+
<source lang="oobas">
 
' horizontal justification - right
 
' horizontal justification - right
 
oCell.setPropertyValue( "HoriJustify", com.sun.star.table.CellHoriJustify.RIGHT )
 
oCell.setPropertyValue( "HoriJustify", com.sun.star.table.CellHoriJustify.RIGHT )
−
</code>
+
</source>
  
 
===Vertical===
 
===Vertical===
Line 376: Line 376:
 
*BOTTOM  
 
*BOTTOM  
  
−
<code>[oobas]
+
<source lang="oobas">
 
' vertical justification - center
 
' vertical justification - center
 
oCell.setPropertyValue( "VertJustify", com.sun.star.table.CellVertJustify.CENTER )
 
oCell.setPropertyValue( "VertJustify", com.sun.star.table.CellVertJustify.CENTER )
−
</code>
+
</source>
  
 
===Orientation===
 
===Orientation===
Line 388: Line 388:
 
*STACKED  
 
*STACKED  
  
−
<code>[oobas]
+
<source lang="oobas">
 
' orientation - STACKED
 
' orientation - STACKED
 
oCell.setPropertyValue( "Orientation", com.sun.star.table.CellOrientation.STACKED )
 
oCell.setPropertyValue( "Orientation", com.sun.star.table.CellOrientation.STACKED )
−
</code>
+
</source>
  
 
===Wrapping, rotation===
 
===Wrapping, rotation===
 
Each cell contains property IsTextWrapped (Boolean) and RotateAngle (Long) for better justification.  
 
Each cell contains property IsTextWrapped (Boolean) and RotateAngle (Long) for better justification.  
  
−
<code>[oobas]
+
<source lang="oobas">
 
' wrapped text
 
' wrapped text
 
oCell.setPropertyValue( "IsTextWrapped", True )
 
oCell.setPropertyValue( "IsTextWrapped", True )
Line 402: Line 402:
 
' vertical text
 
' vertical text
 
oCell.setPropertyValue( "RotateAngle", 90 )
 
oCell.setPropertyValue( "RotateAngle", 90 )
−
</code>
+
</source>
 +
 
 +
=Example: Setting currently selected cells to current date and time=
 +
<source lang="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
 +
</source>
  
 
[[Category:Basic:Tutorials]]
 
[[Category:Basic:Tutorials]]
 +
[[Category:Calc|API]]
 +
[[Category:API]]

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