XLSX module

This module implements the XLSX (Microsoft Excel) format, allowing you to read and write Excel spreadsheets.

Note

To use the features of this module, you have to put a special import statement at the begining of your HXM file: use xlsx;

Module functions

xlsx.open(fileName)

Open an XLSX file and return an XlsxDocument, which provides access to all sheets and their contents. An exception is thrown if the file does not exist, is not accessible or it is not a valid XLSX file.

Parameters:

fileName (String) – Path to the XLSX file.

Return type:

XlsxDocument

xlsx.create(fileName)

Create a new XLSX file and return an XlsxDocument, which provides access to all sheets and their contents. This will overwrite any existing file at the specified fileName path. The file is created even if the user does not save it (e.g. with XlsxDocument.save()). An exception is thrown if fileName is incorrect.

Parameters:

fileName (String) – Path to the XLSX file.

Return type:

XlsxDocument

Classes

class XlsxDocument

Represents an Excel workbook.

getSheet(sheetNumber)
getSheet(sheetName)

Retrieves a sheet from the workbook by its index or by its name. If an integer is specified, the index must be positive or zero and must be strictly smaller than the number of sheets in the workbook. If a string is specified, a sheet with the same name must exist in the workbook. Otherwise, an exception will be thrown. The sheet name is case-sensitive.

Parameters:

  • sheetNumber (int) – Index of the sheet (starting at 0).

  • sheetName (String) – Name of the sheet.

Return type:

XlsxSheet

getSheetNames()

Returns a map of all sheet names in the workbook, indexed by their position, starting at 0.

Return type:

Map (array of strings)

createSheet(sheetName)

Creates a new sheet in the workbook with the specified name. The new sheet is appended at the end of the workbook. Retuns the instance of the XlsxSheet created. An exception is thrown if a sheet with the specified sheetName already exists.

Parameters:

sheetName (String) – Name of the sheet.

Return type:

XlsxSheet

deleteSheet(sheetNumber)
deleteSheet(sheetName)

Deletes a sheet from the workbook by its index or by its name. If an integer is specified, the index must be positive or zero and must be strictly smaller than the number of sheets in the workbook. If a string is specified, a sheet with the same name must exist in the workbook. Otherwise, an exception will be thrown. The sheet name is case-sensitive.

An Excel workbook must contain at least one sheet. Therefore, deleting the last sheet of a workbook will also throw an exception.

Parameters:

  • sheetNumber (int) – Index of the sheet (starting at 0).

  • sheetName (String) – Name of the sheet.

save()

Saves changes made to the workbook back to the original file. The file name used is the one entered when the workbook was opened or created.

saveAs(fileName)

Saves changes made to the workbook as a new XLSX file at the given path. This will overwrite any existing file at the specified fileName path.

Parameters:

fileName (String) – Path to the XLSX file.

class XlsxSheet

Represents a single worksheet from an Excel workbook.

Note

Cells can be identified either by their Excel address (such as “A1” or “B2”), or by their column and row number (0-based). The valid range for cell positions in an Excel sheet is:

  • Columns: [0, 16383] (0-based, corresponding to columns A to XFD)

  • Rows: [0, 1048575] (0-based, corresponding to rows 1 to 1048576)

An exception is thrown if you try to access cells outside these ranges.

getValue(cellAddress)
getValue(columnNumber, rowNumber)

Gets the value of the cell using Excel-style cell adressing (e.g. “A1”, “B2”) or with the specified column and row.

The type of value returned depends on the cell’s native type. It can be a string, a boolean, an integer, a double or nil. Nil is returned if the cell contains no value.

Parameters:

  • cellAddress (String) – Address of the cell.

  • columnNumber (int) – Index of the column (starting at 0).

  • rowNumber (int) – Index of the row (starting at 0).

Return type:

String, int, double, bool or nil

setValue(cellAddress, value)
setValue(columnNumber, rowNumber, value)

Sets a value to the cell using Excel-style cell addressing (e.g. “A1”, “B2”) or at the specified column and row.

Parameters:

  • cellAddress (String) – Address of the cell.

  • columnNumber (int) – Index of the column (starting at 0).

  • rowNumber (int) – Index of the row (starting at 0).

  • value – Value to write. Only strings, integers, doubles, booleans and nil are accepted.

getBoolValue(cellAddress)
getBoolValue(columnNumber, rowNumber)

Gets the boolean value of the cell using Excel-style cell adressing (e.g. “A1”, “B2”) or with the specified column and row. An exception is thrown if there is no value or if the value is not a boolean.

Parameters:

  • cellAddress (String) – Address of the cell.

  • columnNumber (int) – Index of the column (starting at 0).

  • rowNumber (int) – Index of the row (starting at 0).

Return type:

bool

setBoolValue(cellAddress, value)
setBoolValue(columnNumber, rowNumber, value)

