创新项目实训
任务
项目的功能基本都已经实现,为了扩展系统数据分析的功能,参考了ChartExcel,准备实现一个简单的在线AI对Excel操作的功能,下面记录下工作流程。
在线Excel
本项目采用的是开源的工具LuckySheet。
- Luckysheet,一款纯前端类似excel的在线表格,功能强大、配置简单、完全开源。
- 可以通过CDN或者npm进行引入,我们采用的是CDN的方式,即在vue项目中public/index.html中添加下面的脚本代码。
<head> <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' /> </head> <body> <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> <!--yourApp--> </body>
- 使用流程
- 创建容器
<!--关键是id--> <div v-loading="loading" element-loading-text="拼命加载中" element-loading-spinner="el-icon-loading" element-loading-background="rgba(0, 0, 0, 0.8)" id="luckysheet" style="margin-top:39px;padding:0px;position:absolute;width:1470px;height:623px;left: 0px;top: 0px;"> </div>
- 在挂载页面时,进行初始化,创建实例
mounted() { luckysheet.create({ container: "luckysheet", showinfobar: false, showsheetbar: true, showtoolbar: true, showstatisticbar: true, showstatusbar: true, showgrid: true, showrowheader: true, showcolumnheader: true, rowheight: 25, columnwidth: 100, zoom: 100, defaultzoom: 100, defaultrowheight: 25, defaultcolumnwidth: 100, lang: 'zh', // 更多配置选项... }); },
- 到此就可以实现了基本的功能,如果要更多功能或者详细信息,请参考整体配置
- 创建容器
导入Excel功能
使用的是LuckyExcel工具。
- Luckyexcel,是一个适配 Luckysheet 的excel导入导出库,只支持.xlsx格式文件(不支持.xls)。但是似乎官方中并没有导出的功能。
- 通过npm或者CDN进行引入,我们采用的是
npm install luckyexcel
- 使用方法比较简单
- 引入
import LuckyExcel from 'luckyexcel'
- 配合el-upload进行使用
<el-upload action="#" :auto-upload="false" :show-file-list="false" :on-change="importExcel"> <el-button icon="el-icon-upload2">导入 Excel</el-button> </el-upload>
importExcel(file,fileList){ this.file=file.raw // 使用 LuckyExcel 转换文件 LuckyExcel.transformExcelToLucky( this.file, (exportJson) => { console.log(exportJson.sheets) this.totalRow=exportJson.sheets[0].celldata[exportJson.sheets[0].celldata.length-1].r this.totalCol=exportJson.sheets[0].celldata[exportJson.sheets[0].celldata.length-1].c //console.log(this.totalCol) luckysheet.create({ container: "luckysheet", showinfobar: false, showsheetbar: true, showtoolbar: true, showstatisticbar: true, showstatusbar: true, showgrid: true, showrowheader: true, showcolumnheader: true, rowheight: 25, columnwidth: 100, zoom: 100, defaultzoom: 100, defaultrowheight: 25, defaultcolumnwidth: 100, lang: 'zh', data:exportJson.sheets }); }, (err) => { console.error('Import failed. Is your file a valid xlsx?', err); } ); },
- 引入
导出Excel
官方提供了两种方法,我们采用的是结合file-saver与exceljs的方法。
- 安装file-saver与exceljs——
npm i file-saver exceljs
- 下载文件export.js
// import { createCellPos } from './translateNumToLetter' const Excel = require('exceljs') import FileSaver from 'file-saver' export var testaaa = function (){ console.log("..."); } export var 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.写入 buffer const 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.row let column = rang.column for (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.value const borderData = Object.assign({}, elem.value) delete borderData.col_index delete borderData.row_index let 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].v cell.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 = fill break } target.font = font target.alignment = alignment target.value = value 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的val let 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'] = template border['right'] = template border['bottom'] = template border['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 + 1 let s = '' while (n >= 0) { s = String.fromCharCode((n % len) + ordA) + s n = Math.floor(n / len) - 1 } return s }
- 直接调用方法即可
downloadExcel(){ exportExcel(luckysheet.getAllSheets(),"下载") },
- 贴个图
扩展对话框
- 对话框
<div class="input-container"> <el-input v-model="need" type="textarea" style="width:400px;" :rows="4" placeholder="输入内容" /> <div class="button-container"> <el-button circle icon="el-icon-video-play" @click="handleExcel"></el-button> <el-button circle icon="el-icon-refresh-left" @click="handleBack"></el-button> </div> </div> <style scoped> .input-container { position: absolute; bottom: 80px; left: 50%; transform: translateX(-50%); background-color: rgba(90, 79, 79, 0.5); padding: 10px; border-radius: 5px; z-index: 1000; display: flex; /* 使用Flexbox布局 */ } .button-container { display: flex; flex-direction: column; /* 使按钮垂直堆叠 */ margin-left: 10px; margin-top: 10px; align-items: flex-end;; /* 设置交叉轴对齐方式 */ } </style>
- 贴张图,看着有模有样了…
LuckySheet中的API学习
- 更多API的操作请查看官网,下面简单介绍几种常用的。
- 选区操作即自动获取全部数据。
//获取第一个sheet中的有数据的第一行,第一列,最后一行和最后一列 this.totalCol=luckysheet.getAllSheets()[0].celldata[luckysheet.getAllSheets()[0].celldata.length-1].c this.firstCol=luckysheet.getAllSheets()[0].celldata[0].c this.totalRow=luckysheet.getAllSheets()[0].celldata[luckysheet.getAllSheets()[0].celldata.length-1].r this.firstRow=luckysheet.getAllSheets()[0].celldata[0].r //选区操作,获得所有有数据的区域,即页面显示会出现高亮 luckysheet.setRangeShow({row:[this.firstRow,this.totalRow],column:[this.firstCol,this.totalCol]}) //将选区部分的数据,导出为JSON格式,false的意思是首行不是表头 luckysheet.getRangeJson(false)
- 表格操作
//在raw行column列设置值为value luckysheet.setCellValue(raw,column,value)
- 回退操作
handleBack(){ luckysheet.undo() },
AI操作表格
-
有了上面的API,我们的设计思路就是,将表格中的数据导出为JSON格式与输入框输入的需求一并传给AI,让其生成新的JSON格式的数据,例如多生成一行。
-
由于我们的模型是文本模型,对于计算等不能支持,故采用让其生成函数表达式,即如求平均值就生成文本“=(10+10)/2”,或者“=SUM(B9:C9)”表达式即可。
-
最后将生成的JSON数组,传回到前端后,使用setCellValue方法即可。
showJsonData.forEach(item=>{ var column=0 Object.keys(item).forEach(key=>{ luckysheet.setCellValue(raw,column,item[key]) column++ }) raw++ })
-
展示
- JSON格式数据
[ {"A": "省份", "B": "GDP"}, {"A": "山东", "B": 10}, {"A": "河南", "B": 20}, {"A": "陕西", "B": 50}, {"A": "山西", "B": 15}, {"A": "福建", "B": 100}, {"A": "河北", "B": 60}, {"A": "宁夏", "B": 200}, {"A": "甘肃", "B": 10}, {"A": "平均值", "B":"=(10+20+50+15+100+60+200+10)/8"} ]
- 前端展示,效果还行…
- 问题是可能AI还不够聪明,对于一些复杂的功能不能够实现,还有待学习改进哇~
- JSON格式数据