XLLOOP

XLLoop is an addin, written in c++, that is used as a layer between Excel and java server. XLLoop is licensed under the Common Public License (CPL).

A short overview and description of configuration parameters is presented in xlloop-about (and is copied as a local link in xlloop-about (local)). The short guide lists configuration parameteres for the XLLoop configuration .ini file and provides a summary of servers implemented in different programming languages.

XLLoop Project Overview

The project source files are groupped into three folders: (i) common folder that include various utility classes such as converter class, log class, dictionary class, etc.; (ii) xll folder which includes the classes, which implement connection between Excel and java server (such as BinaryProtocol.cpp, HttpProtocol.cpp, and JasonCodec.cpp classes) and calsses that implement interaction between java and Excel (such as XLCodec.cpp, XLUtil.cpp, and SheetGenerator.cpp classes); and (iii) yajl folder which contains some supporting c code. The last folder is used a linked library in the original xlloop project. In the current project the yajl library source files were adde to the project and recompiled.

The project output is Xlloopkr.xll library. The project key library dependencies include xlcall32.lib and and java jdk1.8.0. The xlcall32.lib library should be obtained from Microsoft website (listed below) and java development kit (jdk) should be obtained from Oracle jdk1.8 download website (if it has not been installed). The path to the jdk folder is specified then in visual studio project "project properties>general>include directories" (for example as C:\Program Files\Java\jdk1.8.0_40\include; C:\Program Files\Java\jdk1.8.0_40\include\win32;). Visual studio may complain about other missing libraries (such as, for example, winhttp.lib or winsock32.lib). You need to identify the library location in your system and link them to the visual studio project. The original project includes also msvcrt.lib in the project lib/ folder. The declaration of the exported functions is performed in the Xlloopkr.def file.

XLL Overview

