Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Using XLSX library
// reading workbook from binary contents
var workbook = XLSX.read(excelBinaryContents, {type: 'binary'});
// retrieve properties of workbook (will be needed later)
var wbProps = workbook.Workbook.WBProps;
// get any particular worksheet by name
var worksheet = workbook.Sheets['Work Sheet Name'];
// get any cell
var cell = wrkSheet['A1'];
// reading number column could look as follows:
var parsed = parseFloat(cell.v);
// the most interesting is retrieving dates from Excel
var momentDate = this.parseExcelDate(cell.v, wbProps);
// and the function itself
parseExcelDate: function (dateValue, wbProps) {
if(dateValue) {
// it is a string, but it really represents a number and not a date
if(typeof dateValue === 'string' && /^\d+$/.test(dateValue)) {
dateValue = parseFloat(dateValue);
}
if(typeof dateValue === 'number') {
var dt = XLSX.SSF.parse_date_code(dateValue, {date1904: wbProps.date1904 === '1'});
// new Date(2015, 9, 18); // 18th October(!) 2015 in @JavaScript
var monthToJs = dt.m - 1;
return moment(new Date(dt.y, monthToJs, dt.d));
}
// else assume a string representing a date
// we use few allowed formats, but explicitly parse not strictly
var formats = ['YYYY-MM-DD', 'DD-MM-YYYY', 'MM/DD/YYYY'];
return moment(dateValue, formats, false);
}
return null;
},
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment