Xlsx

Opens existing Excel `.xlsx` workbooks, reads and changes cells and saves the result. It cannot create a workbook from scratch: start from an existing file or template. Only the `.xlsx` format is supported, and `.xlsx` is appended to a file name that does not end with it. Rows and columns are numbered from 1 and the column comes before the row in `getCell` and `setCell`. Cells are read as text the way Excel shows them: formulas are evaluated, numbers use the number format of the cell, and dates become `yyyy-MM-dd` (or `yyyy-MM-dd HH:mm:ss` when they have a time), which `Xlsx.dateFormat` can change.

• Kind Library, a global in every script
• Category Files & Documents
• Functions 3

Objects

• Workbook
• Sheet

Functions

• open Workbook Opens an existing workbook.
• dateFormat self Sets the format used when date cells are read, for workbooks that are already open and for ones opened later.
• close self Closes every workbook opened with `Xlsx.open` that is still open.

open()

Xlsx.open(location: string)→Workbook

Opens an existing workbook. Backslashes in the path are turned into `/` and `.xlsx` is added when the name does not end with it. The workbook is loaded into memory: changes only reach disk with `Workbook.save`. Throws `Xlsx.open failed` when the file is missing or cannot be read, and the parser's own message when the file is not a valid `.xlsx`.

Parameters

location string

Workbook file, relative to the workspace

Returns

Workbook An Excel workbook that is open in memory: get its sheets, change them and save the workbook.

Example runs in CI

-- template.xlsx must already exist in the project; Xlsx edits existing workbooks
local workbook = Xlsx.open("template.xlsx")
local sheet = workbook.sheet("Sheet1")
sheet.setCell(1, 2, "Alice")
print(sheet.getCell(1, 2))
workbook.save("report.xlsx")
workbook.close()

dateFormat()

Xlsx.dateFormat(pattern: string)→self

Sets the format used when date cells are read, for workbooks that are already open and for ones opened later. Without it dates are `yyyy-MM-dd`, or `yyyy-MM-dd HH:mm:ss` when the cell has a time. Once set, the pattern is used for every date cell, with or without a time, so add `HH:mm:ss` if you need the time. Throws `Xlsx.dateFormat received an invalid date pattern` for a bad pattern.

Parameters

pattern string

Java date-time pattern, for example `dd-MM-yyyy` or `dd/MM/yyyy HH:mm`

Returns

self chainable The library itself, so calls can be chained.

Example runs in CI

Xlsx.dateFormat("dd-MM-yyyy")
local sheet = Xlsx.open("template.xlsx").sheet("Sheet1")
print(sheet.getCell(1, 1))

close()

Xlsx.close()→self

Closes every workbook opened with `Xlsx.open` that is still open. Unsaved changes are lost. Throws after trying all of them when one fails to close.

Returns

self chainable The library itself, so calls can be chained.