Module xlsx

ballerina/xlsx Ballerina library

0.9.0

Overview

The ballerina/xlsx module provides functionality to read and write Microsoft Excel files in the XLSX format with type-safe data binding to Ballerina records. It exposes a simple file-based API (parseSheet / writeSheet) for single-sheet ETL and an object-based Workbook API for multi-sheet operations, byte-array I/O, and Excel Tables.

All processing is done locally with no external service dependencies.

Quickstart

To use the xlsx module in your Ballerina application, modify the .bal file as follows:

Step 1: Import the module

Import the xlsx module.


import ballerina/xlsx;

Step 2: Invoke module functions

Parse an XLSX file into typed records


type Employee record {|
    string name;
    int age;
    string department;
|};

Employee[] employees = check xlsx:parseSheet("employees.xlsx");

You can also target a specific sheet by name or index, or fall back to a flexible shape:


Employee[] sales = check xlsx:parseSheet("report.xlsx", "Sales");
map<xlsx:CellValue?>[] rows = check xlsx:parseSheet("unknown.xlsx");
string[][] raw = check xlsx:parseSheet("anything.xlsx");

Write records to an XLSX file


Employee[] employees = [
    {name: "John", age: 30, department: "IT"},
    {name: "Jane", age: 28, department: "HR"}
];

check xlsx:writeSheet(employees, "output.xlsx", "Employees");

Note: The write is atomic — on failure the original file is preserved, never partially overwritten.

Map non-matching headers with `@xlsx:Name`

When the Excel column header does not match the Ballerina field name, use the @xlsx:Name annotation. The mapping is bidirectional — it applies on both parse and write.


type Employee record {|
    @xlsx:Name {value: "Employee Name"}
    string name;

    @xlsx:Name {value: "Years of Service"}
    int tenure;
|};

Employee[] employees = check xlsx:parseSheet("employees.xlsx");

Work with multiple sheets via the `Workbook` API


xlsx:Workbook wb = check xlsx:fromFile("report.xlsx");

string[] sheetNames = wb.getSheetNames();
xlsx:Sheet sales = check wb.getSheet("Sales");
Employee[] salesRows = check sales.getRows();

xlsx:Sheet summary = check wb.createSheet("Summary");
check summary.putRows(salesRows);

check wb.save();
check wb.close();

Workbook construction comes in three flavours:


xlsx:Workbook wb1 = new;                                    // empty in-memory workbook (saveAs required to persist)
xlsx:Workbook wb2 = check xlsx:fromFile("existing.xlsx");   // open an existing file (errors if missing)
xlsx:Workbook wb3 = check xlsx:fromBytes(sourceBytes);      // open from a byte array (e.g., SFTP / HTTP body)

To create a brand-new file with a specific name, use new (in-memory) followed by wb.saveAs("path.xlsx").

Bytes in, bytes out


byte[] inputBytes = check sftp->get("/in/orders.xlsx");
xlsx:Workbook wb = check xlsx:fromBytes(inputBytes);

xlsx:Sheet sheet = check wb.getSheet(0);
Order[] orders = check sheet.getRows();
// ... enrich orders ...
check sheet.putRows(orders);

byte[] outputBytes = check wb.toBytes();
check sftp->put("/out/orders-enriched.xlsx", outputBytes);
check wb.close();

Read and write Excel Tables

Excel Tables (ListObjects) are addressed by name and are unique across the workbook. For one-shot read/write flows, use the tier 1 conveniences:


Employee[] employees = check xlsx:parseTable("sales.xlsx", "EmployeeTable");

Employee[] newEmployees = [{name: "Alice", age: 31, department: "Eng"}];
check xlsx:writeTable([...employees, ...newEmployees], "sales.xlsx", "EmployeeTable");
// writeTable auto-expands the table to fit the data

For richer operations (totals row, rename, resize, or coordination with other workbook changes), go through the Workbook API:


xlsx:Workbook wb = check xlsx:fromFile("sales.xlsx");
xlsx:Table empTable = check wb.getTable("EmployeeTable");

Employee[] employees = check empTable.getRows();
if check empTable.hasTotalRow() {
    map<xlsx:CellValue?> totals = check empTable.getTotalRow();
    // ...
}
check empTable.putRows([...employees, ...newEmployees]);

check wb.save();
check wb.close();

Bind dates and times to `time:Civil`, `time:Date`, or `time:TimeOfDay`

Declare the field's type to control the shape — typed time records, or ISO 8601 strings:


import ballerina/time;

type Transaction record {|
    int id;
    time:Civil timestamp;      // date-time cell → time:Civil
    time:Date settledOn;       // date-only cell → time:Date
    decimal amount;
|};

Transaction[] txns = check xlsx:parseSheet("transactions.xlsx");

Writing the same record back produces date-formatted cells, not text cells. Use string instead of the time:* types if you want ISO strings in your record.

Continue parsing on row-level errors with fail-safe mode


Employee[] employees = check xlsx:parseSheet("data.xlsx", 0, {
    failSafe: {
        enableConsoleLogs: true,
        fileOutputMode: {
            filePath: "./xlsx-errors.log",
            contentType: RAW_AND_METADATA
        }
    }
});

Rows that fail type conversion or constraint validation are skipped and logged; structural errors (corrupted file, missing sheet) still fail immediately.

Step 3: Run the Ballerina application


bal run

Examples

The xlsx module provides practical examples illustrating usage in various scenarios. Explore these examples, ordered from the simplest Tier 1 read/write loop to in-memory byte pipelines.

Process Employee Data — Tier 1 quickstart. Write employee records, read them back into typed records, filter, write the filtered subset. Demonstrates parseSheet, writeSheet, and @xlsx:Name column mapping.
Monthly Sales Report — Build a multi-sheet workbook with an embedded Excel Table and time:Date columns; reopen and query it through the Workbook + Table APIs.
Validated Bulk Import — Parse a partner file with @constraint validation and fail-safe error logging — clean rows flow downstream; rejected rows are logged with their raw values and reason.
In-Memory Pipeline — Process XLSX bytes end-to-end without disk I/O. Demonstrates xlsx:fromBytes and Workbook.toBytes() — the shape an HTTP service or queue consumer would use.

Functions

fromBytes

Isolated Function

function fromBytes(byte[] sourceBytes) returns Workbook|Error

Opens an XLSX workbook from an in-memory byte array.

Returns an error if the bytes are not a valid XLSX workbook. The resulting workbook has no associated file; use saveAs(path) to persist it.


byte[] payload = check io:fileReadBytes("report.xlsx");
xlsx:Workbook wb = check xlsx:fromBytes(payload);

Parameters

sourceBytes byte[] - XLSX content as a byte array

Return Type

Workbook|Error - The opened workbook, or an Error if the bytes are invalid

fromFile

Isolated Function

function fromFile(string path) returns Workbook|Error

Opens an XLSX workbook from a file path.

Returns an error if the path does not exist or the file is not a valid XLSX. To create a new file, use new Workbook() and then saveAs(path).


xlsx:Workbook wb = check xlsx:fromFile("report.xlsx");

Parameters

path string - Path to the XLSX file

Return Type

Workbook|Error - The opened workbook, or an Error if the path is missing or the file is invalid

parseSheet

Isolated Function

function parseSheet(string path, string|int sheet, ParseOptions options, typedesc<Row> t) returns t[]|Error

Parse an XLSX file into Ballerina values.

This is the recommended way to read XLSX files. It reads the specified sheet (first sheet by default) and converts rows to the target type.

Supports parsing to:

string[][] - Raw string array
record{}[] - Array of records (with header-to-field mapping)
map<CellValue?>[] - Array of maps (keys are column headers)


// Parse first sheet as records
Employee[] employees = check xlsx:parseSheet("employees.xlsx");

// Parse specific sheet by name
Employee[] sales = check xlsx:parseSheet("report.xlsx", "Sales");

// Parse specific sheet by index with options
Employee[] data = check xlsx:parseSheet("report.xlsx", 1, {headerRowIndex: 2});

Parameters

path string - Path to the XLSX file

sheet string|int (default 0) - Sheet to read: name (string) or index (int, 0-based). Default: 0 (first sheet)

options ParseOptions (default {}) - Parse options

t typedesc<Row> (default <>) - Target row type descriptor (record, map, or string[])

Return Type

t[]|Error - Parsed data or error

parseTable

Isolated Function

function parseTable(string path, string tableName, ParseOptions options, typedesc<Row> t) returns t[]|Error

Parse data from an Excel table by name.

Tables are unique by name across the entire workbook, so no sheet specification is needed. Headers are automatically excluded from results.

Supports parsing to:

string[][] - Raw string array
record{}[] - Array of records (table headers map to fields)
map<CellValue?>[] - Array of maps (keys are column headers)


// Parse table as records
Employee[] employees = check xlsx:parseTable("sales.xlsx", "EmployeeTable");

// Parse with options
Employee[] data = check xlsx:parseTable("report.xlsx", "SalesTable", {
    enableConstraintValidation: true
});

Parameters

path string - Path to the XLSX file

tableName string - Name of the table to parse

options ParseOptions (default {}) - Parse options

t typedesc<Row> (default <>) - Target row type descriptor (record, map, or string[])

Return Type

t[]|Error - Parsed data or TableNotFoundError

writeSheet

Isolated Function

function writeSheet(Row[] data, string path, string sheetName, *RowWriteOptions options) returns Error?

Write Ballerina data to an XLSX file.

This is the recommended way to write XLSX files. Creates a single-sheet XLSX file from the provided data.

Supports writing from:

string[][] - Raw string array (first row can be headers)
record{}[] - Array of records (field names become headers)
map<CellValue?>[] - Array of maps (keys become headers)


Employee[] employees = [{name: "John", age: 30}];

// Write to file (default sheet name "Sheet1")
check xlsx:writeSheet(employees, "output.xlsx");

// Write with an explicit sheet name
check xlsx:writeSheet(employees, "report.xlsx", "Employees");

// Write with sheet name + additional row options
check xlsx:writeSheet(employees, "report.xlsx", "Employees", writeHeaders = false);

Parameters

data Row[] - Data to write

path string - Path to the output XLSX file

sheetName string (default "Sheet1") - Name of the sheet to create (default: "Sheet1")

options *RowWriteOptions - Row-level write options (writeHeaders, startRowIndex)

Return Type

Error? - Error if write fails

writeTable

Isolated Function

function writeTable(Row[] data, string path, string tableName, *RowWriteOptions options) returns Error?

Write data to an existing Excel table.

