vue项目导入excel成功后下载导入结果(后端返回的list数组)
需求：
点击批量导入按钮，弹出弹窗。
下载模板如图二
上传后，如果有错误，会弹出提示，如图三
点击查看失败原因，会自动下载失败的excel如图四。
请求参数和返回结果
1. vue项目导出表格功能实现步骤
npm install file-saver@2.0.2
npm install xlsx@0.16.0
需要的页面引入详细看src/utils/exportExcel.js
2. 页面实现
<template><div><el-button type="primary" @click="batchUpAndDown" size="mini">导入商品上下架</el-button><up-loader ref="addUploader" @downLoad="addExcelExport" @upLoad="addExcelImport" :tip="tip" :tipRed="tipRed"></up-loader><el-dialog title="上传结果" :visible.sync="dialogUpLoaderVisible" width="300px"><div style="text-align: center; margin: 20px 0;">上传失败！</div><div slot="footer" style="text-align: center"><el-button type="primary" @click="handleUpLoaderDialogConfirm" size="mini">下载文件，查看失败原因</el-button></div></el-dialog></div>
</template><script>
import exportSheet from '@/utils/exportExcel'
import * as utils from '@/utils/utils'
import UpLoader from "@/module/components/uploader/uploader.vue"
export default {
components: {UpLoader},data(){tip: '说明：批量导入商品上下架状态信息；支持.xlsx .xls格式。',tipRed: '请根据下载模版编辑文件进行上传!',importAddConfig: {regionCode: '区域编号',storeNo: '母店编号',goodsNo: '商品编号',shelfStatusName: '状态（上架/下架）',},dialogUpLoaderVisible: false,importAddFailedConfig: {regionCode: '区域编号',storeNo: '母店编号',goodsNo: '商品编号',shelfStatusName: '状态（上架/下架）',errMsg: '失败原因'},failureList: [],},
methods: {batchUpAndDown() {this.$refs.addUploader.open()},// 下载模版addExcelExport() {console.log('new Date()', new Date())console.log('utils.formatDate(new Date())', utils.formatDate(new Date()))eportsheet.exportFileByConfig([{}], `菜猫商品信息批量导入${utils.formatDate(new Date())}.xlsx`, this.importAddConfig)},async addExcelImport(file) {let parseConfig = {}for (let key in this.importAddConfig) {parseConfig[this.importAddConfig[key]] = key}await eportsheet.parseToJSON(file, parseConfig).then(result => {if(!result){this.$notify.error({ title: '提示', message:"上传文件数据解析为空" })return false}if(result.length > 5000){this.$notify.error({ title: '提示', message:"文件过大，请上传最大支持5000条数据。" })return false}// 循环excel中的每一项for(let i=0; i<result.length; i++){let row = result[i]if(!row.regionCode){this.$notify.error({ title: '提示', message:"区域编号不能为空" })return false}if(!row.storeNo){this.$notify.error({ title: '提示', message:"母店编号不能为空" })return false}if(!row.goodsNo){this.$notify.error({ title: '提示', message:"商品编号不能为空" })return false}if(!row.shelfStatusName){this.$notify.error({ title: '提示', message:"上下架状态不能为空" })return false}}// 将excel的数据转成数组后传给后端，后端返回操作结果的文件流this.$http.post(requestUrl.buyer.batchImportShelfStatus, result).then(res => {if (res.code === 0) {this.$refs.addUploader.close()if (res.data.length == 0) {this.$notify.success({title: '操作提示',message: "导入成功"})this.doSearchHandle()} else {this.dialogUpLoaderVisible = truethis.failureList = res.datareturn}// this.exportLoading = false} else {this.$notify.error({title: '提示',message: res.msg})}})})},handleUpLoaderDialogConfirm() {let exportedData = this.failureListtry {const tHeader = utils.objectToArray(this.importAddFailedConfig, false)const filterVal = utils.objectToArray(this.importAddFailedConfig, true)exportSheet.ExportJsonToExcel({header: tHeader,filterVal,data: exportedData,filename: '商品信息批量导入结果'})this.dialogUpLoaderVisible = false} catch (exception) {this.loading = falsethis.$notify.error({title: '温馨提示',message: '导出遇到错误'})}},}
}</script>
@/module/components/uploader/uploader.vue
<template><!--批量导入弹窗--><el-dialog :visible.sync="visible" size="large" title="批量导入" :loading="loadingInfo"><el-row class="mv10"><el-col :span="24"><label :for="`excelFile${tmp}`" class="excelFileLabel"><span class="file-label-text">请选择文件</span><input accept=".xlm,.xls,.xlt,.xlw,.xlsx" type="file" :id="`excelFile${tmp}`" hidden @change="handleFileSelect" :value="fileValue" /><el-input class="search-input" v-model="file.name" disabled></el-input></label><el-button style="margin-left: 10px" size="small" type="text" @click="exportTemp">下载模版</el-button></el-col><el-col v-if="tip || tipRed"><div class="tipBox">{{tip}}<span class="tipRed">{{tipRed}}</span></div></el-col></el-row><el-row><el-col :span="24"><el-button style="margin-top: 20px" :loading="loadingInfo" size="small" type="primary" @click="submitUpload">导入</el-button><el-button @click="exportErrorFile" v-if="upStatus.code">下载错误列表</el-button></el-col></el-row><el-row class="mt20" v-if="upStatus.code"><el-col :span="24" style="color:red"><p>{{file.name}} 导入失败，请修改后重新上传</p><p>失败原因：{{upStatus.msg}}</p></el-col></el-row></el-dialog>
</template>
<script>export default {name: 'upLoader',props: {loadingInfo: { type: Boolean, default: false },tip: { type: String, default: '' },tipRed: { type: String, default: '' },},data () {return {tmp: Date.now(),visible: false,fileValue: '',loading: false,file: { name: '' },upStatus: {code: '',msg: '',data: []},tempConfig: {}}},methods: {handleFileSelect (e) {this.file = e.target.files[0] || { name: '' }console.log(e.target.files)console.log(this.file)// 如果文件改变需要初始化上传状态this.upStatus = { code: '', msg: '', data: [] }},exportTemp () {try {// let item = {}// Object.keys(this.tempConfig).forEach(v => {// item[v] = ''// })// exportsheet.exportFileByConfig([item], '配送中心盘点.xlsx', this.tempConfig)// this.$notify.success({// title: '提示',// message: '下载模块成功'// })this.$emit('downLoad')} catch (e) {this.$notify.error({title: '提示',message: '模板下载遇到错误'})}},submitUpload () {if (!this.file.name || this.file.name.indexOf(".xl") === -1) {this.$notify.warning({title: '提示',message: '请选择excel文件'})return}this.$emit('upLoad', this.file)// let items = []// let parseConfig = {}// for (let key in this.tempConfig) {// parseConfig[this.tempConfig[key]] = key// }// this.loading = true// try {// items = await exportsheet.parseToJSON(this.file, parseConfig)// // 过滤空行// items = items.filter(item => !!item.LocatorNO)// api.post('/api/CheckInventory/CheckLovInvImport', items, true)// .then(res => {// this.loading = false// if (res.code === 0) {// this.$notify.success({// title: '提示',// message: '导入成功'// })// this.$emit('success', res.data)// this.upStatus.code = 0// this.visible = false// } else {// this.upStatus = {...res}// }// })// .catch(e => {// this.loading = false// this.$notify.error({// title: '提示',// message: e.message// })// })// } catch (e) {// this.loading = false// this.$notify.error({// title: '提示',// message: e.message// })// }},open () {this.visible = truethis.fileValue = ''this.file = { name: '' }},close () {this.visible = false}}
}
</script>
<style scoped>
.file-label-text {cursor: pointer;color: #409eff;
}
.tipBox {margin: 10px 0;
}
.tipRed {color: red;
}
</style>/src/utils/utils.js
/* @description: 将对象的key转化成数组* @param {type} byeKey true 将key转化成数组 false将key对应的值转化成数组* @return:*/
export function objectToArray(obj, byeKey) {let arrList = []for(let key in obj) {if(byeKey) {arrList.push(key)}else {arrList.push(obj[key])}}return arrList
}
src/utils/exportExcel.js
/* eslint-disable */
import { saveAs } from 'file-saver'
import XLSX from 'xlsx'function generateArray(table) {var out = [];var rows = table.querySelectorAll('tr');var ranges = [];for (var R = 0; R < rows.length; ++R) {var outRow = [];var row = rows[R];var columns = row.querySelectorAll('td');for (var C = 0; C < columns.length; ++C) {var cell = columns[C];var colspan = cell.getAttribute('colspan');var rowspan = cell.getAttribute('rowspan');var cellValue = cell.innerText;if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;//Skip rangesranges.forEach(function (range) {if (R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e.c) {for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null);}});//Handle Row Spanif (rowspan || colspan) {rowspan = rowspan || 1;colspan = colspan || 1;ranges.push({s: {r: R,c: outRow.length},e: {r: R + rowspan - 1,c: outRow.length + colspan - 1}});};//Handle ValueoutRow.push(cellValue !== "" ? cellValue : null);//Handle Colspanif (colspan)for (var k = 0; k < colspan - 1; ++k) outRow.push(null);}out.push(outRow);}return [out, ranges];
};function datenum(v, date1904) {if (date1904) v += 1462;var epoch = Date.parse(v);return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
}function sheet_from_array_of_arrays(data, opts) {var ws = {};var range = {s: {c: 10000000,r: 10000000},e: {c: 0,r: 0}};for (var R = 0; R != data.length; ++R) {for (var C = 0; C != data[R].length; ++C) {if (range.s.r > R) range.s.r = R;if (range.s.c > C) range.s.c = C;if (range.e.r < R) range.e.r = R;if (range.e.c < C) range.e.c = C;var cell = {v: data[R][C]};if (cell.v == null) continue;var cell_ref = XLSX.utils.encode_cell({c: C,r: R});if (typeof cell.v === 'number') cell.t = 'n';else if (typeof cell.v === 'boolean') cell.t = 'b';else if (cell.v instanceof Date) {cell.t = 'n';cell.z = XLSX.SSF._table[14];cell.v = datenum(cell.v);} else cell.t = 's';ws[cell_ref] = cell;}}if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);return ws;
}function Workbook() {if (!(this instanceof Workbook)) return new Workbook();this.SheetNames = [];this.Sheets = {};
}function s2ab(s) {var buf = new ArrayBuffer(s.length);var view = new Uint8Array(buf);for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;return buf;
}/* @description: 格式化要导出的数据* @param {type} * @return: */
function formatJson(filterVal, jsonData) {return jsonData.map(v => filterVal.map(j => {if (j === 'timestamp') {return parseTime(v[j])} else {return v[j]}}))
}function export_table_to_excel(id) {var theTable = document.getElementById(id);var oo = generateArray(theTable);var ranges = oo[1];/* original data */var data = oo[0];var ws_name = "SheetJS";var wb = new Workbook(),ws = sheet_from_array_of_arrays(data);/* add ranges to worksheet */// ws['!cols'] = ['apple', 'banan'];ws['!merges'] = ranges;/* add worksheet to workbook */wb.SheetNames.push(ws_name);wb.Sheets[ws_name] = ws;var wbout = XLSX.write(wb, {bookType: 'xlsx',bookSST: false,type: 'binary'});saveAs(new Blob([s2ab(wbout)], {type: "application/octet-stream"}), "test.xlsx")
}function export_json_to_excel({multiHeader = [],header,filterVal,data,filename,merges = [],autoWidth = true,bookType = 'xlsx'
} = {}) {/* original data */filename = filename || 'excel-list'data = formatJson(filterVal, data)data.unshift(header);for (let i = multiHeader.length - 1; i > -1; i--) {data.unshift(multiHeader[i])}var ws_name = "SheetJS";var wb = new Workbook(),ws = sheet_from_array_of_arrays(data);if (merges.length > 0) {if (!ws['!merges']) ws['!merges'] = [];merges.forEach(item => {ws['!merges'].push(XLSX.utils.decode_range(item))})}if (autoWidth) {/*设置worksheet每列的最大宽度*/const colWidth = data.map(row => row.map(val => {/*先判断是否为null/undefined*/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};}}))/*以第一行为初始值*/let 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;}/* add worksheet to workbook */wb.SheetNames.push(ws_name);wb.Sheets[ws_name] = ws;var wbout = XLSX.write(wb, {bookType: bookType,bookSST: false,type: 'binary'});saveAs(new Blob([s2ab(wbout)], {type: "application/octet-stream"}), `${filename}.${bookType}`);
}/ @excelFile File* @config Object 解析字段* @setter Object XLSX.utils.sheet_to_json的配置
*/
function parseToJSON (excelFile, config, setter = {}) {return new Promise((resolve, reject) => {if (window.FileReader) {let result = []let fr = new FileReader()fr.readAsBinaryString(excelFile)fr.onload = ev => {try {let data = ev.target.result// 以二进制形式读取excel文件let workbook = XLSX.read(data, {type: 'binary'})// 只遍历第一个表let name = workbook.SheetNames[0]let sheet = workbook.Sheets[name]if (sheet) {result = XLSX.utils.sheet_to_json(sheet, {defval: '', ...setter})if (config) {result.forEach(item => {for (let key in item) {if (config[key]) {item[config[key]] = item[key]if(config[key] !== key) {delete item[key]}}}})}}resolve(result)} catch (e) {reject(new Error("文件类型不正确"))}}return}reject(new Error("该浏览器不支持该功能，请更换或升级浏览器"))})
}export default {export_table_to_excel,ExportJsonToExcel: export_json_to_excel,parseToJSON
}
