Vue2+Univer实现可编辑Excel

注：本文章仅限于参考借鉴，其中一些代码样例没有用到官方API。

内容简介：本文章可以完成使用Univer表格插件，对数据进行增删改查，以及基本样式修改

Univer官方：Univer

一、 安装插件（大部分插件版本均为 0.1.13）

pnpm add @univerjs/core@0.1.13 @univerjs/design@0.1.13 @univerjs/docs@0.1.13 @univerjs/engine-formula@0.1.13 @univerjs/engine-render@0.1.13 @univerjs/facade@0.1.15 @univerjs/sheets@0.1.13 @univerjs/sheets-formula@0.1.13 @univerjs/sheets-ui@0.1.13 

二、 创建一个容器

<div ref="container" class="univer-container" :data="data" style="height: 100vh"></div>

三、引入插件和样式

import {
  Univer,
  UniverInstanceType,
  LocaleType,
  IPermissionService,
} from "@univerjs/core";
import { defaultTheme } from "@univerjs/design";
import { UniverDocsPlugin } from "@univerjs/docs";
import { UniverDocsUIPlugin } from "@univerjs/docs-ui";
import { UniverFormulaEnginePlugin } from "@univerjs/engine-formula";
import { UniverRenderEnginePlugin } from "@univerjs/engine-render";
import {
  UniverSheetsPlugin,
  RangeProtectionPermissionEditPoint,
  WorkbookEditablePermission,
} from "@univerjs/sheets";
import { UniverSheetsFormulaPlugin } from "@univerjs/sheets-formula";
import { UniverSheetsUIPlugin } from "@univerjs/sheets-ui";
import { UniverUIPlugin } from "@univerjs/ui";
import { FUniver } from "@univerjs/facade";
import { BooleanNumber, SheetTypes } from "@univerjs/core";

import { zhCN, enUS } from "univer:locales";
// TODO:
import "@univerjs/sheets-ui/lib/index.css";

四、 初始化表格

  mounted() {
    this.init(this.data);
    this.dataUrl = this.$route.query.dataUrl;
    this.uploadUrl = this.$route.query.uploadUrl;
    this.getData();
  },
  methods: {
    /**
     * Initialize univer instance and workbook instance
     * @param data {IWorkbookData} document see https://univer.work/api/core/interfaces/IWorkbookData.html
     */

    init(data = {}) {
      const univer = new Univer({
        theme: defaultTheme,
        locale: LocaleType.zhCN,
        locales: {
          [LocaleType.ZH_CN]: zhCN,
          [LocaleType.EN_US]: enUS,
        },
      });
      this.univer = univer;

      // core plugins
      univer.registerPlugin(UniverRenderEnginePlugin);
      univer.registerPlugin(UniverFormulaEnginePlugin);
      univer.registerPlugin(UniverUIPlugin, {
        container: this.$refs.container,
      });

      // doc plugins
      univer.registerPlugin(UniverDocsPlugin, {
        hasScroll: false,
      });
      univer.registerPlugin(UniverDocsUIPlugin);

      // sheet plugins
      univer.registerPlugin(UniverSheetsPlugin);
      univer.registerPlugin(UniverSheetsUIPlugin);
      univer.registerPlugin(UniverSheetsFormulaPlugin);

      // create workbook instance
      this.workbook = univer.createUnit(UniverInstanceType.UNIVER_SHEET, data);
    },
}

五、 数据格式及加载

1、数据加载：在容器内使用 :data=" 数据  "

2、数据格式：

data: {
        id: "workbook-01",
        locale: LocaleType.ZH_CN,
        name: "universheet",
        sheetOrder: ["sheet-01"],
        appVersion: "3.0.0-alpha",
        sheets: {
          "sheet-01": {
            type: SheetTypes.GRID,
            id: "sheet-01",
            cellData: {
              //数据
              0: {
                0: {
                  v: "Hellow Univer",
                },
              },
            },
            name: "sheet1", // 表名
            tabColor: "red",
            hidden: BooleanNumber.FALSE,
            rowCount: 6222, //表格行数
            columnCount: 10, //表格列数
            zoomRatio: 1,
            scrollTop: 200,
            scrollLeft: 100,
            defaultColumnWidth: 200, //单元格的宽度
            defaultRowHeight: 45, //单元格高度
            status: 1,
            showGridlines: 1,
            hideRow: [],
            hideColumn: [],
            rowHeader: {
              width: 46,
              hidden: BooleanNumber.FALSE,
            },
            columnHeader: {
              height: 20,
              hidden: BooleanNumber.FALSE,
            },
            selections: ["A2"],
            rightToLeft: BooleanNumber.FALSE,
            pluginMeta: {},
            mergeData: [],
          },
        },
      },

3、效果展示：

注：以下代码为部分代码，仅供参考，建议以官方API为准

六、 观察数据变化，并重新创建Univer实例

 watch: {
    // watch data change, and re-create univer instance
    data: {
      handler(val) {
        this.destroyUniver();
        this.init(val);
      },
      immediate: true,
    },
  },

destroyUniver() {
      this.univer?.dispose();
      this.univer = null;
      this.workbook = null;
    },

七、 获取cellData

const univerAPI = FUniver.newAPI(this.univer);
const activeSheet = univerAPI.getActiveWorkbook().getActiveSheet();
const savedData = univerAPI.getActiveWorkbook().getSnapshot();
let excel = savedData.sheets["sheet-01"].cellData;

八、 设置cellData

const univerAPI = FUniver.newAPI(this.univer);
const activeSheet = univerAPI.getActiveWorkbook().getActiveSheet();
const range1 = activeSheet.getRange();

range1.setValues(数据);

九、 设置表格样式

const univerAPI = FUniver.newAPI(this.univer);
const activeSheet = univerAPI.getActiveWorkbook().getActiveSheet();

// (startRow，startColumn，endRow，endColumn)
const headTitle = activeSheet.getRange(0, 0, 1, this.headerLength);
const sheetStyle = activeSheet.getRange(0, 0, rowCount, columnCount);
// 垂直居中（top/middle/bottom）
sheetStyle.setVerticalAlignment("middle");
// 左右居中（left/center/right）
sheetStyle.setHorizontalAlignment("center");
// 字体粗细（normal/bold）
headTitle.setFontWeight("bold");
// 字体大小（数字即可）
headTitle.setFontSize("13");

十、 合并单元格

this.data.sheets["sheet-01"].mergeData = [
        { startRow: 1, endRow: 2, startColumn: 0, endColumn: 0 },
        { startRow: 3, endRow: 9, startColumn: 0, endColumn: 0 },
        { startRow: 1, endRow: 2, startColumn: 1, endColumn: 1 },
        { startRow: 3, endRow: 5, startColumn: 1, endColumn: 1 },
        { startRow: 6, endRow: 7, startColumn: 1, endColumn: 1 },
        { startRow: 8, endRow: 9, startColumn: 1, endColumn: 1 },
      ];

十一、 冻结行列

this.data.sheets["sheet-01"].freeze= {
        startRow: -1,
        startColumn: 1,
        ySplit: 0,
        xSplit: 1,
    }

cellData : custom自定义字段（具体内容为作者使用，仅供参考！！！）

for (var out = 0; out < this.list.length; out++) {
        this.newObj[out + 1] = {};
        var values = Object.values(this.list[out]);
        var keys = Object.keys(this.list[out]);
        for (var inner = 0, i = 0; inner < values.length; inner++) {
          // console.log(keys[inner] + inner);
          if (
            (keys[inner] == "nodeName" && keys[inner + 1] == "nId") ||
            (keys[inner] == "psetName" && keys[inner + 1] == "psetId")
          ) {
            // console.log(keys[inner]);
            if (keys[inner + 1] == "nId") {
              this.newObj[out + 1][i] = {
                v: values[inner],
                custom: {
                  nId: values[inner + 1],
                },
              };
            } else {
              this.newObj[out + 1][i] = {
                v: values[inner],
                custom: {
                  psetId: values[inner + 1],
                },
              };
            }

            i++;
          } else {
            if (keys[inner] !== "nId" && keys[inner] !== "psetId")
              this.newObj[out + 1][inner - i] = { v: values[inner] };
            // console.log(keys[inner]);
          }
        }
      }

注：推荐使用Element UI 添加效果组件