xlsx-populate (dtjohnson/xlsx-populate)

xlsx-populate

https://github.com/dtjohnson/xlsx-populate
Admin
Excel XLSX parser and generator written in JavaScript with Node.js and browser support, featuring...

Tokens:50,110
Snippets:319
Trust Score:6.7
Update:4 months ago
Show doc for...
Context Summary (auto-generated)
Raw
# xlsx-populate

xlsx-populate is a comprehensive Excel XLSX parser and generator written in JavaScript with support for both Node.js and browsers. The library provides a jQuery/d3-style method chaining interface that makes it intuitive to manipulate Excel workbooks programmatically. Unlike many other Excel libraries, xlsx-populate is designed with a focus on preserving existing workbook features and styles, which means you can open, modify, and save Excel files without losing formatting, formulas, or other advanced features that may not be explicitly supported by the library's API.

The library supports a wide range of Excel features including cells, ranges, rows, columns, sheets, formulas, styles, rich text, hyperlinks, data validation, encryption, and more. It uses JSZip to handle the XLSX file format (which is essentially a ZIP archive of XML files) and manipulates the underlying XML structure directly rather than deserializing it into a complete object model. This approach allows the library to preserve unsupported features while still providing a rich API for common operations. The library is particularly useful for generating reports, populating templates, parsing data from existing workbooks, and automating Excel-based workflows.

## APIs and Key Functions

### Creating and Loading Workbooks

Create a new blank workbook or load from file/data.

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

// Create a new blank workbook
XlsxPopulate.fromBlankAsync()
    .then(workbook => {
        workbook.sheet("Sheet1").cell("A1").value("Hello World");
        return workbook.toFileAsync("./new-workbook.xlsx");
    })
    .catch(err => console.error(err));

// Load from existing file
XlsxPopulate.fromFileAsync("./existing.xlsx")
    .then(workbook => {
        const value = workbook.sheet("Sheet1").cell("A1").value();
        console.log("Cell A1 contains:", value);
    })
    .catch(err => console.error(err));

// Load from data (Buffer, Blob, ArrayBuffer, etc.)
const fs = require('fs');
const data = fs.readFileSync('./input.xlsx');
XlsxPopulate.fromDataAsync(data)
    .then(workbook => {
        console.log("Workbook loaded from buffer");
    })
    .catch(err => console.error(err));
```

### Cell Operations

Read and write cell values with various data types.

```javascript
XlsxPopulate.fromBlankAsync()
    .then(workbook => {
        const sheet = workbook.sheet(0);

        // Set different value types
        sheet.cell("A1").value("Text String");
        sheet.cell("A2").value(42);
        sheet.cell("A3").value(true);
        sheet.cell("A4").value(new Date(2024, 0, 1));
        sheet.cell("A5").value(null); // Clear cell

        // Set formula
        sheet.cell("A6").formula("SUM(A2:A5)");

        // Get values
        const textValue = sheet.cell("A1").value();
        const numberValue = sheet.cell("A2").value();

        // Navigate cells relatively
        sheet.cell("B1")
            .value("First")
            .relativeCell(1, 0).value("Second")  // B2
            .relativeCell(1, 0).value("Third");  // B3

        // Get cell information
        const address = sheet.cell("A1").address(); // "A1"
        const rowNum = sheet.cell("A1").rowNumber(); // 1
        const colNum = sheet.cell("A1").columnNumber(); // 1
        const colName = sheet.cell("A1").columnName(); // "A"

        return workbook.toFileAsync("./cells.xlsx");
    })
    .catch(err => console.error(err));
```

### Range Operations

Manipulate multiple cells at once with ranges.

```javascript
XlsxPopulate.fromBlankAsync()
    .then(workbook => {
        const sheet = workbook.sheet("Sheet1");

        // Create a range
        const range = sheet.range("A1:C3");

        // Set all cells to same value
        range.value(5);

        // Set values with 2D array
        range.value([
            ["Name", "Age", "City"],
            ["John", 30, "New York"],
            ["Jane", 25, "Boston"]
        ]);

        // Set values with callback
        range.value((cell, ri, ci) => `R${ri}C${ci}`);

        // Get all values as 2D array
        const values = range.value();
        console.log(values);

        // Apply styles to range
        range.style("bold", true);

        // Merge cells
        sheet.range("A5:C5").merged(true).value("Merged Cell");

        // Get used range (all cells with data)
        const usedRange = sheet.usedRange();
        if (usedRange) {
            const allData = usedRange.value();
            console.log("Sheet data:", allData);
        }

        // Iterate over range
        range.forEach((cell, ri, ci, range) => {
            console.log(`Cell [${ri},${ci}]:`, cell.value());
        });

        // Map range to new values
        const mapped = range.map((cell, ri, ci) => cell.value() * 2);

        return workbook.toFileAsync("./ranges.xlsx");
    })
    .catch(err => console.error(err));
