LibXL

Note: This guide is currently only for Lumina developers.

What is it?

LibXLCOM is a wrapper of the LibXL library that can be used from an Analytica model via COM Integration to read and write cell data to an Excel spreadsheet without Excel in the loop.

I explored this avenue as a possible option for bypassing Excel in SVET and other ACP applications. I purchased the LibXL library, and was hoping it might be something that could be incorporated into Analytica/ADE directly one day. Currently, that doesn’t look likely.

It is experimental, ready for experimentation to see if this COM wrapper might at least serve as an interim solution until we have something better.

What it can do

You can use the component to load an *.XLSX or *.XLS file. Once loaded, you can read values from individual cells or from a rectangular range (including a named range), and can write values to cells. You can save the in-memory data back to a *.XLSX or *.XLS file.

Limitations

Date values: When writing a date-number or date-time-number to a cell that contains a non-date, a number is written and the cell is not changed to a date type. Likewise, when writing a number to a cell that contains a date, the cell remains a date (corresponding to the number of days since 1901).
- This limitation exists because of serious bugs encountered in LibXL when I attempted to toggle the date/number type.
No formats: Currently I have not exposed any reading or changing of cell formats. It just reads/writes cell values.
Can’t change file type: If you read a *.XLSX file, you must save it as a *.XLSX file -- you can’t change it to a *.XLS format. Similarly, *.XLS files must remain *.XLS.
No Excel Engine: The library reads/sets the values of each cell, but does not include any computational engine. So changing an input cell will have no effect on values in other cells.

Installation

1. Copy the following two files into the same directory on the computer where the component is to be used.

2. Start CMD as administrator, CD to the directory containing this file, and type:

LibXLCOM.exe /RegServer

If you get an access permission error when launching, go into DCOMCNFG, find “XL class”, go into Properties / Launch & Activation permissions, and make sure Allow local Launch and local Activation is enabled.