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.
- 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.
1: LibXLCOM 2 - extract this archive into a directory on the computer where the component is to be used.
(With build LibXLCOM 2, the dll is named libxl64.dll - rename it to libxl.dll first or it will not registger)
2. Start CMD as administrator, CD to the directory containing this file, and type:
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 );
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.,
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.
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!”
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:
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.
- Speed: Reading numbers only, timing test returned 1.8M cells/second
- 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.
wb->SetValuesByPos(1,1,1,IndexLength(Col),IndexLength(Row), COMArray( Data, Col, Row ) )
- Speed: Timing test writing numbers: 490K cells/second
- Returns a 1-D array of sheet names.
Enable comment auto-refresher