_{Official ZPE/YASS documentation}zpe.lib.xlsx

Introduction

The zpe.lib.xlsx plugin provides native support for creating, opening, editing and saving Microsoft Excel (.xlsx) files directly from ZPE/YASS.

The plugin exposes two global functions for creating or opening workbooks, and then provides workbook and sheet objects for manipulating sheets and cells.

• Create a workbook in memory using xlsx_new()
• Open an existing workbook from disk using xlsx_open(path)
• Work with sheets using get_sheet() / add_sheet()
• Write and read cells using set_cell() / get_cell()

Installation

Place zpe.lib.xlsx.jar in your ZPE native-plugins folder and restart ZPE.

You can also download with the ZULE Package Manager by using:

zpe --zule install zpe.lib.xlsx.jar

Usage

The typical workflow is:

• Create a workbook (or open one)
• Get a sheet
• Write/read cells
• Save and close

import "zpe.lib.xlsx"

wb = xlsx_new()
sheet = wb->get_sheet(0)

sheet->set_cell(0, 0, "Name")
sheet->set_cell(0, 1, "Age")

sheet->set_cell(1, 0, "Jamie")
sheet->set_cell(1, 1, 34)

wb->save("example.xlsx")
wb->close()

Functions

The following global functions are exposed by the plugin.

xlsx_new() ⇒ ZPEXLSXWorkbook

Creates a new workbook in memory and returns a ZPEXLSXWorkbook object. A default sheet named Sheet1 is created automatically.

xlsx_open(string path) ⇒ ZPEXLSXWorkbook

Opens an existing .xlsx file from disk and returns a ZPEXLSXWorkbook object. Returns false if the file cannot be opened.

Permission: This function requires permission level 3 because it reads from disk.

Objects

ZPEXLSXWorkbook

A workbook represents an Excel file. Workbooks can be created in memory, loaded from disk, modified, then saved back to disk.

new_file() ⇒ ZPEXLSXWorkbook

Clears the current workbook and creates a new workbook in memory. A default sheet named Sheet1 is created automatically. Returns the workbook object.

open(string path) ⇒ boolean

Opens an existing workbook from disk. Returns true on success, otherwise false.

Permission: Requires permission level 3.

save(string path) ⇒ boolean

Saves the workbook to disk. Returns true on success, otherwise false.

Permission: Requires permission level 3.

close() ⇒ boolean

Closes the workbook and releases file resources. Returns true on success.

add_sheet(string name) ⇒ ZPEXLSXSheet

Creates a new sheet and returns a ZPEXLSXSheet object for it.

get_sheet(mixed name_or_index) ⇒ ZPEXLSXSheet

Returns a sheet by name or by index.

If name_or_index is numeric, it is treated as a zero-based index. Otherwise it is treated as a sheet name. Returns false if the sheet does not exist.

get_sheet_count() ⇒ number

Returns the number of sheets in the workbook.

ZPEXLSXSheet

A sheet represents a worksheet inside a workbook. Sheets allow reading and writing values to individual cells.

set_cell(number row, number col, mixed value) ⇒ boolean

Sets a cell value at the given row and column index. Row and column indices are zero-based.

The plugin attempts to store the value as:

• boolean (true/false)
• number (if it parses as a numeric value)
• string (otherwise)

Returns true on success, otherwise false.

get_cell(number row, number col) ⇒ mixed

Returns the value of a cell.

If the cell does not exist, an empty string is returned. Formula cells return the formula text.

get_last_row() ⇒ number

Returns the last row index used in the sheet (zero-based). If the sheet is empty, this may return 0.

get_name() ⇒ string

Returns the name of the sheet.

Examples

1) Creating a workbook with multiple sheets

import "zpe.lib.xlsx"

wb = xlsx_new()

people = wb.get_sheet(0)
people.set_cell(0, 0, "Name")
people.set_cell(0, 1, "Age")

people.set_cell(1, 0, "Jamie")
people.set_cell(1, 1, 34)

scores = wb.add_sheet("Scores")
scores.set_cell(0, 0, "Player")
scores.set_cell(0, 1, "Score")

scores.set_cell(1, 0, "Theo")
scores.set_cell(1, 1, 9001)

wb.save("multi-sheet.xlsx")
wb.close()

2) Opening an existing workbook and reading values

import "zpe.lib.xlsx"

wb = xlsx_open("example.xlsx")
if (wb == false)
  print("Could not open workbook.")
  stop
end if

sheet = wb.get_sheet(0)

name = sheet.get_cell(1, 0)
age = sheet.get_cell(1, 1)

print(name)
print(age)

wb.close()

Notes and caveats

• Uses Apache POI internally for Excel file handling.
• Row and column indices are zero-based.
• Saving and opening workbooks requires permission level 3 because it reads/writes files.
• Formula cells currently return the formula text rather than the evaluated value. If you need formula evaluation, this can be added later.
• If a requested cell does not exist, get_cell returns an empty string.
• Cross-platform (Windows, macOS, Linux).