1.node-excel
node-excel作为node.js模块导出数据到Excel xlsx文件中。在使用node-excel导入数据之前，先对其进行配置。
| 字段 | 功能 |
|---|---|
| cols | 一组列定义，列定义包含：列名称和列类型，同时也可以定义宽度但是非必需 |
| beforeCellWrite | 这是一个回调，可选择的，返回的结果写入到cell中 |
| rows | 数据，rows是一个数组结构，每一个单元的长度应该和cols相同。样式可选，如果想要配置样式，需要一个有效的styles xml文件。可以通过解压一个xlsx文件，拷贝该文件的style xml文件 |
| stylesXmlFile | 配置style xml文件 |
实现如下所示：
var express = require('express');
var nodeExcel = require('excel-export');
var app = express();
app.get('/Excel', function(req, res){
var conf ={};
conf.stylesXmlFile = "styles.xml";
conf.cols = [{
caption:'string',
type:'string',
beforeCellWrite:function(row, cellData){
return cellData.toUpperCase();
},
width:28.7109375
},{
caption:'date',
type:'date',
beforeCellWrite:function(){
var originDate = new Date(Date.UTC(1899,11,30));
return function(row, cellData, eOpt){
if (eOpt.rowNum%2){
eOpt.styleIndex = 1;
}
else{
eOpt.styleIndex = 2;
}
if (cellData === null){
eOpt.cellType = 'string';
return 'N/A';
} else
return (cellData - originDate) / (24 * 60 * 60 * 1000);
}
}()
},{
caption:'bool',
type:'bool'
},{
caption:'number',
type:'number'
}];
conf.rows = [
['pi', new Date(Date.UTC(2013, 4, 1)), true, 3.14],
["e", new Date(2012, 4, 1), false, 2.7182],
["M&M<>'", new Date(Date.UTC(2013, 6, 9)), false, 1.61803],
["null date", null, true, 1.414]
];
var result = nodeExcel.execute(conf);
res.setHeader('Content-Type', 'application/vnd.openxmlformats');
res.setHeader("Content-Disposition", "attachment; filename=" + "Report.xlsx");
res.end(result, 'binary');
});
app.listen(3000);
console.log('Listening on port 3000');
如果不想直接将结果返回给前端，可以通过写文件的方式，写入本地文件：
var writeStream = fs.createWriteStream(destPath)
writeStream.on('finish', function () {
res.send({
status: 0,
message: 'ok',
})
});
writeStream.on('error', async function (err) {
res.send({
status: 1,
message: err.message,
})
});
writeStream.write(result, 'binary');
writeStream.end();
SpreadsheetML
xlsx的样式有固定的语法格式，这里举一个例子说明：
<?xml version="1.0"?>
<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<ss:Styles>
<ss:Style ss:ID="1">
<ss:Font ss:Bold="1"/>
</ss:Style>
</ss:Styles>
<ss:Worksheet ss:Name="Sheet1">
<ss:Table>
<ss:Column ss:Width="80"/>
<ss:Column ss:Width="80"/>
<ss:Column ss:Width="80"/>
<ss:Row ss:StyleID="1">
<ss:Cell>
<ss:Data ss:Type="String">First Name</ss:Data>
</ss:Cell>
<ss:Cell>
<ss:Data ss:Type="String">Last Name</ss:Data>
</ss:Cell>
<ss:Cell>
<ss:Data ss:Type="String">Phone Number</ss:Data>
</ss:Cell>
</ss:Row>
<ss:Row>
<ss:Cell>
<ss:Data ss:Type="String">Nancy</ss:Data>
</ss:Cell>
<ss:Cell>
<ss:Data ss:Type="String">Davolio</ss:Data>
</ss:Cell>
<ss:Cell>
<ss:Data ss:Type="String">(206)555-9857</ss:Data>
</ss:Cell>
</ss:Row>
...
</ss:Row>
</ss:Table>
</ss:Worksheet>
</ss:Workbook>
2.js-xlsx
SheetJS/js-xlsx开源项目关注的人比较多，维护的也不错，一直有人维护。
npm install xlsx
image.png
在使用这个库之前，先介绍库中的一些概念：
- workbook对象，指的是整份 Excel 文档。在使用 js-xlsx 读取 Excel 文档之后就会获得 workbook 对象。
- worksheet 对象，指的是 Excel 文档中的表。我们知道一份 Excel 文档中可以包含很多张表，而每张表对应的就是 worksheet 对象。
- cell 对象，指的就是 worksheet 中的单元格，一个单元格就是一个 cell 对象。
```java
//打开Excel文件，返回 workbook
import XLSX from 'xlsx';
const workbook = XLSX.readFile('someExcel.xlsx', opts);
// 获取 Excel 中所有表名
const sheetNames = workbook.SheetNames; // 返回 ['sheet1', 'sheet2']
// 根据表名获取对应某张表
const worksheet = workbook.Sheets[sheetNames[0]];
// 获取 A1 单元格对象
let a1 = worksheet['A1']; // 返回 { "t": "n", "v": 2, "w": "2"}
// 获取 A1 中的值
a1.v // 返回真实值2
// 获取表的有效范围
worksheet['!ref'] // 返回 'A1:B20'
// 获取排序过的单元格
worksheet['!range'] // 返回range对象，{ s: { r: 0, c: 0}, e: { r: 100, c: 2 } }
// 获取合并过的单元格
worksheet['!merges'] // 返回merges对象，[ {s: { r: 0, c: 0 }, c: { r: 2, c: 1 } } ]
关系如下所示：
// workbook
{
SheetNames: ['sheet1', 'sheet2'],
Sheets: {
// worksheet
'sheet1': {
// cell
'A1': { ... },
// cell
'A2': { ... },
...
},
// worksheet
'sheet2': {
// cell
'A1': { ... },
// cell
'A2': { ... },
...
}
}
}
2.1 接口
解析和写入函数
| id | 函数 | 说明 |
|---|---|---|
| 1 | XLSX.read(data, read_opts) |
试图解析data数据 |
| 2 | XLSX.readFile(filename, read_opts) |
解析filename文件，opts参考Parsing Options |
| 3 | XLSX.write(wb, write_opts) |
写数据到workbook |
| 4 | XLSX.writeFile(wb, filename, write_opts) |
写数据到文件 |
| 5 | XLSX.writeFileAsync(filename, wb, o, cb) |
异步写文件 |
Utilities函数
| id | 函数 | 说明 |
|---|---|---|
| 1 | aoa_to_sheet |
将数组数据写入工作簿 |
| 2 | json_to_sheet |
将json数据写入工作簿 |
| 3 | table_to_sheet |
将DOM TABLE元素写入工作簿 |
| 4 | sheet_to_json |
工作簿转化为json |
| 5 | sheet_to_csv |
工作簿转化为csv |
| 6 | sheet_to_html |
工作簿转化为HTML |
| 7 | sheet_to_formulae |
工作簿转化为行数据 |
| 8 | format_cell |
cell转化为text |
| 9 | encode_row / decode_row |
转换行 |
| 10 | encode_col / decode_col |
转换列 |
| 11 | encode_cell / decode_cell |
转换cell地址 |
| 12 | encode_range / decode_range |
转换cell排序 |
2.2 单元格对象
| Key | Description |
|---|---|
| v | 单元格名称 |
| w | 内容 |
| t | 类型: b Boolean, n Number, e error, s String, d Date |
| f | 单元格样式 |
| F | 排序样式 |
| r | 富媒体编码 |
| h | HTML绘制富媒体 |
| c | 单元格描述 |
| z | 样式个数 |
| l | 单元格链接 |
| s | 单元格主题 |
2.3 实例
导出表格
- 构建特定的数据结构，如下。
- 调用 XLSX.writeFile(workbook, filename) 即可。
// workbook
{
SheetNames: ['mySheet'],
Sheets: {
'mySheet': {
'!ref': 'A1:E4', // 必须要有这个范围才能输出，否则导出的 excel 会是一个空表
A1: { v: 'id' },
...
}
}
}
var _headers = ['id', 'name', 'age', 'country', 'remark']
var _data = [ { id: '1',
name: 'test1',
age: '30',
country: 'China',
remark: 'hello' },
{ id: '2',
name: 'test2',
age: '20',
country: 'America',
remark: 'world' },
{ id: '3',
name: 'test3',
age: '18',
country: 'Unkonw',
remark: '???' } ];
var headers = _headers
// 为 _headers 添加对应的单元格位置
// [ { v: 'id', position: 'A1' },
// { v: 'name', position: 'B1' },
// { v: 'age', position: 'C1' },
// { v: 'country', position: 'D1' },
// { v: 'remark', position: 'E1' } ]
.map((v, i) => Object.assign({}, {v: v, position: String.fromCharCode(65+i) + 1 }))
// 转换成 worksheet 需要的结构
// { A1: { v: 'id' },
// B1: { v: 'name' },
// C1: { v: 'age' },
// D1: { v: 'country' },
// E1: { v: 'remark' } }
.reduce((prev, next) => Object.assign({}, prev, {[next.position]: {v: next.v}}), {});
var data = _data
// 匹配 headers 的位置，生成对应的单元格数据
// [ [ { v: '1', position: 'A2' },
// { v: 'test1', position: 'B2' },
// { v: '30', position: 'C2' },
// { v: 'China', position: 'D2' },
// { v: 'hello', position: 'E2' } ],
// [ { v: '2', position: 'A3' },
// { v: 'test2', position: 'B3' },
// { v: '20', position: 'C3' },
// { v: 'America', position: 'D3' },
// { v: 'world', position: 'E3' } ],
// [ { v: '3', position: 'A4' },
// { v: 'test3', position: 'B4' },
// { v: '18', position: 'C4' },
// { v: 'Unkonw', position: 'D4' },
// { v: '???', position: 'E4' } ] ]
.map((v, i) => _headers.map((k, j) => Object.assign({}, { v: v[k], position: String.fromCharCode(65+j) + (i+2) })))
// 对刚才的结果进行降维处理（二维数组变成一维数组）
// [ { v: '1', position: 'A2' },
// { v: 'test1', position: 'B2' },
// { v: '30', position: 'C2' },
// { v: 'China', position: 'D2' },
// { v: 'hello', position: 'E2' },
// { v: '2', position: 'A3' },
// { v: 'test2', position: 'B3' },
// { v: '20', position: 'C3' },
// { v: 'America', position: 'D3' },
// { v: 'world', position: 'E3' },
// { v: '3', position: 'A4' },
// { v: 'test3', position: 'B4' },
// { v: '18', position: 'C4' },
// { v: 'Unkonw', position: 'D4' },
// { v: '???', position: 'E4' } ]
.reduce((prev, next) => prev.concat(next))
// 转换成 worksheet 需要的结构
// { A2: { v: '1' },
// B2: { v: 'test1' },
// C2: { v: '30' },
// D2: { v: 'China' },
// E2: { v: 'hello' },
// A3: { v: '2' },
// B3: { v: 'test2' },
// C3: { v: '20' },
// D3: { v: 'America' },
// E3: { v: 'world' },
// A4: { v: '3' },
// B4: { v: 'test3' },
// C4: { v: '18' },
// D4: { v: 'Unkonw' },
// E4: { v: '???' } }
.reduce((prev, next) => Object.assign({}, prev, {[next.position]: {v: next.v}}), {});
// 合并 headers 和 data
var output = Object.assign({}, headers, data);
// 获取所有单元格的位置
var outputPos = Object.keys(output);
// 计算出范围
var ref = outputPos[0] + ':' + outputPos[outputPos.length - 1];
// 构建 workbook 对象
var wb = {
SheetNames: ['mySheet'],
Sheets: {
'mySheet': Object.assign({}, output, { '!ref': ref })
}
};
// 导出 Excel
XLSX.writeFile(wb, 'output.xlsx');
2.4 实例2
功能：读入一个表格，将数据更改保存，保存单元格样式
npm install xlsx-style --save
这里需要使用xlsx-style替代xlsx
import XLSX from 'xlsx-style';
var workbook = XLSX.readFile("han.xlsx", {cellStyles:true, bookFiles:true})
var worksheet = workbook.Sheets[workbook.SheetNames[0]];
var result = XLSX.utils.sheet_to_formulae(worksheet);
worksheet['A4'].v = 10
XLSX.writeFile(workbook, 'out.xlsx');
image.png
修改之后样式如下：
image.png
在测试中也发现，图片资源和图表资源没有能够保留，这点需要留意。
参考：http://m8y.org/Microsoft_Office_2003_XML_Reference_Schemas/Help/html/spreadsheetml_HV01151864.htm
在 Node.js 中利用 js-xlsx 处理 Excel 文件
https://github.com/SheetJS/js-xlsx
https://www.npmjs.com/package/xlsx-style
