|
一、前言
vue项目纯前端导出Excel，数据结构数组中包含数组，实现按所需的列导出Excel，运用到xlsx-populate插件，导出的Excel文件：带边框、首列合并、表格嵌套表格、分别生成chinese和english两个工作表。导出表格实现效果如下：
二、准备工作
1.安装依赖
npm install file-saver -S //使用版本：^2.0.5
npm install script-loader -S //使用版本：^0.7.2
npm install xlsx -S //使用版本：^0.17.3
//xlsx-populate使excel带样式导出
npm install xlsx-populate -S //使用版本：^1.21.0
2.引入js文件工具库
在src文件夹下新建excel文件夹，添加export.js文件。
有个需求是生成一个chinese和一个english工作表。如果只需要生成一个工作表的可以只创建一个工作表。
//导入 xlsx 模块
import XLSX from "xlsx";
//导入 xlsxPopulate 模块
import XlsxPopulate from "xlsx-populate";
// 用于统一设置报表的样式 “A" "B" EXCEL 的列
const alphabetList = ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z",];
function workbook2blob(workbook) {
// 生成excel的配置项
const wopts = {
// 要生成的文件类型
bookType: "xlsx",
// 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
bookSST: false,
type: "binary",
};
const wbout = XLSX.write(workbook, wopts);
// 将字符串转ArrayBuffer
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;
}
const blob = new Blob([s2ab(wbout)], {
type: "application/octet-stream",
});
return blob;
}
//导出 handleExport --> 只创建一个工作表时，只传tableZh,titleZh,dataInfo就可。
export function handleExport(tableZh,tableEn,titleZh,titleEn,dataInfo) {
//创建一个空工作簿
const wb = XLSX.utils.book_new();
//表格数据——中英文版
const finalDataZh = [...titleZh, ...tableZh];
const finalDataEn = [...titleEn, ...tableEn];
//将json数据转为sheet
const sheetZh = XLSX.utils.json_to_sheet(finalDataZh, { skipHeader: true,});
const sheetEn = XLSX.utils.json_to_sheet(finalDataEn, { skipHeader: true,});
//创建工作表，第三个参数为生成excel的sheet名称
XLSX.utils.book_append_sheet(wb, sheetZh, "Chinese");
XLSX.utils.book_append_sheet(wb, sheetEn, "English");
const workbookBlob = workbook2blob(wb);
//dataInfo为接受的样式和合并参数
return addStyle(workbookBlob, dataInfo);
}
//添加样式的方法
function addStyle(workbookBlob, dataInfo) {
return XlsxPopulate.fromDataAsync(workbookBlob).then((workbook) => {
// 循环所有的表改变样式
for (let index = 0; index {
// 所有cell垂直居中,修改字体
sheet.usedRange().style({
fontFamily: "Arial",
verticalAlignment: "center",
});
// 去除所有边框 (网格线)
// sheet.gridLinesVisible(false);
// 设置单元格宽度
alphabetList.forEach((item) => {
sheet.column(item).width(15);
});
// 合并单元格
if(dataInfo.mergesRange){
for(let i=0;i title加粗合并及居中
sheet.range(dataInfo.titleRange).merged(true).style({
//加粗
bold: true,
//水平居中
horizontalAlignment: "center",
//垂直居中
verticalAlignment: "center",
//字号
fontSize: 14,
});
sheet.range(dataInfo.tbodyRange).style({
horizontalAlignment: "center",
//内容放不下时候允许换行
wrapText: true,
fontSize: 10,
});
if(dataInfo.keystyle){
for(let i=0;i URL.createObjectURL(workbookBlob) // 创建blob地址
);
});
}
三、代码实现
1、代码文件位置
2、表格数据结构
做项目时是从后台数据库获取的表格数据，这里为了直接定义静态数据，数组包含对象数组，结构复杂，格式为：
3、组件代码实现
3.1 Demos父组件代码
;
表格列表拖拽
下拉框嵌入表格
import ExportExcel from './ExportExcel';
export default {
name:"Demos",
components:{
ExportExcel,
},
data() {
return {
activeName: 'first',
totaltableList:[
{
"id": 1,
"kpi_name":'2022年4月月度考核表',
"kpi_name_en":'2022-04-monthly-check-form',
"user_name":"张三",
"start_time":"2022-04-01",
"goal_list": [
{
"goal": "出勤率",
"goal_en": "attendance rate",
"kpi_method": "出勤率达到100%，迟到一次扣10元。",
"kpi_method_en": "If the attendance rate reaches 100%, 10 yuan will be deducted once being late.",
"weight": 50,
"end_score": 99,
},
{
"goal": "工作态度",
"goal_en": "working attitude",
"kpi_method": "积极主动，态度端正。",
"kpi_method_en": "Initiative and good attitude.",
"weight": 50,
"end_score": 98,
},
]
},
{
"id": 2,
"kpi_name":'2022年5月月度考核表',
"kpi_name_en":'2022-05-monthly-check-form',
"user_name":"李四",
"start_time":"2022-05-01",
"goal_list": [
{
"goal": "出勤率",
"goal_en": "attendance rate",
"kpi_method": "出勤率达到100%，迟到一次扣10元。",
"kpi_method_en": "If the attendance rate reaches 100%, 10 yuan will be deducted once being late.",
"weight": 50,
"end_score": 100,
},
{
"goal": "工作态度",
"goal_en": "working attitude",
"kpi_method": "积极主动，态度端正。",
"kpi_method_en": "Initiative and good attitude.",
"weight": 50,
"end_score": 100,
},
]
},
],
}
},
}
::v-deep .el-table .cell{
text-align: center;
}
3.2 ExportExcel子组件代码
导出Excel
// 引入选择按列导出弹窗组件
import ExportColumn from './ExportColumn'
import {handleExport} from '../../../../excel/export.js'
export default {
components: {
ExportColumn,
},
props:{
totaltableList:{
type:Array,
default:()=>[]
}
},
data(){
return {
selectcloumnDrawer:false,//控制导出列弹窗显示
columnList:[],//选中的列
columnListModel:[//全部列
{ name:"考核名称",
name_en:"Check Name",
field_code:"kpi_name", },
{ name:"归属用户",
name_en:"belong user",
field_code:"user_name", },
{ name:"开始日期",
name_en:"start time",
field_code:"start_time", },
{ name:"目标详情",
name_en:"goal detail",
field_code:"goal_list", },
],
}
},
methods:{
//导出excel
exportExcel(checkedColumn) {
this.columnList = checkedColumn
this.exportExcelfn()
},
exportExcelfn(){
const alphabetList = ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"]
//主表格数据
const tHeaderZh = this.columnList.map((p)=>{return p.name})//选择导出列的中文表头
const tHeaderEn = this.columnList.map((p)=>{return p.name_en})//选择导出列的英文表头
const filterVal = this.columnList.map((p)=>{return p.field_code})//选择导出列的字段key
//获取导出表格的原始数据
var list = JSON.parse(JSON.stringify(this.totaltableList))//totaltableList是表格json数据
const isHasChildExcel = filterVal.includes('goal_list')//判断是否导出目标详情子表格
//根据goal_list长度新增行，导出Excel包含子表格时执行
if(isHasChildExcel){
//子表格数据
const cHeaderZh = ["考核项目","考核办法","比重(%)","最后得分"]
const cHeaderEn = ["Check Item","Check Target","weight(%)","Check Method","score"]
const cfilterVal = ["goal","kpi_method","weight","end_score"]
const endData = { goalValue:cHeaderZh, goalEnValue:cHeaderEn, keyValue:cfilterVal, }
var cHeaderLen = cHeaderZh.length
//根据goal_list长度新增行
list.forEach((p,index) => {
var ret = []
if(p.goal_list.length === 0){
ret.push(p)
}else{
p.goal_list.unshift(endData)
for(var i = 0;i {
this.$set(list1,alphabetList[i],p)
})
table.push(list1)
//表身转为{A:'',B:''}格式
dataZh.map((p) => {
const list = {}
for(let i=0;i {
var result = filterVal.map(j => {
if(j === "kpi_name"){
if(language){
return v[j+'_en']
}else{
return v[j]
}
}else if(j === "goal" || j === "kpi_method" || j === "weight" || j === "end_score"){ //有子表格数据时根据索引显示
var goalIndex = 0
if(vIndex !== 0){
for(var i=1;i= i){
if(jsonData[vIndex].id === jsonData[vIndex-i].id){
goalIndex = i
}
}
}
}
if(v.goal_list[goalIndex]){
if('keyValue' in v.goal_list[goalIndex]){
for(var i in v.goal_list[goalIndex].keyValue){
if(v.goal_list[goalIndex].keyValue[i] === j){
if(language){
return v.goal_list[goalIndex].goalEnValue[i]
}else{
return v.goal_list[goalIndex].goalValue[i]
}
}
}
}
if(language && (j === "goal" || j === "kpi_method" || j === "goal_str")){
return v.goal_list[goalIndex][j]?v.goal_list[goalIndex][j+'_en']:''
}else{
return v.goal_list[goalIndex][j]?v.goal_list[goalIndex][j]:''
}
}else{
return ''
}
}else{
return v[j]
}
})
return result
})
return result1
},
closeDialog(){
this.selectcloumnDrawer = false
this.$bus.$emit('refershcolumn')
},
}
}
.exportBtn{
float: right;
margin: 4px auto;
}
::v-deep .el-dialog__header {
background-color: #ecf1f6;
margin-bottom: 4px;
}
::v-deep .el-dialog__body {
padding: 2px 20px 20px;
}
3.3 ExportColumn子组件按列导出的代码
全选
{{itemKey.name}}
确定导出
重置
export default {
name: 'ExportColumn',
props:{columnList:Array},
data() {
return {
checkAll:false,
checkedColumn:[],
isIndeterminate:false
}
},
mounted(){
this.$bus.$on('refershcolumn',this.resetexportColumn)
console.log(this.columnList);
},
methods:{
handleCheckAllChange(val) {
this.checkedColumn = val ? [...this.columnList] : [];
this.isIndeterminate = false;
},
handleCheckedColumnChange(value) {
let checkedCount = value.length;
this.checkAll = checkedCount === this.columnList.length;
this.isIndeterminate = checkedCount > 0 && checkedCount |