vue实现在线Excel表格功能

目录

1.安装x-data-spreadsheet xlsx

2.引入 

3.使用

1.安装x-data-spreadsheet xlsx

npm i x-data-spreadsheet xlsx复制

2.引入 

import zhCN from "x-data-spreadsheet/src/locale/zh-cn";
import Spreadsheet from "x-data-spreadsheet";
import * as XLSX from "xlsx";
Spreadsheet.locale("zh-cn", zhCN);复制

3.使用

<template>
  <div class="CptOutbox">
    <div class="ToolOutbox">
      <input type="file" @change="chageFile" ref="inputFile" />
      <button @click="exportExcel">导出xlsx</button>
      <button @click="SaveExcel">批量保存</button>
    </div>
    <div class="ExcelOutbox">
      <!--web spreadsheet组件-->
      <div
        class="sheetContainerbox"
        ref="sheetContainer"
        id="x-spreadsheet-demo"
      ></div>
    </div>
  </div>
</template>
  
  <script>
//引入依赖包
import zhCN from "x-data-spreadsheet/src/locale/zh-cn";
import Spreadsheet from "x-data-spreadsheet";
import * as XLSX from "xlsx";
Spreadsheet.locale("zh-cn", zhCN);
export default {
  name: "XspreadsheetDemo",
  data() {
    return {
      xs: null,
    };
  },
  mounted() {
    this.init();
  },
  methods: {
    init() {
      this.xs = new Spreadsheet("#x-spreadsheet-demo", {
        mode: "edit",
        showToolbar: true,
        showGrid: true,
        showContextmenu: true,
        showBottomBar: true,
        view: {
          height: () => this.$refs.sheetContainer.offsetHeight,
          width: () => this.$refs.sheetContainer.offsetWidth,
        },
        style: {
          bgcolor: "#ffffff",
          align: "left",
          valign: "middle",
          textwrap: false,
          strike: false,
          underline: false,
          color: "#0a0a0a",
          font: {
            name: "Helvetica",
            size: 10,
            bold: false,
            italic: false,
          },
        },
        row: {
          len: 10000,
          height: 25,
        },
        col: {
          len: 26,
          width: 100,
          indexWidth: 60,
          minWidth: 60,
        },
      });
      let excelData = localStorage.getItem("WebExcelData");
      if (excelData) {
        this.xs.loadData(excelData); // load data
      }
 
      this.xs.change((data) => {
        localStorage.setItem("WebExcelData", data);
      });
 
      // data validation
      this.xs.validate();
      import('./data.json')
      .then((res) => {
        console.log(`res`, res);
        localStorage.setItem("WebExcelData", res.Data);
        this.xs.loadData(res.Data)
      })
    },
    exportExcel() {
      var new_wb = this.xtos(this.xs.getData());
      console.log(`new_wb`, new_wb);
      XLSX.writeFile(new_wb, "SheetJS.xlsx");
    },
    xtos(sdata) {
      console.log(sdata);
      var out = XLSX.utils.book_new();
      sdata.forEach((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((k) => {
            var idx = +k;
            if (isNaN(idx)) return;
            aoa[ri][idx] = row.cells[k].text;
          });
        }
        var ws = XLSX.utils.aoa_to_sheet(aoa);
 
        /** 读取在线中的合并单元格，并写入导出的数据中
                   * merges: Array(19)
                      0: "A16:P16"
                      1: "A17:P17"
                      2: "O2:P2"
                      3: "F2:G2"
                   */
        ws["!merges"] = [];
        xws.merges.forEach((merge) => {
          ws["!merges"].push(XLSX.utils.decode_range(merge));
        });
 
        XLSX.utils.book_append_sheet(out, ws, xws.name);
      });
      return out;
    },
    chageFile() {
      this.importExcel(this.$refs.inputFile.files[0]);
    },
    importExcel(file) {
      let reader = new FileReader();
      reader.onload = (e) => {
        let data = e.target.result;
        let fixedData = this.fixData(data);
        let workbook = XLSX.read(btoa(fixedData), { type: "base64" });
        console.log(workbook);
        console.log("this.stox(workbook)",this.stox(workbook));
        this.xs.loadData(this.stox(workbook));
      };
      reader.readAsArrayBuffer(file);
      // return workbook
    },
    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;
    },
    stox(wb) {
      console.log(`wb`, wb);
      var out = [];
      wb.SheetNames.forEach((name) => {
        var o = { name: name, rows: {}, merges: [] };
        var ws = wb.Sheets[name];
        var aoa = XLSX.utils.sheet_to_json(ws, { raw: false, header: 1 });
        aoa.forEach((r, i) => {
          var cells = {};
          r.forEach((c, j) => {
            cells[j] = { text: c };
          });
          o.rows[i] = { cells: cells };
        });
 
        ///
        console.log(`ws`, ws);
        // 设置合并单元格
        if (ws["!merges"]) {
          ws["!merges"].forEach((merge) => {
            /** merge = {
             *  s: {c: 0, r: 15}
             *  e: {c: 15, r: 15}
             * }
             */
            // 修改 cell 中 merge [合并行数,合并列数]
            let cell = o.rows[merge.s.r].cells[merge.s.c];
 
            //无内容单元格处理
            if (!cell) {
              cell = { text: "" };
            }
            cell.merge = [merge.e.r - merge.s.r, merge.e.c - merge.s.c];
            o.rows[merge.s.r].cells[merge.s.c] = cell;
 
            // 修改 merges
            o.merges.push(XLSX.utils.encode_range(merge));
          });
        }
        
        out.push(o);
      });
      return out;
    },
    ///
    SaveExcel() {
 
    },
   
  },
};
</script>
  <style lang="scss" scoped>
.CptOutbox {
  width: 100%;
  height: 100%;
  .ToolOutbox {
    display: flex;
    flex-flow: row nowrap;
    align-items: center;
    width: 100%;
    height: 50px;
  }
  .ExcelOutbox {
    display: flex;
    flex-flow: row nowrap;
    align-items: center;
    width: 100%;
    height: calc(100% - 50px);
    .sheetContainerbox {
      width: 100%;
      height: 100%;
    }
  }
}
</style>复制

原Excel样式如下

导入到 x-data-spreadsheet 如下图

x-spreadsheet-doc文档

x-spreadsheet在线示例