Database-js XLSX Wrapper

1.0.6 · maintenance · verified Wed Apr 22

The `database-js-xlsx` package provides a robust `database-js` compatible interface for interacting with Microsoft Excel XLSX files, acting as a crucial bridge between SQL-like queries and spreadsheet data. It is built upon the `xlsx-populate` library for efficient in-memory spreadsheet manipulation and utilizes `node-sqlparser` to interpret SQL commands. Currently stable at version 1.0.6, its release cadence is tied to its underlying dependencies and the `database-js` ecosystem, rather than a fixed schedule. A key advantage of `database-js-xlsx` is its cross-platform compatibility, a significant improvement over Windows-specific drivers within the `database-js` family, such as `database-js-adodb`. Developers should note that the library works with an in-memory copy of the spreadsheet; all changes are buffered and written back to disk only when the connection is explicitly closed. This design means any external modifications to the file during an active connection will be overwritten. The SQL capabilities are limited, supporting SELECT, UPDATE, INSERT, and DELETE statements with functional WHERE clauses, but explicitly prohibiting JOINs and currently lacking support for GROUP BY. Furthermore, LIMIT and OFFSET operations are consolidated into a single `LIMIT [offset,]number` syntax, requiring developers to adapt their pagination strategies accordingly. This package is ideal for Node.js applications needing to perform basic CRUD operations on Excel data programmatically without complex setup or platform restrictions.

Common errors

Warnings

Install

Imports

Quickstart

Demonstrates connecting to an XLSX file, performing CRUD (Create, Read, Update, Delete) operations, and properly closing the connection using `database-js-xlsx`. It also includes handling for initial file creation and path resolution.

const Database = require('database-js');
const path = require('path');

// This import is crucial for the 'database-js-xlsx' driver to be available
require('database-js-xlsx');

(async () => {
    let connection, statement, rows;
    const filePath = path.join(__dirname, 'test.xlsx');
    // For a new file, 'test.xlsx' will be created on connection.close()
    connection = new Database(`database-js-xlsx:///${filePath}`);

    try {
        console.log("Connected to XLSX file. Initializing with sample data if new.");

        // Example: Create/Ensure Sheet1 has columns if it's a new file
        // Note: This operation may implicitly create Sheet1 and its headers if not present
        try {
            await connection.prepareStatement("CREATE TABLE IF NOT EXISTS Sheet1 (Name TEXT, City TEXT, State TEXT, Age INTEGER)");
        } catch (e) {
            // CREATE TABLE is not directly supported, but the first INSERT will define schema
            console.log("CREATE TABLE is not supported, proceeding with inserts to define schema.");
        }

        // Example: Insert data (if no data exists for 'Alice')
        console.log("\nInserting new data into Sheet1 (if not exists)...");
        statement = await connection.prepareStatement("INSERT OR IGNORE INTO Sheet1 (Name, City, State, Age) VALUES (?, ?, ?, ?)");
        await statement.query('Alice Smith', 'Anytown', 'CA', 25);
        await statement.query('Bob Johnson', 'Otherville', 'NY', 32);
        console.log("Sample data ensured.");

        // Example: Select data
        statement = await connection.prepareStatement("SELECT * FROM Sheet1 WHERE State = ?");
        rows = await statement.query('CA');
        console.log("\nQuery Results for 'CA':");
        console.log(rows);

        // Example: Update data
        console.log("\nUpdating Alice Smith's age...");
        statement = await connection.prepareStatement("UPDATE Sheet1 SET Age = ? WHERE Name = ?");
        await statement.query(26, 'Alice Smith');
        console.log("Alice Smith's age updated.");

        // Verify changes
        statement = await connection.prepareStatement("SELECT * FROM Sheet1 WHERE Name = ?");
        rows = await statement.query('Alice Smith');
        console.log("\nVerifying updated data for Alice Smith:");
        console.log(rows);

        // Example: Delete data
        console.log("\nDeleting Bob Johnson's data...");
        statement = await connection.prepareStatement("DELETE FROM Sheet1 WHERE Name = ?");
        await statement.query('Bob Johnson');
        console.log("Bob Johnson's data deleted.");

    } catch (error) {
        console.error("An error occurred:", error);
    } finally {
        if (connection) {
            console.log(`\nClosing connection and saving changes to '${filePath}'...`);
            await connection.close();
            console.log("Connection closed and file saved.");
        }
    }
})();

view raw JSON →