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 |
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
- XL.Book.SheetCount
- XL.Format.SetFont
- XL.LoadBook
- XL.Sheet.CellIsFormula
- XL.Sheet.CellReadFormula
- XL.Sheet.CellWriteBooleans
- XL.Sheet.CellWriteComment
- XL.Sheet.CellWriteComments
- XL.Sheet.CellWriteError
- XL.Sheet.CellWriteFormulaBoolean
- XL.Sheet.CellWriteFormulaNumber
- XL.Sheet.CellWriteFormulas
- XL.Sheet.CellWriteFormulaText
- XL.Sheet.CellWriteNumber
- XL.Sheet.CellWriteNumbers
- XL.Sheet.CellWriteText
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