node.js 如何使用exceljs在node.js中下载创建的excel文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38189089/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to download created excel file in node.js using exceljs
提问by codeinprogress
I am using exceljs module for creating excel file. The problem is it is neither getting created nor getting saved in the path.
我正在使用 exceljs 模块来创建 excel 文件。问题是它既没有被创建也没有被保存在路径中。
var excel = require('exceljs');
var options = {
filename: './streamed-workbook.xlsx',
useStyles: true,
useSharedStrings: true
};
var workbook = new Excel.stream.xlsx.WorkbookWriter(options);
var sheet = workbook.addWorksheet('My Sheet');
worksheet.columns = [
{ header: 'Id', key: 'id', width: 10 },
{ header: 'Name', key: 'name', width: 32 },
{ header: 'D.O.B.', key: 'DOB', width: 10 }
];
worksheet.addRow({id: 1, name: 'John Doe', dob: new Date(1970,1,1)});
worksheet.addRow({id: 2, name: 'Jane Doe', dob: new Date(1965,1,7)});
worksheet.commit();
workbook.commit().then(function(){
console.log('xls file is written.');
});
But when I run the code nothing happens. The excel is not created. What am I missing here?
但是当我运行代码时，什么也没有发生。没有创建excel。我在这里缺少什么？
*********************** Edit ************************** Made the following changes to my code but still its not working.
*********************** 编辑 **************************对我的代码进行了以下更改，但仍然无法正常工作。
var Excel = require('exceljs');
var workbook = new Excel.Workbook();
var worksheet = workbook.addWorksheet('My Sheet');
worksheet.columns = [
{ header: 'Id', key: 'id', width: 10 },
{ header: 'Name', key: 'name', width: 32 },
{ header: 'D.O.B.', key: 'DOB', width: 10 }
];
worksheet.addRow({id: 1, name: 'John Doe', dob: new Date(1970,1,1)});
worksheet.addRow({id: 2, name: 'Jane Doe', dob: new Date(1965,1,7)});
workbook.commit();
workbook.xlsx.writeFile('./temp.xlsx').then(function() {
// done
console.log('file is written');
});
回答by Danielle
In order to send the excel workbook to the client you can:
要将 Excel 工作簿发送给客户端，您可以：
Using async await:
使用异步等待：
async function sendWorkbook(workbook, response) {
var fileName = 'FileName.xlsx';
response.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
await workbook.xlsx.write(response);
response.end();
}
Using promise:
使用承诺：
function sendWorkbook(workbook, response) {
var fileName = 'FileName.xlsx';
response.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
workbook.xlsx.write(response).then(function(){
response.end();
});
}
回答by codeinprogress
So I figured out that I was getting an error because of the workbook.commit(). I removed the commit and everything started working like a charm. Here is the entire working code of creating and downloading an excel file:
所以我发现我因为 workbook.commit() 而出错。我删除了提交，一切都开始像魅力一样工作。以下是创建和下载 excel 文件的完整工作代码：
Note: I am using an npm module called tempfileto create a temporary file path for the created excel file. This file path is then automatically removed. Hope this helps.
注意：我正在使用一个名为tempfile的 npm 模块为创建的 excel 文件创建一个临时文件路径。然后会自动删除此文件路径。希望这可以帮助。
try {
var workbook = new Excel.Workbook();
var worksheet = workbook.addWorksheet('My Sheet');
worksheet.columns = [
{ header: 'Id', key: 'id', width: 10 },
{ header: 'Name', key: 'name', width: 32 },
{ header: 'D.O.B.', key: 'DOB', width: 10 }
];
worksheet.addRow({id: 1, name: 'John Doe', dob: new Date(1970,1,1)});
worksheet.addRow({id: 2, name: 'Jane Doe', dob: new Date(1965,1,7)});
var tempFilePath = tempfile('.xlsx');
workbook.xlsx.writeFile(tempFilePath).then(function() {
console.log('file is written');
res.sendFile(tempFilePath, function(err){
console.log('---------- error downloading file: ' + err);
});
});
} catch(err) {
console.log('OOOOOOO this is the error: ' + err);
}
回答by gandalivs
get answer from this link https://github.com/exceljs/exceljs/issues/37
从这个链接https://github.com/exceljs/exceljs/issues/37得到答案
router.get('/createExcel', function (req, res, next) {
var workbook = new Excel.Workbook();
workbook.creator = 'Me';
workbook.lastModifiedBy = 'Her';
workbook.created = new Date(1985, 8, 30);
workbook.modified = new Date();
workbook.lastPrinted = new Date(2016, 9, 27);
workbook.properties.date1904 = true;
workbook.views = [
{
x: 0, y: 0, width: 10000, height: 20000,
firstSheet: 0, activeTab: 1, visibility: 'visible'
}
];
var worksheet = workbook.addWorksheet('My Sheet');
worksheet.columns = [
{ header: 'Id', key: 'id', width: 10 },
{ header: 'Name', key: 'name', width: 32 },
{ header: 'D.O.B.', key: 'dob', width: 10, outlineLevel: 1, type: 'date', formulae: [new Date(2016, 0, 1)] }
];
worksheet.addRow({ id: 1, name: 'John Doe', dob: new Date(1970, 1, 1) });
worksheet.addRow({ id: 2, name: 'Jane Doe', dob: new Date(1965, 1, 7) });
res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
res.setHeader("Content-Disposition", "attachment; filename=" + "Report.xlsx");
workbook.xlsx.write(res)
.then(function (data) {
res.end();
console.log('File write done........');
});
}
回答by philipeachille
This code snippet is using node.js with the excel4node and express modules in order to convert JSON data to an Excel file and send it to the client, using Javascript.
此代码片段将 node.js 与 excel4node 和 express 模块一起使用，以便使用 Javascript 将 JSON 数据转换为 Excel 文件并将其发送到客户端。
const xl = require('excel4node');
const express = require('express');
const app = express();
var json = [{"Vehicle":"BMW","Date":"30, Jul 2013 09:24 AM","Location":"Hauz Khas, Enclave, New Delhi, Delhi, India","Speed":42},{"Vehicle":"Honda CBR","Date":"30, Jul 2013 12:00 AM","Location":"Military Road, West Bengal 734013, India","Speed":0},{"Vehicle":"Supra","Date":"30, Jul 2013 07:53 AM","Location":"Sec-45, St. Angel's School, Gurgaon, Haryana, India","Speed":58},{"Vehicle":"Land Cruiser","Date":"30, Jul 2013 09:35 AM","Location":"DLF Phase I, Marble Market, Gurgaon, Haryana, India","Speed":83},{"Vehicle":"Suzuki Swift","Date":"30, Jul 2013 12:02 AM","Location":"Behind Central Bank RO, Ram Krishna Rd by-lane, Siliguri, West Bengal, India","Speed":0},{"Vehicle":"Honda Civic","Date":"30, Jul 2013 12:00 AM","Location":"Behind Central Bank RO, Ram Krishna Rd by-lane, Siliguri, West Bengal, India","Speed":0},{"Vehicle":"Honda Accord","Date":"30, Jul 2013 11:05 AM","Location":"DLF Phase IV, Super Mart 1, Gurgaon, Haryana, India","Speed":71}]
const createSheet = () => {
return new Promise(resolve => {
// setup workbook and sheet
var wb = new xl.Workbook();
var ws = wb.addWorksheet('Sheet');
// Add a title row
ws.cell(1, 1)
.string('Vehicle')
ws.cell(1, 2)
.string('Date')
ws.cell(1, 3)
.string('Location')
ws.cell(1, 4)
.string('Speed')
// add data from json
for (let i = 0; i < json.length; i++) {
let row = i + 2
ws.cell(row, 1)
.string(json[i].Vehicle)
ws.cell(row, 2)
.date(json[i].Date)
ws.cell(row, 3)
.string(json[i].Location)
ws.cell(row, 4)
.number(json[i].Speed)
}
resolve( wb )
})
}
app.get('/excel', function (req, res) {
createSheet().then( file => {
file.write('ExcelFile.xlsx', res);
})
});
app.listen(3040, function () {
console.log('Excel app listening on port 3040');
});回答by Princess
There is a mistake in your Code.
您的代码中存在错误。
You have declared your My Sheet with one variable and using a different variable in your entire code.
您已使用一个变量声明了 My Sheet，并在整个代码中使用了不同的变量。
var sheet= workbook.addWorksheet('My Sheet');
worksheet.columns = [ { header: 'Id', key: 'id', width: 10 }, { header: 'Name', key: 'name', width: 32 }, { header: 'D.O.B.', key: 'DOB', width: 10 } ];
worksheet.addRow({id: 1, name: 'John Doe', dob: new Date(1970,1,1)});
worksheet.addRow({id: 2, name: 'Jane Doe', dob: new Date(1965,1,7)});
worksheet.commit();
var sheet= workbook.addWorksheet('My Sheet');
工作表.columns = [ { header: 'Id', key: 'id', width: 10 }, { header: 'Name', key: 'name', width: 32 }, { header: 'DOB', key: 'DOB'，宽度：10 } ];
工作表.addRow({id: 1, name: 'John Doe', dob: new Date(1970,1,1)});
工作表.addRow({id: 2, name: 'Jane Doe', dob: new Date(1965,1,7)});
工作表.commit();
Change variable Sheetto Worksheet. Like the below code
将变量Sheet更改为Worksheet。像下面的代码
var worksheet= workbook.addWorksheet('My Sheet');
worksheet.columns = [ { header: 'Id', key: 'id', width: 10 }, { header: 'Name', key: 'name', width: 32 }, { header: 'D.O.B.', key: 'DOB', width: 10 } ];
worksheet.addRow({id: 1, name: 'John Doe', dob: new Date(1970,1,1)});
worksheet.addRow({id: 2, name: 'Jane Doe', dob: new Date(1965,1,7)});
worksheet.commit();
var worksheet= workbook.addWorksheet('My Sheet');
工作表.columns = [ { header: 'Id', key: 'id', width: 10 }, { header: 'Name', key: 'name', width: 32 }, { header: 'DOB', key: 'DOB'，宽度：10 } ];
工作表.addRow({id: 1, name: 'John Doe', dob: new Date(1970,1,1)});
工作表.addRow({id: 2, name: 'Jane Doe', dob: new Date(1965,1,7)});
工作表.commit();
This should fix your issue. Thanks
这应该可以解决您的问题。谢谢
回答by Karthik
var excel = require("exceljs");
var workbook1 = new excel.Workbook();
workbook1.creator = 'Me';
workbook1.lastModifiedBy = 'Me';
workbook1.created = new Date();
workbook1.modified = new Date();
var sheet1 = workbook1.addWorksheet('Sheet1');
var reHeader=['FirstName','LastName','Other Name'];
var reColumns=[
{header:'FirstName',key:'firstname'},
{header:'LastName',key:'lastname'},
{header:'Other Name',key:'othername'}
];
sheet1.columns = reColumns;
workbook1.xlsx.writeFile("./uploads/error.xlsx").then(function() {
console.log("xlsx file is written.");
});
This creates xlsx file in uploads folder.
这会在上传文件夹中创建 xlsx 文件。
