Skip to main content

Sheet Class

Sheet is Alia's built-in two-dimensional array table class, used to handle mappings between multiple arrays. It is a data structure similar to Excel spreadsheets, supporting efficient table operations, data lookup, and table joins.

Creating a Sheet​

Creating with Headers and Data​

const headers = ['id', 'name', 'age', 'city']
const data = [
  [1, 'Alice', 25, 'Beijing'],
  [2, 'Bob', 30, 'Shanghai'],
  [3, 'Charlie', 35, 'Guangzhou']
]
const sheet = new Sheet [headers, data]

Creating an Empty Sheet​

const emptySheet = new Sheet

Importing from a CSV File​

Alia supports importing data directly from CSV files via the import statement, automatically converting it to the Sheet type.

~ Contents of data.csv:
~ name,age,city,score
~ Alice,30,Beijing,95
~ Bob,25,Shanghai,88
~ Charlie,35,Guangzhou,92

import 'data.csv' as sheet

print sheet.getCell at row 0 column 'name'   ~ outputs 'Alice'
print sheet.getCell at row 1 column 'age'    ~ outputs 30
print sheet.getCell at row 2 column 'score'  ~ outputs 92

Features:

  • The first line of the CSV file is automatically recognized as the header
  • Type recognition for numbers, booleans (true/false, case-insensitive), and strings
  • Empty cells are converted to null
  • Quoted fields are supported (fields containing commas)
  • Windows and Unix line endings are handled automatically

Instance Properties​

headers​

Returns the header array (read-only).

const sheet = new Sheet [['id', 'name', 'age'], [[1, 'Alice', 25]]]
print sheet.headers  ~ outputs: ['id', 'name', 'age']

data​

Returns the data as a two-dimensional array (read-only).

const sheet = new Sheet [['id', 'name'], [[1, 'Alice'], [2, 'Bob']]]
print sheet.data  ~ outputs: [[1, 'Alice'], [2, 'Bob']]

rowMap​

Returns the row index mapping table (read-only). Index keys are prefixed with __index_.

const sheet = new Sheet [['id', 'name'], [[1, 'Alice'], [2, 'Bob']]]
sheet.buildIndex 'id'
print sheet.rowMap  ~ outputs: [object Map]

Instance Methods​

getRow​

Returns the row at the given index. The signature is sheet.getRow index; it takes a row index and returns a copy of that row's array.

const sheet = new Sheet [['id', 'name', 'age'], [[1, 'Alice', 25], [2, 'Bob', 30]]]
print sheet.getRow 0  ~ outputs: [1, 'Alice', 25]
print sheet.getRow 1  ~ outputs: [2, 'Bob', 30]

getColumn​

Returns all data in the specified column. The signature is sheet.getColumn key; it takes a column name and returns a one-dimensional array for that column.

const sheet = new Sheet [['id', 'name', 'city'], [[1, 'Alice', 'Beijing'], [2, 'Bob', 'Shanghai']]]
print sheet.getColumn 'name'  ~ outputs: ['Alice', 'Bob']
print sheet.getColumn 'city'  ~ outputs: ['Beijing', 'Shanghai']

getCell​

Returns the value of the specified cell. The signature is sheet.getCell at row R column C, returning the cell's value.

const sheet = new Sheet [['id', 'name', 'age'], [[1, 'Alice', 25], [2, 'Bob', 30]]]
print sheet.getCell at row 0 column 'name'  ~ outputs: 'Alice'
print sheet.getCell at row 1 column 'age'   ~ outputs: 30

setCell​

Sets the value of the specified cell. The signature is sheet.setCell at row R column C to V, returning the Sheet itself to support chaining.

const sheet = new Sheet [['id', 'name', 'city'], [[1, 'Alice', 'Beijing'], [2, 'Bob', 'Shanghai']]]
sheet.setCell at row 1 column 'city' to 'Guangzhou'
print sheet.getCell at row 1 column 'city'  ~ outputs: 'Guangzhou'

appendRow​

Appends a row of data. The signature is sheet.appendRow row; it takes a row array and returns the Sheet itself to support chaining. Array literal arguments are passed in call form.

const sheet = new Sheet [['id', 'name'], [[1, 'Alice'], [2, 'Bob']]]
call sheet.appendRow [[3, 'Charlie']]
print sheet.getRow 2  ~ outputs: [3, 'Charlie']

lookup​

Vertical lookup (sheet.lookup V returning R in M). Searches matchCol for a row equal to value and returns that row's returnCol value. Similar to Excel's VLOOKUP function. Returns null when there is no match.

const sheet = new Sheet [['id', 'name', 'city'], [[1, 'Alice', 'Beijing'], [2, 'Bob', 'Shanghai']]]

~ Look up a city by ID
const city = sheet.lookup 2 returning 'city' in 'id'
print city  ~ outputs: 'Shanghai'