```

### Styling Cells

Apply comprehensive formatting to cells, ranges, rows, and columns.

```javascript
XlsxPopulate.fromBlankAsync()
    .then(workbook => {
        const sheet = workbook.sheet(0);

        // Font styles
        sheet.cell("A1")
            .value("Bold Text")
            .style({
                bold: true,
                italic: false,
                underline: true,
                strikethrough: false,
                fontSize: 14,
                fontFamily: "Arial",
                fontColor: "ff0000"  // RGB red
            });

        // Alignment
        sheet.cell("A2")
            .value("Centered")
            .style({
                horizontalAlignment: "center",
                verticalAlignment: "center",
                wrapText: true,
                indent: 2
            });

        // Fill colors
        sheet.cell("A3")
            .value("Colored Background")
            .style("fill", "ffff00"); // Yellow background

        // Pattern fill
        sheet.cell("A4")
            .value("Pattern")
            .style("fill", {
                type: "pattern",
                pattern: "darkDown",
                foreground: "ff0000",
                background: { theme: 3, tint: 0.4 }
            });

        // Borders
        sheet.cell("A5")
            .value("Borders")
            .style("border", true); // Thin border all sides

        sheet.cell("A6")
            .value("Custom Borders")
            .style("border", {
                top: { style: "thin", color: "ff0000" },
                bottom: { style: "thick", color: "0000ff" },
                left: true,
                right: "medium"
            });

        // Number formats
        sheet.cell("B1").value(1234.56).style("numberFormat", "#,##0.00");
        sheet.cell("B2").value(0.85).style("numberFormat", "0.00%");
        sheet.cell("B3").value(42788).style("numberFormat", "mm/dd/yyyy");

        // Row and column styles (applies to all cells)
        sheet.row(10).style("bold", true);
        sheet.column("C").style({ italic: true, fill: "e0e0e0" });

        // Set row height and column width
        sheet.row(1).height(30);
        sheet.column("A").width(20);

        return workbook.toFileAsync("./styles.xlsx");
    })
    .catch(err => console.error(err));
```

### Sheet Management

Add, rename, move, delete, and configure sheets.

```javascript
XlsxPopulate.fromBlankAsync()
    .then(workbook => {
        // Get sheet by name or index
        const sheet1 = workbook.sheet("Sheet1");
        const firstSheet = workbook.sheet(0);

        // Get all sheets
        const allSheets = workbook.sheets();
        console.log("Sheet count:", allSheets.length);

        // Add new sheets
        const newSheet1 = workbook.addSheet("Sales");
        const newSheet2 = workbook.addSheet("Data", 0); // Add at index 0
        const newSheet3 = workbook.addSheet("Summary", "Sales"); // Add before Sales

        // Rename sheet
        sheet1.name("Renamed Sheet");

        // Move sheet
        workbook.moveSheet("Sales", 0); // Move to beginning
        workbook.moveSheet("Data", "Summary"); // Move before Summary

        // Delete sheet
        workbook.deleteSheet("Data");
        // Or: workbook.sheet("Data").delete();

        // Set active sheet
        workbook.activeSheet("Sales");
        sheet1.active(true);

        // Hide/unhide sheet
        sheet1.hidden(true); // Hide
        sheet1.hidden(false); // Show
        sheet1.hidden("very"); // Very hidden (not accessible via UI)

        // Sheet properties
        sheet1.tabColor("ff0000"); // Red tab
        sheet1.tabSelected(true);
        sheet1.gridLinesVisible(false);
        sheet1.rightToLeft(true); // For RTL languages

        // Clone sheet
        const clonedSheet = workbook.cloneSheet(sheet1, "Sheet Copy");

        return workbook.toFileAsync("./sheets.xlsx");
    })
    .catch(err => console.error(err));
