vue3 使用@vue-office/excel预览excel文件 demo
显示如下:
npm地址:https://www.npmjs.com/package/@vue-office/excel
@vue-office还有pdf和docx,按需下载对应插件
npm install @vue-office/excel vue-demi
vue代码如下
app.vue
<template>
<div class="download">
<h1>下载及回显相关</h1>
<div class="box">
<h2>vue-office / excel回显</h2>
<input type="file" id="input"
accept=".xls,.xlsx,.csv"
ref="inputFileRef"
@change="openExcelHandle"
multiple
/>
<el-table
v-if="tableData.length"
ref="tableRef"
:data="tableData"
>
<el-table-column
label="文件名称"
prop="name"
align="left"
>
<template #default="scope">
{{ scope.row.name }}
</template>
</el-table-column>
<el-table-column label="文件大小" prop="size"/>
<el-table-column label="最后修改时间" prop="lastModified" />
<el-table-column label="预览">
<template #default="{ row }">
<el-button @click="singleReview(row)">预览</el-button>
</template>
</el-table-column>
</el-table>
</div>
<excel ref="excelReviewRef" :propsUrl="vueOfficeExcel"/>
</div>
</template>
<script setup name="Download">
import { ref, reactive } from 'vue'
import Excel from './components/vueOfficeExcelReview.vue'
const excelReviewRef = ref(null)
let vueOfficeExcel = reactive({})
const inputFileRef = ref(null)
const tableData = ref([])
// el-input中change事件中的event只是文件地址
// input change事件中的event有file对象
function openExcelHandle(event) {
for(let i = 0; i < event.target.files.length; i++) {
tableData.value.push(event.target.files[i])
}
}
function singleReview(row) {
if (!row) {
excelReviewRef.value.showHandle('', true)
return
}
excelReviewRef.value.showHandle(row)
}
</script>
<style scoped>
.logo {
height: 6em;
padding: 1.5em;
will-change: filter;
transition: filter 300ms;
}
.logo:hover {
filter: drop-shadow(0 0 2em #646cffaa);
}
.logo.vue:hover {
filter: drop-shadow(0 0 2em #42b883aa);
}
</style>
vueofficeExcelReview.vue(主要页面)
<template>
<div class="excel-review">
<!-- 不使用dialog弹框是可以直接在页面下方显示的 -->
<el-dialog
v-model="visibility"
:title="title"
width="98%"
:closeOnClickModal="false"
:closeOnPressEscape="false"
customClass="first-login-dialog"
@close="closeHandle"
>
<VueOfficeExcel
v-if="fileUrl"
:src="fileUrl"
@rendered="renderedHandler"
@error="errorHandler"/>
</el-dialog>
</div>
</template>
<script setup>
import VueOfficeExcel from '@vue-office/excel'
import '@vue-office/excel/lib/index.css'
import { ref, reactive } from 'vue'
const visibility = ref(false)
const fileUrl = ref(null)
function renderedHandler() {
console.log("渲染完成")
}
function errorHandler() {
console.log("渲染失败")
}
let file = reactive({})
const title = ref('excel预览')
function showHandle(argFile) { // 下方使用的是arraybuffer的方式,
file = argFile
let fileReader = new FileReader()
fileReader.readAsArrayBuffer(file)
fileReader.onload = (e) => {
fileUrl.value = fileReader.result
}
title.value += file.name
visibility.value = true
}
function closeHandle() {
title.value = 'excel预览'
fileUrl.value = null
visibility.value = false
}
defineExpose({
showHandle
})
</script>
<style lang="scss" scoped>
.excel-review {
:deep(.el-dialog) {
margin-top: 0;
margin-bottom: 0;
padding: 0;
height: 100vh;
.el-dialog__body {
padding: 0;
.vue-office-excel {
height: calc(100vh - 60px);
}
}
}
}
</style>
vue使用sheetjs提取excel数据并使用el-table进行数据预览
官网链接:https://github.com/liyutg/sheetjs-docs-zh-cn
js:
import XLSX from 'xlsx'
// files是使用inpu标签取到的fileList,tableHeader是自定义传入的table表头
export const ExcelToJson = async function (files, tableHeader) {
if (!files.length) return
const data = []
for (let i = 0; i < files.length; i++) {
const singleFileData = await fileLoop(files[i], tableHeader)
data.push(...singleFileData)
}
return data
}
function fileLoop (file, tableHeader) {
return new Promise((resolve, reject) => {
const fileReader = new FileReader()
fileReader.readAsArrayBuffer(file)
fileReader.onload = (e) => {
const string = e.target.result
const wb = XLSX.read(string)
// sheet_to_json 把工作表转换为JS对象数组
// 假设只有第一个sheet
// 不传{ header: tableHeader }会默认使用excel数据第一行作为表头,且第一行不作为数据
const json = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]], { header: tableHeader })
resolve(json)
}
})
}
ExcelToJson返回值为json格式的数组对象(传入表头数据会自动匹配内容,传入表头 > 实际使用表头,多余的表头会略去;传入表头 < 实际使用表头,多余的内容会略去)。例:[{name: '我是一个名字', age: '18'}]
vue
<template>
<div>
<input type="file"
@change="getExcelFile"
accept=".xls,.xlsx"
multiple
/>
<el-table
v-if="tableData.length"
:data="tableData"
>
<el-table-column
v-for="head in tableHeader"
:label="head"
:prop="head"
/>
</el-table>
</div>
</template>
<script setup>
import { ExcelToJson } from '@/utils/excelToJson'
const tableData = ref([])
const tableHeader = ['id', '名称', '角色', '测试1', '测试2']
async function getExcelFile(event) {
if (!event.target.files.length) return
let files = event.target.files
let fileJson = await ExcelToJson(files, tableHeader)
tableData.value.push(...fileJson)
}
</script>
excel内容如下:
传入tableHeader返回数据如下:
不传tableHeader返回数据如下: