You may have seen that we wrap LibXL into a plugin for Xojo: The MBS Xojo XL Plugin. We support all the built-in features from LibXL like read/write Excel documents. That includes older XLS or newer XLSX format as well as templates. But we add a few of our own developments related to Xojo around that. Let us show you.
We have eight classes for you:
- XLBookMBS is the base class for a document.
- XLSheetMBS describes the individual sheets in an Excel document.
- XLFormatMBS defines the formatting of a cell, which may include a font definition.
- XLFontMBS defines a font specification. That may be as simple as bold or a color.
- XLFilterColumnMBS defines filter options for a column.
- XLAutoFilterMBS provides the properties for auto filter options.
- XLCopyOptionsMBS are used for our copy functions to define what to copy.
- XLRichStringMBS allows access in detail on styled text.
We don't just provide you functions to use rich text with XLRichStringMBS class. We got further and allow you to convert the rich text in Excel into the styled text as Xojo uses it. StyledText function on the class will convert the content to StyledText and return it, so you can directly assign it to a TextArea control. With the AddStyledText method you can add text from a TextArea control into a new XLRichStringMBS object.
For your convenience the XLSheetMBS class has a method WriteStyledText, where you can directly pass StyledText from TextArea control to assign styled text to a cell. Our ReadStyledText function will read a cell containing rich text and translate to StyledText object for Xojo.
We have functions to copy rows from one sheet to other (or same) sheet. Similar you can copy a column in one sheet to another sheet, which can be in the same or other book. If you like you can also copy the whole sheet content from one sheet to another or to copy a sheet to another book. Since copy functions can copy from one book to another, you can convert from XLS to XLSX and back if needed.
Date and DateTime
To use Xojo Date and DateTime objects, we have functions in XLSheetMBS class. You can write a date to a cell with WriteDate function and pass a DateTime with WriteDateTime function. Please make sure your cell has a format showing the value as date with the desired formatting. If needed, just pass format with the call.
To read you can use ReadDate and ReadDateTime functions which translate the numeric timestamp in Excel to a Xojo Date or DateTime object.
In general LibXL handles colors in either index mode or RGB mode. But when using RGB mode, you can set and get colors with the color data type. Just use the ColorRGB property on the XLFontMBS class.
Loading or saving an Excel document can take some time. For this reason we provide six MT functions, optimized for being used in a Xojo thread, so they yield time to other threads.
We have a few convenience functions to read cells in a sheet as variant. Instead of reading values as number, text, boolean or date, you can get it as variant.
An XLBookMBS can either be in XML mode or not. With XML mode you edit a XLSX document and otherwise you edit the older XLS format. When loading documents with shared Load methods, we figure out the format and initialize the right book object and load the value.
The plugin is built with the current LibXL library at the time of building the plugin. If there is a newer release coming of the library and you like to use it, we have a way for you: Download the newer library and use LoadLibrary functions in XLBookMBS class to load it. The plugin will stop using the built-in library.
Similar you can of course load an older version, but please be cautious. Calling new functions may cause a crash as the plugin may expect them where they may not be.
Let us know if you have any questions or another function may be missing for you.