Create empty Workbook unsing NodeJS

[arthurspa]

I've already installed js-xlsx with:

npm install xlsx

How do I create an empty Workbook using nodejs?

Example:

XLSX = require('xlsx');
var wb =  new XLSX.Workbook(); // This won't work.

I want to create a new Workbook to load data from a database and then save it.

Thanks.

[rodw]

Hi @arthurspa, just picked up js-xlsx this evening so there may well be a better answer, but following the example at http://sheetjs.com/demos/writexlsx.html I've noticed that something like:

var wb = { SheetNames:[], Sheets:{} };

seems to be a sufficient to initialize a Workbook object.

That is, any object (map) with a SheetNames array and Sheets map can (when SheetNames and Sheets are correctly populated) be saved as described at https://github.com/SheetJS/js-xlsx#writing-workbooks.

[waylonflinn]

I ran into this too. So, I made a module to make it easier to create new workbooks and do simple editing of existing workbooks.

https://github.com/waylonflinn/xlsx-workbook

Here's a snippet from the docs:

// the Workbook object gives you more control and stores multiple sheets 
var Workbook = require('xlsx-workbook').Workbook;
 
var workbook = new Workbook();
 
var sales = workbook.add("Sales");
var costs = workbook.add("Costs");
 
sales[0][0] = 304.50;
sales[1][0] = 159.24;
sales[2][0] = 493.38;
 
costs[0][0] = 102.50;
costs[1][0] = 59.14;
costs[2][0] = 273.32;
 
// automatically appends the '.xlsx' extension 
workbook.save("Revenue-Summary");

[SheetJSDev]

@rodw has the right idea: the only requirements for a workbook are the SheetNames string array and Sheets object.

@waylonflinn nice work! Definitely add a note in the wiki. Also, consider adding helpers to insert and remove cells/rows/columns -- #352 is a good starting point.

[SheetJSDev]

@arthurspa https://github.com/SheetJS/js-xlsx/blob/master/tests/write.js starts from scratch and generates files.

[SheetJSDev]

@FrankBirik the syntax x[y] allows dynamic key access for objects and arrays. This is valid JS:

var x = {y:3};
console.log(x["y"]); // will print out 3

[FrankBirik]

Yeah I realize my mistake yesterday, thank you for responding so quickly
Very appreciated!

[LilSebastian5000]

@SheetJSDev Taking a look at index.d.ts, 'utils.book_new();" and "utils.book_append_sheet();" do not exist, despite being in the referenced "write.js" test file. What are the alternatives?

[SheetJSDev]

@ryanpm40 Good catch! it's missing in the types definition but is present in the library: https://github.com/SheetJS/js-xlsx/blob/master/bits/95_api.js#L30

We'll update the type definitions in the next release.

The functions themselves are simple enough that you could do it manually:

/* simple blank workbook object */
utils.book_new = function()/*:Workbook*/ {
	return { SheetNames: [], Sheets: {} };
};

/* add a worksheet to the end of a given workbook */
utils.book_append_sheet = function(wb/*:Workbook*/, ws/*:Worksheet*/, name/*:?string*/) {
	/* ... some error checking ... */
	wb.SheetNames.push(name);
	wb.Sheets[name] = ws;
};

[LilSebastian5000]

@SheetJSDev Awesome, thanks dev! I'm still having difficulty creating a workbook in Angular 4- Is there a way to set a WorkBook object?

If I just set a variable to "{ SheetNames:[], Sheets:{} };", then I'm thrown an error "Property 'Props' does not exist on type '{ SheetNames: any[]; Sheets: {}; }'." when trying to set up the properties.

[SheetJSDev]

The definition declares it as optional: https://github.com/SheetJS/js-xlsx/blob/master/types/index.d.ts#L216

You can set Props to an empty object {}

[davidwiltonws]

Just adding to this because it's the first item that comes up on google. There is a utility class:

const wb = XLSX.utils.book_new();

Or in TS:

const wb: xls.WorkBook = XLSX.utils.book_new();