Writes data to the specified table. If the data exceeds the current table size, the table automatically expands to accommodate the new rows.


Employee[] newEmployees = [...];
check xlsx:writeTable(newEmployees, "sales.xlsx", "EmployeeTable");

Parameters

data Row[] - Data to write

path string - Path to the XLSX file containing the table

tableName string - Name of the table to write to

options *RowWriteOptions - Row-level write options (writeHeaders, startRowIndex)

Return Type

Error? - TableNotFoundError if table doesn't exist, or other Error

Classes

xlsx: Workbook

Isolated

Represents an Excel workbook.

A workbook contains one or more sheets and provides methods to access sheets, create new sheets, delete sheets, and save to files.

Construct an empty in-memory workbook directly with new. Use the module-level xlsx:fromFile and xlsx:fromBytes factory functions to open an existing XLSX from a path or byte array.


// Empty in-memory workbook
xlsx:Workbook empty = check new;

// Open an existing file
xlsx:Workbook wb = check xlsx:fromFile("report.xlsx");
string[] sheets = wb.getSheetNames();
xlsx:Sheet sheet = check wb.getSheet("Sales");
// ... modify data ...
check wb.save();   // Overwrites the original file
check wb.close();

// Open from a byte array (e.g., HTTP payload)
xlsx:Workbook fromBytes = check xlsx:fromBytes(sourceBytes);

Constructor

Initialize an empty in-memory workbook.

No file association; save() errors until a path is set via saveAs(path). To open an existing workbook, use xlsx:fromFile(path) or xlsx:fromBytes(bytes).

Creating an empty in-memory workbook cannot fail, so init does not return an error.

init ()

getSheetNames

Isolated Function

function getSheetNames() returns string[]|Error

Get all sheet names in the workbook.

Return Type

string[]|Error - Array of sheet names

getSheetCount

Isolated Function

function getSheetCount() returns int|Error

Get the number of sheets in the workbook.

Return Type

int|Error - Sheet count

hasSheet

Isolated Function

function hasSheet(string name) returns boolean|Error

Check whether a sheet with the given name exists in the workbook.


if check wb.hasSheet("Sales") {
    xlsx:Sheet s = check wb.getSheet("Sales");
}

Parameters

name string - Sheet name

Return Type

boolean|Error - true if the sheet exists, false otherwise, or an Error if the workbook is closed

getSheet

Isolated Function

function getSheet(string|int target) returns Sheet|Error

Get a sheet by name or index.


xlsx:Sheet byName = check workbook.getSheet("Sales");
xlsx:Sheet byIndex = check workbook.getSheet(0);

Parameters

target string|int - Sheet name (string) or 0-based index (int)

Return Type

Sheet|Error - Sheet instance, or an Error (e.g. SheetNotFoundError) if not found or the workbook is closed

createSheet

Isolated Function

function createSheet(string name) returns Sheet|Error

Create a new sheet in the workbook.


xlsx:Sheet newSheet = check workbook.createSheet("Report");
check newSheet.putRows(data);

Parameters

name string - Name for the new sheet

Return Type

Sheet|Error - New sheet instance or Error if name already exists

deleteSheet

Isolated Function

function deleteSheet(string|int target) returns Error?

Delete a sheet by name or index.


check workbook.deleteSheet("TempData");
check workbook.deleteSheet(0);            // Delete first sheet

Parameters

target string|int - Sheet name (string) or 0-based index (int)

Return Type

Error? - SheetNotFoundError if the sheet doesn't exist, or another Error (e.g., refusing to delete the last sheet — Excel requires at least one)

save

Isolated Function

function save() returns Error?

Save the workbook to its source file.

Overwrites the file the workbook was opened from (xlsx:fromFile(path)) or the path most recently passed to saveAs(path). Returns an error for in-memory workbooks (new) that haven't yet been saved to a path.


xlsx:Workbook wb = check xlsx:fromFile("data.xlsx");
// ... modify ...
check wb.save();  // Overwrites data.xlsx

Return Type

Error? - Error if no source path or save fails

saveAs

Isolated Function

function saveAs(string path) returns Error?

Save the workbook to a new location.

After calling saveAs(), subsequent calls to save() will write to this new location.


xlsx:Workbook wb = check new;
xlsx:Sheet sheet = check wb.createSheet("Data");
check sheet.putRows(data);
check wb.saveAs("output.xlsx");
// Now wb.save() would write to output.xlsx

Parameters

path string - Path to save the XLSX file

Return Type

Error? - Error if save fails

toBytes

Isolated Function

function toBytes() returns byte[]|Error

Serialize the workbook to a byte array.

Useful for returning the workbook as an HTTP response body, embedding it in a larger payload, or any flow that needs the bytes without writing to a file.


byte[] bytes = check wb.toBytes();
// ... use the bytes ...

Return Type

byte[]|Error - XLSX bytes or Error if serialization fails

close

Isolated Function

function close() returns Error?

Close the workbook and release resources.

Always call this when done with the workbook to free memory.

Return Type

Error? - Error if close fails

getTable

Isolated Function

function getTable(string name) returns Table|Error

Get a table by name from anywhere in the workbook.

Table names are unique across the entire workbook, so no sheet specification is needed.


xlsx:Table empTable = check wb.getTable("EmployeeTable");
Employee[] employees = check empTable.getRows();

Parameters