XLL is a DLL-file that is created by C or C++ and then compiled with an interface that allows the functions to be access with XL via XL API. The xll library uses the xlcall32.lib library to interact with Excel and must implement the corresponding interface (export certain functions). (Simple dll libraries can be used within Excel but can't call back to Excel. Therefore they are one-way directional and can be used only from within Excel. The xll libraries can access Excel cells and use Excel data).

The static xlcall32.lib library is downloadable from Microsoft as part of the Microsoft Excel 2013 XLL SDK, which includes both 32-bit and 64-bit versions of this library. The process of enabling dlls to call back to Excel is described in this MSDN: creating XLLs overview and is summarized here.

Excel 2013 SDK

Excel 2013 SDK can be downloaded at Excel 2013 SDK: Excel 2013 XLL Software Development Kit. Note that the xlcall32.lib library should be updated whenever your system was updated. Otherwise the visual studio may fail to build the project.

Visual Studio Project Overview

The project was rebuild using Visual Studio 15. The list of project parameters is provided in this Visual Studio parameters file. The project must be linked to Microsoft SDK libraries (check C:\Program Files (x86)\Microsoft SDKs\Windows folder for installed SDK or download it from). After installation you may also need to add Microsot Foundation Classes (Control Panel->Programs and Features->Microsoft Visual Studio-> Change->Modify->Add Microsoft Foundation Classes).

Path to visual studio exe file: C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE

Xlloop addin execution logs: C:\Projects\java\data\jmc\excel\xlloop\log\xlloop-alexa.log. The file path is set in the xlloop .ini configuration file.

XLLoopkr.cpp methods

The list below summarizes the key methods of the XLLoopkr.cpp class.

1. void InitializeSession(int index). Initialize a new session with the server. The method supports using user login / password for authorization on the server.


2. bool InitProtocol(int index). Create a new protocol object: binary (BinaryProtocol) or html (HttpProtocol). Test conncetion using the protocol and, if there is no connection, then establish new connection with the server and create new sesion (by calling the above InitializeSession method).


3. void RegisterFunctions(LPXLOPER xDLL, int index). Ask the server for a list of functions and register them (register function name, help, category, argument help, and other parameters).


4. void RegisterServer(LPXLOPER xDLL, int index). Regsiter FSExecute and FSExecuteVolatile functions that allow to run server functions and then call RegisterFunctions method above to ask for server functions and regsiter them in Excel.


5. void ParseProviders(char* providers). Regsiter each server if there are multiple server providers (??).


6. Declare and export Excel functions. Seems like currently the addin allows to declare only 511 Excel functions (to be tested). If true, the number can be easily extended by adding the DECLARE_EXCEL_FUNCTION(number) code lines at the end of the Xlloopkr.cpp file
.

Added modifications to the XLLoop code

The list below summarizes the changes tht were added to the main Xlloopkr.cpp class.

1. Automatic start of the server.


2. Registration of Excel functions.

Summary of Visul Studio Configuration

The developement steps can be summarized as follows:

1. Identify your windows operating system (for example windows 8 or windows 10);


2. Download Microsoft SDK consistent with you system (the SDK contains the libraries necessary to build the project)


3. Download Microsoft Excel 2013 XLL SDK and retrieve xlcall32.lib library (x86 and x64 versions);


4. Set up Visul Studio project configuration parameters (for a detailed list of parameters see Visual Studio configuration notes).
   
   
   1. Set up the paths to the Windows SDK library folders;
   
   
   2. Set up the path to the project library folders that contains xlcall32.lib library;
   
   
   3. Select project output as a dll library file;
   
   

FAQ and Troubleshooting

• XLL OOP Add-in
  
  
  1. Load addin. Often after you load addin but the name is not picked from the configuration file, the adding loading fails and after you reopen Excel, you will see the following message: "The file format and extension of .xll don't match. The file could be corrupted or unsafe. Unless you trust its source don't open it. Do you want to open it anyway".
     
     That message usually means:
     
     
     1. Either the .xll is built for the wrong bitness (32-bit or 64-bit) compared to the version of Excel (only an issue with Excel 2010), or
     
     
     2. Some dependency of your .xll is not being loaded, sometimes the C runtime version, otherwise some .dll that it needs. Basically your .xll is crashing when it loads.
     
     
     For the Visual Studio configuration parameters required to build the .xll that is recognized by Excel see Visual Studio configuration notes.
  
  
  2. Update / remove the addin: After addin was added to Excel files, there is no direct option to remove the addin from the list. To remove the addin, you need either to delete the related .xll file or move it to a different folder. After Excel fails to find it, it will ask you whether to remove the addin from the list. After the addin was removed from the list, you can add a new version of the addin.
  
  
  3. Find addin path: The addin list does not allow you to see from which folder the addin was added. The paths to installed addins can be identified using this Excel addins.xlsm file with simple VBA code, which lists the paths to all installed Excel addins.
  
  


• Java Server
  
  
  1. Returned objects types: some object types were identified that are not calculated and transferred correctly between Excel and java server.
     
     
     1. One example of such object type is jkr.datalink.iLib.data.math.sets.ISetDiscreteRn. The SetDiscreteRn that implements the interface also extends java standard LinkedHashSet class. If multiple instances of the set object with different set parameters are calculated in the Excel workbook, the addin may return a single copy for all different set objects. The addin picks at random a single copy of the multiple created set objects that is returned as output of each function that builds the set object. Basically it is not possible to create directly multiple different set objects within an Excel file. The problem is illustrated in this test sets.xlsm file.
        
        A simple workaround for the problem is to return a set wrapper object instead of the set object. It is likely that the problem exists only for a few specific java types (or possibly only for java set objects) but we did not investigate further the roots of the problem.
     
     
     2. Another example is AC.Data.list_x(Object[] range, ...) function that may return a String value instead of the ArrayList object. If the function is specified as FS("AC.Data.list_x", Object[] range, ...), then the method returns the correct ArrayList object.
     
     
  
  

A L E X A   NOTES