Topics   All   Mac OS X (Only)   Windows (Only)   Linux (Only, Not)   iOS (Only, Not)  
Components   Crossplatform Mac & Win   Server   Client   Old   Guides   Examples
New in version: 7.4   7.5   8.0   8.1   8.2   8.3   8.4   8.5   9.0   9.1    Statistic  

XL.Sheet.CellWriteFormula

Writes a formula into cell.

Component Version macOS Windows Server FileMaker Cloud FileMaker iOS SDK
XL 3.5 Yes Yes Yes Yes Yes
MBS( "XL.Sheet.CellWriteFormula"; bookRef; sheetIndex; Row; Column; Formula { ; Format } )   More

Parameters

Parameter Description Example value
bookRef The reference to the workbook. Please use XL.LoadBook to load a file. $ref
sheetIndex The index of the sheet. This number ranges from 0 to XL.Book.SheetCount-1. 0
Row The row for the cell. First row has index 0. $row
Column The column for the cell. First column has index 0. $column
Formula The new formula to write. "1+2"
Format Optional
The format for the cell. This is index from 0 to XL.GetFormatCount-1 in the book's list of formats.		 0

Result

Returns OK or error message.

Description

Writes a formula into cell.
As LibXL 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.
See also XL.Sheet.CellReadFormula and XL.Sheet.CellIsFormula.
Examples for formulas are: "TODAY()", "IF(C1>0;ABS(C1*D1);"""")", "SUM(E16:E38)" or "E39+E39*E40".

Examples

Loads book, writes some cells and saves back to container field:

#Load template excel file
Set Variable [$bookRef; Value:MBS( "XL.LoadBook"; XL::ExcelFile)]
Set Variable [$sheet; Value:0]
#First row
Set Variable [$row; Value:4]
Set Variable [$r; Value:MBS( "XL.Sheet.CellWriteNumber"; $BookRef; $sheet; $row; 0; 1 )]
Set Variable [$r; Value:MBS( "XL.Sheet.CellWriteText"; $BookRef; $sheet; $row; 1; "Cherries" )]
Set Variable [$r; Value:MBS( "XL.Sheet.CellWriteNumber"; $BookRef; $sheet; $row; 2; 5 )]
#Second row
Set Variable [$row; Value:5]
Set Variable [$r; Value:MBS( "XL.Sheet.CellWriteNumber"; $BookRef; $sheet; $row; 0; 2 )]
Set Variable [$r; Value:MBS( "XL.Sheet.CellWriteText"; $BookRef; $sheet; $row; 1; "Banana" )]
Set Variable [$r; Value:MBS( "XL.Sheet.CellWriteNumber"; $BookRef; $sheet; $row; 2; 3,5 )]
#Third row
Set Variable [$row; Value:6]
Set Variable [$r; Value:MBS( "XL.Sheet.CellWriteNumber"; $BookRef; $sheet; $row; 0; 3 )]
Set Variable [$r; Value:MBS( "XL.Sheet.CellWriteText"; $BookRef; $sheet; $row; 1; "Oranges" )]
Set Variable [$r; Value:MBS( "XL.Sheet.CellWriteNumber"; $BookRef; $sheet; $row; 2; 6,25 )]
#Last row
Set Variable [$row; Value:13]
Set Variable [$r; Value:MBS( "XL.Sheet.CellWriteFormula"; $BookRef; $sheet; $row; 2; "SUM(C5:C13)" )]
#Save to container and cleanup
Set Field [XL::OutputFile; MBS("XL.Save"; $bookRef; "test.xls")]
Set Variable [$r; Value:MBS( "XL.Book.Release"; $bookRef)]
Set Field [XL::Result; $r]

Writes a formula with bold font format:

# add a bold font based on another font
Set Variable [ $textFontBold ; Value: MBS( "XL.Book.AddFont"; $book; $textFont) ]
Set Variable [ $r ; Value: MBS( "XL.Font.SetBold"; $book; $textFontBold; 1) ]
# add format based on other format
Set Variable [ $dollarFormatBold ; Value: MBS( "XL.Book.AddFormat"; $book; $dollarFormat) ]
Set Variable [ $r ; Value: MBS( "XL.Format.SetFont"; $book; $dollarFormatBold; $textFontBold) ]
# use format
Set Variable [ $r ; Value: MBS( "XL.Sheet.CellWriteFormula"; $book; $sheet; 38; 4; "SUM(E16:E38)"; $dollarFormatBold) ]

See also

Example Databases

Created 18th August 2014, last changed 12nd November 2018


XL.Sheet.CellWriteError   -   XL.Sheet.CellWriteFormulaBoolean

Feedback: Report problem or ask question.



Links
MBS FileMaker tutorial videos