Download StarOffice™ Programmer`s Tutorial

Dim
Dim
Dim
Dim
Dim
Dim
oCell As Object, oRange As Object
nCols As Long, nRows As Long
oCellSum As Object, oCellAvg As Object
eSumFunc As Long, eAvgFunc As Long
nSum As Long, nAvg As Long
n As Long
oRange = GetCellRange(oDocument,0, 1, 0, 1, 5)
nCols = oRange.Columns.Count
nRows = oRange.Rows.Count
For n = 0 To nRows-1
oCell = oRange.getCellByPosition(0,n)
oCell.Value = n
Next n
eSumFunc = _
com.sun.star.sheet.GeneralFunction.SUM
eAvgFunc = _
com.sun.star.sheet.GeneralFunction.AVERAGE
nSum = oRange.computeFunction(eSumFunc)
nAvg = oRange.computeFunction(eAvgFunc)
oCell = GetCell(oDocument,0,0,nRows)
oCell.String = "Sum"
oCell = GetCell(oDocument,0,0,nRows +1)
oCell.String = "Average"
oCellSum = GetCell(oDocument,0,1,nRows)
oCellSum.Value = nSum
oCellAvg = GetCell(oDocument,0,1,nRows + 1)
oCellAvg.Value = nAvg
This program fills all cells in the range B1 through B6 with the numbers 0 through 5
(see for loop). It then computes the sum and the average over these values. Cells A7
and A8 are labeled with Sum and Average, respectively and the calculated values
are then written into the cells B7 and B8. Although the values are as correct as they
can be, this calculation method has a serious disadvantage: Whenever one of the
values used changes, you must recalculate the sum and average yourself. Since this
is precisely the job of a spreadsheet, you should use a formula for this purpose:
oCellAvg.Formula = "=SUM(B1:B6)/"+ CStr(nRows)
This line changes the content of cell B7 so that it contains a formula instead of the
value.
To summarize: To set the content of a cell to text, you use the String property, to
enter a value in a cell, you set the Value property. If you want to put a formula in a
cell, you assign it (including the equals sign) to the Formula property. Note that
function names must be English if you use the Formula property. To use functions
in your local language, you must use the FormulaLocal property. If you want to
know what a cell contains, you can retrieve its Type property:
Sub Printinfo (oCell As Object)
Dim eType as Long
eType = oCell.Type
If eType = com.sun.star.table.CellContentType.VALUE Then
Print CStr(oCell.Value)
Elseif eType = com.sun.star.table.CellContentType.TEXT Then
Using StarOffice API - Building blocks
69