ExcelJS

2,606 阅读12分钟

简介

读取,操作并写入电子表格数据和样式到 XLSX 和 JSON 文件。
一个 Excel 电子表格文件逆向工程项目。

ExcelJS 周下载量 450k,github star 9k,并且拥有中文文档,对国内开发者很友好。虽然文档是以README 的形式,可读性不太好,但重在内容,常用的功能基本都有覆盖。

本文版本

image.png

对比

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

image.png

通过 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为整行设置背景色,这样的话这一行没有内容的单元格也会有颜色,如图:

image.png

从 E 列开始其实就没有数据了,如果只想给非空单元格设置背景呢?

很遗憾 row 暴露的方法不支持直接这样设置,但可以曲线救国,遍历本行的所有非空单元格,再给每个单元格设置背景即可。

// 通过 cell 设置背景色,更精准
headerRow.eachCell((cell, colNum) => {
  cell.fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: {argb: 'dff8ff'},
  }
})

image.png

使用单元格控制会更加的精准,可以看到空的单元格已经没有背景色了。

修改字体样式

可以设置文字的字体、字号、颜色等属性,支持的属性如下表:

字体属性描述示例值
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'}}
};