前端獲取excel表格數(shù)據(jù)并在瀏覽器展示方法實(shí)例

更新時(shí)間：2024年08月21日 09:33:29 作者：qq_44532387

在開(kāi)發(fā)過(guò)程中,難免會(huì)碰到用前端來(lái)處理excel文件的需求,這篇文章主要給大家介紹了關(guān)于前端獲取excel表格數(shù)據(jù)并在瀏覽器展示方的相關(guān)資料,文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下

前言

插件地址：Installation – Univer

本人是在使用react時(shí)產(chǎn)生這個(gè)需求的所以示范代碼使用react

使用其他框架的可以提取關(guān)鍵代碼實(shí)現(xiàn)

1、安裝插件

安裝Univer

Univer：https://univer.ai/zh-CN

使用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、引入插件和實(shí)例化插件

你需要在項(xiàng)目中引入 Univer 的樣式文件、語(yǔ)言包，以及一些必要的插件：

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

然后創(chuàng)建一個(gè) Univer 實(shí)例，并注冊(cè)這些插件：

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

要加載數(shù)據(jù)還需這個(gè)包 @univerjs/facade

npm i @univerjs/facade

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

3、獲取數(shù)據(jù)源

上傳本地文件

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" }); // 數(shù)據(jù)源
        };
    };

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

根據(jù)接口獲取

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

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

    return <></>
}

4、處理成插件所需的數(shù)據(jù)格式

數(shù)據(jù)格式定義：Interface: IWorkbookData – Univer

workbook // 數(shù)據(jù)源 
 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, // 每個(gè)單元格的數(shù)據(jù)
        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、在獲取數(shù)據(jù)源后渲染到界面上(完整代碼)

渲染效果界面

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(); // 接口獲取刪除此行注釋  本地上傳點(diǎn)擊上傳按鈕
  }, []);


  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);

    // 創(chuàng)建一個(gè)空白的表格可刪除以下代碼注釋 并在useEffect中執(zhí)行init();

    // univer.current.createUnit(UniverInstanceType.UNIVER_SHEET, {
    //   id: "gyI0JO",
    //   sheetOrder: ["RSfWjJFv4opmE1JaiRj80"],
    //   name: "",
    //   appVersion: "0.1.11",
    //   locale: "zhCN",
    //   styles: {},
    //   sheets: {
    //     RSfWjJFv4opmE1JaiRj80: {
    //       id: "RSfWjJFv4opmE1JaiRj80",
    //       name: "測(cè)試",
    //       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: "",
    //     },
    //   ],
    // });
  };

   // 點(diǎn)擊上傳按鈕獲取數(shù)據(jù)
  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" }); // 數(shù)據(jù)源
      const jsonWorkbook = convertWorkbookToJson(workbook);

      univer.current.createUnit(UniverInstanceType.UNIVER_SHEET, jsonWorkbook) // 輸入數(shù)據(jù)生成表格
    };
  };

    // 接口獲取
  const fetchExcelData = async () => {
    try {
      const response = await axios("http://xxx.xxx.xxx", {
        method: "get",
        responseType: "arraybuffer", // 確保以數(shù)組緩沖區(qū)的形式獲取二進(jìn)制數(shù)據(jù)
        headers: {
          Authorization: "bearer xxx", // 添加你的認(rèn)證令牌
        },
      });

      const data = new Uint8Array(response.data);
      const workbook = XLSX.read(data, { type: "array" }); // 數(shù)據(jù)源
      const jsonWorkbook = convertWorkbookToJson(workbook);

      univer.current.createUnit(UniverInstanceType.UNIVER_SHEET, jsonWorkbook) // 輸入數(shù)據(jù)生成表格
    } 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;