简介
读取,操作并写入电子表格数据和样式到 XLSX 和 JSON 文件。
一个 Excel 电子表格文件逆向工程项目。
ExcelJS 周下载量 450k,github star 9k,并且拥有中文文档,对国内开发者很友好。虽然文档是以README 的形式,可读性不太好,但重在内容,常用的功能基本都有覆盖。
- 更详细的介绍参考官方文档:github.com/exceljs/exc…
- 参考学习:zhuanlan.zhihu.com/p/526658433…
本文版本
对比
xlsx
呼声最高的是 xlsx[3] ,又叫 SheetJS
,也是下载量最高和 star
最多的库。试用了一下很强大,但是!默认不支持改变样式,想要支持改变样式,需要使用它的收费版本。
本着勤俭节约的原则,很多人使用了另一个第三方库:xlsx-style[4] ,但是使用起来极其复杂,还需要改 node_modules 源码,这个库最后更新时间也定格在了 6年前。还有一些其他的第三方样式拓展库,质量参差不齐。
使用成本和后期的维护成本很高,不得不放弃。
ExcelJS
ExcelJS[5] 周下载量 450k,github star 9k,并且拥有中文文档,对国内开发者很友好。虽然文档是以README 的形式,可读性不太好,但重在内容,常用的功能基本都有覆盖。
最近更新时间是6个月内,试用了一下,集成很简单,再加之文档丰富,就选它了。
安装
npm install exceljs
下载到本地还需要另一个库:file-saver
npm install file-saver
基本概念
workbook
workbook:工作簿,可以理解为整个 excel 表格。
通过 const workbook = new ExcelJS.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);
worksheet
工作表,即 Excel 表格中的 sheet 页。
通过 const sheet = workbook.addWorksheet('My Sheet')
创建工作表,每个 workbook 可添加多个 worksheet。
使用 addWorksheet 函数的第二个参数来指定工作表的选项。
// 创建带有红色标签颜色的工作表
const sheet = workbook.addWorksheet('My Sheet', {properties:{tabColor:{argb:'FFC0000'}}});
// 创建一个隐藏了网格线的工作表
const sheet = workbook.addWorksheet('My Sheet', {views: [{showGridLines: false}]});
// 创建一个第一行和列冻结的工作表
const sheet = workbook.addWorksheet('My Sheet', {views:[{xSplit: 1, ySplit:1}]});
// 使用A4设置的页面设置设置创建新工作表 - 横向
const worksheet = workbook.addWorksheet('My Sheet', {
pageSetup:{paperSize: 9, orientation:'landscape'}
});
// 创建一个具有页眉页脚的工作表
const sheet = workbook.addWorksheet('My Sheet', {
headerFooter:{firstHeader: "Hello Exceljs", firstFooter: "Hello World"}
});
// 创建一个冻结了第一行和第一列的工作表
const sheet = workbook.addWorksheet('My Sheet', {views:[{state: 'frozen', xSplit: 1, ySplit:1}]});
columns
列,通过 worksheet.columns
可设置表头。
// 添加列标题并定义列键和宽度
// 注意:这些列结构仅是构建工作簿的方便之处,除了列宽之外,它们不会完全保留。
worksheet.columns = [
{ header: 'Id', key: 'id', width: 10 },
{ header: 'Name', key: 'name', width: 32 },
{ header: 'D.O.B.', key: 'DOB', width: 10, outlineLevel: 1 }
];
// 通过键,字母和基于1的列号访问单个列
const idCol = worksheet.getColumn('id');
const nameCol = worksheet.getColumn('B');
const dobCol = worksheet.getColumn(3);
// 设置列属性
// 注意:将覆盖 C1 单元格值
dobCol.header = 'Date of Birth';
// 注意:这将覆盖 C1:C2 单元格值
dobCol.header = ['Date of Birth', 'A.K.A. D.O.B.'];
// 从现在开始,此列将以 “dob” 而不是 “DOB” 建立索引
dobCol.key = 'dob';
dobCol.width = 15;
// 如果需要,隐藏列
dobCol.hidden = true;
// 为列设置大纲级别
worksheet.getColumn(4).outlineLevel = 0;
worksheet.getColumn(5).outlineLevel = 1;
// 列支持一个只读字段,以指示基于 `OutlineLevel` 的折叠状态
expect(worksheet.getColumn(4).collapsed).to.equal(false);
expect(worksheet.getColumn(5).collapsed).to.equal(true);
// 遍历此列中的所有当前单元格
dobCol.eachCell(function(cell, rowNumber) {
// ...
});
// 遍历此列中的所有当前单元格,包括空单元格
dobCol.eachCell({ includeEmpty: true }, function(cell, rowNumber) {
// ...
});
// 添加一列新值
worksheet.getColumn(6).values = [1,2,3,4,5];
// 添加稀疏列值
worksheet.getColumn(7).values = [,,2,3,,5,,7,,,,11];
// 剪切一列或多列(右边的列向左移动)
// 如果定义了列属性,则会相应地对其进行切割或移动
// 已知问题:如果拼接导致任何合并的单元格移动,结果可能是不可预测的
worksheet.spliceColumns(3,2);
// 删除一列,再插入两列。
// 注意:第4列及以上的列将右移1列。
// 另外:如果工作表中的行数多于列插入项中的值,则行将仍然被插入,就好像值存在一样。
const newCol3Values = [1,2,3,4,5];
const newCol4Values = ['one', 'two', 'three', 'four', 'five'];
worksheet.spliceColumns(3, 1, newCol3Values, newCol4Values);
row
行,可以添加一行或者同时添加多行数据,是使用最频繁的属性。
// 获取一个行对象。如果尚不存在,则将返回一个新的空对象
const row = worksheet.getRow(5);
// Get multiple row objects. If it doesn't already exist, new empty ones will be returned
const rows = worksheet.getRows(5, 2); // start, length (>0, else undefined is returned)
// 获取工作表中的最后一个可编辑行(如果没有,则为 `undefined`)
const row = worksheet.lastRow;
// 设置特定的行高
row.height = 42.5;
// 隐藏行
row.hidden = true;
// 为行设置大纲级别
worksheet.getRow(4).outlineLevel = 0;
worksheet.getRow(5).outlineLevel = 1;
// 行支持一个只读字段,以指示基于 `OutlineLevel` 的折叠状态
expect(worksheet.getRow(4).collapsed).to.equal(false);
expect(worksheet.getRow(5).collapsed).to.equal(true);
row.getCell(1).value = 5; // A5 的值设置为5
row.getCell('name').value = 'Zeb'; // B5 的值设置为 “Zeb” - 假设第2列仍按名称键入
row.getCell('C').value = new Date(); // C5 的值设置为当前时间
// 获取行并作为稀疏数组返回
// 注意:接口更改:worksheet.getRow(4) ==> worksheet.getRow(4).values
row = worksheet.getRow(4).values;
expect(row[5]).toEqual('Kyle');
// 通过连续数组分配行值(其中数组元素 0 具有值)
row.values = [1,2,3];
expect(row.getCell(1).value).toEqual(1);
expect(row.getCell(2).value).toEqual(2);
expect(row.getCell(3).value).toEqual(3);
// 通过稀疏数组分配行值(其中数组元素 0 为 `undefined`)
const values = []
values[5] = 7;
values[10] = 'Hello, World!';
row.values = values;
expect(row.getCell(1).value).toBeNull();
expect(row.getCell(5).value).toEqual(7);
expect(row.getCell(10).value).toEqual('Hello, World!');
// 使用列键按对象分配行值
row.values = {
id: 13,
name: 'Thing 1',
dob: new Date()
};
// 在该行下方插入一个分页符
row.addPageBreak();
// 遍历工作表中具有值的所有行
worksheet.eachRow(function(row, rowNumber) {
console.log('Row ' + rowNumber + ' = ' + JSON.stringify(row.values));
});
// 遍历工作表中的所有行(包括空行)
worksheet.eachRow({ includeEmpty: true }, function(row, rowNumber) {
console.log('Row ' + rowNumber + ' = ' + JSON.stringify(row.values));
});
// 连续遍历所有非空单元格
row.eachCell(function(cell, colNumber) {
console.log('Cell ' + colNumber + ' = ' + cell.value);
});
// 遍历一行中的所有单元格(包括空单元格)
row.eachCell({ includeEmpty: true }, function(cell, colNumber) {
console.log('Cell ' + colNumber + ' = ' + cell.value);
});
// 提交给流一个完成的行
row.commit();
// 行尺寸
const rowSize = row.cellCount;
const numValues = row.actualCellCount;
添加行
// Add a couple of Rows by key-value, after the last current row, using the column keys
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)});
// Add a row by contiguous Array (assign to columns A, B & C)
worksheet.addRow([3, 'Sam', new Date()]);
// Add a row by sparse Array (assign to columns A, E & I)
const rowValues = [];
rowValues[1] = 4;
rowValues[5] = 'Kyle';
rowValues[9] = new Date();
worksheet.addRow(rowValues);
// Add a row with inherited style
// This new row will have same style as last row
// And return as row object
const newRow = worksheet.addRow(rowValues, 'i');
// Add an array of rows
const rows = [
[5,'Bob',new Date()], // row by array
{id:6, name: 'Barbara', dob: new Date()}
];
// add new rows and return them as array of row objects
const newRows = worksheet.addRows(rows);
// Add an array of rows with inherited style
// These new rows will have same styles as last row
// and return them as array of row objects
const newRowsStyled = worksheet.addRows(rows, 'i');
使用
简单的表格导出
import { saveAs } from 'file-saver';
import { Workbook } from 'exceljs';
onExportBasicExcel(){
const workbook = new ExcelJs.Workbook();
// 添加sheet
const worksheet = workbook.addWorksheet('demo sheet');
// 设置 sheet 的默认行高
worksheet.properties.defaultRowHeight = 20;
// 设置列
worksheet.columns = [
{ header: 'Id', key: 'id', width: 10 },
{ header: 'Name', key: 'name', width: 32 },
{ header: 'D.O.B.', key: 'DOB', width: 10, outlineLevel: 1 }
];
// 添加行
let list = [
{id:1,name:'test1',DOB:'11'},
{id:2,name:'test2',DOB:'22'},
{id:3,name:'test3',DOB:'33'}
]
worksheet.addRows(list);
// 导出excel
saveWorkbook(workbook, 'simple-demo.xlsx');
}
// `saveWorkbook()`是自己封装的方法,接收 workbook 和文件名来下载 excel 到本地。
// 下载是使用 `file-saver`库。
saveWorkbook(workbook, fileName) {
// 导出文件
workbook.xlsx.writeBuffer().then(data => {
const blob = new Blob([data], { type: '' });
saveAs(blob, fileName);
});
}
在
ExcelJS
中,header 字段表示显示的表头内容,key 是用于匹配数据的 key,width 是列宽。在 Table 的 column 中都有对应的字段,取出来赋值即可。
注意设置列宽的时候,在线表格和 excel 的单位可能不一致,需要除以一个系数才不至于太宽。至于具体除多少,可以不断试验得出个最佳值,我试的除以 5 效果比较好。
注意列宽,目前没查到excel的单位是什么,参考SheetJs,所以应该除以6.5
通过 worksheet.addRows()
方法可以为工作表添加多行数据,因为上面我们已经设置了表头,程序知道了每列数据应该匹配哪个字段,所以这里直接传入 Table 的 dataSource 即可。
也可以通过 worksheet.addRow()
逐行添加数据。
合并单元格
let mergesArr = [
{ row: 2, rowspan: 1, col: 1, colspan: 3 },
{ row: 8, rowspan: 2, col: 1, colspan: 2 }
];
// 按开始行,开始列,结束行,结束列合并
// worksheet.mergeCells(2, 1, 2, 3);
mergesArr.forEach(el => {
worksheet.mergeCells(el.row, el.col, el.row + el.rowspan - 1, el.col + el.colspan - 1);
});
带样式的表格导出
import { saveAs } from 'file-saver';
import { Workbook } from 'exceljs';
onExportBasicExcel(){
const workbook = new ExcelJs.Workbook();
// 添加sheet
const worksheet = workbook.addWorksheet('demo sheet');
// 设置 sheet 的默认行高
worksheet.properties.defaultRowHeight = 20;
// 设置列
worksheet.columns = [
{ header: 'Id', key: 'id', width: 10 },
{ header: 'Name', key: 'name', width: 32 },
{ header: 'D.O.B.', key: 'DOB', width: 10, outlineLevel: 1 }
];
// 添加行
let list = [
{id:1,name:'test1',DOB:'11'},
{id:2,name:'test2',DOB:'22'},
{id:3,name:'test3',DOB:'33'}
]
let rows = worksheet.addRows(list);
rows?.forEach(row => {
// 设置字体
row.font = {
size: 11,
name: '微软雅黑'
};
// 设置对齐方式
row.alignment = { vertical: 'middle', horizontal: 'left', wrapText: false };
});
// 给表头添加背景色
let headerRow = worksheet.getRow(1);
// 通过 cell 设置样式,更精准
headerRow.eachCell((cell, colNum) => {
// 设置背景色
cell.fill = {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: 'dff8ff' }
};
// 设置字体
cell.font = {
bold: true,
italic: true,
size: 12,
name: '微软雅黑',
color: { argb: 'ff0000' }
};
});
// 导出excel
saveWorkbook(workbook, 'simple-demo.xlsx');
}
// `saveWorkbook()`是自己封装的方法,接收 workbook 和文件名来下载 excel 到本地。
// 下载是使用 `file-saver`库。
saveWorkbook(workbook, fileName) {
// 导出文件
workbook.xlsx.writeBuffer().then(data => {
const blob = new Blob([data], { type: '' });
saveAs(blob, fileName);
});
}
多级表头
设置每一个表头列所要显示的信息和应该匹配的 key,但是它无法设置多级表头,所以需要换一种思路,摒弃列(表头)的概念,把表头也当成一行数据来自己写入。下面的每行数据,也都自己通过计算匹配出应该在什么位置显示什么内容。
通过 worksheet.addRow()
将表头添加为一行数据,多行表头就添加两次。
worksheet.addRow({
id: i,
name: theName,
etc: someOtherDetail
});
数据格式
支持数组对象,也支持二维数组
worksheet.addRow([3, 'Sam', new Date()]);
let rows = worksheet.addRows([
[11, 22, 33],
[111, 222, 333]
]);
样式
单元格,行和列均支持一组丰富的样式和格式,这些样式和格式会影响单元格的显示方式。
通过分配以下属性来设置样式:
添加背景色
我们先给表头添加背景。因为表头是第一行,可以通过 getRow(1)
来获取表头这一行:
// 给表头添加背景色
let headerRow = worksheet.getRow(1);
headerRow.fill = {
type: 'pattern',
pattern: 'solid',
fgColor: {argb: 'dff8ff'},
}
可以直接用 row.fill
为整行设置背景色,这样的话这一行没有内容的单元格也会有颜色,如图:
从 E 列开始其实就没有数据了,如果只想给非空单元格设置背景呢?
很遗憾 row 暴露的方法不支持直接这样设置,但可以曲线救国,遍历本行的所有非空单元格,再给每个单元格设置背景即可。
// 通过 cell 设置背景色,更精准
headerRow.eachCell((cell, colNum) => {
cell.fill = {
type: 'pattern',
pattern: 'solid',
fgColor: {argb: 'dff8ff'},
}
})
使用单元格控制会更加的精准,可以看到空的单元格已经没有背景色了。
修改字体样式
可以设置文字的字体、字号、颜色等属性,支持的属性如下表:
字体属性 | 描述 | 示例值 |
---|---|---|
name | 字体名称。 | 'Arial', 'Calibri', etc. |
family | 备用字体家族。整数值。 | 1 - Serif, 2 - Sans Serif, 3 - Mono, Others - unknown |
scheme | 字体方案。 | 'minor', 'major', 'none' |
charset | 字体字符集。整数值。 | 1, 2, etc. |
size | 字体大小。整数值。 | 9, 10, 12, 16, etc. |
color | 颜色描述,一个包含 ARGB 值的对象。 | { argb: 'FFFF0000'} |
bold | 字体 粗细 | true, false |
italic | 字体 倾斜 | true, false |
underline | 字体 下划线 样式 | true, false, 'none', 'single', 'double', 'singleAccounting', 'doubleAccounting' |
strike | 字体 | true, false |
outline | 字体轮廓 | true, false |
vertAlign | 垂直对齐 | 'superscript', 'subscript' |
与设置背景色相同,可以通过 row 或 cell 来设置。示例将通过 cell 设置。
修改表头的字体为微软雅黑,字号12号,颜色为红色,加粗斜体。
// 通过 cell 设置样式,更精准
headerRow.eachCell((cell, colNum) => {
// 设置背景色
cell.fill = {
type: 'pattern',
pattern: 'solid',
fgColor: {argb: 'dff8ff'},
}
// 设置字体
cell.font = {
bold: true,
italic: true,
size: 12,
name: '微软雅黑',
color: {argb: 'ff0000'},
};
})
设置对齐方式
---------------- | ----------- | ----- | ----- | ------- | ---- | --------- | | 水平的 | 垂直 | 文本换行 | 自适应 | 缩进 | 阅读顺序 | 文本旋转 | | left | top | true | true | integer | rtl | 0 to 90 | | center | middle | false | false | | ltr | -1 to -90 | | right | bottom | | | | | vertical | | fill | distributed | | | | | | | justify | justify | | | | | | | centerContinuous | | | | | | | | distributed
表格默认的对齐方式是靠下对齐,一般都会设置为垂直方向居中对齐,文本靠左对齐,数字靠右对齐。这里为了方便都设置为水平方向靠左对齐,垂直方向居中对齐。
// 添加行
let rows = worksheet.addRows(list);
rows?.forEach(row => {
// 设置字体
row.font = {
size: 11,
name: '微软雅黑',
};
// 设置对齐方式
row.alignment = {vertical: 'middle', horizontal: 'left', wrapText: false,};
})
addRows()
的返回值是被添加的行的数组,然后循环对每行设置字体和对齐方式,就完成了对整个 excel 的样式自定义。
当然也可以对每个 cell 进行设置,效果是一样的。
设置边框
有效边框样式
- thin
- dotted
- dashDot
- hair
- dashDotDot
- slantDashDot
- mediumDashed
- mediumDashDotDot
- mediumDashDot
- medium
- double
- thick
// 在A1周围设置单个细边框
ws.getCell('A1').border = {
top: {style:'thin'},
left: {style:'thin'},
bottom: {style:'thin'},
right: {style:'thin'}
};
// 在A3周围设置双细绿色边框
ws.getCell('A3').border = {
top: {style:'double', color: {argb:'FF00FF00'}},
left: {style:'double', color: {argb:'FF00FF00'}},
bottom: {style:'double', color: {argb:'FF00FF00'}},
right: {style:'double', color: {argb:'FF00FF00'}}
};
// 在A5中设置厚红十字边框
ws.getCell('A5').border = {
diagonal: {up: true, down: true, style:'thick', color: {argb:'FFFF0000'}}
};