前端获取excel表格数据并在浏览器展示

qq_44532387

已于 2024-07-03 11:40:59 修改

阅读量726

点赞数 3

文章标签：前端 excel javascript react

于 2024-06-26 17:22:21 首次发布

本文链接：https://blog.csdn.net/qq_44532387/article/details/139992852

版权

插件地址：Installation – Univer

本人是在使用react时产生这个需求的所以示范代码使用react

使用其他框架的可以提取关键代码实现

1、安装插件

安装Univer

使用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

使用pnpm

pnpm add @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

安装xlsx

npm i xlsx

2、引入插件和实例化插件

你需要在项目中引入 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, UniverInstanceType } 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";

然后创建一个 Univer 实例，并注册这些插件：

const univer = new Univer({
  theme: defaultTheme,
  locale: LocaleType.ZH_CN,
  locales: {
    [LocaleType.ZH_CN]: Tools.deepMerge(
      SheetsZhCN,
      DocsUIZhCN,
      SheetsUIZhCN,
      UIZhCN,
      DesignZhCN,
    ),
  },
});
 
univer.registerPlugin(UniverRenderEnginePlugin);
univer.registerPlugin(UniverFormulaEnginePlugin);
 
univer.registerPlugin(UniverUIPlugin, {
  container: 'app',
});
 
univer.registerPlugin(UniverDocsPlugin, {
  hasScroll: false,
});
univer.registerPlugin(UniverDocsUIPlugin);
 
univer.registerPlugin(UniverSheetsPlugin);
univer.registerPlugin(UniverSheetsUIPlugin);
univer.registerPlugin(UniverSheetsFormulaPlugin);
 
univer.createUnit(UniverInstanceType.UNIVER_SHEET, {});

要加载数据还需这个包 @univerjs/facade

npm i @univerjs/facade

import { FUniver } from "@univerjs/facade";
 
const univerAPI = FUniver.newAPI(univer);

3、获取数据源

上传本地文件

const App = () => {
    const handleFileUpload = (e) => {
        const file = e.target.files[0];
        const reader = new FileReader();

        reader.onload = (event) => {
          const data = new Uint8Array(event.target.result);
          const workbook = XLSX.read(data, { type: "array" }); // 数据源
        };
    };

    return <>
        <input type="file" onChange={handleFileUpload} />
    </>
}

根据接口获取

const App = () => {
    const fetchExcelData = async () => {
        try {
          const response = await axios("http://xxx.xxx.xxx", {
            method: "get",
            responseType: "arraybuffer",
            headers: {
              Authorization: "bearer xxx", // 添加你的认证令牌
            },
          });

          const data = new Uint8Array(response.data);
          const workbook = XLSX.read(data, { type: "array" }); // 数据源
        } catch (error) {
          console.error("Failed to fetch Excel data:", error);
        }
      };

    return <></>
}

4、处理成插件所需的数据格式

数据格式定义：Interface: IWorkbookData – Univer

workbook // 数据源 
 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 });
      console.log(jsonSheet);
      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,  // 多少行
        columnCount: maxColumnCount, // 多少列
        zoomRatio: 1,
        defaultColumnWidth: 73,
        defaultRowHeight: 23,
        cellData: cellData, // 每个单元格的数据
        showGridlines: 1,
        rowHeader: {
          width: 40,
          hidden: 0,
        },
        columnHeader: {
          height: 20,
          hidden: 0,
        },
      };

      sheetOrder.push(sheetId);
    });

    return {
      id: "workbook",
      sheetOrder: sheetOrder,
      locale: "zhCN",
      sheets: sheets,
    };
  };

5、在获取数据源后渲染到界面上(完整代码)

渲染效果界面

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, UniverInstanceType } 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 { FUniver } from "@univerjs/facade";
import { useEffect, useRef, useState } from "react";
import * as XLSX from "xlsx";

