Door 13 - LibXL

Welcome to door 13 of our advent calendar. Today it's all about the LibXL component. Did you know that you can use the MBS FileMaker Plugin to read, create and modify Excel files without having Excel installed?

This is made possible by the LibXL component. LibXL is an independent product that can be used in FileMaker with the plugin. This means that you need an additional LibXL license in addition to your plugin license to be able to use the functions properly. Today I will show you how you can read and modify data with LibXL and even create your own files.

Preparation

But before we start our work, we first need to make the LibXL library available so that we can use the functions in the plugin. The library file you need can be found in the examples supplied with the plugin download. Exactly which file you need depends on your operating system. If you want to initialize the library on a Mac, then you need the library file with the extension dylib. If you use Windows, we have a file for the old Windows computers that work with 32 bit and a file with the extension dll for the 64 bit systems. If you want to use LibXL on a Linux server, we have included libxl.so with the Linux plugins.

For initialization we have the function XL.Initialize. Here we enter the path to the library. If you already have a LibXL license, enter the name and license key here. If you have customers who use different operating systems, you can also copy the InitXL script from the examples, the libraries can then simply be placed in the same folder as the database file. But for actual deployment, it may be easier to put the libXL file into the same folder as the plugin. Then you can pass "" as path and the plugin automatically checks the plugin folder. At the end of the script, the Initialize function is called with the appropriate parameters. You can adjust the values of the parameters in the script previously.

  # If you like to get a LibXL license, please follow links on our pricing page:
# https://www.monkeybreadsoftware.de/filemaker/pricing.shtml

Set Variable [ $LicenseeName ; Value: "your name" ] 
Set Variable [ $LicenseKey ; Value: "your LibXL license key" ] 
Set Variable [ $r ; Value: MBS( "XL.Initialize"; $path; $LicenseeName; $LicenseKey) ] 
If [ $r  ≠ "OK" ] 
	Show Custom Dialog [ "Error" ; $r ] 
	Halt Script
End If

When we work in a script, it makes sense to check at the beginning of each script whether the LibXL is initialized. To do this, we use the XL.IsInitialized function which returns a 0 if the initialization has not yet been performed. The beginning can look like this:

If [ MBS("XL.IsInitialized") ≠ 1 ] 
	Perform Script [ Specified: From list ; "InitXL" ; Parameter:    ]
End If

Create file

Let's start our work. First we want to create a file in which we want to enter data. An Excel file is called a book in LibXL. The individual tables within such a file are on sheets. So we want to create a new workbook. To do this, we have the XL.NewBook function. It is used to create a working environment in the memory for which we receive a reference number back with the function, which we can then continue to work with. In an optional parameter we can also decide whether an Excel file should be created in the old xls format (parameter = 0) or the newer xlsx format (parameter = 1). Of course, our sheet must now be added to this workbook using XL.Book.AddSheet. In the parameters we specify the reference to which the book belongs and the name that the sheet should have. If you want to use an already created sheet as a template for the new one, you can optionally specify the sheet index of the template here. We then receive the sheet index of the sheet just created as a return.

Set Variable [ $Book ; Value: MBS("XL.NewBook"; 1) ] 
Set Variable [ $Sheet ; Value: MBS("XL.Book.AddSheet"; $Book; "Sheet 1") ]

We can now fill this with life.

If you do not yet have a license, there is always a warning text in the first line of the document and you cannot write to this line. If you try to do this, an error will occur, which is why we want to start filling the second row in our example. We have many different functions for writing in a cell, which we use depending on what we want to write in the cell. If we want to write a simple text, for example, we use the XL.Sheet.CellWriteText function. Here we first enter the book reference and then the sheet index in the parameters, followed by the cell that is to be written to in the table. In our case the second row and the first column. Since we start counting at 0, we have a 1 as the value for the row and a 0 for the column. Now the text follows and we can optionally specify a format, but more on that later.

Set Variable [ $r ; Value: MBS("XL.Sheet.CellWriteText"; $Book; $Sheet; 1; 0; "Hello") ]

Of course, there is not only a function for text, but also for numbers, dates, a Boolean value or formulas. For formulas, we enter the formula we want to calculate as the value. We can even use cells with their coordinates as values or, for example, calculate totals in formulas over entire ranges. In this example, cells B2 and C2 are added together. We have also previously set the values 49 and 2.

