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 = ;
Create a Report
var sqlxlsx = ;
Define or Change Report Parameters
sqlxlsx;
Change an Individual Parameter
sqlxlsx
Create and Define
You can create and define a report with one or more parameters all at once, like this:
var 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;
Example
var Sqlxlsx = 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" {console;} {console;} { processstdout; } { processstdout; console; } { console; } ; sqlxlsx;
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) {}