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

New Features!

• Number Formats
  • Now including read()

Contents

• Interface
  • Create a Workbook
  • Add a Worksheet
  • Access Worksheets
  • Fetching Sheets
  • Column Headers
  • Adding and Handling Rows
  • Handling Individual Cells
  • Merged Cells
  • Number Formats
  • Reading XLSX
  • Writing XLSX
• Value Types

Interface

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

Fetching Sheets

// by name
var sheet = workbook.getWorksheet("My Sheet");

// by id
var sheet = workbook.getWorksheet(1);

Column Headers

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

Adding and Handling Rows

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

## Handling Individual Cells

```javascript
// Modify/Add individual cell
sheet.getCell("C3").value = new Date(1968, 5, 1);

// query a cell's type
assert(sheet.getCell("C3").type == Excel.ValueType.Date);

Merged Cells

// merge a range of cells
sheet.mergeCells("A4:B5");

// ... merged cells are linked
sheet.getCell("B5").value = "Hello, World!";
assert(sheet.getCell("A4").value === sheet.getCell("B5").value);
assert(sheet.getCell("A4") === sheet.getCell("B5").master);

Number Formats

// display value as "1 3/5"
ws.getCell("A1").value = 1.6;
ws.getCell("A1").numFmt = "# ?/?";

// display value as "1.60%"
ws.getCell("B1").value = 1.6;
ws.getCell("B1").numFmt = "0.00%";

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

Value Types

The following value types are supported.

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 }