网站导航
> 文章列表 > vue3+vite项目集成Luckysheet，前后端通信，编辑回显，项目开发记录

vue3+vite项目集成Luckysheet，前后端通信，编辑回显，项目开发记录

网友: 文章列表 2024-03-22 01:31:06

vue3+vite项目集成Luckysheet，前后端通信，编辑回显，项目开发记录

Luckysheet是一款非常强大的在线excel，配置简单而且完全开源，而且有很多社区解决方案

官方文档地址：Luckysheet文档

要使用Luckysheet，首先引入：

1、在这个网站拉取源码Luckysheet: 🚀Luckysheet ，一款纯前端类似excel的在线表格，功能强大、配置简单、完全开源。

 2、执行 npm install 下载依赖

3、执行  npm run build 进行打包

4、把打包好的dist文件放在自己项目的public文件夹下

5、在根目录的html文件添加以下代码

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

 ok，基本工作已经完成！

5、封装 Luckysheet 组件，在components文件夹下新建Luckysheet文件

<template><div class="box"><div id="luckysheet"></div></div>
</template><script setup lang="ts">
import { exportExcel } from "./export";
import LuckyExcel from 'luckyexcel'
import { Message } from "@arco-design/web-vue";// 配置项
const options = {container: 'luckysheet', // 设定DOM容器的idtitle: 'luckysheet', // 设定表格名称lang: 'zh', // 设定表格语言data:[{name:'Sheet1'}]
}
const initLuckysheet = () =>{luckysheet.destroy()luckysheet.create(options);
}
nextTick(() => {// 初始化表格initLuckysheet()
})
// 获取file
const downloadExcel = async() => {const data = luckysheet.getluckysheetfile()const luckysheetName  = luckysheet.getWorkbookName()const exportData = await exportExcel(data)const blob = new Blob([exportData])const file = new File([blob], luckysheetName + '.xlsx', { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' })return file
}const jsonData = ref({})
// 加载excel
const loadExcel = (evt:any) => {// const files = evt.target.filesconst files = evtconsole.log();if (files == null || files.length == 0) {Message.error('No files wait for import')return}let name = files.namelet suffixArr = name.split('.'),suffix = suffixArr[suffixArr.length - 1]if (suffix != 'xlsx') {Message.error('当前仅支持xlsx文件')throw new Error("当前仅支持xlsx文件");return}LuckyExcel.transformExcelToLucky(files, function (exportJson:any, luckysheetfile:any) {if (exportJson.sheets == null || exportJson.sheets.length == 0) {Message.error('无法读取excel文件的内容，目前不支持xls文件！')return}console.log('exportJson', exportJson)jsonData.value = exportJsonluckysheet.destroy()luckysheet.create({container: 'luckysheet', //luckysheet is the container id// showinfobar: false,data: exportJson.sheets,lang: 'zh', // 设定表格语言title: exportJson.info.name,userInfo: exportJson.info.name.creator})})
}defineExpose({downloadExcel,loadExcel
})</script><style scoped lang="less">
.download{position: absolute;z-index: 99;left: 10px;
}
#luckysheet {margin: 0px;padding: 0px;position: absolute;width: 100%;left: 0px;top: 0;bottom: 0px;::v-deep(.luckysheet_info_detail_back){display: none !important;}::v-deep(.luckysheet-share-logo){display: none !important;}
}</style>

代码解释：

  • 首先在template里面写的<div id="luckysheet"></div>是luckysheet挂载的dom节点

  • options是表格的配置项，具体的配置可以参考官网，我觉得这样就够了

  • 这里面有两个方法，downloadExcel是获取luckysheet文件内容，转换成excel文件

  • loadExcel方法是把后端传过来的excel文件转换成luckysheet文件，回显上去

然后安装所需依赖：npm install exceljs -S 和 npm install luckyexcel -S

然后再luckysheet文件平级新建 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("导出成功！")//如果要下载，就放开下面这句话，然后改luckysheet的对应方法// 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) {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 = valuereturn 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
}export {exportExcel
}

然后就可以在页面中引入了：

<Luckysheet ref="luckysheetRef"></Luckysheet>//js
const luckysheetRef = ref()const handleConfirm = () => {// 获取文件luckysheetRef.value.downloadExcel().then((res:any)=>{console.log(res); //这就是file文件，可以在这里传给后端})
}//回显，我这里是弹窗打开的时候就调接口，把获取到的文件传给luckysheet定义的方法
//blob文件，先转成file文件var file1 = new File([blob], name, {type: 'application/json',lastModified: Date.now()});nextTick(()=>{try {luckysheetRef.value.loadExcel(file1)} catch (error) {state.fillVisible = false}})

这就大功告成了，有什么问题，欢迎留言

网络标签：

相关问题