Skip to content

Instantly share code, notes, and snippets.

@scott1028
Last active January 6, 2023 12:00
Show Gist options
  • Save scott1028/b26170522c6ad0dcf9b9548bb27357df to your computer and use it in GitHub Desktop.
Save scott1028/b26170522c6ad0dcf9b9548bb27357df to your computer and use it in GitHub Desktop.
help function for xlsx to resolve extra excel file blocking issue
const _ = require('lodash');
const xlsx = require('xlsx');
const sheet_to_json = (sheet, headerRowIndex = 1, map = v => v) => {
const filteredData = _.toPairs(sheet).filter(([, value]) => value.hasOwnProperty('w'));
const dataMap = _.fromPairs(filteredData);
const columnKeys = [];
let totalRows = 0;
filteredData.forEach(([key]) => {
const { columnKey, rowKey } = /(?<columnKey>[A-z]+)(?<rowKey>\d+)/.exec(key).groups;
if (!new Set(columnKeys).has(columnKey)) {
columnKeys.push(columnKey);
}
if (+rowKey > totalRows) {
totalRows = +rowKey;
}
});
columnKeys.sort();
const columnKeyNameMap = _.fromPairs(columnKeys.map(key => {
return [key, _.get(dataMap, `${key}${headerRowIndex}.w`)];
}));
const output = [];
for (let startRowIndex = 3; startRowIndex <= totalRows; startRowIndex++) {
const rowData = _.fromPairs(columnKeys.map(key => {
return [_.get(columnKeyNameMap, key), map(_.chain(dataMap).get(`${key}${startRowIndex}.w`, '').value())];
}));
output.push(rowData);
}
return { json: output, columns: Object.values(columnKeyNameMap) };
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment