当前位置： 首页 > article >正文

前端univer创建、编辑excel

article 2024/9/21 16:07:56

前端univer创建、编辑excel

源码在线demo：https://codesandbox.io/p/sandbox/univer-q87kqg?file=/src/Demo.jsx

univer官网地址：https://univer.ai/zh-CN/guides/sheet/introduction

安装univer

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 @univerjs/facade

安装xlsx处理数据

npm install xlsx

创建实例

  1. 引入 Univer 的样式文件、语言包，插件
import "@univerjs/design/lib/index.css";
import "@univerjs/ui/lib/index.css";
import "@univerjs/docs-ui/lib/index.css";
import "@univerjs/sheets-ui/lib/index.css";
import "@univerjs/sheets-formula/lib/index.css";
import { LocaleType, Tools, Univer } from "@univerjs/core";
import { defaultTheme } from "@univerjs/design";
import { UniverFormulaEnginePlugin } from "@univerjs/engine-formula";
import { UniverRenderEnginePlugin } from "@univerjs/engine-render";
import { UniverUIPlugin } from "@univerjs/ui";
import { UniverDocsPlugin } from "@univerjs/docs";
import { UniverDocsUIPlugin } from "@univerjs/docs-ui";
import { UniverSheetsPlugin } from "@univerjs/sheets";
import { UniverSheetsFormulaPlugin } from "@univerjs/sheets-formula";
import { UniverSheetsUIPlugin } from "@univerjs/sheets-ui";
import DesignZhCN from "@univerjs/design/locale/zh-CN";
import UIZhCN from "@univerjs/ui//locale/zh-CN";
import DocsUIZhCN from "@univerjs/docs-ui/locale/zh-CN";
import SheetsZhCN from "@univerjs/sheets/locale/zh-CN";
import SheetsUIZhCN from "@univerjs/sheets-ui/locale/zh-CN";
import { UniverInstanceType } from "@univerjs/core";
import { FUniver } from "@univerjs/facade";
  1. 创建sheet实例，挂载到dom元素上
  const workbookConfig = {
    id: 'workbook',
    locale: "zhCN",
  }

  const univerAPI = useRef();
  const univer = useRef();
  
  useEffect(() => {
    createSheet()
    return () => {
      univerAPI.current.disposeUnit(workbookConfig.id);
    };
  }, []);
  const createSheet = (config = workbookConfig) => {
    if (univerAPI.current) {
      univerAPI.current.disposeUnit(workbookConfig.id);
    }
    univer.current = new Univer({
      theme: defaultTheme,
      locale: LocaleType.ZH_CN,
      locales: {
        [LocaleType.ZH_CN]: Tools.deepMerge(
          SheetsZhCN,
          DocsUIZhCN,
          SheetsUIZhCN,
          UIZhCN,
          DesignZhCN
        ),
      },
    });
    univer.current.registerPlugin(UniverRenderEnginePlugin);
    univer.current.registerPlugin(UniverFormulaEnginePlugin);
    univer.current.registerPlugin(UniverUIPlugin, {
      container: "univer-sheet-container-id",
    });
    univer.current.registerPlugin(UniverDocsPlugin);
    univer.current.registerPlugin(UniverDocsUIPlugin);
    univer.current.registerPlugin(UniverSheetsPlugin);
    univer.current.registerPlugin(UniverSheetsUIPlugin);
    univer.current.registerPlugin(UniverSheetsFormulaPlugin);
    univer.current.createUnit(UniverInstanceType.UNIVER_SHEET, config);
    univerAPI.current = FUniver.newAPI(univer.current);
  }


    // 挂在实例
	<div id="univer-sheet-container-id" className="univer-sheet-container" style={{height: "500px" }} />

导入excel文件、通过xlsx对文件进行处理，通过@univerjs/facade导入数据

  /** 上传文件 */
  const handleChangeUploadExcel = async (fileData) => {
    const reader = new FileReader();
    reader.onload = (e) => {
      const data = e.target.result;
      const workbook = XLSX.read(data, { type: "binary" });
      const excelData = convertWorkbookToJson(workbook);
      console.log(
        "%c [ excelData ]-87",
        "font-size:13px; background:pink; color:#bf2c9f;",
        excelData
      );
      createSheet(excelData);
    };
    reader.readAsBinaryString(fileData.file);
  };

  /** 将sheet数据转换为json */
  const convertWorkbookToJson = (workbook) => {
    const sheets = {};
    const sheetOrder = [];
    workbook.SheetNames.forEach((sheetName, sheetIndex) => {
      const worksheet = workbook.Sheets[sheetName];
      const jsonSheet = XLSX.utils.sheet_to_json(worksheet, { header: 1 });
      const cellData = {};
      let maxColumnCount = 0;
      jsonSheet.forEach((row, rowIndex) => {
        row.forEach((cell, colIndex) => {
          if (cell !== null && cell !== undefined && cell !== "") {
            if (!cellData[rowIndex]) {
              cellData[rowIndex] = [];
            }
            cellData[rowIndex][colIndex] = { v: cell };
            if (colIndex + 1 > maxColumnCount) {
              maxColumnCount = colIndex + 1;
            }
          }
        });
      });
      const sheetId = `sheet_${sheetIndex}`;
      sheets[sheetId] = {
        id: sheetId,
        name: sheetName,
        rowCount: jsonSheet.length + 50,
        columnCount: maxColumnCount + 50,
        zoomRatio: 1,
        cellData: cellData,
        showGridlines: 1,
      };
      sheetOrder.push(sheetId);
    });
    return {
      ...workbookConfig,
      sheetOrder: sheetOrder,
      sheets: sheets,
    };
  };
 
  <Upload
    accept=".xls,.xlsx"
     onChange={handleChangeUploadExcel}
     beforeUpload={() => false}
     showUploadList={false}
     multiple={false}
   >
     <Button type="primary" className="upload-btn">
       选择excel文件
     </Button>
   </Upload>

获取表格数据

univerAPI.current.getActiveWorkbook().save()

效果：

初始化：
在这里插入图片描述
导入数据

在这里插入图片描述
获取表格数据
在这里插入图片描述

完整代码：

import React, { useEffect, useRef, useState } from "react";
import "antd/dist/antd.css";
import "./index.css";
import { Button, Upload } from "antd";
import "@univerjs/design/lib/index.css";
import "@univerjs/ui/lib/index.css";
import "@univerjs/docs-ui/lib/index.css";
import "@univerjs/sheets-ui/lib/index.css";
import "@univerjs/sheets-formula/lib/index.css";
import { LocaleType, Tools, Univer } from "@univerjs/core";
import { defaultTheme } from "@univerjs/design";
import { UniverFormulaEnginePlugin } from "@univerjs/engine-formula";
import { UniverRenderEnginePlugin } from "@univerjs/engine-render";
import { UniverUIPlugin } from "@univerjs/ui";
import { UniverDocsPlugin } from "@univerjs/docs";
import { UniverDocsUIPlugin } from "@univerjs/docs-ui";
import { UniverSheetsPlugin } from "@univerjs/sheets";
import { UniverSheetsFormulaPlugin } from "@univerjs/sheets-formula";
import { UniverSheetsUIPlugin } from "@univerjs/sheets-ui";
import DesignZhCN from "@univerjs/design/locale/zh-CN";
import UIZhCN from "@univerjs/ui//locale/zh-CN";
import DocsUIZhCN from "@univerjs/docs-ui/locale/zh-CN";
import SheetsZhCN from "@univerjs/sheets/locale/zh-CN";
import SheetsUIZhCN from "@univerjs/sheets-ui/locale/zh-CN";
import { UniverInstanceType } from "@univerjs/core";
import { FUniver } from "@univerjs/facade";
import * as XLSX from "xlsx";

const workbookConfig = {
  id: "workbook",
  locale: "zhCN",
};

const App = () => {
  const univerAPI = useRef();
  const univer = useRef();
  const [excelData, setExcelData] = useState("");

  useEffect(() => {
    univer.current = new Univer({
      theme: defaultTheme,
      locale: LocaleType.ZH_CN,
      locales: {
        [LocaleType.ZH_CN]: Tools.deepMerge(
          SheetsZhCN,
          DocsUIZhCN,
          SheetsUIZhCN,
          UIZhCN,
          DesignZhCN
        ),
      },
    });
    univer.current.registerPlugin(UniverRenderEnginePlugin);
    univer.current.registerPlugin(UniverFormulaEnginePlugin);
    univer.current.registerPlugin(UniverUIPlugin, {
      container: "univer-sheet-container-id",
    });
    univer.current.registerPlugin(UniverDocsPlugin);
    univer.current.registerPlugin(UniverDocsUIPlugin);
    univer.current.registerPlugin(UniverSheetsPlugin);
    univer.current.registerPlugin(UniverSheetsUIPlugin);
    univer.current.registerPlugin(UniverSheetsFormulaPlugin);
    univer.current.createUnit(UniverInstanceType.UNIVER_SHEET, {});
    univerAPI.current = FUniver.newAPI(univer.current);
    return () => {
      univerAPI.current.disposeUnit(workbookConfig.id);
    };
  }, []);

  /** 获取表格数据 */
  const handleGetSheetData = () => {
    const data = univerAPI.current.getActiveWorkbook().save();
    console.log(
      "%c [ data ]-68",
      "font-size:13px; background:pink; color:#bf2c9f;",
      data
    );
    setExcelData(JSON.stringify(data));
  };

  /** 上传文件 */
  const handleChangeUploadExcel = async (fileData) => {
    const reader = new FileReader();
    reader.onload = (e) => {
      const data = e.target.result;
      const workbook = XLSX.read(data, { type: "binary" });
      const excelData = convertWorkbookToJson(workbook);
      console.log(
        "%c [ excelData ]-87",
        "font-size:13px; background:pink; color:#bf2c9f;",
        excelData
      );
      univerAPI.current.disposeUnit(workbookConfig.id);
      univer.current.createUnit(UniverInstanceType.UNIVER_SHEET, excelData);
    };
    reader.readAsBinaryString(fileData.file);
  };

  /** 将sheet数据转换为json */
  const convertWorkbookToJson = (workbook) => {
    const sheets = {};
    const sheetOrder = [];
    workbook.SheetNames.forEach((sheetName, sheetIndex) => {
      const worksheet = workbook.Sheets[sheetName];
      const jsonSheet = XLSX.utils.sheet_to_json(worksheet, { header: 1 });
      const cellData = {};
      let maxColumnCount = 0;
      jsonSheet.forEach((row, rowIndex) => {
        row.forEach((cell, colIndex) => {
          if (cell !== null && cell !== undefined && cell !== "") {
            if (!cellData[rowIndex]) {
              cellData[rowIndex] = [];
            }
            cellData[rowIndex][colIndex] = { v: cell };
            if (colIndex + 1 > maxColumnCount) {
              maxColumnCount = colIndex + 1;
            }
          }
        });
      });
      const sheetId = `sheet_${sheetIndex}`;
      sheets[sheetId] = {
        id: sheetId,
        name: sheetName,
        rowCount: jsonSheet.length + 50,
        columnCount: maxColumnCount + 50,
        zoomRatio: 1,
        cellData: cellData,
        showGridlines: 1,
      };
      sheetOrder.push(sheetId);
    });
    return {
      ...workbookConfig,
      sheetOrder: sheetOrder,
      sheets: sheets,
    };
  };

  return (
    <div className="main-container">
      <div>
        <Upload
          accept=".xls,.xlsx"
          onChange={handleChangeUploadExcel}
          beforeUpload={() => false}
          showUploadList={false}
          multiple={false}
        >
          <Button type="primary" className="upload-btn">
            选择excel文件
          </Button>
        </Upload>
      </div>
      <div
        id="univer-sheet-container-id"
        className="univer-sheet-container"
        style={{ height: "500px" }}
      />
      <div>
        <Button type="primary" onClick={handleGetSheetData}>
          获取表格数据
        </Button>
      </div>
      表格数据：<div>{excelData}</div>
    </div>
  );
};
export default App;

源码在线demo：https://codesandbox.io/p/sandbox/univer-q87kqg?file=/src/Demo.jsx

http://www.kler.cn/news/313451.html

相关文章：

  • 大模型爬虫—ScrapeGraphAI
  • AutoDL云计算GPT-SoVITS-TTS语音声色克隆语音合成
  • 卡牌抽卡机小程序：市场发展下的创新
  • 【Webpack--006】处理字体图标资源
  • 【2024华为杯数学建模竞赛】E题 解题思路 | 视频特征提取
  • 管理和合并多个开发者的 Git 提交：团队协作的艺术
  • 2023-基于深度学习的射频指纹识别与信道效应缓解
  • 使用Django 搭建自动化平台
  • 基于JavaWeb开发的java springboot+mybatis电影售票网站管理系统前台+后台设计和实现
  • Angular 2 TypeScript：现代Web开发的利器
  • Java免税购物商城：Spring Boot技术实现
  • intellij-idea创建html项目
  • 什么是chatgpt？国内有哪些类gpt模型？
  • 《家庭无线网络覆盖项目》
  • 装载机智能AI影像防撞系统，危险区域实现熄火停车功能！
  • halcon 快速定义字典
  • Hive企业级调优[3]—— Explain 查看执行计划
  • react crash course 2024 (1)理论概念
  • oracle 11g写一个判断是否是身份证的函数，函数名称为：FUN_IS_IDENNO
  • C++第一次练习
  • WPF 依赖属性改变触发响应事件
  • 反转字符串中的单词--力扣151
  • 影刀RPA实战：网页爬虫之苦瓜书籍数据
  • 前后端分离项目--下载功能
  • 移植Linux：如何制作rootfs？
  • 相机畸变系数$b_1,b_2$与畸变系数aspect ratio和skew的互转
  • css scrollbar-width: none 隐藏默认滚动条
  • WPF DataGrid 列表中，DataGrid.Columns 列根据不同的值显示不同内容
  • MyBatis 源码解析：TypeHandler 设计与自定义实现
  • ★pwn 更改pwn题libc保姆级教程★