XL.Sheet.CellWriteText

Writes a string into cell.

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
Text	The new text to write.	"hello"
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

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]

Write text with two lines in one cell:

Set Variable [$r; Value:MBS( "XL.Sheet.CellWriteText"; $book; $sheet; 16; 1; "Bananas¶Apple"; $myFormat)]

Write two lines with adjusting the row height:

# create format with wrap
#
Set Variable [ $textFormat ; Value: MBS( "XL.Book.AddFormat"; $book) ]
Set Variable [ $r ; Value: MBS( "XL.Format.SetWrap"; $book; $textFormat; 1) ]
#
# put in text with Char(10) for new line
#
Set Variable [ $r ; Value: MBS( "XL.Sheet.CellWriteText"; $book; $sheet; $row; 1; "First line" & Char(10) & "Second line"; $textFormat) ]
#
# make line higher
Set Variable [ $r ; Value: MBS( "XL.Sheet.SetRow"; $book; $sheet; $row; 20 ) ]

Create header row:

#
# define a font:
#
Set Variable [ $headerFont ; Value: MBS( "XL.Book.AddFont"; $book) ]
Set Variable [ $r ; Value: MBS( "XL.Font.SetSize"; $book; $headerFont; 12) ]
Set Variable [ $r ; Value: MBS( "XL.Font.SetName"; $book; $headerFont; "Century Gothic") ]
Set Variable [ $r ; Value: MBS( "XL.Font.SetBold"; $book; $headerFont; 1) ]
#
# define a format:
#
Set Variable [ $headerFormat ; Value: MBS( "XL.Book.AddFormat"; $book) ]
Set Variable [ $r ; Value: MBS( "XL.Format.SetFont"; $book; $headerFormat; $headerFont) ]
Set Variable [ $r ; Value: MBS( "XL.Format.SetFillPattern"; $book; $headerFormat; 1) ]
Set Variable [ $r ; Value: MBS( "XL.Format.SetPatternForegroundColor"; $book; $headerFormat; MBS( "XL.Color.Pack"; $book; 200; 200; 200) ) ]
Set Variable [ $r ; Value: MBS( "XL.Format.SetAlignH"; $book; $headerFormat; 2 /* center */ ) ]
#
Set Variable [ $r ; Value: MBS( "XL.Sheet.SetRow"; $book; $sheet; $row ; 20 ) ]
Set Variable [ $r ; Value: MBS( "XL.Sheet.CellWriteText"; $book; $sheet; $row; 0; "Account" ; $headerFormat ) ]
Set Variable [ $r ; Value: MBS( "XL.Sheet.CellWriteText"; $book; $sheet; $row; 1; "Account Group" ; $headerFormat ) ]
Set Variable [ $r ; Value: MBS( "XL.Sheet.CellWriteText"; $book; $sheet; $row; 2; "Report Name" ; $headerFormat ) ]
Set Variable [ $r ; Value: MBS( "XL.Sheet.CellWriteText"; $book; $sheet; $row; 3; "Date TS" ; $headerFormat ) ]
Set Variable [ $r ; Value: MBS( "XL.Sheet.CellWriteText"; $book; $sheet; $row; 4; "Text" ; $headerFormat ) ]
Set Variable [ $r ; Value: MBS( "XL.Sheet.CellWriteText"; $book; $sheet; $row; 5; "Text2" ; $headerFormat ) ]
Set Variable [ $r ; Value: MBS( "XL.Sheet.CellWriteText"; $book; $sheet; $row; 6; "Text3" ; $headerFormat ) ]

See also

• XL.Sheet.CellWriteComment
• XL.Sheet.CellWriteDate
• XL.Sheet.CellWriteDates
• XL.Sheet.CellWriteError
• XL.Sheet.CellWriteFormula
• XL.Sheet.CellWriteNumbers
• XL.Sheet.CellWriteTexts
• XL.Sheet.SetCol
• XL.Sheet.SetProtect
• XL.Sheet.SetRow

Release notes

• Version 12.2
  • Fixed issue with XL.Sheet.CellWriteText to allow you to pass empty value to not change format.
• Version 10.3
  • Added XL.Sheet.CellWriteTextAsNumber, XL.Sheet.GetFirstFilledCol, XL.Sheet.GetFirstFilledRow, XL.Sheet.GetLastFilledCol and XL.Sheet.GetLastFilledRow functions.
• Version 9.1
  • Fixed a problem with XL.Sheet.CellWriteTexts function and empty entries in value list.
• Version 8.0
  • Added XL.Sheet.CellReadBooleans, XL.Sheet.CellReadComments, XL.Sheet.CellReadDates, XL.Sheet.CellReadFormulas, XL.Sheet.CellReadNumbers, XL.Sheet.CellReadTexts, XL.Sheet.CellWriteBlanks, XL.Sheet.CellWriteBooleans, XL.Sheet.CellWriteComments, XL.Sheet.CellWriteDates, XL.Sheet.CellWriteFormulas, XL.Sheet.CellWriteNumbers, XL.Sheet.CellWriteTexts, XL.Sheet.GetCellErrors, XL.Sheet.GetCellFormats, XL.Sheet.RemoveComments and XL.Sheet.SetCellFormats.

Example Databases

• XL/AutoFilter
• XL/Change Column Names
• XL/Conditional Formatting
• XL/Edit Sheet
• XL/Export records
• XL/Writing data

Blog Entries

• MBS Plugin Advent calendar: 13 - XL
• MBS FileMaker Plugin, version 12.2pr3
• New column names in an Excel document
• How to use XL functions with MBS FileMaker Plugin

This function checks for a license.

Created 18th August 2014, last changed 7th February 2020

XL.Sheet.CellWriteStyledText - XL.Sheet.CellWriteTextAsNumber