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.
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.
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 });
Live Demo
This demo runs in the web browser! Click "Click to Generate File!" and the browser should generate a XLSX file.
Run the Demo Locally
- Web Browser
- Command-Line (NodeJS)
- Desktop App
- Mobile App
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>
Install the dependencies:
npm i --save https://cdn.sheetjs.com/xlsx-latest/xlsx-latest.tgz
Save the following script to snippet.js
and run node snippet.js
:
const XLSX = require("xlsx");
(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 });
})();
Native fetch
support was added in NodeJS 18. For older versions of NodeJS,
the script will throw an error fetch is not defined
. A third-party library
like axios
presents a similar API for fetching data:
Example using axios (click to show)
Install the dependencies:
npm i --save https://cdn.sheetjs.com/xlsx-latest/xlsx-latest.tgz axios
The differences in the script are highlighted below.
const XLSX = require("xlsx");
const axios = require("axios");
(async() => {
/* fetch JSON data and parse */
const url = "https://sheetjs.com/data/executive.json";
const raw_data = (await axios(url, {responseType: "json"})).data;
/* 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 });
})();
Other Server-Side Platforms (click to show)
- Deno
- Bun
Save the following script to snippet.ts
and run with
deno run --allow-net --allow-write snippet.ts
:
// @deno-types="https://cdn.sheetjs.com/xlsx-latest/package/types/index.d.ts"
import * as XLSX from 'https://cdn.sheetjs.com/xlsx-latest/package/xlsx.mjs';
/* 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: any) => row.terms.some((term: any) => term.type === "prez"));
/* flatten objects */
const rows = prez.map((row: any) => ({
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: number, r: any) => 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 });
Download https://cdn.sheetjs.com/xlsx-latest/package/xlsx.mjs to xlsx.mjs
Save the following script to snippet.js
and run with bun snippet.js
:
import * as XLSX from './xlsx.mjs';
import * as fs from 'fs';
XLSX.set_fs(fs);
/* 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 });
Save the following script to snippet.html
:
<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>
Save the following to package.json
:
{
"name": "sheetjs-nwjs",
"author": "sheetjs",
"version": "0.0.0",
"main": "snippet.html",
"dependencies": {
"nw": "~0.66.0",
"xlsx": "https://cdn.sheetjs.com/xlsx-latest/xlsx-latest.tgz"
}
}
Install dependencies and build the app:
npm i
npx -p nw-builder nwbuild --mode=build .
Run the generated app in the build\sheetjs-nwjs
folder. It will show a save
dialog box. After selecting a path, the app will write the file.
Follow the Environment Setup of the React Native documentation before testing the demo.
Create a new project by running the following commands in the Terminal:
npx react-native init SheetJSPres --version="0.70.6"
cd SheetJSPres
npm i -S https://cdn.sheetjs.com/xlsx-latest/xlsx-latest.tgz [email protected]
Save the following to App.js
in the project:
import React from 'react';
import { Alert, Button, SafeAreaView, Text, View } from 'react-native';
import { utils, version, write } from 'xlsx';
import RNFetchBlob from 'react-native-blob-util';
const make_workbook = 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 = utils.json_to_sheet(rows);
const workbook = utils.book_new();
utils.book_append_sheet(workbook, worksheet, "Dates");
/* fix headers */
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 } ];
/* React Native does not support `writeFile`. This is a low-level write ! */
/* write workbook to buffer */
const buf = write(workbook, {type:'buffer', bookType:"xlsx"});
/* write buffer to file */
const filename = RNFetchBlob.fs.dirs.DocumentDir + "/Presidents.xlsx";
await RNFetchBlob.fs.writeFile(filename, Array.from(buf), 'ascii');
return filename;
};
const App = () => ( <SafeAreaView><View style={{ marginTop: 32, padding: 24 }}>
<Text style={{ fontSize: 24, fontWeight: 'bold' }}>SheetJS {version} Export Demo</Text>
<Button title='Press to Export' onPress={async() => {
try {
const filename = await make_workbook();
Alert.alert("Export Finished", `Exported to ${filename}`);
} catch(err) {
Alert.alert("Export Error", `Error ${err.message||err}`);
}
}}/>
</View></SafeAreaView> );
export default App;
- Android
- iOS
The Android demo has been tested in Windows 10 and in macOS.
Test the app in the Android simulator:
npm run android
After clicking "Press to Export", the app will show an alert with the location to the generated file.
In the Android simulator, pulling the file requires additional steps. This command will pull a Base64-encoded string from the simulator:
adb exec-out run-as com.sheetjspres base64 files/Presidents.xlsx > pres.b64
Decoding the file requires an OS-specific command:
- Windows
- macOS
certutil -decode .\pres.b64 .\Presidents.xlsx
base64 -D pres.b64 > Presidents.xlsx
This command generates Presidents.xlsx
which can be opened.
For testing on a real device, Android 10+ requires an additional step to access
the generated file. Add highlighted lines to App.js
:
/* write buffer to file */
const filename = RNFetchBlob.fs.dirs.DocumentDir + "/Presidents.xlsx";
await RNFetchBlob.fs.writeFile(filename, Array.from(buf), 'ascii');
await RNFetchBlob.MediaCollection.copyToMediaStore({
parentFolder: "",
mimeType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
name: "Presidents.xlsx"
}, "Download", filename);
return filename;
Presidents.xlsx
will be copied to the Downloads
folder.
This demo runs in iOS and requires a Macintosh computer with Xcode installed.
The native component must be linked:
cd ios; pod install; cd ..
Test the app in the iOS simulator:
npm run ios
After clicking "Press to Export", the app will show an alert with the location to the generated file.