1. Foreword
The vue project is a pure front-end export to Excel. The data structure array contains arrays to export Excel according to the required columns. Using the xlsx-populate plug-in, the exported Excel file: with borders, first column merging, nested tables, and Chinese generation respectively. and english two worksheets. The effect of exporting the table is as follows:
2. Preparation
1. Install dependencies
npm install file-saver -S //Used version: ^2.0.5 npm install script-loader -S //Using version: ^0.7.2 npm install xlsx -S //Using version: ^0.17.3 //xlsx-populate enables excel to export with style npm install xlsx-populate -S //Use version: ^1.21.0
2. Introduce js file tool library
Create a new excel folder under the src folder and add the export.js file.
There is a requirement to generate a Chinese and an English worksheet. If you only need to generate one worksheet, you can just create one worksheet.
//Import xlsx module
import XLSX from "xlsx";
//Import xlsxPopulate module
import XlsxPopulate from "xlsx-populate";
// Used to uniformly set the report style "A" "B" EXCEL columns
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) {<!-- -->
// Generate excel configuration items
const wopts = {<!-- -->
//The file type to generate
bookType: "xlsx",
// Whether to generate Shared String Table. The official explanation is that if it is turned on, the generation speed will decrease, but it will have better compatibility on lower version IOS devices.
bookSST: false,
type: "binary",
};
const wbout = XLSX.write(workbook, wopts);
// Convert string to 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) & amp; 0xff;
return buf;
}
const blob = new Blob([s2ab(wbout)], {<!-- -->
type: "application/octet-stream",
});
return blob;
}
//Export handleExport --> When creating only one worksheet, just pass tableZh, titleZh, dataInfo.
export function handleExport(tableZh,tableEn,titleZh,titleEn,dataInfo) {<!-- -->
//Create an empty workbook
const wb = XLSX.utils.book_new();
//Tabular data - Chinese and English version
const finalDataZh = [...titleZh, ...tableZh];
const finalDataEn = [...titleEn, ...tableEn];
//Convert json data to sheet
const sheetZh = XLSX.utils.json_to_sheet(finalDataZh, {<!-- --> skipHeader: true,});
const sheetEn = XLSX.utils.json_to_sheet(finalDataEn, {<!-- --> skipHeader: true,});
//Create a worksheet, the third parameter is the name of the sheet to generate excel
XLSX.utils.book_append_sheet(wb, sheetZh, "Chinese");
XLSX.utils.book_append_sheet(wb, sheetEn, "English");
const workbookBlob = workbook2blob(wb);
//dataInfo is the accepted style and merge parameters
return addStyle(workbookBlob, dataInfo);
}
//Method to add style
function addStyle(workbookBlob, dataInfo) {<!-- -->
return XlsxPopulate.fromDataAsync(workbookBlob).then((workbook) => {<!-- -->
// Loop through all tables to change styles
for (let index = 0; index < workbook._maxSheetId; index + + ) {<!-- -->
//Set the row height sheet('sheet number').row(row number).height('row height')
workbook.sheet(index).row(1).height(25);
// Cancel vertical centering
// workbook.sheet(index).printOptions("verticalCentered", undefined);
//Print margin pre-template
workbook.sheet(index).pageMarginsPreset("narrow");
//You can add content to the specified grid and merge cells
// workbook.sheet(index).range("M43:P43").value('Content to be written:').merged(true)
}
workbook.sheets().forEach((sheet) => {<!-- -->
// Center all cells vertically and modify the font
sheet.usedRange().style({<!-- -->
fontFamily: "Arial",
verticalAlignment: "center",
});
// Remove all borders (grid lines)
// sheet.gridLinesVisible(false);
//Set cell width
alphabetList.forEach((item) => {<!-- -->
sheet.column(item).width(15);
});
// Merge Cells
if(dataInfo.mergesRange){<!-- -->
for(let i=0;i<dataInfo.mergesRange.length;i + + ){<!-- -->
sheet.range(dataInfo.mergesRange[i]).merged(true).style({<!-- -->
//center horizontally
horizontalAlignment: "center",
//center vertically
verticalAlignment: "center",
});
}
}
// .style is to add style --> title bold, merge and center
sheet.range(dataInfo.titleRange).merged(true).style({<!-- -->
//bold
bold: true,
//center horizontally
horizontalAlignment: "center",
//center vertically
verticalAlignment: "center",
//font size
fontSize: 14,
});
sheet.range(dataInfo.tbodyRange).style({<!-- -->
horizontalAlignment: "center",
//Allow line breaks when the content cannot fit
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)
}
}
// Header bold and background color
sheet.range(dataInfo.theadRange).style({<!-- -->
wrapText: true,
fill: "C9C7C7",
bold: true,
horizontalAlignment: "center",
fontSize: 10,
});
//Table black thin border
sheet.range(dataInfo.tableRange).style({<!-- -->
border: {<!-- -->
style: "thin",
color: "000000",
direction: "both",
},
});
});
return workbook.outputAsync().then(
(workbookBlob) => URL.createObjectURL(workbookBlob) // Create blob address
);
});
}
3. Code implementation
1. Code file location
2. Table data structure
When working on a project, the tabular data is obtained from the backend database. In order to directly define static data, the array contains an object array with a complex structure and the format is:
3. Component code implementation
3.1 Demos parent component code
<template>
<div>
<el-tabs v-model="activeName" type="border-card" style="height: calc(100vh - 62px);">
<el-tab-pane label="Export Excel on demand" name="first">
<ExportExcel :totaltableList="totaltableList"></ExportExcel>
<el-table :data="totaltableList" border>
<el-table-column label="Assessment name" prop="kpi_name"></el-table-column>
<el-table-column label="Attributable user" prop="user_name"></el-table-column>
<el-table-column label="Start date" prop="start_time"></el-table-column>
<el-table-column label="Operation" width="130px">
<el-button type="primary" icon="el-icon-edit" size="mini"></el-button> & amp;nbsp;
<el-button type="danger" icon="el-icon-delete" size="mini"></el-button>
</el-table-column>
</el-table>
</el-tab-pane>
<el-tab-pane label="Table drag" name="second">Table list drag</el-tab-pane>
<el-tab-pane label="Drop-down box embedded in table" name="third">Drop-down box embedded in table</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":'Monthly Assessment Form for April 2022',
"kpi_name_en":'2022-04-monthly-check-form',
"user_name":"Zhang San",
"start_time":"2022-04-01",
"goal_list": [
{<!-- -->
"goal": "Attendance",
"goal_en": "attendance rate",
"kpi_method": "If the attendance rate reaches 100%, 10 yuan will be deducted for each late arrival.",
"kpi_method_en": "If the attendance rate reaches 100%, 10 yuan will be deducted once being late.",
"weight": 50,
"end_score": 99,
},
{<!-- -->
"goal": "Work attitude",
"goal_en": "working attitude",
"kpi_method": "Be proactive and have a correct attitude.",
"kpi_method_en": "Initiative and good attitude.",
"weight": 50,
"end_score": 98,
},
]
},
{<!-- -->
"id": 2,
"kpi_name":'May 2022 Monthly Assessment Form',
"kpi_name_en":'2022-05-monthly-check-form',
"user_name":"李思",
"start_time":"2022-05-01",
"goal_list": [
{<!-- -->
"goal": "Attendance",
"goal_en": "attendance rate",
"kpi_method": "If the attendance rate reaches 100%, 10 yuan will be deducted for each late arrival.",
"kpi_method_en": "If the attendance rate reaches 100%, 10 yuan will be deducted once being late.",
"weight": 50,
"end_score": 100,
},
{<!-- -->
"goal": "Work attitude",
"goal_en": "working attitude",
"kpi_method": "Be proactive and have a correct attitude.",
"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 sub-component code
<template>
<div>
<div><el-button class="exportBtn" size="small" @click="selectcloumnDrawer = true">Export Excel</el-button></div>
<!-- Export columns on demand -->
<el-dialog :visible.sync="selectcloumnDrawer" title="Select Column" @close="closeDialog()">
<ExportColumn :columnList="columnListModel" @exportExcel="exportExcel"></ExportColumn>
</el-dialog>
</div>
</template>
<script>
//Introduce the select export pop-up window component by column
import ExportColumn from './ExportColumn'
import {<!-- -->handleExport} from '../../../../excel/export.js'
export default {<!-- -->
components: {<!-- -->
ExportColumn,
},
props:{<!-- -->
totaltableList:{<!-- -->
type:Array,
default:()=>[]
}
},
data(){<!-- -->
return {<!-- -->
selectcloumnDrawer:false,//Control the export column pop-up window display
columnList:[],//Selected column
columnListModel:[//All columns
{<!-- --> name:"Assessment name",
name_en:"Check Name",
field_code:"kpi_name", },
{<!-- --> name:"belonging user",
name_en:"belong user",
field_code:"user_name", },
{<!-- --> name:"Start Date",
name_en:"start time",
field_code:"start_time", },
{<!-- --> name:"Target details",
name_en:"goal detail",
field_code:"goal_list", },
],
}
},
methods:{<!-- -->
//Export 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"]
//Main table data
const tHeaderZh = this.columnList.map((p)=>{<!-- -->return p.name})//Select the Chinese header of the exported column
const tHeaderEn = this.columnList.map((p)=>{<!-- -->return p.name_en})//Select the English header of the exported column
const filterVal = this.columnList.map((p)=>{<!-- -->return p.field_code})//Select the field key of the exported column
//Get the original data of the exported table
var list = JSON.parse(JSON.stringify(this.totaltableList))//totaltableList is table json data
const isHasChildExcel = filterVal.includes('goal_list')//Determine whether to export the target details subtable
//Add rows according to the length of goal_list, executed when exporting Excel to include subtables
if(isHasChildExcel){<!-- -->
//Subtable data
const cHeaderZh = ["Assessment items","Assessment methods","Proportion (%)","Final score"]
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
//Add new rows based on goal_list length
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)
//Exported header data when containing nested table data
for(var i = 1;i < cHeaderZh.length;i + + ){<!-- -->
tHeaderZh.push('Target details')
tHeaderEn.push('goal detail')
}
filterVal.splice(-1,1,...cfilterVal)
}
//Convert the array to a two-dimensional array
const dataZh = this.formatJson(filterVal, list);
const dataEn = this.formatJson(filterVal, list,"language");
//Convert the objects in the array to {A:'',B:''} format
const tableZh = this.changecolmuntokey(tHeaderZh,dataZh,alphabetList)
const tableEn = this.changecolmuntokey(tHeaderEn,dataEn,alphabetList)
//Set merged cell rows
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: Row merging is only required starting from the third row
rowSpanList.push(rowSpan)
index = index + goallength[i] + 1
i++
}
}
}
//Merge cell rows
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
})
//Cell column merge
mergesRange.unshift(`${<!-- -->alphabetList[columnlen]}2:${<!-- -->alphabetList[tHeaderZh.length-1]}2`)
}
//Specify attributes such as Excel style and merge range
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}`,
};
//Merge cells only when there are subtables
if(isHasChildExcel) this.$set(dataInfo,'mergesRange',mergesRange.flat())
//Title of the first row of the table
const titleZh = [{<!-- --> A: "Employee Appraisal Form" }];
const titleEn = [{<!-- --> A: "Employee Evaluation Table" }];
//Pass in the obtained data and export it by referencing the handleExport method.
handleExport(tableZh,tableEn,titleZh,titleEn,dataInfo).then(url => {<!-- -->
const downloadAnchorNode = document.createElement("a");
downloadAnchorNode.setAttribute("href", url);
downloadAnchorNode.setAttribute(
"download",
"ExportList-Kpis.xlsx" //Customize the name of the export file
);
downloadAnchorNode.click();
downloadAnchorNode.remove();
});
setTimeout(()=>{<!-- -->this.closeDialog('exportexcel')})
},
//Convert the objects in the array to {A:'',B:''} format
changecolmuntokey(tHeaderZh,dataZh,alphabetList){<!-- -->
const table = []
const list1 = []
//Convert the header to {A:'',B:''} format
tHeaderZh.map((p,i)=>{<!-- -->
this.$set(list1,alphabetList[i],p)
})
table.push(list1)
//Convert the table body to {A:'',B:''} format
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
},
//Customize two-dimensional array data format
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"){<! -- --> //Display according to index when there is subtable data
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 & amp; & amp; (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 Code for exporting columns by ExportColumn subcomponent
<template>
<div>
<div class="dialog_body">
<el-checkbox :indeterminate="isIndeterminate" v-model="checkAll" @change="handleCheckAllChange" style="float:left">Select all</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">Confirm export</el-button>
<el-button @click="resetexportColumn">Reset</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 & amp; & amp; checkedCount <this.columnList.length;
},
exportExcel(){<!-- -->
if(this.checkedColumn.length === 0){<!-- -->
this.openmessage()
}else{<!-- -->
this.$emit('exportExcel',this.checkedColumn)
}
},
openmessage(){<!-- -->
this.$message({<!-- -->message: 'Please select the export column',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>
4. Summary
It should be noted that because the database data formats are different, the data obtained from the background must be converted into the format required for export in order to be exported correctly. If you understand all of these, you will basically be able to export Excel tables on the front end. If you encounter the need to export Excel again, don’t panic! 0