在线表格编辑 工具

工具：x-data-spreadsheet

git：https://github.com/myliang/x-spreadsheet

中文文档：https://hondrytravis.github.io/x-spreadsheet-doc/

参考：https://my.oschina.net/daoos/blog/4

/**
 * @desc 在线表格
 * @name ExcelOnline
 * @author
 * @date 2020/11/05
 */
import React, { Component } from 'react'
import Spreadsheet from 'x-data-spreadsheet' //在线编辑插件
import XLSX from 'xlsx' //生成表格插件

import 'x-data-spreadsheet/dist/locale/zh-cn' //在线编辑汉化包

class ExcelOnline extends Component {
  componentDidMount() {
    this.init()
  }
  init = () => {
    Spreadsheet.locale('zh-cn')
    const s = new Spreadsheet(document.getElementById('x-spreadsheet-demo'))
      .loadData({}) // 加载数据
      .change((data) => {
        // save data to db //改变并保存到数据库
      })
    // data validation
    s.validate()
    this.setState({ spreed: s })
  }
  /**
   * @name getExcel
   * @description 导入数据
   */
  getExcel = (fileSelected) => {
    const { spreed } = this.state
    let file = fileSelected.target.files[0]
    let reader = new FileReader()
    reader.onload = (e) => {
      let data = e.target.result,
        fixedData = this.fixData(data),
        workbook = XLSX.read(btoa(fixedData), { type: 'base64' })
      spreed.loadData(this.stox(workbook))
    }
    reader.readAsArrayBuffer(file)
  }
  /**
   *@name fixData
   @description 导入数据处理，格式转化
   */
  fixData = (data) => {
    var o = '',
      l = 0,
      w = 10240
    for (; l < data.byteLength / w; ++l)
      o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w, l * w + w)))
    o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w)))
    return o
  }
  /**
   *@name stox
   @description 导入数据处理
   */
  stox = (wb) => {
    var out = []
    wb.SheetNames.forEach(function (name) {
      var o = { name: name, rows: {} }
      var ws = wb.Sheets[name]
      var aoa = XLSX.utils.sheet_to_json(ws, { raw: false, header: 1 })
      aoa.forEach(function (r, i) {
        var cells = {}
        r.forEach(function (c, j) {
          cells[j] = { text: c }
        })
        o.rows[i] = { cells: cells }
      })
      out.push(o)
    })
    return out
  }

  /**
   *@name xtos
   @description 导出文件处理
   */
  xtos = (sdata) => {
    var out = XLSX.utils.book_new()
    sdata.forEach(function (xws) {
      var aoa = [[]]
      var rowobj = xws.rows
      for (var ri = 0; ri < rowobj.len; ++ri) {
        var row = rowobj[ri]
        if (!row) continue
        aoa[ri] = []
        Object.keys(row.cells).forEach(function (k) {
          var idx = +k
          if (isNaN(idx)) return
          aoa[ri][idx] = row.cells[k].text
        })
      }
      var ws = XLSX.utils.aoa_to_sheet(aoa)
      XLSX.utils.book_append_sheet(out, ws, xws.name)
    })
    return out
  }

  /**
   * @name exportJson
   * @description 导出JSON数据
   */
  exportJson = () => {
    const { spreed } = this.state
    console.log('导出JSON数据======', JSON.stringify(spreed.getData()))
  }

  /**
   * @name exportExcel
   * @description 导出Excel表格文件
   */
  exportExcel = () => {
    const { spreed } = this.state
    var new_wb = this.xtos(spreed.getData())
    /* generate download */
    XLSX.writeFile(new_wb, 'SheetJS.xlsx')
  }

  render() {
    return (
      <div>
        Home
        <input type="file" onChange={this.getExcel} />
        <div onClick={this.exportJson}>导出JSON</div>
        <div onClick={this.exportExcel}>导出xlsx</div>
        <div id="x-spreadsheet-demo"></div>
        <div id="xss-demo"></div>
      </div>
    )
  }
}

export default ExcelOnline

330213