README

sqlxlsx version 1.0.5

Create Excel files from SQL queries using the node-oracledb database driver and the exceljs streaming XLSX Writer.

About

Currently, sqlxlsx only works for Oracle databases but eventually I would like to make it compatible with more. Such as MySQL.

Prerequisites

There are several prerequisites needed to both compile and run the Oracle database driver. Please visit the node-oracledb INSTALL.md page for more information.

Installation

Run npm install sqlxlsx to install from the NPM registry.

Interface

var Sqlxlsx = require('sqlxlsx');

Create a Report

var sqlxlsx = new Sqlxlsx();

Define or Change Report Parameters

sqlxlsx.update_cfg({
    sql: 'select somedata from sometable',
    oracledb_cfg: {
        user: 'username',
        pass: 'password',
        connectString: '192.168.1.254/YOGA' // Easy Connect syntax
    },
    oracledb_args: [],
    numRows: 100, // This is the number of rows to pull/stream at a time
    exceljs_wsName: 'worksheet name',
    exceljs_options: {
        filename: "./report.xlsx", // specifies the path to a file to write the XLSX workbook to
        useStyles: false,
        useSharedStrings: true,
        creator: "Name"
    }
});

Change an Individual Parameter

sqlxlsx.update_cfg({exceljs_wsName: 'new name'};

Create and Define

You can create and define a report with one or more parameters all at once, like this:

var sqlxlsx = new Sqlxlsx({
    sql: 'select somedata from sometable',
    oracledb_cfg: {
        user: 'username',
        pass: 'password',
        connectString: '192.168.1.254/YOGA' // Easy Connect syntax
    },
    oracledb_args: [],
    numRows: 100, // This is the number of rows to pull/stream at a time
    exceljs_wsName: 'worksheet name',
    exceljs_options: {
        filename: "./report.xlsx", // specifies the path to a file to write the XLSX workbook to
        useStyles: false,
        useSharedStrings: true,
        creator: "Name"
    }
});

Run a Report

This runs and saves the report to a file.

sqlxlsx.run(function(err) {
    console.log('report complete!');
});

Example

var Sqlxlsx = require('sqlxlsx'),
    sqlxlsx = new Sqlxlsx({
        sql: 'select somedata from sometable',
        oracledb_cfg: {
            user: 'username',
            pass: 'password',
            connectString: '192.168.1.254/YOGA' // Easy Connect syntax
        },
        oracledb_args: [],
        numRows: 100, // This is the number of rows to pull/stream at a time
        exceljs_wsName: 'worksheet name',
        exceljs_options: {
            filename: "report.xlsx", // specifies the path to a file to write the XLSX workbook to
            useStyles: false,
            useSharedStrings: true,
            creator: "Name"
        },
        afterConnect: function() {console.log('Oracle connection made.');},
        afterDisconnect: function() {console.log('Oracle connection released.');},
        afterEachFetch: function(row_count) {
            process.stdout.write("Streaming data (" + row_count + " rows) to file... \x1B[0G");
        },
        afterFetch: function(row_count) {
            process.stdout.clearLine();
            console.log('Downloaded ' + row_count + ' rows to an Excel file. ');
        },
        afterExecute: function() {
            console.log('Query executed. ');
        }
    });

sqlxlsx.run(function(err) {console.log('report complete!');});

Parameter Definitions

oracledb_cfg: see node-oracledb getConnection(): connAttrs connAttrs
sql: The SQL string that is executed. The SQL string may contain bind parameters.
oracledb_args: see node-oracledb execute(): Bind Parameters
numRows: number of rows to stream from each call to the database driver
exceljs_wsName: the name to give the Worksheet
exceljs_options: same as options defined within ExcelJS.stream.xlsx.WorkbookWriter namespace.
afterConnect: optional function to call after connecting to the database function() {}
afterDisconnect: optional function to call after disconnecting from the database function() {}
afterEachFetch: optional function to call after each fetch function(row_count) {}
afterFetch: optional function to call after last fetch function(row_count) {}
afterExecute: optional function to call once sql successfully executes function(row_count) {}
callback: define a function to call once complete function(err) {}

Usage no npm install needed!