In-memory query engine over typed columns. Tables are filled row by row or loaded from sources (CSV files, folders, the program's own objects) and queried with SQL-like statements; aggregates, Describe() and Finder (file search) are included. Compile with -lib query.
XlsxSource
An Excel workbook (.xlsx) as tables, one per worksheet, named after the sheet. Cells keep their types: numbers, text (shared or inline, rich text joined, whitespace kept), booleans, dates, and error values (as their text, such as '#DIV/0!'). A formula cell is its last calculated value. A column is typed by reading all of it, as CsvSource types CSV columns: whole numbers make an INT column, other numbers a FLOAT column, dates a DATE column, booleans a BOOL column, and any mix, or text, a STRING column holding each cell as it would display ('1.5', 'TRUE', '2026-03-15'). An empty cell is empty.
Dates are numbers in a date or time format, built in or custom ('yyyy-mm-dd', 'h:mm'), counted from 1900 or, when the workbook says so, 1904. In the 1900 system Excel counts a 29 February 1900 that never was (Lotus 1-2-3's leap-year bug): serials below 60 are moved a day so they read as Excel shows them, and 60 itself reads as 28 February 1900. Elapsed-time formats ('[h]:mm') stay numbers, in days. Times are wall-clock, read to the second.
The table is the sheet's used range: its columns run from the first used column to the last, and its rows from the first row holding a value (the header, by default) to the last. Rows and cells a sheet leaves out are empty, so the table's rows line up with the sheet's. Header cells name the columns; an empty one takes its column letter ('C'), and a repeated name gets '_2', '_3'. Without a header the columns are named by letter.
A merged range keeps its value in its top-left cell and leaves the rest empty, as Excel stores it; SetFillMerged copies the value into every cell of the range instead.
The workbook is read with ArchiveSource's zip reader, so the same limits and checks apply: every part is checked against its CRC32 and inflated no further than it says, within 256 MB a part and 1 GB in all unless SetLimits says otherwise.
Example
catalog := Catalog->New();
if(catalog->Load(XlsxSource->New("budget.xlsx"))) {
rs := catalog->Query("select dept, sum(amount) as total from \"Q1 2026\" group by dept");
};Operations
- New
- FromBytes
- GetError
- GetSheets
- GetTables
- Load
- Read
- ReadAll
- SetFillMerged
- SetFirstRow
- SetHeader
- SetLimits
- SetPrefix
FromBytes # function
Makes a source over a workbook already in memory
function : FromBytes(bytes:Byte[]) ~ XlsxSourceParameters
| Name | Type | Description |
|---|---|---|
| bytes | Byte | .xlsx bytes |
Return
| Type | Description |
|---|---|
| XlsxSource | source |
GetError #
Gets the reason the last read failed
method : public : GetError() ~ StringReturn
| Type | Description |
|---|---|
| String | error, Nil if none |
GetSheets #
Gets the workbook's worksheet names, in workbook order
method : public : GetSheets() ~ String[]Return
| Type | Description |
|---|---|
| String | sheet names, Nil if the workbook cannot be read (see GetError) |
GetTables #
Gets the tables the workbook provides; their columns are known only after reading them
method : public : GetTables() ~ TableSpec[]Return
| Type | Description |
|---|---|
| TableSpec | table shapes |
Load #
Loads the worksheets into a catalog; nothing is added unless every sheet reads. The scan hint is not used.
method : public : Load(catalog:Catalog, hint:ScanHint) ~ BoolParameters
| Name | Type | Description |
|---|---|---|
| catalog | Catalog | catalog |
| hint | ScanHint | ignored; the engine applies the query afterwards |
Return
| Type | Description |
|---|---|
| Bool | true if loaded |
New # constructor
Constructor; every worksheet becomes a table
New(path:String)Parameters
| Name | Type | Description |
|---|---|---|
| path | String | workbook file or URL |
New # constructor
Constructor for one worksheet
New(path:String, sheet:String)Parameters
| Name | Type | Description |
|---|---|---|
| path | String | workbook file or URL |
| sheet | String | worksheet name |
Read #
Reads one worksheet: the one named in the constructor, or else the first
method : public : Read() ~ TableReturn
| Type | Description |
|---|---|
| Table | table, Nil on error (see GetError) |
ReadAll #
Reads every worksheet, or the one named in the constructor
method : public : ReadAll() ~ Vector<Table>Return
| Type | Description |
|---|---|
| Vector<Table> | tables in workbook order, Nil on error (see GetError) |
SetFillMerged #
Sets whether a merged range's value fills every cell of the range, rather than only its top-left cell (the default, which is how Excel stores it)
method : public : SetFillMerged(fill:Bool) ~ NilParameters
| Name | Type | Description |
|---|---|---|
| fill | Bool | true to fill merged ranges |
SetFirstRow #
Sets the sheet row the table starts at (the header, if there is one), for sheets with a title or notes above the data. By default the table starts at the first row holding a value.
method : public : SetFirstRow(row:Int) ~ NilParameters
| Name | Type | Description |
|---|---|---|
| row | Int | sheet row number, from 1; 0 for the default |
SetHeader #
Sets whether the first row names the columns (the default). Without a header every row is data and the columns are named by letter: A, B, C ...
method : public : SetHeader(header:Bool) ~ NilParameters
| Name | Type | Description |
|---|---|---|
| header | Bool | true if the first row is a header |
SetLimits #
Sets how much may be decompressed: a part of the workbook larger than 'member_bytes' is refused, and so is a read that takes the total past 'total_bytes'. The defaults are 256 MB and 1 GB.
method : public : SetLimits(member_bytes:Int, total_bytes:Int) ~ NilParameters
| Name | Type | Description |
|---|---|---|
| member_bytes | Int | most bytes one part may hold uncompressed |
| total_bytes | Int | most bytes all parts read may hold uncompressed |