Skip to main content

Export Tutorial

Many modern data sources provide an API to download data in JSON format. Many users prefer to work in spreadsheet software. SheetJS libraries help bridge the gap by translating programmer-friendly JSON to user-friendly workbooks.

The goal of this example is to generate a XLSX workbook of US President names and birthdates. We will download and wrangle a JSON dataset using standard JavaScript functions. Once we have a simple list of names and birthdates, we will use SheetJS API functions to build a workbook object and export to XLSX.

The "Live Demo" section includes a working demo in this page! "Run the Demo Locally" shows how to run the workflow in iOS / Android apps, desktop apps, NodeJS scripts and other environments.

The following sequence diagram shows the process:

sequenceDiagram
  actor U as User
  participant P as Page
  participant A as API
  U->>P: click button
  P->>A: fetch data
  A->>P: raw data
  Note over P: process data
  Note over P: make workbook
  Note over P: export file
  P->>U: download workbook

Acquire Data​

The raw data is available in JSON form1. It has been mirrored at https://docs.sheetjs.com/executive.json

Raw Data​

Acquiring the data is straightforward with fetch:

const url = "https://docs.sheetjs.com/executive.json";
const raw_data = await (await fetch(url)).json();
Code Explanation (click to show)

fetch is a low-level API for downloading data from an endpoint. It separates the network step from the response parsing step.

Network Step

fetch(url) returns a Promise representing the network request. The browser will attempt to download data from the URL. If the network request succeeded, the Promise will "return" with a Response object.

Using modern syntax, inside an async function, code should await the fetch:

const response = await fetch(url);

Checking Status Code

If the file is not available, the fetch will still succeed.

The status code, stored in the status property of the Response object, is a standard HTTP status code number. Code should check the result.

Typically servers will return status 404 "File not Found" if the file is not available. A successful request should have status 200 "OK".

Extracting Data

Response#json will try to parse the data using JSON.parse. Like fetch, the json method returns a Promise that must be await-ed:

const raw_data = await response.json();

The Response object has other useful methods. Response#arrayBuffer will return the raw data as an ArrayBuffer, suitable for parsing workbook files.

Production Use

Functions can test each part independently and report different errors:

async function get_data_from_endpoint(url) {
  /* perform network request */
  let response;
  try {
    response = await fetch(url);
  } catch(e) {
    /* network error */
    throw new Error(`Network Error: ${e.message}`);
  }

  /* check status code */
  if(response.status == 404) {
    /* server 404 error -- file not found */
    throw new Error("File not found");
  }
  if(response.status != 200) {
    /* for most servers, a successful response will have status 200 */
    throw new Error(`Server status ${response.status}: ${response.statusText}`);
  }

  /* parse JSON */
  let data;
  try {
     data = await response.json();
  } catch(e) {
    /* parsing error */
    throw new Error(`Parsing Error: ${e.message}`);
  }

  return data;
}

The raw data is an Array of objects2. For this discussion, the relevant data for John Adams is shown below:

{
  "name": {
    "first": "John",          // <-- first name
    "last": "Adams"           // <-- last name
  },
  "bio": {
    "birthday": "1735-10-19", // <-- birthday
  },
  "terms": [                  // <-- array of presidential terms
    { "type": "viceprez", "start": "1789-04-21", },
    { "type": "viceprez", "start": "1793-03-04", },
    { "type": "prez",     "start": "1797-03-04", } // <-- presidential term
  ]
}

Filtering for Presidents​

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

The terms field of each object is an array of terms. A term is a Presidential term if the type property is "prez". We are interested in Presidents that served at least one term. The following line creates an array of Presidents:

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

JavaScript code can be extremely concise. The "Code Explanation" blocks explain the code in more detail.

Code Explanation (click to show)

Verifying if a person was a US President

Array#some takes a function and calls it on each element of an array in order. If the function ever returns true, Array#some returns true. If each call returns false, Array#some returns false.

The following function tests if a term is presidential:

const term_is_presidential = term => term.type == "prez";

To test if a person was a President, that function should be tested against every term in the terms array:

const person_was_president = person => person.terms.some(term => term.type == "prez");

Creating a list of US Presidents

Array#filter takes a function and returns an array. The function is called on each element in order. If the function returns true, the element is added to the final array. If the function returns false, the element is not added.

Using the previous function, this line filters the dataset for Presidents:

const prez = raw_data.filter(row => person_was_president(row));

Placing the person_was_president function in-line, the final code is:

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

Sorting by First Term​

The dataset is sorted in chronological order by the first presidential or vice presidential term. The Vice President and President in a given term are sorted alphabetically.

Barack Obama became President and Joseph Biden became Vice President in 2009. Since "Biden" is alphabetically before "Obama", Biden's data appears first.

The goal is to sort the presidents in order of their initial presidential term.

The first step is adding the first presidential term start date to the dataset. The following code looks at each president and creates a start property that represents the start of the first presidential term.

prez.forEach(row => row.start = row.terms.find(term => term.type === "prez").start);
Code Explanation (click to show)

Finding the first presidential term

Array#find will find the first value in an array that matches a criterion. The first presidential term can be found with the following function:

const first_prez_term = prez => prez.terms.find(term => term.type === "prez");

If no element in the array matches the criterion, Array#find does not return a value. In this case, since prez was created by filtering for people that served at least one presidential term, the code assumes a term exists.

The start of a President's first Presidential term is therefore

const first_prez_term_start = prez => first_prez_term(prez).start;

Adding the first start date to one row

The following function creates the desired start property:

const prez_add_start = prez => prez.start = first_prez_term_start(prez);

Adding the first start date to each row

Array#forEach takes a function and calls it for every element in the array. Any modifications to objects affect the objects in the original array.

The previous function can be used directly:

prez.forEach(row => prez_add_start(row));

Working in reverse, each partial function can be inserted in place. These lines of code are equivalent:

/* start */
prez.forEach(row => prez_add_start(row));

/* put `prez_add_start` definition into the line */
prez.forEach(row => row.start = first_prez_term_start(row));

/* put `first_prez_term_start` definition into the line */
prez.forEach(row => row.start = first_prez_term(row).start);

/* put `first_prez_term` definition into the line */
prez.forEach(row => row.start = row.terms.find(term => term.type === "prez").start);

At this point, each row in the prez array has a start property. Since the start properties are strings, the following line sorts the array:

prez.sort((l,r) => l.start.localeCompare(r.start));
Code Explanation (click to show)

Comparator Functions and Relative Ordering in JavaScript

A comparator takes two arguments and returns a number that represents the relative ordering. comparator(a,b) should return a negative number if a should be placed before b. If b should be placed before a, the comparator should return a positive number.

If the start properties were numbers, the following comparator would suffice:

const comparator_numbers = (a,b) => a - b;

For strings, JavaScript comparison operators can work:

const comparator_string_simple = (a,b) => a == b ? 0 : a < b ? -1 : 1;

However, that comparator does not handle diacritics. For example, "z" < "é". It is strongly recommended to use String#localeCompare to compare strings:

const comparator_string = (a,b) => a.localeCompare(b);

Comparing two Presidents

The start properties of the Presidents should be compared:

const compare_prez = (a,b) => (a.start).localeCompare(b.start);

Sorting the Array

Array#sort takes a comparator function and sorts the array in place. Using the Presidential comparator:

prez.sort((l,r) => compare_prez(l,r));

Placing the compare_prez function in the body:

prez.sort((l,r) => l.start.localeCompare(r.start));

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
}));
Code Explanation (click to show)

Wrangling One Data Row

The key fields for John Adams are shown below:

{
  "name": {
    "first": "John",          // <-- first name
    "last": "Adams"           // <-- last name
  },
  "bio": {
    "birthday": "1735-10-19", // <-- birthday
  }
}

If row is the object, then

  • row.name.first is the first name ("John")
  • row.name.last is the last name ("Adams")
  • row.bio.birthday is the birthday ("1735-10-19")

The desired object has a name and birthday field:

function get_data(row) {
  var name = row.name.first + " " + row.name.last;
  var birthday = row.bio.birthday;
  return ({
    name: name,
    birthday: birthday
  });
}

This can be shortened by adding the fields to the object directly:

function get_data(row) {
  return ({
    name: row.name.first + " " + row.name.last,
    birthday: row.bio.birthday
  });
}

When writing an arrow function that returns an object, parentheses are required:

//  open paren required --V
  const get_data = row => ({
    name: row.name.first + " " + row.name.last,
    birthday: row.bio.birthday
  });
// ^-- close paren required

Wrangling the entire dataset

Array#map calls a function on each element of an array and returns a new array with the return values of each function.

Using the previous method:

const rows = prez.map(row => get_data(row));

The get_data function can be added in place:

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_sheet3 generates a worksheet:

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

XLSX.utils.book_new4 creates a new workbook and XLSX.utils.book_append_sheet5 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_aoa6 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.7

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 } ];

After cleanup, the generated workbook looks like the screenshot below:

Final export

Export a File​

XLSX.writeFile8 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.

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

Live Demo​

This demo runs in the web browser! Click "Click to Generate File!" and the browser should try to create Presidents.xlsx

function Presidents() { return ( <button onClick={async () => {
  /* fetch JSON data and parse */
  const url = "https://docs.sheetjs.com/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"));

  /* sort by first presidential term */
  prez.forEach(row => row.start = row.terms.find(term => term.type === "prez").start);
  prez.sort((l,r) => l.start.localeCompare(r.start));

  /* 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 });
}}><b>Click to Generate file!</b></button> ); }

https://sheetjs.com/pres.html is a hosted version of this demo.

Run the Demo Locally​

Save the following script to SheetJSStandaloneDemo.html:

SheetJSStandaloneDemo.html
<body>
<script src="https://cdn.sheetjs.com/xlsx-0.20.3/package/dist/xlsx.full.min.js"></script>
<script>
(async() => {
  /* fetch JSON data and parse */
  const url = "https://docs.sheetjs.com/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"));

  /* sort by first presidential term */
  prez.forEach(row => row.start = row.terms.find(term => term.type === "prez").start);
  prez.sort((l,r) => l.start.localeCompare(r.start));

  /* 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>

After saving the file, run a local web server in the folder with the HTML file. For example, if NodeJS is installed:

npx http-server .

The server process will display a URL (typically http://127.0.0.1:8080). Open http://127.0.0.1:8080/SheetJSStandaloneDemo.html in your browser.

Footnotes​

  1. https://theunitedstates.io/congress-legislators/executive.json is the original location of the example dataset. The contributors to the dataset dedicated the content to the public domain. ↩

  2. See "The Executive Branch" in the dataset documentation. ↩

  3. See json_to_sheet in "Utilities" ↩

  4. See book_new in "Utilities" ↩

  5. See book_append_sheet in "Utilities" ↩

  6. See sheet_add_aoa in "Utilities" ↩

  7. See "Column Properties" ↩

  8. See writeFile in "Writing Files" ↩