1. Export data as xlsx table file. Table with drop-down selection
Introduced
npm install [email protected]
Introduce (if npm introduction fails)
cnpm install [email protected]
3. Page introduction method import ExcelJS from "exceljs";
II. Example 1
<template>
<button @click="exportExcel">ExportExcel</button>
</template>
<script>
import ExcelJS from "exceljs";
export default {
methods: {
async exportExcel() {
const workbook = new ();
// Create a worksheet. There can be multiple worksheets in the echelectel const worksheet = ("Sheet1");
// Set the header of the created worksheet ("A1").value = "Name";
("B1").value = "age";
("C1").value = "gender";
("D1").value = "source";
// Add data rows It is generally used to loop through data transmission from our backend to each row exported (["Alex", 25, "male", "WeChat"]);
(["Alix", 30, "female", "WeChat"]);
(["Andy", 35, "male", "WeChat"]);
// Set the data list that sets our drop-down options const dropdownOptions = ["male", "female"];
const dropdownOptions1 = ["WeChat", "Alipay", "Cloud Flash Payment"];
// Create our second worksheet to store the options for our drop-down menu const worksheet2 = ("sheet2");
// Here is a display of the second worksheet one by one. See Figure 2 ("A").values = dropdownOptions
("B").values = dropdownOptions1
// Here is a display of the second worksheet one by one line. See Figure 3 // (dropdownOptions);
// (dropdownOptions1);
// Note: ---------------------------------------------------------------------------------------------------------------------------- // If it is a row by row, we have to write it like this, column c of our sheet // formulae: [`=sheet2!$A$1:$Z$1`],
// The second line must be written in this way: [`=sheet2!$A$2:$Z$2`], but this way he will only write A-Z in the second line
// If one column is one column, then our sheet's C column is associated with the first column of sheet2 data, then this is what it should be written // formulae: [`=sheet2!$A:$A`],
// If it is associated with the second column, you must write formulae: [`=sheet2!$B:$B`],
// Gender pull-down This is used to associate a column in worksheet 1 to a row in worksheet 2 as the value of the drop-down box or a column const col = ("C");
// Iterate through all current cells in this column, including empty cells ({ includeEmpty: true }, function (cell, rowNumber) {
// Set the drop-down list = {
type: "list",
allowBlank: true,
formulae: [`=sheet2!$A:$A`], //Line value // formulae: [`=sheet2!$A$1:$Z$1`], //Column value };
});
//Source drop-down const col3 = ("D");
// Iterate through all current cells in this column, including empty cells ({ includeEmpty: true }, function (cell, rowNumber) {
// Set the drop-down list = {
type: "list",
allowBlank: true,
formulae: [`=sheet2!$B:$B`],//Line value // formulae: [`=sheet2!$A$2:$Z$2`],//Column takes the value };
});
// Export Excel file const buffer = await ();
const blob = new Blob([buffer], {
type: "application/",
});
const url = (blob);
const link = ("a");
= url;
= "";
();
(url);
},
},
};
</script>
III. Example 2-Dynamic Data
//Introduce formatting tool classimport {timestampToDay, timestampToTime} from '@/utils/'
export default {
props: {
exportExcelArray: {
type: Array,
default: () => {
return []
}
},
tableData: {
type: Array,
default: () => {
return []
}
},
// Export the fields to which the custom font color belongs showRedList: {
type: Array,
default: () => {
return ['Failed', 'no']
}
},
showOrangeList: {
type: Array,
default: () => {
return ['Untested']
}
},
showGreenList: {
type: Array,
default: () => {
return ['pass', 'yes']
}
},
// Export custom font colors customColor: {
type: String,
default: 'D63640'
},
customGreen: {
type: String,
default: '1AD96F'
},
customOrange: {
type: String,
default: 'f2b400'
},
// Whether to display multiple worksheets-tables with drop-down box showMultipleMenu: {
type: Boolean,
default: false
},
// Whether to display multiple worksheets - independent showMultiple: {
type: Boolean,
default: false
},
},
methods: {
//Format formatter data format formatter (value,item,row,prop) {
// ('value',value)
// ('item',item)
// ('row',row)
// ('prop',prop)
//For the situation of multi-layer object hierarchy in table if(('.') > 0){
let temp = ('.')
// ('temp',temp)
//Nest two layers in the item (list array) if( == 2){
let temp = ('.')
let arry = row[temp[0]]
let result = ''
// Is the multi-layer object level an array if((arry)){
for(let i in arry){
// ('arry[i][temp[1]]',arry[i][temp[1]])
if(!(arry[i][temp[1]])){
if( == 'common-type'){ //Universal type conversion result = arry[i][temp[1]]
} else if( == 'time-type'){ //Time standard formatting if(){
result = timestampToDay(arry[i][temp[1]])
}else {
result = timestampToTime(arry[i][temp[1]])
}
} else if( == 'amount-type'){ //Amount conversion result = (arry[i][temp[1]] / 100).toFixed(2)
} else {
result = arry[i][temp[1]]
}
return result
}
}
}else{
// Is the multi-layer object level an object if(!(row[temp[0]][temp[1]])){
if( == 'common-type'){ //Universal type conversion result = row[temp[0]][temp[1]]
} else if( == 'time-type'){ //Time standard formatting if(){
result = timestampToDay(row[temp[0]][temp[1]])
}else {
result = timestampToTime(row[temp[0]][temp[1]])
}
} else if( == 'amount-type'){ //Amount conversion result = (row[temp[0]][temp[1]] / 100).toFixed(2)
} else {
result = row[temp[0]][temp[1]]
}
return result
}
}
}
} else{ //No nested objects in item let temp = ('.')
if( == 'common-type'){ //Universal type conversion let arry =
for(let i in arry){
if(arry[i].value == value){
return arry[i].label
}
}
} else if( == 'time-type'){ //Time standard formatting // ('row[temp[0]])1',row[temp[0]])
if(!(row[temp[0]])){
// return (0, - 2)
if(){
return timestampToDay(value)
}else {
return timestampToTime(value)
}
}
} else if( == 'amount-type'){ //Amount conversion return (value / 100).toFixed(2)
} else {
return value
}
}
},
// Whether to display multiple worksheets async exportExcelMultiple() {
const vm = this
let workbook = new ();
// Whether to display multiple worksheets - independent if(){
let workbookList = [
{
"sheetName": 'Quality Inspection List',
worksheetData: , // Export data from the interface worksheetHeaderList: // Excel top title customization }
]
workbook = await (workbook,workbookList);
}else {
// Create a worksheet. There can be multiple worksheets in the echelectel const worksheet = ("Sheet1");
// Set the header of the created worksheet // Table header object-If the title exceeds 26 letters, continue to be added let topCellObj = {
1: 'A',
2: 'B',
3: 'C',
4: 'D',
5: 'E',
6: 'F',
7: 'G',
8: 'H',
9: 'I',
10: 'J',
11: 'K',
12: 'L',
13: 'M',
14: 'N',
15: 'O',
16: 'P',
17: 'Q',
18: 'R',
19: 'S',
20: 'T',
21: 'U',
22: 'V',
23: 'W',
24: 'S',
25: 'Y',
26: 'Z',
}
((item,index)=>{
for(let path in topCellObj){
if(path == index + 1){
// Header title assignment (topCellObj[path]+"1").value = ;
break
}
}
})
// Add data rows It is generally used to loop through data transmission from our backend to each row exported ((row)=> {
let arr = []
for (const [key, value] of (row)) {
// (`Key: ${key}, Value: ${value}`);
for (let i=0; i < ; i++) {
let item = [i]
if ( == key) {
// Format fields let formatterValue = (row[],item,row,)
arr = [...arr, ...[{index:i,value: formatterValue ? formatterValue : value}]]
break
}
}
}
// index sort and return value field const arrNew = ((a, b) => - ).map(item => );
(arrNew);
})
// return
// Add column title and define column keys and widths // Note: These column structures are only a convenience for building a workbook, and they are not completely preserved except for column width. = ((item)=>{
return {
header: ,
key: ,
width: ,
}
})
// Set the background color and font color of the first row (1).eachCell({ includeEmpty: true }, (cell, colNumber) => {
= {
type: 'pattern',
pattern: 'solid',
// fgColor: { argb: (colNumber === 1 || colNumber === 2) ? 'FF79bbff' : 'FF95d475' }
fgColor: { argb: 'FF79bbff' }
};
= {
size: 10,
color: { argb: 'FFFFFFFF' } // White };
= {
horizontal: "center",// Horizontal center vertical: 'middle', // Vertical center }
});
// Add border + horizontal + vertical centering for (let num = 0; num < ._number; num++) {
// Loop out each line for (let index = 0; index < ; index++) {
let rowCell = (num + 1).getCell(index + 1)
// ('rowCell',rowCell)
// If the value of the table is contained in the color list, the cell will be assigned a color style. if(rowCell&&rowCell._value&&rowCell._value.model&&rowCell._value.&&num>0){
const colValue = rowCell._value.
// Custom font color let col = '000000'
// red if((colValue)){
col =
}
//green if((colValue)){
col =
}
// orange color if((colValue)){
col =
}
// Font = {
size: 10,
// Custom font color color: {argb: col},
}
}
// Loop out every cell = {
// Add borders to cells top: { style: "thin" },
left: { style: "thin" },
bottom: { style: "thin" },
right: { style: "thin" },
};
= {
// Add horizontal + vertical centering to the cell horizontal: "center",// Horizontal center vertical: 'middle', // Vertical center };
}
}
// Set the data list that sets our drop-down options const dropdownOptions = ["Auspicious Star", "Nasda", "Punel", "Aaron"];
const dropdownOptions1 = ["Self-development", "English Card", "Micro-step", "Mao Xian", "Wentai"];
// Create our second worksheet to store the options for our drop-down menu const worksheet2 = ("sheet2");
// Here is a display of the second worksheet one by one. See Figure 2 ("A").values = dropdownOptions
("B").values = dropdownOptions1
// Here is a display of the second worksheet one by one line. See Figure 3 // (dropdownOptions);
// (dropdownOptions1);
// Note: ---------------------------------------------------------------------------------------------------------------------------- // If it is a row by row, we have to write it like this, column c of our sheet // formulae: [`=sheet2!$A$1:$Z$1`],
// The second line must be written in this way: [`=sheet2!$A$2:$Z$2`], but this way he will only write A-Z in the second line
// If one column is one column, then our sheet's C column is associated with the first column of sheet2 data, then this is what it should be written // formulae: [`=sheet2!$A:$A`],
// If it is associated with the second column, you must write formulae: [`=sheet2!$B:$B`],
// Gender pull-down This is used to associate a column in worksheet 1 to a row in worksheet 2 as the value of the drop-down box or a column const col = ("F");
// Iterate through all current cells in this column, including empty cells ({ includeEmpty: true }, function (cell, rowNumber) {
// Set the drop-down list = {
type: "list",
allowBlank: true,
formulae: [`=sheet2!$A:$A`], //Line value // formulae: [`=sheet2!$A$1:$Z$1`], //Column value };
});
//Source drop-down const col3 = ("G");
// Iterate through all current cells in this column, including empty cells ({ includeEmpty: true }, function (cell, rowNumber) {
// Set the drop-down list = {
type: "list",
allowBlank: true,
formulae: [`=sheet2!$B:$B`],//Line value // formulae: [`=sheet2!$A$2:$Z$2`],//Column takes the value };
});
}
// Export Excel file const buffer = await ();
const blob = new Blob([buffer], {
type: "application/",
});
const url = (blob);
const link = ("a");
= url;
= "Quality Inspection List Record.xlsx";
();
(url);
},
// workbook: Create a new form // workbook-sheetName: table name, // workbook-worksheetData: table data, // workbook-worksheetHeaderList: Top title of the table // workbookList: Export list + table header async exportExcelMultipleEach(workbook,workbookList) {
const vm = this
((mItem,index)=>{
// Create a worksheet. There can be multiple worksheets in the echelectel const worksheet = (mItem&& ? : "Sheet"+(index+1));
// Set the header of the created worksheet // Table header object-If the title exceeds 26 letters, continue to be added let topCellObj = {
1: 'A',
2: 'B',
3: 'C',
4: 'D',
5: 'E',
6: 'F',
7: 'G',
8: 'H',
9: 'I',
10: 'J',
11: 'K',
12: 'L',
13: 'M',
14: 'N',
15: 'O',
16: 'P',
17: 'Q',
18: 'R',
19: 'S',
20: 'T',
21: 'U',
22: 'V',
23: 'W',
24: 'S',
25: 'Y',
26: 'Z',
}
((item,index)=>{
for(let path in topCellObj){
if(path == index + 1){
(topCellObj[path]+"1").value = ;
break
}
}
})
// Add data rows It is generally used to loop through data transmission from our backend to each row exported ((row)=> {
let arr = []
for (const [key, value] of (row)) {
// (`Key: ${key}, Value: ${value}`);
for (let i=0; i < ; i++) {
let item = [i]
if ( == key) {
// Format fields let formatterValue = (row[],item,row,)
arr = [...arr, ...[{index:i,value: formatterValue ? formatterValue : value}]]
break
}
}
}
// index sort and return value field const arrNew = ((a, b) => - ).map(item => );
(arrNew);
})
// return
// Add column title and define column keys and widths // Note: These column structures are only a convenience for building a workbook, and they are not completely preserved except for column width. = ((item)=>{
return {
header: ,
key: ,
width: ,
}
})
// Set the background color and font color of the first row (1).eachCell({ includeEmpty: true }, (cell, colNumber) => {
= {
type: 'pattern',
pattern: 'solid',
// fgColor: { argb: (colNumber === 1 || colNumber === 2) ? 'FF79bbff' : 'FF95d475' }
fgColor: { argb: 'FF79bbff' }
};
= {
size: 10,
color: { argb: 'FFFFFFFF' } // White };
= {
horizontal: "center",// Horizontal center vertical: 'middle', // Vertical center }
});
// Add border + horizontal + vertical centering for (let num = 0; num < ._number; num++) {
// Loop out each line for (let index = 0; index < ; index++) {
let rowCell = (num + 1).getCell(index + 1)
// ('rowCell',rowCell)
// If the value of the table is contained in the color list, the cell will be assigned a color style. if(rowCell&&rowCell._value&&rowCell._value.model&&rowCell._value.&&num>0){
const colValue = rowCell._value.
// Custom font color let col = '000000'
// red if((colValue)){
col =
}
//green if((colValue)){
col =
}
// orange color if((colValue)){
col =
}
// Font = {
size: 10,
// Custom font color color: {argb: col},
}
}
// Loop out every cell = {
// Add borders to cells top: { style: "thin" },
left: { style: "thin" },
bottom: { style: "thin" },
right: { style: "thin" },
};
= {
// Add horizontal + vertical centering to the cell horizontal: "center",// Horizontal center vertical: 'middle', // Vertical center };
}
}
})
return workbook
},
}
}
3.1 exportExcelArray format
exportExcelArray: [
{
prop: 'serialNumber', // Field key label: 'Serial number', // Field name formatterFlag: false, // Does the value need to be formatted align: 'center', // Layout-Center width: 10, // Column width },
{
prop: 'createTime',
label: 'Create time',
formatterFlag: true,
formatterType: 'time-type', // Time check-time stamp converted to date },
]
3.2 @/utils/
/**
* Timestamp
* @param {*} timestamp timestamp
*/
//The tail is zero-compensated (13 is a timestamp string, and the backend return format is not necessarily 13 bits)const padEndFun = (val,digit,num) => {
if(val == '' || val == undefined || val == null){
return ''
}
if(typeof val == 'number'){
val = ()
}
// ('val',val)
let str = (digit, ())
return Number(str)
},
const timestampToTime = (timestamp) => {
if(timestamp == '' || timestamp == undefined || timestamp == null){
return ''
}
if(padEndFun){
//The timestamp is 10 digits and no multiplication is 1000. timestamp = padEndFun(timestamp,13,0)
}
let date = new Date(timestamp) //The timestamp is 10 digits and no multiplication is 1000. let Y = () + '-'
let M =
(() + 1 < 10 ?
'0' + (() + 1) :
() + 1) + '-'
let D =
(() < 10 ? '0' + () : ()) + ' '
let h =
(() < 10 ? '0' + () : ()) + ':'
let m =
(() < 10 ? '0' + () : ()) +
':'
let s =
() < 10 ? '0' + () : ()
return Y + M + D + h + m + s
};
const timestampToDay = (timestamp) => {
if(timestamp == '' || timestamp == undefined || timestamp == null){
return ''
}
if(padEndFun){
//The timestamp is 10 digits and no multiplication is 1000. timestamp = padEndFun(timestamp,13,0)
}
let date = new Date(timestamp) //The timestamp is 10 digits and no multiplication is 1000. let Y = () + '-'
let M =
(() + 1 < 10 ?
'0' + (() + 1) :
() + 1) + '-'
let D =
(() < 10 ? '0' + () : ())
return Y + M + D
};
/**
* Storage localStorage
*/
const setStore = (name, content) => {
if (!name) return;
if (typeof content !== 'string') {
content = (content);
}
(name, content);
}
/**
* Get localStorage
*/
const getStore = name => {
if (!name) return;
return (name);
}
/**
* Delete localStorage
*/
const removeStore = name => {
if (!name) return;
(name);
}
/**
* Set cookies
**/
function setCookie(name, value, day) {
let date = new Date();
(() + day);
= name + '=' + value + ';expires=' + date;
};
/**
* Get cookies
**/
function getCookie(name) {
let reg = RegExp(name + '=([^;]+)');
let arr = (reg);
if (arr) {
return arr[1];
} else {
return '';
}
};
/**
* Delete cookies
**/
function delCookie(name) {
setCookie(name, null, -1);
};
/**
* Export
**/
export {
timestampToTime,
timestampToDay,
setStore,
getStore,
removeStore,
setCookie,
getCookie,
delCookie
}
The above is the detailed content of the complete picture and text tutorial for using Exceljs to export Excel files in Vue pure front-end. For more information about exporting Excel with Vue Exceljs, please pay attention to my other related articles!