Export2Excel使用及配置说明

Sunny3096

已于 2022-06-10 10:59:59 修改

阅读量8.3k

点赞数 5

分类专栏：前端-Vue 文章标签：前端

于 2022-06-09 19:22:43 首次发布

原文链接：https://segmentfault.com/a/1190000022772664

版权

前端-Vue 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

因要实现前端导出 excel 文件，并且对导出文件的样式进行一些修改，比如颜色、字体、合并单元格等，所以使用 xlsx-style 这个项目，它可以对导出的 excel 文件进行一些样式上的修改，这个项目是 SheetJS 的一个分支。其实 SheetJS 也是支持修改导出文件的样式的，不过是在它的专业版中， SheetJS 分为社区版和专业版的，社区版是开源的，但是却不支持修改导出文件的样式，专业版拥有更多的功能，这其中就包括修改样式，但是如果需要使用专业版，要邮件联系 SheetJS 的开发者，去咨询价格，购买它。

下来说说如何使用 xlsx-style ，导出 excel 文件，并修改样式。

一、安装

浏览器：

<script src="dist/xlsx.full.min.js"></script>

npm：

npm install xlsx-style --save

如果 npm 安装时出现如下报错，

需要修改源码：
1、在\node_modules\xlsx-style\dist\cpexcel.js 807行把 var cpt = require('./cpt' + 'able'); 改成 var cpt = cptable;

2、在\node_modules\xlsx-style\ods.js 10行和13行把路径改为 require('./xlsx')

bower：

bower install js-xlsx-style#beta

二、属性说明

Cell Object

指单元格对象，格式为 {c:C, r:R}，其中 C 代表列号，R 代表行号。

例如单元格 B5 用对象表示就是 {c:1, r:4}。

单元格范围用对象表示为{s:S, e:E}，其中 S 表示第一个单元格对象，E 表示最后一个单元格对象。

例如单元格范围 A3:B7 由对象表示就是 {s:{c:0, r:2}, e:{c:1, r:6}}。

在工作表对象中设置一个单元格对象，是以编码后的单元格为属性，进行设置。
例如：

//设置A1单元格的值是123，类型是字符串，字体颜色是FF0187FA
worksheet["A1"] = {
    v: "123",
    t: "s",
    s: {
        font: {
            color: { rgb: "FF0187FA" }
        },
    }
}

v：单元格的值
t：单元格的类型 'b'布尔值、'n'数字、'e'错误、's'字符串、'd'日期
s：单元格的样式

单元格的属性不止有v、t、s这个三个属性，还有其他属性，具体看这里，但是实现导出功能，使用这三个已经足够了。

在 XLSX.utils 对象中有一些方法可以对单元格和单元格范围进行转化。

//编码行号
XLSX.utils.encode_row(2);  //"3"
//解码行号
XLSX.utils.decode_row("2"); //1

//编码列标
XLSX.utils.encode_col(2);  //"C"
//解码列标 
XLSX.utils.decode_col("A"); //0

//编码单元格
XLSX.utils.encode_cell({ c: 1, r: 1 });  //"B2"
//解码单元格
XLSX.utils.decode_cell("B1"); //{c: 1, r: 0}

//编码单元格范围
XLSX.utils.encode_range({ s: { c: 1, r: 0 }, e: { c: 2, r: 8 } });  //"B1:C9"
//解码单元格范围
XLSX.utils.decode_range("B1:C9"); //{s:{c: 1, r: 0},e: {c: 2, r: 8}}

Worksheet Object

Worksheet Object 指工作表对象，这个对象中每个不以!开头的属性，都代表一个单元格。

例如

worksheet["A1"] 返回A1单元格对象。

worksheet['!ref']：表示工作表范围的字符串。
例如：

worksheet['!ref'] = "A1:B5"
worksheet['!cols']：存储列对象的数组，可以在这里设置列宽。
例如：

//wpx 字段表示以像素为单位，wch 字段表示以字符为单位
worksheet['!cols'] = [
  { wpx: 200 }, //设置第1列列宽为200像素
  { wch: 50 },  //设置第2列列宽为50字符
];
worksheet['!merges']：存储合并单元格范围的数组。
例如：

//合并B2到D4范围内的单元格
worksheet['!merges'] = [
  {
      s: { c: 1, r: 1 }, //B2
      e: { c: 3, r: 3 }, //D4
  }
]
worksheet['!freeze']：冻结单元格。
例如：

//冻结第一行和第一列：
worksheet['!freeze'] = {
  xSplit: "1",  //冻结列
  ySplit: "1",  //冻结行
  topLeftCell: "B2",  //在未冻结区域的左上角显示的单元格，默认为第一个未冻结的单元格
  state: "frozen"
}

以下是打印相关的设置

worksheet['!rowBreaks']：行分页数组。
例如：

//第一行为一页，第二行和第三行为一页，第三行之后为一页
worksheet['!rowBreaks'] = [1,3]

worksheet['!colBreaks']：列分页数组。
例如：

//第一列为一页，第二列和第三列为一页，第三列之后为一页
worksheet['!colBreaks'] = [1,3]

worksheet['!pageSetup']：设置缩放大小和打印方向的对象。
例如：

//缩放100%，打印方向为纵向
worksheet['!pageSetup'] = {
  scale: '100',
  orientation: 'portrait'
}
// orientation 取值如下：
// 'portrait'  - 纵向    
// 'landscape' - 横向

worksheet['!printHeader']：需要重复的第一行和最后一行索引的数组，用于分页时重复打印表头。
例如：
```
//分页时重复打印第一行
worksheet['!printHeader'] = [1,1]
```

Workbook Object

Workbook Object 指工作簿对象。

workbook.SheetNames：存储工作表名称的数组。
workbook.Sheets：存储工作表对象的对象。
workbook.Sheets[sheetname]：返回对应名称的工作表对象。

三、单元格样式

设置单元格的样式，就是设置工作表对象中的单元格对象的 s 属性。这个属性的值也是一个对象，它有五个属性：fill、font、numFmt、alignment和border。

样式属性	子属性	值	描述
fill	patternType	`PatternTypes`	图案样式
	bgColor	`COLOR_SPEC`	背景色，设置填充时的图案颜色
	fgColor	`COLOR_SPEC`	前景色，单元格的背景颜色
font	name	表示字体名称的字符串	字体名称，默认值是 `"Calibri"`
	sz	表示字号的数字	字号
	color	`COLOR_SPEC`	字体颜色
	bold	`true` 或 `false`	加粗
	underline	`true` 或 `false`	下划线
	italic	`true` 或 `false`	倾斜
	strike	`true` 或 `false`	删除线
	vertAlign	`'superscript'` 或 `'subscript'`	上标或下标
numFmt		字符串或数字	对数字类型的单元格进行格式化
alignment	vertical	`"bottom"`、`"center"` 或 `"top"`	垂直对齐
	horizontal	`"left"`、`"center"` 或 `"right"`	水平对齐
	wrapText	`true` 或 `false`	自动换行
	readingOrder	`0`、`1` 或 `2`	文字方向
	textRotation	数字，`0`至`180` 或 `255` （默认为`0`）	文本旋转角度
		`45`	逆时针旋转45度
		`90`	逆时针旋转90度
		`135`	顺时针旋转45度
		`180`	顺时针旋转90度
		`255`	垂直排列
border	top	`{ style: BORDER_STYLE, color: COLOR_SPEC }`	上边框样式
	bottom	`{ style: BORDER_STYLE, color: COLOR_SPEC }`	下边框样式
	left	`{ style: BORDER_STYLE, color: COLOR_SPEC }`	左边框样式
	right	`{ style: BORDER_STYLE, color: COLOR_SPEC }`	右边框样式
	diagonal	`{ style: BORDER_STYLE, color: COLOR_SPEC }`	对角线样式
	diagonalUp	`true` 或 `false`	上对角线
	diagonalDown	`true` 或 `false`	下对角线

PatternTypes

PatternTypes 指填充时的图案样式，取值如下：

none
solid
darkGray
mediumGray
lightGray
gray125
gray0625
darkHorizontal
darkVertical
darkDown
darkUp
darkGrid
darkTrellis
lightHorizontal
lightVertical
lightDown
lightUp
lightGrid
lightTrellis

在 Excel 中点击单元格，选择设置单元格格式，点击填充会出现图案样式选项。

COLOR_SPEC

COLOR_SPEC 指设定颜色的对象，取值如下：

{ rgb: "FFFFAA00" } 十六进制ARGB值（Hex），这里有一个网页可以进行颜色值转换
{ theme: "1"} 主题颜色的整数索引，默认是0。

numFmt

样式属性 numFmt 的作用是对数字类型的单元格进行格式化。在 xlsx-style 中内置一个 table_fmt 对象，这个对象存储了一些进行格式化的规则。

var table_fmt = {
    0: 'General',
    1: '0',
    2: '0.00',
    3: '#,##0',
    4: '#,##0.00',
    9: '0%',
    10: '0.00%',
    11: '0.00E+00',
    12: '# ?/?',
    13: '# ??/??',
    14: 'm/d/yy',
    15: 'd-mmm-yy',
    16: 'd-mmm',
    17: 'mmm-yy',
    18: 'h:mm AM/PM',
    19: 'h:mm:ss AM/PM',
    20: 'h:mm',
    21: 'h:mm:ss',
    22: 'm/d/yy h:mm',
    37: '#,##0 ;(#,##0)',
    38: '#,##0 ;[Red](#,##0)',
    39: '#,##0.00;(#,##0.00)',
    40: '#,##0.00;[Red](#,##0.00)',
    45: 'mm:ss',
    46: '[h]:mm:ss',
    47: 'mmss.0',
    48: '##0.0E+0',
    49: '@',
    56: '"上午/下午 "hh"時"mm"分"ss"秒 "'
};

numFmt 的取值如下：

1、table_fmt 对象的属性值。
例如：
```
worksheet["A1"].s.numFmt = "0.00%";
```

2、table_fmt 对象的属性名对应的数字。
例如：

worksheet["A1"].s.numFmt = 0;worksheet["A1"].s.numFmt = 0;

3、Excel 内置规则对应的字符串。
例如：
```
worksheet["A1"].s.numFmt = "yyyy/m/d h:mm";
```
在 Excel 中对应的操作就是，右键单元格，选择设置单元格格式，选择自定义，选择 yyyy/m/d h:mm 类型。
4、如果熟悉 Excel 中的自定义格式，可以使用自定义格式的字符串。
例如：
```
//单元格输入1时显示男，输入0时显示女 
worksheet["A1"].s.numFmt = '[=1]"男";[=0]"女"';
```
在 Excel 中对应的操作就是，右键单元格，选择设置单元格格式，选择自定义，在类型中输入 [=1]"男";[=0]"女"。

readingOrder

样式属性 alignment 的子属性 readingOrder 表示单元格的文字方向。语言的阅读与书写顺序并不都是从左到右的，比如阿拉伯语就是从右到左的。

readingOrder 的取值如下：

0：根据内容决定
1：从左到右
2：从右到左

在 Excel 中对应的操作就是，右键单元格，选择设置单元格格式，在对齐选项中设置文字方向。

BORDER_STYLE

BORDER_STYLE 是用来设置边框样式的一个字符串，可用取值如下：

thin

medium

thick

dotted

hair

dashed

mediumDashed

dashDot

mediumDashDot

dashDotDot

mediumDashDotDot

slantDashDot

double

合并单元格的边框是合并区域内的每个单元格指定的。因此，如果需要设置3x3单元格合并后的单元格边框，需要为8个不同的单元格设置边框：

左侧单元格的左边框
右侧单元格的右边框
顶部单元格的上边框
底部单元格的下边框

导出文件

xlsx-style 有两个输出数据的方法 write 和 writeFile 方法，需要注意的是 writeFile 方法需要基于 node 环境才可以使用。

XLSX.write(workbook, wopts);
XLSX.writeFile(workbook, filename, wopts);

filename：
writeFile 方法需要传入 filename 参数，也就是要创建文件的名称，也可以是路径。
例如：

XLSX.writeFile(workbook, "out.xlsx", wopts);
XLSX.writeFile(workbook, "./folder/out.xlsx", wopts);

wopts：

属性名	默认值	描述
type		输出数据类型（请参见下面的输出类型）
cellDates	false	将日期存储为类型`'d'`(默认为`'n'`)
bookSST	false	是否生成共享字符串表
bookType	`'xlsx'`	工作簿的类型(`xlsx`、`xlsm` 或 `xlsb`)
showGridLines	true	是否显示网格线
Props	null	工作簿的属性

type：

值	描述
`"base64"`	Base64编码
`"binary"`	二进制字符串
`"buffer"`	`nodejs` 缓冲区
`"file"`	直接创建文件（`node` 环境下有效）

如果使用 write 方法需要设置 type 属性，而且如果设置 type 属性为 file，还需要在 wopts 参数中增加一个 file 属性，值是要创建文件的路径。

如果使用 writeFile 方法不需要设置 type 属性，因为在 xlsx-style 的源码中已经将 type 属性设置为 file 了，而且使用这个方法，也不需要在 wopts 参数中设置 bookType 属性，因为 bookType 是通过第二个参数 filename 来判断的。

bookSST：
bookSST 设置为 true 时会生成共享字符串表。
SST 指共享字符串表，一个工作簿可能有成千上万个包含字符串（非数字）数据的单元格。而这些单元格中可能有许多重复数据。实现共享字符串表是为了通过仅读取和写入重复数据一次来提高打开和保存文件的性能。
更详情的解释可以参阅以下内容：
Working with the shared string table (Open XML SDK)
Exporting to excel using xlsx library and SST

Props：

Props 可以设置为一个对象，存入以下与工作簿相关的信息：

属性名	描述
`"title"`	标题
`"subject"`	主题
`"creator"`	创建者
`"keywords"`	关键字
`"description"`	描述

设置这些属性后，在 Excel 中点击文件，选择信息，然后点击属性，然后再点击高级属性，最后选择摘要就可以看到了。

使用 write 方法实现下载：

var wopts = { bookType: 'xlsx', type: 'binary' };
var wbout = XLSX.write(workbook, wopts);

function s2ab(s) {
    var buf = new ArrayBuffer(s.length);
    var view = new Uint8Array(buf);
    for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
    return buf;
}
var blob = new Blob([s2ab(wbout)], { type: "application/octet-stream" });

function saveAs(obj, filename) {
    var link = document.createElement("a");
    link.download = filename;
    link.href = URL.createObjectURL(obj);
    link.click();
    URL.revokeObjectURL(obj);
}
saveAs(blob, "out.xlsx");

原理就是，把 write 方法输出的数据，用 s2ab 方法转为 Uint8Array 对象，然后通过 new Blob 再得到一个 Blob 对象，再通过 URL.createObjectURL 方法将 Blob 对象作为参数，得到一个对象URL，最后把对象URL设置为临时的一个 a 链接的 href 属性值，实现下载功能。

使用 writeFile 方法实现下载：