const App = () => {
  const univerAPI = useRef();
  const univer = useRef();

  useEffect(() => {
    // fetchExcelData(); // 接口获取删除此行注释  本地上传点击上传按钮
  }, []);


  const init = () => {
    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: "excel2",
    });

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

    univer.current.registerPlugin(UniverSheetsPlugin);
    univer.current.registerPlugin(UniverSheetsUIPlugin);
    univer.current.registerPlugin(UniverSheetsFormulaPlugin);

    univerAPI.current = FUniver.newAPI(univer.current);

    // 创建一个空白的表格可删除以下代码注释 并在useEffect中执行init();

    // univer.current.createUnit(UniverInstanceType.UNIVER_SHEET, {
    //   id: "gyI0JO",
    //   sheetOrder: ["RSfWjJFv4opmE1JaiRj80"],
    //   name: "",
    //   appVersion: "0.1.11",
    //   locale: "zhCN",
    //   styles: {},
    //   sheets: {
    //     RSfWjJFv4opmE1JaiRj80: {
    //       id: "RSfWjJFv4opmE1JaiRj80",
    //       name: "测试",
    //       tabColor: "",
    //       hidden: 0,
    //       rowCount: 20,
    //       columnCount: 10,
    //       zoomRatio: 1,
    //       freeze: {
    //         startRow: -1,
    //         startColumn: -1,
    //         ySplit: 0,
    //         xSplit: 0,
    //       },
    //       scrollTop: 0,
    //       scrollLeft: 0,
    //       defaultColumnWidth: 73,
    //       defaultRowHeight: 23,
    //       mergeData: [],
    //       cellData: {
    //         0: [
    //           {
    //             v: "123",
    //           },
    //           {
    //             v: "222",
    //           },
    //         ],
    //       },
    //       rowData: {
    //         0: {
    //           h: 105,
    //           hd: 0,
    //         },
    //       },
    //       columnData: {
    //         0: {
    //           w: 105,
    //           hd: 0,
    //         },
    //         1: {
    //           w: 100,
    //           hd: 0,
    //         },
    //         2: {
    //           w: 125,
    //           hd: 0,
    //         },
    //         3: {
    //           w: 125,
    //           hd: 0,
    //         },
    //         4: {
    //           w: 125,
    //           hd: 0,
    //         },
    //         5: {
    //           w: 125,
    //           hd: 0,
    //         },
    //         6: {
    //           w: 125,
    //           hd: 0,
    //         },
    //         7: {
    //           w: 125,
    //           hd: 0,
    //         },
    //         8: {
    //           w: 125,
    //           hd: 0,
    //         },
    //         9: {
    //           w: 125,
    //           hd: 0,
    //         },
    //       },
    //       showGridlines: 1,
    //       rowHeader: {
    //         width: 40,
    //         hidden: 0,
    //       },
    //       columnHeader: {
    //         height: 20,
    //         hidden: 0,
    //       },
    //       selections: ["B2"],
    //       rightToLeft: 0,
    //     },
    //   },
    //   resources: [
    //     {
    //       name: "SHEET_DEFINED_NAME_PLUGIN",
    //       data: "",
    //     },
    //   ],
    // });
  };

   // 点击上传按钮获取数据
  const handleFileUpload = (e) => {
    const file = e.target.files[0];
    const reader = new FileReader();

    reader.onload = (event) => {
      const data = new Uint8Array(event.target.result);
      const workbook = XLSX.read(data, { type: "array" }); // 数据源
      const jsonWorkbook = convertWorkbookToJson(workbook);

      univer.current.createUnit(UniverInstanceType.UNIVER_SHEET, jsonWorkbook) // 输入数据生成表格
    };
  };

    // 接口获取
  const fetchExcelData = async () => {
    try {
      const response = await axios("http://xxx.xxx.xxx", {
        method: "get",
        responseType: "arraybuffer", // 确保以数组缓冲区的形式获取二进制数据
        headers: {
          Authorization: "bearer xxx", // 添加你的认证令牌
        },
      });

      const data = new Uint8Array(response.data);
      const workbook = XLSX.read(data, { type: "array" }); // 数据源
      const jsonWorkbook = convertWorkbookToJson(workbook);

      univer.current.createUnit(UniverInstanceType.UNIVER_SHEET, jsonWorkbook) // 输入数据生成表格
    } catch (error) {
      console.error("Failed to fetch Excel data:", error);
    }
  };

  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 });
      console.log(jsonSheet);
      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,
        columnCount: maxColumnCount,
        zoomRatio: 1,
        defaultColumnWidth: 73,
        defaultRowHeight: 23,
        mergeData: mergeData,
        cellData: cellData,
        showGridlines: 1,
        rowHeader: {
          width: 40,
          hidden: 0,
        },
        columnHeader: {
          height: 20,
          hidden: 0,
        },
      };

      sheetOrder.push(sheetId);
    });

    return {
      id: "workbook",
      sheetOrder: sheetOrder,
      locale: "zhCN",
      sheets: sheets,
    };
  };
  return (
    <>
      {/* 表格容器 */}
      <div id="excel2" style={{ width: "1000px", height: "800px" }}></div>
      {/* 上传按钮 */}
      <input type="file" onChange={handleFileUpload} />
    </>
  );
};

export default App;