Sheet

object

One sheet of a workbook: read single cells, read whole columns grouped by header, and write cells. Rows and columns are numbered from 1. Cell texts are what Excel would show: formulas are evaluated, numbers follow the number format of the cell and dates follow `Xlsx.dateFormat`.

Kind Object
Library Xlsx
Category Files & Documents
Functions 4

How to get one

local workbook = Xlsx.open(location)
local sheet = workbook.sheet(sheetName)

Returned by

Workbook.sheet()

Functions

headers table Returns the data of the sheet grouped by column header, taking row 1 as the header row.
columns table Returns every column of the sheet including the first row, without treating any row as a header.
getCell string Returns the text of one cell.
setCell self Sets one cell, creating the row and cell when needed.

`headers()`

sheet.headers()→table

Returns the data of the sheet grouped by column header, taking row 1 as the header row. The result is a table keyed by header text. Each value is a list with one entry for every existing row below the header, and each entry is a table `{ value = text, row = number, col = number }` where `row` and `col` are the 1-based position in the sheet. Rows that do not exist in the file are skipped, so use `row` rather than the list position to know where a value came from. Columns with an empty header and everything to the right of the last non-empty header are left out. Values are trimmed, and a cell whose text starts with `#--` and ends with `--#` is returned as an empty string. An empty sheet gives an empty table.

Returns

table Table keyed by header text; each value is a list of `{ value, row, col }` tables

Example runs in CI

local sheet = Xlsx.open("template.xlsx").sheet("Sheet1")
sheet.setCell(1, 2, "Alice")
sheet.setCell(2, 2, 30)
local data = sheet.headers()
print(data.name[1].value, data.age[1].value, data.name[1].row)

`columns()`

sheet.columns()→table

Returns every column of the sheet including the first row, without treating any row as a header. The result is a list of columns in sheet order. Each column is a list of `{ value = text, row = number, col = number }` tables, one for every existing row. Columns that have no cell in any row are skipped, so the list index can differ from the sheet's column number: use `col`. Values are trimmed, and a cell with text between `#--` and `--#` is returned as an empty string. An empty sheet gives an empty table.

Returns

table List of columns; each column is a list of `{ value, row, col }` tables

`getCell()`

sheet.getCell(column: number, row: number)→string

Returns the text of one cell. The column comes first, then the row (`getCell(2, 5)` is cell B5). The text is not trimmed. Formulas are evaluated, booleans are `TRUE` or `FALSE` and a cell that does not exist gives an empty string. A column below 1 throws a Java error; a row below 1 gives an empty string.

Parameters

column number: Column number, starting at 1 (A = 1)
row number: Row number, starting at 1

Returns

string The cell text, or an empty string when the cell is empty

Example runs in CI

local sheet = Xlsx.open("template.xlsx").sheet("Sheet1")
print(sheet.getCell(1, 1), sheet.getCell(2, 1), "[" .. sheet.getCell(9, 9) .. "]")

`setCell()`

sheet.setCell(cellLoc: number, rowLoc: number, value: any)→self

Sets one cell, creating the row and cell when needed. The column comes first, then the row. The cell style (number or date format) is not changed. Numbers, and text that parses as a number (`"42"`, `"1.5"`, `"007"`, `"5d"`), are stored as a number, so `"007"` becomes `7`. Booleans are stored as boolean cells, `nil` clears the cell (it becomes empty) and anything else is stored as text; text starting with `=` is stored as text, not as a formula. Nothing reaches disk until `Workbook.save`. A column or row below 1 throws a Java error.

Parameters

cellLoc number: Column number, starting at 1 (A = 1)
rowLoc number: Row number, starting at 1
value any: Text, number or boolean to store; numeric text becomes a number, nil clears the cell

Returns

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

Example runs in CI

local sheet = Xlsx.open("template.xlsx").sheet("Sheet1")
sheet.setCell(1, 2, "Alice").setCell(2, 2, 31)
print(sheet.getCell(1, 2), sheet.getCell(2, 2))