vue在线编辑表格导入导出

npm i file-saver
npm i exceljs 
npm i luckyexcel

index.html （方式一在html中引入）

<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>

exportExcel.js

// const Excel = require('exceljs/dist/es5/exceljs.browser')
import Excel from 'exceljs'import FileSaver from 'file-saver'export let exportExcel = (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',})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
}

LuckyExcel.vue

<template><div><div class="control"><p>在线链接: <input type="text" v-model="fileURL" placeholder="输入在线地址" /><button @click="getOriginFile">预览链接文件</button></p><p>本地文件(.xlsx格式) :<input type="file" @change="previewLocal" /></p><p><button @click="exportFile">导出当前表格</button></p><p>没有做loading处理，请打开控制台查看打印信息</p></div><!-- 用于渲染表格的容器 --><div id="luckysheet"></div></div>
</template>
<script>
import axios from 'axios'
import { exportExcel } from '@/utils/exportExcel.js'
import LuckyExcel from "luckyexcel"
export default {name: 'LuckyExcel',data() {return {fileURL: ''}},created() {this.loadPlugins()},methods: {loadPlugins() {const baseURL = '//cdn.jsdelivr.net/npm/luckysheet@latest/dist'this.loading = truethis.tip = '正在加载依赖插件，请耐心等待...'Promise.all([this.asynLoad(`${baseURL}/plugins/css/pluginsCss.css`, true),this.asynLoad(`${baseURL}/plugins/plugins.css`, true),this.asynLoad(`${baseURL}/css/luckysheet.css`, true),this.asynLoad(`${baseURL}/assets/iconfont/iconfont.css`, true),this.asynLoad(`${baseURL}/plugins/js/plugin.js`),this.asynLoad(`${baseURL}/luckysheet.umd.js`),]).then(() => {luckysheet = window.luckysheetthis.getOriginFile() // 获取远端文件}).catch(res => {this.loading = falsethis.errStatus = 1this.errorTitle = '插件加载失败，请刷新后重试！'})},asynLoad(src, isCss = false) {return new Promise(res => {let elif (isCss) {el = document.createElement('link')el.rel = 'stylesheet'el.href = src} else {el = document.createElement('script')el.src = src}document.documentElement.appendChild(el)el.onload = el.onreadystatechange = function() {if (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete') {res(true)}this.onload = this.onreadystatechange = null}})},// 获取远程文件getOriginFile() {this.tip = '正在下载文件...'this.loading = trueaxios({url: this.fileURL,responseType: 'blob',}).then(({ data }) => {const blob = new Blob([data])const file = new File([blob], this.fileName)this.init(file) // 开始渲染}).catch(e => {this.errorTitle = '文件解析失败，请下载后使用 Excel 打开或点击重试！'this.errStatus = 2}).finally(() => {this.loading = false})},// 渲染方法，接受文件对象，如果是本地文件直接传入文件即可init(file) {luckysheet.destroy() // 先销毁当前容器LuckyExcel.transformExcelToLucky(file, exportJson => {if (exportJson.sheets === null || !exportJson.sheets.length) {this.$message.error('无法读取excel文件的内容，当前不支持xls文件！')return}luckysheet.create({container: 'luckysheet',showinfobar: false,lang: 'zh',data: exportJson.sheets,title: exportJson.info.name,userInfo: exportJson.info.name.creator,})})},previewLocal(e) {const file = e.target.files[0]if (!file) return console.log('没有选中文件')this.init(file)},exportFile() {exportExcel(luckysheet.getAllSheets(), 'fileName');}},
}
</script>
<style lang="scss" scoped>
#luckysheet {width: 80vw;height: 70vh;flex: 1;box-shadow: inset 0 0 3px #ccc;
}
.control {display: flex;align-items: center;justify-content: space-between;box-sizing: border-box;
}
.control p + p {margin-left: 10px;padding-left: 10px;border-left: 1px solid #ccc;
}
</style>

使用组件

<template>`
<LuckyExcel></LuckyExcel>
</template>
components: {LuckyExcel},