LibXL for FileMaker with extras

[MonkeybreadSoftware]

For a few years we have worked on integrating the LibXL library and making functionality available for FileMaker. Beside all the standard built-in features from LibXL to read/write Excel files, we have some extra functionality developed ourselves:

Copy Cells

LibXL comes with a copy cell command, see XL.Sheet.CopyCell. Beside that we added XL.CopyRow to copy content of a row. Similarly XL.CopyColumn copies a column. As you can pass different book reference numbers, you may even copy from one document to another one or copy from one sheet to another sheet within the same document. 

The XL.CopySheet function can copy the content of a sheet to another document, great if you like to extract one sheet from one document and copy it to a new document. Finally we made XL.CopyContent to copy the content of all sheets into a new document. That helps to convert between XLS and XLSX, older and newer Excel formats in both directions.

TimeStamps

Since FileMaker uses timestamps, we have XL.Date.DoubleToTimeStamp and XL.Date.TimeStampToDouble functions to convert the numeric time values in Excel to/from timestamps in FileMaker. 

The XL.Sheet.CellWriteDate function writes a timestamp or date from FileMaker directly into a cell. You still need to pass a format parameter referencing the date format to show the value later. 

Styled Text

Text fields in FileMaker may contain styles. Our plugin can translate the styles to/from Excel representation for you. Just read a styled cell with XL.Sheet.CellReadStyledText and you get styles back in the FileMaker format. 
With XL.Sheet.CellWriteStyledText function the MBS FileMaker Plugin can take your styled text from FileMaker and convert styles to Excel format. The styled text then is placed into the cell. 

Values

Instead of having functions for number, text and date, you can also use XL.Sheet.CellReadValue function to read a cell as value. We convert the value to FileMaker format and return it. For example we return a numeric value as number and a text value as a text. Dates are converted to FileMaker timestamps. Empty or blank cells give an empty value.

Batch Processing

To read a lot of cells at once, you can use XL.Sheet.CellReadComments, XL.Sheet.CellReadFormulas, XL.Sheet.CellReadNumbers and XL.Sheet.CellReadTexts to get a range of cells as list of values. 

To fill a lot of cells at once, you can use XL.Sheet.CellWriteBlanks, XL.Sheet.CellWriteBooleans, XL.Sheet.CellWriteComments, XL.Sheet.CellWriteDates, XL.Sheet.CellWriteFormulas, XL.Sheet.CellWriteNumbers and XL.Sheet.CellWriteTexts. Please pass a list of values for the relevant type.

Our XL.Sheet.GetText function collects all texts from all cells in a sheet and returns it as one big text block. Great for storing in a field and use for full text search in a media database. Same does XL.Book.GetText function, but for the whole document.

Pictures

When calling XL.Book.AddPictureContainer, we take the picture from the container and pass it. When you query a picture with XL.Book.GetPicture function, we provide the picture as container value with preview, if possible.

Load documents

When you start with loading a document, we do our best to decide whether this is an old or new style document and whether it may be a template document. So you don't need to tell whether you need a XLS or XLSX object beforehand. We also handle passing container data with the Excel document to LibXL via our functions. 

Utility functions

We have a few helper functions. For example XL.Book.SheetIndexForName looks up the index for a sheet by name. The function XL.Book.DeleteSheetsExcept can delete all sheets except the one you want to keep.

Let us know if you have any questions or another function may be missing for you.

See also How to use XL functions with MBS FileMaker Plugin, New column names in an Excel document and Use formatted text in Excel files with FileMaker and LibXL