```

### Formulas

Set and retrieve cell formulas.

```javascript
XlsxPopulate.fromBlankAsync()
    .then(workbook => {
        const sheet = workbook.sheet(0);

        // Set data
        sheet.cell("A1").value(10);
        sheet.cell("A2").value(20);
        sheet.cell("A3").value(30);

        // Set formulas
        sheet.cell("A4").formula("SUM(A1:A3)");
        sheet.cell("B1").formula("A1*2");
        sheet.cell("B2").formula("IF(A2>15,\"High\",\"Low\")");
        sheet.cell("B3").formula("AVERAGE(A1:A3)");

        // Shared formula across range
        sheet.range("C1:C3").formula("A1+B1");

        // Get formula
        const formula = sheet.cell("A4").formula();
        console.log("Formula:", formula); // "SUM(A1:A3)"

        // Note: Excel stores calculated values, but xlsx-populate
        // does not recalculate formulas
        const value = sheet.cell("A4").value();
        console.log("Stored value:", value); // Value from last Excel calculation

        return workbook.toFileAsync("./formulas.xlsx");
    })
    .catch(err => console.error(err));
```

### Rich Text

Create and manipulate rich text with multiple formatting styles in one cell.

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

XlsxPopulate.fromBlankAsync()
    .then(workbook => {
        const sheet = workbook.sheet(0);

        // Create rich text
        const richText = new XlsxPopulate.RichText();
        richText
            .add("Hello ", { bold: true, fontColor: "ff0000" })
            .add("World", { italic: true, fontSize: 14 })
            .add("!", { underline: true });

        sheet.cell("A1").value(richText);

        // Add text with line breaks (automatically enables wrapText)
        sheet.cell("A2").value()
            .add("Line 1\n", { bold: true })
            .add("Line 2\n", { italic: true })
            .add("Line 3", { fontSize: 12 });

        // Read rich text
        const cellValue = sheet.cell("A1").value();
        if (cellValue instanceof XlsxPopulate.RichText) {
            console.log("Concatenated text:", cellValue.text());
            console.log("Fragment count:", cellValue.length);

            // Access fragments
            for (let i = 0; i < cellValue.length; i++) {
                const fragment = cellValue.get(i);
                console.log("Fragment:", fragment.value());
                console.log("Bold:", fragment.style("bold"));
            }
        }

        // Modify existing rich text
        const rt = sheet.cell("A1").value();
        rt.get(0).style("fontSize", 20);
        rt.add(" Extra", { fontColor: "0000ff" });
        rt.remove(1); // Remove second fragment
        rt.clear(); // Clear all

        return workbook.toFileAsync("./richtext.xlsx");
    })
    .catch(err => console.error(err));
```

### Date Handling

Work with dates using Excel's numeric date format.

```javascript
XlsxPopulate.fromBlankAsync()
    .then(workbook => {
        const sheet = workbook.sheet(0);

        // Set date value and format
        const date = new Date(2024, 0, 15); // January 15, 2024
        sheet.cell("A1")
            .value(date)
            .style("numberFormat", "mm/dd/yyyy");

        sheet.cell("A2")
            .value(new Date())
            .style("numberFormat", "dddd, mmmm dd, yyyy");

        sheet.cell("A3")
            .value(new Date())
            .style("numberFormat", "h:mm:ss AM/PM");

        // Convert between dates and Excel numbers
        const excelNumber = XlsxPopulate.dateToNumber(new Date(2024, 0, 1));
        console.log("Excel number:", excelNumber); // 45292

        const dateFromNumber = XlsxPopulate.numberToDate(45292);
        console.log("Date:", dateFromNumber); // Mon Jan 01 2024

        // Read date from cell (returns number)
        const cellValue = sheet.cell("A1").value();
        if (typeof cellValue === "number") {
            const date = XlsxPopulate.numberToDate(cellValue);
            console.log("Cell date:", date);
        }

        return workbook.toFileAsync("./dates.xlsx");
    })
    .catch(err => console.error(err));
```

### Hyperlinks

Add hyperlinks to cells for external URLs, emails, and internal references.

```javascript
XlsxPopulate.fromBlankAsync()
    .then(workbook => {
        const sheet = workbook.sheet(0);

        // External URL
        sheet.cell("A1")
            .value("Visit Website")
            .style({ fontColor: "0563c1", underline: true })
            .hyperlink("https://www.example.com");

        // URL with tooltip
        sheet.cell("A2")
            .value("Google")
            .style({ fontColor: "0563c1", underline: true })
            .hyperlink({
                hyperlink: "https://www.google.com",
                tooltip: "Search Engine"
            });

        // Email link
        sheet.cell("A3")
            .value("Send Email")
            .hyperlink({
                email: "user@example.com",
                emailSubject: "Hello from Excel"
            });

        // Internal link to another cell
        sheet.cell("A4")
            .value("Go to Sheet2")
            .hyperlink("Sheet2!A1");

        // Internal link using cell reference
        const targetCell = workbook.sheet("Sheet1").cell("Z100");
        sheet.cell("A5")
            .value("Jump to Z100")
            .hyperlink(targetCell);

        // Get hyperlink
        const link = sheet.cell("A1").hyperlink();
        console.log("Hyperlink:", link);

        // Clear hyperlink
        sheet.cell("A1").hyperlink(undefined);

        return workbook.toFileAsync("./hyperlinks.xlsx");
    })
    .catch(err => console.error(err));
```

