Difference between revisions of "Spreadsheet common"
m |
|||
(13 intermediate revisions by 6 users not shown) | |||
Line 3: | Line 3: | ||
==Current document== | ==Current document== | ||
− | < | + | <syntaxhighlight lang="oobas"> |
Dim oDoc As Object | Dim oDoc As Object | ||
oDoc = ThisComponent | oDoc = ThisComponent | ||
− | </ | + | </syntaxhighlight> |
==Load existing document== | ==Load existing document== | ||
− | < | + | <syntaxhighlight lang="oobas"> |
Dim oDoc As Object | Dim oDoc As Object | ||
− | oDoc = StartDesktop.loadComponentFromURL( ConvertToURL( "/home/robert/abc. | + | oDoc = StartDesktop.loadComponentFromURL( ConvertToURL( "/home/robert/abc.ods" ), "_blank", _ |
0, Array() ) | 0, Array() ) | ||
− | </ | + | </syntaxhighlight> |
Document type check: | Document type check: | ||
− | < | + | <syntaxhighlight 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 | ||
− | </ | + | </syntaxhighlight> |
=Working with sheets= | =Working with sheets= | ||
==Active sheet== | ==Active sheet== | ||
− | < | + | <syntaxhighlight lang="oobas"> |
Function fnActiveSheet | Function fnActiveSheet | ||
ThisComponent.getCurrentController.getActiveSheet | ThisComponent.getCurrentController.getActiveSheet | ||
end function | end function | ||
− | </ | + | </syntaxhighlight> |
==Number of sheets== | ==Number of sheets== | ||
− | < | + | <syntaxhighlight lang="oobas"> |
thisComponent.getSheets.getCount | thisComponent.getSheets.getCount | ||
− | </ | + | </syntaxhighlight> |
==Get sheet by index== | ==Get sheet by index== | ||
− | < | + | <syntaxhighlight 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 ) | ||
− | </ | + | </syntaxhighlight> |
==Get sheet by name== | ==Get sheet by name== | ||
− | < | + | <syntaxhighlight lang="oobas"> |
Dim oSheet As Object | Dim oSheet As Object | ||
oSheet = thisComponent.getSheets.getByName( "Sheet1" ) | oSheet = thisComponent.getSheets.getByName( "Sheet1" ) | ||
− | </ | + | </syntaxhighlight> |
==Iterate over all sheets== | ==Iterate over all sheets== | ||
− | < | + | <syntaxhighlight 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 | ||
− | </ | + | </syntaxhighlight> |
==Create new sheet== | ==Create new sheet== | ||
− | < | + | <syntaxhighlight 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 ) | ||
− | </ | + | </syntaxhighlight> |
=Working with rows, columns= | =Working with rows, columns= | ||
==Row, Column selection== | ==Row, Column selection== | ||
− | < | + | <syntaxhighlight 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 ) | ||
− | </ | + | </syntaxhighlight> |
==Column Properties== | ==Column Properties== | ||
− | < | + | <syntaxhighlight 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) | ||
− | </ | + | </syntaxhighlight> |
==Row properties== | ==Row properties== | ||
− | < | + | <syntaxhighlight lang="oobas"> |
' row height (in 100ths of mm) | ' row height (in 100ths of mm) | ||
oRow.setPropertyValue("Height", 100) | oRow.setPropertyValue("Height", 100) | ||
Line 117: | Line 117: | ||
' page break? | ' page break? | ||
oRow.setPropertyValue("IsStartOfNewPage", False) | oRow.setPropertyValue("IsStartOfNewPage", False) | ||
− | + | </syntaxhighlight> | |
− | </ | + | |
==Insert, delete row== | ==Insert, delete row== | ||
− | < | + | <syntaxhighlight 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 126: | ||
' remove 3 lines from the 7th line | ' remove 3 lines from the 7th line | ||
oSheet.getRows.removeByIndex( 6, 3 ) | oSheet.getRows.removeByIndex( 6, 3 ) | ||
− | </ | + | </syntaxhighlight> |
==Insert, delete column== | ==Insert, delete column== | ||
− | < | + | <syntaxhighlight 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 135: | ||
' remove 3 columns from the G column | ' remove 3 columns from the G column | ||
oSheet.getColumns.removeByIndex( 6, 3 ) | oSheet.getColumns.removeByIndex( 6, 3 ) | ||
− | </ | + | </syntaxhighlight> |
=Working with cells= | =Working with cells= | ||
==Cell object== | ==Cell object== | ||
− | < | + | <syntaxhighlight lang="oobas"> |
Dim oCell As Object | Dim oCell As Object | ||
Line 148: | Line 147: | ||
'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 | ||
− | </ | + | </syntaxhighlight> |
Each cell can contain text, number or formula: | Each cell can contain text, number or formula: | ||
− | < | + | <syntaxhighlight lang="oobas"> |
' modify text | ' modify text | ||
oCell.String = "This is A1 cell" | oCell.String = "This is A1 cell" | ||
Line 173: | Line 172: | ||
oCell.Formula = "=A2+A3" | oCell.Formula = "=A2+A3" | ||
' Old form: oCell.setPropertyValue("Formula", "=A2+A3") | ' Old form: oCell.setPropertyValue("Formula", "=A2+A3") | ||
− | </ | + | </syntaxhighlight> |
==Content type detection== | ==Content type detection== | ||
− | < | + | <syntaxhighlight 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 189: | ||
' oCell contains formula | ' oCell contains formula | ||
End Select | End Select | ||
− | </ | + | </syntaxhighlight> |
=Cell range= | =Cell range= | ||
Line 205: | Line 204: | ||
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. | ||
− | < | + | <syntaxhighlight lang="oobas"> |
Dim oRange As New com.sun.star.table.CellRangeAddress | Dim oRange As New com.sun.star.table.CellRangeAddress | ||
Line 216: | Line 215: | ||
oSheet.insertCells( oRange, com.sun.star.sheet.CellInsertMode.DOWN ) | oSheet.insertCells( oRange, com.sun.star.sheet.CellInsertMode.DOWN ) | ||
− | </ | + | </syntaxhighlight> |
Possible values for the second argument are: | Possible values for the second argument are: | ||
*NONE | *NONE | ||
Line 227: | Line 226: | ||
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. | ||
− | < | + | <syntaxhighlight lang="oobas"> |
Dim oRange As New com.sun.star.table.CellRangeAddress | Dim oRange As New com.sun.star.table.CellRangeAddress | ||
Line 238: | Line 237: | ||
oSheet.removeCells( oRange, com.sun.star.sheet.CellInsertMode.UP ) | oSheet.removeCells( oRange, com.sun.star.sheet.CellInsertMode.UP ) | ||
− | </ | + | </syntaxhighlight> |
Possible values for the second argument are: | Possible values for the second argument are: | ||
*NONE | *NONE | ||
Line 255: | Line 254: | ||
Following example will move the A2:B3 cells to the A1. | Following example will move the A2:B3 cells to the A1. | ||
− | < | + | <syntaxhighlight lang="oobas"> |
oRange As New com.sun.star.table.CellRangeAddress | oRange As New com.sun.star.table.CellRangeAddress | ||
Line 277: | Line 276: | ||
' copy the selected range to new position | ' copy the selected range to new position | ||
oSheet.copyRange( oAddress, oRange ) | oSheet.copyRange( oAddress, oRange ) | ||
− | </ | + | </syntaxhighlight> |
==Range selection by name== | ==Range selection by name== | ||
You can create range address with cell names: | You can create range address with cell names: | ||
− | < | + | <syntaxhighlight lang="oobas"> |
oRange = oSheet.getCellRangeByName( "B2:C3" ) | oRange = oSheet.getCellRangeByName( "B2:C3" ) | ||
− | </ | + | </syntaxhighlight> |
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: | ||
− | < | + | <syntaxhighlight lang="oobas"> |
oCell = oRange.getCellByPosition( 0, 0 ) | oCell = oRange.getCellByPosition( 0, 0 ) | ||
− | </ | + | </syntaxhighlight> |
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== | ||
− | < | + | <syntaxhighlight lang="oobas"> |
Dim nFlags As Long | Dim nFlags As Long | ||
Line 302: | Line 301: | ||
oRange.clearContents( nFlags ) | oRange.clearContents( nFlags ) | ||
− | </ | + | </syntaxhighlight> |
Available flags listed below: | Available flags listed below: | ||
Line 335: | Line 334: | ||
*BOTTOM_RIGHT | *BOTTOM_RIGHT | ||
− | < | + | <syntaxhighlight 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 350: | ||
oCell.setPropertyValue( "ShadowFormat", oShadow ) | oCell.setPropertyValue( "ShadowFormat", oShadow ) | ||
− | </ | + | </syntaxhighlight> |
==Justification== | ==Justification== | ||
===Horizontal=== | ===Horizontal=== | ||
− | Each cell contains property HoriJustify with possible values (com.sun.star.table.CellHoriJustify): | + | Each cell contains property HoriJustify with possible values (<idl>com.sun.star.table.CellHoriJustify</idl>): |
*STANDARD | *STANDARD | ||
*LEFT | *LEFT | ||
Line 364: | Line 363: | ||
*REPEAT | *REPEAT | ||
− | < | + | <syntaxhighlight 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 ) | ||
− | </ | + | </syntaxhighlight> |
===Vertical=== | ===Vertical=== | ||
− | Each cell contains property VertJustify with possible values (com.sun.star.table.CellVertJustify): | + | Each cell contains property VertJustify with possible values (<idl>com.sun.star.table.CellVertJustify</idl>): |
*STANDARD | *STANDARD | ||
*TOP | *TOP | ||
Line 376: | Line 375: | ||
*BOTTOM | *BOTTOM | ||
− | < | + | <syntaxhighlight 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 ) | ||
− | </ | + | </syntaxhighlight> |
===Orientation=== | ===Orientation=== | ||
Line 388: | Line 387: | ||
*STACKED | *STACKED | ||
− | < | + | <syntaxhighlight lang="oobas"> |
' orientation - STACKED | ' orientation - STACKED | ||
oCell.setPropertyValue( "Orientation", com.sun.star.table.CellOrientation.STACKED ) | oCell.setPropertyValue( "Orientation", com.sun.star.table.CellOrientation.STACKED ) | ||
− | </ | + | </syntaxhighlight> |
===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. | ||
− | < | + | <syntaxhighlight lang="oobas"> |
' wrapped text | ' wrapped text | ||
oCell.setPropertyValue( "IsTextWrapped", True ) | oCell.setPropertyValue( "IsTextWrapped", True ) | ||
Line 402: | Line 401: | ||
' vertical text | ' vertical text | ||
oCell.setPropertyValue( "RotateAngle", 90 ) | oCell.setPropertyValue( "RotateAngle", 90 ) | ||
− | </ | + | </syntaxhighlight> |
+ | |||
+ | =Example: Setting currently selected cells to current date and time= | ||
+ | <syntaxhighlight 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 | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | [[Category:Basic:Tutorials]] | ||
+ | [[Category:Calc|API]] | ||
+ | [[Category:API]] |
Latest revision as of 12:43, 4 February 2021
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