Created
February 29, 2016 10:43
-
-
Save andriybuday/b284c15f134d80667e95 to your computer and use it in GitHub Desktop.
Using XLSX library
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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