Would you like to clone this notebook?

When you clone a notebook you are able to make changes without affecting the original notebook.

Cancel

Issue #610

node v6.17.1
version: master
endpointsharetweet
To generate a JS array-of-arrays, call `sheet_to_json`:
/* runkit boilerplate -- you will do something else in your code */ var GOT = require('got'); var data = (await GOT('https://cdn.rawgit.com/SheetJS/js-xlsx/65f1c7e5/demos/extendscript/sheetjs.xlsx?raw=true', {encoding:'binary'})).body; /* require XLSX */ var XLSX = require('xlsx'); /* read workbook */ var wb = XLSX.read(data, {type:'binary'}); /* generate array of arrays */ var js = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]], {raw:true, header:1});
Since this is a standard JS array, you can iterate the array to get your row arrays:
/* loop the rows */ js.forEach(function(row, idx) { console.log(idx, row); })
Let's change one of the values
/* change "A1" to 123 */ js[0][0] = 123; js;
To generate a worksheet object, use `aoa_to_sheet`:
/* Generate worksheet based on the array of arrays */ var new_ws = XLSX.utils.aoa_to_sheet(js); new_ws;
Now all we have to do is attach the new worksheet to the workbook. To overwrite the worksheet, just set `wb.Sheets[sheetname]`. To add a new worksheet, you also need to add the sheet name to the `wb.SheetNames` array. Let's overwrite:
/* attach the new worksheet to the workbook. assigning to the old name overwrites the old worksheet */ wb.Sheets[wb.SheetNames[0]] = new_ws;
Finally, let's save the workbook! If you want to download the actual file, in the output block click the dropdown and choose "Download", then you will see a button "Download as .zip". Rename the .zip file with a .xlsx extension.
/* generate the data - bookType refers to output file type - type refers to the output data type */ var output = XLSX.write(wb, {bookType:"xlsx", type:"buffer"});
You can manually verify that the data has changed, but we can do something even more clever: Just re-parse the file!
var new_wb = XLSX.read(output, {type:'buffer'}); var rt_js = XLSX.utils.sheet_to_json(new_wb.Sheets[new_wb.SheetNames[0]], {raw:true, header:1});
Loading…

no comments

    sign in to comment