Monkeybread Software - MBS FileMaker Plugin: XL.Book.AddFormat

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

XL.Book.AddFormat

Adds a new format to the workbook, initial parameters can be copied from other format.

Component	Version	macOS	Windows	Linux	Server	iOS SDK
XL	3.5	✅ Yes	✅ Yes	✅ Yes	✅ Yes	✅ Yes

MBS( "XL.Book.AddFormat"; BookRef { ; initFormat } ) More

Parameters

Parameter Description Example Flags

BookRef The reference to the workbook. Please use XL.LoadBook to load a file. $ref

initFormat The index of an existing format which you want to use as a template. 0 Optional

Parameter	Description	Example	Flags
BookRef	The reference to the workbook. Please use XL.LoadBook to load a file.	$ref
initFormat	The index of an existing format which you want to use as a template.	0	Optional

Result

Returns index of new format or error.

Description

Adds a new format to the workbook, initial parameters can be copied from other format.

Examples

Writes sheet with red font:

#create new xls file
Set Variable [$book; Value:MBS( "XL.NewBook"; 0 )]
#create fonts
Set Variable [$redColor; Value:MBS( "XL.Color.Pack"; $book; 255; 0; 0)]
Set Variable [$redFont; Value:MBS( "XL.Book.AddFont"; $book)]
Set Variable [$r; Value:MBS( "XL.Font.SetColor"; $book; $redFont; $redColor)]
#
#create formats
Set Variable [$redFormat; Value:MBS( "XL.Book.AddFormat"; $book)]
Set Variable [$r; Value:MBS( "XL.Format.SetFont"; $book; $redFormat; $redFont)]
#
#create sheet
Set Variable [$sheet; Value:MBS( "XL.Book.AddSheet"; $book; "Sales Receipt")]
#
#add number in red
Set Variable [$r; Value:MBS( "XL.Sheet.CellWriteNumber"; $book; $sheet; 5; 3; 123; $redFormat)]
#
Set Field [Writing data::Output; MBS("XL.Book.Save"; $book; "test.xls")]
Set Variable [$r; Value:MBS("XL.Book.Release"; $book)]

Use a number format:

# create new format
Set Variable [$format; Value:MBS( "XL.Book.AddFormat"; $book)]
# use existing number format
Set Variable [$r; Value:MBS( "XL.Format.SetNumFormat"; $book; $format; 7)]
# and use format
Set Variable [$r; Value:MBS( "XL.Sheet.CellWriteNumber"; $book; $sheet; 5; 3; 123; $format)]

Apply a new format to a column:

#create new format
Set Variable [$format; Value:MBS( "XL.Book.AddFormat"; $BookRef )]
Set Variable [$r; Value:MBS( "XL.Format.SetNumFormat"; $BookRef; $format; 7)]
#set as format for column
Set Variable [$r; Value:MBS( "XL.Sheet.SetColumn"; $BookRef; 0; 6; 6; 50 ; $format; 0 )]
#loop over all rows
Set Variable [$lastRow; Value:MBS( "XL.Sheet.GetLastRow"; $BookRef; $sheet )]
Set Variable [$row; Value:1]
Loop
Set Variable [$r; Value:MBS( "XL.Sheet.SetCellFormat"; $BookRef; $sheet; $row; 6; $format )]
#next
Set Variable [$row; Value:$row + 1]
Exit Loop If [$row > $lastRow]
End Loop

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 ) ]