vue3集成LuckySheet实现导入本地Excel进行在线编辑，以及导出功能

第一步：克隆或者下载下面的代码

git clone https://github.com/dream-num/Luckysheet.git

第二步：安装依赖

npm install
npm install gulp -g  

第三步：运行

npm run dev

效果如下图所示

第四步：打包
打包执行成功后，在文件夹目录下会出现dis文件夹，如下图所示：

npm run build

第五步：本地引入

把dist文件夹中的代码全部复制粘贴到你项目的public文件夹中，index.html文件除外。在你项目的index.html文件中引入如下代码，如果你复制的位置是其他地方，需要用绝对路径引入这些文件。

<link rel='stylesheet' href='./public/plugins/css/pluginsCss.css' />
<link rel='stylesheet' href='./public/plugins/plugins.css' />
<link rel='stylesheet' href='./public/css/luckysheet.css' />
<link rel='stylesheet' href='./public/assets/iconfont/iconfont.css' />
<script src="./public/plugins/js/plugin.js"></script>
<script src="./public/luckysheet.umd.js"></script>		

接下来就是在项目中使用这个插件
首先要引入luckyexcel 的依赖，我们导入导出本地excel会用到

 npm install luckyexcel --save如果引入依赖报错可能是依赖冲突，可以使用下面的npm install luckyexcel --save --force

然后创建一个vue页面文件

