项目使用的
element-ui
vue-office
xlsx
如果是vue2.6版本或以下还需要额外安装 @vue/composition-api
npm install @vue/composition-api
安装插件 @vue-office/excel需要使用最新版本1.3.0
npm install @vue-office/excel@1.3.0 vue-demi
npm install xlsx@0.16.6
npm install xlsx-populate@1.21.0
<template>
<div class="table-html-wrap">
<el-upload class="upload-demo" :on-change="handleChange" :on-success="successAction" accept=".xlsx" :auto-upload="false" :show-file-list="false">
<el-button size="small" icon="el-icon-upload2" type="primary">上传表格</el-button>
</el-upload>
<vue-office-excel :src="excelUrl" :options="{heightOffset:15,widthOffset:10}" style="height: 80vh;" @rendered="renderedHandler" @error="errorHandler" />
</div>
</template>
<script>
//引入VueOfficeExcel组件
import VueOfficeExcel from '@vue-office/excelUrl'
//引入相关样式
import '@vue-office/excelUrl/lib/index.css'
import XLSX from 'xlsx';
const XlsxPopulate = require('xlsx-populate');
export default {
components: {
VueOfficeExcel
},
data() {
return {
excelUrl: '',
}
},
methods: {
async handleChange(files) {
this.$loading({
lock: true,
text: '拼命上传中，请稍候...',
spinner: 'el-icon-loading',
background: 'rgba(0, 0, 0, 0.7)',
});
var filesType = files.name.substring(files.name.lastIndexOf('.') + 1)
const extension2 = filesType === 'xlsx'
if (!extension2) {
this.$message.error('只支持xlsx表格文件');
this.$loading().close();
return
}
this.fromFileAsync(files.raw)
},
successAction(res, file) {
// 上传成功之后的回调
console.log('上传成功', res);
},
// 对表格的指定的内容进行修改之后预览
async fromFileAsync(blobData) {
let that = this
console.log('blobData', blobData)
// 使用XLSX读取Blob数据
const file = new Blob([blobData], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
const reader = new FileReader();
reader.readAsArrayBuffer(file);
reader.onload = async function (e) {
const workbook = await XlsxPopulate.fromDataAsync(e.target.result);
const valList = that.getValueData(e.target.result)
console.log('workbook--------', workbook)
console.log('valList--------', valList)
const worksheet = workbook.sheet(0);
for (let k in valList) {
const cell = worksheet.cell(valList[k].key); //根据单元格内容 如 A1
cell.value(valList[k].value); // 对工作簿值进行修改
}
// 将修改后的工作簿转换为Blob对象
const modifiedFileData = await workbook.outputAsync();
// 转换为 Blob 类型
const blob = new Blob([modifiedFileData], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
that.excelUrl = blob
that.$loading().close();
};
// 可以在这里下载或处理修改后的文件
},
renderedHandler() {
console.log('渲染完成')
},
errorHandler() {
console.log('渲染失败')
},
attachmentFile(id) {
if (this.excelUrl) {
this.downloadBold(this.excelUrl, false)
return
}
this.downloadData(this.fileUrl + id, '', 'get').then((res) => {
this.downloadBold(res)
});
},
// 下载文件
downloadBold(res) {
var blob = res.data ? res.data : res;
if (window.navigator.msSaveOrOpenBlob) {
navigator.msSaveBlob(blob);
} else {
var downloadElement = document.createElement('a');
if (this.excelUrl) {
blob = this.excelUrl || blob;
}
var href = (window.URL || window.webkitURL).createObjectURL(blob);
downloadElement.href = href;
downloadElement.download = `${this.infoData.title}.xlsx`;
document.body.appendChild(downloadElement);
downloadElement.click();
document.body.removeChild(downloadElement);
window.URL.revokeObjectURL(href);
}
},
// 获取excel内容指定标识 #@#内容#@# 单元格位置
getValueData(data) {
const workbook = XLSX.read(data, { type: 'array' });
const sheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[sheetName];
console.log('workbook---getValueData', workbook)
// 读取Excel文件的数据
console.log('sheet', worksheet)
let keyObj = {} // 获取需要替换的值
const reg = /[a-zA-Z0-9]/ //匹配字母数字 排除!开头的
for (let k in worksheet) {
if (reg.test(k) && this.replaceText(worksheet[k].v)) {
keyObj[worksheet[k].v] = {
key: k, //单元格位置 如 A1 之类的
value: '' // 默认设置值为空 可以根据自己需要修改值
}
}
}
return keyObj
},
replaceText(str) {
const match = str && str.match(/#@#(.*?)#@#/);
let content = ''
if (match && match.length > 1) {
content = match;
}
return content
},
}
}
</script>
