excel表格在线编辑（开源版）

文章目录

前言

本文记录好用的开源在线表格
具体如图显示

另外记录下更名后的univer~，如下图（有兴趣可自行详细了解）
univer

• 在线思维导图

一、Luckysheet

参考gitee
一款纯前端类似excel的在线表格，功能强大、配置简单、完全开源。

vue3+vite 例子

vue3+vite

• 引入样式及脚本

<linkrel="stylesheet"href="https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/css/pluginsCss.css"/><linkrel="stylesheet"href="https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/plugins.css"/><linkrel="stylesheet"href="https://cdn.jsdelivr.net/npm/luckysheet/dist/css/luckysheet.css"/><linkrel="stylesheet"href="https://cdn.jsdelivr.net/npm/luckysheet/dist/assets/iconfont/iconfont.css"/><scriptsrc="https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/js/plugin.js"></script><scriptsrc="https://cdn.jsdelivr.net/npm/luckysheet/dist/luckysheet.umd.js"></script>

• 安装所需依赖（exceljs、file-saver和luckyexcel）

npm i exceljs file-saver luckyexcel -S

• 新建 components/excel.vue

<template><divstyle="position: absolute;top: 0"><inputid="uploadBtn"type="file"@change="loadExcel"/><span>Or Load remote xlsx file:</span><selectv-model="selected"@change="selectExcel"><optiondisabledvalue="">Choose</option><optionv-for="option in options":key="option.text":value="option.value">
        {{ option.text }}
      </option></select><ahref="javascript:void(0)"@click="downloadExcel">Download source xlsx file</a></div><divid="luckysheet"></div><divv-show="isMaskShow"id="tip">Downloading</div></template><scriptsetup>import{ ref, onMounted }from'vue'import{ exportExcel }from'./export'import LuckyExcel from'luckyexcel'const isMaskShow =ref(false)const selected =ref('')const jsonData =ref({})const options =ref([{text:'Money Manager.xlsx',value:'https://minio.cnbabylon.com/public/luckysheet/money-manager-2.xlsx'},{text:'Activity costs tracker.xlsx',value:'https://minio.cnbabylon.com/public/luckysheet/Activity%20costs%20tracker.xlsx'},{text:'House cleaning checklist.xlsx',value:'https://minio.cnbabylon.com/public/luckysheet/House%20cleaning%20checklist.xlsx'},{text:'Student assignment planner.xlsx',value:'https://minio.cnbabylon.com/public/luckysheet/Student%20assignment%20planner.xlsx'},{text:'Credit card tracker.xlsx',value:'https://minio.cnbabylon.com/public/luckysheet/Credit%20card%20tracker.xlsx'},{text:'Blue timesheet.xlsx',value:'https://minio.cnbabylon.com/public/luckysheet/Blue%20timesheet.xlsx'},{text:'Student calendar (Mon).xlsx',value:'https://minio.cnbabylon.com/public/luckysheet/Student%20calendar%20%28Mon%29.xlsx'},{text:'Blue mileage and expense report.xlsx',value:'https://minio.cnbabylon.com/public/luckysheet/Blue%20mileage%20and%20expense%20report.xlsx'}])constloadExcel=(evt)=>{const files = evt.target.files
  if(files ==null|| files.length ==0){alert('No files wait for import')return}let name = files[0].name
  let suffixArr = name.split('.'),
    suffix = suffixArr[suffixArr.length -1]if(suffix !='xlsx'){alert('Currently only supports the import of xlsx files')return}
  LuckyExcel.transformExcelToLucky(files[0],function(exportJson, luckysheetfile){if(exportJson.sheets ==null|| exportJson.sheets.length ==0){alert('Failed to read the content of the excel file, currently does not support xls files!')return}
    console.log('exportJson', exportJson)
    jsonData.value = exportJson

    window.luckysheet.destroy()

    window.luckysheet.create({container:'luckysheet',//luckysheet is the container idshowinfobar:false,data: exportJson.sheets,title: exportJson.info.name,userInfo: exportJson.info.name.creator
    })})}constselectExcel=(evt)=>{const value = selected.value
  const name = evt.target.options[evt.target.selectedIndex].innerText

  if(value ==''){return}
  isMaskShow.value =true

  LuckyExcel.transformExcelToLuckyByUrl(value, name,(exportJson, luckysheetfile)=>{if(exportJson.sheets ==null|| exportJson.sheets.length ==0){alert('Failed to read the content of the excel file, currently does not support xls files!')return}
    console.log('exportJson', exportJson)
    jsonData.value = exportJson

    isMaskShow.value =false
    window.luckysheet.destroy()

    window.luckysheet.create({container:'luckysheet',//luckysheet is the container idshowinfobar:false,data: exportJson.sheets,title: exportJson.info.name,userInfo: exportJson.info.name.creator
    })})}constdownloadExcel=()=>{// const value = selected.value;;//// if(value.length==0){//     alert("Please select a demo file");//     return;// }//// var elemIF = document.getElementById("Lucky-download-frame");// if(elemIF==null){//     elemIF = document.createElement("iframe");//     elemIF.style.display = "none";//     elemIF.id = "Lucky-download-frame";//     document.body.appendChild(elemIF);// }// elemIF.src = value;exportExcel(luckysheet.getAllSheets(),'下载')}// !!! create luckysheet after mountedonMounted(()=>{
  luckysheet.create({container:'luckysheet'})})</script><stylescoped>#luckysheet{margin: 0px;padding: 0px;position: absolute;width: 100%;left: 0px;top: 30px;bottom: 0px;}#uploadBtn{font-size: 16px;}#tip{position: absolute;z-index: 1000000;left: 0px;top: 0px;bottom: 0px;right: 0px;background:rgba(255, 255, 255, 0.8);text-align: center;font-size: 40px;align-items: center;justify-content: center;display: flex;}</style>

• 新建 exports.js

// import { createCellPos } from './translateNumToLetter'import Excel from'exceljs'import FileSaver from'file-saver'constexportExcel=function(luckysheet, value){// 参数为luckysheet.getluckysheetfile()获取的对象// 1.创建工作簿，可以为工作簿添加属性const workbook =newExcel.Workbook()// 2.创建表格，第二个参数可以配置创建什么样的工作表if(Object.prototype.toString.call(luckysheet)==='[object Object]'){
    luckysheet =[luckysheet]}
  luckysheet.forEach(function(table){if(table.data.length ===0)returntrue// 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)returntrue})// return// 4.写入 bufferconst buffer = workbook.xlsx.writeBuffer().then(data=>{// console.log('data', data)const blob =newBlob([data],{type:'application/vnd.ms-excel;charset=utf-8'})
    console.log("导出成功！")
    FileSaver.saveAs(blob,`${value}.xlsx`)})return buffer
}varsetMerge=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
    )})}varsetBorder=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})}varsetStyleAndValue=function(cellArr, worksheet){if(!Array.isArray(cellArr))return
  cellArr.forEach(function(row, rowid){
    row.every(function(cell, columnid){if(!cell)returntruelet 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 }}elseif(!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

      returntrue})})}varfillConvert=function(bg){if(!bg){return{}}// const bgc = bg.replace('#', '')let fill ={type:'pattern',pattern:'solid',fgColor:{argb: bg.replace('#','')}}return fill
}varfontConvert=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
}varalignmentConvert=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
}varborderConvert=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
}functionaddborderToCell(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
}functioncreateCellPos(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)+ s

    n = Math.floor(n / len)-1}return s
}export{
  exportExcel
}

运行后预览显示，可导入，下载

如有启发，可点赞收藏哟~