Components All New MacOS Windows Linux iOS
Examples Mac & Win Server Client Guides Statistic FMM Blog Deprecated Old

XL.Sheet.CellWriteFormula

Writes a formula into cell.

Component Version macOS Windows Linux Server 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 Flags
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 The format for the cell. This is index from 0 to XL.GetFormatCount-1 in the book's list of formats. 0 Optional

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 macOS 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".

See also XL.Sheet.CellWriteFormulaBoolean, XL.Sheet.CellWriteFormulaNumber and XL.Sheet.CellWriteFormulaText where you can provide the result of the calculation as most viewer don't calculate and just show the stored value. Excel will perform missing calculations when you open the document.

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

Release notes

Example Databases

Blog Entries

This function checks for a license.

Created 18th August 2014, last changed 29th August 2023


XL.Sheet.CellWriteError - XL.Sheet.CellWriteFormulaBoolean