Gathering detailed insights and metrics for xlsx
Gathering detailed insights and metrics for xlsx
Gathering detailed insights and metrics for xlsx
Gathering detailed insights and metrics for xlsx
node-xlsx
NodeJS Excel files parser & builder
xlsx-style
Excel (XLSB/XLSX/XLSM/XLS/XML) and ODS spreadsheet parser and writer (extended to enable read/write of cell formats with xlsx files)
vxe-table-plugin-export-xlsx
基于 vxe-table 表格的扩展插件,支持导出 xlsx 格式
xlsx-populate
Excel XLSX parser/generator written in JavaScript with Node.js and browser support, jQuery/d3-style method chaining, and a focus on keeping existing workbook features and styles in tact.
📗 SheetJS Spreadsheet Data Toolkit -- New home https://git.sheetjs.com/SheetJS/sheetjs
npm install xlsx
Typescript
Module System
Min. Node Version
Node Version
NPM Version
96.7
Supply Chain
99
Quality
76.1
Maintenance
76
Vulnerability
100
License
Total Downloads
413,315,413
Last Day
471,396
Last Week
2,473,413
Last Month
10,746,021
Last Year
110,663,264
35,251 Stars
770 Commits
7,993 Forks
614 Watching
3 Branches
179 Contributors
Latest Version
0.18.5
Package Id
xlsx@0.18.5
Unpacked Size
7.15 MB
Size
2.33 MB
File Count
26
NPM Version
8.5.0
Node Version
16.14.2
Cumulative downloads
Total Downloads
Last day
-1.9%
471,396
Compared to previous day
Last week
4.1%
2,473,413
Compared to previous week
Last month
10.6%
10,746,021
Compared to previous month
Last year
7.5%
110,663,264
Compared to previous year
The SheetJS Community Edition offers battle-tested open-source solutions for extracting useful data from almost any complex spreadsheet and generating new spreadsheets that will work with legacy and modern software alike.
SheetJS Pro offers solutions beyond data processing: Edit complex templates with ease; let out your inner Picasso with styling; make custom sheets with images/graphs/PivotTables; evaluate formula expressions and port calculations to web apps; automate common spreadsheet tasks, and much more!
Browser Test and Support Matrix
Supported File Formats
Standalone Browser Scripts
The complete browser standalone build is saved to dist/xlsx.full.min.js
and
can be directly added to a page with a script
tag:
1<script lang="javascript" src="dist/xlsx.full.min.js"></script>
CDN | URL |
---|---|
unpkg | https://unpkg.com/xlsx/ |
jsDelivr | https://jsdelivr.com/package/npm/xlsx |
CDNjs | https://cdnjs.com/libraries/xlsx |
For example, unpkg
makes the latest version available at:
1<script src="https://unpkg.com/xlsx/dist/xlsx.full.min.js"></script>
The complete single-file version is generated at dist/xlsx.full.min.js
dist/xlsx.core.min.js
omits codepage library (no support for XLS encodings)
A slimmer build is generated at dist/xlsx.mini.min.js
. Compared to full build:
With bower:
1$ bower install js-xlsx
ECMAScript Modules
The ECMAScript Module build is saved to xlsx.mjs
and can be directly added to
a page with a script
tag using type=module
:
1<script type="module"> 2import { read, writeFileXLSX } from "./xlsx.mjs"; 3 4/* load the codepage support library for extended support with older formats */ 5import { set_cptable } from "./xlsx.mjs"; 6import * as cptable from './dist/cpexcel.full.mjs'; 7set_cptable(cptable); 8</script>
The npm package also exposes the module
with the module
parameter, supported in Angular and other projects:
1import { read, writeFileXLSX } from "xlsx"; 2 3/* load the codepage support library for extended support with older formats */ 4import { set_cptable } from "xlsx"; 5import * as cptable from 'xlsx/dist/cpexcel.full.mjs'; 6set_cptable(cptable);
Deno
xlsx.mjs
can be imported in Deno. It is available from unpkg
:
1// @deno-types="https://unpkg.com/xlsx/types/index.d.ts" 2import * as XLSX from 'https://unpkg.com/xlsx/xlsx.mjs'; 3 4/* load the codepage support library for extended support with older formats */ 5import * as cptable from 'https://unpkg.com/xlsx/dist/cpexcel.full.mjs'; 6XLSX.set_cptable(cptable);
NodeJS
With npm:
1$ npm install xlsx
By default, the module supports require
:
1var XLSX = require("xlsx");
The module also ships with xlsx.mjs
for use with import
:
1import * as XLSX from 'xlsx/xlsx.mjs'; 2 3/* load 'fs' for readFile and writeFile support */ 4import * as fs from 'fs'; 5XLSX.set_fs(fs); 6 7/* load 'stream' for stream support */ 8import { Readable } from 'stream'; 9XLSX.stream.set_readable(Readable); 10 11/* load the codepage support library for extended support with older formats */ 12import * as cpexcel from 'xlsx/dist/cpexcel.full.mjs'; 13XLSX.set_cptable(cpexcel);
Photoshop and InDesign
dist/xlsx.extendscript.js
is an ExtendScript build for Photoshop and InDesign
that is included in the npm
package. It can be directly referenced with a
#include
directive:
1#include "xlsx.extendscript.js"
For broad compatibility with JavaScript engines, the library is written using
ECMAScript 3 language dialect as well as some ES5 features like Array#forEach
.
Older browsers require shims to provide missing functions.
To use the shim, add the shim before the script tag that loads xlsx.js
:
1<!-- add the shim first --> 2<script type="text/javascript" src="shim.min.js"></script> 3<!-- after the shim is referenced, add the library --> 4<script type="text/javascript" src="xlsx.full.min.js"></script>
The script also includes IE_LoadFile
and IE_SaveFile
for loading and saving
files in Internet Explorer versions 6-9. The xlsx.extendscript.js
script
bundles the shim in a format suitable for Photoshop and other Adobe products.
Most scenarios involving spreadsheets and data can be broken into 5 parts:
Acquire Data: Data may be stored anywhere: local or remote files, databases, HTML TABLE, or even generated programmatically in the web browser.
Extract Data: For spreadsheet files, this involves parsing raw bytes to read the cell data. For general JS data, this involves reshaping the data.
Process Data: From generating summary statistics to cleaning data records, this step is the heart of the problem.
Package Data: This can involve making a new spreadsheet or serializing
with JSON.stringify
or writing XML or simply flattening data for UI tools.
Release Data: Spreadsheet files can be uploaded to a server or written locally. Data can be presented to users in an HTML TABLE or data grid.
A common problem involves generating a valid spreadsheet export from data stored
in an HTML table. In this example, an HTML TABLE on the page will be scraped,
a row will be added to the bottom with the date of the report, and a new file
will be generated and downloaded locally. XLSX.writeFile
takes care of
packaging the data and attempting a local download:
1// Acquire Data (reference to the HTML table)
2var table_elt = document.getElementById("my-table-id");
3
4// Extract Data (create a workbook object from the table)
5var workbook = XLSX.utils.table_to_book(table_elt);
6
7// Process Data (add a new row)
8var ws = workbook.Sheets["Sheet1"];
9XLSX.utils.sheet_add_aoa(ws, [["Created "+new Date().toISOString()]], {origin:-1});
10
11// Package and Release Data (`writeFile` tries to write and save an XLSB file)
12XLSX.writeFile(workbook, "Report.xlsb");
This library tries to simplify steps 2 and 4 with functions to extract useful
data from spreadsheet files (read
/ readFile
) and generate new spreadsheet
files from data (write
/ writeFile
). Additional utility functions like
table_to_book
work with other common data sources like HTML tables.
This documentation and various demo projects cover a number of common scenarios and approaches for steps 1 and 5.
Utility functions help with step 3.
"Acquiring and Extracting Data" describes solutions for common data import scenarios.
"Packaging and Releasing Data" describes solutions for common data export scenarios.
"Processing Data" describes solutions for common workbook processing and manipulation scenarios.
"Utility Functions" details utility functions for translating JSON Arrays and other common JS structures into worksheet objects.
Data processing should fit in any workflow
The library does not impose a separate lifecycle. It fits nicely in websites and apps built using any framework. The plain JS data objects play nice with Web Workers and future APIs.
JavaScript is a powerful language for data processing
The "Common Spreadsheet Format" is a simple object representation of the core concepts of a workbook. The various functions in the library provide low-level tools for working with the object.
For friendly JS processing, there are utility functions for converting parts of a worksheet to/from an Array of Arrays. The following example combines powerful JS Array methods with a network request library to download data, select the information we want and create a workbook file:
The goal is to generate a XLSB workbook of US President names and birthdays.
Acquire Data
Raw Data
https://theunitedstates.io/congress-legislators/executive.json has the desired data. For example, John Adams:
1{ 2 "id": { /* (data omitted) */ }, 3 "name": { 4 "first": "John", // <-- first name 5 "last": "Adams" // <-- last name 6 }, 7 "bio": { 8 "birthday": "1735-10-19", // <-- birthday 9 "gender": "M" 10 }, 11 "terms": [ 12 { "type": "viceprez", /* (other fields omitted) */ }, 13 { "type": "viceprez", /* (other fields omitted) */ }, 14 { "type": "prez", /* (other fields omitted) */ } // <-- look for "prez" 15 ] 16}
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:
1const prez = raw_data.filter(row => row.terms.some(term => term.type === "prez"));
Lining up the data
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 the subfield
row.bio.birthday
. Using Array#map
, the dataset can be massaged in one call:
1const rows = prez.map(row => ({ 2 name: row.name.first + " " + row.name.last, 3 birthday: row.bio.birthday 4}));
The result is an array of "simple" objects with no nesting:
1[ 2 { name: "George Washington", birthday: "1732-02-22" }, 3 { name: "John Adams", birthday: "1735-10-19" }, 4 // ... one row per President 5]
Extract Data
With the cleaned dataset, XLSX.utils.json_to_sheet
generates a worksheet:
1const 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":
1const workbook = XLSX.utils.book_new();
2XLSX.utils.book_append_sheet(workbook, worksheet, "Dates");
Process Data
Fixing headers
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:
1XLSX.utils.sheet_add_aoa(worksheet, [["Name", "Birthday"]], { origin: "A1" });
Fixing Column Widths
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:
1worksheet["!cols"] = [ { wch: 10 } ]; // set column A width to 10 characters
One Array#reduce
call over rows
can calculate the maximum width:
1const max_width = rows.reduce((w, r) => Math.max(w, r.name.length), 10); 2worksheet["!cols"] = [ { wch: max_width } ];
Note: If the starting point was a file or HTML table, XLSX.utils.sheet_to_json
will generate an array of JS objects.
Package and Release Data
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.
1XLSX.writeFile(workbook, "Presidents.xlsx");
Complete Example
1// Uncomment the next line for use in NodeJS: 2// const XLSX = require("xlsx"), axios = require("axios"); 3 4(async() => { 5 /* fetch JSON data and parse */ 6 const url = "https://theunitedstates.io/congress-legislators/executive.json"; 7 const raw_data = (await axios(url, {responseType: "json"})).data; 8 9 /* filter for the Presidents */ 10 const prez = raw_data.filter(row => row.terms.some(term => term.type === "prez")); 11 12 /* flatten objects */ 13 const rows = prez.map(row => ({ 14 name: row.name.first + " " + row.name.last, 15 birthday: row.bio.birthday 16 })); 17 18 /* generate worksheet and workbook */ 19 const worksheet = XLSX.utils.json_to_sheet(rows); 20 const workbook = XLSX.utils.book_new(); 21 XLSX.utils.book_append_sheet(workbook, worksheet, "Dates"); 22 23 /* fix headers */ 24 XLSX.utils.sheet_add_aoa(worksheet, [["Name", "Birthday"]], { origin: "A1" }); 25 26 /* calculate column width */ 27 const max_width = rows.reduce((w, r) => Math.max(w, r.name.length), 10); 28 worksheet["!cols"] = [ { wch: max_width } ]; 29 30 /* create an XLSX file and try to save to Presidents.xlsx */ 31 XLSX.writeFile(workbook, "Presidents.xlsx"); 32})();
For use in the web browser, assuming the snippet is saved to snippet.js
,
script tags should be used to include the axios
and xlsx
standalone builds:
1<script src="https://unpkg.com/xlsx/dist/xlsx.full.min.js"></script> 2<script src="https://unpkg.com/axios/dist/axios.min.js"></script> 3<script src="snippet.js"></script>
File formats are implementation details
The parser covers a wide gamut of common spreadsheet file formats to ensure that "HTML-saved-as-XLS" files work as well as actual XLS or XLSX files.
The writer supports a number of common output formats for broad compatibility with the data ecosystem.
To the greatest extent possible, data processing code should not have to worry about the specific file formats involved.
The demos
directory includes sample projects for:
Frameworks and APIs
angularjs
angular and ionic
knockout
meteor
react and react-native
vue 2.x and weex
XMLHttpRequest and fetch
nodejs server
databases and key/value stores
typed arrays and math
Bundlers and Tooling
Platforms and Integrations
deno
electron application
nw.js application
Chrome / Chromium extensions
Download a Google Sheet locally
Adobe ExtendScript
Headless Browsers
canvas-datagrid
x-spreadsheet
react-data-grid
vue3-table-light
Swift JSC and other engines
"serverless" functions
internet explorer
Other examples are included in the showcase.
https://sheetjs.com/demos/modify.html shows a complete example of reading, modifying, and writing files.
https://github.com/SheetJS/sheetjs/blob/HEAD/bin/xlsx.njs is the command-line tool included with node installations, reading spreadsheet files and exporting the contents in various formats.
API
Extract data from spreadsheet bytes
1var workbook = XLSX.read(data, opts);
The read
method can extract data from spreadsheet bytes stored in a JS string,
"binary string", NodeJS buffer or typed array (Uint8Array
or ArrayBuffer
).
Read spreadsheet bytes from a local file and extract data
1var workbook = XLSX.readFile(filename, opts);
The readFile
method attempts to read a spreadsheet file at the supplied path.
Browsers generally do not allow reading files in this way (it is deemed a
security risk), and attempts to read files in this way will throw an error.
The second opts
argument is optional. "Parsing Options"
covers the supported properties and behaviors.
Examples
Here are a few common scenarios (click on each subtitle to see the code):
readFile
uses fs.readFileSync
under the hood:
1var XLSX = require("xlsx"); 2 3var workbook = XLSX.readFile("test.xlsx");
For Node ESM, the readFile
helper is not enabled. Instead, fs.readFileSync
should be used to read the file data as a Buffer
for use with XLSX.read
:
1import { readFileSync } from "fs"; 2import { read } from "xlsx/xlsx.mjs"; 3 4const buf = readFileSync("test.xlsx"); 5/* buf is a Buffer */ 6const workbook = read(buf);
readFile
uses Deno.readFileSync
under the hood:
1// @deno-types="https://deno.land/x/sheetjs/types/index.d.ts" 2import * as XLSX from 'https://deno.land/x/sheetjs/xlsx.mjs' 3 4const workbook = XLSX.readFile("test.xlsx");
Applications reading files must be invoked with the --allow-read
flag. The
deno
demo has more examples
For modern websites targeting Chrome 76+, File#arrayBuffer
is recommended:
1// XLSX is a global from the standalone script
2
3async function handleDropAsync(e) {
4 e.stopPropagation(); e.preventDefault();
5 const f = e.dataTransfer.files[0];
6 /* f is a File */
7 const data = await f.arrayBuffer();
8 /* data is an ArrayBuffer */
9 const workbook = XLSX.read(data);
10
11 /* DO SOMETHING WITH workbook HERE */
12}
13drop_dom_element.addEventListener("drop", handleDropAsync, false);
For maximal compatibility, the FileReader
API should be used:
1function handleDrop(e) {
2 e.stopPropagation(); e.preventDefault();
3 var f = e.dataTransfer.files[0];
4 /* f is a File */
5 var reader = new FileReader();
6 reader.onload = function(e) {
7 var data = e.target.result;
8 /* reader.readAsArrayBuffer(file) -> data will be an ArrayBuffer */
9 var workbook = XLSX.read(data);
10
11 /* DO SOMETHING WITH workbook HERE */
12 };
13 reader.readAsArrayBuffer(f);
14}
15drop_dom_element.addEventListener("drop", handleDrop, false);
https://oss.sheetjs.com/sheetjs/ demonstrates the FileReader technique.
Starting with an HTML INPUT element with type="file"
:
1<input type="file" id="input_dom_element">
For modern websites targeting Chrome 76+, Blob#arrayBuffer
is recommended:
1// XLSX is a global from the standalone script
2
3async function handleFileAsync(e) {
4 const file = e.target.files[0];
5 const data = await file.arrayBuffer();
6 /* data is an ArrayBuffer */
7 const workbook = XLSX.read(data);
8
9 /* DO SOMETHING WITH workbook HERE */
10}
11input_dom_element.addEventListener("change", handleFileAsync, false);
For broader support (including IE10+), the FileReader
approach is recommended:
1function handleFile(e) {
2 var file = e.target.files[0];
3 var reader = new FileReader();
4 reader.onload = function(e) {
5 var data = e.target.result;
6 /* reader.readAsArrayBuffer(file) -> data will be an ArrayBuffer */
7 var workbook = XLSX.read(e.target.result);
8
9 /* DO SOMETHING WITH workbook HERE */
10 };
11 reader.readAsArrayBuffer(file);
12}
13input_dom_element.addEventListener("change", handleFile, false);
The oldie
demo shows an IE-compatible fallback scenario.
For modern websites targeting Chrome 42+, fetch
is recommended:
1// XLSX is a global from the standalone script 2 3(async() => { 4 const url = "http://oss.sheetjs.com/test_files/formula_stress_test.xlsx"; 5 const data = await (await fetch(url)).arrayBuffer(); 6 /* data is an ArrayBuffer */ 7 const workbook = XLSX.read(data); 8 9 /* DO SOMETHING WITH workbook HERE */ 10})();
For broader support, the XMLHttpRequest
approach is recommended:
1var url = "http://oss.sheetjs.com/test_files/formula_stress_test.xlsx"; 2 3/* set up async GET request */ 4var req = new XMLHttpRequest(); 5req.open("GET", url, true); 6req.responseType = "arraybuffer"; 7 8req.onload = function(e) { 9 var workbook = XLSX.read(req.response); 10 11 /* DO SOMETHING WITH workbook HERE */ 12}; 13 14req.send();
The xhr
demo includes a longer discussion and more examples.
http://oss.sheetjs.com/sheetjs/ajax.html shows fallback approaches for IE6+.
readFile
wraps the File
logic in Photoshop and other ExtendScript targets.
The specified path should be an absolute path:
1#include "xlsx.extendscript.js"
2
3/* Read test.xlsx from the Documents folder */
4var workbook = XLSX.readFile(Folder.myDocuments + "/test.xlsx");
The extendscript
demo includes a more complex example.
readFile
can be used in the renderer process:
1/* From the renderer process */ 2var XLSX = require("xlsx"); 3 4var workbook = XLSX.readFile(path);
Electron APIs have changed over time. The electron
demo
shows a complete example and details the required version-specific settings.
The react
demo includes a sample React Native app.
Since React Native does not provide a way to read files from the filesystem, a third-party library must be used. The following libraries have been tested:
The base64
encoding returns strings compatible with the base64
type:
1import XLSX from "xlsx"; 2import { FileSystem } from "react-native-file-access"; 3 4const b64 = await FileSystem.readFile(path, "base64"); 5/* b64 is a base64 string */ 6const workbook = XLSX.read(b64, {type: "base64"});
The ascii
encoding returns binary strings compatible with the binary
type:
1import XLSX from "xlsx"; 2import { readFile } from "react-native-fs"; 3 4const bstr = await readFile(path, "ascii"); 5/* bstr is a binary string */ 6const workbook = XLSX.read(bstr, {type: "binary"});
read
can accept a NodeJS buffer. readFile
can read files generated by a
HTTP POST request body parser like formidable
:
1const XLSX = require("xlsx"); 2const http = require("http"); 3const formidable = require("formidable"); 4 5const server = http.createServer((req, res) => { 6 const form = new formidable.IncomingForm(); 7 form.parse(req, (err, fields, files) => { 8 /* grab the first file */ 9 const f = Object.entries(files)[0][1]; 10 const path = f.filepath; 11 const workbook = XLSX.readFile(path); 12 13 /* DO SOMETHING WITH workbook HERE */ 14 }); 15}).listen(process.env.PORT || 7262);
The server
demo has more advanced examples.
Node 17.5 and 18.0 have native support for fetch:
1const XLSX = require("xlsx"); 2 3const data = await (await fetch(url)).arrayBuffer(); 4/* data is an ArrayBuffer */ 5const workbook = XLSX.read(data);
For broader compatibility, third-party modules are recommended.
request
requires a null
encoding to yield Buffers:
1var XLSX = require("xlsx"); 2var request = require("request"); 3 4request({url: url, encoding: null}, function(err, resp, body) { 5 var workbook = XLSX.read(body); 6 7 /* DO SOMETHING WITH workbook HERE */ 8});
axios
works the same way in browser and in NodeJS:
1const XLSX = require("xlsx"); 2const axios = require("axios"); 3 4(async() => { 5 const res = await axios.get(url, {responseType: "arraybuffer"}); 6 /* res.data is a Buffer */ 7 const workbook = XLSX.read(res.data); 8 9 /* DO SOMETHING WITH workbook HERE */ 10})();
The net
module in the main process can make HTTP/HTTPS requests to external
resources. Responses should be manually concatenated using Buffer.concat
:
1const XLSX = require("xlsx"); 2const { net } = require("electron"); 3 4const req = net.request(url); 5req.on("response", (res) => { 6 const bufs = []; // this array will collect all of the buffers 7 res.on("data", (chunk) => { bufs.push(chunk); }); 8 res.on("end", () => { 9 const workbook = XLSX.read(Buffer.concat(bufs)); 10 11 /* DO SOMETHING WITH workbook HERE */ 12 }); 13}); 14req.end();
When dealing with Readable Streams, the easiest approach is to buffer the stream and process the whole thing at the end:
1var fs = require("fs"); 2var XLSX = require("xlsx"); 3 4function process_RS(stream, cb) { 5 var buffers = []; 6 stream.on("data", function(data) { buffers.push(data); }); 7 stream.on("end", function() { 8 var buffer = Buffer.concat(buffers); 9 var workbook = XLSX.read(buffer, {type:"buffer"}); 10 11 /* DO SOMETHING WITH workbook IN THE CALLBACK */ 12 cb(workbook); 13 }); 14}
When dealing with ReadableStream
, the easiest approach is to buffer the stream
and process the whole thing at the end:
1// XLSX is a global from the standalone script 2 3async function process_RS(stream) { 4 /* collect data */ 5 const buffers = []; 6 const reader = stream.getReader(); 7 for(;;) { 8 const res = await reader.read(); 9 if(res.value) buffers.push(res.value); 10 if(res.done) break; 11 } 12 13 /* concat */ 14 const out = new Uint8Array(buffers.reduce((acc, v) => acc + v.length, 0)); 15 16 let off = 0; 17 for(const u8 of arr) { 18 out.set(u8, off); 19 off += u8.length; 20 } 21 22 return out; 23} 24 25const data = await process_RS(stream); 26/* data is Uint8Array */ 27const workbook = XLSX.read(data);
More detailed examples are covered in the included demos
JSON and JS data tend to represent single worksheets. This section will use a few utility functions to generate workbooks.
Create a new Workbook
1var workbook = XLSX.utils.book_new();
The book_new
utility function creates an empty workbook with no worksheets.
Spreadsheet software generally require at least one worksheet and enforce the requirement in the user interface. This library enforces the requirement at write time, throwing errors if an empty workbook is passed to write functions.
API
Create a worksheet from an array of arrays of JS values
1var worksheet = XLSX.utils.aoa_to_sheet(aoa, opts);
The aoa_to_sheet
utility function walks an "array of arrays" in row-major
order, generating a worksheet object. The following snippet generates a sheet
with cell A1
set to the string A1
, cell B1
set to B1
, etc:
1var worksheet = XLSX.utils.aoa_to_sheet([ 2 ["A1", "B1", "C1"], 3 ["A2", "B2", "C2"], 4 ["A3", "B3", "C3"] 5]);
"Array of Arrays Input" describes the function and the
optional opts
argument in more detail.
Create a worksheet from an array of JS objects
1var worksheet = XLSX.utils.json_to_sheet(jsa, opts);
The json_to_sheet
utility function walks an array of JS objects in order,
generating a worksheet object. By default, it will generate a header row and
one row per object in the array. The optional opts
argument has settings to
control the column order and header output.
"Array of Objects Input" describes the function and
the optional opts
argument in more detail.
Examples
"Zen of SheetJS" contains a detailed example "Get Data from a JSON Endpoint and Generate a Workbook"
x-spreadsheet
is an interactive
data grid for previewing and modifying structured data in the web browser. The
xspreadsheet
demo includes a sample script with the
xtos
function for converting from x-spreadsheet data object to a workbook.
https://oss.sheetjs.com/sheetjs/x-spreadsheet is a live demo.
The database
demo includes examples of working with
databases and query results.
@tensorflow/tfjs
and other libraries expect data in simple
arrays, well-suited for worksheets where each column is a data vector. That is
the transpose of how most people use spreadsheets, where each row is a vector.
When recovering data from tfjs
, the returned data points are stored in a typed
array. An array of arrays can be constructed with loops. Array#unshift
can
prepend a title row before the conversion:
1const XLSX = require("xlsx"); 2const tf = require('@tensorflow/tfjs'); 3 4/* suppose xs and ys are vectors (1D tensors) -> tfarr will be a typed array */ 5const tfdata = tf.stack([xs, ys]).transpose(); 6const shape = tfdata.shape; 7const tfarr = tfdata.dataSync(); 8 9/* construct the array of arrays */ 10const aoa = []; 11for(let j = 0; j < shape[0]; ++j) { 12 aoa[j] = []; 13 for(let i = 0; i < shape[1]; ++i) aoa[j][i] = tfarr[j * shape[1] + i]; 14} 15/* add headers to the top */ 16aoa.unshift(["x", "y"]); 17 18/* generate worksheet */ 19const worksheet = XLSX.utils.aoa_to_sheet(aoa);
The array
demo shows a complete example.
API
Create a worksheet by scraping an HTML TABLE in the page
1var worksheet = XLSX.utils.table_to_sheet(dom_element, opts);
The table_to_sheet
utility function takes a DOM TABLE element and iterates
through the rows to generate a worksheet. The opts
argument is optional.
"HTML Table Input" describes the function in more detail.
Create a workbook by scraping an HTML TABLE in the page
1var workbook = XLSX.utils.table_to_book(dom_element, opts);
The table_to_book
utility function follows the same logic as table_to_sheet
.
After generating a worksheet, it creates a blank workbook and appends the
spreadsheet.
The options argument supports the same options as table_to_sheet
, with the
addition of a sheet
property to control the worksheet name. If the property
is missing or no options are specified, the default name Sheet1
is used.
Examples
Here are a few common scenarios (click on each subtitle to see the code):
1<!-- include the standalone script and shim. this uses the UNPKG CDN --> 2<script src="https://unpkg.com/xlsx/dist/shim.min.js"></script> 3<script src="https://unpkg.com/xlsx/dist/xlsx.full.min.js"></script> 4 5<!-- example table with id attribute --> 6<table id="tableau"> 7 <tr><td>Sheet</td><td>JS</td></tr> 8 <tr><td>12345</td><td>67</td></tr> 9</table> 10 11<!-- this block should appear after the table HTML and the standalone script --> 12<script type="text/javascript"> 13 var workbook = XLSX.utils.table_to_book(document.getElementById("tableau")); 14 15 /* DO SOMETHING WITH workbook HERE */ 16</script>
Multiple tables on a web page can be converted to individual worksheets:
1/* create new workbook */
2var workbook = XLSX.utils.book_new();
3
4/* convert table "table1" to worksheet named "Sheet1" */
5var sheet1 = XLSX.utils.table_to_sheet(document.getElementById("table1"));
6XLSX.utils.book_append_sheet(workbook, sheet1, "Sheet1");
7
8/* convert table "table2" to worksheet named "Sheet2" */
9var sheet2 = XLSX.utils.table_to_sheet(document.getElementById("table2"));
10XLSX.utils.book_append_sheet(workbook, sheet2, "Sheet2");
11
12/* workbook now has 2 worksheets */
Alternatively, the HTML code can be extracted and parsed:
1var htmlstr = document.getElementById("tableau").outerHTML; 2var workbook = XLSX.read(htmlstr, {type:"string"});
The chrome
demo shows a complete example and details the
required permissions and other settings.
In an extension, it is recommended to generate the workbook in a content script and pass the object back to the extension:
1/* in the worker script */
2chrome.runtime.onMessage.addListener(function(msg, sender, cb) {
3 /* pass a message like { sheetjs: true } from the extension to scrape */
4 if(!msg || !msg.sheetjs) return;
5 /* create a new workbook */
6 var workbook = XLSX.utils.book_new();
7 /* loop through each table element */
8 var tables = document.getElementsByTagName("table")
9 for(var i = 0; i < tables.length; ++i) {
10 var worksheet = XLSX.utils.table_to_sheet(tables[i]);
11 XLSX.utils.book_append_sheet(workbook, worksheet, "Table" + i);
12 }
13 /* pass back to the extension */
14 return cb(workbook);
15});
The headless
demo includes a complete demo to convert HTML
files to XLSB workbooks. The core idea is to add the script to the page, parse
the table in the page context, generate a base64
workbook and send it back
for further processing:
1const XLSX = require("xlsx");
2const { readFileSync } = require("fs"), puppeteer = require("puppeteer");
3
4const url = `https://sheetjs.com/demos/table`;
5
6/* get the standalone build source (node_modules/xlsx/dist/xlsx.full.min.js) */
7const lib = readFileSync(require.resolve("xlsx/dist/xlsx.full.min.js"), "utf8");
8
9(async() => {
10 /* start browser and go to web page */
11 const browser = await puppeteer.launch();
12 const page = await browser.newPage();
13 await page.goto(url, {waitUntil: "networkidle2"});
14
15 /* inject library */
16 await page.addScriptTag({content: lib});
17
18 /* this function `s5s` will be called by the script below, receiving the Base64-encoded file */
19 await page.exposeFunction("s5s", async(b64) => {
20 const workbook = XLSX.read(b64, {type: "base64" });
21
22 /* DO SOMETHING WITH workbook HERE */
23 });
24
25 /* generate XLSB file in webpage context and send back result */
26 await page.addScriptTag({content: `
27 /* call table_to_book on first table */
28 var workbook = XLSX.utils.table_to_book(document.querySelector("TABLE"));
29
30 /* generate XLSX file */
31 var b64 = XLSX.write(workbook, {type: "base64", bookType: "xlsb"});
32
33 /* call "s5s" hook exposed from the node process */
34 window.s5s(b64);
35 `});
36
37 /* cleanup */
38 await browser.close();
39})();
The headless
demo includes a complete demo to convert HTML
files to XLSB workbooks using PhantomJS. The core idea
is to add the script to the page, parse the table in the page context, generate
a binary
workbook and send it back for further processing:
1var XLSX = require('xlsx'); 2var page = require('webpage').create(); 3 4/* this code will be run in the page */ 5var code = [ "function(){", 6 /* call table_to_book on first table */ 7 "var wb = XLSX.utils.table_to_book(document.body.getElementsByTagName('table')[0]);", 8 9 /* generate XLSB file and return binary string */ 10 "return XLSX.write(wb, {type: 'binary', bookType: 'xlsb'});", 11"}" ].join(""); 12 13page.open('https://sheetjs.com/demos/table', function() { 14 /* Load the browser script from the UNPKG CDN */ 15 page.includeJs("https://unpkg.com/xlsx/dist/xlsx.full.min.js", function() { 16 /* The code will return an XLSB file encoded as binary string */ 17 var bin = page.evaluateJavaScript(code); 18 19 var workbook = XLSX.read(bin, {type: "binary"}); 20 /* DO SOMETHING WITH workbook HERE */ 21 22 phantom.exit(); 23 }); 24});
NodeJS does not include a DOM implementation and Puppeteer requires a hefty
Chromium build. jsdom
is a lightweight alternative:
1const XLSX = require("xlsx"); 2const { readFileSync } = require("fs"); 3const { JSDOM } = require("jsdom"); 4 5/* obtain HTML string. This example reads from test.html */ 6const html_str = fs.readFileSync("test.html", "utf8"); 7/* get first TABLE element */ 8const doc = new JSDOM(html_str).window.document.querySelector("table"); 9/* generate workbook */ 10const workbook = XLSX.utils.table_to_book(doc);
The "Common Spreadsheet Format" is a simple object representation of the core concepts of a workbook. The utility functions work with the object representation and are intended to handle common use cases.
API
Append a Worksheet to a Workbook
1XLSX.utils.book_append_sheet(workbook, worksheet, sheet_name);
The book_append_sheet
utility function appends a worksheet to the workbook.
The third argument specifies the desired worksheet name. Multiple worksheets can
be added to a workbook by calling the function multiple times. If the worksheet
name is already used in the workbook, it will throw an error.
Append a Worksheet to a Workbook and find a unique name
1var new_name = XLSX.utils.book_append_sheet(workbook, worksheet, name, true);
If the fourth argument is true
, the function will start with the specified
worksheet name. If the sheet name exists in the workbook, a new worksheet name
will be chosen by finding the name stem and incrementing the counter:
1XLSX.utils.book_append_sheet(workbook, sheetA, "Sheet2", true); // Sheet2
2XLSX.utils.book_append_sheet(workbook, sheetB, "Sheet2", true); // Sheet3
3XLSX.utils.book_append_sheet(workbook, sheetC, "Sheet2", true); // Sheet4
4XLSX.utils.book_append_sheet(workbook, sheetD, "Sheet2", true); // Sheet5
List the Worksheet names in tab order
1var wsnames = workbook.SheetNames;
The SheetNames
property of the workbook object is a list of the worksheet
names in "tab order". API functions will look at this array.
Replace a Worksheet in place
1workbook.Sheets[sheet_name] = new_worksheet;
The Sheets
property of the workbook object is an object whose keys are names
and whose values are worksheet objects. By reassigning to a property of the
Sheets
object, the worksheet object can be changed without disrupting the
rest of the worksheet structure.
Examples
This example uses XLSX.utils.aoa_to_sheet
.
1var ws_name = "SheetJS"; 2 3/* Create worksheet */ 4var ws_data = [ 5 [ "S", "h", "e", "e", "t", "J", "S" ], 6 [ 1 , 2 , 3 , 4 , 5 ] 7]; 8var ws = XLSX.utils.aoa_to_sheet(ws_data); 9 10/* Add the worksheet to the workbook */ 11XLSX.utils.book_append_sheet(wb, ws, ws_name);
API
Modify a single cell value in a worksheet
1XLSX.utils.sheet_add_aoa(worksheet, [[new_value]], { origin: address });
Modify multiple cell values in a worksheet
1XLSX.utils.sheet_add_aoa(worksheet, aoa, opts);
The sheet_add_aoa
utility function modifies cell values in a worksheet. The
first argument is the worksheet object. The second argument is an array of
arrays of values. The origin
key of the third argument controls where cells
will be written. The following snippet sets B3=1
and E5="abc"
:
1XLSX.utils.sheet_add_aoa(worksheet, [ 2 [1], // <-- Write 1 to cell B3 3 , // <-- Do nothing in row 4 4 [/*B5*/, /*C5*/, /*D5*/, "abc"] // <-- Write "abc" to cell E5 5], { origin: "B3" });
"Array of Arrays Input" describes the function and the
optional opts
argument in more detail.
Examples
The special origin value -1
instructs sheet_add_aoa
to start in column A of
the row after the last row in the range, appending the data:
1XLSX.utils.sheet_add_aoa(worksheet, [ 2 ["first row after data", 1], 3 ["second row after data", 2] 4], { origin: -1 });
The "Common Spreadsheet Format" section describes the object structures in greater detail.
API
Generate spreadsheet bytes (file) from data
1var data = XLSX.write(workbook, opts);
The write
method attempts to package data from the workbook into a file in
memory. By default, XLSX files are generated, but that can be controlled with
the bookType
property of the opts
argument. Based on the type
option,
the data can be stored as a "binary string", JS string, Uint8Array
or Buffer.
The second opts
argument is required. "Writing Options"
covers the supported properties and behaviors.
Generate and attempt to save file
1XLSX.writeFile(workbook, filename, opts);
The writeFile
method packages the data and attempts to save the new file. The
export file format is determined by the extension of filename
(SheetJS.xlsx
signals XLSX export, SheetJS.xlsb
signals XLSB export, etc).
The writeFile
method uses platform-specific APIs to initiate the file save. In
NodeJS, fs.readFileSync
can create a file. In the web browser, a download is
attempted using the HTML5 download
attribute, with fallbacks for IE.
Generate and attempt to save an XLSX file
1XLSX.writeFileXLSX(workbook, filename, opts);
The writeFile
method embeds a number of different export functions. This is
great for developer experience but not amenable to tree shaking using the
current developer tools. When only XLSX exports are needed, this method avoids
referencing the other export functions.
The second opts
argument is optional. "Writing Options"
covers the supported properties and behaviors.
Examples
writeFile
uses fs.writeFileSync
in server environments:
1var XLSX = require("xlsx");
2
3/* output format determined by filename */
4XLSX.writeFile(workbook, "out.xlsb");
For Node ESM, the writeFile
helper is not enabled. Instead, fs.writeFileSync
should be used to write the file data to a Buffer
for use with XLSX.write
:
1import { writeFileSync } from "fs"; 2import { write } from "xlsx/xlsx.mjs"; 3 4const buf = write(workbook, {type: "buffer", bookType: "xlsb"}); 5/* buf is a Buffer */ 6const workbook = writeFileSync("out.xlsb", buf);
writeFile
uses Deno.writeFileSync
under the hood:
1// @deno-types="https://deno.land/x/sheetjs/types/index.d.ts"
2import * as XLSX from 'https://deno.land/x/sheetjs/xlsx.mjs'
3
4XLSX.writeFile(workbook, "test.xlsx");
Applications writing files must be invoked with the --allow-write
flag. The
deno
demo has more examples
writeFile
wraps the File
logic in Photoshop and other ExtendScript targets.
The specified path should be an absolute path:
1#include "xlsx.extendscript.js"
2
3/* output format determined by filename */
4XLSX.writeFile(workbook, "out.xlsx");
5/* at this point, out.xlsx is a file that you can distribute */
The extendscript
demo includes a more complex example.
XLSX.writeFile
wraps a few techniques for triggering a file save:
URL
browser API creates an object URL for the file, which the library uses
by creating a link and forcing a click. It is supported in modern browsers.msSaveBlob
is an IE10+ API for triggering a file save.IE_FileSave
uses VBScript and ActiveX to write a file in IE6+ for Windows
XP and Windows 7. The shim must be included in the containing HTML page.There is no standard way to determine if the actual file has been downloaded.
1/* output format determined by filename */
2XLSX.writeFile(workbook, "out.xlsb");
3/* at this point, out.xlsb will have been downloaded */
XLSX.writeFile
techniques work for most modern browsers as well as older IE.
For much older browsers, there are workarounds implemented by wrapper libraries.
FileSaver.js
implements saveAs
.
Note: XLSX.writeFile
will automatically call saveAs
if available.
1/* bookType can be any supported output type */ 2var wopts = { bookType:"xlsx", bookSST:false, type:"array" }; 3 4var wbout = XLSX.write(workbook,wopts); 5 6/* the saveAs call downloads a file on the local machine */ 7saveAs(new Blob([wbout],{type:"application/octet-stream"}), "test.xlsx");
Downloadify
uses a Flash SWF button
to generate local files, suitable for environments where ActiveX is unavailable:
1Downloadify.create(id,{ 2 /* other options are required! read the downloadify docs for more info */ 3 filename: "test.xlsx", 4 data: function() { return XLSX.write(wb, {bookType:"xlsx", type:"base64"}); }, 5 append: false, 6 dataType: "base64" 7});
The oldie
demo shows an IE-compatible fallback scenario.
A complete example using XHR is included in the XHR demo, along with examples for fetch and wrapper libraries. This example assumes the server can handle Base64-encoded files (see the demo for a basic nodejs server):
1/* in this example, send a base64 string to the server */ 2var wopts = { bookType:"xlsx", bookSST:false, type:"base64" }; 3 4var wbout = XLSX.write(workbook,wopts); 5 6var req = new XMLHttpRequest(); 7req.open("POST", "/upload", true); 8var formdata = new FormData(); 9formdata.append("file", "test.xlsx"); // <-- server expects `file` to hold name 10formdata.append("data", wbout); // <-- `data` holds the base64-encoded data 11req.send(formdata);
The headless
demo includes a complete demo to convert HTML
files to XLSB workbooks using PhantomJS. PhantomJS
fs.write
supports writing files from the main process but has a different
interface from the NodeJS fs
module:
1var XLSX = require('xlsx'); 2var fs = require('fs'); 3 4/* generate a binary string */ 5var bin = XLSX.write(workbook, { type:"binary", bookType: "xlsx" }); 6/* write to file */ 7fs.write("test.xlsx", bin, "wb");
Note: The section "Processing HTML Tables" shows how to generate a workbook from HTML tables in a page in "Headless WebKit".
The included demos cover mobile apps and other special deployments.
The streaming write functions are available in the XLSX.stream
object. They
take the same arguments as the normal write functions but return a NodeJS
Readable Stream.
XLSX.stream.to_csv
is the streaming version of XLSX.utils.sheet_to_csv
.XLSX.stream.to_html
is the streaming version of XLSX.utils.sheet_to_html
.XLSX.stream.to_json
is the streaming version of XLSX.utils.sheet_to_json
.1var output_file_name = "out.csv"; 2var stream = XLSX.stream.to_csv(worksheet); 3stream.pipe(fs.createWriteStream(output_file_name));
1/* to_json returns an object-mode stream */
2var stream = XLSX.stream.to_json(worksheet, {raw:true});
3
4/* the following stream converts JS objects to text via JSON.stringify */
5var conv = new Transform({writableObjectMode:true});
6conv._transform = function(obj, e, cb){ cb(null, JSON.stringify(obj) + "\n"); };
7
8stream.pipe(conv); conv.pipe(process.stdout);
The NUMBERS writer requires a fairly large base. The supplementary xlsx.zahl
scripts provide support. xlsx.zahl.js
is designed for standalone and NodeJS
use, while xlsx.zahl.mjs
is suitable for ESM.
Browser
1<meta charset="utf8"> 2<script src="xlsx.full.min.js"></script> 3<script src="xlsx.zahl.js"></script> 4<script> 5var wb = XLSX.utils.book_new(); var ws = XLSX.utils.aoa_to_sheet([ 6 ["SheetJS", "<3","விரிதாள்"], 7 [72,,"Arbeitsblätter"], 8 [,62,"数据"], 9 [true,false,], 10]); XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); 11XLSX.writeFile(wb, "textport.numbers", {numbers: XLSX_ZAHL, compression: true}); 12</script>
Node
1var XLSX = require("./xlsx.flow"); 2var XLSX_ZAHL = require("./dist/xlsx.zahl"); 3var wb = XLSX.utils.book_new(); var ws = XLSX.utils.aoa_to_sheet([ 4 ["SheetJS", "<3","விரிதாள்"], 5 [72,,"Arbeitsblätter"], 6 [,62,"数据"], 7 [true,false,], 8]); XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); 9XLSX.writeFile(wb, "textport.numbers", {numbers: XLSX_ZAHL, compression: true});
Deno
1import * as XLSX from './xlsx.mjs'; 2import XLSX_ZAHL from './dist/xlsx.zahl.mjs'; 3 4var wb = XLSX.utils.book_new(); var ws = XLSX.utils.aoa_to_sheet([ 5 ["SheetJS", "<3","விரிதாள்"], 6 [72,,"Arbeitsblätter"], 7 [,62,"数据"], 8 [true,false,], 9]); XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); 10XLSX.writeFile(wb, "textports.numbers", {numbers: XLSX_ZAHL, compression: true});
https://github.com/sheetjs/sheetaki pipes write streams to nodejs response.
JSON and JS data tend to represent single worksheets. The utility functions in this section work with single worksheets.
The "Common Spreadsheet Format" section describes
the object structure in more detail. workbook.SheetNames
is an ordered list
of the worksheet names. workbook.Sheets
is an object whose keys are sheet
names and whose values are worksheet objects.
The "first worksheet" is stored at workbook.Sheets[workbook.SheetNames[0]]
.
API
Create an array of JS objects from a worksheet
1var jsa = XLSX.utils.sheet_to_json(worksheet, opts);
Create an array of arrays of JS values from a worksheet
1var aoa = XLSX.utils.sheet_to_json(worksheet, {...opts, header: 1});
The sheet_to_json
utility function walks a workbook in row-major order,
generating an array of objects. The second opts
argument controls a number of
export decisions including the type of values (JS values or formatted text). The
"JSON" section describes the argument in more detail.
By default, sheet_to_json
scans the first row and uses the values as headers.
With the header: 1
option, the function exports an array of arrays of values.
Examples
x-spreadsheet
is an interactive
data grid for previewing and modifying structured data in the web browser. The
xspreadsheet
demo includes a sample script with the
stox
function for converting from a workbook to x-spreadsheet data object.
https://oss.sheetjs.com/sheetjs/x-spreadsheet is a live demo.
react-data-grid
is a data grid tailored for
react. It expects two properties: rows
of data objects and columns
which
describe the columns. For the purposes of massaging the data to fit the react
data grid API it is easiest to start from an array of arrays.
This demo starts by fetching a remote file and using XLSX.read
to extract:
1import { useEffect, useState } from "react"; 2import DataGrid from "react-data-grid"; 3import { read, utils } from "xlsx"; 4 5const url = "https://oss.sheetjs.com/test_files/RkNumber.xls"; 6 7export default function App() { 8 const [columns, setColumns] = useState([]); 9 const [rows, setRows] = useState([]); 10 useEffect(() => {(async () => { 11 const wb = read(await (await fetch(url)).arrayBuffer(), { WTF: 1 }); 12 13 /* use sheet_to_json with header: 1 to generate an array of arrays */ 14 const data = utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]], { header: 1 }); 15 16 /* see react-data-grid docs to understand the shape of the expected data */ 17 setColumns(data[0].map((r) => ({ key: r, name: r }))); 18 setRows(data.slice(1).map((r) => r.reduce((acc, x, i) => { 19 acc[data[0][i]] = x; 20 return acc; 21 }, {}))); 22 })(); }); 23 24 return <DataGrid columns={columns} rows={rows} />; 25}
vue3-table-lite
is a simple
VueJS 3 data table. It is featured in the VueJS demo.
The database
demo includes examples of working with
databases and query results.
@tensorflow/tfjs
and other libraries expect data in simple
arrays, well-suited for worksheets where each column is a data vector. That is
the transpose of how most people use spreadsheets, where each row is a vector.
A single Array#map
can pull individual named rows from sheet_to_json
export:
1const XLSX = require("xlsx"); 2const tf = require('@tensorflow/tfjs'); 3 4const key = "age"; // this is the field we want to pull 5const ages = XLSX.utils.sheet_to_json(worksheet).map(r => r[key]); 6const tf_data = tf.tensor1d(ages);
All fields can be processed at once using a transpose of the 2D tensor generated
with the sheet_to_json
export with header: 1
. The first row, if it contains
header labels, should be removed with a slice:
1const XLSX = require("xlsx"); 2const tf = require('@tensorflow/tfjs'); 3 4/* array of arrays of the data starting on the second row */ 5const aoa = XLSX.utils.sheet_to_json(worksheet, {header: 1}).slice(1); 6/* dataset in the "correct orientation" */ 7const tf_dataset = tf.tensor2d(aoa).transpose(); 8/* pull out each dataset with a slice */ 9const tf_field0 = tf_dataset.slice([0,0], [1,tensor.shape[1]]).flatten(); 10const tf_field1 = tf_dataset.slice([1,0], [1,tensor.shape[1]]).flatten();
The array
demo shows a complete example.
API
Generate HTML Table from Worksheet
1var html = XLSX.utils.sheet_to_html(worksheet);
The sheet_to_html
utility function generates HTML code based on the worksheet
data. Each cell in the worksheet is mapped to a <TD>
element. Merged cells
in the worksheet are serialized by setting colspan
and rowspan
attributes.
Examples
The sheet_to_html
utility function generates HTML code that can be added to
any DOM element by setting the innerHTML
:
1var container = document.getElementById("tavolo"); 2container.innerHTML = XLSX.utils.sheet_to_html(worksheet);
Combining with fetch
, constructing a site from a workbook is straightforward:
1<body> 2 <style>TABLE { border-collapse: collapse; } TD { border: 1px solid; }</style> 3 <div id="tavolo"></div> 4 <script src="https://unpkg.com/xlsx/dist/xlsx.full.min.js"></script> 5 <script type="text/javascript"> 6(async() => { 7 /* fetch and parse workbook -- see the fetch example for details */ 8 const workbook = XLSX.read(await (await fetch("sheetjs.xlsx")).arrayBuffer()); 9 10 let output = []; 11 /* loop through the worksheet names in order */ 12 workbook.SheetNames.forEach(name => { 13 14 /* generate HTML from the corresponding worksheets */ 15 const worksheet = workbook.Sheets[name]; 16 const html = XLSX.utils.sheet_to_html(worksheet); 17 18 /* add a header with the title name followed by the table */ 19 output.push(`<H3>${name}</H3>${html}`); 20 }); 21 /* write to the DOM at the end */ 22 tavolo.innerHTML = output.join("\n"); 23})(); 24 </script> 25</body>
It is generally recommended to use a React-friendly workflow, but it is possible
to generate HTML and use it in React with dangerouslySetInnerHTML
:
1function Tabeller(props) {
2 /* the workbook object is the state */
3 const [workbook, setWorkbook] = React.useState(XLSX.utils.book_new());
4
5 /* fetch and update the workbook with an effect */
6 React.useEffect(() => { (async() => {
7 /* fetch and parse workbook -- see the fetch example for details */
8 const wb = XLSX.read(await (await fetch("sheetjs.xlsx")).arrayBuffer());
9 setWorkbook(wb);
10 })(); });
11
12 return workbook.SheetNames.map(name => (<>
13 <h3>name</h3>
14 <div dangerouslySetInnerHTML={{
15 /* this __html mantra is needed to set the inner HTML */
16 __html: XLSX.utils.sheet_to_html(workbook.Sheets[name])
17 }} />
18 </>));
19}
The react
demo includes more React examples.
It is generally recommended to use a VueJS-friendly workflow, but it is possible
to generate HTML and use it in VueJS with the v-html
directive:
1import { read, utils } from 'xlsx'; 2import { reactive } from 'vue'; 3 4const S5SComponent = { 5 mounted() { (async() => { 6 /* fetch and parse workbook -- see the fetch example for details */ 7 const workbook = read(await (await fetch("sheetjs.xlsx")).arrayBuffer()); 8 /* loop through the worksheet names in order */ 9 workbook.SheetNames.forEach(name => { 10 /* generate HTML from the corresponding worksheets */ 11 const html = utils.sheet_to_html(workbook.Sheets[name]); 12 /* add to state */ 13 this.wb.wb.push({ name, html }); 14 }); 15 })(); }, 16 /* this state mantra is required for array updates to work */ 17 setup() { return { wb: reactive({ wb: [] }) }; }, 18 template: ` 19 <div v-for="ws in wb.wb" :key="ws.name"> 20 <h3>{{ ws.name }}</h3> 21 <div v-html="ws.html"></div> 22 </div>` 23};
The vuejs
demo includes more React examples.
The sheet_to_*
functions accept a worksheet object.
API
Generate a CSV from a single worksheet
1var csv = XLSX.utils.sheet_to_csv(worksheet, opts);
This snapshot is designed to replicate the "CSV UTF8 (.csv
)" output type.
"Delimiter-Separated Output" describes the
function and the optional opts
argument in more detail.
Generate "Text" from a single worksheet
1var txt = XLSX.utils.sheet_to_txt(worksheet, opts);
This snapshot is designed to replicate the "UTF16 Text (.txt
)" output type.
"Delimiter-Separated Output" describes the
function and the optional opts
argument in more detail.
Generate a list of formulae from a single worksheet
1var fmla = XLSX.utils.sheet_to_formulae(worksheet);
This snapshot generates an array of entries representing the embedded formulae. Array formulae are rendered in the form
Stable Version
2
7.5/10
Summary
SheetJS Regular Expression Denial of Service (ReDoS)
Affected Versions
< 0.20.2
Patched Versions
0.20.2
7.8/10
Summary
Prototype Pollution in sheetJS
Affected Versions
< 0.19.3
Patched Versions
0.19.3
3
5.5/10
Summary
Denial of Service in SheetJS Pro
Affected Versions
< 0.17.0
Patched Versions
0.17.0
5.5/10
Summary
Denial of Service in SheetJS Pro
Affected Versions
< 0.17.0
Patched Versions
0.17.0
5.5/10
Summary
Denial of Service in SheetsJS Pro
Affected Versions
< 0.17.0
Patched Versions
0.17.0
Reason
no binaries found in the repo
Reason
0 existing vulnerabilities detected
Reason
license file detected
Details
Reason
Found 6/30 approved changesets -- score normalized to 2
Reason
0 commit(s) and 0 issue activity found in the last 90 days -- score normalized to 0
Reason
no effort to earn an OpenSSF best practices badge detected
Reason
security policy file not detected
Details
Reason
project is not fuzzed
Details
Reason
branch protection not enabled on development/release branches
Details
Reason
SAST tool is not run on all commits -- score normalized to 0
Details
Score
Last Scanned on 2024-12-09
The Open Source Security Foundation is a cross-industry collaboration to improve the security of open source software (OSS). The Scorecard provides security health metrics for open source projects.
Learn More