Vue2集成LuckExcel实现excel在线编辑及保存

LuckSheet

gitee网址：https://gitee.com/mengshukeji/Luckysheet
luckSheet文档网址：https://dream-num.github.io/LuckysheetDocs

npm安装相关依赖

npm i exceljs file-saver luckyexcel

vue使用luckSheet

第一步： 通过CDN引入

在index.html中引入下面的css和js

如果实现可以访问上面网址，查找可以使用的地址
百度网盘地址：luckExcel.zip

<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/plugins/css/pluginsCss.css' />
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/plugins/plugins.css' />
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/css/luckysheet.css' />
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/assets/iconfont/iconfont.css' />
<script src="https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/plugins/js/plugin.js"></script>
<script src="https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/luckysheet.umd.js"></script>

第二步：指定一个excel编辑容器

<div id="luckysheet" style="margin:0px;padding:0px;position:absolute;width:100%;height:100%;left: 0px;top: 0px;"></div>

第三步：创建一个表格

html:
<script>$(function () {//配置项var options = {container: 'luckysheet' //luckysheet为容器id}luckysheet.create(options)})
</script>vue:mounted() {this.init();},methods: {init() {var options = {container: 'luckysheet'}luckysheet.create(options);}

至此便实现Excel在线编辑的功能

通过文件url实现excel文件的加载

 LuckyExcel.transformExcelToLuckyByUrl(this.localUrl, this.localName,(exportJson, luckysheetfile) => {if (exportJson.sheets == null || exportJson.sheets.length === 0) {alert("Fail to read the content of the excel file")return;}const firstData = [];const secondData = {};for (let i = 0; i < exportJson.sheets.length; i++) {firstData[i] = {name: exportJson.sheets[i].name,index: exportJson.sheets[i].index,order: exportJson.sheets[i].order,status: exportJson.sheets[i].status,config: exportJson.sheets[i].config}firstData[i].config.row = 10parseInt(exportJson.sheets[i].status) === 1 ? firstData[i].celldata = exportJson.sheets[i].celldata : 0 == 0secondData[exportJson.sheets[i].index] = exportJson.sheets[i].celldata}console.log('exportJson',exportJson)// luckysheet生成网页excelluckysheet.destroy()luckysheet.create({container: 'luckysheetDiv', // 设定DOM容器的idshowtoolbar: false, // 是否显示工具栏showinfobar: false, // 是否显示顶部信息栏showsheetbarConfig: {add: false, //新增sheetmenu: false, //sheet管理菜单sheet: true //sheet页显示},sheetRightClickConfig: {delete: false, // 删除copy: false, // 复制rename: false, //重命名color: false, //更改颜色hide: false, //隐藏，取消隐藏move: false, //向左移，向右移},showstatisticBar: false, // 是否显示底部计数栏sheetBottomConfig: false, // sheet页下方的添加行按钮和回到顶部按钮配置allowEdit: false, // 是否允许前台编辑enableAddRow: false, // 是否允许增加行enableAddCol: false, // 是否允许增加列sheetFormulaBar: false, // 是否显示公式栏enableAddBackTop: false, //返回头部按钮data: [exportJson.sheets[0]], //表格内容gridKey: this.localUrl,// loadUrl: JSON.stringify(firstData), //首先加载的数据，被修改后需要String类型// loadSheetUrl: JSON.stringify(secondData), //随后加载的数据，被修改后需要String类型title: "", //表格标题});})

Excel工具类export.js

// import { createCellPos } from './translateNumToLetter'
import Excel from 'exceljs'
import FileSaver from 'file-saver'
import http from "../request/http";
import { Message } from 'element-ui';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 => {const blob = new Blob([data], {type: 'application/vnd.ms-excel;charset=utf-8'})console.log('导出成功！')FileSaver.saveAs(blob, `${value}.xlsx`)})return buffer
}const excelUpload = async function (luckysheet,name) {// 参数为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 => {const blob = new Blob([data], {type: 'application/vnd.ms-excel;charset=utf-8'})const formData = {docName: `${name || "document"}.xlsx`,docType: "excel",file: blob,}try {const response = http.post("/documents/save", formData, {headers: {"Content-Type": "multipart/form-data",},});if (response.data.code === 200) {Message.success("文件保存到数据库成功");} else {Message.error(response.data.message);}} catch (error) {Message.error("文件保存到数据库失败");}// 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) {if (!Array.isArray(cellArr)) {return}cellArr.forEach(function (row, rowid) {row.every(function (cell, columnid) {if (!cell) {return true}let 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))// eslint-disable-next-line no-unused-varsfor (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}}// 出现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
}// eslint-disable-next-line no-unused-vars
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,excelUpload }