专栏首页FEWYJavaScript导出excel文件,并修改文件样式

JavaScript导出excel文件,并修改文件样式

说明

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

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

示例:

安装

CDN:

<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.16.0/xlsx.min.js"></script>

npm:

npm install xlsx-style --save

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

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

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

bower:

bower install js-xlsx-style#beta

单元格对象、工作表对象和工作簿对象解释

Cell Object

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:单元格的样式

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

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 属性。这个属性的值也是一个对象,它有五个属性:fillfontnumFmtalignmentborder

样式属性

子属性

描述

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 指设定颜色的对象,取值如下:

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;
  • 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 有两个输出数据的方法 writewriteFile 方法,需要注意的是 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 方法实现下载:

XLSX.writeFile(workbook, "out.xlsx");

虽然看上去使用 writeFile 方法要比 write 方法简单很多,但是要注意 writeFile 方法只能在 node 环境下使用。

总结

前端导出 excel 文件,并修改导出文件样式的功能最重要的就是准备好符合结构的工作簿对象(Workbook Object),在这个步骤设定好要导出文件的样式,而后面的步骤都是固定的函数,就比较简单了。

文中 Excel 相关的截图,均是在 Microsoft Excel 2016 版截取的,不同版本的 Excel 显示可能稍有不同。

如果你觉得 xlsx-style 的功能还不够全面,不能实现你的需求,这里再推荐一个项目 ExcelJS,这个项目的功能更加全面,而且项目也还在维护,可以试试看能否满足需求。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 简单说 正则表达式——要注意lastIndex属性

    每个RegExp对象都包含5个属性,source、global、ignoreCase、multiline、lastIndex。 source:是一个只读的字...

    FEWY
  • 简单说 CSS的vertical-align

    vertical-align属性,是CSS属性中一个比较重要的属性,也是比较不好理解的一个。 我们今天就来说说它。

    FEWY
  • JavaScript实现 满天星 导航栏

    实现这个效果,需要掌握的知识不用很多,知道简单的CSS,会用JS 获取元素, 能绑定事件基本就足够了。 好的,我们直接来看代码,注释已经写的很详细了,不想看...

    FEWY
  • Spread for Windows Forms高级主题(2)---理解单元格类型

    理解单元格类型基本信息 Spread支持几十种单元格类型,如复选框单元格、日期时间单元格、或者一个简单的文本单元格。单元格类型可以对单独的单元格、列、行、一个单...

    葡萄城控件
  • Excel小技巧23:便捷的Ctrl键

    使用Ctrl键来选择不相邻的单元格或单元格区域,恐怕是很多使用Excel的人最喜爱的功能了。例如,你可以按照下面的操作来选择单元格:

    fanjy
  • Range单元格对象常用方法(一)

    今天介绍的单元格对象方法在平时使用excel也是常规的操作,如单元格的复制、剪切、删除、清除内容等。只是在VBA编程中是通过代码的形式来运行。

    无言之月
  • Range单元格对象方法(三)Specialcells方法

    大家好,上节介绍了单元格的自动筛选AutoFilter方法。在结尾处引出了单元格Speicalcells方法,利用它删除可见单元格。本节就介绍快速定位特定单元格...

    无言之月
  • 巧用格式刷解决合并单元格无法统计问题

    最近小编一直在分享Python,有朋友和我聊! 我是谁!我来自哪里!我要去往何处! 好吧,为了能对得起我的名字《阿凯的Excel》 本小编决定每周不少于一...

    用户1332619
  • Spread for Windows Forms快速入门(7)---单元格的交互操作

    单元格的编辑模式 通常情况下,当终端用户双击单元格时,编辑控件将允许用户在该单元格中输入内容。在一个单元格中编辑的能力被称为编辑模式。一些属性和方法可以用来自定...

    葡萄城控件
  • Spread for Windows Forms高级主题(5)---数据处理

    使用表单的API处理数据 你可以将数据以有格式或无格式字符串或者数据对象的形式填充到单元格中。将数据填充到单元格的最好方式取决于你想添加字符串数据还是数据对象,...

    葡萄城控件

扫码关注云+社区

领取腾讯云代金券