Foreword
For front-end developers who frequently build enterprise management backends, working with tables to manipulate data is essential. When refactoring existing projects, it’s a good practice to encapsulate some reusable custom hooks. This article is ‘useExportExcel.js’
Hooks encapsulated based on a personal project environment; this article only shares insights and best practices for hook encapsulation, so the accompanying code may not be directly applicable to others.
Project Environment
Vue 3.x + Ant Design Vue 3.x + Vite 3.x
For the biggest role of the enterprise management background, it is used to manage various data conditions in the enterprise. At the same time, based on the actual business process, customers have large-scale reporting requirements at the end of the year (middle). Therefore, document output in the form of data reports is essential. This article is based on the Hooks-Export Data Report, which is encapsulated in this common demand scenario.
Encapsulation Thinking: Report Data Sources
-Backend API returns data
The back end returns a binary blob file, and the front end uses the blob to download it, that is, refer to the method of ‘useDownloadFile.js’.
-Export interface data from the frontend
The front-end export interface data accounts for a relatively small proportion in the enterprise management background, and is generally used for special cases with a small amount of data. For example, one’s own project is a failure data report and failure reason statistics table displayed after the user fails to import some data into Excel, which is exported by the front-end export data.
Encapsulation and Decomposition: Frontend Report Generation
Receive ‘options’ configuration objects, including data (source data), key (unique identification of row data used to generate the table), title (table title), and fileName (export file name)
// 通过数组数据前端导出excel
const exportByArray = options => {
if (!options.data || !options.key || !options.title || !options.fileName)
return new Error('缺少必需参数');
const arr = options.data.map(v =>
options.key.map(j => {
return v[j];
}),
);
arr.unshift(options.title);
const ws = utils.aoa_to_sheet(arr);
const colWidth = arr.map(row =>
row.map(val => {
if (val == null) {
return { wch: 10 };
} else if (val.toString().charCodeAt(0) > 255) {
return { wch: val.toString().length * 2 };
} else {
return { wch: val.toString().length };
}
}),
);
const result = colWidth[0];
for (let i = 1; i < colWidth.length; i++) {
for (let j = 0; j < colWidth[i].length; j++) {
if (result[j]['wch'] < colWidth[i][j]['wch']) {
result[j]['wch'] = colWidth[i][j]['wch'];
}
}
}
ws['!cols'] = result;
const wb = utils.book_new();
utils.book_append_sheet(wb, ws, options.fileName);
writeFile(wb, options.fileName + '.xlsx');
};Sheet.js: A Method for Generating Reports on the Front End
The ’utils.aoa_to_sheet ’,‘utils.book_new’,‘utils.book_append_sheet’, and ‘writeFile’ used in the front-end report generation method are all derived from SheetJS.
Step1: Project installation dependency ‘yarn add xlsx’
Step2: Introducing ’import { utils, writeFile } from ‘xlsx’ in the Hooks file’’
Step3: Refer to the official API and improve the front-end export method in Hooks SheetJS-Utility Functions
-utils.aoa_to_sheet
Converting a two-dimensional array into a sheet will automatically process data of types such as number, string, boolean, boolean, date, etc.
-utils.table_to_sheet
If the dom of a table is directly converted into a sheet, the colspan and rowspan will be automatically identified and converted into corresponding cells for merging.
-utils.json_to_sheet
convert an array of objects key-value into a sheet. you can set the header
These three methods are all SheetJS export methods. There are differences. Considering the actual data, the last choice is’utils. aoa_to_sheet ’. The other methods can be found in the official document.
The above is a complete report export process. ‘utils.book_new’ => Create a workbook ‘utils.aoa_to_sheet’ => Convert source data to a worksheet ‘utils.book_append_sheet’ => Append a worksheet to the workbook ‘writeFile’ => Call download
Encapsulation and Decomposition: Optimization of Data Export from Backend APIs
Because it is necessary to request the back-end interface to export, that is, download the returned binary file, still consider the user experience design, add a secondary confirmation pop-up window, and take the token necessary for the interface from the store.
// 打开导出文件确认弹窗
const exportByResBlob = options => {
Modal.confirm({
title: options.title ? options.title : '导出确认',
content: options.content ? options.content : '确认导出报表吗？',
onOk() {
downloadFile(options);
return Promise.resolve();
},
});
};Complete code for useExportExcel.js
export function useExportExcel() {
const userStore = useUserStore();
let xhr = null;
let downloading = false; // 限制同一文件同时触发多次下载
onBeforeUnmount(() => {
xhr && xhr.abort();
});
// 打开导出文件确认弹窗
const exportByResBlob = options => {
Modal.confirm({
title: options.title ? options.title : '导出确认',
content: options.content ? options.content : '确认导出报表吗？',
onOk() {
downloadFile(options);
return Promise.resolve();
},
});
};
// 通过请求后端接口文件流导出excel
const downloadFile = options => {
try {
if (downloading || !options.url || !options.fileName)
return new Error('缺少必需参数');
downloading = true;
const paramsStr = stringify(options.params || {});
xhr = new XMLHttpRequest();
xhr.responseType = 'blob';
if (paramsStr) {
xhr.open('get', `${options.url}?${paramsStr}`, true);
} else {
xhr.open('get', options.url, true);
}
xhr.setRequestHeader('token', userStore.userToken);
xhr.onloadend = function (e) {
if (e.target.status === 200) {
const aElement = document.createElement('a');
const blob = e.target.response;
const url = window.URL.createObjectURL(blob);
aElement.style.display = 'none';
aElement.href = url;
aElement.download = `${options.fileName}.xlsx`;
document.body.appendChild(aElement);
aElement.click();
if (window.URL) {
window.URL.revokeObjectURL(blob);
} else {
window.webkitURL.revokeObjectURL(blob);
}
document.body.removeChild(aElement);
downloading = false;
}
};
xhr.send();
} catch (e) {
console.error(e);
downloading = false;
Modal.error({
title: '提示',
content: '导出发生异常，请重试',
});
}
};
// 通过数组数据前端导出excel
const exportByArray = options => {
if (!options.data || !options.key || !options.title || !options.fileName) return new Error('缺少必需参数');
const arr = options.data.map(v =>
options.key.map(j => {
return v[j];
}),
);
arr.unshift(options.title);
const ws = utils.aoa_to_sheet(arr);
const colWidth = arr.map(row =>
row.map(val => {
if (val == null) {
return { wch: 10 };
} else if (val.toString().charCodeAt(0) > 255) {
return { wch: val.toString().length * 2 };
} else {
return { wch: val.toString().length };
}
}),
);
const result = colWidth[0];
for (let i = 1; i < colWidth.length; i++) {
for (let j = 0; j < colWidth[i].length; j++) {
if (result[j]['wch'] < colWidth[i][j]['wch']) {
result[j]['wch'] = colWidth[i][j]['wch'];
}
}
}
ws['!cols'] = result;
const wb = utils.book_new();
utils.book_append_sheet(wb, ws, options.fileName);
writeFile(wb, options.fileName + '.xlsx');
};
return {
exportByResBlob,
exportByArray,
};
}