/* 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'); |
This comment has been minimized.
This comment has been minimized.
Hi mani95lisa! I'm using this piece of code with Express without problems. Just add this after the writeFile.
The generated file will start to download immediately. I hope it helps you. Good coding! |
This comment has been minimized.
This comment has been minimized.
hello |
This comment has been minimized.
This comment has been minimized.
Hello, On top of amit-jamwal's question, is there a way to include images in the rendered excel file? Thanks. |
This comment has been minimized.
This comment has been minimized.
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. In the demo, I was that Excel doesn't give that warning when I open the file. What's the magic there? |
This comment has been minimized.
This comment has been minimized.
Hi, I am getting error when I use this on client side Can you help me in fixing this issue? |
This comment has been minimized.
This comment has been minimized.
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? |
This comment has been minimized.
This comment has been minimized.
@bvenkatr make sure to include the source scripts in the correct order:
|
This comment has been minimized.
This comment has been minimized.
Hi. |
This comment has been minimized.
This comment has been minimized.
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 Adding |
This comment has been minimized.
This comment has been minimized.
I'm looking to giving style to the cells in node environment but I can't.
but when trying to do the same on backend it doesn't work at all |
This comment has been minimized.
This comment has been minimized.
In https://stackoverflow.com/a/42066961/8895274 is a way to generate xlsx with styles |
This comment has been minimized.
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!