前端univer创建、编辑excel

前端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);};},[]);constcreateSheet=(config = workbookConfig)=>{if(univerAPI.current){
      univerAPI.current.disposeUnit(workbookConfig.id);}
    univer.current =newUniver({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导入数据

/** 上传文件 */consthandleChangeUploadExcel=async(fileData)=>{const reader =newFileReader();
    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 */constconvertWorkbookToJson=(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*asXLSXfrom"xlsx";const workbookConfig ={id:"workbook",locale:"zhCN",};constApp=()=>{const univerAPI =useRef();const univer =useRef();const[excelData, setExcelData]=useState("");useEffect(()=>{
    univer.current =newUniver({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);};},[]);/** 获取表格数据 */consthandleGetSheetData=()=>{const data = univerAPI.current.getActiveWorkbook().save();
    console.log("%c [ data ]-68","font-size:13px; background:pink; color:#bf2c9f;",
      data
    );setExcelData(JSON.stringify(data));};/** 上传文件 */consthandleChangeUploadExcel=async(fileData)=>{const reader =newFileReader();
    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 */constconvertWorkbookToJson=(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>);};exportdefault App;

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