Last active
January 1, 2017 23:11
-
-
Save optilude/2aa1d1ccb8c3398eb0afce32226ac8eb to your computer and use it in GitHub Desktop.
Helper for JS-XLSX (https://github.com/sheetjs/js-xlsx) to read tables with named columns into a data structure that is easy to work with.
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
function doSomething(workbook) { | |
let { sheet, range } = findSheet(workbook, "Invoices"); | |
if(sheet === null) { | |
return null; | |
} | |
let { columns, firstRow } = findTable(sheet, range, { | |
name: "Person name", | |
company: "Company name", | |
date: "Date", | |
amount: "Amount" | |
}); | |
if(firstRow === null) { | |
return null; | |
} | |
const data = readTable(sheet, range, columns, firstRow, (row) => false); | |
data.forEach((record, index) => { | |
// to domsething with `record`, which will have keys `name`, `company`, `date` and `amount` | |
// to get a real date... | |
let date = convertExcelDate(record.date); | |
}); | |
} |
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
import _ from 'lodash'; | |
import XLSX from 'xlsx'; | |
const dc = XLSX.utils.decode_cell, | |
ec = (r, c) => { return XLSX.utils.encode_cell({r: r, c: c}); }; | |
/** | |
* Find a sheet in the workbook by name, and return an object with keys | |
* `sheet` and `range`, where `range` is an object describing the valid cells | |
* of the sheet, like `{min: {r: 1, c: 1}, max: {r: 5, c:5}}`. | |
*/ | |
export function findSheet(workbook, sheetName) { | |
let sheet = workbook.Sheets[sheetName], | |
range = {min: {r: 0, c: 0}, max: {r: 0, c: 0}}; | |
if(!sheet) { | |
return { sheet: null, range: null }; | |
} | |
// find size of the sheet | |
let ref = sheet['!ref']; | |
if(!ref && ref.indexOf(':') === -1) { | |
throw new Error("Malformed workbook - no !ref property"); | |
} | |
range.min = dc(ref.split(':')[0]); | |
range.max = dc(ref.split(':')[1]); | |
return { sheet, range }; | |
} | |
/** | |
* Find the start position of a table in the given sheet. `colMap` describes | |
* the table columns as an object with key prop -> column title. Returns an | |
* object with keys `columns` (maps prop -> 0-indexed column number) and | |
* `firstRow`, the number of the first row of the table (will be `null`) if the | |
* table was not found. | |
*/ | |
// colMap: prop -> col name | |
export function findTable(sheet, range, colMap) { | |
let firstRow = null, | |
colsToFind = _.keys(colMap).length, | |
// colmap lowercase title -> prop | |
colLookup = _.reduce(colMap, (m, v, k) => { m[_.isString(v)? v.toLowerCase() : v] = k; return m; }, {}), | |
// colmap props -> 0-indexed column | |
columns = _.reduce(colMap, (m, v, k) => { m[k] = null; return m; }, {}); | |
// Look for header row and extract columns | |
for(let r = range.min.r; r <= range.max.r - 1; ++r) { | |
let colsFound = 0; | |
for(let c = range.min.c; c <= range.max.c; ++c) { | |
let cell = sheet[ec(r, c)]; | |
if(cell && cell.v !== undefined) { | |
let prop = colLookup[cell.t === 's'? cell.v.toLowerCase() : cell.v]; | |
if(prop) { | |
columns[prop] = c; | |
++colsFound; | |
} | |
} | |
} | |
if(colsFound === colsToFind) { | |
firstRow = r + 1; | |
break; | |
} | |
} | |
return { columns, firstRow }; | |
} | |
/** | |
* Given the `cols` and `firstRow` as returned by `findTable()`, return a list | |
* of objects of all table values. Continues to the end of the sheet unless | |
* passed a function `stop` that takes a mapped row object as an argument and | |
* returns `true` for that row. | |
*/ | |
export function readTable(sheet, range, columns, firstRow, stop) { | |
let data = []; | |
for(let r = firstRow; r <= range.max.r; ++r) { | |
let row = _.reduce(columns, (m, c, k) => { | |
let cell = sheet[ec(r, c)]; | |
m[k] = cell? cell.v : null; | |
return m; | |
}, {}); | |
if(stop && stop(row)) { | |
break; | |
} | |
data.push(row); | |
} | |
return data; | |
} | |
/** | |
* Turn an Excel numeric date into a JavaScript Date object. | |
*/ | |
export function convertExcelDate(excelDate) { | |
return new Date((excelDate - (25569))*86400*1000); | |
} | |
/** | |
* Turn a JavaScript Date into an Excel numeric date | |
*/ | |
export function toExcelDate(jsDate) { | |
return Number((jsDate.getTime()/(1000*60*60*24)) + 25569) | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment