Skip to content

Instantly share code, notes, and snippets.

Created May 22, 2014 12:00
Show Gist options
  • Save SheetJSDev/88a3ca3533adf389d13c to your computer and use it in GitHub Desktop.
Save SheetJSDev/88a3ca3533adf389d13c to your computer and use it in GitHub Desktop.
/* require XLSX */
var XLSX = require('XLSX')
function datenum(v, date1904) {
if(date1904) v+=1462;
var epoch = Date.parse(v);
return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
function sheet_from_array_of_arrays(data, opts) {
var ws = {};
var range = {s: {c:10000000, r:10000000}, e: {c:0, r:0 }};
for(var R = 0; R != data.length; ++R) {
for(var C = 0; C != data[R].length; ++C) {
if(range.s.r > R) range.s.r = R;
if(range.s.c > C) range.s.c = C;
if(range.e.r < R) range.e.r = R;
if(range.e.c < C) range.e.c = C;
var cell = {v: data[R][C] };
if(cell.v == null) continue;
var cell_ref = XLSX.utils.encode_cell({c:C,r:R});
if(typeof cell.v === 'number') cell.t = 'n';
else if(typeof cell.v === 'boolean') cell.t = 'b';
else if(cell.v instanceof Date) {
cell.t = 'n'; cell.z = XLSX.SSF._table[14];
cell.v = datenum(cell.v);
else cell.t = 's';
ws[cell_ref] = cell;
if(range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
return ws;
/* original data */
var data = [[1,2,3],[true, false, null, "sheetjs"],["foo","bar",new Date("2014-02-19T14:30Z"), "0.3"], ["baz", null, "qux"]]
var ws_name = "SheetJS";
function Workbook() {
if(!(this instanceof Workbook)) return new Workbook();
this.SheetNames = [];
this.Sheets = {};
var wb = new Workbook(), ws = sheet_from_array_of_arrays(data);
/* add worksheet to workbook */
wb.Sheets[ws_name] = ws;
/* write file */
XLSX.writeFile(wb, 'test.xlsx');
Copy link

Hi, Thanks for your work!, but I have a simple question, please have a look , thanks!
When I use this alone, everything works fine. When I use this with express, I want to use res.send buffer or saved file, the xlsx file or data are broken.
I don't know why, if you have any usage with express or server request, please reply me, thanks again!

Copy link

pjanaya commented Sep 3, 2014

Hi mani95lisa!

I'm using this piece of code with Express without problems. Just add this after the writeFile.'test.xlsx');

The generated file will start to download immediately.

I hope it helps you.

Good coding!

Copy link

I am new with this and want to know that how to add style in cell.
like i want a header whose background color is green and some of the value in data cells with font color red.

Copy link


On top of amit-jamwal's question, is there a way to include images in the rendered excel file?


Copy link

When I use "" in express middleware, everything works fine, except that Excel file gives a warning about repairing the file content and trusting the resource. On clicking Yes, it opens the file there's no data loss, everything is readable.
It's just annoying to see that we see that warning all the time.

In the demo, I was that Excel doesn't give that warning when I open the file. What's the magic there?

Copy link

bvenkatr commented Mar 9, 2016

Hi, I am getting error when I use this on client side
TypeError: jszip is not a function
at write_zip (http://localhost:3001/plugin/js-xlsx/dist/xlsx.js:11295:12)

Can you help me in fixing this issue?

Copy link

hey this works like magic thanks, but the cells appear to be too narrow, is there a way to add style to this particular snippet?...maybe have the cells width increase automatically?....

also if i want to have an array of js objects describing each of the cells structure and styling ie


where would i start?

Copy link

hypogealgaol commented May 19, 2016

@bvenkatr make sure to include the source scripts in the correct order:

<script src="js-xlsx-master/jszip.js"></script>
<script src="js-xlsx-master/shim.js"></script>
<script src="js-xlsx-master/xlsx.js"></script>
<script src="js-xlsx-master/dist/ods.js"></script>

Copy link

cuspymd commented Dec 1, 2016

The limit value '10000000' is meaningful?
I have checked '1,048,576 rows by 16,384 columns' is the maximum limit of Microsoft Excel and Libreoffice Calc.

Copy link

ghost commented Jan 2, 2017

I was getting corrupted files when using this code under a node environment. After a bit of messing around, I found the problem was the datenum function returns 12 decimal places which after writing out to a document, excel appears to not like.

Adding datenum(cell.v).toFixed(10) appears to solve this issue if anyone else comes across this problem.

Copy link

I'm looking to giving style to the cells in node environment but I can't.
I have done styling cells in frontend like this inside sheet_from_array_of_arrays iteration

		fgColor:{ rgb: "CCFFCC" }
	border: {
		top: {style: "medium", color: {rgb: "000"}},
		right: {style: "medium", color: {rgb: "000"}},
		bottom: {style: "medium", color: {rgb: "000"}},
		left: {style: "medium", color: {rgb: "000"}}

but when trying to do the same on backend it doesn't work at all

Copy link

In is a way to generate xlsx with styles

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment