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

源码地址: klren0312/realtimeExcel: ws + jexcel, create simple realtime excel (github.com)

一. 原理

使用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. 使用库

• ws websocket库
• ioredis redis操作库

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()
    }))
  }
}

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

结果

iexcel.zzes1314.cn

参考资料

• The javascript spreadsheet (bossanova.uk)
• WebSocket - Web APIs | MDN (mozilla.org)