univer表格应用及案例

最新推荐文章于 2024-06-28 15:40:18 发布

Eric⠀

最新推荐文章于 2024-06-28 15:40:18 发布

阅读量521

点赞数 21

文章标签：前端 javascript js vue.js 数据可视化

本文链接：https://blog.csdn.net/m0_73884922/article/details/139971295

版权

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

文章目录

前言
一、Univer介绍
二、使用步骤

一、Univer介绍

Univer 是 Office 系列办公套件的开源替代品，包括电子表格、文档和幻灯片。目前提供公式、数字格式、条件格式、数据验证、图文混排等功能。你可以将 Univer 集成到你的系统当中，并基于 Univer 开发个性化的业务需求。

二、使用步骤

1、使用UMD

<scriptsrc="https://unpkg.com/@univerjs/umd/lib/univer.full.umd.js"></script> <linkrel="stylesheet"href="https://unpkg.com/@univerjs/umd/lib/univer.css">

将上边两个链接复制到浏览器打开，另存为进行下载到本地
引入到publish/index.html中
组件内使用

<template>
  <div ref="sheetContainer" style="height: 100%"></div>
</template>

<script setup>
const {
  UniverCore,
  UniverDesign,
  UniverEngineRender,
  UniverEngineFormula,
  UniverDocs,
  UniverDocsUi,
  UniverUi,
  UniverSheets,
  UniverSheetsUi,
  UniverSheetsNumfmt,
  UniverSheetsFormula,
  UniverFacade,
} = window;

const props = defineProps({
  data: {
    type: Object,
    default: () => ({}),
  },
});

const univerRef = ref(null);
const workbook = ref(null);
const sheetContainer = ref(null);
const univerAPI = ref(null);

const init = (data) => {
  const univer = new UniverCore.Univer({
    theme: UniverDesign.defaultTheme,
  });
  univerRef.value = univer;

  univer.registerPlugin(UniverEngineRender.UniverRenderEnginePlugin);
  univer.registerPlugin(UniverEngineFormula.UniverFormulaEnginePlugin);

  univer.registerPlugin(UniverUi.UniverUIPlugin, {
    container: sheetContainer.value,
  });

  univer.registerPlugin(UniverDocs.UniverDocsPlugin, {
    hasScroll: false,
  });
  univer.registerPlugin(UniverDocsUi.UniverDocsUIPlugin);

  univer.registerPlugin(UniverSheets.UniverSheetsPlugin);
  univer.registerPlugin(UniverSheetsUi.UniverSheetsUIPlugin);
  univer.registerPlugin(UniverSheetsNumfmt.UniverSheetsNumfmtPlugin);
  univer.registerPlugin(UniverSheetsFormula.UniverSheetsFormulaPlugin);

  workbook.value = univer.createUniverSheet(data);
  univerAPI.value = UniverFacade.FUniver.newAPI(univer);
};

onMounted(() => {
  init(props.data);
});

const getData = () => {
  if (!workbook.value) {
    throw new Error('Workbook is not initialized');
  }
  return workbook.value.save();
};

defineExpose({
  getData,
  univerAPI: univerAPI.value,
});
</script>

2、npm安装

npm install @univerjs/core @univerjs/design @univerjs/docs @univerjs/docs-ui @univerjs/engine-formula @univerjs/engine-render @univerjs/sheets @univerjs/sheets-formula @univerjs/sheets-ui @univerjs/ui

npm install @univerjs/facade

三、表格数据结构

/**
 * Copyright 2023-present DreamNum Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

var { UniverCore } = window;

/**
 * Default workbook data
 * @type {IWorkbookData} document see https://univer.work/api/core/interfaces/IWorkbookData.html
 */
export const DEFAULT_WORKBOOK_DATA = {
  id: 'workbook-01',
  locale: UniverCore.LocaleType.ZH_CN,
  name: 'universheet',
  sheetOrder: ['sheet-01', 'sheet-02', 'sheet-03'],
  appVersion: '3.0.0-alpha',
  sheets: {
    'sheet-01': {
      type: UniverCore.SheetTypes.GRID,
      id: 'sheet-01',
      cellData: {
        0: {
          0: {
            v: 'Hello World',
          },
        },
      },
      name: 'sheet1',
      tabColor: 'red',
      hidden: UniverCore.BooleanNumber.FALSE,
      rowCount: 1000,
      columnCount: 20,
      zoomRatio: 1,
      scrollTop: 200,
      scrollLeft: 100,
      defaultColumnWidth: 93,
      defaultRowHeight: 27,
      status: 1,
      showGridlines: 1,
      hideRow: [],
      hideColumn: [],
      rowHeader: {
        width: 46,
        hidden: UniverCore.BooleanNumber.FALSE,
      },
      columnHeader: {
        height: 20,
        hidden: UniverCore.BooleanNumber.FALSE,
      },
      selections: ['A2'],
      rightToLeft: UniverCore.BooleanNumber.FALSE,
      pluginMeta: {},
    },
    'sheet-02': {
      type: UniverCore.SheetTypes.GRID,
      id: 'sheet-02',
      name: 'sheet2',
      cellData: {},
    },
    'sheet-03': {
      type: UniverCore.SheetTypes.GRID,
      id: 'sheet-03',
      name: 'sheet3',
      cellData: {},
    },
  },
};

四、API

// 获取当前表数据
const activeSheet = univerAPI.getActiveWorkbook().getActiveSheet()

// 设置单元格 value
 const range = activeSheet.getRange(0, 0)
 if (!range) {
   throw new Error('range is not defined')
 }
range.setValue(value)

// 设置某个范围单元格数据
 const values = [
  ['Hello', 'World!'],
  ['Hello', 'Univer!'],
]
const range = activeSheet.getRange(0, 0, values.length, values[0].length)
// getRange对应的从第0行第0列，到第X行，第X列（索引0开始）
if (!range)
      throw new Error('range is not defined')
range.setValues(values)

// 获取某个单元格value =>getRange=>第X行，第X列, 到 到第X行，第X列（索引0开始）
const range = activeSheet.getRange(0, 0, 2, 2)
if (!range)
  throw new Error('range is not defined')

const data = []
range.forEach((row, col, cell) => {
  data[row] = data[row] || []
  data[row][col] = cell.v?.toString()
})

// 获取workbookData 和传入的数据格式一致
const activeWorkbook = univerAPI.getActiveWorkbook()
console.log(JSON.stringify(activeWorkbook.getSnapshot(), null, 2))

// 获取sheetData 数据 -> 是获取workbookData中的sheet中的data
const activeWorkbook = univerAPI.getActiveWorkbook()
if (!activeWorkbook)
  throw new Error('activeWorkbook is not defined')

const snapshot = activeWorkbook.getSnapshot()
const sheet1 = Object.values(snapshot.sheets).find((sheet) => {
  return sheet.id === 'sheet-01'
})

// 创建sheet
const activeWorkbook = univerAPI.getActiveWorkbook()
const sheet = activeWorkbook.create('Sheet2', 10, 10) // 设置10行10列表格
 
// scroll to B100
const activeWorkbook = univerAPI.getActiveWorkbook()
univerAPI.executeCommand('sheet.command.scroll-to-cell', {
  range: {
    // top 设置 0 0
    startColumn: 1, 
    startRow: 99,
  },
})
// scroll to bottom
const { rowCount } = activeSheet._worksheet.getSnapshot()
univerAPI.executeCommand('sheet.command.scroll-to-cell', {
  range: {
    startColumn: 0,
    startRow: rowCount - 1,
  },
})

// 设置单元格背景
const range = activeSheet.getRange(0, 0, 1, 1)
range?.setBackgroundColor('red')

// 聚焦单元格
const subUnitId = activeSheet._worksheet.getSheetId()
univerAPI.executeCommand('sheet.operation.set-selections', {
  selections: [{
    range: {
      startRow: 0,
      startColumn: 0,
      endRow: 0,
      endColumn: 0,
      rangeType: 0,
    },
  }],
  subUnitId,
  unitId: activeWorkbook.getId(),
  type: 2,
})

// 清除A1 styles
const subUnitId = activeSheet._worksheet.getSheetId()

await univerAPI.executeCommand('sheet.operation.set-selections', {
  selections: [{
    range: {
      startRow: 0,
      startColumn: 0,
      endRow: 0,
      endColumn: 0,
      rangeType: 0,
    },
  }],
  subUnitId,
  unitId: activeWorkbook.getId(),
  type: 2,
})

univerAPI.executeCommand('sheet.command.clear-selection-format')

五、案例应用：univer在线协同表格

使用node.js搭建websorket

后端代码:

//创建一个WebSocket服务器，在8080端口启动
const WebSocket = require('ws');
const server = new WebSocket.Server({ port: 8089 });

// 监听前端连接websocket（ws.on的connection事件）
server.on('connection', function connection(ws, req) {
  const ip = req.socket.remoteAddress;
  const port = req.socket.remotePort;
  const clientName = ip + port;

  console.log('%s is connected ', clientName);
});
//只要有WebSocket连接到该服务器，就会触发'connection'事件；req对象可以用来获取客户端的信息，如ip、端口号
//获取所有已连接的客户端信息，则可以使用server.clients数据集

// 服务器接收数据（ws.on的message事件）
module.exports.listener = () => {
  server.on('connection', (ws, req) => {
    console.log('有客户端连接成功了', ws, req);

    // 对客户端的连接对象进行message事件的监听
    // 当客户端有消息发送给服务器时，服务器就能够触发该消息
    // msg:由客户端发给服务端的数据
    ws.on('message', (msg = {}) => {
      console.log('客户端发送给服务器端', msg);
      // 当接收到客户端传的参数之后服务器端可以执行某些操作(具体看需求)
      // 小编这里是做了一个数据返回给客户端
      // 是当客户端连接成功之后会发送一条信息告诉服务器，服务器监听到信息之后再返回数据给客户端

      // 由服务端往客户端发送数据
      server.clients.forEach((client) => {
        console.log(client, 'client');
        // client.send(JSON.stringify(info));
        client.send(msg);
      });
    });
  });
};

前端代码：

<template>
  <div>
    <div id="univerContainer" style="height: 500px"></div>
  </div>
</template>

<script setup>
var {
  UniverCore,
  UniverDesign,
  UniverEngineRender,
  UniverEngineFormula,
  UniverDocs,
  UniverDocsUi,
  UniverUi,
  UniverSheets,
  UniverSheetsUi,
  UniverSheetsNumfmt,
  UniverSheetsFormula,
  UniverFacade,
} = window;

var univer = new UniverCore.Univer({
  theme: UniverDesign.defaultTheme,
});

univer.registerPlugin(UniverEngineRender.UniverRenderEnginePlugin);
univer.registerPlugin(UniverEngineFormula.UniverFormulaEnginePlugin);

univer.registerPlugin(UniverUi.UniverUIPlugin, {
  container: 'univerContainer',
});

univer.registerPlugin(UniverDocs.UniverDocsPlugin, {
  hasScroll: false,
});
univer.registerPlugin(UniverDocsUi.UniverDocsUIPlugin);

univer.registerPlugin(UniverSheets.UniverSheetsPlugin);
univer.registerPlugin(UniverSheetsUi.UniverSheetsUIPlugin);
univer.registerPlugin(UniverSheetsNumfmt.UniverSheetsNumfmtPlugin);
univer.registerPlugin(UniverSheetsFormula.UniverSheetsFormulaPlugin);


univer.createUniverSheet({
  id: 'univer-1',
  sheets: {
    'sheet-01': {
      id: 'sheet-01',
      name: 'sheet1',
      cellData: {},
    },
  },
});

const univerAPI = UniverFacade.FUniver.newAPI(univer);

const ws = univerAPI.createSocket('ws://localhost:8089');

ws.open$.subscribe(() => {
  console.log('websocket opened');
  // ws.send('hello')
});

ws.message$.subscribe((message) => {
  console.log('websocket message', JSON.parse(message.data));
  const content = JSON.parse(message.data);
  console.log('content', content);

  const { command, options } = content;

  const { id, params } = command;

  console.log(params, 'params--------');

  // 接受到协同数据，本地落盘
  univerAPI.executeCommand(id, params, options);
});

ws.close$.subscribe(() => {
  console.log('websocket closed');
});

ws.error$.subscribe((error) => {
  console.log('websocket error', error);
});

univerAPI.onCommandExecuted((command, options) => {
  // 仅同步本地 mutation
  if (
    command.type !== 2 ||
    options?.fromCollab ||
    options?.onlyLocal ||
    command.id === 'doc.mutation.rich-text-editing'
  ) {
    return;
  }

  const commandInfo = JSON.stringify({
    command,
    options: { fromCollab: true },
  });
  console.log(commandInfo, 'commandInfo');
  ws.send(commandInfo);
});
</script>

注意：必须指定id才能协同

univer.createUniverSheet({
  id: 'univer-1',
  sheets: {
    'sheet-01': {
      id: 'sheet-01',
      name: 'sheet1',
      cellData: {},
    },
  },
});