Introduction

The XlsxToolBx library of functions allows to handle .xlsx files (Excel 2007 and later versions) or .xls (Excel 97/2003).

It is used to fully create a workbook or handle an existing workbook.

This library is available for Visual Adelia, Adelia Web and Adelia Cloud.

Limitations

The XlsxToolBx does not work with localized functions. This means that the function names in formulas and cell formats have to be expressed in English.

Configuration

The XlsxToolBx is available for Visual Adelia, Adelia Web and Adelia Cloud. It is based on a Java library for handling Excel Apache POI workbooks (https://poi.apache.org/spreadsheet).

The Windows version of the library is in fact implemented in the form of a proxy using the Adelia client/server to directly call the Java version via Middleware services.

Configuration for Java version

Concerns Adelia Web, Adelia Cloud and Visual Adelia versions generated in Java. The XlsxToolBx is natively supported in this configuration. No action is required to put it in place.

However, the library used takes up a relatively large amount of memory and it is best to increase the available memory space to handle large documents (parameters -Xms and -Xmx, recommended values -Xms256m -Xmx1024m for JVM 32bits).

Configuration for Windows version

Concerns Visual Adelia programs generated in C.

In this configuration, the program uses a Middleware service using the logical server *DOC_SERVER.

You must configure this logical server in your client configuration.

The physical server must point to a Java daemon.

Caution: if processing is moved, the file names (open and save functions) will be relative to the server.

Use

This section introduces the operating principles of the XlsxToolBx library.

Object handle

A handle is a reference to an object which can be handled by the toolbox. It is represented in an Adelia program by a NUM_BIN_4-type numerical value.

Unlike the DocxToolBx, each cell in an Excel document is individually addressable (sheet name + row / column).

The XlsxToolBx uses only one handle to reference the workbook itself, and does not provide function-handling handles.

All the XlsxToolBx handling functions take as a parameter the handle of the document to which they apply.

For example, opening and closing a document:

CALL_DLL 'XlsxToolBx' 'WorkbookOpen' fileName workbookHandle rc

CALL_DLL 'XlsxToolBx' 'WorkbookClose' workbookHandle rc

Cells Manipulation

An Excel workbook is divided into a set of sheets which are themselves 2D arrays containing cells.

The XlsxToolBx allows you to directly handle cells or cell ranges within a sheet in the workbook.

Generally functions for retrieving information (XXXGetXXX functions) address only the first cell in the specified range, while editing functions (XXXSetXXX) make it possible to apply the processing to all cells in the specified range.

Cell ranges must be specified in the form A1: C3 (column A row 1 to column C line 3).

The range may include the name of the spreadsheet referenced: the selection is then of the form Sheet1! A1: C3.

If the name of the worksheet is omitted, the selection is considered to relate to the active sheet of the workbook.

You can process multiple cell ranges simultaneously.

In this case, separate ranges by the ';' character, for example: Sheet1!A1:C3;Sheet2!A1:C3.

Example: to change the font of the first cell of the sheet Sheet1

CALL_DLL 'XlsxToolBx' 'StyleSetFont' documentHandle 'Sheet1!A1' 'Comic Sans MS' rc

Classes

The XlsxToolBx functions are grouped into classes defined by the function's scope of application.

The classes are:

Class	Description
Workbook	This class provides Excel workbook management functions.
Sheet	This class provides workbook sheet management functions.
Selection	This class provides calculation and cell data management functions.
Style	This class provides cell style management functions.
Image	This class provides image management functions.