Free Node.js Library to Create & Read Excel XLSX Files

Getting Started with XLSX-Populate

The recommended way to install XLSX-Populate is using npm, please use the following script for a smooth installation.

npm install xlsx-populate

XLSX Workbook Creation and Modification in Node.js

The open source XLSX-Populate library has provided complete and robust functionality for creating new Excel workbooks from scratch as well as modifying existing ones inside Node.js applications. This includes the ability to add, rename, and delete worksheets within a workbook, giving users full control over the structure of their Excel XLSX files. Here is a very simple example that shows, how software developers can populate data in a workbook and access worksheet and cells within the workbook to manipulate them. Inside Node.js environment.

const XlsxPopulate = require('xlsx-populate');

// Load a new blank workbook
XlsxPopulate.fromBlankAsync()
    .then(workbook => {
        // Modify the workbook.
        workbook.sheet("Sheet1").cell("A1").value("This is neat!");

        // Write to file.
        return workbook.toFileAsync("./out.xlsx");
    });

Apply Style & Formatting to XLSX File in Node.js

Beyond basic cell values and formulas, the XLSX-Populate library excels in styling and formatting capabilities inside Node.js environment. Software Developers can merge cells, apply border styles, set cell alignment, as well as can apply rich text formatting within cells, including multiple fonts, colors, and styles in a single cell. Conditional formatting is also supported, which means you can dynamically change cell styles based on their values, making it easier to highlight important data trends and outliers. The following example demonstrates a simple code example for setting and getting a cell style inside Node.js applications.

// Get a single style
const bold = cell.style("bold"); // true

// Get multiple styles
const styles = cell.style(["bold", "italic"]); // { bold: true, italic: true }

// Set a single style
cell.style("bold", true);

// Set multiple styles
cell.style({ bold: true, italic: true });

Worksheet Cell Manipulation in Node.js Apps

One of the core features of open source XLSX-Populate library is its extensive support for handling and manipulating data inside worksheet cell. Software Developers can easily set and get the value of cells, which can be numbers, strings, dates, or even rich text. Additionally, the library supports cell formatting, allowing for customization of font styles, sizes, colors, and more. This is essential for creating professional and readable spreadsheets. Here is a very useful example that shows how to set a cell to rich texts inside Node.js applications.

const RichText = require('xlsx-Populate').RichText;
const cell = workbook.sheet(0).cell('A1');
// set a cell value to rich text
cell.value(new RichText());

// add two rich text fragments
cell.value()
    .add('hello ', { italic: true, bold: true })
    .add('world!', { fontColor: 'FF0000' });

Data Validation in Excel XLSX Files in Node.js

Data validation is a critical feature for ensuring data integrity and accuracy in Excel files. The open source XLSX-Populate library makes it easy for software developers to set data validation rules on cells inside their Node.js applications. This includes dropdown lists, numeric constraints, and other validation criteria that help prevent data entry errors. The following example shows, how software developer can set/get data validation inside Node.js environment.

// Set the data validation
cell.dataValidation({
    type: 'list',
    allowBlank: false,
    showInputMessage: false,
    prompt: false,
    promptTitle: 'String',
    showErrorMessage: false,
    error: 'String',
    errorTitle: 'String',
    operator: 'String',
    formula1: '$A:$A',//Required
    formula2: 'String'
});

//Here is a short version of the one above.
cell.dataValidation('$A:$A');

// Get the data validation
const obj = cell.dataValidation(); // Returns an object

// Remove the data validation
cell.dataValidation(null); //Returns the cell