Sets a boolean value to the cell using Excel-style cell addressing (e.g. “A1”, “B2”) or at the specified column and row.

Parameters:

  • cellAddress (String) – Address of the cell.

  • columnNumber (int) – Index of the column (starting at 0).

  • rowNumber (int) – Index of the row (starting at 0).

  • value (bool) – Value to write.

getIntValue(cellAddress)
getIntValue(columnNumber, rowNumber)

Gets the integer value of the cell using Excel-style cell adressing (e.g. “A1”, “B2”) or with the specified column and row. An exception is thrown if there is no value or if the value is not an integer. In particular, any call to this function on a cell with a decimal part will return an exception. Use getDoubleValue() for this.

Parameters:

  • cellAddress (String) – Address of the cell.

  • columnNumber (int) – Index of the column (starting at 0).

  • rowNumber (int) – Index of the row (starting at 0).

Return type:

int

setIntValue(cellAddress, value)
setIntValue(columnNumber, rowNumber, value)

Sets an integer value to the cell using Excel-style cell addressing (e.g. “A1”, “B2”) or at the specified column and row.

Parameters:

  • cellAddress (String) – Address of the cell.

  • columnNumber (int) – Index of the column (starting at 0).

  • rowNumber (int) – Index of the row (starting at 0).

  • value (int) – Value to write.

getDoubleValue(cellAddress)
getDoubleValue(columnNumber, rowNumber)

Gets the double value of the cell using Excel-style cell adressing (e.g. “A1”, “B2”) or with the specified column and row. An exception is thrown if there is no value or if the value is not a number (neither a double nor an integer).

Parameters:

  • cellAddress (String) – Address of the cell.

  • columnNumber (int) – Index of the column (starting at 0).

  • rowNumber (int) – Index of the row (starting at 0).

Return type:

double

setDoubleValue(cellAddress, value)
setDoubleValue(columnNumber, rowNumber, value)

Sets a double value to the cell using Excel-style cell addressing (e.g. “A1”, “B2”) or at the specified column and row.

Parameters:

  • cellAddress (String) – Address of the cell.

  • columnNumber (int) – Index of the column (starting at 0).

  • rowNumber (int) – Index of the row (starting at 0).

  • value (double) – Value to write.

getStringValue(cellAddress)
getStringValue(columnNumber, rowNumber)

Gets the string value of the cell using Excel-style cell adressing (e.g. “A1”, “B2”) or with the specified column and row. An exception is thrown if there is no value or if the value is not a string.

Note in particular that numbers are not converted to strings, and that an empty cell is not equivalent to an empty string. In these cases, an exception will also be thrown.

Parameters:

  • cellAddress (String) – Address of the cell.

  • columnNumber (int) – Index of the column (starting at 0).

  • rowNumber (int) – Index of the row (starting at 0).

Return type:

String

setStringValue(cellAddress, value)
setStringValue(columnNumber, rowNumber, value)

Sets a string value to the cell using Excel-style cell addressing (e.g. “A1”, “B2”) or at the specified column and row.

Parameters:

  • cellAddress (String) – Address of the cell.

  • columnNumber (int) – Index of the column (starting at 0).

  • rowNumber (int) – Index of the row (starting at 0).

  • value (String) – Value to write.

getDateTimeValue(cellAddress)
getDateTimeValue(columnNumber, rowNumber)

Gets the datetime.DateTime value of the cell using Excel-style cell adressing (e.g. “A1”, “B2”) or with the specified column and row. An exception is thrown if there is no value or if the value cannot be read as a float.

Note that Excel has no true date type. Internally, Excel stores dates and times as floating point numbers, where the integer part represents the number of days since January 1, 1900 and the fractional part represents the time of day. For example, 1.0 corresponds to 1900-01-01 00:00:00, while 2.5 corresponds to 1900-01-02 12:00:00.

Parameters:

  • cellAddress (String) – Address of the cell.

  • columnNumber (int) – Index of the column (starting at 0).

  • rowNumber (int) – Index of the row (starting at 0).

Return type:

datetime.DateTime

setDateTimeValue(cellAddress, value)
setDateTimeValue(columnNumber, rowNumber, value)

Sets an instance of datetime.DateTime to the cell using Excel-style cell addressing (e.g. “A1”, “B2”) or at the specified column and row. The cell will be formatted as a Date cell.

Parameters:

  • cellAddress (String) – Address of the cell.

  • columnNumber (int) – Index of the column (starting at 0).

  • rowNumber (int) – Index of the row (starting at 0).

  • value (datetime.DateTime) – Value to write.

readRangeValues(columnNumber, rowNumber, nbCols, nbRows)
readRangeValues(topLeftAddress, bottomRightAddress)
readRangeValues(rangeAddress)

Reads a set of rows and columns and returns it as a map of maps. This method is the preferred way to retrieve a large volume of data from an Excel workbook. It’s much faster than calling individual methods such as getValue().

