Introduction to js-xlsx
js-xlsx is a powerful library for reading, manipulating, and writing spreadsheet data and this guide will walk you through its core functionalities and provide dozens of useful API explanations with code snippets.
Getting Started with js-xlsx
npm install xlsx
Reading a Spreadsheet
const XLSX = require('xlsx');
const workbook = XLSX.readFile('example.xlsx');
const sheet_name_list = workbook.SheetNames;
const worksheet = workbook.Sheets[sheet_name_list[0]];
const data = XLSX.utils.sheet_to_json(worksheet);
console.log(data);
Generating a New Spreadsheet
const new_workbook = XLSX.utils.book_new();
const new_worksheet = XLSX.utils.json_to_sheet(data);
XLSX.utils.book_append_sheet(new_workbook, new_worksheet, 'Sheet1');
XLSX.writeFile(new_workbook, 'new_example.xlsx');
Appending Data to Existing Worksheet
// Append rows to existing worksheet
const new_data = [
{"name": "John", "age": 28},
{"name": "Jane", "age": 32}
];
XLSX.utils.sheet_add_json(worksheet, new_data, {skipHeader: true, origin: -1});
XLSX.writeFile(workbook, 'updated_example.xlsx');
Adding a New Worksheet
// Add a new worksheet
const another_worksheet = XLSX.utils.json_to_sheet([{"name": "Alice", "age": 24}]);
XLSX.utils.book_append_sheet(workbook, another_worksheet, 'Sheet2');
XLSX.writeFile(workbook, 'example_with_two_sheets.xlsx');
Advanced Examples and Use Cases
Filtering Data
const filtered_data = data.filter(row => row.age < 30);
XLSX.utils.sheet_add_json(worksheet, filtered_data, {skipHeader: true, origin: "A2"});
XLSX.writeFile(workbook, 'filtered_example.xlsx');
Styling Cells
worksheet['A1'].s = {
font: {name: "Arial", sz: 24, color: {rgb: "FF0000"}},
fill: {fgColor: {rgb: "FFFF00"}}
};
XLSX.writeFile(workbook, 'styled_example.xlsx');
Creating a Web App Example
Let's build a simple web application that allows users to upload an Excel file and display its contents:
// index.html
<!DOCTYPE html>
<html>
<head>
<title>Excel Reader</title>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.17.0/xlsx.full.min.js"></script>
</head>
<body>
<input type="file" id="upload" />
<pre id="output"></pre>
<script>
document.getElementById('upload').addEventListener('change', (evt) => {
let file = evt.target.files[0];
let reader = new FileReader();
reader.onload = (e) => {
let data = new Uint8Array(e.target.result);
let workbook = XLSX.read(data, {type: 'array'});
let worksheet = workbook.Sheets[workbook.SheetNames[0]];
let json = XLSX.utils.sheet_to_json(worksheet, {header: 1});
document.getElementById('output').textContent = JSON.stringify(json, null, 2);
};
reader.readAsArrayBuffer(file);
});
</script>
</body>
</html>
This example demonstrates how to set up an HTML file with an input for uploading Excel files and a script to read and display the file contents using js-xlsx.
By understanding these APIs and implementing them in various ways, you can handle a wide range of tasks involving spreadsheet data with js-xlsx.
Hash: ae81d69d2d6ad5b94fcd6cc123c13c0652a8a09735653288be3ef51ffe296d23
