vue3+luckyexcel+php在线编辑excel文件
开发过程中，需要开发一个在线编辑excel文档的功能，找到了这个合适的组件
Luckysheet ，一款纯前端类似excel的在线表格，功能强大、配置简单、完全开源。
可以导入文档，预览、编辑、保存、导出等功能，可以满足大部分需求
第一步：需要先安装 vue3 运行下面三个安装命令
npm install exceljs -S
npm install luckyexcel -S
npm install file-saver
第二步：前端部分index.html 加入引用代码
<link rel='stylesheet' href='/luckysheet/pluginsCss.css' />
<link rel='stylesheet' href='/luckysheet/plugins.css' />
<link rel='stylesheet' href='/luckysheet/luckysheet.css' />
<link rel='stylesheet' href='/luckysheet/iconfont.css' />
<script src="/luckysheet/plugin.js"></script>
<script src="/luckysheet/luckysheet.umd.js"></script>组件部分test.vue
<template>
<div style="position: absolute; top: 0">
<input id="uploadBtn" type="file" @change="loadExcel" />
<button class="btn" @click="getExcel">后台数据</button>
<span>Or文件:</span>
<select v-model="selected" @change="selectExcel">
<option disabled value="">Choose</option>
<option v-for="option in options" :key="option.text" :value="option.value">
{{ option.text }}
</option>
</select>
<input class="inp" type="text" v-model="excelTitel">
<button class="blueBtn" @click="editClicked">编辑</button>
<button class="btn" @click="saveExcel">保存</button>
<a href="javascript:void(0)" @click="downloadExcel">下载</a>
</div>
<div id="luckysheet"></div>
<div v-show="isMaskShow" id="tip">Downloading</div>
</template>test.vue script代码部分
import { ref, onMounted } from 'vue'
import http from '@/assets/js/procure-http.js'
import { exportExcel } from '@/components/export'
import LuckyExcel from 'luckyexcel'
const isMaskShow = ref(false)
const selected = ref('')
const jsonData = ref({})
const excelTitel = ref('')
const congifdata = ref({
container: 'luckysheet',
title: "bi", // 工作簿名称
lang: "zh", // 设定表格语言 国际化设置，允许设置表格的语言，支持中文("zh")和英文("en")
allowCopy: false, // 是否允许拷贝
showtoolbar: false, // 是否显示工具栏
showinfobar: true, // 是否显示顶部信息栏
showsheetbar: false, // 是否显示底部sheet页按钮
showstatisticBar: false, // 是否显示底部计数栏
sheetBottomConfig: false, // sheet页下方的添加行按钮和回到顶部按钮配置
allowEdit: false, // 是否允许前台编辑
enableAddRow: false, // 允许增加行
enableAddCol: false, // 允许增加列
userInfo: false, // 右上角的用户信息展示样式
showRowBar: false, // 是否显示行号区域
showColumnBar: false, // 是否显示列号区域
sheetFormulaBar: false, // 是否显示公式栏
enableAddBackTop: false,//返回头部按钮
rowHeaderWidth: 0,//纵坐标
columnHeaderHeight: 0,//横坐标
showstatisticBarConfig: {
count:false,
view:false,
zoom:false,
},
showsheetbarConfig: {
add: false, //新增sheet
menu: false, //sheet管理菜单
sheet: false, //sheet页显示
},
forceCalculation: true,//强制计算公式
})
const options = ref([
{ text: 'Money Manager.xlsx', value: 'https://xxxxxx/storage/salarytemp/20231222/20231222162622.xlsx' },
{text: 'Activity costs tracker.xlsx', value: 'https://xxxxxx/storage/salary/20231031/0f724adf33a2d3d0b95071b0c52fb711.xlsx'}
])
const loadExcel = (evt) => {
const files = evt.target.files
if (files == null || files.length == 0) {
alert('No files wait for import')
return
}
let name = files[0].name
let suffixArr = name.split('.'),
suffix = suffixArr[suffixArr.length - 1]
if (suffix != 'xlsx') {
alert('Currently only supports the import of xlsx files')
return
}
LuckyExcel.transformExcelToLucky(files[0], function (exportJson, luckysheetfile) {
if (exportJson.sheets == null || exportJson.sheets.length == 0) {
alert('Failed to read the content of the excel file, currently does not support xls files!')
return
}
console.log('exportJson', exportJson)
jsonData.value = exportJson
console.log(exportJson.sheets)
window.luckysheet.destroy()
excelTitel.value = exportJson.info.name
congifdata.value.data = exportJson.sheets
congifdata.value.title = exportJson.info.name
congifdata.value.userInfo = exportJson.info.name.creator
window.luckysheet.create(congifdata.value)
})
}
const selectExcel = (evt) => {
const value = selected.value
const name = evt.target.options[evt.target.selectedIndex].innerText
if (value == '') {
return
}
isMaskShow.value = true
LuckyExcel.transformExcelToLuckyByUrl(value, name, (exportJson, luckysheetfile) => {
if (exportJson.sheets == null || exportJson.sheets.length == 0) {
alert('Failed to read the content of the excel file, currently does not support xls files!')
return
}
console.log('exportJson', exportJson)
jsonData.value = exportJson
isMaskShow.value = false
window.luckysheet.destroy()
window.luckysheet.create({
container: 'luckysheet', //luckysheet is the container id
showinfobar: false,
data: exportJson.sheets,
title: exportJson.info.name,
userInfo: exportJson.info.name.creator
})
})
}
// 导出
const downloadExcel = () => {
exportExcel(luckysheet.getAllSheets(), excelTitel.value)
}
// 从后台获取数据
const getExcel = () => {
http.get('/index/index', {}, res => {
if(res.code == 200) {
window.luckysheet.destroy()
console.log(JSON.parse(res.data))
congifdata.value.data = JSON.parse(res.data)
congifdata.value.title = '测试'
window.luckysheet.create(congifdata.value)
}
})
}
// 保存excel数据
const saveExcel = () => {
var excel = luckysheet.getAllSheets();
//去除临时数据,减小体积
for(var i in excel)
excel[i].data = undefined
// console.log(JSON.stringify(data))
http.post('/index/update', {data:JSON.stringify(excel)}, res => {
console.log(res)
if(res.code == 200) {
}
})
}
const editClicked = () =>{
congifdata.value.showtoolbar = true
congifdata.value.allowEdit = true
luckysheet.create(congifdata.value)
}
// !!! create luckysheet after mounted
onMounted(() => {
luckysheet.create(congifdata.value)
})
</script>
<style scoped>
#luckysheet {
margin: 0px;
padding: 0px;
position: absolute;
width: 100%;
left: 0px;
top: 30px;
bottom: 0px;
height:900px;
}
#uploadBtn {
font-size: 16px;
}
#tip {
position: absolute;
z-index: 1000000;
left: 0px;
top: 0px;
bottom: 0px;
right: 0px;
background: rgba(255, 255, 255, 0.8);
text-align: center;
font-size: 40px;
align-items: center;
justify-content: center;
display: flex;
}
</style>运行后效果如图
本地引用文件的需要下载好组件
