7

I'm currently trying to export an array to an excel file with cell formatting.

I'm starting off with this code here:

https://github.com/SheetJS/js-xlsx/blob/master/tests/write.js

But the problem is that whenever I'm trying to export it (save the file as an xlsx file) this is the error that shows up in the console:

Uncaught TypeError: Cannot read property 'writeFileSync' of undefined    xlsx.js:5182 
writeSync                 xlsx.js:5182 
writeFileSync             xlsx.js:5173 
process_xlsx              Test.html:379 
reader.onload             Test.html:438

The last 2 lines are basically the part of the code which says

XLSX.writeFile(wb, 'sheetjs.xlsx');

I know wb is not undefined as if I try and do console.log of it, the excel spreadsheet shows up properly :|

Can someone help me with this? I'm also trying to have each cell have a different formatting (IE different color/bolded/filled/etc)

Johnti
  • 133
  • 2
  • 2
  • 8

1 Answers1

3

You base your code on a node.js test. The documentation states:

Writing Workbooks

For writing, the first step is to generate output data. The helper functions write and writeFile will produce the data in various formats suitable for dissemination. The second step is to actual share the data with the end point. Assuming workbook is a workbook object:

nodejs write to file:

/* output format determined by filename */
XLSX.writeFile(workbook, 'out.xlsx');
/* at this point, out.xlsx is a file that you can distribute */

write to binary string (using FileSaver.js):

/* bookType can be 'xlsx' or 'xlsm' or 'xlsb' */
var wopts = { bookType:'xlsx', bookSST:false, type:'binary' };

var wbout = XLSX.write(workbook,wopts);

function s2ab(s) {
  var buf = new ArrayBuffer(s.length);
  var view = new Uint8Array(buf);
  for (var i=0; i!=s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
  return buf;
}

/* the saveAs call downloads a file on the local machine */
saveAs(new Blob([s2ab(wbout)],{type:""}), "test.xlsx")

So to sum up: You try to use node.js internal functions in the browser, which fails. If you try to follow the seconds approach ( XLSX.write() instead of XLSX.writeFile()), you should be fine.

Sirko
  • 72,589
  • 19
  • 149
  • 183
  • Oh wow thank you so much. I feel like a dumbass now :( (still new to JS so please excuse me haha). Do you happen to know how I would be able to change the cell formatting for each individual cell? – Johnti Dec 18 '14 at 15:31
  • In the [cell object, you have plenty of properties](https://github.com/SheetJS/js-xlsx/#user-content-cell-object), e.g., `r` for "rich text encoding". This is probably your start. Maybe try to create an Excel sheet with the formating you want and load it using that lib. Then you can have a look at how these styles are encoded. – Sirko Dec 18 '14 at 15:44
  • I've been taking a look at this but I still can't make it work. Can you take 1 last look at the js-xlsx for me and help me figure this out? All I need is to be able to bold, change text color, and fill color for each cell. – Johnti Dec 18 '14 at 17:22
  • @Johnti After some testing I have doubts, that this library parses all style information. I was just able to extract some information for background color using [this code ](http://pastebin.com/bpi8CNtL) and a test file with the respective formatting in cells A1 to A4. – Sirko Dec 18 '14 at 18:34
  • 1
    I'm not looking to parse what style's in the excel file. I'm looking to set the style of the cell which is output'd. I'm not on GH. – Johnti Dec 18 '14 at 18:58
  • I'm aware, that you do not want to parse, but output. But this lib has no real documentation. So if you want to specify sth. the best way would be to give it an example and learn from that. I tried that and failed. That's all I'm saying. Maybe you should keep a look, if there will be answers on that github post as it is basically the same as your request. – Sirko Dec 18 '14 at 19:09
  • Ah ok thanks. I was hoping people on StackOverflow can somehow help me with this. Thanks for all your help so far! I'll keep an eye on that GH page – Johnti Dec 18 '14 at 20:04