纯前端导出Excel详细过程（包含按列导出+表格带样式+表格合并+表格嵌套表格+中英文两个sheet）
站长
· 阅读数 31
一、前言
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 < workbook._maxSheetId; index++) {
// 设置行高 sheet(’sheet号’).row(行号).height('行高')
workbook.sheet(index).row(1).height(25);
// 取消垂直居中
// workbook.sheet(index).printOptions("verticalCentered", undefined);
//打印页边距预模板
workbook.sheet(index).pageMarginsPreset("narrow");
//可以给指定的格子添加内容并合并单元格
// workbook.sheet(index).range("M43:P43").value('要写入的内容:').merged(true)
}
workbook.sheets().forEach((sheet) => {
// 所有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<dataInfo.mergesRange.length;i++){
sheet.range(dataInfo.mergesRange[i]).merged(true).style({
//水平居中
horizontalAlignment: "center",
//垂直居中
verticalAlignment: "center",
});
}
}
// .style 是添加样式 --> 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<dataInfo.keystyle.length;i++){
sheet.column(dataInfo.keystyle[i]).style({
wrapText: true,
horizontalAlignment: "left",
});
sheet.column(dataInfo.keystyle[i]).width(60)
}
}
// 表头加粗及背景色
sheet.range(dataInfo.theadRange).style({
wrapText: true,
fill: "C9C7C7",
bold: true,
horizontalAlignment: "center",
fontSize: 10,
});
// 表格黑色细边框
sheet.range(dataInfo.tableRange).style({
border: {
style: "thin",
color: "000000",
direction: "both",
},
});
});
return workbook.outputAsync().then(
(workbookBlob) => URL.createObjectURL(workbookBlob) // 创建blob地址
);
});
}
三、代码实现
1、代码文件位置
2、表格数据结构
做项目时是从后台数据库获取的表格数据，这里为了直接定义静态数据，数组包含对象数组，结构复杂，格式为：
3、组件代码实现
3.1 Demos父组件代码
<template>
<div>
<el-tabs v-model="activeName" type="border-card" style="height: calc(100vh - 62px);">
<el-tab-pane label="按需导出Excel" name="first">
<ExportExcel :totaltableList="totaltableList"></ExportExcel>
<el-table :data="totaltableList" border>
<el-table-column label="考核名称" prop="kpi_name"></el-table-column>
<el-table-column label="归属用户" prop="user_name"></el-table-column>
<el-table-column label="开始日期" prop="start_time"></el-table-column>
<el-table-column label="操作" width="130px">
<el-button type="primary" icon="el-icon-edit" size="mini"></el-button>
<el-button type="danger" icon="el-icon-delete" size="mini"></el-button>
</el-table-column>
</el-table>
</el-tab-pane>
<el-tab-pane label="表格拖拽" name="second">表格列表拖拽</el-tab-pane>
<el-tab-pane label="下拉框嵌入表格" name="third">下拉框嵌入表格</el-tab-pane>
</el-tabs>
</div>
</template>
<script>
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,
},
]
},
],
}
},
}
</script>
<style scoped>
::v-deep .el-table .cell{
text-align: center;
}
</style>
3.2 ExportExcel子组件代码
<template>
<div>
<div><el-button class="exportBtn" size="small" @click="selectcloumnDrawer = true">导出Excel</el-button></div>
<!-- 按需导出列 -->
<el-dialog :visible.sync="selectcloumnDrawer" title="选择列" @close="closeDialog()">
<ExportColumn :columnList="columnListModel" @exportExcel="exportExcel"></ExportColumn>
</el-dialog>
</div>
</template>
<script>
// 引入选择按列导出弹窗组件
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 < p.goal_list.length;i++){
ret.push(p)
}
}
list.splice(index,1,ret)
})
list = [].concat(...list)
//包含嵌套表格数据时导出的表头数据
for(var i = 1;i < cHeaderZh.length;i++){
tHeaderZh.push('目标详情')
tHeaderEn.push('goal detail')
}
filterVal.splice(-1,1,...cfilterVal)
}
//将数组转为二维数组
const dataZh = this.formatJson(filterVal, list);
const dataEn = this.formatJson(filterVal, list,"language");
//将数组中的对象转为{A:'',B:''}格式
const tableZh = this.changecolmuntokey(tHeaderZh,dataZh,alphabetList)
const tableEn = this.changecolmuntokey(tHeaderEn,dataEn,alphabetList)
//设置合并单元格行
if(isHasChildExcel){
var rowSpanList = []
let rowSpan = {}
const goallength = this.totaltableList.map(p=>p.goal_list.length)
const columnlen = isHasChildExcel?tHeaderZh.length-cHeaderLen:tHeaderZh.length
for(let j=0;j<columnlen;j++){
let index = 0
let i = 0
for(; i < goallength.length;){
for(; index < dataZh.length;){
rowSpan = [[j, index+3],[j,index+3+goallength[i]]] //+3:从第三行开始才需要行合并
rowSpanList.push(rowSpan)
index = index+goallength[i]+1
i++
}
}
}
//单元格行合并
var mergesRange = rowSpanList.map((p)=>{
var mergeslist = []
for(let index=0;index<columnlen;index++){
var data = p.map((q)=>{
const ret = alphabetList[index].toString()+q[1].toString()
return ret
})
data = data.join(':')
mergeslist.push(data)
}
return mergeslist
})
//单元格列合并
mergesRange.unshift(`${alphabetList[columnlen]}2:${alphabetList[tHeaderZh.length-1]}2`)
}
//指定Excel样式和合并范围等属性
const dataInfo = {
titleCell: "A1",
titleRange: `A1:${alphabetList[tHeaderZh.length-1]}1`,
theadRange: `A2:${alphabetList[tHeaderZh.length-1]}2`,
tbodyRange: `A3:${alphabetList[tHeaderZh.length-1]}${dataZh.length + 2}`,
tableRange: `A2:${alphabetList[tHeaderZh.length-1]}${dataZh.length + 2}`,
};
//有子表格时才有合并单元格
if(isHasChildExcel) this.$set(dataInfo,'mergesRange',mergesRange.flat())
//表格第一行标题
const titleZh = [{ A: "员工考核表" }];
const titleEn = [{ A: "Employee Evaluation Table" }];
//传入得到的数据，导出引用handleExport方法导出
handleExport(tableZh,tableEn,titleZh,titleEn,dataInfo).then(url => {
const downloadAnchorNode = document.createElement("a");
downloadAnchorNode.setAttribute("href", url);
downloadAnchorNode.setAttribute(
"download",
"ExportList-Kpis.xlsx" //自定义导出文件的名称
);
downloadAnchorNode.click();
downloadAnchorNode.remove();
});
setTimeout(()=>{this.closeDialog('exportexcel')})
},
//将数组中的对象转为{A:'',B:''}格式
changecolmuntokey(tHeaderZh,dataZh,alphabetList){
const table = []
const list1 = []
//表头转为{A:'',B:''}格式
tHeaderZh.map((p,i)=>{
this.$set(list1,alphabetList[i],p)
})
table.push(list1)
//表身转为{A:'',B:''}格式
dataZh.map((p) => {
const list = {}
for(let i=0;i<p.length;i++){
this.$set(list,alphabetList[i],p[i])
}
table.push(list)
})
return table
},
//自定义二维数组数据格式
formatJson(filterVal, jsonData,language) {
var result1 = jsonData.map((v,vIndex) => {
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<=v.goal_list.length;i++){
if(vIndex >= 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')
},
}
}
</script>
<style scoped>
.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;
}
</style>
3.3 ExportColumn子组件按列导出的代码
<template>
<div>
<div class="dialog_body">
<el-checkbox :indeterminate="isIndeterminate" v-model="checkAll" @change="handleCheckAllChange" style="float:left">全选</el-checkbox>
<div style="margin: 15px 0;"></div>
<el-checkbox-group v-model="checkedColumn" @change="handleCheckedColumnChange" class="flexcolumn">
<el-checkbox style="display:flex;white-space:normal;width:142px;margin-right:10px;word-break:break-word;height:30px;" v-for="itemKey in columnList" :label="itemKey" :key="itemKey.name">{{itemKey.name}}</el-checkbox>
</el-checkbox-group>
</div>
<div class="right_sub_btn">
<el-button type="primary" @click="exportExcel">确定导出</el-button>
<el-button @click="resetexportColumn">重置</el-button>
</div>
</div>
</template>
<script>
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 < this.columnList.length;
},
exportExcel(){
if(this.checkedColumn.length === 0){
this.openmessage()
}else{
this.$emit('exportExcel',this.checkedColumn)
}
},
openmessage(){
this.$message({message: '请选择导出列',type: 'error',offset:200,duration:2000})
},
resetexportColumn(){
this.checkedColumn.splice(0,this.checkedColumn.length)
this.checkAll = false
this.isIndeterminate = false
},
}
}
</script>
<style scoped>
.dialog_body{
margin-bottom: 50px;
}
.right_sub_btn{
position: absolute;
right: 10px;
bottom: 20px;
}
.flexcolumn{
width: 100%;
display: flex;
flex-wrap: wrap;
justify-content: left;
}
</style>
四、总结
要注意的是，由于数据库数据格式不一样，从后台得到的数据要转换为自己导出需要的格式才能正确导出。如果全部搞懂这些，前端导出Excel表格基本全都会了。要是再遇到导出Excel的需求不慌啦！^0^
转载自:https://juejin.cn/post/7097007976715649061