安装
导入导出excel这是前端做管理系统最常用的功能了,
这里使用到了两个组件: file-saver (用于保存文件) xlsx (用于导入导出excel)
npm install -S-D file-saver
npm install -S-D xlsx
2.建立文件
打开你的vue项目,在src/js文件夹下新建 json2excel.js。
import { saveAs } from 'file-saver'
import XLSX from 'xlsx/dist/xlsx.full.min'
// 将json数据处理为xlsx需要的格式
function datenum(v, date1904) {
if (date1904) v += 1462
let epoch = Date.parse(v)
return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000)
}
function data2ws(data) {
const ws = {}
const range = {s: {c: 10000000, r: 10000000}, e: {c: 0, r: 0}}
for (let R = 0; R != data.length; ++R) {
for (let C = 0; C != data[R].length; ++C) {
if (range.s.r > R) range.s.r = R
if (range.s.c > C) range.s.c = C
if (range.e.r < R) range.e.r = R
if (range.e.c < C) range.e.c = C
const cell = { v: data[R][C] }
if (cell.v == null) continue
const cell_ref = XLSX.utils.encode_cell({c: C, r: R})
if (typeof cell.v === 'number') cell.t = 'n'
else if (typeof cell.v === 'boolean') cell.t = 'b'
else if (cell.v instanceof Date) {
cell.t = 'n'
cell.z = XLSX.SSF._table[14]
cell.v = datenum(cell.v)
}
else cell.t = 's'
ws[cell_ref] = cell
}
}
if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range)
return ws
}
// 导出Excel
function Workbook() {
if (!(this instanceof Workbook)) return new Workbook()
this.SheetNames = []
this.Sheets = {}
}
function s2ab(s) {
const buf = new ArrayBuffer(s.length)
const view = new Uint8Array(buf)
for (let i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF
return buf
}
/*
* th => 表头
* data => 数据
* fileName => 文件名
* fileType => 文件类型
* sheetName => sheet页名
*/
export default function toExcel ({th, data, fileName, fileType, sheetName}) {
data.unshift(th)
const wb = new Workbook(), ws = data2ws(data)
sheetName = sheetName || 'sheet1'
wb.SheetNames.push(sheetName)
wb.Sheets[sheetName] = ws
fileType = fileType || 'xlsx'
var wbout = XLSX.write(wb, {bookType: fileType, bookSST: false, type: 'binary'})
fileName = fileName || '列表'
saveAs(new Blob([s2ab(wbout)], {type: "application/octet-stream"}), `${fileName}.${fileType}`)
}
3.使用
1.导出excel
这里有两种使用方法,一种是加载为组件,一种是全局加载,我这里因为大部分页面都需要这个导出功能,所以我就直接在全局加载了。
第一步:在main.js中全局挂载toExcel方法
import toExcel from '../src/js/json2excel'
Vue.prototype.$toExcel = toExcel
第二步:创建下载表格的方法即可
//下载表格
downExcel() {
const th = ['文件路径', '检测结果', '违规原因', '关键词'] //创建表头
const filterVal = ['path', 'conclusion', 'msg', 'words'] //输出的内容
const data = this.dialogData.error.map(v => filterVal.map(k => v[k]))//循环添加条数
const [fileName, fileType, sheetName] = ['违规文件检测结果', 'xlsx', '违规文件目录']//文件名称,后缀,文件内的页名
this.$toExcel({th, data, fileName, fileType, sheetName})
},
2.读取excel
此方法应该配合一个file框使用
importfxx(obj) {
//读取excel
let _this = this;
let inputDOM = this.$refs.upload;
// 通过DOM取文件数据
this.file = event.currentTarget.files[0];
var rABS = false; //是否将文件读取为二进制字符串
var f = this.file;
var reader = new FileReader();
//if (!FileReader.prototype.readAsBinaryString) {
FileReader.prototype.readAsBinaryString = function(f) {
var binary = "";
var rABS = false; //是否将文件读取为二进制字符串
var pt = this;
var wb; //读取完成的数据
var outdata;
var reader = new FileReader();
reader.onload = function(e) {
var bytes = new Uint8Array(reader.result);
var length = bytes.byteLength;
for (var i = 0; i < length; i++) {
binary += String.fromCharCode(bytes[i]);
}
//此处引入,用于解析excel
if (rABS) {
wb = XLSX.read(btoa(fixdata(binary)), {
//手动转化
type: "base64"
});
} else {
wb = XLSX.read(binary, {
type: "binary"
});
}
//outdata = XLSX.utils.sheet_to_txt(wb.Sheets[wb.SheetNames[1]]);
//console.log("解析的文件内容为:" + outdata)
outdata = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[1]]);
//outdata就是读取的数据(不包含标题行即表头,表头会作为对象的下标)
//此处可对数据进行处理
// let arr = [];
// outdata.map(v => {
// let obj = {}
// obj.code = v['Code']
// obj.name = v['Name']
// obj.pro = v['province']
// obj.cit = v['city']
// obj.dis = v['district']
// arr.push(obj)
// });
// _this.da=arr;
// _this.dalen=arr.length;
//把结果转为json字符串
var jsonstr = JSON.stringify(outdata);
console.log("解析的文件内容为:" + jsonstr)
//console.log(jsonstr)
_this.text = jsonstr;
//return arr;
};
reader.readAsArrayBuffer(f);
};
if (rABS) {
reader.readAsArrayBuffer(f);
} else {
reader.readAsBinaryString(f);
}
}
网友评论