~ Looking up a nonexistent value returns null
const notFound = sheet.lookup 99 returning 'city' in 'id'
print notFound  ~ outputs: null

join​

Table join (users.join depts on L and R as Y). Joins another Sheet based on the left and right column names and merges the data. When the join type after as is omitted, an inner join is used.

Parameters:

  • otherSheet: another Sheet object
  • leftCol (after on): the column name in the current Sheet used for the join
  • rightCol (after and): the column name in the other Sheet used for the join
  • joinType (after as): the join type, one of: 'inner' (default), 'left'
~ Create the users table
const users = new Sheet [['id', 'name', 'dept_id'], [[1, 'Alice', 101], [2, 'Bob', 102], [3, 'Charlie', 101]]]

~ Create the departments table
const depts = new Sheet [['dept_id', 'dept_name'], [[101, 'Engineering'], [102, 'Marketing'], [103, 'Sales']]]

~ Inner join
const innerJoined = users.join depts on 'dept_id' and 'dept_id' as 'inner'
print innerJoined.headers  ~ outputs: ['id', 'name', 'dept_id', 'dept_name']

~ Left join
const leftJoined = users.join depts on 'dept_id' and 'dept_id' as 'left'

groupBy​

Group mapping. The signature is sheet.groupBy key with fn; it groups rows by a column and applies an aggregation function to each group. The aggregation function is an Alias object that receives each group's rows array as an argument.

Parameters:

  • groupCol: the column name used for grouping
  • aggFunc: the aggregation function, an Alias object that receives each group's rows array as an argument
const sales = new Sheet [['product', 'region', 'amount'], [
  ['A', 'North', 100],
  ['A', 'South', 150],
  ['B', 'North', 80],
  ['B', 'South', 120],
  ['A', 'North', 50]
]]

~ Group by product and compute total sales
const sumFunc = alias <Array rows> -> {
  let sum = 0
  each row in rows {
    sum = sum + row[2]
  }
  sum
}
const totalByProduct = sales.groupBy 'product' with sumFunc

print totalByProduct  ~ outputs a Sheet table: headers are ['product', 'result'], and the grouped result data is [['A', 300], ['B', 200]]

buildIndex​

Builds an index. The signature is sheet.buildIndex key; it builds a hash map for the specified column to speed up subsequent lookup and join operations. Returns the Sheet itself to support chaining.

const sheet = new Sheet [['id', 'name', 'city'], [
  [1, 'Alice', 'Beijing'],
  [2, 'Bob', 'Shanghai'],
  [3, 'Charlie', 'Guangzhou']
]]

~ Build an index on the id column
sheet.buildIndex 'id'

~ Subsequent lookup operations are accelerated by the index
const city = sheet.lookup 2 returning 'city' in 'id'
print city  ~ outputs: 'Shanghai'

findIndexByValue​

Finds a row index by value. The signature is sheet.findIndexByValue val in column col, returning the index of the first matching row, or -1 when there is no match.

const sheet = new Sheet [['id', 'name'], [[1, 'Alice'], [2, 'Bob']]]
print sheet.findIndexByValue 2 in column 'id'   ~ outputs: 1
print sheet.findIndexByValue 99 in column 'id'  ~ outputs: -1

toArray​

Exports a plain array (including the header). Takes no arguments and is invoked in call form; returns a two-dimensional array whose first row is the header and whose remaining rows are data rows.

const sheet = new Sheet [['id', 'name'], [[1, 'Alice'], [2, 'Bob']]]
const arr = call sheet.toArray
print arr  ~ outputs: [['id', 'name'], [1, 'Alice'], [2, 'Bob']]

toObject​

Exports an object. Takes no arguments and is invoked in call form; returns an object keyed by row index with row objects as values.

const sheet = new Sheet [['id', 'name'], [[1, 'Alice'], [2, 'Bob']]]
const obj = call sheet.toObject
print obj  ~ outputs: *[0: *[id: 1, name: 'Alice'], 1: *[id: 2, name: 'Bob']]

clone​

Deep-copies the current Sheet. Takes no arguments and is invoked in call form; returns a new Sheet object containing the same data without sharing references.

const original = new Sheet [['id', 'name'], [[1, 'Alice'], [2, 'Bob']]]
const copy = call original.clone

~ Modifying the copy does not affect the original
copy.setCell at row 0 column 'name' to 'Alicia'
print original.getCell at row 0 column 'name'  ~ outputs: 'Alice'
print copy.getCell at row 0 column 'name'      ~ outputs: 'Alicia'

toString​

Returns the string representation of the Sheet. Takes no arguments and is invoked in call form.

const sheet = new Sheet [['id', 'name'], [[1, 'Alice'], [2, 'Bob']]]
print call sheet.toString
~ outputs:
~ Sheet:
~ id	name
~ 1	Alice
~ 2	Bob

valueOf​

Returns the Sheet's internal value object, containing headers and data. Takes no arguments and is invoked in call form.

const sheet = new Sheet [['id', 'name'], [[1, 'Alice']]]
print call sheet.valueOf  ~ outputs: [object Object]

Usage Examples​

Basic Operations​

~ Create a Sheet
const employees = new Sheet [['id', 'name', 'department', 'salary'], [
  [1, 'Alice', 'Engineering', 8000],
  [2, 'Bob', 'Marketing', 6000],
  [3, 'Charlie', 'Engineering', 9000],
  [4, 'David', 'Sales', 5000]
]]

~ Get all employee names
const names = employees.getColumn 'name'
print ('Employee names: ' .. (call names.toString))

~ Get a specific employee's information
const alice = employees.getRow 0
print ('Info for Alice: ' .. (call alice.toString))

~ Update an employee's salary
employees.setCell at row 1 column 'salary' to 6500
print ('New salary for Bob: ' .. (employees.getCell at row 1 column 'salary'))

~ Add a new employee
call employees.appendRow [[5, 'Eve', 'Engineering', 7500]]
print ('Row count (including header): ' .. ((call employees.toArray).length))

Data Lookup​

const products = new Sheet [['product_id', 'product_name', 'price'], [
  [101, 'Laptop', 5999],
  [102, 'Phone', 3999],
  [103, 'Tablet', 2999],
  [104, 'Headphones', 899]
]]

~ Use lookup to find a product price
const laptopPrice = products.lookup 101 returning 'price' in 'product_id'
print ('Laptop price: ' .. laptopPrice)

~ Build an index to speed up lookups
products.buildIndex 'product_id'

~ Subsequent lookups will be faster
const phonePrice = products.lookup 102 returning 'price' in 'product_id'
print ('Phone price: ' .. phonePrice)

~ Find a row index by value
print products.findIndexByValue 103 in column 'product_id'  ~ outputs: 2

Table Joins​

~ Create the orders table
const orders = new Sheet [['order_id', 'customer_id', 'amount'], [
  [1, 1, 100],
  [2, 2, 200],
  [3, 1, 150],
  [4, 3, 300]
]]

~ Create the customers table
const customers = new Sheet [['customer_id', 'customer_name', 'email'], [
  [1, 'Alice', 'alice@example.com'],
  [2, 'Bob', 'bob@example.com'],
  [5, 'Eve', 'eve@example.com']
]]

~ Inner join: returns only matching records
const orderDetails = orders.join customers on 'customer_id' and 'customer_id' as 'inner'
print 'Inner join result:'
print call orderDetails.toString

~ Left join: keeps all records from the left table
const orderDetailsLeft = orders.join customers on 'customer_id' and 'customer_id' as 'left'
print 'Left join result:'
print call orderDetailsLeft.toString

Grouping and Aggregation​

const salesData = new Sheet [['region', 'quarter', 'sales'], [
  ['North', 'Q1', 1000],
  ['North', 'Q2', 1200],
  ['South', 'Q1', 800],
  ['South', 'Q2', 900],
  ['North', 'Q3', 1100],
  ['South', 'Q3', 950]
]]

~ Group by region and compute total sales
const sumFunc = alias <Array rows> -> {
  let total = 0
  each row in rows {
    total = total + row[2]
  }
  total
}
const totalByRegion = salesData.groupBy 'region' with sumFunc

print 'Total sales by region:'
print totalByRegion

~ Group by quarter and compute average sales
const avgFunc = alias <Array rows> -> {
  let sum = 0
  each row in rows {
    sum = sum + row[2]
  }
  sum / rows.length
}
const avgByQuarter = salesData.groupBy 'quarter' with avgFunc

print 'Average sales by quarter:'
print avgByQuarter

Data Export​

const sheet = new Sheet [['id', 'name', 'email'], [
  [1, 'Alice', 'alice@example.com'],
  [2, 'Bob', 'bob@example.com']
]]

~ Export as a plain array
const arr = call sheet.toArray
print 'Exported array:'
print call arr.toString

~ Export as an object
print call sheet.toObject

~ Deep copy
const backup = call sheet.clone
backup.setCell at row 0 column 'email' to 'alice.new@example.com'
print 'Original data unmodified:'
print sheet.getCell at row 0 column 'email'

Notes​

  1. Index caching: The lookup method automatically builds and caches an index for the matched column; subsequent lookups on the same column use the cached index.
  2. Index invalidation: Modifying data (e.g. via setCell or appendRow) clears all cached indexes, which then need to be rebuilt.
  3. Join types: Currently inner and left join types are supported.
  4. Aggregation functions: The groupBy aggregation function is an Alias object that receives each group's rows array as an argument.

Next Steps​