### Data Validation

Add dropdown lists and validation rules to cells.

```javascript
XlsxPopulate.fromBlankAsync()
    .then(workbook => {
        const sheet = workbook.sheet(0);

        // Simple dropdown list
        sheet.cell("A1").dataValidation("Item1,Item2,Item3,Item4");

        // List from range
        sheet.cell("A2").dataValidation("$B$1:$B$5");

        // Full data validation options
        sheet.cell("A3").dataValidation({
            type: 'list',
            allowBlank: true,
            showInputMessage: true,
            prompt: "Please select an item",
            promptTitle: "Select Item",
            showErrorMessage: true,
            error: "Invalid selection",
            errorTitle: "Error",
            formula1: 'Item1,Item2,Item3'
        });

        // Range validation
        sheet.range("A5:A10").dataValidation({
            type: 'whole',
            operator: 'between',
            formula1: '1',
            formula2: '100',
            showErrorMessage: true,
            errorTitle: 'Invalid Number',
            error: 'Please enter a number between 1 and 100'
        });

        // Get validation
        const validation = sheet.cell("A1").dataValidation();
        console.log("Validation:", validation);

        // Clear validation
        sheet.cell("A1").dataValidation(null);

        return workbook.toFileAsync("./validation.xlsx");
    })
    .catch(err => console.error(err));
```

### Find and Replace

Search and replace text in cells across sheets or workbooks.

```javascript
XlsxPopulate.fromFileAsync("./data.xlsx")
    .then(workbook => {
        // Find in entire workbook
        const matches = workbook.find("foo");
        console.log("Found matches:", matches.length);

        // Find and replace in workbook
        workbook.find("foo", "bar");

        // Find in specific sheet
        const sheetMatches = workbook.sheet("Sheet1").find("test");

        // Find with RegExp
        const regexMatches = workbook.find(/\d{3}-\d{3}-\d{4}/);

        // Replace with callback
        workbook.find(/price: \$(\d+)/, match => {
            const price = parseInt(match[1]);
            return `price: $${price * 1.1}`; // Increase by 10%
        });

        // Case-sensitive search with RegExp
        workbook.find(/ERROR/g, "WARNING");

        // Check if specific cell matches
        const cellMatches = workbook.sheet(0).cell("A1").find("hello");
        console.log("Cell A1 matches:", cellMatches);

        return workbook.toFileAsync("./replaced.xlsx");
    })
    .catch(err => console.error(err));
```

### Encryption

Protect workbooks with passwords.

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

// Load password-protected workbook
XlsxPopulate.fromFileAsync("./protected.xlsx", { password: "secret123" })
    .then(workbook => {
        console.log("Workbook decrypted successfully");

        // Modify workbook
        workbook.sheet(0).cell("A1").value("Updated");

        // Save with new password
        return workbook.toFileAsync("./output.xlsx", { password: "newpassword456" });
    })
    .catch(err => console.error(err));

// Save workbook with encryption
XlsxPopulate.fromBlankAsync()
    .then(workbook => {
        workbook.sheet(0).cell("A1").value("Confidential Data");

        // Save with password
        return workbook.toFileAsync("./encrypted.xlsx", { password: "mysecret" });
    })
    .catch(err => console.error(err));

// Save unencrypted (even if loaded with password)
XlsxPopulate.fromFileAsync("./protected.xlsx", { password: "secret123" })
    .then(workbook => {
        // Save without password (unencrypted)
        return workbook.toFileAsync("./unencrypted.xlsx");
    })
    .catch(err => console.error(err));
```

### Defined Names

Work with named ranges and defined names.

```javascript
XlsxPopulate.fromBlankAsync()
    .then(workbook => {
        const sheet = workbook.sheet(0);

        // Set data
        sheet.range("A1:A10").value([[1], [2], [3], [4], [5], [6], [7], [8], [9], [10]]);

        // Create workbook-scoped defined name
        workbook.definedName("MyRange", sheet.range("A1:A10"));
        workbook.definedName("TaxRate", "0.08");

        // Create sheet-scoped defined name
        sheet.definedName("SheetData", sheet.range("A1:B20"));

        // Use defined name in formula
        sheet.cell("B1").formula("SUM(MyRange)");

        // Get defined name value
        const range = workbook.definedName("MyRange");
        console.log("Range address:", range.address());

        const taxRate = workbook.definedName("TaxRate");
        console.log("Tax rate:", taxRate);

        // Delete defined name
        workbook.definedName("OldName", null);

        return workbook.toFileAsync("./definednames.xlsx");
    })
    .catch(err => console.error(err));
