ut-xlsx v0.0.1

UT xlsx

ut-xlsx modules is designed for parsing MS Excel files and write the result to a json file. Final output of the process is a json file where each line is a parseable (via JSON.parse) string representing each row in MS Excel file. In order to use it just pass an xlsx file and some optional parameters like column definitions, working directory (used to unzip excel file and storing some temporary files required by parsing process (all temp data is removed after parse ends)) and result file name.

Basic usage:

var utXlsx = require('ut-xlsx');
var path = require('path');
var parser = new utXlsx(path.join(__dirname, 'testFile.xlsx'));
parser.parse().then((parseSummary) => {
	console.log(parseSummary);
    // parseSummary contains
    // headers: []
    // dataFilePath: resultFilePath or 'sheetData.json' (default value)
    // validRows: number
    // errors: []
});

More complex usage:

var utXlsx = require('ut-xlsx');
var path = require('path');
var Joi = require('joi');
var columnDefinitions = [
    {
        columnName: 'colA', // 'colA' must be met in column headers
        mandatory: true,
        joiSchema: Joi.string().regex(/^\d+$/).required()
    },
    {
        columnName: 'colB',
        mandatory: false,
        joiSchema: Joi.string()
    }
];
var parser = new utXlsx({
        sourceFilePath: path.join(__dirname, 'testFile.xlsx'), 
        columnDefinitions: columnDefinitions,
        workDir: __dirname,
        resultFilePath: path.join(__dirname, 'credit-payments.json')
    });
parser.parse().then((parseSummary) => {
    console.log(parseSummary);
});

Note: mandatory option states that column should persist in excel file (column headers), otherwise file will be parsed and if there is no matching between non mandatory column definition and excel column header there will be no data for that column in the result file. joiSchema option will be applied to the parsed data, if validation is not successful then data will not be stored in final result file, and error will be logged as parse summary errors.

Some known limitations:

• Currently this module reads data only from first sheet in the excel file.
• No styling or formatting is applied to data just pure data is grabbed. For example if there is any formatting for a number cell to display value in format 0.00 in order to display 12344.56 while the actual value is 12344.555555 then within result file will be written 12344.555555.
• This module does not support merged columns (two or three columns with a single header).