Writes a formula
into cell with optionally specified format.
If you pass
in a value of 1, then it will set the cell
to "TRUE", otherwise "FALSE".
xlHandle to an Excel file is obtained by the
XLCreateFromTemplate function. formatHandle is an optional parameter which can be
omitted. A formatHandle can be obtained by a call to XLAddFormat.htm.
row and column
are zero based. Please remember that Excel files have limits like 65536 rows
and 256 columns.
Caution: As XLWriteFormula
does not calculate the result of the formula, it will not encode the result in the
Excel file. It does only store the formula, so an app opening the file will
have to do the calculation. But QuickLook on Mac OS X does not calculate, so
results do not show there.
Parameters:
|
Name |
Type |
Value |
Description |
|
xlHandle |
Integer |
0 - n |
A handle
to an Excel document. The document must have been created by using the
function XLCreateFromTemplate
and the Integer we obtained from it is used as xlHandle
here. |
|
row |
Integer |
0 – n |
Index of
row to write to |
|
column |
Integer |
0 – n |
Index of
column to write to |
|
value |
String |
valid Excel formula |
Example:
"=SUM(B15:C15)" |
|
formatHandle |
Integer |
0 - n |
Optional
handle to a format object. The formatHandle
is is obtained by a call to XLAddFormat |
Available
in:
|
WindowMain |
WindowLink |
|
YES |
NO |
Hint: You can find references to
XojoScripts which make use of this function by sending the following SQL
statement to the database:
Select
id,GUID, ScriptName,ScriptCode from im_scripts where ScriptCode Like
'%XLWriteFormula%'
Example
XojoScript:
Dim hXL As
Integer
// Get Handle to newly created Excel file
hXL
= XLCreateFromTemplate("myXLtest.xls")
// Add Format for vertical alignment
Dim frmtHandle As
Integer = XLAddFormat(hXL)
// Set Alignment of Format
XLFormatSetAlign(hXL, frmtHandle, "ALIGNV", "ALIGNVCENTER")
// Define Excel calculation formula
Dim formula As
String
If hXL > -1
Then
Dim i As integer
Dim h As Double = 25
For i = 2 To 100
// Example: =SUM(B15:C15)
formula = "=SUM(B"
+ Str(i+1) + ":C"
+ Str(i+1) + ")"
XLSetRowHeight(hXL,frmtHandle,i,h)
XLWriteString(hXL,i,0,"Zeile "
+ Str(i),frmtHandle)
XLWriteNumber(hXL,i,1,i,frmtHandle)
XLWriteNumber(hXL,i,2,(Ceil(rnd*100)),frmtHandle)
XLWriteFormula(hXL,i,3,formula,frmtHandle)
Next
// Set Column Widths, -1 = AutoFit
Dim w As Double = 10
XLSetColWidth(hXL,0,0,w)
XLSetColWidth(hXL,1,3,15)
XLSave(hXL)
XL(hXL,"launch")
End If