如何使用 Nodejs 创建 Excel 文件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17450412/
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 create an Excel File with Nodejs?
提问by geeky_monster
I am a nodejs programmer . Now I have a table of data that I want to save in Excel File format . How do I go about doing this ?
我是一个 nodejs 程序员。现在我有一个数据表，我想以 Excel 文件格式保存。我该怎么做？
I found a few Node libraries . But most of them are Excel Parsers rather than Excel Writers .I am using a Linux Server . Hence need something that can run on Linux . Please let me know if there are any helpful libraries that you know of .
我找到了一些 Node 库。但他们中的大多数是 Excel Parsers 而不是 Excel Writers。我使用的是 Linux 服务器。因此需要一些可以在 Linux 上运行的东西。如果您知道有任何有用的库，请告诉我。
Or is there a way I can convert a CSV file to an xls file ( programmatically ) ?
或者有没有办法将 CSV 文件转换为 xls 文件（以编程方式）？
回答by mikemaccana
excel4nodeis a maintained, native Excel file creator built from the official specification. It's similar to, but more maintained than mxexcel-buildermentioned in the other answer.
excel4node是根据官方规范构建的维护的本机 Excel 文件创建器。它与另一个答案中提到的mxexcel-builder相似，但维护得更好。
// Require library
var excel = require('excel4node');
// Create a new instance of a Workbook class
var workbook = new excel.Workbook();
// Add Worksheets to the workbook
var worksheet = workbook.addWorksheet('Sheet 1');
var worksheet2 = workbook.addWorksheet('Sheet 2');
// Create a reusable style
var style = workbook.createStyle({
font: {
color: '#FF0800',
size: 12
},
numberFormat: '$#,##0.00; ($#,##0.00); -'
});
// Set value of cell A1 to 100 as a number type styled with paramaters of style
worksheet.cell(1,1).number(100).style(style);
// Set value of cell B1 to 300 as a number type styled with paramaters of style
worksheet.cell(1,2).number(200).style(style);
// Set value of cell C1 to a formula styled with paramaters of style
worksheet.cell(1,3).formula('A1 + B1').style(style);
// Set value of cell A2 to 'string' styled with paramaters of style
worksheet.cell(2,1).string('string').style(style);
// Set value of cell A3 to true as a boolean type styled with paramaters of style but with an adjustment to the font size.
worksheet.cell(3,1).bool(true).style(style).style({font: {size: 14}});
workbook.write('Excel.xlsx');
回答by geeky_monster
I just figured a simple way out . This works -
我只是想出了一个简单的方法。这有效 -
Just create a file with Tabs as delimiters ( similar to CSV but replace comma with Tab ). Save it with extension .XLS . The file can be opened in Excel .
只需创建一个带有 Tabs 作为分隔符的文件（类似于 CSV 但用 Tab 替换逗号）。使用扩展名 .XLS 保存它。该文件可以在 Excel 中打开。
Some code to help --
一些代码来帮助 -
var fs = require('fs');
var writeStream = fs.createWriteStream("file.xls");
var header="Sl No"+"\t"+" Age"+"\t"+"Name"+"\n";
var row1 = "0"+"\t"+" 21"+"\t"+"Rob"+"\n";
var row2 = "1"+"\t"+" 22"+"\t"+"bob"+"\n";
writeStream.write(header);
writeStream.write(row1);
writeStream.write(row2);
writeStream.close();
This creates the file in XLS file format . It doesnt work if you try XLSX instead of XLS .
这将创建 XLS 文件格式的文件。如果您尝试使用 XLSX 而不是 XLS ，则它不起作用。
回答by Hesham Yassin
Use msexcel-builder. Install it with:
使用msexcel-builder。安装它：
npm install msexcel-builder
Then:
然后：
// Create a new workbook file in current working-path
var workbook = excelbuilder.createWorkbook('./', 'sample.xlsx')
// Create a new worksheet with 10 columns and 12 rows
var sheet1 = workbook.createSheet('sheet1', 10, 12);
// Fill some data
sheet1.set(1, 1, 'I am title');
for (var i = 2; i < 5; i++)
sheet1.set(i, 1, 'test'+i);
// Save it
workbook.save(function(ok){
if (!ok)
workbook.cancel();
else
console.log('congratulations, your workbook created');
});
回答by Frosty Z
You should check ExcelJS
你应该检查ExcelJS
Works with CSV and XLSX formats.
适用于 CSV 和 XLSX 格式。
Great for reading/writing XLSX streams. I've used it to stream an XLSX download to an Express response object, basically like this:
非常适合读/写 XLSX 流。我用它来将 XLSX 下载流式传输到 Express 响应对象，基本上是这样的：
app.get('/some/route', function(req, res) {
res.writeHead(200, {
'Content-Disposition': 'attachment; filename="file.xlsx"',
'Transfer-Encoding': 'chunked',
'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
})
var workbook = new Excel.stream.xlsx.WorkbookWriter({ stream: res })
var worksheet = workbook.addWorksheet('some-worksheet')
worksheet.addRow(['foo', 'bar']).commit()
worksheet.commit()
workbook.commit()
}
Works great for large files, performs much better than excel4node(got huge memory usage & Node process "out of memory" crash after nearly 5 minutes for a file containing 4 million cells in 20 sheets) since its streaming capabilities are much more limited (does not allows to "commit()" data to retrieve chunks as soon as they can be generated)
非常适合大文件，性能比excel4node 好得多（对于一个包含 20 个工作表中 400 万个单元的文件，在将近 5 分钟后出现大量内存使用和 Node 进程“内存不足”崩溃），因为它的流媒体功能更加有限（确实如此）不允许“提交（）”数据以在生成块后立即检索）
See also this SO answer.
另请参阅此 SO 答案。
回答by Maciej Jankowski
XLSx in the new Office is just a zipped collection of XML and other files. So you could generate that and zip it accordingly.
新 Office 中的 XLSx 只是 XML 和其他文件的压缩集合。所以你可以生成它并相应地压缩它。
Bonus: you can create a very nice template with styles and so on:
奖励：您可以创建一个非常漂亮的模板，包括样式等：
- Create a template in 'your favorite spreadsheet program'
- Save it as ODS or XLSx
- Unzip the contents
- Use it as baseand fill
content.xml(orxl/worksheets/sheet1.xml) with your data - Zip it all before serving
- 在“您最喜欢的电子表格程序”中创建模板
- 将其另存为 ODS 或 XLSx
- 解压内容
- 将其用作基础并用您的数据填充
content.xml（或xl/worksheets/sheet1.xml） - 上菜前全部拉上拉链
However I found ODS (openoffice) much more approachable (excel can still open it), here is what I found in content.xml
但是我发现 ODS (openoffice) 更平易近人（excel 仍然可以打开它），这是我在 content.xml
<table:table-row table:style-name="ro1">
<table:table-cell office:value-type="string" table:style-name="ce1">
<text:p>here be a1</text:p>
</table:table-cell>
<table:table-cell office:value-type="string" table:style-name="ce1">
<text:p>here is b1</text:p>
</table:table-cell>
<table:table-cell table:number-columns-repeated="16382"/>
</table:table-row>
回答by tmanolatos
Or - build on @Jamaica Geek's answer, using Express - to avoid saving and reading a file:
或者 - 基于@Jamaica Geek 的回答，使用 Express - 避免保存和读取文件：
res.attachment('file.xls');
var header="Sl No"+"\t"+" Age"+"\t"+"Name"+"\n";
var row1 = [0,21,'BOB'].join('\t')
var row2 = [0,22,'bob'].join('\t');
var c = header + row1 + row2;
return res.send(c);
回答by defraggled
Although this question has several answers, they may now be a little dated.
虽然这个问题有几个答案，但它们现在可能有点过时了。
New readers may prefer to consider the xlsx or "sheetsJS" package, which now seems to now be by far the most popular node package for this use case.
新读者可能更喜欢考虑 xlsx 或“sheetsJS”包，它现在似乎是迄今为止最流行的用于此用例的节点包。
The current top answer recommends excel4node , which does look quite good - but the latter package seems less maintained (and far less popular) than the former.
当前的最佳答案推荐 excel4node ，它看起来确实不错 - 但后一个包似乎比前者更少维护（并且远不受欢迎）。
Answering the question directly, using xlsx:
直接回答问题，使用xlsx：
const XLSX = require('xlsx');
/* create a new blank workbook */
const wb = XLSX.utils.book_new();
// Do stuff, write data
//
//
// write the workbook object to a file
XLSX.writeFile(workbook, 'out.xlsx');
回答by Nilesh Pawar
Using fspackage we can create excel/CSV file from JSON data.
使用fs包，我们可以从 JSON 数据创建 excel/CSV 文件。
Step 1:Store JSON data in a variable (here it is in jsnvariable).
第 1 步：将JSON 数据存储在一个变量中（这里是在jsn变量中）。
Step 2:Create empty string variable(here it is data).
第 2 步：创建空字符串变量（这里是data）。
Step 3:Append every property of jsnto string variable data, while appending put '\t'in-between 2 cells and '\n' after completing the row.
步骤3：将jsn的每个属性附加到字符串变量数据中，同时'\t'在完成行后附加放置在2个单元格和'\n'之间。
Code:
代码：
var fs = require('fs');
var jsn = [{
"name": "Nilesh",
"school": "RDTC",
"marks": "77"
},{
"name": "Sagar",
"school": "RC",
"marks": "99.99"
},{
"name": "Prashant",
"school": "Solapur",
"marks": "100"
}];
var data='';
for (var i = 0; i < jsn.length; i++) {
data=data+jsn[i].name+'\t'+jsn[i].school+'\t'+jsn[i].marks+'\n';
}
fs.appendFile('Filename.xls', data, (err) => {
if (err) throw err;
console.log('File created');
});
回答by rajpoot rehan
install exceljs
安装exceljs
npm i exceljs --save
import exceljs
导入exceljs
var Excel = require('exceljs');
var workbook = new Excel.Workbook();
create workbook
创建工作簿
var options = {
filename: __dirname+'/Reports/'+reportName,
useStyles: true,
useSharedStrings: true
};
var workbook = new Excel.stream.xlsx.WorkbookWriter(options);
after create worksheet
创建工作表后
var worksheet = workbook.addWorksheet('Rate Sheet',{properties:{tabColor:{argb:'FFC0000'}}});
in worksheet.column array you pass column name in header and array key pass in key
在 worksheet.column 数组中，您在标题中传递列名，在键中传递数组键
worksheet.columns = [
{ header: 'column name', key: 'array key', width: 35},
{ header: 'column name', key: 'array key', width: 35},
{ header: 'column name', key: 'array key', width: 20},
];
after using forEach loop append row one by one in exel file
使用 forEach 循环后，在 exel 文件中逐行追加
array.forEach(function(row){ worksheet.addRow(row); })
you can also perfome loop on each exel row and cell
您还可以在每个 exel 行和单元格上执行循环
worksheet.eachRow(function(row, rowNumber) {
console.log('Row ' + rowNumber + ' = ' + JSON.stringify(row.values));
});
row.eachCell(function(cell, colNumber) {
console.log('Cell ' + colNumber + ' = ' + cell.value);
});
