我有一个报告任务，其中所有数据都应填写在 excel 文件中，并且应该从后端通过邮件发送给客户。我使用 excel.js 编写了一个 excel 文件，它使用较少的数据工作正常。如果数据大于 2000 或大于该 obj，则所有数据都不会在 excel 文件中填充。以下是我尝试过的示例。
下面是其中的 API。
router.get('/:type/:fromDate/:toDate',userAuth,(req,res)=>{
if(!req.query.ids) return res.send({'message':'Please send ID as query',statusCode:2});
let ids = req.query.ids.split(',');
var workbook = new Excel.Workbook();
let type = req.params.type
workbook.creator = ' 32';
workbook.lastModifiedBy = '321';
workbook.created = new Date();
workbook.modified = new Date();
workbook.views = [{
x: 0, y: 0, width: 10000, height: 20000,
firstSheet: 0, activeTab: 1, visibility: 'visible'
}]
var reportWorkSheet = workbook.addWorksheet( req.params.type +' Report', {
pageSetup: { paperSize: 9, orientation: 'landscape' }
});
if(type === 'customers'){
userCustomerReport(req,res ,ids , reportWorkSheet ,workbook );
} else if(type === 'interactions'){
userInteractionReport(req ,res, ids , reportWorkSheet ,workbook , req.params.fromDate , req.params.toDate);
} else if(type === 'allocations'){
userAllocationReport(req ,res,ids , reportWorkSheet ,workbook);
} else return res.send({'message':'Please check the request type',statusCode:2});
})
commonColomns = () => ([
{ header: 'Customer Name', key: 'cName', width: 25, style: { font: { size: 12 } } },
{ header: 'Customer Phone', key: 'cPhone', width: 35, style: { font: { size: 12 } } },
{ header: 'Customer Email', key: 'cEmail', width: 35, style: { font: { size: 12 } } },
{ header: 'Customer Company Name', key: 'cCompName', width: 18, style: { font: { size: 12 } } },
{ header: 'Assigned to name', key: 'assignedTName', width: 18, style: { font: { size: 12 } } },
{ header: 'Assigned from name ', key: 'assignedFName', width: 20, style: { font: { size: 12 } } }
]);
// here i am generation all JSON data .
function userInteractionReport(req ,res , ids ,reportWorkSheet , workbook , fromDate , toDate) {
let idString = req.query.ids.split(',');
let id =[];
idString.forEach(element => {id.push(new ObjectID(element));});
Interaction.aggregate([
{ $match:{$or: [{"assigned.toId":{$in:id}},{"assigned.fromId":{$in:id}}] ,createdTimeStamp : {$gte:Number(fromDate),$lt:Number(toDate)}} },
{ "$project": {
"assigned": 1,
"type": 1,
"priority": 1,
"customer": 1,
"customFields": 1,
"dateTime": 1,
"notes":1,
"length": { "$size": "$customFields" }
}},
{ "$sort": { "length": -1 } },
])
.then((interactions)=>{
if(!interactions[0]){
return res.send({'message':'No data found',statusCode:1 , "data":0})
}
let columns = commonColomns();
columns.push({ header: 'type', key: 'type', width: 25, style: { font: { size: 12 } } });
columns.push({ header: 'priority', key: 'priority', width: 25, style: { font: { size: 12 } } });
columns.push({ header: 'Company Address', key: 'cAddress', width: 25, style: { font: { size: 12 } } });
columns.push({ header: 'Key Decision Maker Name', key: 'kdm', width: 25, style: { font: { size: 12 } } });
columns.push({ header: 'Key Decision Maker Phone', key: 'kdmPhone', width: 25, style: { font: { size: 12 } } });
columns.push({ header: 'Date', key: 'dateTime', width: 25, style: { font: { size: 12 } } });
columns.push({ header: 'Notes', key: 'notes', width: 25, style: { font: { size: 12 } } });
for (let i = 0; i < interactions[0].customFields.length; i++) {
columns.push({ header: interactions[0].customFields[i].dName , key: interactions[0].customFields[i].dName, width: 25, style: { font: { size: 12 } } });
}
reportWorkSheet.columns = columns;
interactions.forEach(interaction => {
let assignedTo = interaction.assigned.toName ? interaction.assigned.toName : '';
let assignedFrom = interaction.assigned.fromName ? interaction.assigned.fromName : '';
let companyName = interaction.customer.company ? interaction.customer.company.name : '';
let cAddress ;
let kdm,kdmPhone ;
if(interaction.customer.company.address){
let companyAddress = interaction.customer.company.address ;
cAddress = companyAddress.street ? companyAddress.street+' ,' : '' ;
cAddress = cAddress + (companyAddress.city ? companyAddress.city + ' ,' :'' );
cAddress = cAddress + (companyAddress.state ? companyAddress.state +' ,' :'') ;
cAddress = cAddress+ (companyAddress.country ? companyAddress.country+' ,':'') ;
cAddress = cAddress + (companyAddress.pincode ? companyAddress.pincode +' ,' :'');
}
if(interaction.customer.company.kdm){
kdm = interaction.customer.company.kdm.fName;
kdmPhone = interaction.customer.company.kdm.phone;
}
let row = {
cName:interaction.customer.fName || '' + ' '+interaction.customer.lName || '',
cPhone : interaction.customer.phone.join(','),
assignedTName : assignedTo,
assignedFName : assignedFrom,
cEmail : interaction.email || ' ',
source : interaction.source || '',
type : interaction.type || '',
dateTime : interaction.dateTime ? new Date(interaction.dateTime) : '',
notes : interaction.notes || '',
priority : interaction.priority === 1 ? "High" : interaction.priority === 2 ? "Medium" : interaction.priority === 3 ? "Low" : " " ,
status : interaction.status||'',
cCompName : companyName,
cAddress : cAddress,
kdm :kdm,
kdmPhone:kdmPhone
}
for (let i = 0; i < interaction.customFields.length; i++) {
row[interaction.customFields[i].dName] = interaction.customFields[i].type === "dateTime" ? moment(interaction.customFields[i].value).format('l, h:mm:ss a') : interaction.customFields[i].value || " ";
}
reportWorkSheet.addRow(row);
});
return interactions;
}).then((interactions)=>{
writeWorkbook(workbook , req);
res.send({"messgae":"report send sucessfully" , "statusCode":0 ,"data":""})
}).catch((e)=>{
console.log(e);
})
}
function writeWorkbook(workbook , req) {
workbook.xlsx.writeFile('templates/excel/Report.xlsx')
.then(function () {
sendMail(req);
console.log("report send successfully ");
});
}
编写 excel 文件后将其附加到邮件并发送。
尝试流式传输：
// pipe from stream
const workbook = new Excel.Workbook()
workbook.useSharedStrings = false
stream.pipe(workbook.xlsx.createInputStream())
但是，内存管理似乎是此库的一个持续问题(截至本答案时(。请参阅此 github 问题以供参考：
https://github.com/exceljs/exceljs/issues/709 和这些相关问题
您可能希望使用另一个库来处理大量的 excel 文件(例如：Node-libxl。顺便说一句，这种扩展是付费扩展(。
如果你可以使用Python，你也可以尝试OpenPyxl
我用这段代码解决了这个问题。我尝试使用流而不是缓冲区。我使用打字稿对此进行编码，但问题是 当数据命> 250K 行时，它将表示堆内存不足。所以对于一个大数据，不用用 Nodejs，而是用Golang，非常强大，非常快速的编译和写入数据。
如果您使用它并且仍然收到堆内存错误，请尝试运行它。
node --max-old-space-size=<enter amount of more memory here> index.js
例如
(类似 6GB(：node --max-old-space-size=6124 index.js
或
(类似 12GB( ：node --max-old-space-size=12028 index.js
而不是像我们通常那样跑步。
node index.js
但是不建议设置最大内存，因为 RAM 需要更多资源来处理，并且会降低服务器性能。
请注意，我使用的是节点 14。
export async function exportToexcel(res: any, jsonSetting: ObjExcelSetting): Promise<any> {
// Initiate Excel Workbook
const workBook = new excel.stream.xlsx.WorkbookWriter({
// the most important part, dont forget to set this
stream: res
});
// initiate into variable from jsonSetting; column, data, sheetname
let { column, data, sheetname } = jsonSetting;
// default name for sheet if null
let defaultsheetname = sheetname ?? "Sheet"
// Add the worksheet
const workSheet = workBook.addWorksheet(defaultsheetname);
// Set the column
workSheet.columns = column
// Looping for adding the data to excel
console.log("Looping for adding the data to excel")
for (let i = 0; i < data.length; i++) {
const r = i + 1;
// dont forget to commit for **every** loop
workSheet.addRow(data[i]).commit();
}
// commit the workbook
console.log("Commit the excel workboom")
await workBook.commit();
}
res: any-> 指的是 ExpressJS 中的响应、请求方法或另一个框架(如 nestJS(中的类似内容
jsonSetting: ObjExcelSetting-> 引用我的函数的元数据，包含、列、工作表名和 jsondata。有关更多信息，请阅读 exceljs 文档，ObjexcelSetting只是打字稿注释，如果您使用 Javascript，请不要使用它
const workBook = new excel ...-> 是指启动模块，如const excel = require('exceljs')或import * as excel from 'exceljs'
数据将在Blob中返回，所以你猜你怎么能解析它。