Skip to content

Instantly share code, notes, and snippets.

@stockhuman
Created February 16, 2019 20:56
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save stockhuman/a478e1b70979f52a9455818ca7016eec to your computer and use it in GitHub Desktop.
Save stockhuman/a478e1b70979f52a9455818ca7016eec to your computer and use it in GitHub Desktop.
Manipulate XLSX spreadsheets with node
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