导出自定义Excel表格
序:
最近做的项目涉及到了excel的导出,需要满足以下条件:
- 实现excel导出
- 兼容ie11
- 统计数据
- 修改宽高、合并单元格
- 修改其它样式
一、js-xlsx 介绍
xlsx
由SheetJS出品的js-xlsx
是一款非常方便的只需要纯JS即可读取和导出excel的工具库,功能强大,支持格式众多,支持xls
、xlsx
、ods
(一种OpenOffice专有表格文件格式)等十几种格式。本文全部都是以xlsx
格式为例。
1、兼容性![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lAdWnrl7-1647329954432)(C:\Users\huawei\AppData\Roaming\Typora\typora-user-images\image-20220210101914502.png)]](https://i-blog.csdnimg.cn/blog_migrate/754f2454c03abf8bbf0dec5ca4046e73.png)
2、使用
xlsx
目录的dist
目录下有很多个JS文件,一般情况下用xlsx.core.min.js
就够了,xlsx.full.min.js
则是包含了所有功能模块。
2.1.直接script
标签引入即可:
<script type="text/javascript" src="./js/xlsx.core.min.js"></script>
2.2.引入XLSX
安装插件
npm install xlsx --save
在组件中引入XLSX
import XLSX from 'xlsx'
3、读取excel
读取excel主要是通过XLSX.read(data, {type: type});
方法来实现,返回一个叫WorkBook
的对象,type主要取值如下:
base64
: 以base64方式读取;binary
: BinaryString格式(byte n is data.charCodeAt(n))string
: UTF8编码的字符串;buffer
: nodejs Buffer;array
: Uint8Array,8位无符号数组;file
: 文件的路径(仅nodejs下支持);
3.1. 获取workbook对象
3.1.1.读取本地文件
// 读取本地excel文件
function readWorkbookFromLocalFile(file, callback) {
var reader = new FileReader();
reader.onload = function(e) {
var data = e.target.result;
var workbook = XLSX.read(data, {
type: 'binary'});
if(callback) callback(workbook);
};
reader.readAsBinaryString(file);
}
3.1.2. 读取网络文件
// 从网络上读取某个excel文件,url必须同域,否则报错
function readWorkbookFromRemoteFile(url, callback) {
var xhr = new XMLHttpRequest();
xhr.open('get', url, true);
xhr.responseType = 'arraybuffer';
xhr.onload = function(e) {
if(xhr.status == 200) {
var data = new Uint8Array(xhr.response)
var workbook = XLSX.read(data, {
type: 'array'});
if(callback) callback(workbook);
}
};
xhr.send();
}
3.2. 详解 workbook
3.2.1. Workbook Object
workbook
里面有什么东西呢,我们打印出来看一下:
可以看到,SheetNames
里面保存了所有的sheet名字,然后Sheets
则保存了每个sheet的具体内容(我们称之为Sheet Object
)。每一个sheet
是通过类似A1
这样的键值保存每个单元格的内容,我们称之为单元格对象(Cell Object
):
3.2.2. Sheet Object
每一个Sheet Object
表示一张表格,只要不是!
开头的都表示普通cell
,否则,表示一些特殊含义,具体如下:
-
sheet['!ref']
:表示所有单元格的范围,例如从A1到F8则记录为A1:F8
; -
sheet[!merges]
:存放一些单元格合并信息,是一个数组,每个数组由包含s
和e
构成的对象组成,s
表示开始,e
表示结束,r
表示行,c
表示列; -
sheet['!cols']
:列属性对象的数组。列宽实际上以标准化方式存储在文件中,以“最大数字宽度”(渲染数字 0-9 的最大宽度,以像素为单位)衡量。解析时,列对象存储字段中的像素宽度、wpx
字段中的字符宽度以及wch
字段中的最大数字宽度MDW
。比如'!cols': [{wpx: 45}, {wpx: 165}, {wpx: 45}, {wpx: 45}]
[ { hidden? : boolean, //列的显示true或隐藏false /* column width is specified in one of the following ways: */ wpx? : number, // 列宽 单位是像素 width? : number, // width in Excel's "Max Digit Width", width*256 is integral wch? : number, // width in characters /* other fields for preserving features from files */ MDW? : number, // Excel's "Max Digit Width" unit, always integral } ]
-
sheet['!rows']
: 行属性对象数组,如文档后面所述。每个行对象都对包括行高和可见性在内的属性进行编码。 -
等等;
关于单元格合并,看懂下面这张图基本上就没问题了:
结果如下:
3.2.3. 单元格对象
每一个单元格是一个对象(Cell Object
),主要有t
、v
、r
、h
、w
等字段(详见这里):
- t:表示内容类型,
s
表示string类型,n
表示number类型,b
表示boolean类型,d
表示date类型,等等- v:表示原始值;
- f:表示公式,如
B2+B3
;- h:HTML内容
- w:格式化后的内容
- r:富文本内容
rich text
- s: 单元格的样式
font: {name: '宋体', sz: 30, bold: true},
;alignment: { horizontal: 'center',vertical: 'center',wrap_text: true}
3.2.4. 读取workbook
普通方法:
// 读取 excel文件
function outputWorkbook(workbook) {
var sheetNames = workbook.SheetNames; // 工作表名称集合
sheetNames.forEach(name => {
var worksheet = workbook.Sheets[name]; // 只能通过工作表名称来获取指定工作表
for(var key in worksheet) {
// v是读取单元格的原始值
console.log(key, key[0] === '!' ? worksheet[key] : worksheet[key].v);
}
});
}
根据!ref
确定excel的范围,再根据!merges
确定单元格合并(如果有),最后输出整个table,比较麻烦,幸运的是,插件自身已经写好工具类XLSX.utils
给我们直接使用,无需我们自己遍历,工具类输出主要包括如下:
有些不常用,常用的主要是:
XLSX.utils.sheet_to_csv
:生成CSV格式XLSX.utils.sheet_to_txt
:生成纯文本格式XLSX.utils.sheet_to_html
:生成HTML格式XLSX.utils.sheet_to_json
:输出JSON格式
常用的主要是sheet_to_csv
或者sheet_to_html
,转csv的话会忽略格式、单元格合并等信息,所以复杂表格可能不适用。转html的话会保留单元格合并,但是生成的是<html></html>
代码,而不是<table></table>
,需要对表格进行一些定制时不太方便,所以具体还是要视情况来采用合适的工具类。
这里写一个采用转csv方式输出结果的简单示例,可点击这里查看在线DEMO:
function readWorkbook(workbook)
{
var sheetNames = workbook.SheetNames; // 工作表名称集合
var worksheet = workbook.Sheets[sheetNames[0]]; // 这里我们只读取第一张sheet
var csv = XLSX.utils.sheet_to_csv(worksheet);
document.getElementById('result').innerHTML = csv2table(csv);
}
// 将csv转换成简单的表格,会忽略单元格合并,在第一行和第一列追加类似excel的索引
function csv2table(csv)
{
var html = '<table>';
var rows = csv.split('\n');
rows.pop(); // 最后一行没用的
rows.forEach(function(row, idx) {
var columns = row.split(',');
columns.unshift(idx+1); // 添加行索引
if(idx == 0) {
// 添加列索引
html += '<tr>';
for(var i=0; i<columns.length; i++) {
html += '<th>' + (i==0?'':String.fromCharCode(65+i-1)) + '</th>';
}
html += '</tr>';
}
html += '<tr>';
columns.forEach(function(column) {
html += '<td>'+column+'</td>';
});
html += '</tr>';
});
html += '</table>';
return html;
}
4、导出excel的逻辑:
- excel整个表格专业名词是
workbook
,里面每张表格是sheet
- 页面引入xlsx的库,
https://unpkg.com/xlsx/dist/xlsx.core.min.js
- 把数据生成sheet,
var sheet = XLSX.utils.json_to_sheet(jsonData)
,json_to_sheet是将由对象组成的数组转化成sheet,当然还有 aoa_to_sheet将一个二维数组转成sheet 和 table_to_sheet将table的dom直接转成sheet - 创建虚拟的workbook,
var wb = XLSX.utils.book_new()
- 把sheet添加到workbook里,
XLSX.utils.book_append_sheet(wb, sheet, "这里是sheetName");
- 把workbook转成blob,
var blob = workbook2blob(wb)
,这里workbook2blob需要手动写啦; - 利用a标签和createObjectURL实现下载功能,
openDownloadDialog(blob, 'excel的标题.xlsx');
,这里openDownloadDialog也会在下面放上代码
4.1.手动生成
导出excel文件,主要是如何生成一个sheet
,我们这里写一个最简单的csv转excel示例:
// csv转sheet对象
function csv2sheet(csv) {
var sheet = {
}; // 将要生成的sheet
csv = csv.split('\n');
csv.forEach(function(row, i) {
row = row.split(',');
if(i == 0) sheet['!ref'] = 'A1:'+String.fromCharCode(65+row.length-1)+(csv.length-1);
row.forEach(function(col, j) {
sheet[String.fromCharCode(65+j)+(i+1)] = {
v: col};
});
});
return sheet;
}
// 将一个sheet转成最终的excel文件的blob对象,然后利用URL.createObjectURL下载
function sheet2blob(sheet, sheetName) {
sheetName = sheetName || 'sheet1';
var workbook = {
SheetNames: [sheetName],
Sheets: {
}
};
workbook.Sheets[sheetName] = sheet;
// 生成excel的配置项
var wopts = {
bookType: 'xlsx', // 要生成的文件类型
bookSST: false, // 是否生成Shared String Table,官方解释是,如果开启生成速度会下降,但在低版本IOS设备上有更好的兼容性
type: 'binary'
};
var wbout = XLSX.write(workbook, wopts);
var blob = new Blob([s2ab(wbout)], {
type:"application/octet-stream"});
// 字符串转ArrayBuffer
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;
}
return blob;
}
拿到上面的blob对象就可以直接下载了:
/**
* 通用的打开下载对话框方法,没有测试过具体兼容性
* @param url 下载地址,也可以是一个blob对象,必选
* @param saveName 保存文件名,可选
*/
function openDownloadDialog(url, saveName)
{
if(typeof url == 'object' && url instanceof Blob)
{
url = URL.createObjectURL(url); // 创建blob地址
}
var aLink = document.createElement('a');
aLink.href = url;
aLink.download = saveName || ''; // HTML5新增的属性,指定保存文件名,可以不要后缀,注意,file:///模式下不会生效
var event;
if(window.MouseEvent) event = new MouseEvent('click');
else
{
event = document.createEvent('MouseEvents');
event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
}
aLink.dispatchEvent(event);
}
/**
* 通用的打开下载对话框方法,没有测试过具体兼容性
* @param url 下载地址,也可以是一个blob对象,必选
* @param saveName 保存文件名,可选
*/
function openDownloadDialog(url, saveName)
{
if(typeof url == 'object' && url instanceof Blob)
{
url = URL.createObjectURL(url); // 创建blob地址
}
var aLink = document.createElement('a');
aLink.href = url;
aLink.download = saveName || ''; // HTML5新增的属性,指定保存文件名,可以不要后缀,注意,file:///模式下不会生效
var event;