write.js

/* 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.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;

/* write file */
XLSX.writeFile(wb, 'test.xlsx');

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!

Hi mani95lisa!

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

res.download('test.xlsx');

The generated file will start to download immediately.

I hope it helps you.

Good coding!

hello
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.

Hello,

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

Thanks.

When I use "res.download" 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?

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?

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

[{
value:"name",
color:"red",
font:"16"
},{
value:"name",
color:"red",
font:"16"
}]

where would i start?

@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>

Hi.
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.

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.

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

cell.s={
	fill:{
		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

In https://stackoverflow.com/a/42066961/8895274 is a way to generate xlsx with styles