name string - Table name

Return Type

Table|Error - Table, or an Error (e.g. TableNotFoundError) if not found or the workbook is closed

getAllTables

Isolated Function

function getAllTables() returns Table[]|Error

Get all tables across all sheets in the workbook.


xlsx:Table[] allTables = check wb.getAllTables();
foreach xlsx:Table t in allTables {
    io:println("Table: ", t.getName(), " in sheet: ", t.getSheetName());
}

Return Type

Table[]|Error - Array of all tables (may be empty), or Error on retrieval failure

Enums

xlsx: ErrorLogContentType

Content types for error log output.

Controls what information is included when logging parsing errors.

Members

METADATA - Log only metadata: timestamp, location (row/column), error message. Output format: {"time":"...","location":{"row":N,"column":N},"message":"..."}

RAW - Log only the raw offending row data as a JSON array. Output format: ["value1", "value2", ...]

RAW_AND_METADATA - Log both raw data and metadata for comprehensive debugging. Output format: {"time":"...","location":{...},"offendingRow":"[...]","message":"..."}

xlsx: FileWriteOption

File write options for error logs.

Controls how the error log file is written when multiple errors occur.

Members

APPEND - Append new errors to the existing log file (default).

OVERWRITE - Overwrite the log file on first error, then append subsequent errors.

xlsx: FormulaMode

Formula handling mode for cells containing formulas.

Members

CACHED - Use the last calculated/cached value (default).

TEXT - Return the formula string as text (e.g., "=SUM(A1:A10)").

Annotations

xlsx: Name

NameConfig

record field

Annotation to specify the Excel column name for a record field.

Records

xlsx: CellRange

Closed record

Represents a rectangular cell range in a sheet.

All indices are 0-based (matching internal representation). For example, row 0 is the first row (Excel row 1), column 0 is column A.

Fields

firstRowIndex int - Index of the first row in the range (0-based)

lastRowIndex int - Index of the last row in the range (0-based)

firstColumnIndex int - Index of the first column in the range (0-based)

lastColumnIndex int - Index of the last column in the range (0-based)

xlsx: ErrorDetails

Closed record

Details for XLSX errors.

Fields

sheetName? string - Name of the sheet where error occurred (if applicable)

tableName? string - Name of the table where error occurred (if applicable)

cellAddress? string - Cell address where error occurred (if applicable)

rowNumber? int - Row number where error occurred (if applicable)

columnNumber? int - Column number where error occurred (if applicable)

xlsx: FailSafeOptions

Closed record

Configuration for fail-safe error handling during parsing.

When enabled, parsing continues even when row-level errors occur (e.g., type conversion failures). Errors can be logged to console, file, or both. Rows with errors are skipped, and only successfully parsed rows are returned.

Note: Critical structural errors (corrupted file, missing sheet, header errors) will always cause parsing to fail immediately, regardless of fail-safe configuration.

Fields

enableConsoleLogs boolean(default true) - Enable logging errors to console (default: true)

includeSourceDataInConsole boolean(default false) - Include offending row data in console output (default: false)

fileOutputMode? FileOutputMode - Optional file-based error logging configuration

Example: Console logging only

Example: File logging only

xlsx: FileOutputMode

Closed record

Configuration for file-based error logging.

When provided in FailSafeOptions, parsing errors will be written to the specified file.

Fields

filePath string - Path to the error log file (required)

contentType ErrorLogContentType(default METADATA) - What content to include in logs (default: METADATA)

fileWriteOption FileWriteOption(default APPEND) - How to handle existing log files (default: APPEND)

xlsx: Location

Closed record

Location within an XLSX file where an error occurred.

Fields

row int - Row number (1-based, as displayed in Excel)

column int - Column number (1-based)

xlsx: LogOutput

Closed record

Structured error log output.

Represents the JSON structure written to error log files when using METADATA or RAW_AND_METADATA content types.

Fields

time? string - ISO 8601 timestamp when the error occurred

location? Location - Row and column where the error occurred

message? string - Error message describing what went wrong

offendingRow? string - The raw row data that caused the error (only with RAW_AND_METADATA)

xlsx: NameConfig

Closed record

Annotation to map a record field to a specific Excel column name. Use this when the Excel column header doesn't match the Ballerina field name.


type Employee record {
    @xlsx:Name {value: "First Name"}
    string firstName;
    @xlsx:Name {value: "Employee ID"}
    int id;
};

When reading, headers "First Name" and "Employee ID" will map to firstName and id. When writing, field names will produce headers "First Name" and "Employee ID". Fields without this annotation use their Ballerina field name as the Excel header.

Fields

value string - The Excel column header name to map to this field.

xlsx: ParseOptions

Closed record

Options for parsing XLSX data.

Fields

headerRowIndex int?(default 0) - Row containing column headers/names (0-based index). Set to null if the sheet has no headers - columns will be named "col0", "col1", etc. Example: If headers are in row 1 (second row), set this to 1.

dataStartRowIndex? int - Row where actual data begins (0-based index). If not specified, defaults to headerRowIndex + 1 (or 0 if headerRowIndex is null).

rowCount int?(default ()) - Maximum number of data rows to read. Set to null to read all rows (default). Example: rowCount: 100 reads at most 100 rows starting from dataStartRowIndex.

formulaMode FormulaMode(default CACHED) - How to handle formula cells (default: CACHED)

enableConstraintValidation boolean(default true) - Whether to validate type constraints (default: true). When enabled, parsed records are validated against any Ballerina @constraint annotations defined on the record type. Note: Disable for better performance when constraints aren't needed.

caseInsensitiveHeaders boolean(default false) - Whether to match headers case-insensitively (default: false). When enabled, header "Name" will match record field "name" or "NAME".

allowDataProjection record {| nilAsOptionalField boolean, absentAsNilableType boolean |}|false (default {}) - Data projection configuration (default: enabled/lenient mode).
- Default {}: Lenient mode - sheet columns don't need to match all record fields
- Set to false: Strict mode - all record fields must have matching columns

failSafe? FailSafeOptions - Fail-safe error handling configuration. When set, parsing continues on row-level errors (type conversion, validation). Errors are logged and problematic rows are skipped. Critical errors (file not found, corrupted file) still fail immediately.

xlsx: RowReadOptions

Closed record

Options for reading rows from a sheet.

Fields

headerRowIndex int?(default 0) - Row containing column headers/names (0-based index). Set to null if the sheet has no headers - columns will be named "col0", "col1", etc.

dataStartRowIndex? int - Row where actual data begins (0-based index). If not specified, defaults to headerRowIndex + 1 (or 0 if headerRowIndex is null).

rowCount int?(default ()) - Maximum number of data rows to read. Set to null to read all rows (default).

formulaMode FormulaMode(default CACHED) - How to handle formula cells (default: CACHED)

enableConstraintValidation boolean(default true) - Whether to validate type constraints (default: true). When enabled, parsed records are validated against any Ballerina @constraint annotations defined on the record type. Note: Disable for better performance when constraints aren't needed.

caseInsensitiveHeaders boolean(default false) - Whether to match headers case-insensitively (default: false).

allowDataProjection record {| nilAsOptionalField boolean, absentAsNilableType boolean |}|false (default {}) - Data projection configuration (see ParseOptions for details).

failSafe? FailSafeOptions - Fail-safe error handling configuration (see ParseOptions).

xlsx: RowWriteOptions

Closed record

Options for writing rows to a sheet.

Fields

writeHeaders boolean(default true) - Whether to write headers (default: true)

startRowIndex int(default 0) - Row number to start writing (0-based, default: 0)

getName

Isolated Function

function getName() returns string|Error

Get the name of the sheet.

Return Type

string|Error - Sheet name, or an Error if the handle is invalid

getUsedRange

Isolated Function

function getUsedRange() returns string|Error

Get the used range of the sheet in A1 notation.

The used range is the smallest rectangular area that contains all cells with data. This excludes "ghost rows" - rows that have formatting but no actual data.

Return Type

string|Error - Range string (e.g., "A1:D50"), or an Error if the handle is invalid

getUsedCellRange

Isolated Function

function getUsedCellRange() returns CellRange?|Error

Get the used cell range of the sheet as a structured record.

Returns 0-based row and column indices representing the rectangular area containing all cells with actual data. Returns nil if the sheet is empty.

This is useful for:

Determining sheet bounds before iterating
Planning data writes at specific positions
Understanding the actual data footprint of a sheet


xlsx:Workbook wb = check xlsx:fromFile("data.xlsx");
xlsx:Sheet sheet = check wb.getSheet("Sales");
xlsx:CellRange? range = sheet.getUsedCellRange();
if range != () {
    io:println("Data spans rows ", range.firstRowIndex, " to ", range.lastRowIndex);
}

Return Type

CellRange?|Error - CellRange record with 0-based indices, nil if sheet is empty, or an Error if the handle is invalid

getRowCount

Isolated Function

function getRowCount() returns int|Error

Get the number of rows with data.

Return Type

int|Error - Row count, or an Error if the handle is invalid

getColumnCount

Isolated Function

function getColumnCount() returns int|Error

Get the number of columns with data.

Return Type

int|Error - Column count, or an Error if the handle is invalid

getRows

Isolated Function

function getRows(RowReadOptions options, typedesc<Row> t) returns t[]|Error

Get all rows from the sheet.

Supports reading to:

string[][] - Raw string array
record{}[] - Array of records


// As string array
string[][] rows = check sheet.getRows();

// As records
type Employee record {| string name; int age; |};
Employee[] employees = check sheet.getRows();

Parameters

options RowReadOptions (default {}) - Read options

t typedesc<Row> (default <>) - Target row type descriptor (record, map, or string[])

Return Type

t[]|Error - Array of rows or error

getRow

Isolated Function

function getRow(int index, RowReadOptions options, typedesc<Row> t) returns t|Error

Get a single row from the sheet by index.

Supports reading to:

string[] - Raw string array for the row
record{} - Single record


// Get row as string array
string[] row = check sheet.getRow(5);

// Get row as record
type Employee record {| string name; int age; |};
Employee employee = check sheet.getRow(5);

Parameters

index int - Row index (0-based, relative to data start row). Example: If dataStartRowIndex=1, getRow(0) returns Excel row 1, getRow(2) returns Excel row 3.

options RowReadOptions (default {}) - Read options

t typedesc<Row> (default <>) - Target type descriptor

Return Type

t|Error - Single row or error

putRows

Isolated Function

function putRows(Row[] data, *RowWriteOptions options) returns Error?

Write rows to the sheet.

