Skip to content

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

const ExcelJs: typeof xls

The 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

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 instances
const 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(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.

ParameterTypeDescription
workbookExcelJs.WorkbookOptional. The workbook to wrap. Defaults to a new workbook.

Availability: Server

workbook: ExcelJs.Workbook

The underlying ExcelJS workbook. Use it to add sheets, style cells or read cells directly.

Type: ExcelJs.Workbook · Availability: Server

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.

ParameterTypeDescription
optionsPartial<SimpleXls.XlsParseToObjectOptions<T>>Optional. Which sheet and rows to read.

XlsParseToObjectOptions fields

FieldTypeRequiredDescription
sheetNameOrNumberstring | numberNoName or number (starting with 1) of the sheet. Defaults to 1.
startOnRowNumbernumberNoNumber of the header row. Data is read from the next row on. Defaults to 1.
attributesForHeaderTitlesRecord<string, keyof T | null> | ((headerTitle: string) => keyof T | null) | nullNoMaps 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<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.

ParameterTypeDescription
entityConstrutorClass<T, []>The entity class, for example This.Material.
optionsPartial<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<T extends object, A extends AdditionalColumns<T>>(objects: T[], options?: Partial<SimpleXls.XlsFillWithObjectOptions<T, A>>): void

Writes 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.

ParameterTypeDescription
objectsT[]The objects to write.
optionsPartial<SimpleXls.XlsFillWithObjectOptions<T, A>>Optional. Which columns to write and how.

XlsFillWithObjectOptions fields

FieldTypeRequiredDescription
columns(Columns<T, A> | '*')[] | ((availableColumns: Columns<T, A>[]) => Columns<T, A>[])NoThe 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 ['*'].
additionalColumnsANoComputed 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.
hideColumnsColumns<T, A>[] | ((availableColumns: Columns<T, A>[]) => Columns<T, A>[])NoColumns to leave out, for example to exclude some properties covered by '*'.
avoidDuplicateColumnsbooleanNoWrites each column only once, even if it is listed several times. Defaults to true.
includeHeaderRowbooleanNoWrites a header row before the data. Defaults to true.
headerRowTitlesstring[] | Partial<Record<Columns<T, A>, string>> | ((column: Columns<T, A>) => string)NoHeader titles: an array in column order, an object mapping column names to titles, or a function. Columns without a title use the column name.
columnFormatsPartial<Record<Columns<T, A>, string>>NoExcel number formats per column, for example 'dd.mm.yyyy' or '#,##0.00'.
startOnRowNumbernumberNoRow on which writing starts. Defaults to 1.
sheetNameOrNumberstring | numberNoName or number (starting with 1) of the sheet to write into. The sheet must exist. Defaults to 1.
entityConstructorForHeaderRowIfListIsEmptyClass<T, []> | nullNoEntity class used to work out the '*' columns when objects is empty, so that the header row is still written.

Returns: void · Availability: Server

fillWithEntities<T extends EntityInstance, A extends AdditionalColumns<T>>(entities: T[], options?: Partial<SimpleXls.XlsFillWithEntityOptions<T, A>>): void

Writes a list of entity instances into a sheet. Works like fillWithObjects(); '*' covers the entity’s properties.

ParameterTypeDescription
entitiesT[]The entity instances to write.
optionsPartial<SimpleXls.XlsFillWithEntityOptions<T, A>>Optional. The same fields as XlsFillWithObjectOptions.

Returns: void · Availability: Server

fromEntities<T extends EntityInstance, A extends AdditionalColumns<T>>(entities: T[], options?: Partial<SimpleXls.XlsFillWithEntityOptions<T, A>>): SimpleXls

Creates a new workbook and fills its first sheet with a list of entity instances, as fillWithEntities() does.

ParameterTypeDescription
entitiesT[]The entity instances to write.
optionsPartial<SimpleXls.XlsFillWithEntityOptions<T, A>>Optional. See fillWithObjects().

Returns: SimpleXls · Availability: Server

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.

ParameterTypeDescription
pathstringPath of the file, relative to the working file area.
fileType'xlsx' | 'csv'Format of the file.
optionsPartial<ExcelJs.CsvReadOptions>Optional, CSV only. ExcelJS CSV options, such as the delimiter (parserOptions.delimiter).

Returns: Promise<SimpleXls> · Availability: Server

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.

ParameterTypeDescription
bufferBuffer | ArrayBufferContent of the file.
fileType'xlsx' | 'csv'Format of the file.
optionsPartial<ExcelJs.CsvReadOptions>Optional, CSV only. ExcelJS CSV options.

Returns: Promise<SimpleXls> · Availability: Server

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.

ParameterTypeDescription
tokenstring | FileAccessTokenThe file access token, or its token string.
fileType'xlsx' | 'csv'Format of the file.
optionsPartial<ExcelJs.CsvReadOptions>Optional, CSV only. ExcelJS CSV options.

Returns: Promise<SimpleXls> · Availability: Server

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.

ParameterTypeDescription
pathstringPath of the file, relative to the working file area.
fileType'xlsx' | 'csv'Format to write.
optionsExcelJs.CsvWriteOptions | ExcelJs.XlsxWriteOptionsOptional. ExcelJS write options for the chosen format.

Returns: Promise<void> · Availability: Server

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.

ParameterTypeDescription
fileType'xlsx' | 'csv'Format to write.
optionsPartial<ExcelJs.CsvWriteOptions> | ExcelJs.XlsxWriteOptionsOptional. ExcelJS write options for the chosen format. For XLSX, useStyles defaults to true.

Returns: Promise<Buffer> · Availability: Server

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.

ParameterTypeDescription
fileNamestringFile name offered for download.
fileType'xlsx' | 'csv'Format to write.
fileAccessTokenOptionsCreateFileAccessTokenOptionsValidity of the token.
xlsOptions / csvOptionsExcelJs.CsvWriteOptions | ExcelJs.XlsxWriteOptionsOptional. ExcelJS write options for the chosen format.

CreateFileAccessTokenOptions fields

FieldTypeRequiredDescription
isPermanentbooleanYesIf true, the token does not expire.
userIdUserIdTypeNoRestricts the token to this user.
expiresAtDateNoWhen the token expires.
expiresOnDownloadbooleanNoIf true, the token expires after the first download.
deleteFileWhenExpiredbooleanNoDeletes the stored file when the token expires. Defaults to true.

Returns: Promise<FileAccessToken> · Availability: Server

convertColumnNumberToName(colNrOneBased: number): string

Converts a column number to its Excel column letters, for example 1 to A and 28 to AB.

ParameterTypeDescription
colNrOneBasednumberColumn number, starting with 1.

Returns: string · Availability: Server

convertColumnNameToNumber(colName: string): number

Converts Excel column letters to the column number, for example AB to 28. The letters must be upper case.

ParameterTypeDescription
colNamestringColumn letters, for example 'AB'.

Returns: number · Availability: Server

interface XlsParseToObjectOptions<T extends object>

Options for parseToObjects(). See the field table there.

type XlsParseToEntityOptions<T extends EntityInstance> = XlsParseToObjectOptions<EntityRecord<T>>

Options for parseToEntities(), with attributesForHeaderTitles restricted to the entity’s property names.

interface XlsFillWithObjectOptions<T extends object, A extends AdditionalColumns<T>>

Options for fillWithObjects(). See the field table there.

interface XlsFillWithEntityOptions<T extends EntityInstance, A extends AdditionalColumns<T>>

Options for fillWithEntities() and fromEntities(), with the same fields as XlsFillWithObjectOptions.