Exporting an array to excel file with cell formatting

Question

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)

If you use that testfile as a basis, make sure to properly include all dependencies — Sirko
– Sirko, Commented Dec 18, 2014 at 14:12
For this, isn't the dependencies just xlsx.js and jszip.js? I also included jquery.js in case — Johnti
– Johnti, Commented Dec 18, 2014 at 14:19
Right now, just as a test, this is what I have: notepad.cc/share/AOFCw4Elv8 — Johnti
– Johnti, Commented Dec 18, 2014 at 14:26

Sirko · Accepted Answer · 2014-12-18 14:54:37Z

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.

answered Dec 18, 2014 at 14:54

Sirko

74.3k19 gold badges157 silver badges194 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Johnti Over a year ago

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?

Sirko Over a year ago

In the cell object, you have plenty of properties, 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.

Johnti Over a year ago

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.

Sirko Over a year ago

@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 ](pastebin.com/bpi8CNtL) and a test file with the respective formatting in cells A1 to A4.

Johnti Over a year ago

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.

|

Collectives™ on Stack Overflow

Exporting an array to excel file with cell formatting

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related