Skip to main content

Models

Models provide the primary API for interacting with SheetXL and they are implemented as plain TypeScript libraries that can be used in any Javascript environment (browser, node, other).

While SheetXL has many interfaces the most commonly used interfaces are:

IWorkbookModelISheetModelgetSheetsISheetRangegetRangeICellModelforEachICellStylestyleISheetHeadersModelsICellHeaderISheetHeaderRangeIWorkbookRangegetRangeforEachforEachstyleICellValuevaluegetRowsgetColumns

WorkbookModel​

Workbook models contain the the most common interaction mechanisms within SheetXL.

To create a workbook model simply import the @sheetxl/models package and create a new instance.

// create a default workbook
const workbookModel:IWorkbookModel = new WorkbookModel();

Ranges​

The simplest way to interact with data is by using IWorkbookRange. This can be accessed from the IWorkbookModel.

// create a default workbook with no data
const workbookModel:IWorkbookModel = new WorkbookModel();
// get a range that can be iterated or updated
const range:IWorkbookRange = workbookModel.getRange('Sheet1!A1:E5000');

To listen for data range changes use addRangeListener.

// create a default workbook
const workbookModel:IWorkbookModel = new WorkbookModel();
// get a range that can be iterators or allow for updates
const listener =
  (range: IWorkbookRange | null, sheet: ISheetModel | null, workbook: IWorkbookModel | null): void => {
  // do something with the range.
};
const removeListener:RemoveListener = addRangeListener(
  workbookModel.getRange('Sheet1!A1:E5000'), listener);
// call removeListener() to remove the listener when finished

Working with Data​

Reading Data Values​

Spreadsheets can have very large amounts of data and often contain a huge number of empty elements. (Excel has 1.6 billion cells even for an empty spreadsheet).

To enable high performance read-access IWorkbookRange.forEach is used to iterate over the data skipping any blank cells.

const options:CellVisitorOptions = {
  includeEmpty: false; // This is the default but shown for illustration purposes.
}
workbookModel.getRange('Sheet1!A1:E5000').forEach(
  (value: ICellValue, coord: CellCoords, context: CellVisitorContext) => {
    // only cells with a value will be visited
  }, options);

Writing Data Values​

IWorkbookRange.update is used for updates and to support batch and non-batch.

// update a single cell
const updateExample1:RangeUpdateValue = 100;
workbookModel.getRange('Sheet1!A1').update(updateExample1);

// update a range of cells
const updateExample2:RangeUpdateValue = [[100, 200], [300, 400]];
workbookModel.getRange('Sheet1!A1').update(updateExample2);
tip

To support batching of non-contiguous data see ISheetModel.setCellPairs.

Formatting​

ICellStyle is used to format cell styles on a range of cells or cell headers. The cell style is contained in ICellModel.style interface.

Reading Cell Styles Values​

Cell Styles are accessed as a property on the ICellModel interface which is available from the IWorkbookRange interface or from the forEach.

// Note recommended if access many values; use forEach to iterate over data
const cell:ICellModel = workbookModel.getRange('Sheet1!A1').cell();
const style:ICellStyle = cell.style;

workbookModel.getRange('Sheet1!A1:E5').forEach(
  (value: ICellValue, coord: CellCoords, context: CellVisitorContext) => {
    // only cells with a value will be visited. A complex ICellModel can be accessed using the context.
    const cell:ICellModel = context.getCell();
    const style:ICellStyle = cell.style;
  }, options);

Writing Cell Styles Values​

Writing cells styles is similar to writing values and the IWorkbookRange.update is used. The biggest addition to data values is that cell styles are complex objects that support 'partial' updates.

To update a style a JSON object that mirrors the ICellStyle interface is used but with the following rules.

  1. If value is a valid type it will replace the existing value.
  2. If value is null it will 'default' the value to the original style value.
  3. If the value is undefined or not present it will be ignored.
  4. If the value is a string it will be parsed similar to CSS (See individual style property for details); for example a border can be set by using the following: 1px solid red or a font can be set by using 14pt Arial.
  5. If the value is a function then the function will be called with the original value and the result will be applied Note - This can have negative performance impacts so use carefully.
Updating style
// update style to several cells
const updateExample1:RangeUpdateValue = {
  style: {
    font: '14pt Arial',
    fill: 'aliceBlue', // PresetColor.AliceBlue,
  }
};
workbookModel.getRange('Sheet1!A1:E5').update(updateExample1);

// now revert the fill to the normal style
workbookModel.getRange('Sheet1!A1:E5').update({
  style: {
    fill: null,
  }
});

Headers​

Reading or Updating Headers​

Headers contain information that is applied to entire rows or columns such as:

  • Size (Width/Height)
  • Visibility
  • Custom Titles
Updating headers
// Set 4 columns
const updateHeader:CellHeaderUpdate = {
  size: 120
};
workbookModel.getRange('Sheet1!B:E').updateColumns(updateHeader);
// Hide some rows
const updateHeader:CellHeaderUpdate = {
  style: {
    hidden: true,
  }
};
workbookModel.getRange('Sheet1!2:20').updateRows(updateHeader);
note

As an optimization headers contain styling information for cell ranges that span the entire header, for example column width or row height, but as a best practice it is recommended to use the ICellRange to update styling.

Inserting/Removing Cells​

Inserting and removing cells or headers (rows/columns) is supported via the ICellRange interface using IWorkbookRange.insertCells and IWorkbookRange.removeCells.

Inserting/Removing Headers
// insert column
workbookModel.getRange('Sheet1!B:E').insertCells('right');

// remove row
workbookModel.getRange('Sheet1!2:2').removeCells('up');

Views​

Workbooks and Sheets have layout information that is not part of the data but only effects how information is displayed for a given user/context.

Reading Views​

Read SheetView Property
// find out if the row grid lines are visible
const view:ISheetView = workbookModel.getSheet('Sheet1').getView();
const isShowRowGridLines:boolean =  view.showRowGridlines;

Writing Views​

Writing SheetView Property
// set showRowGridLines to false
workbookModel.getSheet('Sheet1').setView({
  showRowGridlines: true,
});

Listening to View Changes​

Listening to SheetView Updates
const listener:ISheetModelListener = {
  onViewChange?(update: ISheetView | null | undefined): void => {
    // something interesting here
  }
};
const removeListener = workbookModel.getSheet('Sheet1').addListener(listener);
// don't forget to removelistener when finished
removeListener();

Themes​

Cell Styles use Themes, implemented as IDocTheme, to determine colors, fonts, and other visual effects. These are based on the OOXML standards used by Microsoft Excel and (mostly) Google Sheets.

The theme is generally embedded within the Sheet or Workbook so if these are shared the themes will also be shared.

A working example is available at CodeSandbox/custom-app-theme

Loading and Saving State​

Most models implement the JSONSerializable interface which enables a common API for loading and saving state as JSON. If a model implements JSONSerializable then by convention it's first argument in the constructor is a matching JSON object. This is done to avoid initializing objects twice.

Save to JSON
const asJSON:JSONSerializable<WorkbookJSON> = workbookModel.toJSON();
// save asJSON to local storage or db
Read from JSON
const asJSON:JSONSerializable<WorkbookJSON> = { /* from db or local storage */ };
// preferred way
const workbookModel:IWorkbookModel = new WorkbookModel(asJSON);
// works but discouraged
const workbookModel2:IWorkbookModel = new WorkbookModel();
workbookModel2.fromJSON(asJSON);

Create workbook model from initial data​

A common use case is to just display a Workbook with initial data.
The WorkbookModel constructor also takes a 2D array of ICellValues.

const workbook:IWorkbookModel = new WorkbookModel([
  [1,2,3],
  [4,5,6],
  [7,8,9]
]);

Import and Exporting​

Import and Exporting file formats other than the native SheetXL JSON type is done using using the @sheetxl/io package. This is MIT open-sourced and is available on github.

The formats, including native JSON, currently support are:

  • CSV
  • Excel
  • SheetXL JSON (native)

The @sheetxl/sheet-mui/DefaultIORegistry has methods that dynamically import the @sheetxl/io package to convenient import and export.This code is also MIT open-sourced and is available on github.

@sheetxl/sheet-mui/DefaultIORegistry
/**
 * Import a workbook from an ArrayBuffer.
 */
DefaultIORegistry.importFromArrayBuffer(
  arrayBuffer: ArrayBuffer | Promise<ArrayBuffer>,
  key: string,
  options: MultiImportWorkbookOptions=null
): Promise<IWorkbookModel>;

/**
 * Will try to read from local file system using available IO handlers.
 * The input can be either a File, a Promise<File>, or a string.
 * If a string is provided, if should be either an extension or a mimetype
 * that will be passed to the accept attribute of an
 * input: @see https://developer.mozilla.org/en-US/docs/Web/HTML/Attributes/accept.
 * @param input
 * @param options
 * @returns a promise that resolves to an ImportResults object.
 *
 * @remarks
 * This is just a wrapper around CommonUtils.openFile, DefaultIORegistry.importFromArrayBuffer,
 *  and notification.
 */
DefaultIORegistry.importFromFile(
  input: File | Promise<File> | string=null,
  options: MultiImportWorkbookOptions=null,
  notifier?: Notifier
): Promise<ImportResults>;

/**
 * Exports to the local file system attempting to use the fileName provided. This will
 * use the exportType to determine the export handler to use.
 * @returns a Promise indicating success or failure.
 */
DefaultIORegistry.exportToFile(
  workbookModel: IWorkbookModel,
  fileName: string | null,
  exportType?: ExportType
): Promise<boolean>;
note

If another format is needed or something is missing please let me know. I am working with SheetJS and custom libraries as required.

More Models Examples​

All (most) UI operations are available via the models and can be access via the IWorkbookRange interface.

Another way to see a set of examples is to use the use developer console of full demo:

via demo console​

  • Open the standalone demo at standalone demo
  • Open the developer tools (Ctl+Shift+I)
  • In the console window type sheetXl.moreExamples()

via CodeSandbox​