univer表格应用及案例

大家好，欢迎来到IT知识分享网。

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

一、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: {}, }, }, });