Security News
NIST Misses 2024 Deadline to Clear NVD Backlog
NIST has failed to meet its self-imposed deadline of clearing the NVD's backlog by the end of the fiscal year. Meanwhile, CVE's awaiting analysis have increased by 33% since June.
The exceljs npm package is a comprehensive library for reading, writing, and manipulating Excel files in various formats such as XLSX, CSV, and more. It provides a wide range of functionalities to work with Excel documents programmatically, including creating new sheets, styling cells, adding formulas, and handling large datasets efficiently.
Reading Excel Files
This feature allows you to read existing Excel files. You can iterate through rows and cells, access values, and perform operations based on the data.
{"const ExcelJS = require('exceljs');
const workbook = new ExcelJS.Workbook();
workbook.xlsx.readFile('path/to/file.xlsx')
.then(() => {
const worksheet = workbook.getWorksheet('Sheet1');
worksheet.eachRow({ includeEmpty: true }, (row, rowNumber) => {
console.log('Row ' + rowNumber + ' = ' + JSON.stringify(row.values));
});
});"}
Writing Excel Files
This feature enables you to create new Excel files or modify existing ones. You can add worksheets, rows, and cells with data, and save the file to the disk.
{"const ExcelJS = require('exceljs');
const workbook = new ExcelJS.Workbook();
const sheet = workbook.addWorksheet('My Sheet');
sheet.addRow(['Name', 'Profession']);
sheet.addRow(['John Doe', 'Developer']);
workbook.xlsx.writeFile('path/to/newfile.xlsx');"}
Styling Cells
This feature allows you to apply various styles to cells, such as fonts, colors, borders, and fills. It helps in making the data more readable and visually appealing.
{"const ExcelJS = require('exceljs');
const workbook = new ExcelJS.Workbook();
const sheet = workbook.addWorksheet('My Sheet');
const row = sheet.addRow(['Name', 'Profession']);
row.getCell(1).font = { bold: true };
row.getCell(2).fill = {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: 'FFFF0000' }
};
workbook.xlsx.writeFile('path/to/styledfile.xlsx');"}
Adding Formulas
This feature lets you insert formulas into cells. You can also pre-calculate the result of the formula and store it in the cell.
{"const ExcelJS = require('exceljs');
const workbook = new ExcelJS.Workbook();
const sheet = workbook.addWorksheet('My Sheet');
const row = sheet.addRow([100, 200, { formula: 'A1+B1', result: 300 }]);
workbook.xlsx.writeFile('path/to/formulafile.xlsx');"}
Handling Large Data Sets
This feature is particularly useful for handling large datasets without running out of memory. It streams the data to the file system as it's being processed.
{"const ExcelJS = require('exceljs');
const workbook = new ExcelJS.stream.xlsx.WorkbookWriter({ filename: 'path/to/largefile.xlsx' });
const sheet = workbook.addWorksheet('My Sheet');
for (let i = 0; i < 1000000; i++) {
sheet.addRow(['Row ' + i, 'Data']);
}
workbook.commit();"}
The 'xlsx' package is another popular library for parsing and writing various spreadsheet formats. It is known for its simplicity and small bundle size, but it may not offer as many features for styling and manipulating data as exceljs.
SheetJS, also known as 'xlsx', is a powerful and comprehensive library that supports a wide range of spreadsheet formats. It is similar to exceljs in functionality but differs in API design and implementation details.
The 'node-xlsx' package is a simpler alternative for parsing and building XLSX/CSV files. It focuses on basic functionality and is easier to use for simple tasks, but lacks the advanced features and fine control provided by exceljs.
Read, manipulate and write spreadsheet data to XLSX and JSON.
Reverse engineered from Excel spreadsheet files as a project.
npm install exceljs
var Excel = require("exceljs");
// create a workbook
var workbook = new Excel.Workbook();
// add a worksheet
var sheet = workbook.addWorksheet("My Sheet");
// access worksheets
workbook.worksheets.forEach(function(worksheet) {
// ...
});
// fetch sheet by name
var sheet = workbook.getWorksheet("My Sheet");
// fetch sheet by id
var sheet = workbook.getWorksheet(1);
// Add column headers and define column keys and widths
sheet.columns = [
{ header: "Id", key: "id", width: 10 },
{ header: "Name", key: "name", width: 32 },
{ header: "D.O.B.", key: "dob", width: 10 }
];
// Add a couple of Rows by key-value (after the last current row)
sheet.addRow({id: 1, name: "John Doe", dob: new Date(1970,1,1)});
sheet.addRow({id: 2, name: "Jane Doe", dob: new Date(1965,1,7)});
// Add a row by contiguous Array (assign to columns A, B & C)
sheet.addRow([3, "Sam", new Date()]);
// Add a row by sparse Array (assign to columns A, E & I)
var row = [];
row[1] = 4;
row[5] = "Kyle";
row[9] = new Date();
sheet.addRow(row);
// get a row as a sparse array
row = sheet.getRow(4);
assert(row[5] == "Kyle");
// iterate over all rows (as sparse arrays)
sheet.eachRow(function(number, row) {
console.log("Row " + number + " = " + JSON.stringify(row));
});
// Modify individual cells
sheet.getCell("C3").value = new Date(1968, 5, 1);
// query a cell's type
assert(sheet.getCell("C3").type == Excel.ValueType.Date);
// Add new cells individually
sheet.getCell("C5").value = {formula:"MIN(C2:C3)", new Date(1965,1,7)};
// merge cells
sheet.mergeCells("A4:B5");
// ... merged cells are linked
sheet.getCell("B5").value = "Hello, World!";
assert(sheet.getCell("A4").value === sheet.getCell("B5"));
Enum Name | Enum(*) | Description | Example Value |
---|---|---|---|
Excel.ValueType.Null | 0 | No value. | null |
Excel.ValueType.Merge | 1 | N/A | N/A |
Excel.ValueType.Number | 2 | A numerical value | 3.14 |
Excel.ValueType.String | 3 | A text value | "Hello, World!" |
Excel.ValueType.Date | 4 | A Date value | new Date() |
Excel.ValueType.Hyperlink | 5 | A hyperlink | { text: "www.mylink.com", hyperlink: "http://www.mylink.com" } |
Excel.ValueType.Formula | 6 | A formula | { formula: "A1+A2", result: 7 } |
// read from a file
var workbook = new Excel.Workbook();
workbook.xlsx.readFile(filename)
.then(function() {
// use workbook
});
// pipe from stream
var workbook = new Excel.Workbook();
stream.pipe(workbook.xlsx.createInputStream());
// write to a file
var workbook = createAndFillWorkbook();
workbook.xlsx.writeFile(filename)
.then(function() {
// done
});
// write to a stream
workbook.xlsx.write(stream)
.then(function() {
// done
});
FAQs
Excel Workbook Manager - Read and Write xlsx and csv Files.
The npm package exceljs receives a total of 1,606,390 weekly downloads. As such, exceljs popularity was classified as popular.
We found that exceljs demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 2 open source maintainers collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
NIST has failed to meet its self-imposed deadline of clearing the NVD's backlog by the end of the fiscal year. Meanwhile, CVE's awaiting analysis have increased by 33% since June.
Security News
Cloudflare has launched a setup wizard allowing users to easily create and manage a security.txt file for vulnerability disclosure on their websites.
Security News
The Socket Research team breaks down a malicious npm package targeting the legitimate DOMPurify library. It uses obfuscated code to hide that it is exfiltrating browser and crypto wallet data.