Skip to content

Instantly share code, notes, and snippets.

@asimmittal
Last active Mar 28, 2018
Embed
What would you like to do?
A gist to turn my excel template into JSON
var Excel = require("exceljs");
var fs = require("fs");
function churnString(valString, dataType){
let valNum = parseFloat(valString);
let percentageTypes = [
'natWageGrowth', 'natEmpGrowth', 'natTurnoverRate', 'wgJobHolders', 'wgJobSwitchers', 'wgEntrants',
'wgValue', 'wgHolders', 'wgSwitchers', 'wgEntrants','empGrowth','turnover'
];
let suffix = '', prefix ='';
let isPct = (percentageTypes.indexOf(dataType) >= 0) ? true : false;
if(isPct){
//value is a percentage
suffix = '%';
prefix = '';
}
else {
//value is a level (dollar)
prefix = '$';
suffix = '';
}
if(isNaN(valNum)) {
return valString;
}
else{
valNum = (valNum * 100) / 100;
valNum = (isPct) ? (valNum * 100).toFixed(2): valNum;
let result = prefix + valNum + suffix;
return result;
}
}
function parseFile(infile, outfile){
let workbook = new Excel.Workbook();
let result = [];
console.log("\n**** JSONifying XLXS file ****\n");
console.log("--> filename:", infile);
workbook.xlsx.readFile(infile).then(function () {
let sheets = workbook._worksheets;
for(var i = 1; i <= sheets.length; i++){
try{
let sheet = sheets[i];
if(!sheet) break;
let worksheet = workbook.getWorksheet(sheet.id);
let sheetName = ((worksheet.name.split('-'))[1]).toLowerCase().trim();
let periods = [];
let columns = [];
let data = {};
console.log("----> processing ["+i+"]:", sheetName);
worksheet.eachRow({includeEmpty: false}, (_row, _rowNumber) => {
let row = _row.values;
let rowNum = _rowNumber - 1;
//let's get rid of the first two columns
row = row.slice(3);
let newRow = [];
//now get rid of all the empty values in the row
for (let k = 0; k < row.length; k++){
let col = (row[k]) ? ''+row[k] : '';
col = col.trim();
if(col.length > 0) newRow.push(col);
}
//now let's populate the fields
// 0th row > periods
// 1st row will contain duplicates. To get unique elements, get slice of row from 0 to (length of Jth row / number of periods)
// all other rows, the first col is the key, other items are the value
if (rowNum == 0){
periods = newRow.filter(function(item, pos) {
return newRow.indexOf(item) == pos;
});
//console.log("--> periods", '['+periods+']');
}
else if(rowNum == 1){
columns = newRow.slice(0, newRow.length / periods.length)
//console.log("---> cols", '['+columns+']');
}
else if(newRow.length > 0){
let key = newRow[0];
let val = newRow.slice(1);
val = val.map( i => churnString(i, key));
data[key] = val;
}
});
//now that we have periods, columns and data, create the object
//for this sheet
let sheetObject = {
type: sheetName,
periods: periods,
columns: columns,
data: data
};
result.push(sheetObject);
}
catch(e){
break;
}
}
let jsonString = JSON.stringify(result, null, 4);
fs.writeFileSync(outfile, jsonString);
console.log("\n*** mock data generated ***\n")
});
}
module.exports = parseFile;
/*************************************************************
* main: if this script is run this directly on its own
* node xlsToJson.js -i "input_path.xlsx" -o "output_path.json"
**************************************************************/
if(!module.parent){
//process command line args to get in and out paths
let inputIndex = process.argv.indexOf("-i") + 1;
let outputIndex = process.argv.indexOf("-o") + 1;
let inputPath = process.argv[inputIndex];
let outputPath = process.argv[outputIndex];
if(inputPath && outputPath){
let isInpValid = fs.existsSync(inputPath) && fs.lstatSync(inputPath).isFile();
let isOutValid = (outputPath.trim().length > 0) ? true : false;
if(isInpValid && isOutValid){
parseFile(inputPath, outputPath);
}
else console.error("Provide valid paths for input and output files");
}
else console.error("Check command line args. Help: node xlsToJson.js -i 'input_path.xlsx' -o 'output_path.json'");
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment