exceljs

What is exceljs?

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.

What are exceljs's main functionalities?

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();"}

Other packages similar to exceljs

xlsx

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

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.

node-xlsx

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.

README

ExcelJS

Read, manipulate and write spreadsheet data to XLSX and JSON.

Reverse engineered from Excel spreadsheet files as a project.

Installation

npm install exceljs

Interface

var Excel = require("????");

Construction

// 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 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 another row by Array sheet.addRow([3, "Sam", new Date()]);

// 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"));

Value Types

Name	Enum(*)	Description	Example Value
Null	0	No value.	null
Merge	1	N/A	N/A
Number	2	A numerical value	3.14
String	3	A text value	"Hello, World!"
Date	4	A Date value	new Date()
Hyperlink	5	A hyperlink	{ text: "www.mylink.com", hyperlink: "http://www.mylink.com" }
Formula	6	A formula	{ formula: "A1+A2", result: 7 }

(*) Enum values can be accessed via Excel.ValueType

Reading XLSX

// 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());

Writing XLSX

// 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 });