The range can be specified in 3 different ways:

  • Either by providing the coordinates of the first cell (0-based) followed by the number of columns and rows.

  • By providing the coordinates of the top-left cell and the bottom-right cell in Excel notation.

  • Or by providing a range in Excel notation, i.e. 2 coordinates separated by a colon, e.g. “A2:C18”.

The first dimension of the returned map of maps contains the rows read. The first dimension contains as many elements as the number of rows requested, regardless of whether the rows are empty or not.

The second dimension contains the cells read for each row, with one value per column. The type of each value read depends on the cell’s native value. The map of maps can therefore contain a combination of booleans, integers, doubles or strings.

When a cell is empty, no value is stored in the map, but the corresponding key in the map is incremented by 1. This is in conformity with the map rules, which make no distinction between an unassigned key and a key whose associated value is nil.

When a cell contains an error in Excel (like #NAME? or #DIV/0!), these errors are reported as a Not A Number value (nan in HXM).

Parameters:

  • columnNumber (int) – Index of the starting column (starting at 0).

  • rowNumber (int) – Index of the starting row (starting at 0).

  • nbCols (int) – Number of columns to read.

  • nbRows (int) – Number of rows to read.

  • topLeftAddress (String) – Address of the first top-left cell.

  • bottomRightAddress (String) – Address of the last bottom-right cell

  • rangeAddress (String) – Range to read in Excel notation (e.g. A2:C18)

Return type:

Map

writeRangeValues(columnNumber, rowNumber, nbCols, nbRows, values)
writeRangeValues(topLeftAddress, bottomRightAddress, values)
writeRangeValues(rangeAddress, values)

Writes a set of rows and columns. This method is the preferred way to write a large volume of data to an Excel workbook. It’s much faster than calling individual methods such as setValue().

The range can be specified in 3 different ways:

  • Either by providing the coordinates of the first cell (0-based) followed by the number of columns and rows.

  • By providing the coordinates of the top-left cell and the bottom-right cell in Excel notation.

  • Or by providing a range in Excel notation, i.e. 2 coordinates separated by a colon, e.g. “A2:C18”.

To write the cells in the Excel workbook, this method performs a double loop on the map contained in the values variable, starting with the rows as the first index and then the columns. The indices all start at 0. In this way, the first value is read from values[0][0] and is written to the first cell of the range address (i.e. the top left cell). If the map contains no elements for certain indices, an empty cell is written. If a value previously existed in the Excel cell, it is deleted.

If non-integer keys or keys outside the expected range exist in the map, they are simply ignored. In addition, only bool, int, double, nil, strings and DateTimes are supported as values. Any other type of value read from the map will throw an exception.

Parameters:

  • columnNumber (int) – Index of the starting column (starting at 0).

  • rowNumber (int) – Index of the starting row (starting at 0).

  • nbCols (int) – Number of columns to read.

  • nbRows (int) – Number of rows to read.

  • topLeftAddress (String) – Address of the first top-left cell.

  • bottomRightAddress (String) – Address of the last bottom-right cell

  • rangeAddress (String) – Range to read in Excel notation (e.g. A2:C18)

  • values (Map) – Values to write in the sheet

columnCount()

Returns the index of the last used column plus one. Note that this number does not necessarily correspond to the number of non-empty columns in the sheet.

rowCount()

Returns the index of the last used row plus one. Note that this number does not necessarily correspond to the number of non-empty rows in the sheet.

Examples

Basic usage

use xlsx;

...

doc = xlsx.open("example.xlsx");
sheet = doc.getSheet(0);

// Gets the value from cell A1
value = sheet.getValue("A1");
println("Value in A1: " + value);

// Sets the value of cell B2
sheet.setValue("B2", 123);

doc.save();

Reading and writing a range of data

use xlsx;

...

doc = xlsx.open("scores.xlsx");
sheet = doc.getSheet("Results");

// Read a 3x2 table starting from cell A1
table = sheet.readRangeValues("A1", "C2");

// Access the data
for [rowId, row in table][colId, cellValue in row] {
    println("Row " + rowId + ", Col " + colId + ": " + cellValue);
}

// Write a 2x2 table
//    0          -4
// 3.14  Pound cake
sheet.writeRangeValues("D3:E4", {
        {0, -4},
        {3.14, "Pound cake", "Ignored key": "Financier"},
        {"Ignored value"}
});

doc.save();

Working with dates

use xlsx;
use datetime;

...

doc = xlsx.open("example.xlsx");
sheet = doc.getSheet(0);

// Gets the date value from cell C3
date = sheet.getDateTimeValue("C3");
println("Date in C3: " + date);

// Sets the current date and time to D4
now = datetime.now();
sheet.setDateTimeValue("D4", now);

// Sets a custom date and time to E5
date = datetime.date(2025, 5, 23, 10, 30, 0);
sheet.setDateTimeValue("E5", date);

doc.save();