Supports writing from:

string[][] - Raw string array
record{}[] - Array of records (field names become headers)


// Write string array
string[][] data = [["Name", "Age"], ["John", "30"]];
check sheet.putRows(data);

// Write records
Employee[] employees = [{name: "John", age: 30}];
check sheet.putRows(employees);

Parameters

data Row[] - Data to write

options *RowWriteOptions - Write options

Return Type

Error? - Error if write fails

getColumn

Isolated Function

function getColumn(string|int columnRef, RowReadOptions options, typedesc<CellValue?> t) returns t[]|Error

Get a column of values by header name or 0-based index.


string[] names = check sheet.getColumn("Name");
decimal[] salaries = check sheet.getColumn(3);

Parameters

columnRef string|int - Column header name (string) or 0-based index (int)

options RowReadOptions (default {}) - Read options

t typedesc<CellValue?> (default <>) - Target cell type descriptor (CellValue?; nilable for columns with blank cells); returns t[]

Return Type

t[]|Error - Column values or error

getCell

Isolated Function

function getCell(int rowIndex, int columnIndex) returns CellValue?|Error

Read a single cell value.

Returns the cell's value as a CellValue (string / number / boolean / date-time), or nil for a blank cell. Narrow as needed at the call site.


xlsx:CellValue? value = check sheet.getCell(0, 2);

Parameters

rowIndex int - 0-based row index (absolute)

columnIndex int - 0-based column index (absolute)

Return Type

CellValue?|Error - Cell value, nil for blank cells, or error

setRow

Isolated Function

function setRow(int rowIndex, Row data, *RowWriteOptions options) returns Error?

Write a single row at the specified row index.

When data is a record or map, the sheet must already have a header row at the position implied by options.headerRowIndex; values are placed into matching columns by header name.


check sheet.setRow(5, ["John", "30", "Engineering"]);
check sheet.setRow(6, {name: "Jane", age: 28});

Parameters

rowIndex int - 0-based row index (absolute)

data Row - Row data (string[], record, or map<CellValue?>)

options *RowWriteOptions - Write options

Return Type

Error? - Error if write fails

setColumn

Isolated Function

function setColumn(string|int columnRef, CellValue?[] data) returns Error?

Write a column of values by header name or 0-based index.

Values are written into successive rows starting from the row immediately after the header row.


check sheet.setColumn("Bonus", [1000, 2000, 1500]);
check sheet.setColumn(4, [true, false, true]);

Parameters

columnRef string|int - Column header name (string) or 0-based index (int)

data CellValue?[] - Column values

Return Type

Error? - Error if write fails

setCell

Isolated Function

function setCell(int rowIndex, int columnIndex, CellValue? value) returns Error?

Write a single cell value by 0-based row and column index.


check sheet.setCell(0, 0, "Header");
check sheet.setCell(1, 2, 42);

Parameters

rowIndex int - 0-based row index

columnIndex int - 0-based column index

value CellValue? - Cell value

Return Type

Error? - Error if write fails

setCellByAddress

Isolated Function

function setCellByAddress(string cellAddress, CellValue? value) returns Error?

Write a single cell value by A1-notation address.


check sheet.setCellByAddress("A1", "Header");
check sheet.setCellByAddress("D5", 42.5);

Parameters

cellAddress string - Cell address in A1 notation (e.g., "A1", "B12")

value CellValue? - Cell value

Return Type

Error? - Error if the address is invalid or write fails

deleteRow

Isolated Function

function deleteRow(int index) returns Error?

Delete a row from the sheet.

Subsequent rows shift up by one, preserving dense indexing.


check sheet.deleteRow(3);  // row 3 is removed; row 4 becomes row 3

Parameters

index int - 0-based row index to delete

Return Type

Error? - Error if delete fails

rename

Isolated Function

function rename(string newName) returns Error?

Rename the sheet.

The new name must satisfy Excel naming rules (≤31 characters, no \ / ? * [ ] :) and must be unique within the workbook.


check sheet.rename("MonthlyReport");

Parameters

newName string - New sheet name

Return Type

Error? - Error if the name is invalid or already taken

getTable

Isolated Function

function getTable(string name) returns Table|Error

Get a table from this sheet by name.


xlsx:Table empTable = check sheet.getTable("EmployeeTable");

Parameters

name string - Table name

Return Type

Table|Error - Table, or an Error (e.g. TableNotFoundError) if not found or the handle is invalid

getTables

Isolated Function

function getTables() returns Table[]|Error

Get all tables in this sheet.


xlsx:Table[] tables = check sheet.getTables();
foreach xlsx:Table t in tables {
    io:println("Table: ", t.getName());
}

Return Type

Table[]|Error - Array of tables (may be empty), or Error on retrieval failure

createTable

Isolated Function

function createTable(string name, CellRange|string range, string[]? headers) returns Table|Error

Create a new table with the specified range.

The range must include at least a header row. If headers are not provided, the first row of the range is used as headers.


// Create from range string
xlsx:Table t1 = check sheet.createTable("SalesTable", "A1:D10");

// Create from CellRange with custom headers
xlsx:Table t2 = check sheet.createTable("BonusTable", {
    firstRowIndex: 0, lastRowIndex: 5,
    firstColumnIndex: 0, lastColumnIndex: 3
}, ["Name", "Department", "Amount", "Date"]);

Parameters

