Skip to main content

Sheet Objects

Excel supports 4 different types of "sheets":

  • "worksheets": normal sheets
  • "chartsheets": full-tab charts
  • "macrosheets": legacy (pre-VBA) macros
  • "dialogsheets": legacy (pre-VBA) dialog windows

Generic Sheet Object

Generic sheets are plain JavaScript objects. Each key that does not start with ! is an A1-style address whose corresponding value is a cell object.

Cell Storage

By default, the parsers and utility functions generate "sparse-mode" worksheet objects. sheet[address] returns the cell object for the specified address.

Dense Mode

When the option dense: true is passed, parsers will generate a "dense-mode" worksheet where cells are stored in an array of arrays. sheet["!data"][R][C] returns the cell object at row R and column C (zero-indexed values).

When processing small worksheets in older environments, sparse worksheets are more efficient than dense worksheets. In newer browsers, when dealing with very large worksheets, dense sheets use less memory and tend to be more efficient.

Migrating to Dense Mode (click to show)

read, readFile, write, writeFile, and the various API functions support sparse and dense worksheets. Functions that accept worksheet or workbook objects (e.g. writeFile and sheet_to_json) will detect dense sheets.

The option dense: true should be used when creating worksheet or book objects:

-var workbook = XLSX.read(data, {...opts});
+var workbook = XLSX.read(data, {...opts, dense: true});

-var sheet = XLSX.utils.aoa_to_sheet([[1,2,3],[4,5,6]], {...opts});
+var sheet = XLSX.utils.aoa_to_sheet([[1,2,3],[4,5,6]], {...opts, dense: true});

-var sheet = XLSX.utils.json_to_sheet([{x:1,y:2}], {...opts});
+var sheet = XLSX.utils.json_to_sheet([{x:1,y:2}], {...opts, dense: true});

Code that manually loops over worksheet objects should test for "!data" key:

const { decode_range, encode_cell } = XLSX.utils;

function log_all_cells(ws) {
var range = decode_range(ws["!ref"]);
var dense = ws["!data"] != null; // test if sheet is dense
for(var R = 0; R <= range.e.r; ++R) {
for(var C = 0; C <= range.e.c; ++C) {
var cell = dense ? ws["!data"]?.[R]?.[C] : ws[encode_cell({r:R, c:C})];
console.log(R, C, cell);
}
}
}

Sheet Properties

Each key starts with !. The properties are accessible as sheet[key].

  • sheet['!ref']: A-1 based range representing the sheet range. Functions that work with sheets should use this parameter to determine the range. Cells that are assigned outside of the range are not processed. In particular, when writing a sheet by hand, cells outside of the range are not included

    Functions that handle sheets should test for the presence of !ref field. If the !ref is omitted or is not a valid range, functions are free to treat the sheet as empty or attempt to guess the range. The standard utilities that ship with this library treat sheets as empty (for example, the CSV output is empty string).

    When reading a worksheet with the sheetRows property set, the ref parameter will use the restricted range. The original range is set at ws['!fullref']

  • sheet['!margins']: Object representing the page margins. The default values follow Excel's "normal" preset. Excel also has a "wide" and a "narrow" preset but they are stored as raw measurements. The main properties are listed below:

Page margin details (click to show)
keydescription"normal""wide""narrow"
leftleft margin (inches)0.71.00.25
rightright margin (inches)0.71.00.25
toptop margin (inches)0.751.00.75
bottombottom margin (inches)0.751.00.75
headerheader margin (inches)0.30.50.3
footerfooter margin (inches)0.30.50.3
/* Set worksheet sheet to "normal" */
ws["!margins"]={left:0.7, right:0.7, top:0.75,bottom:0.75,header:0.3,footer:0.3}
/* Set worksheet sheet to "wide" */
ws["!margins"]={left:1.0, right:1.0, top:1.0, bottom:1.0, header:0.5,footer:0.5}
/* Set worksheet sheet to "narrow" */
ws["!margins"]={left:0.25,right:0.25,top:0.75,bottom:0.75,header:0.3,footer:0.3}

Worksheet Object

In addition to the aforementioned sheet keys, worksheets also add:

  • ws['!cols']: array of column properties objects. Column widths are actually stored in files in a normalized manner, measured in terms of the "Maximum Digit Width" (the largest width of the rendered digits 0-9, in pixels). When parsed, the column objects store the pixel width in the wpx field, character width in the wch field, and the maximum digit width in the MDW field.

  • ws['!rows']: array of row properties objects as explained later in the docs. Each row object encodes properties including row height and visibility.

  • ws['!merges']: array of range objects corresponding to the merged cells in the worksheet. Plain text formats do not support merge cells. CSV export will write all cells in the merge range if they exist, so be sure that only the first cell (upper-left) in the range is set.

  • ws['!outline']: configure how outlines should behave. Options default to the default settings in Excel 2019:

keyExcel featuredefault
aboveDisable "Summary rows below detail"false
leftDisable "Summary rows to the right of detail"false
  • ws['!protect']: object of write sheet protection properties. The password key specifies the password for formats that support password-protected sheets (XLSX/XLSB/XLS). The writer uses the XOR obfuscation method. The following keys control the sheet protection -- set to false to enable a feature when sheet is locked or set to true to disable a feature:
Worksheet Protection Details (click to show)
keyfeature (true=disabled / false=enabled)default
selectLockedCellsSelect locked cellsenabled
selectUnlockedCellsSelect unlocked cellsenabled
formatCellsFormat cellsdisabled
formatColumnsFormat columnsdisabled
formatRowsFormat rowsdisabled
insertColumnsInsert columnsdisabled
insertRowsInsert rowsdisabled
insertHyperlinksInsert hyperlinksdisabled
deleteColumnsDelete columnsdisabled
deleteRowsDelete rowsdisabled
sortSortdisabled
autoFilterFilterdisabled
pivotTablesUse PivotTable reportsdisabled
objectsEdit objectsenabled
scenariosEdit scenariosenabled
  • ws['!autofilter']: AutoFilter object following the schema:
type AutoFilter = {
ref:string; // A-1 based range representing the AutoFilter table range
}

Other Sheet Types

Chartsheet Object

Chartsheets are represented as standard sheets. They are distinguished with the !type property set to "chart".

The underlying data and !ref refer to the cached data in the chartsheet. The first row of the chartsheet is the underlying header.

Macrosheet Object

Macrosheets are represented as standard sheets. They are distinguished with the !type property set to "macro".

Dialogsheet Object

Dialogsheets are represented as standard sheets. They are distinguished with the !type property set to "dialog".