Use formatted text in Excel files with FileMaker and LibXL

[MonkeybreadSoftware]

 

With the recent release of LibXL 3.9, we got new functions for working with styled text. For our MBS FileMaker Plugin, we added new functions for styled text:

• XL.Sheet.CellIsStyledText
• XL.Sheet.CellReadStyledText
• XL.Sheet.CellWriteStyledText

Now you can pass directly styled text from a field in FileMaker to a cell in an Excel document. Or you build a styled text from scratch with text formatting functions in FileMaker like in the example below:

# create sheet
Set Variable [ $sheet ; Value: MBS( "XL.Book.AddSheet"; $book; "Styled Text Test") ] 
# 
Set Variable [ $row ; Value: 1 ] 
Set Variable [ $col ; Value: 0 ] 
Set Variable [ $text ; Value: TextColor ( TextSize ( "Red"; 15 ) & " Text" ; RGB ( 255 ; 0 ; 0 ) ) & TextColor ( TextSize ( " and blue"; 14 ) ; RGB (0 ; 0 ; 255 ) ) & " in FileMaker" ] 
# 
Set Variable [ $r ; Value: MBS( "XL.Sheet.CellWriteStyledText"; $book; $sheet; $row; $col; $text) ] 
# 
Set Variable [ $text ; Value: TextFont ( TextStyleAdd ( "Hello World" ; Bold+Underline+Italic ); "Calibri" ) ] 
Set Variable [ $row ; Value: 2 ] 
Set Variable [ $r ; Value: MBS( "XL.Sheet.CellWriteStyledText"; $book; $sheet; $row; $col; $text) ] 

In our example database we show the styled text in two fields to check if we got them right:

For the conversion to/from styled text, we support passing font name, color, size and the following styles: bold, italic, strikethrough, single/double underline, subscript and superscript. Optionally, you can attach a format to the cell, which may define the alignment and other formatting options. We hope you enjoy this functions and use them to post process the Excel exports from FileMaker or code your own export from scratch via script. 

Please try the new functionality and don't hesitate to contact us with questions. This is already included in 10.2pr4 download.