Set Variable [ $r ; Value: MBS("XL.Sheet.CellWriteNumber"; $Book; $Sheet; 1; 1; 49) ] 
Set Variable [ $r ; Value: MBS("XL.Sheet.CellWriteNumber"; $Book; $Sheet; 1; 2; 2) ] 
Set Variable [ $r ; Value: MBS("XL.Sheet.CellWriteFormula"; $Book; $Sheet; 1; 3; "B2+C2") ] 

If we use the XL.Sheet.CellWriteDate function, we get a number in a cell that is formatted as text, for example. If we want this date to be displayed as a date, the cell must be of type Date

Set Variable [ $r ; Value: MBS( "XL.Sheet.CellWriteDate"; $Book; $Sheet;1; 4; Get(CurrentTimestamp)  ) ] 

For a Boolean value, we can use 1 for TRUE and 0 for FALSE.

Set Variable [ $r ; Value: MBS( "XL.Sheet.CellWriteBoolean"; $Book; $Sheet;1; 5; 1) ]

We have already talked about formats and that we can also pass a format in each of these functions. This formats the text of a document and thus defines its appearance.

Here, for example, we first create a font, which gets the font via the XL.Font.SetName function. In addition, the font should be italic and have a size of 20pt. Now we create a format ( XL.Book.AddFormat ) and transfer the created font to this format ( XL.Format.SetFont ). In a format, you could now enter additional information about the border, for example. You can then specify the format as an additional parameter in the XL.Sheet.CellWriteText function, for example.

Set Variable [ $Font ; Value: MBS("XL.Book.AddFont"; $Book) ] 
Set Variable [ $r ; Value: MBS("XL.Font.SetName"; $Book; $Font; "Comic Sans MS") ] 
Set Variable [ $r ; Value: MBS("XL.Font.SetItalic"; $Book; $Font; 1) ] 
Set Variable [ $r ; Value: MBS("XL.Font.SetSize"; $Book; $Font; 20) ] 
Set Variable [ $CellFormat ; Value: MBS("XL.Book.AddFormat"; $Book) ] 
Set Variable [ $r ; Value: MBS( "XL.Format.SetFont"; $Book; $CellFormat; $Font ) ] 
Set Variable [ $r ; Value: MBS("XL.Sheet.CellWriteText"; $Book; $Sheet; 1; 0; "Hello"; $CellFormat) ] 

Another possibility to transfer formatting from the FileMaker database to the fields is the XL.Sheet.CellWriteStyledText function. This takes the formatting from your field in FileMaker

Set Variable [ $r ; Value: MBS( "XL.Sheet.CellWriteStyledText"; $Book; $Sheet; 1; 6; DoorThirteen::Text ) ] 

If you now want to write this Excel document to a file, you can use the XL.Book.SaveToFile function to write the document from the working memory to a file. If you want to place the file in a container in your database, please use XL.Book.Save.

Set Variable [ $r ; Value: MBS( "XL.Book.SaveToFile";$Book; "/Users/sj/Desktop/AdventXl.xlsx" ) ] 

So that the memory is again free and can continue to be used, call XL.Book.ReleaseAll to remove all Excel working environments. If you only want to remove a specific one, use the XL.Book.Release function and specify the reference in the parameters.

But we can not only create files, we can also load existing files. To do this, we use the XL.LoadBook function, which gives us an Excel file that is located in a file at a specific path or in a container. As a return we then get the reference number with which we can continue working.

Set Variable [ $Book ; Value: MBS( "XL.LoadBook"; "/Users/sj/Desktop/AdventXl.xlsx") ]

Just as we have different functions for writing values, we also have different functions for reading cells. With the XL.Sheet.CellReadValue we have a function that reads the value from the cell and returns a value of the corresponding type. We also have the appropriate function for each individual type.

Set Variable [ $A2 ; Value: MBS("XL.Sheet.CellReadText"; $Book; 0; 1; 0) ] 
Set Variable [ $B2 ; Value: MBS("XL.Sheet.CellReadNumber"; $Book; 0; 1; 1) ] 
Set Variable [ $C2 ; Value: MBS("XL.Sheet.CellReadNumber"; $Book; 0; 1; 2) ] 
Set Variable [ $D2 ; Value: MBS("XL.Sheet.CellReadFormula"; $Book; 0; 1; 3) ] 

The Componte XL has many more cool functions available, just take a look at the documentation or try out our examples. I wish you lots of fun with your tables.