Created
February 16, 2019 20:56
-
-
Save stockhuman/a478e1b70979f52a9455818ca7016eec to your computer and use it in GitHub Desktop.
Manipulate XLSX spreadsheets with node
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
const xlsx = require('xlsx') // install dependencies such as this with 'npm install' | |
let fileNeedle // declares the file we're extracting terms from | |
let fileHaystack // the file we will search in with file_a's terms | |
let needles = [] // strings we're finding out and searching with | |
let output = [] // placeholder output | |
// USAGE: | |
// pass files as arguments like $ node jizz.js "file_a" "file_b" | |
process.argv.forEach((arg, i) => { // iterate over variables | |
if (i == 2) { fileNeedle = arg } // argument after jizz.js is the terms file | |
if (i == 3) { fileHaystack = arg } // argument after terms file is the haystack file | |
}) | |
// read our first file | |
let wkbkNeedle = xlsx.readFile(fileNeedle) | |
wkbkNeedle.SheetNames.forEach(sheetName => { // in each sheet... | |
// convert sheet into JSON rows, this is our row array | |
const rows = xlsx.utils.sheet_to_row_object_array(wkbkNeedle.Sheets[sheetName]) | |
// for each row, alias the data as 'patient' | |
rows.forEach(row => { | |
// if column exists by this name | |
if (row['CGID']) { | |
let data = row['CGID'] | |
needles.push(data) // data from this cell pushed into needles array | |
console.log(Data in row -> ${data}) | |
} | |
}) | |
}) | |
console.log('needles done!') | |
wkbkNeedle = [] // dealloc memory | |
// read the file we're looking into | |
const wkbkHaystack = xlsx.readFile(fileHaystack) | |
wkbkHaystack.SheetNames.forEach(sheetName => { | |
const rows = xlsx.utils.sheet_to_row_object_array(wkbkHaystack.Sheets[sheetName]) | |
rows.forEach(row => { | |
// if column exists by this name | |
if (row['Composite Element REF']) { | |
let data = row['Composite Element REF'] // data from this cell | |
for (let i = 0; i < needles.length; i++) { | |
if (data.includes(needles[i])) { | |
// print out corresponding CE REF value | |
console.log(data) | |
// collect data and add to put put from cells | |
let obj = { | |
"Composite Element REF": row['Composite Element REF'], | |
"Gene Symbol": row['Gene_Symbol'], | |
"Chromosome": row['Chromosome'], | |
"Gene Type": row['Gene_Type'], | |
"CpG type": row ['Feature_Type'], | |
} | |
output.push(obj) | |
console.log(obj) | |
// exit loop | |
return | |
} | |
} | |
} | |
}) | |
}) | |
const outSheet = xlsx.utils.json_to_sheet(output) | |
console.log('data ready for export') | |
// create workbook and export | |
const wb = xlsx.utils.book_new(); | |
xlsx.utils.book_append_sheet(wb, outSheet, 'Dooters'); | |
xlsx.writeFile(wb, "dooters.xlsx") |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment