美文网首页web前端开发程序员
十几分钟写个实时协作表格

十几分钟写个实时协作表格

作者: ZZES_ZCDC | 来源:发表于2021-02-13 11:33 被阅读0次

    最近在使用jexcel, 所以顺便尝试下写个简单的协作表格
    源码地址: klren0312/realtimeExcel: ws + jexcel, create simple realtime excel (github.com)

    aaa.gif

    一. 原理

    使用jexcel的api监听表格的修改, 将修改的相关数据通过websocket传给后台, 后台再将数据广播给其他websocket客户端, 客户端接收到修改后修改表格

    二. websocket数据格式

    1. 心跳

    {
      type: 'checkalive'
    }
    

    2. 获取当前全部数据

    • 客户端发送请求
    {
      type: 'new'
    }
    
    • 服务端发送全部数据
    {
      type: 'history',
      has: true,
      data: []
    }
    

    3. 表格修改

    {
      type: 'excelChange',
      data: {
        x: '', // x坐标
        y: '', // y坐标
        value: '' // 数据
      },
      total: [] // 全部数据, 用于后台缓存全部数据
    }
    

    5. excel导入

    {
      type: 'importExcel',
      data: []
    }
    

    三. websocket后端实现

    1. 使用库

    2. 源码

    ws开启端口为23333, redis用来缓存数据, 也可以改成全局变量存, 这样就不用还要额外安装redis, 毕竟求

    const WebSocket = require('ws')
    const wss = new WebSocket.Server({ port: 23333 })
    const Redis = require("ioredis")
    const redis = new Redis()
    
    wss.on('connection', (ws, req) => {
      ws.on('message', msg => {
        try {
          const data = JSON.parse(msg)
          switch (data.type) {
            case 'checkalive': // 心跳
              ws.send(JSON.stringify({type: 'checkalive'}))
              break
            case 'new': // 新打开页面加载历史数据
              redis.get('cacheData').then(res => {
                if (res) {
                  const cacheData = JSON.parse(res)
                  const newObj = {
                    type: 'history',
                    has: cacheData.length === 0 ? false : true,
                    data: cacheData
                  }
                  ws.send(JSON.stringify(newObj))
                }
              })
              
              break
            case 'excelChange': // 表格修改
              redis.set('cacheData', JSON.stringify(data.total))
              wss.clients.forEach(function each(client) {
                // 通知除了当前客户端的其他客户端
                if (client !== ws && client.readyState === WebSocket.OPEN) {
                  client.send(msg)
                }
              })
              break
            case 'excelInsert': // 表格插入
              redis.set('cacheData', JSON.stringify(data.total))
              wss.clients.forEach(client => {
                // 通知除了当前客户端的其他客户端
                if (client !== ws && client.readyState === WebSocket.OPEN) {
                  client.send(msg)
                }
              })
              break
            case 'importExcel': // 表格导入
              redis.set('cacheData', JSON.stringify(data.data))
              wss.clients.forEach(client => {
                // 通知除了当前客户端的其他客户端
                if (client !== ws && client.readyState === WebSocket.OPEN) {
                  client.send(msg)
                }
              })
              break
          }
        } catch (error) {
        }
      })
    })
    

    四. 前端页面实现

    1. 使用库

    • jexcel
    • jsuite jexcel依赖
    • sheetjs

    2. websocket客户端代码封装

    封装为类, 方便调用, 使用 new WSClient()创建

        // ws连接对象
        class WSClient {
          constructor (url) {
            this.url = url
            this.ws = ''
            this.timeoutId = null
          }
    
          init (excelInstance) {
            this.ws = new WebSocket(this.url)
            this.ws.onopen = () => {
                if (this.ws.readyState === 1) {
                  // 心跳
                  this.ws.send(JSON.stringify({type:'checkalive'}))
                  this.ws.keepAliveTimer = setInterval(() => {
                    if (this.ws.bufferedAmount === 0 && this.ws.readyState === 1) {
                      this.ws.send(JSON.stringify({type:'checkalive'}))
                    }
                  }, 60000)
                  // 重新进入页面, 获取历史数据
                  this.ws.send(JSON.stringify({type: 'new'}))
                }
              }
              this.ws.onmessage = res => {
                try {
                  const msg = JSON.parse(res.data)
                  const data = msg.data
                  switch (msg.type) {
                    case 'excelChange':
                      const oldData = excelInstance.getValueFromCoords(data.x, data.y)
                      if (data.value !== oldData) {
                        comeFromData = `${data.x}${data.y}${data.value}`
                        excelInstance.setValueFromCoords(data.x, data.y, data.value)
                      }
                      break
                    case 'history':
                      if (msg.has) {
                        excelInstance.setData(data)
                      }
                      break
                    case 'excelInsert':
                      comeFromInsert = `${data.mixed}${data.rowNumber}${data.insertBefore}`
                      excelInstance.insertRow({
                        mixed: data.mixed,
                        rowNumber: data.rowNumber,
                        insertBefore: data.insertBefore
                      })
                      break
                    case 'importExcel':
                      excelInstance.setData(data)
                      break
                  }
                } catch (error) {
                }
              }
              this.ws.onerror = () => {}
              this.ws.onclose = e => {
                if (e.code === 23333) return
                clearInterval(this.ws.keepAliveTimer)
                // 判断是否断网
                if (!window.navigator.onLine) {
                  this.ws.close(23333)
                } else {
                  // 一分钟重连一次
                  this.timeoutId = setTimeout(() => {
                    this.ws.close(23333)
                    this.ws = new WebSocket(this.url)
                    clearTimeout(this.timeoutId)
                  }, 60000)
                }
              }
          }
        }
    

    3. jexcel创建

    // 创建excel实例
    const mySpreadsheet = jexcel(document.getElementById('spreadsheet'), {
      data: defaultData,
      columns: [{
          type: 'text',
          width: 200,
        },
        {
          type: 'text',
          width: 200,
        },
        {
          type: 'text',
          width: 200,
        },
        {
          type: 'text',
          width: 200,
        },
        {
          type: 'text',
          width: 200,
        },
        {
          type: 'text',
          width: 200,
        }
      ],
      tableOverflow: true, // 允许滚动
      tableHeight: window.innerHeight, // 最大高度
      allowDeleteRow: false,
      allowDeleteColumn: false,
      // allowManualInsertRow: false,
      allowManualInsertColumn: false,
      oninsertrow: excelInsertRow,
      onchange: excelChange
    })
    
    /**
     * 表格修改触发
     */
    function excelChange (el, currentel, x, y, nv, ov) {
      console.log(comeFromData, `${x}${y}${nv}`)
      if (comeFromData === `${x}${y}${nv}`) {
        comeFromData = ''
        return
      }
      if (nv !== ov) {
        const obj = {
          x: x,
          y: y,
          value: nv
        }
        if (client.ws.readyState === 1) {
          client.ws.send(JSON.stringify({
            type: 'excelChange',
            data: obj,
            total: mySpreadsheet.getData()
          }))
        }
      }
    }
    /**
     * 表格插入新行触发
     */
    function excelInsertRow (el, rowNumber, numOfRows, rowRecords, insertBefore) {
      if (comeFromInsert === `${numOfRows}${rowNumber}${insertBefore}`) {
        comeFromInsert = ''
        return
      }
      const obj = {
        rowNumber: rowNumber,
        mixed: numOfRows,
        insertBefore: insertBefore
      }
      if (client.ws.readyState === 1) {
        client.ws.send(JSON.stringify({
          type: 'excelInsert',
          data: obj,
          total: mySpreadsheet.getData()
        }))
      }
    }
    

    创建ws连接, 并把jexcel对象传入

    const client = new WSClient('ws://122.51.46.108:23333')
    client.init(mySpreadsheet)
    

    4. excel导入逻辑

    /**
     * 导入excel文件
     */
    function fileUpload(e) {
      if (!e)  return
      const files = e.target.files, f = files[0]
      const reader = new FileReader()
      reader.onload = function(e) {
        const data = new Uint8Array(e.target.result)
        const workbook = XLSX.read(data, {type: 'array'})
        const workSheet = workbook.Sheets[workbook.SheetNames[0]]
        const res = XLSX.utils.sheet_to_json(workSheet)
        let arr = []
        res.forEach(d => {
          const filterArr = Object.keys(d).map(v => {
            if (v.indexOf('__EMPTY') !== -1) {
              return ''
            } else {
              return v
            }
          })
          arr.push(filterArr)
        })
        if (mySpreadsheet) {
          mySpreadsheet.setData(arr)
          if (client.ws.readyState === 1) {
            client.ws.send(JSON.stringify({
              type: 'importExcel',
              data: mySpreadsheet.getData()
            }))
          }
        }
        e = null
      }
      reader.readAsArrayBuffer(f)
    }
    

    结果

    http://iexcel.zzes1314.cn

    参考资料

    相关文章

      网友评论

        本文标题:十几分钟写个实时协作表格

        本文链接:https://www.haomeiwen.com/subject/lzyrtltx.html