Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/* 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');
@mani95lisa

This comment has been minimized.

Copy link

mani95lisa commented Aug 31, 2014

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!

@pjanaya

This comment has been minimized.

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.

res.download('test.xlsx');

The generated file will start to download immediately.

I hope it helps you.

Good coding!

@amit-jamwal

This comment has been minimized.

Copy link

amit-jamwal commented Oct 17, 2014

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.

@samuel012

This comment has been minimized.

Copy link

samuel012 commented Oct 19, 2014

Hello,

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

Thanks.

@vsomasvr

This comment has been minimized.

Copy link

vsomasvr commented Aug 21, 2015

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?

@bvenkatr

This comment has been minimized.

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?

@BransonGitomeh

This comment has been minimized.

Copy link

BransonGitomeh commented May 13, 2016

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?

@sinjihn

This comment has been minimized.

Copy link

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

This comment has been minimized.

Copy link

cuspymd commented Dec 1, 2016

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.

@ghost

This comment has been minimized.

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.

@jFranciscov

This comment has been minimized.

Copy link

jFranciscov commented Dec 28, 2017

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

@jFranciscov

This comment has been minimized.

Copy link

jFranciscov commented Dec 28, 2017

In https://stackoverflow.com/a/42066961/8895274 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
You can’t perform that action at this time.