spreadJS初体验

SpreadJS是一款基于JavaScript的电子表格控件，通过以表格形式显示数据、高速的计算引擎以及数百种统计和财务函数及公式，提供类似于Excel的电子表格体验。 它易于实现，可扩展并且灵活。尤其可以帮助增强您的应用程序，并将内容从一组简单的数据转换为更加有用、易懂的类似于Excel的仪表板。 快速开始

App.vue

<template>
  <div>
    <input type="file" @change="importExcel($event)" />
    <button @click="exportExcel">导出EXCEL</button>
    <gc-spread-sheets
      :hostClass="hostClass"
      @workbookInitialized="initWorkbook"
    >
      <gc-worksheet />
      <gc-worksheet />
    </gc-spread-sheets>
  </div>
</template>

<script>
import '@grapecity/spread-sheets/styles/gc.spread.sheets.excel2016colorful.css'
import '@grapecity/spread-sheets-resources-zh'
GC.Spread.Common.CultureManager.culture('zh-cn')
import * as GC from '@grapecity/spread-sheets'
import * as ExcelIO from '@grapecity/spread-excelio'
import '@grapecity/spread-sheets-vue'
import '@grapecity/spread-sheets-charts'
import FileSaver from 'file-saver'
const GCsheets = GC.Spread.Sheets
export default {
  data() {
    return {
      hostClass: 'spread-host',
      spread: null,
      table: null,
      // 源数据
      data: {
        rows: [
          {
            compName: '广东联塑科技实业有限公司',
            compNo: '1010',
            datFees: [
              {
                compName: '广东联塑科技实业有限公司',
                compNo: '1010',
                createTime: null,
                creator: '',
                electricityFees: '2.00',
                imonth: '04',
                iyear: '2022',
                pageCode: '',
                payTotal: '6.00',
                taxFees: '1.00',
                taxRefund: '4.00',
                updateTime: null,
                updator: '',
                waterFees: '3.00',
                xtype: '1',
              },
            ],
            plateTypeName: '塑料管道',
          },
          {
            compName: '福建联塑新材料科技有限公司',
            compNo: '1610',
            datFees: [
              {
                compName: '福建联塑新材料科技有限公司',
                compNo: '1610',
                createTime: null,
                creator: '',
                electricityFees: '2.00',
                imonth: '04',
                iyear: '2022',
                pageCode: '',
                payTotal: '6.00',
                taxFees: '1.00',
                taxRefund: '4.00',
                updateTime: null,
                updator: '',
                waterFees: '3.00',
                xtype: '1',
              },
            ],
            plateTypeName: '塑料管道',
          },
          {
            compName: '佛山市联塑万嘉新卫材有限公司',
            compNo: '1030',
            datFees: [
              {
                compName: '佛山市联塑万嘉新卫材有限公司',
                compNo: '1030',
                createTime: null,
                creator: '',
                electricityFees: '123.00',
                imonth: '04',
                iyear: '2022',
                pageCode: '',
                payTotal: '369.00',
                taxFees: '123.00',
                taxRefund: '0.00',
                updateTime: null,
                updator: '',
                waterFees: '123.00',
                xtype: '5',
              },
            ],
            plateTypeName: '管理及其他',
          },
        ],
      },
    }
  },
  mounted() {},
  methods: {
    // 计算总和
    calcCount(typeArr, type) {
      let obj = typeArr.reduce(
        (res, val) => {
          for (let k in res) {
            res[k] += parseFloat(val[k])
          }
          return res
        },
        {
          taxFees: 0,
          waterFees: 0,
          electricityFees: 0,
          payTotal: 0,
          taxRefund: 0,
        }
      )
      for (let k in obj) {
        obj[k] = obj[k].toFixed(2)
      }
      obj.compName = type ? `${type}合计` : `总计`
      return obj
    },
    // 更改对象key值
    changeKey(obj, keymapping) {
      let oldKeysArr = Object.keys(obj)
      let arr = Object.entries(obj)
      keymapping.forEach((item) => {
        if (oldKeysArr.indexOf(item.key) !== -1) {
          arr[oldKeysArr.indexOf(item.key)][0] = item.newKey
        }
      })
      return Object.fromEntries(arr)
    },
    // 格式化表格数据
    formatData(data) {
      let output = []
      let map = {}
      for (let rec of data) {
        map[rec.plateTypeName] = map[rec.plateTypeName] || []
        map[rec.plateTypeName].push(...rec.datFees)
      }
      let totalCount = []
      for (let k in map) {
        totalCount.push(this.calcCount(map[k], k))
        output.push(...map[k], this.calcCount(map[k], k))
      }
      output.push(this.calcCount(totalCount))
      return output
    },
    // 初始化工作簿
    initWorkbook(spread) {
      let newData = this.formatData(this.data.rows)
      let keyMapping = [
        { key: 'compName', newKey: '公司名称' },
        { key: 'compNo', newKey: '公司编码' },
        { key: 'electricityFees', newKey: '电费' },
        { key: 'imonth', newKey: '月份' },
        { key: 'iyear', newKey: '年份' },
        { key: 'payTotal', newKey: '总开销' },
        { key: 'taxFees', newKey: '税费' },
        { key: 'taxRefund', newKey: '退税' },
        { key: 'waterFees', newKey: '水费' },
      ]
      newData = newData.map(item=>{
        return this.changeKey(item,keyMapping)
      })
      this.spread = spread
      spread.suspendPaint() //暂停spread重绘
      let sheet = spread.getActiveSheet()
      let table = sheet.tables.addFromDataSource('Table1', 2, 1, newData)
      table.style(GCsheets.Tables.TableThemes['medium4'])
      table.highlightFirstColumn(true) // 第一列内容高亮
      sheet.setColumnWidth(0, 20) 
      sheet.setColumnWidth(1, 240) // 设置sheet第二列宽度
      spread.resumePaint() //恢复spread重绘
    },

    importExcel(e) {
      let file = e.target.files[0]
      let self = this
      let excelIO = new ExcelIO.IO()
      excelIO.open(file, (spreadJSON) => {
        self.spread?.fromJSON(spreadJSON)
      })
    },
    exportExcel() {
      if (this.spread) {
        let excelIO = new ExcelIO.IO()
        // spread.toJSON()设置 includeBindingSource 选项为 true,将表单或者表格中的数据源序列化到最终的 JSON 对象
        excelIO.save(this.spread.toJSON( { includeBindingSource: true }), (blob) => {
          FileSaver.saveAs(blob, 'export.xlsx')
        })
      }
    },
  },
}
</script>

<style>
.spread-host {
  width: 100%;
  height: 600px;
}
</style>

package.json

{
  "name": "sjs-vue-app",
  "version": "0.1.0",
  "private": true,
  "scripts": {
    "serve": "vue-cli-service serve",
    "build": "vue-cli-service build",
    "lint": "vue-cli-service lint"
  },
  "dependencies": {
    "@grapecity/spread-excelio": "^14.0.1",
    "@grapecity/spread-sheets": "^15.0.6",
    "@grapecity/spread-sheets-charts": "^15.0.6",
    "@grapecity/spread-sheets-resources-zh": "^15.0.6",
    "@grapecity/spread-sheets-vue": "^15.0.6",
    "@types/file-saver": "^2.0.1",
    "core-js": "^3.6.5",
    "file-saver": "^2.0.2",
    "vue": "^2.6.11"
  },
  "devDependencies": {
    "@vue/cli-plugin-babel": "~4.5.13",
    "@vue/cli-plugin-eslint": "~4.5.13",
    "@vue/cli-service": "~4.5.13",
    "babel-eslint": "^10.1.0",
    "eslint": "^6.7.2",
    "eslint-plugin-vue": "^6.2.2",
    "vue-template-compiler": "^2.6.11"
  },
  "eslintConfig": {
    "root": true,
    "env": {
      "node": true
    },
    "extends": [
      "plugin:vue/essential",
      "eslint:recommended"
    ],
    "parserOptions": {
      "parser": "babel-eslint"
    },
    "rules": {
      "generator-star-spacing": "off",
      "no-tabs": "off",
      "no-unused-vars": "off",
      "no-console": "off",
      "no-irregular-whitespace": "off",
      "no-debugger": "off"
    }
  },
  "browserslist": [
    "> 1%",
    "last 2 versions",
    "not dead"
  ]
}

效果图