有个需求是上传一个文件,读取里面的内容,然后读取第三方的收费接口,然后将结果导出Excel,更是由于客户需要轻量级的应用,并不想进行配置运行环境和服务器部署,所以才有了这种需要前端来读写Excel的需求,博主在这里是用vue搭建了前端项目,最后用electron打包成PC端的可执行应用。在这里我们只介绍读写Excel。
首先需要用到的插件:
读取Excel的插件js-xlsx
npm install xlsx
这个插件亦可以进行Excel的导出,但是普通版本导出无法进行样式的设置,pro版本是收费的,所以还需要另一个封装的版本xlsx-style
npm install xlsx-style --save
接下来,是我画的页面:
<template>
<div class="content">
<div class="filebox">
<el-upload
class="file"
drag
action="#"
:multiple="false"
accept=".xlsx,.xls"
:on-change="testUpload"
:auto-upload="false"
:on-remove="handleRemove"
>
<i class="el-icon-upload"></i>
<div class="el-upload__text">将文件拖到此处,或<em>点击上传</em></div>
<div class="el-upload__tip" slot="tip">只能上传xls/xlsx文件</div>
</el-upload>
</div>
<!-- <input type="file" @change="testUpload" /> -->
<div class="btnbox">
<button class="btn" @click="open818">下载基本信息</button>
<button class="btn" @click="open848">下载风险信息</button>
<button class="btn" @click="open988">下载机构信息</button>
</div>
</div>
</template>
在这里使用了elementUI的上传插件,主要我写的样式不好看,功能也没有它的强大,需要注意的是:
- auto-upload(自动上传)设置为false,因为我并不需要真的上传文件,我只是需要读取excel里面的文件数据。
- action为必填项,不然控制台会一直报错,所以给个假地址
最终博主的页面大概如下,因为并未考虑所有浏览器的兼容性,CSS就不贴出来了:
然后我们要实现on-change的绑定事件,每当文件变动就会读取
import XLSX from "xlsx";
// 读取excel数据并且转换为json
testUpload(file) {
const reader = new FileReader();
reader.onload = function (ev) {
const data = ev.target.result;
// 使用XLSX的read方法可以直接读取
const workbook = XLSX.read(data, { type: "binary" });
const sheetNames = workbook.SheetNames; // 工作表名称集合
const worksheet = workbook.Sheets[sheetNames[0]]; // 这里我们只读取第一张sheet 主要是调取查询参数信息
// XLSX模块内置了工具类,可以将sheet对象直接转换为json格式的类型,key就是excel的表头
const json = XLSX.utils.sheet_to_json(worksheet);
let jsonstr = JSON.stringify(json);
// 将json存储以备调用接口,你亦可以存放在store中
sessionStorage.clear();
sessionStorage.setItem("json", jsonstr);
};
// 这里是读取文件,只有这里执行,上面的onload才会执行
reader.readAsBinaryString(file.raw);
},
需要说明的地方:
- on-change会传入文件对象,可以打印一下看看,file.raw就是blob文件
默认的input[type='file'],文件是file.target.files,files是个数组
- XLSX.read方法的一点简单说明:
读取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下支持) |
- XLSX.utils工具类的一些方法介绍:
将workbook转换为所需要的格式,内置了工具类,方法如下 | |
XLSX.utils.sheet_to_csv | 生成CSV格式 |
XLSX.utils.sheet_to_txt | 生成纯文本格式 |
XLSX.utils.sheet_to_html | 生成HTML格式 |
XLSX.utils.sheet_to_json | 输出JSON格式 |
这个时候excel的读取就完成了,你在控制台已经可以看到读取到的json数据了,博主的逻辑是需要点击按钮才会调用接口,所以在此只是把json暂时存储起来,具体的需求请根据自己的业务进行实际更改,
接下来是导出了,导出我要先介绍一下XLSX.utils工具类里的几个导出方法:
导出的方法,返回worksheet对象 | |
aoa_to_sheet | 将一个二维数组转成sheet |
table_to_sheet | 将一个table dom直接转成sheet,会自动识别colspan和rowspan并将其转成对应的单元格合并 |
json_to_sheet | 将一个由对象组成的数组转成sheet |
在这里常用的其实是aoa_to_sheet,前端进行数据处理会简单一点,数据格式如下:
var aoa = [
["姓名", "性别", "年龄", "注册时间"],
["张三", "男", 18, new Date()],
["李四", "女", 22, new Date()],
]
所以博主下载的方法如下:
// 查询基本信息
export async function getInfo() {
let json = sessionStorage.getItem("json");
let data = JSON.parse(json);
// 构建二维数组,准备生成excel,第一个位列名
let exceldata = [
[
"统一社会信用代码",
"企业名",
"企业评分"
]
];
// 循环调用接口,因为接口并未提供批量操作,请根据自己的业务进行更改
for (const item of data) {
await getBaseinfo818(item).then(res => {
exceldata.push([
res.creditCode ?? "",
res.name ?? "",
res.percentileScore ?? ""
]);
});
}
const sheet = XLSX.utils.aoa_to_sheet(exceldata);
openDownloadDialog(sheet2blob(sheet), "基本信息.xlsx")
}
sheet2blob:将worksheet对象转换为blob,并设置一部分excel样式:
import XLSXSTYLE from "xlsx-style"
export function sheet2blob(sheet, sheetName) {
sheetName = sheetName || 'sheet1';
var workbook = {
SheetNames: [sheetName],
Sheets: {}
};
// 设置单元格格式
setExlStyle(sheet)
workbook.Sheets[sheetName] = sheet;
// 生成excel的配置项
var wopts = {
bookType: 'xlsx', // 要生成的文件类型
bookSST: false, // 是否生成Shared String Table,官方解释是,如果开启生成速度会下降,但在低版本IOS设备上有更好的兼容性
type: 'binary'
};
// 注意这里不是用XLSX来写了,因为不支持样式
var wbout = XLSXSTYLE.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;
}
// 设置excel单元格样式
function setExlStyle(data) {
let borderAll = { //单元格外侧框线
top: {
style: 'thin',
},
bottom: {
style: 'thin'
},
left: {
style: 'thin'
},
right: {
style: 'thin'
}
};
data['!cols'] = [];
let fgColor = ""
for (let key in data) {
if (data[key] instanceof Object) {
// 设置首行颜色,key实际就是excel的坐标,如:A1,B5,CH4。如果有更好的办法欢迎留言
if(/^[A-Z]+1$/.test(key)){
fgColor = "D8D8D8"
}else{
fgColor = "FFFFFF"
}
data[key].t = "String"; // 设置单元格格式为文本类型
data[key].s = {
border: borderAll,
alignment: {
horizontal: 'center', //水平居中对齐
vertical: 'center'
},
font: {
sz: 11
},
fill: {
fgColor: {rgb: fgColor},
},
bold: true,
// numFmt: 0
}
// 设置列宽
data['!cols'].push({ wpx: 150 });
}
}
return data;
}
关于样式的设置,单元格的合并,建议还是看官方文档,博主仅仅是抛砖引玉为大家提供思路,介于一定会有人疑问data[key].t和data[key].s是什么,还是把官方文档拿过来给大家看一下,英语不好,翻译有问题的地方还请见谅:
key值 | 描述 |
v | 初始值 |
w | 格式化的文本(如果适用) |
t | 单元格格式:Boolean, Number, error, String, Date |
f | 单元格公式(如果适用) |
e | 富文本编码(如果适用) |
h | 富文本的 HTML 呈现(如果适用) |
c | 与单元格关联的注释 ** |
z | 已弃用 |
l | 单元格超链接对象 |
s | 单元格的样式/主题(如果适用) |
最后是导出方法openDownloadDialog,原理是创建a标签,然后模拟点击效果实现下载:
export 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);
}
至此,完结
可能会遇到的问题:
- 出现类似如下的错误:
This relative module was not found:
* ./cptable in ./node_modules/xlsx-style/dist/cpexcel.js
修改你的vue.config.js,添加如下:
module.exports = {
...
chainWebpack: (config) => {
...
config.externals({ './cptable': 'var cptable' }) /* 这里是xlsx-style会报奇怪的错误添加的 */
},
...
};