<template><div><div style="height: 10px;position: absolute"><el-uploadref="upload"class="upload-demo"action="https://run.mocky.io/v3/9d059bf9-4660-45f2-925d-ce80ad6c4d15":limit="1":on-change="handleFileChange":auto-upload="false"accept=".xlsx"><template #trigger><el-button type="primary">select file</el-button></template></el-upload></div><div v-if="isShow" id="luckysheet" class="luckysheet-wrap"></div></div>
</template><script setup>
import { ref, onMounted } from 'vue';
import LuckyExcel from 'luckyexcel';
import {ElMessage} from "element-plus";const isShow = ref(false)function handleFileChange(file, newFileList) {const selectedFile = file.raw;if (!(selectedFile instanceof File)) {console.error('传入了非文件对象');return;}if (selectedFile.type === 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') {console.log('文件是xlsx类型');} else {console.error('不是xlsx文件');ElMessage.error('请选择xlsx类型文件')return;}let filename = selectedFile.name.replace(/\.[^/.]+$/, ""); // 去除文件扩展名let reader = new FileReader();reader.onload = async (e) => {let fileData = e.target.result;try {isShow.value = trueLuckyExcel.transformExcelToLucky(fileData, async (exportJson, luckysheetfile) => {console.log(exportJson.sheets)creatExcel(filename, JSON.stringify(exportJson.sheets))});} catch (e) {console.error(e);}};reader.onerror = function() {console.error("File could not be read! Code " + reader.error.code);};reader.readAsArrayBuffer(selectedFile);
}function creatExcel(title, content){const options = {container: 'luckysheet', // 设定DOM容器的idtitle: 'excel 表格', // 设定表格名称lang: 'zh', // 设定表格语言hook: {updated: (e) => {//监听更新,并在1s后自动保存$('#luckysheet_info_detail_save').text("已修改")let title = $('#luckysheet_info_detail_input').val();let content = luckysheet.getAllSheets();//去除临时数据,减小体积for (let i in content)content[i].data = undefinedconsole.log(title)console.log(content)}},}options.data = JSON.parse(content)options.title = title;window.luckysheet.create(options)
}onMounted(() => {});
</script><style scoped>
.luckysheet-wrap {margin: 0px;padding: 0px;position: absolute;width: 100%;height: 100%;left: 0px;top: 0px;
}
</style>

点击按钮选择一个xlsx文件就能导入成功了，效果如下（这里只能导入xlsx文件，导入xls文件会报错，暂时不知道什么原因，如果有xls文件的话可以把表格另存为xlsx类型的文件再导入）

下面放一个完整的demo展示效果

<template><div style="width: 100%; height: 100vh;overflow: auto;"><div><div style="display: flex;"><div style="margin-right: 10px;"><el-upload ref="upload" :auto-upload="false" accept=".xlsx" :show-file-list='false' :on-change="handleChangeUpload"action="#" class="upload-demo" multiple><el-button type="primary">导入</el-button></el-upload></div><el-button type="primary" @click="goBack()">返回</el-button></div></div><span>电源配线图</span><el-table :data="dataList" width="100%" border :max-height="750"><el-table-column label="序号" align="center" key="id" prop="id" fixed width="100px" /><el-table-column label="模板名称" align="left" key="name" prop="name"/><el-table-column label="导入时间" align="center" key="addTime" prop="addTime"/><el-table-column label="编辑时间" align="center" key="upTime" prop="upTime"/><el-table-column label="操作" align="center" class-name="small-padding fixed-width" fixed="right"><template #default="scope"><el-button link type="primary" icon="Edit" @click="updateData(scope.row)">编辑</el-button><el-button link type="primary" icon="Delete" @click="deleteData(scope.row)">删除</el-button></template></el-table-column></el-table><paginationv-show="total > 0":total="total"v-model:page="pageNum"v-model:limit="pageSize"@pagination="getDataList":page-sizes="[5, 10, 20, 50]"/></div>
</template><script setup>import {onBeforeUnmount, ref} from "vue";
import {ElMessage} from "element-plus";
import {closeWindow, openCenteredWindow, verifyCommand} from "../../openWindow";
import {useRouter} from "vue-router";
import {deletePower, importPower, selectPowerList} from "../../../../api/draw/power";
import LuckyExcel from 'luckyexcel';const {proxy} = getCurrentInstance();
const router = useRouter();const dataList = ref([])const total = ref(0)
const pageNum = ref(1)
const pageSize = ref(10)//查询数据
function getDataList(){selectPowerList({pageNum: pageNum.value,pageSize: pageSize.value}).then(result => {dataList.value = result.rowstotal.value = result.total})
}//删除数据
async function deleteData(row){if ( await verifyCommand() ){proxy.$confirm('确定删除吗?', '提示', {confirmButtonText: '确定',cancelButtonText: '取消',type: 'warning'}).then(() => {let data = {id: row.id,}deletePower(data).then(res=>{if (res.code === 200){getDataList()ElMessage.success('删除成功！');}else {ElMessage.error('删除失败')}})}).catch(() => {proxy.$message({type: 'info',message: '取消删除'});});}
}//编辑按钮
function updateData(row){router.push({name: 'excel',state: {id: row.id,title: row.name,content: row.content,}});
}function handleChangeUpload(file) {const selectedFile = file.raw;if (!(selectedFile instanceof File)) {console.error('传入了非文件对象');return;}if (selectedFile.type === 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') {console.log('文件是xlsx类型');} else {console.error('不是xlsx文件');ElMessage.error('请选择xlsx类型文件')return;}let filename = selectedFile.name.replace(/\.[^/.]+$/, ""); // 去除文件扩展名let reader = new FileReader();reader.onload = async (e) => {let fileData = e.target.result;try {LuckyExcel.transformExcelToLucky(fileData, async (exportJson, luckysheetfile) => {console.log(exportJson.sheets)//更改配置中的行数为当前最大行//exportJson.sheets[0].config.rowlen = exportJson.sheets[0].celldata[exportJson.sheets[0].celldata.length-1].r+2let data = {name: filename,content: JSON.stringify(exportJson.sheets)}importPower(data).then(res=>{if (res.code === 200){getDataList()ElMessage.success('导入成功')}else {ElMessage.error(res.msg)}})});} catch (e) {console.error(e);}};reader.onerror = function() {console.error("File could not be read! Code " + reader.error.code);};reader.readAsArrayBuffer(selectedFile);
}onBeforeUnmount (() => {closeWindow();
});// 监听页面即将刷新的事件
window.addEventListener('beforeunload', function (event) {closeWindow();
});const goBack = () => {router.go(-1); // 返回上一页
};getDataList();
</script><style scoped></style>

<template><div><div id="luckysheet" class="luckysheet-wrap"></div></div>
</template><script setup>
import { ref, onMounted } from 'vue';
import {ElMessage} from "element-plus";
import {updatePower} from "../../../api/draw/power";onMounted(() => {setExcelData();setExcelStyle();
});//对表格数据进行渲染
function setExcelData(){let title = history.state.title;let content = history.state.content;const options = {container: 'luckysheet', // 设定DOM容器的idtitle: 'excel 表格', // 设定表格名称lang: 'zh', // 设定表格语言hook: {updated: (e) => {//监听更新,并在1s后自动保存$('#luckysheet_info_detail_save').text("已修改")let title = $('#luckysheet_info_detail_input').val();let content = luckysheet.getAllSheets();//去除临时数据,减小体积for (let i in content)content[i].data = undefined}},}options.data = JSON.parse(content)options.title = title;window.luckysheet.create(options)
}//对默认表格的样式进行修改
function setExcelStyle(){//去除左上角logolet leftLogo = document.querySelector('.luckysheet-share-logo');leftLogo.className = '';//去除左上角返回按钮let leftButton = document.getElementById('luckysheet_info_detail_title');leftButton.remove();// 创建一个新的保存按钮let newDiv = document.createElement('div');newDiv.innerHTML = '保存';newDiv.style.cursor = 'pointer'newDiv.addEventListener('click', function() {saveData();});let firstChild = document.querySelector('#luckysheet_info_detail').firstElementChild;document.querySelector('#luckysheet_info_detail').insertBefore(newDiv, firstChild);
}//保存数据
function saveData(){let title = $('#luckysheet_info_detail_input').val();let content = luckysheet.getAllSheets();//去除临时数据,减小体积for (let i in content)content[i].data = undefined//更改配置中的行数为当前最大行content[0].config.rowlen = content[0].celldata[content[0].celldata.length-1].r+2let id = history.state.idlet data = {id: id,name: title,content: JSON.stringify(content)}updatePower(data).then(res=>{if (res.code === 200){history.state.title = title;history.state.content = JSON.stringify(content);ElMessage.success('保存成功！');}else {ElMessage.error('保存失败')}})
}
</script><style scoped>
.luckysheet-wrap {margin: 0px;padding: 0px;position: absolute;width: 100%;height: 100%;left: 0px;top: 0px;
}
</style>

接下来是导出为本地excel
首先在项目中创建export.js文件

// import { createCellPos } from './translateNumToLetter'
import Excel from 'exceljs'import FileSaver from 'file-saver'const exportExcel = function(luckysheet, value) {// 参数为luckysheet.getluckysheetfile()获取的对象// 1.创建工作簿，可以为工作簿添加属性const workbook = new Excel.Workbook()// 2.创建表格，第二个参数可以配置创建什么样的工作表if (Object.prototype.toString.call(luckysheet) === '[object Object]') {luckysheet = [luckysheet]}luckysheet.forEach(function(table) {if (table.data.length === 0) return  true// ws.getCell('B2').fill = fills.const worksheet = workbook.addWorksheet(table.name)const merge = (table.config && table.config.merge) || {}const borderInfo = (table.config && table.config.borderInfo) || {}//设置单元格宽度// 3.设置单元格合并,设置单元格边框,设置单元格样式,设置值setStyleAndValue(table.data, worksheet)setMerge(merge, worksheet)setBorder(borderInfo, worksheet)return true})// return// 4.写入 bufferconst buffer = workbook.xlsx.writeBuffer().then(data => {// console.log('data', data)const blob = new Blob([data], {type: 'application/vnd.ms-excel;charset=utf-8'})console.log("导出成功！")FileSaver.saveAs(blob, `${value}.xlsx`)})return buffer
}var setMerge = function(luckyMerge = {}, worksheet) {const mergearr = Object.values(luckyMerge)mergearr.forEach(function(elem) {// elem格式：{r: 0, c: 0, rs: 1, cs: 2}// 按开始行，开始列，结束行，结束列合并（相当于 K10:M12）worksheet.mergeCells(elem.r + 1,elem.c + 1,elem.r + elem.rs,elem.c + elem.cs)})
}var setBorder = function(luckyBorderInfo, worksheet) {if (!Array.isArray(luckyBorderInfo)) return// console.log('luckyBorderInfo', luckyBorderInfo)luckyBorderInfo.forEach(function(elem) {// 现在只兼容到borderType 为range的情况// console.log('ele', elem)if (elem.rangeType === 'range') {let border = borderConvert(elem.borderType, elem.style, elem.color)let rang = elem.range[0]// console.log('range', rang)let row = rang.rowlet column = rang.columnfor (let i = row[0] + 1; i < row[1] + 2; i++) {for (let y = column[0] + 1; y < column[1] + 2; y++) {worksheet.getCell(i, y).border = border}}}if (elem.rangeType === 'cell') {// col_index: 2// row_index: 1// b: {//   color: '#d0d4e3'//   style: 1// }const { col_index, row_index } = elem.valueconst borderData = Object.assign({}, elem.value)delete borderData.col_indexdelete borderData.row_indexlet border = addborderToCell(borderData, row_index, col_index)// console.log('bordre', border, borderData)worksheet.getCell(row_index + 1, col_index + 1).border = border}// console.log(rang.column_focus + 1, rang.row_focus + 1)// worksheet.getCell(rang.row_focus + 1, rang.column_focus + 1).border = border})
}
var setStyleAndValue = function(cellArr, worksheet) {worksheet.columns = []if (!Array.isArray(cellArr)) returncellArr.forEach(function(row, rowid) {row.every(function(cell, columnid) {if (!cell) return truelet fill = fillConvert(cell.bg)let font = fontConvert(cell.ff,cell.fc,cell.bl,cell.it,cell.fs,cell.cl,cell.ul)let alignment = alignmentConvert(cell.vt, cell.ht, cell.tb, cell.tr)let value = ''if (cell.f) {value = { formula: cell.f, result: cell.v }} else if (!cell.v && cell.ct && cell.ct.s) {// xls转为xlsx之后，内部存在不同的格式，都会进到富文本里，即值不存在与cell.v，而是存在于cell.ct.s之后// value = cell.ct.s[0].vcell.ct.s.forEach(arr => {value += arr.v})} else {value = cell.v}//  style 填入到_value中可以实现填充色let letter = createCellPos(columnid)let target = worksheet.getCell(letter + (rowid + 1))// console.log('1233', letter + (rowid + 1))for (const key in fill) {target.fill = fillbreak}target.font = fonttarget.alignment = alignmenttarget.value = valuesetColumnsWidth(worksheet, rowid)return true})})
}var fillConvert = function(bg) {if (!bg) {return {}}// const bgc = bg.replace('#', '')let fill = {type: 'pattern',pattern: 'solid',fgColor: { argb: bg.replace('#', '') }}return fill
}var fontConvert = function(ff = 0,fc = '#000000',bl = 0,it = 0,fs = 10,cl = 0,ul = 0
) {// luckysheet：ff(样式), fc(颜色), bl(粗体), it(斜体), fs(大小), cl(删除线), ul(下划线)const luckyToExcel = {0: '微软雅黑',1: '宋体（Song）',2: '黑体（ST Heiti）',3: '楷体（ST Kaiti）',4: '仿宋（ST FangSong）',5: '新宋体（ST Song）',6: '华文新魏',7: '华文行楷',8: '华文隶书',9: 'Arial',10: 'Times New Roman ',11: 'Tahoma ',12: 'Verdana',num2bl: function(num) {return num === 0 ? false : true}}// 出现Bug，导入的时候ff为luckyToExcel的vallet font = {name: typeof ff === 'number' ? luckyToExcel[ff] : ff,family: 1,size: fs,color: { argb: fc.replace('#', '') },bold: luckyToExcel.num2bl(bl),italic: luckyToExcel.num2bl(it),underline: luckyToExcel.num2bl(ul),strike: luckyToExcel.num2bl(cl)}return font
}var alignmentConvert = function(vt = 'default',ht = 'default',tb = 'default',tr = 'default'
) {// luckysheet:vt(垂直), ht(水平), tb(换行), tr(旋转)const luckyToExcel = {vertical: {0: 'middle',1: 'top',2: 'bottom',default: 'top'},horizontal: {0: 'center',1: 'left',2: 'right',default: 'left'},wrapText: {0: false,1: false,2: true,default: false},textRotation: {0: 0,1: 45,2: -45,3: 'vertical',4: 90,5: -90,default: 0}}let alignment = {vertical: luckyToExcel.vertical[vt],horizontal: luckyToExcel.horizontal[ht],wrapText: luckyToExcel.wrapText[tb],textRotation: luckyToExcel.textRotation[tr]}return alignment
}var borderConvert = function(borderType, style = 1, color = '#000') {// 对应luckysheet的config中borderinfo的的参数if (!borderType) {return {}}const luckyToExcel = {type: {'border-all': 'all','border-top': 'top','border-right': 'right','border-bottom': 'bottom','border-left': 'left'},style: {0: 'none',1: 'thin',2: 'hair',3: 'dotted',4: 'dashDot', // 'Dashed',5: 'dashDot',6: 'dashDotDot',7: 'double',8: 'medium',9: 'mediumDashed',10: 'mediumDashDot',11: 'mediumDashDotDot',12: 'slantDashDot',13: 'thick'}}let template = {style: luckyToExcel.style[style],color: { argb: color.replace('#', '') }}let border = {}if (luckyToExcel.type[borderType] === 'all') {border['top'] = templateborder['right'] = templateborder['bottom'] = templateborder['left'] = template} else {border[luckyToExcel.type[borderType]] = template}// console.log('border', border)return border
}function addborderToCell(borders, row_index, col_index) {let border = {}const luckyExcel = {type: {l: 'left',r: 'right',b: 'bottom',t: 'top'},style: {0: 'none',1: 'thin',2: 'hair',3: 'dotted',4: 'dashDot', // 'Dashed',5: 'dashDot',6: 'dashDotDot',7: 'double',8: 'medium',9: 'mediumDashed',10: 'mediumDashDot',11: 'mediumDashDotDot',12: 'slantDashDot',13: 'thick'}}// console.log('borders', borders)for (const bor in borders) {// console.log(bor)if (borders[bor].color.indexOf('rgb') === -1) {border[luckyExcel.type[bor]] = {style: luckyExcel.style[borders[bor].style],color: { argb: borders[bor].color.replace('#', '') }}} else {border[luckyExcel.type[bor]] = {style: luckyExcel.style[borders[bor].style],color: { argb: borders[bor].color }}}}return border
}function createCellPos(n) {let ordA = 'A'.charCodeAt(0)let ordZ = 'Z'.charCodeAt(0)let len = ordZ - ordA + 1let s = ''while (n >= 0) {s = String.fromCharCode((n % len) + ordA) + sn = Math.floor(n / len) - 1}return s
}function setColumnsWidth(worksheet, index){const border = {top: {style: 'thin',},left: {style: 'thin',},bottom: {style: 'thin',},right: {style: 'thin',},}worksheet.columns.map((column) => {// 表头的样式worksheet.getCell(`${column.letter}1`).border = borderworksheet.getCell(`${column.letter}1`).font = {bold: true,}worksheet.getCell(`${column.letter}1`).fill = {type: 'pattern',pattern: 'solid',// fgColor: { argb: 'FF8FBC8F' }, //表头背景色}// 列宽自适应let width = []column.values.map((value) => {if (!value) {width.push(10)} else if (/.*[\u4e00-\u9fa5]+.*$/.test(value)) {width.push(parseFloat(value.toString().length * 2.15))} else {width.push(parseFloat(value.toString().length * 1.15))}})column.width = Math.max(...width)// 行数据的样式worksheet.getCell(`${column.letter}${index + 2}`).border = border})
}export {exportExcel
}

然后在页面中写一个导出按钮，调用方法

import { exportExcel } from './export'function exportMyExcel(){let title = $('#luckysheet_info_detail_input').val();exportExcel(window.luckysheet.getAllSheets(), title)
}

最后点击导出按钮调用方法，浏览器就会弹出下载excel的窗口（如果是谷歌浏览器可能不会弹出窗口而是直接下载了，可以点击浏览器右上角的下载按钮查看）

ps：注意事项
在导出文件时，如果需要将导出的数据通过接口传输给后端，需要进行文件格式的转换，以file为例。

//获取buffer的
workbook.xlsx.writeBuffer()
.then((arrayBuffer) => {const blob = new Blob(arrayBuffer, {//导出为xlsx文件格式type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'});//转为file格式let file = new File([blob], '导出.xlsx', {type: blob.type});/**这里调用接口上传代码*/}).catch(err=>{dialogVisible.value = false})