Skip to main content

Tutorial

SheetJS presents a simple JS interface that works with "Array of Arrays" and "Array of JS Objects". The API functions are building blocks that should be combined with other JS APIs to solve problems.

The discussion focuses on the problem solving mindset. API details are covered in other parts of the documentation.

The goal of this example is to generate a XLSX workbook of US President names and birthdays. Click here to jump to the live demo. The sequence diagram below shows the process:

Acquire Data​

Raw Data​

The raw data is available in JSON form. For convenience, it has been mirrored here

Acquiring the data is straightforward with fetch:

const url = "https://sheetjs.com/data/executive.json";
const raw_data = await (await fetch(url)).json();

The raw data is an Array of objects. This is the data for John Adams:

{
  "id": { /* (data omitted) */ },
  "name": {
    "first": "John",          // <-- first name
    "last": "Adams"           // <-- last name
  },
  "bio": {
    "birthday": "1735-10-19", // <-- birthday
    "gender": "M"
  },
  "terms": [
    { "type": "viceprez", /* (other fields omitted) */ },
    { "type": "viceprez", /* (other fields omitted) */ },
    { "type": "prez", /* (other fields omitted) */ }
  ]
}

Filtering for Presidents​

The dataset includes Aaron Burr, a Vice President who was never President!

Array#filter creates a new array with the desired rows. A President served at least one term with type set to "prez". To test if a particular row has at least one "prez" term, Array#some is another native JS function. The complete filter would be:

const prez = raw_data.filter(row => row.terms.some(term => term.type === "prez"));

Reshaping the Array​

For this example, the name will be the first name combined with the last name (row.name.first + " " + row.name.last) and the birthday will be available at row.bio.birthday. Using Array#map, the dataset can be massaged in one call:

const rows = prez.map(row => ({
  name: row.name.first + " " + row.name.last,
  birthday: row.bio.birthday
}));

The result is an array of "simple" objects with no nesting:

[
  { name: "George Washington", birthday: "1732-02-22" },
  { name: "John Adams", birthday: "1735-10-19" },
  // ... one row per President
]

Create a Workbook​

With the cleaned dataset, XLSX.utils.json_to_sheet generates a worksheet:

const worksheet = XLSX.utils.json_to_sheet(rows);

XLSX.utils.book_new creates a new workbook and XLSX.utils.book_append_sheet appends a worksheet to the workbook. The new worksheet will be called "Dates":

const workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, worksheet, "Dates");

Clean up Workbook​

The data is in the workbook and can be exported.

Rough export

There are multiple opportunities for improvement: the headers can be renamed and the column widths can be adjusted. SheetJS Pro offers additional styling options like cell styling and frozen rows.

Changing Header Names (click to show)

By default, json_to_sheet creates a worksheet with a header row. In this case, the headers come from the JS object keys: "name" and "birthday".

The headers are in cells A1 and B1. XLSX.utils.sheet_add_aoa can write text values to the existing worksheet starting at cell A1:

XLSX.utils.sheet_add_aoa(worksheet, [["Name", "Birthday"]], { origin: "A1" });
Changing Column Widths (click to show)

Some of the names are longer than the default column width. Column widths are set by setting the "!cols" worksheet property.

The following line sets the width of column A to approximately 10 characters:

worksheet["!cols"] = [ { wch: 10 } ]; // set column A width to 10 characters

One Array#reduce call over rows can calculate the maximum width:

const max_width = rows.reduce((w, r) => Math.max(w, r.name.length), 10);
worksheet["!cols"] = [ { wch: max_width } ];

Export a File​

XLSX.writeFile creates a spreadsheet file and tries to write it to the system. In the browser, it will try to prompt the user to download the file. In NodeJS, it will write to the local directory.

note

XLSX.writeFileXLSX only writes XLSX files and is recommended when the export will always be in the .xlsx format. writeFileXLSX is more amenable to tree shaking. This example uses XLSX.writeFile since writeFileXLSX does not support other common export formats like .xls or .xlsb or .csv.

compression: true enables ZIP compression for XLSX and other formats.

XLSX.writeFile(workbook, "Presidents.xlsx", { compression: true });

Final export

Live Demo​

This demo runs in the web browser! Click "Click to Generate File!" and the browser should generate a XLSX file.

Result
Loading...
Live Editor

Run the Demo Locally​

Save the following script to snippet.html and open the page. The page must be hosted (no file:/// access).

https://sheetjs.com/pres.html is a hosted version of the page.

<body>
<script src="https://cdn.sheetjs.com/xlsx-latest/package/dist/xlsx.full.min.js"></script>
<script>
(async() => {
  /* fetch JSON data and parse */
  const url = "https://sheetjs.com/data/executive.json";
  const raw_data = await (await fetch(url)).json();

  /* filter for the Presidents */
  const prez = raw_data.filter(row => row.terms.some(term => term.type === "prez"));

  /* flatten objects */
  const rows = prez.map(row => ({
    name: row.name.first + " " + row.name.last,
    birthday: row.bio.birthday
  }));

  /* generate worksheet and workbook */
  const worksheet = XLSX.utils.json_to_sheet(rows);
  const workbook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workbook, worksheet, "Dates");

  /* fix headers */
  XLSX.utils.sheet_add_aoa(worksheet, [["Name", "Birthday"]], { origin: "A1" });

  /* calculate column width */
  const max_width = rows.reduce((w, r) => Math.max(w, r.name.length), 10);
  worksheet["!cols"] = [ { wch: max_width } ];

  /* create an XLSX file and try to save to Presidents.xlsx */
  XLSX.writeFile(workbook, "Presidents.xlsx", { compression: true });
})();
</script>
<body>