The SheetJS recommended in this issue is a new spreadsheet for both traditional and modern software.
File format support
SheetJS Community Edition provides a tried-and-true open source solution for extracting useful data from almost any complex spreadsheet and generating new spreadsheets that work with both traditional and modern software.
SheetJS Pro offers solutions that go beyond data processing: easy editing of complex templates; Use shape to release your inner Picasso; Create custom worksheets using images/charts/pivottables; Evaluate formula expressions and port calculations to Web applications; Automate common spreadsheet tasks and more.
class=”pgc-h-arrow-right” data-track=”4″>
class=”pgc-h-arrow-right” data-track=”37″>
Complete browser independent build save dist/xlsx.full.min.js and can be added directly to with < script> Page with tag:
< script lang="javascript" src="dist/xlsx.full.min.js"> < /script> Each individual release script is available at https://cdn.sheetjs.com/. The latest version uses the latest tag:
< ! -- use the latest version -->
< script lang="javascript" src="https://cdn.sheetjs.com/xlsx-latest/package/dist/xlsx.full.min.js"> < /script> can refer to a specific distribution by version:
< ! -- use version 0.18.5 -->
< script lang="javascript" src= < / span > "https://cdn.sheetjs.com/xlsx-0.18.5/package/dist/xlsx.full.min.js" & gt; < /script> For production use, download the script and add it to the public folder along with other scripts.
Browser build
The full single-file version is generated at dist/xlsx.full.min.js
dist/xlsx.core.min.js omit code page library (XLS encoding not supported)
in dist/xlsx.mini.min.js. Compared to the full version:
-
- Skip code page library (XLS encoding not supported)
XLSB/XLS/Lotus 1-2-3 / SpreadsheetML 2003 / Numbers
- Node flow utility removed
These scripts are also available on the CDN:
< ! -- use xlsx.mini.min.js from the latest version -->
< script lang="javascript" src="https://cdn.sheetjs.com/xlsx-latest/package/dist/xlsx.mini.min.js"> < /script> ECMAScript module
ECMAScript module builds are saved to xlsx.mjs and can be added directly to pages with script tags, using type=”module” :
< script type="module">
import { read, writeFileXLSX } from "https://cdn.sheetjs.com/xlsx-latest/package/xlsx.mjs";
/* load the codepage support library for extended support with older formats */
import { set_cptable } from "https://cdn.sheetjs.com/xlsx-latest/package/xlsx.mjs";
import * as cptable from 'https://cdn.sheetjs.com/xlsx-latest/package/dist/cpexcel.full.mjs';
set_cptable(cptable);
< /script> The NodeJS package also exposes modules with the module parameter, which Angular and other projects support:
import { read, writeFileXLSX } from "xlsx";
/* load the codepage support library for extended support with older formats */
import { set_cptable } from "xlsx";
import * as cptable from 'xlsx/dist/cpexcel.full.mjs';
set_cptable(cptable); class=”pgc-h-arrow-right” data-track=”30″>
Most scenarios involving spreadsheets and data can be divided into 5 parts:
- Get data : Data can be stored anywhere: local or remote files, databases, HTML tables, or even generated programmatically in a Web browser.
- Extract data : For spreadsheet files, this involves parsing raw bytes to read cell data. For general JS data, this involves reshaping the data.
- Processing data : This step from generating summary statistics to cleaning data records is at the heart of the problem.
- packet : This might involve making a new spreadsheet or using XML to serialize Jsor.stringify or writing XML or simply flattening the data for UI tools.
- Publish data : The spreadsheet file can be uploaded to the server or written locally. The data can be presented to the user 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, the HTML TABLE on the page will be scraped, a line with the report date will be added at the bottom, and the new file will be generated and downloaded locally. XLSX.writeFile is responsible for packing the data and trying to download it locally:
// Acquire Data (reference to the HTML table)
var table_elt = document.getElementById("my-table-id");
// Extract Data (create a workbook object from the table)
var workbook = XLSX.utils.table_to_book(table_elt);
// Process Data (add a new row)
var ws = workbook.Sheets["Sheet1"];
XLSX.utils.sheet_add_aoa(ws, [["Created "+new Date().toISOString()]], {origin:-1});
// Package and Release Data (`writeFile` tries to write and save an XLSB file)
XLSX.writeFile(workbook, "Report.xlsb"); read The library tries to simplify steps 2 and 4 by using the ability to extract useful data from the spreadsheet file (/) and generate a new spreadsheet file from the data (/)readFile. Other utility features, such as use with other common data sources such as HTML tables.
writewriteFiletable_to_book This document and various demonstration projects cover many of the common scenarios and approaches for steps 1 and 5.
class=”pgc-h-arrow-right” data-track=”40″>
Parse workbook
Extract data from spreadsheet bytes
var workbook = XLSX.read(data, opts); This method can extract data from a spreadsheet byte stored in a JS string, a “binary string”, a NodeJS buffer, or a typed array (or) read. Uint8ArrayArrayBuffer
Read spreadsheet bytes from local file and extract data
var workbook = XLSX.readFile(filename, opts); The readFile method attempts to read the spreadsheet file in the provided path. Browsers are generally not allowed to read files this way (it is considered a security risk), and attempts to read files this way will throw an error.
class=”pgc-h-arrow-right” data-track=”69″> example
Local file in NodeJS server
readFilefs.readFileSync is used under the hood:
var XLSX = require("xlsx");
var workbook = XLSX.readFile("test.xlsx"); readFile does not enable helpers for the node ESM. Instead, fs.readFileSync should be used to read file data to Buffer for use with XLSX.read:
import { readFileSync } from "fs";
import { read } from "xlsx/xlsx.mjs";
const buf = readFileSync("test.xlsx");
/* buf is a Buffer */
const workbook = read(buf); user-submitted file in the web page
// XLSX is a global from the standalone script
async function handleDropAsync(e) {
e.stopPropagation(); e.preventDefault();
const f = e.dataTransfer.files[0];
/* f is a File */
const data = await f.arrayBuffer();
/* data is an ArrayBuffer */
const workbook = XLSX.read(data);
/* DO SOMETHING WITH workbook HERE */
}
drop_dom_element.addEventListener("drop", handleDropAsync, false);For maximum compatibility, FileReader should use API:
function handleDrop(e) {
e.stopPropagation(); e.preventDefault();
var f = e.dataTransfer.files[0];
/* f is a File */
var reader = new FileReader();
reader.onload = function(e) {
var data = e.target.result;
/* reader.readAsArrayBuffer(file) -> data will be an ArrayBuffer */
var workbook = XLSX.read(data);
/* DO SOMETHING WITH workbook HERE */
};
reader.readAsArrayBuffer(f);
}
drop_dom_element.addEventListener("drop", handleDrop, false);Simple Download Example
/**
* JSON Conversion Excel
*/
function jsonToSheet () {
let json = [
{
"Name of person": "EJ",
"Gender": "male",
},
{
"Name of person": "LSQ",
"Gender": "famale",
}
]
// Instantiate a workbook
let book = XLSX.utils.book_new()
// Instantiate a Sheet
let sheet = XLSX.utils.json_to_sheet(json, {
header: ['Name of person', 'Gender']
})
// Writes the Sheet to the workbook
XLSX.utils.book_append_sheet(book, sheet, 'Sheet1')
// Write to the file, directly trigger the browser download
XLSX.writeFile(book, 'jsonToSheet.xlsx')
}
/**
* Array conversion Excel
*/
function arrayToSheet () {
let data = [
['Name of person', 'Gender'],
['EJ', 'male'],
['LSQ', 'famale']
]
// Instantiate a workbook
let book = XLSX.utils.book_new()
// Instantiate a Sheet
let sheet = XLSX.utils.aoa_to_sheet(data)
//Writes the Sheet to the workbook
XLSX.utils.book_append_sheet(book, sheet, 'Sheet1')
// Write to the file, directly trigger the browser download
XLSX.writeFile(book, 'arrayToSheet.xlsx')
}—END—
Open source protocol：Apache-2.0 License
