1. Products
  2.   Spreadsheet
  3.   JavaScript
  4.   xlsx-populate
 
  

JavaScript Library to Generate and Parse Excel XLSX Files  

Open Source JavaScript API to Populate Excel data from a worksheet, manage rows and columns, manage Excel sheets, data validation, XLSX agile encryption or decryption, and so on.

What is xlsx-populate ?

xlsx-populate is a powerful JavaScript library that enables software developers to build applications that can generate and parse Excel XLSX with ease. The library was first written for Node.js and later browser support is also provided. The library has included several important features such as populating Excel data from excel sheets, pulling data out of existing workbooks, managing rows, and columns, managing Excel sheets, finding and replacing data, managing cell formatting, rich texts support, Data validation support, manage hyperlinks, page margins support, printing support, XLSX agile encryption or decryption and much more.

The library is very stable and emphasizes on keeping the existing workbook features and styles intact. The library supports several important features related to spreadsheet rows and columns, such as you can access rows and columns in order to change the size, hide or show, or access cells.

he xlsx-populate can easily read an Excel worksheet from a specified Excel file, get its data in JSON format, and can export it to any other worksheet with ease. You can easily parse and manipulate multiple cells at once. The library has included support for several important features related to managing Excel Worksheets and workbooks such as adding new sheets, renaming a worksheet, moving the sheet to other locations, and many more.

Previous Next

Getting Started with xlsx-populate

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

Install xlsx-populate via npm

npm install xlsx-populate

You can download the compiled shared library from GitHub repository and install it.

Manage Excel Worksheet via JavaScript API

The open source library xlsx-populate has provided support for managing your excel worksheets with ease inside their own JavaScript applications. The library has included numerous options for managing sheets, such as adding a new worksheet, assigning names to a worksheet, renaming a sheet, move worksheet, delete a worksheet, get the active sheet, getting a sheet by name, or index, and so on.

Add New Worksheet in Different ways via JavaScript API

// Add a new sheet named 'New 1' at the end of the workbook
const newSheet1 = workbook.addSheet('New 1');

// Add a new sheet named 'New 2' at index 1 (0-based)
const newSheet2 = workbook.addSheet('New 2', 1);

// Add a new sheet named 'New 3' before the sheet named 'Sheet1'
const newSheet3 = workbook.addSheet('New 3', 'Sheet1');

// Add a new sheet named 'New 4' before the sheet named 'Sheet1' using a Sheet reference.
const sheet = workbook.sheet('Sheet1');
const newSheet4 = workbook.addSheet('New 4', sheet);

Search and Replace in Excel Workbook

The free library xlsx-populate enables software developers to easily search text in a workbook and can also replace it with a new one using JavaScript commands. It provides several important options such as finding all occurrences of the text, searching text in the first sheet, finding the text but don't replace it, checking if a particular cell matches the value, replacing all lowercase letters with uppercase, and many more.

Find & Replace Text in Cells in a Workbook via JavaScript API

// Find all occurrences of the text "foo" in the workbook and replace with "bar".
workbook.find("foo", "bar"); // Returns array of matched cells

// Find the matches but don't replace.
workbook.find("foo");

// Just look in the first sheet.
workbook.sheet(0).find("foo");

// Check if a particular cell matches the value.
workbook.sheet("Sheet1").cell("A1").find("foo"); // Returns true or false

Cell Formatting in Excel Workbook

The open source library xlsx-populate supports several important features for Excel worksheet cell formatting using JavaScript code. You can easily set or get a single style as well as multiple styles with ease. You can also set all cells in a range with a single style or multiple styles using any combination. Same as Microsoft Excel setting a style on a column will apply that style to all existing cells as well as to any new cells that are populated.

Set/Get Single or Multiple Styles for Worksheet Cells via JavaScript

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

Excel Data Validation support

The data validation feature in Microsoft Excel helps users to control what can be entered in the worksheet. For example, you can place a check that a number value is entered or date occurs is a particular format, etc. The open source library xlsx-populate provides a complete support setting, getting or removing a cell data validation. You can also apply validation on a range of cells with ease

Perform Data Validation via JavaScript API

// 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
 English