在vue中使用excel
使用方法
1.安装依赖
npm install vue-json-excel
2.在项目的入口文件(main.js)中引入
import Vue from 'vue'
import JsonExcel from 'vue-json-excel'
Vue.component('downloadExcel', JsonExcel)
3.在模板中使用
<download-excel
:data = "json_data"
:fields = "json_fields"
name = "用户统计列表">
导出Excel
</download-excel>
4.Excel表格表头的设置
export default{
data(){
return{
json_fields: { //导出Excel表格的表头设置
'序号': 'type',
'姓名': 'userName',
'年龄': 'age',
'手机号': 'phone',
'注册时间': 'createTime',
},
}
}
}
5.Excel表格中的数据
export default{
data(){
return{
json_data:[
{"userName":"张三","age":18,"gender":"phone":15612345612,"createTime":"2019-10-22"},
{"userName":"李四","age":17,"gender":"phone":15612345613,"createTime":"2019-10-23"},
{"userName":"王五","age":19,"gender":"phone":15612345615,"createTime":"2019-10-25"},
{"userName":"赵六","age":18,"gender":"phone":15612345618,"createTime":"2019-10-15"},
]
}
}
}
js使用方法
安装依赖
npm i xlsx
引入依赖
代码使用
// 初始化存放合并表头的list
let arr = []
// 存放二级表头的集合,因为使用es6语法中的在Mis平台不支持
let head = []
// 将二级表头的数据进行整理整理成字符串数组
this.userInsuranceTableHeader.map(el => {
arr.push(el.columnName)
})
// 把数据转换成字符串
let table = this.tableDate.map(res => Object.values(res))
// 特殊处理，需要拆分的地方进行整理
table.map(arr => {
let aaa = arr[12].split("、")
aaa.indexOf("养老保险") > 0 ? arr.splice(4,0,"√") :arr.splice(4,0,null)
aaa.indexOf("失业保险") > 0 ? arr.splice(5,0,"√") :arr.splice(5,0,null)
aaa.indexOf("工伤保险") > 0? arr.splice(6,0,"√"):arr.splice(6,0,null)
aaa.indexOf("医疗保险") > 0? arr.splice(7,0,"√"):arr.splice(7,0,null)
aaa.indexOf("住房公积金") > 0? arr.splice(8,0,"√"):arr.splice(8,0,null)
arr.splice(17, 1);
})
// 需要打印的格式，需要合并的把合并内容添加null
let aoa = [
["增加增加（（ 需要提供身份证复印件需要提供身份证复印件 ，合同复印件合同复印件 ））",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,,null,null],
["序号","姓名","身份证号码","民族","险种 （打√ ）",null,null,null,null,"参保起始时间","岗位工种","合同起始时间","合同终止时间","社保基数","医保基数","公积金基数","联系电话","家庭地址"],
[null,null,null,null,"养老","失业","工商","医疗","公积金","（可从当年一月份补收）",null,null,null,null,null,null,,null,null],
// arr,
...table
]
// 转换成xlsx的格式
const sheet1 = XLSX.utils.aoa_to_sheet(aoa);
sheet1['!merges'] = [
// 设置A1-C1的单元格合并
{s: {r: 0, c: 0}, e: {r: 0, c: 18}},
{s: {r: 1, c: 0}, e: {r: 2, c: 0}},
{s: {r: 1, c: 1}, e: {r: 2, c: 1}},
{s: {r: 1, c: 2}, e: {r: 2, c: 2}},
{s: {r: 1, c: 3}, e: {r: 2, c: 3}},
{s: {r: 1, c: 4}, e: {r: 1, c: 8}},
{s: {r: 1, c: 11}, e: {r: 2, c: 11}},
{s: {r: 1, c: 12}, e: {r: 2, c: 12}},
{s: {r: 1, c: 13}, e: {r: 2, c: 13}},
{s: {r: 1, c: 14}, e: {r: 2, c: 14}},
{s: {r: 1, c: 15}, e: {r: 2, c: 15}},
{s: {r: 1, c: 16}, e: {r: 2, c: 16}},
{s: {r: 1, c: 17}, e: {r: 2, c: 17}},
{s: {r: 1, c: 18}, e: {r: 2, c: 18}},
];
//控制单元格宽度
sheet1["!cols"] = [{ wpx: 150},
{wpx: 70},
{ wpx: 150 },
{ wpx: 70},
{ wpx: 150},
{ wpx: 120},
{ wpx: 120},
{ wpx: 120} ,
{ wpx: 120},
{ wpx: 120},
{ wpx: 120},
{ wpx: 120} ,
{ wpx: 120},
{ wpx: 120},
{ wpx: 120} ,
{ wpx: 120} ,
{ wpx: 120},
{ wpx: 120}
];
// 第二个表格需要的内容
const sheet2 = XLSX.utils.json_to_sheet(this.tableList);
// 创建成新的文本内容
const wb = XLSX.utils.book_new();
// 添加新的页签
XLSX.utils.book_append_sheet(wb, sheet1, '收件人列表');
XLSX.utils.book_append_sheet(wb, sheet2, '表格 sheet2');
// 文本转出
const workbookBlob = this.workbook2blob(wb);
// 导出表格
this.openDownloadDialog(workbookBlob, '收件人列表.xlsx');