database-js-xlsx

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."); } } })();