name string - Unique table name (across workbook)

range CellRange|string - Table range as CellRange record or A1 notation string

headers string[]? (default ()) - Optional custom headers; if not provided, first row is used

Return Type

Table|Error - Created table or error

createTableFromData

Isolated Function

function createTableFromData(string name, Row[] data, int startRowIndex, int startColumnIndex) returns Table|Error

Create a new table from data, automatically calculating the range.

Writes the data first, then creates a table around it. An Excel table always has a header row: for records the field names (or @xlsx:Name) become the headers, for maps the keys, and for string[][] the first row is taken as the header. There is no option to omit headers — skipping headers is a parse-side concern (RowReadOptions.headerRowIndex).


Employee[] employees = [...];
xlsx:Table empTable = check sheet.createTableFromData("EmployeeTable", employees);

Parameters

name string - Unique table name (across workbook)

data Row[] - Data to write: records/maps (headers from field names/keys) or string[][] (first row = header)

startRowIndex int (default 0) - Starting row for the table (default: 0)

startColumnIndex int (default 0) - Starting column for the table (default: 0)

Return Type

Table|Error - Created table or error

deleteTable

Isolated Function

function deleteTable(string name) returns Error?

Delete a table from this sheet.

The table structure is removed but the underlying data is preserved.


check sheet.deleteTable("OldTable");

Parameters

name string - Table name to delete

Return Type

Error? - An Error (e.g. TableNotFoundError) if not found or the handle is invalid

xlsx: Table

Represents an Excel Table (ListObject) within a worksheet.

Tables provide structured access to data with automatic header handling, optional totals row support, and auto-expand capability when writing. Table names are unique across the entire workbook.

Instances are obtained from a Workbook or Sheet via methods like getTable, createTable, etc.; direct construction (new Table()) is not supported.


xlsx:Workbook wb = check xlsx:fromFile("sales.xlsx");
xlsx:Table empTable = check wb.getTable("EmployeeTable");

// Read data (headers excluded automatically)
Employee[] employees = check empTable.getRows();

// Write data (auto-expands if needed)
check empTable.putRows(newEmployees);

check wb.save();
check wb.close();

getName

Isolated Function

function getName() returns string|Error

Get the name of the table.

Table names are unique across the entire workbook.

Return Type

string|Error - Table name, or an Error if the handle is invalid

getDisplayName

Isolated Function

function getDisplayName() returns string|Error

Get the display name of the table.

The display name is what appears in Excel's UI. It may differ from the internal name.

Return Type

string|Error - Table display name, or an Error if the handle is invalid

getSheetName

Isolated Function

function getSheetName() returns string|Error

Get the name of the sheet containing this table.

Return Type

string|Error - Sheet name, or an Error if the handle is invalid

getRange

Isolated Function

function getRange() returns string|Error

Get the full range of the table (including headers and total row) in A1 notation.

Return Type

string|Error - A1-notation range string (e.g., "A1:D10"), or an Error if the handle is invalid

getCellRange

Isolated Function

function getCellRange() returns CellRange|Error

Get the full range of the table (including headers and total row) as a CellRange.

All indices are 0-based.

Return Type

CellRange|Error - CellRange representing the full table area, or an Error if the handle is invalid

getDataRange

Isolated Function

function getDataRange() returns string|Error

Get the data range of the table (excluding headers and total row) in A1 notation.

Return Type

string|Error - A1-notation range string, or an Error if the handle is invalid

getDataCellRange

Isolated Function

function getDataCellRange() returns CellRange|Error

Get the data range of the table (excluding headers and total row) as a CellRange.

All indices are 0-based.

Return Type

CellRange|Error - CellRange representing only the data area, or an Error if the handle is invalid

getRowCount

Isolated Function

function getRowCount() returns int|Error

Get the number of data rows in the table.

Returns only data rows, excluding header and totals row.

Return Type

int|Error - Data row count, or an Error if the handle is invalid

getColumnCount

Isolated Function

function getColumnCount() returns int|Error

Get the number of columns in the table.

Return Type

int|Error - Column count, or an Error if the handle is invalid

getHeaders

Isolated Function

function getHeaders() returns string[]|Error

Get the column header names.

Returns an array of header names in column order.

Return Type

string[]|Error - Array of header strings, or an Error if the handle is invalid

getRows

Isolated Function

function getRows(RowReadOptions options, typedesc<Row> t) returns t[]|Error

Get all data rows from the table.

Headers and totals row are automatically excluded. Supports reading to:

string[][] - Raw string array
record{}[] - Array of records (headers map to fields)


// As string array
string[][] rows = check table.getRows();

// As records
type Employee record {| string name; int age; |};
Employee[] employees = check table.getRows();

Parameters

options RowReadOptions (default {}) - Read options

t typedesc<Row> (default <>) - Target row type descriptor (record, map, or string[])

Return Type

t[]|Error - Array of data rows or error

getRow

Isolated Function

function getRow(int index, RowReadOptions options, typedesc<Row> t) returns t|Error

Get a single data row from the table by index.

The index is 0-based within the data range (first data row is index 0). Headers and totals are excluded from indexing.


type Employee record {| string name; int age; |};
Employee first = check table.getRow(0);

Parameters

index int - Row index (0-based within data range)

options RowReadOptions (default {}) - Read options

t typedesc<Row> (default <>) - Target type descriptor

Return Type

t|Error - Single row or error

putRows

Isolated Function

function putRows(Row[] data, *RowWriteOptions options) returns Error?

Write rows to the table.

If the data exceeds the current table size, the table automatically expands. Existing data is overwritten starting from the first data row.


Employee[] employees = [{name: "John", age: 30}, {name: "Jane", age: 25}];
check table.putRows(employees);

Parameters

data Row[] - Data to write (records or arrays)

options *RowWriteOptions - Write options

Return Type

Error? - Error if write fails

hasTotalRow

Isolated Function

function hasTotalRow() returns boolean|Error

Check if the table has a total row.

Return Type

boolean|Error - true if a total row exists, or an Error if the handle is invalid

getTotalRow

Isolated Function

function getTotalRow(typedesc<map<CellValue?>> t) returns t|Error

Get the total row values.

Returns a map keyed by column name. Each value binds to its natural cell value — a whole number to int, a fractional number to decimal, a date/time to an ISO string — or () for a blank total cell.


if check table.hasTotalRow() {
    map<xlsx:CellValue?> totals = check table.getTotalRow();
    io:println("Total salary: ", totals["Salary"]);
}

Parameters

t typedesc<map<CellValue?>> (default <>) - Result map type descriptor; leave at the default map<CellValue?> (the intended target). A narrower target (e.g. map<int>) only succeeds if every total cell fits it.

Return Type

t|Error - Map of column names to total values, or error if no total row

rename

Isolated Function

function rename(string newName) returns Error?

Rename the table.

The new name must be unique within the workbook.

Parameters

newName string - New table name

Return Type

Error? - Error if rename fails (e.g., name already exists)

resize

Isolated Function

function resize(CellRange|string newRange) returns Error?

Resize the table to a new range.

The new range must include at least one header row and one data row. This is for manual resizing; use putRows() for automatic expansion.

Parameters

newRange CellRange|string - New table range as a CellRange record or an A1-notation string

Return Type

Error? - Error if resize fails (e.g., invalid range, overlap)

Union types

xlsx: Row

map<CellValue?>|string[]

Row

A single row in a sheet — the atomic data unit. A row is one of two shapes:

map<CellValue?> - Dynamic map (keys are column headers; values are cell values, with () for a blank cell). A typed record also binds when every field is CellValue?-typed; use @xlsx:Name for field names that differ from the column headers. To absorb columns beyond the declared fields, give the record a CellValue? rest descriptor — record {| ...; CellValue?...; |}.
string[] - Raw cell text in column order

The map's value type is CellValue? (not anydata) so the row contract matches what a cell can actually hold: a target field of an unsupported type (e.g. xml, byte[], a nested record) is rejected at compile time rather than failing at runtime.

xlsx: CellValue

CellValue

A populated XLSX cell value.

A cell in an XLSX file can hold a string, a number (int / float / decimal), a boolean, or a date/time. This union encodes that contract honestly at the type level — users can't ask for unsupported types like xml or byte[] from a cell and get a runtime error; the type system rejects it at compile time.

CellValue is the non-nil value type. Where a cell may be blank, the nilable form CellValue? is used — Sheet.getCell, Sheet.getColumn, the cell setters, and Table.getTotalRow — with () representing an empty cell.

Import

import ballerina/xlsx;

Other versions

0.9.0

Metadata

Released date: 13 days ago

Version: 0.9.0

License: Apache-2.0

Compatibility

Platform: java21

Ballerina version: 2201.12.0

GraalVM compatible: Yes

Pull count

Total: 2

Current verison: 2

Weekly downloads

Source repository

Keywords

xlsx

excel

spreadsheet

data

Contributors

Dependencies

ballerina/time/2.7.0 ballerina/io/1.8.0 ballerina/log/2.14.0

Cookie policy

Delete policy

functions

classes

enums

annotations

configurables

records

errors

objectTypes

unionTypes

ballerina/xlsx Ballerina library

Overview

Quickstart

Step 1: Import the module

Step 2: Invoke module functions

Parse an XLSX file into typed records

Write records to an XLSX file

Map non-matching headers with @xlsx:Name

Work with multiple sheets via the Workbook API

Bytes in, bytes out

Read and write Excel Tables

Bind dates and times to time:Civil, time:Date, or time:TimeOfDay

Continue parsing on row-level errors with fail-safe mode

Step 3: Run the Ballerina application

Examples

Functions

fromBytes

Parameters

Return Type

fromFile

Parameters

Return Type

parseSheet

Parameters

Return Type

parseTable

Parameters

Return Type

writeSheet

Parameters

Return Type

writeTable

Parameters

Return Type

Classes

xlsx: Workbook

Constructor

getSheetNames

Return Type

getSheetCount

Return Type

hasSheet

Parameters

Return Type

getSheet

Parameters

Return Type

createSheet

Parameters

Return Type

deleteSheet

Parameters

Return Type

save

Return Type

saveAs

Parameters

Return Type

toBytes

Return Type

close

Return Type

getTable

Parameters

Return Type

getAllTables

Return Type

Enums

xlsx: ErrorLogContentType

Members

xlsx: FileWriteOption

Map non-matching headers with `@xlsx:Name`

Work with multiple sheets via the `Workbook` API

Bind dates and times to `time:Civil`, `time:Date`, or `time:TimeOfDay`