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.

• LibXl.dll
• LibXlCOM.exe

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.

How to use

Recommended: Use the attached Analytica library (see Reference section) to do all of the following. For those that want to start from scratch, follow the directions below:

Use the following Analytica code to instantiate the object and load a spreadsheet.

Var xl := COMCreateObject("LibXLCOM.XL" );

Var not (xl->load( Filename ))

Then MsgBox("Unable to open Excel file " &

Filename & chr(13) & xl->errorMessage );

xl

The result is a «XLS.Workbook» COM object. To free up the memory consumed by it, just invalidate all variables holding a copy of it.

Once you have the xl instance, use the methods and properties in the Reference section to read/write from it. As per the Analytica COM integration convention, use without parens when accessing a property, e.g.,

xl->errorMessage

And use with parens when calling a method, e.g.,

xl->load( “C:\DataFolder\ABC.XLSX” )

If you want to specify the result indexes for the result of a method that returns an array, call using, e.g.,

COMCallMethod( xl, “ReadValues”, “Sheet1!A5:D10”,

resultIndex: Col, Row )

When passing an array to an array parameter, use the COMArray function, e.g.,:

xl->SetValues( “Sheet1!A5:D10”, COMArray( X, Row, Col ) )

When reading, the Excel data type (bool, text, number, date, null/empty) is mapped to the corresponding Analytica data type.

When writing, the Analytica data type is mapped to the Excel data type with the exception of the Number/Date distinction. The Number/Date type for the cell, as it was read from the file, is not changed when you write a number or date.

Method parameters use a (Col,Row) order convention.

Reference

Properties and methods of an «XLS.Workbook» object are listed below. The return value’s data type is indicated in front of the property or method’s name. Null means it has no return value. Parameter data types are shown in pseudo-Analytica parameter qualifier style.

Parameters named «range» accept:

• Single cell, e.g., “Sheet1!B5” or “B5”
• Range of cell, e.g., “Sheet1!B5:F20”
• The name of a named range, “SomeRangeName”
• Named range on a given sheet: “Sheet3!SomeRangeName”
• A sheet only, suffixed with “!”, e.g., “Sheet2!”

Methods

Most of the built-in Analytica spreadsheet functionality has been recreated in this library:

For things this library doesn't cover, here are the following LibXL methods: