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

前端univer创建、编辑excel

news 来源：原创 2024/9/21 13:57:11

前端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

创建实例

引入 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";

创建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,};};<Uploadaccept=".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><Uploadaccept=".xls,.xlsx"onChange={handleChangeUploadExcel}beforeUpload={() => false}showUploadList={false}multiple={false}><Button type="primary" className="upload-btn">选择excel文件</Button></Upload></div><divid="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;