Sys.Xls
Sys.Xls reads and writes Excel workbooks and CSV files. The SimpleXls class covers the common cases: turning a sheet into a list of objects or entities, and filling a sheet from a list. For anything else, its workbook property and Sys.Xls.ExcelJs give access to the full ExcelJS library.
Availability: Server
Properties
Section titled “Properties”ExcelJs
Section titled “ExcelJs”const ExcelJs: typeof xlsThe ExcelJS library (exceljs 4.4.0), re-exported as-is. Use it for workbook features that SimpleXls does not cover, such as styles, merged cells or formulas.
const workbook = new Sys.Xls.ExcelJs.Workbook()const sheet = workbook.addWorksheet('Report')sheet.getCell('A1').value = 'Total'const xls = new Sys.Xls.SimpleXls(workbook)Type: typeof xls (the exceljs module) · Availability: Server
Classes
Section titled “Classes”SimpleXls
Section titled “SimpleXls”SimpleXls wraps one ExcelJS workbook. Create one with new Sys.Xls.SimpleXls() for an empty workbook, or load an existing file with fromFile(), fromBuffer() or fromFileAccessToken(). Sheets are addressed by name or by number, starting with 1.
// orders: a list of This.Order entity instancesconst xls = Sys.Xls.SimpleXls.fromEntities(orders, { columns: ['orderNumber', 'orderDate', 'customerName'], additionalColumns: { total: (order) => order.quantity * order.unitPrice, }, headerRowTitles: { orderNumber: 'Order', orderDate: 'Date', customerName: 'Customer', total: 'Total' }, columnFormats: { orderDate: 'dd.mm.yyyy', total: '#,##0.00' },})await xls.toFile('exports/orders.xlsx', 'xlsx')constructor()
Section titled “constructor()”constructor(workbook?: ExcelJs.Workbook)Creates a SimpleXls for an existing ExcelJS workbook, or for a new empty one. If the workbook has no sheets, Lowgile adds one.
| Parameter | Type | Description |
|---|---|---|
workbook | ExcelJs.Workbook | Optional. The workbook to wrap. Defaults to a new workbook. |
Availability: Server
workbook
Section titled “workbook”workbook: ExcelJs.WorkbookThe underlying ExcelJS workbook. Use it to add sheets, style cells or read cells directly.
Type: ExcelJs.Workbook · Availability: Server
parseToObjects()
Section titled “parseToObjects()”parseToObjects<T extends object>(options?: Partial<SimpleXls.XlsParseToObjectOptions<T>>): any[]Reads a sheet into a list of plain objects. The header row provides the property names; each following row becomes one object. Reading stops at the first empty row.
| Parameter | Type | Description |
|---|---|---|
options | Partial<SimpleXls.XlsParseToObjectOptions<T>> | Optional. Which sheet and rows to read. |
XlsParseToObjectOptions fields
| Field | Type | Required | Description |
|---|---|---|---|
sheetNameOrNumber | string | number | No | Name or number (starting with 1) of the sheet. Defaults to 1. |
startOnRowNumber | number | No | Number of the header row. Data is read from the next row on. Defaults to 1. |
attributesForHeaderTitles | Record<string, keyof T | null> | ((headerTitle: string) => keyof T | null) | null | No | Maps header titles to property names. |
Cell values are simplified: a hyperlink gives its text, rich text gives the plain text, a formula gives its last calculated result, and an error gives the error code.
Returns: any[] · Availability: Server
Note
parseToObjects() ignores attributesForHeaderTitles: the property names are always the header titles. Use parseToEntities() if you need the mapping.
parseToEntities()
Section titled “parseToEntities()”parseToEntities<T extends EntityInstance>(entityConstrutor: Class<T, []>, options?: Partial<SimpleXls.XlsParseToEntityOptions<T>>): T[]Reads a sheet into a list of new entity instances. Each column whose header matches an entity property name is copied into that property; other columns are ignored. Reading stops at the first empty row.
| Parameter | Type | Description |
|---|---|---|
entityConstrutor | Class<T, []> | The entity class, for example This.Material. |
options | Partial<SimpleXls.XlsParseToEntityOptions<T>> | Optional. The same fields as XlsParseToObjectOptions. Use attributesForHeaderTitles to map header titles to property names, either as an object ({ 'Material no.': 'materialNumber' }) or as a function that receives the header title. A header mapped to null is ignored. |
Cell values are simplified as for parseToObjects() and assigned without type conversion.
Returns: T[] · Availability: Server
fillWithObjects()
Section titled “fillWithObjects()”fillWithObjects<T extends object, A extends AdditionalColumns<T>>(objects: T[], options?: Partial<SimpleXls.XlsFillWithObjectOptions<T, A>>): voidWrites a list of objects into a sheet, one row per object, with an optional header row. Lowgile sets the column widths to fit the content and, if there is a header row, makes it bold and adds an auto filter.
| Parameter | Type | Description |
|---|---|---|
objects | T[] | The objects to write. |
options | Partial<SimpleXls.XlsFillWithObjectOptions<T, A>> | Optional. Which columns to write and how. |
XlsFillWithObjectOptions fields
| Field | Type | Required | Description |
|---|---|---|---|
columns | (Columns<T, A> | '*')[] | ((availableColumns: Columns<T, A>[]) => Columns<T, A>[]) | No | The columns, in order, as property names. '*' stands for all properties of the first object (for entities, their property names). A function receives the available properties and returns the column list. Defaults to ['*']. |
additionalColumns | A | No | Computed columns, as an object mapping a column name to a function (object, entityIdx, allEntities) => value. Columns not listed in columns are added at the end. |
hideColumns | Columns<T, A>[] | ((availableColumns: Columns<T, A>[]) => Columns<T, A>[]) | No | Columns to leave out, for example to exclude some properties covered by '*'. |
avoidDuplicateColumns | boolean | No | Writes each column only once, even if it is listed several times. Defaults to true. |
includeHeaderRow | boolean | No | Writes a header row before the data. Defaults to true. |
headerRowTitles | string[] | Partial<Record<Columns<T, A>, string>> | ((column: Columns<T, A>) => string) | No | Header titles: an array in column order, an object mapping column names to titles, or a function. Columns without a title use the column name. |
columnFormats | Partial<Record<Columns<T, A>, string>> | No | Excel number formats per column, for example 'dd.mm.yyyy' or '#,##0.00'. |
startOnRowNumber | number | No | Row on which writing starts. Defaults to 1. |
sheetNameOrNumber | string | number | No | Name or number (starting with 1) of the sheet to write into. The sheet must exist. Defaults to 1. |
entityConstructorForHeaderRowIfListIsEmpty | Class<T, []> | null | No | Entity class used to work out the '*' columns when objects is empty, so that the header row is still written. |
Returns: void · Availability: Server
fillWithEntities()
Section titled “fillWithEntities()”fillWithEntities<T extends EntityInstance, A extends AdditionalColumns<T>>(entities: T[], options?: Partial<SimpleXls.XlsFillWithEntityOptions<T, A>>): voidWrites a list of entity instances into a sheet. Works like fillWithObjects(); '*' covers the entity’s properties.
| Parameter | Type | Description |
|---|---|---|
entities | T[] | The entity instances to write. |
options | Partial<SimpleXls.XlsFillWithEntityOptions<T, A>> | Optional. The same fields as XlsFillWithObjectOptions. |
Returns: void · Availability: Server
fromEntities()
Section titled “fromEntities()”fromEntities<T extends EntityInstance, A extends AdditionalColumns<T>>(entities: T[], options?: Partial<SimpleXls.XlsFillWithEntityOptions<T, A>>): SimpleXlsCreates a new workbook and fills its first sheet with a list of entity instances, as fillWithEntities() does.
| Parameter | Type | Description |
|---|---|---|
entities | T[] | The entity instances to write. |
options | Partial<SimpleXls.XlsFillWithEntityOptions<T, A>> | Optional. See fillWithObjects(). |
Returns: SimpleXls · Availability: Server
fromFile()
Section titled “fromFile()”fromFile(path: string, fileType: 'xlsx'): Promise<SimpleXls>fromFile(path: string, fileType: 'csv', options?: Partial<ExcelJs.CsvReadOptions>): Promise<SimpleXls>fromFile(path: string, fileType: XlsFileType, options?: Partial<ExcelJs.CsvReadOptions>): Promise<SimpleXls>Loads an XLSX or CSV file from the working file area.
| Parameter | Type | Description |
|---|---|---|
path | string | Path of the file, relative to the working file area. |
fileType | 'xlsx' | 'csv' | Format of the file. |
options | Partial<ExcelJs.CsvReadOptions> | Optional, CSV only. ExcelJS CSV options, such as the delimiter (parserOptions.delimiter). |
Returns: Promise<SimpleXls> · Availability: Server
fromBuffer()
Section titled “fromBuffer()”fromBuffer(buffer: Buffer | ArrayBuffer, fileType: 'xlsx'): Promise<SimpleXls>fromBuffer(buffer: Buffer | ArrayBuffer, fileType: 'csv', options?: Partial<ExcelJs.CsvReadOptions>): Promise<SimpleXls>fromBuffer(buffer: Buffer | ArrayBuffer, fileType: XlsFileType, options?: Partial<ExcelJs.CsvReadOptions>): Promise<SimpleXls>Loads an XLSX or CSV file from binary data, for example an uploaded file.
| Parameter | Type | Description |
|---|---|---|
buffer | Buffer | ArrayBuffer | Content of the file. |
fileType | 'xlsx' | 'csv' | Format of the file. |
options | Partial<ExcelJs.CsvReadOptions> | Optional, CSV only. ExcelJS CSV options. |
Returns: Promise<SimpleXls> · Availability: Server
fromFileAccessToken()
Section titled “fromFileAccessToken()”fromFileAccessToken(token: string | FileAccessToken, fileType: 'xlsx'): Promise<SimpleXls>fromFileAccessToken(token: string | FileAccessToken, fileType: 'csv', options?: Partial<ExcelJs.CsvReadOptions>): Promise<SimpleXls>fromFileAccessToken(token: string | FileAccessToken, fileType: XlsFileType, options?: Partial<ExcelJs.CsvReadOptions>): Promise<SimpleXls>Loads an XLSX or CSV file that a file access token refers to.
| Parameter | Type | Description |
|---|---|---|
token | string | FileAccessToken | The file access token, or its token string. |
fileType | 'xlsx' | 'csv' | Format of the file. |
options | Partial<ExcelJs.CsvReadOptions> | Optional, CSV only. ExcelJS CSV options. |
Returns: Promise<SimpleXls> · Availability: Server
toFile()
Section titled “toFile()”toFile(path: string, fileType: 'xlsx', options?: ExcelJs.XlsxWriteOptions): Promise<void>toFile(path: string, fileType: 'csv', options?: ExcelJs.CsvWriteOptions): Promise<void>toFile(path: string, fileType: XlsFileType, options?: ExcelJs.CsvWriteOptions | ExcelJs.XlsxWriteOptions): Promise<void>Writes the workbook as an XLSX or CSV file to the working file area. CSV files contain the first sheet only.
| Parameter | Type | Description |
|---|---|---|
path | string | Path of the file, relative to the working file area. |
fileType | 'xlsx' | 'csv' | Format to write. |
options | ExcelJs.CsvWriteOptions | ExcelJs.XlsxWriteOptions | Optional. ExcelJS write options for the chosen format. |
Returns: Promise<void> · Availability: Server
toBuffer()
Section titled “toBuffer()”toBuffer(fileType: 'xlsx', options?: ExcelJs.XlsxWriteOptions): Promise<Buffer>toBuffer(fileType: 'csv', options?: Partial<ExcelJs.CsvWriteOptions>): Promise<Buffer>toBuffer(fileType: XlsFileType, options?: Partial<ExcelJs.CsvWriteOptions> | ExcelJs.XlsxWriteOptions): Promise<Buffer>Returns the workbook as XLSX or CSV data, for example to attach it to an email.
| Parameter | Type | Description |
|---|---|---|
fileType | 'xlsx' | 'csv' | Format to write. |
options | Partial<ExcelJs.CsvWriteOptions> | ExcelJs.XlsxWriteOptions | Optional. ExcelJS write options for the chosen format. For XLSX, useStyles defaults to true. |
Returns: Promise<Buffer> · Availability: Server
toFileAccessToken()
Section titled “toFileAccessToken()”toFileAccessToken(fileName: string, fileType: 'xlsx', fileAccessTokenOptions: CreateFileAccessTokenOptions, xlsOptions?: ExcelJs.XlsxWriteOptions): Promise<FileAccessToken>toFileAccessToken(fileName: string, fileType: 'csv', fileAccessTokenOptions: CreateFileAccessTokenOptions, csvOptions?: ExcelJs.CsvWriteOptions): Promise<FileAccessToken>toFileAccessToken(fileName: string, fileType: XlsFileType, fileAccessTokenOptions: CreateFileAccessTokenOptions, xlsOptions?: ExcelJs.CsvWriteOptions | ExcelJs.XlsxWriteOptions): Promise<FileAccessToken>Stores the workbook as a file in the database and returns a file access token for it, for example to let the End user download it.
| Parameter | Type | Description |
|---|---|---|
fileName | string | File name offered for download. |
fileType | 'xlsx' | 'csv' | Format to write. |
fileAccessTokenOptions | CreateFileAccessTokenOptions | Validity of the token. |
xlsOptions / csvOptions | ExcelJs.CsvWriteOptions | ExcelJs.XlsxWriteOptions | Optional. ExcelJS write options for the chosen format. |
CreateFileAccessTokenOptions fields
| Field | Type | Required | Description |
|---|---|---|---|
isPermanent | boolean | Yes | If true, the token does not expire. |
userId | UserIdType | No | Restricts the token to this user. |
expiresAt | Date | No | When the token expires. |
expiresOnDownload | boolean | No | If true, the token expires after the first download. |
deleteFileWhenExpired | boolean | No | Deletes the stored file when the token expires. Defaults to true. |
Returns: Promise<FileAccessToken> · Availability: Server
convertColumnNumberToName()
Section titled “convertColumnNumberToName()”convertColumnNumberToName(colNrOneBased: number): stringConverts a column number to its Excel column letters, for example 1 to A and 28 to AB.
| Parameter | Type | Description |
|---|---|---|
colNrOneBased | number | Column number, starting with 1. |
Returns: string · Availability: Server
convertColumnNameToNumber()
Section titled “convertColumnNameToNumber()”convertColumnNameToNumber(colName: string): numberConverts Excel column letters to the column number, for example AB to 28. The letters must be upper case.
| Parameter | Type | Description |
|---|---|---|
colName | string | Column letters, for example 'AB'. |
Returns: number · Availability: Server
SimpleXls.XlsParseToObjectOptions
Section titled “SimpleXls.XlsParseToObjectOptions”interface XlsParseToObjectOptions<T extends object>Options for parseToObjects(). See the field table there.
SimpleXls.XlsParseToEntityOptions
Section titled “SimpleXls.XlsParseToEntityOptions”type XlsParseToEntityOptions<T extends EntityInstance> = XlsParseToObjectOptions<EntityRecord<T>>Options for parseToEntities(), with attributesForHeaderTitles restricted to the entity’s property names.
SimpleXls.XlsFillWithObjectOptions
Section titled “SimpleXls.XlsFillWithObjectOptions”interface XlsFillWithObjectOptions<T extends object, A extends AdditionalColumns<T>>Options for fillWithObjects(). See the field table there.
SimpleXls.XlsFillWithEntityOptions
Section titled “SimpleXls.XlsFillWithEntityOptions”interface XlsFillWithEntityOptions<T extends EntityInstance, A extends AdditionalColumns<T>>Options for fillWithEntities() and fromEntities(), with the same fields as XlsFillWithObjectOptions.
Related
Section titled “Related”- Work with Excel and CSV files
- Sys.Fs
- Sys.Files
- Sys API overview