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});
no comments
sign in to comment