LibXL


LibXL is a library for reading from and writing to Excel files in ACP. Microsoft Excel does not work reliably on server computers, so you need to use LibXL in ACP models instead of Analytica's built-in Excel functions. You can use the SS Lib XL.ana Analytica library which has a set of functions using LibXL that parallel Analytica's spreadsheet functions.

LibXL is already installed on Lumina ACP servers. If your organization has its own ACP server, you may need to install it on it. You can also install LibXL on your own desktop computer (along with SS Lib XL.ana) for use in developing an ACP application to use it.

Note: This guide is currently only for Lumina developers until we complete implementation and docs for LibXL.

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. 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: LibXLCOM 2 - download then extract the files from this archive into a directory on the computer where the component is to be used.

Any directory can be used eg :c:\libxlcom.

Note: You will need to ensure that there are no instances of libxl running. (Incase this is an update to an earlier build)

  • Open task manager
  • Under 'Details' look for any LibXLCOM.exe instances running
  • Kill any instances that are running
Kill LibXL task.png

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

LibXLCOM.exe /RegServer

Note: Launch the command prompt explicitly as an admin. Eg right click on CMD and select run as administrator. Even if you are an admin user. If you don't launch as an Administrator then the registration may not be effective for all users.

If there is no error then the registration should have worked.

To test the installation you can use this model and spreadsheet. Place both in the same Acp project folder and open the model - then evaluate the node Test Reading Workbook. A successful result is either a com object t or an xlsx workbook.

DCOM settings

If you get an access permission error when launching, go into DCOMCNFG, find “XL class”, go into Properties.

  • Launch & Activation permissions, set to custom and make sure Allow local Launch and local Activation is enabled for the user running ACP.
  • Identity tab. Normally here you should set the user to be the Launching user (which in ACP is the AcpUser).

More configuration

If you are don't get a successful Libxl result from this test model, you can look into further configuration options here Debugging the LibXL installation.

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.

Note: 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 included LibXL methods:

Null AddSheet(sheetName : text ; index : integer )

Adds a new sheet to the workbook as the «index»th sheet. When «index» is 1, the new sheet is the first sheet, when «index» is 0 or exceeds the number of sheets, it is the last sheet. When index is negative, it will have -«index» sheets after it.

Value GetCellValue( sheetNum, col, row : integer )

Returns a value from a single cell identified by the sheet number, column number and row number. Array abstracts for reading a range.

Boolean load(filename : Text)

Loads an Excel file into memory. Be sure to supply a full, absolute path. Returns true if successful. Sets the errorMessage property when there is a problem.

Array NamedRanges( sheetNum,flags : integer )

Returns a list of the named ranges in the «sheetNum» worksheet, or all named ranges when «sheetNum» is 0.
«Flags» can have the following bits added together:
1 = Include hidden named ranges
2 = Ranges with global scope only.

Array RangeToPos( range : Text )

Returns the coordinates of a range as a 1-D array with five elements:
[sheetNum,col1,row1,colN,rowN].

Array ReadValues( range : Text )

Returns a 2-D array containing the values in «range».

Array ReadValuesByPos( sheetNum, col1, row1, colN, rowN : Integer)

Returns a 2-D array containing the values within the indicated rectangle, specified by its upper left and lower-right integer position. All parameters are integers.
Examples:
COMCallMethod(wb,"ReadValuesByPos",1,1,1,IndexLength(Col),IndexLength(Row),resultIndex:Col,Row)
or
wb->ReadValuesByPos(1,1,IndexLength(Col),IndexLength(Row))
Speed: Reading numbers only, timing test returned 1.8M cells/second

Boolean SaveAs(filename)

Saves the in-memory spreadsheet to a file. «filename» must be provided and should be an absolute path name.

Null SetCellValue(sheetNum,col,row : integer ; value )

Sets a single cell value, where the cell is specified by sheet number, column number and row number.

Null SetNamedRange( rangeName : text ; sheetNum, col1, row1, colN, rowN, flags : integer )

Adds or sets a named range in a given sheet. The flags=1 bit makes it a global named range, otherwise it is local to the sheet.

Null SetValues( range : text ; values : array )

Writes a 2-D array of cell values to a «range».
Must values be numbers and an array of the same size as range? Can’t you set a text, or a value to one cell?

Null SetValuesByPos( sheetNum, col1, row1, colN, rowN : Integer ; values : Array )

Writes a 2-D array to a rectangle identified by its upper-left and lower-right corners. All parameters are integers.
Example:
wb->SetValuesByPos(1,1,1,IndexLength(Col),IndexLength(Row), COMArray( Data, Col, Row ) )
Speed: Timing test writing numbers: 490K cells/second

Array Sheets()

Returns a 1-D array of sheet names.
Comments


You are not allowed to post comments.