Skip to content

Instantly share code, notes, and snippets.

@SheetJSDev
Created May 22, 2014 12:00
  • Star 37 You must be signed in to star a gist
  • Fork 5 You must be signed in to fork a gist
Star You must be signed in to star a gist
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.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
/* write file */
XLSX.writeFile(wb, 'test.xlsx');
@mani95lisa
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!

@pjanaya
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
Copy link

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
Copy link

Hello,

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

Thanks.

@vsomasvr
Copy link

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

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

where would i start?

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

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

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.

@juan-francisco-vega
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

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

@juan-francisco-vega
Copy link

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