```

### Page Setup and Printing

Configure page margins, print options, and panes.

```javascript
XlsxPopulate.fromBlankAsync()
    .then(workbook => {
        const sheet = workbook.sheet(0);

        // Print options
        sheet.printOptions("headings", true); // Print row/column headers
        sheet.printOptions("horizontalCentered", true);
        sheet.printOptions("verticalCentered", true);
        sheet.printGridLines(true); // Print grid lines

        // Page margins (in inches)
        sheet.pageMarginsPreset("normal"); // normal, wide, narrow
        sheet.pageMargins("top", 1.0);
        sheet.pageMargins("bottom", 1.0);
        sheet.pageMargins("left", 0.75);
        sheet.pageMargins("right", 0.75);
        sheet.pageMargins("header", 0.5);
        sheet.pageMargins("footer", 0.5);

        // Freeze panes
        sheet.freezePanes(1, 1); // Freeze first row and column
        sheet.freezePanes("B2"); // Freeze top-left to B2

        // Split panes
        sheet.splitPanes(2000, 1000); // Horizontal and vertical split

        // Reset panes
        sheet.resetPanes();

        // Page breaks
        sheet.row(20).addPageBreak(); // Horizontal page break
        sheet.column("E").addPageBreak(); // Vertical page break

        return workbook.toFileAsync("./pagesetup.xlsx");
    })
    .catch(err => console.error(err));
```

### Output Methods

Generate workbook output in various formats.

```javascript
XlsxPopulate.fromBlankAsync()
    .then(workbook => {
        workbook.sheet(0).cell("A1").value("Export Test");

        // Save to file (Node.js only)
        return workbook.toFileAsync("./output.xlsx");
    })
    .then(() => {
        console.log("File saved");
    })
    .catch(err => console.error(err));

// Get output as Buffer (Node.js)
XlsxPopulate.fromBlankAsync()
    .then(workbook => {
        workbook.sheet(0).cell("A1").value("Buffer Test");
        return workbook.outputAsync();
    })
    .then(buffer => {
        console.log("Buffer size:", buffer.length);
        // Send buffer over network, etc.
    })
    .catch(err => console.error(err));

// Get output as base64
XlsxPopulate.fromBlankAsync()
    .then(workbook => {
        return workbook.outputAsync("base64");
    })
    .then(base64 => {
        console.log("Base64 length:", base64.length);
    })
    .catch(err => console.error(err));

// Get output as Blob (Browser)
XlsxPopulate.fromBlankAsync()
    .then(workbook => {
        return workbook.outputAsync("blob");
    })
    .then(blob => {
        // Create download link in browser
        const url = window.URL.createObjectURL(blob);
        const a = document.createElement("a");
        a.href = url;
        a.download = "output.xlsx";
        a.click();
        window.URL.revokeObjectURL(url);
    })
    .catch(err => console.error(err));

// Output with password
XlsxPopulate.fromBlankAsync()
    .then(workbook => {
        return workbook.outputAsync({
            type: "nodebuffer",
            password: "protected"
        });
    })
    .then(buffer => {
        console.log("Encrypted buffer created");
    })
    .catch(err => console.error(err));
```

## Summary

xlsx-populate is a powerful and flexible library for working with Excel XLSX files in JavaScript. Its primary use cases include generating dynamic Excel reports from application data, populating pre-designed Excel templates with data, parsing and extracting data from existing Excel files, automating Excel-based data processing workflows, and creating data export functionality in web applications. The library is particularly valuable when you need to work with complex Excel files that contain sophisticated formatting, formulas, or other advanced features that must be preserved.

The library's architecture is designed around method chaining, making it intuitive to build up complex operations step by step. Integration is straightforward in both Node.js and browser environments, with support for various input and output formats including files, buffers, blobs, and base64 strings. The promise-based API makes it easy to compose asynchronous operations, and the library's approach of directly manipulating XML rather than fully deserializing the workbook ensures that features not explicitly exposed in the API are still preserved when files are saved. Whether you're building a reporting system, creating a data import/export feature, or automating Excel-based workflows, xlsx-populate provides a comprehensive solution with excellent documentation and a consistent, easy-to-use API.