当前位置： 代码网 > it编程>编程语言>Javascript > 前端获取excel表格数据并在浏览器展示方法实例

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

2024年09月06日 • Javascript •我要评论
前言插件地址：installation – univer本人是在使用react时产生这个需求的 所以示范代码使用react使用其他框架的可以提取关键代码实现1、安装插件安装univerun

前言

插件地址：installation – univer

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

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

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、引入插件和实例化插件

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

总结 

到此这篇关于前端获取excel表格数据并在浏览器展示的文章就介绍到这了,更多相关前端获取excel表格数据展示内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！

赞 (0)

相关文章:

版权声明：本文内容由互联网用户贡献，该文观点仅代表作者本人。本站仅提供信息存储服务，不拥有所有权，不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 2386932994@qq.com 举报，一经查实将立刻删除。

发表评论

验证码：
最近更新的文章
推荐阅读
大家感兴趣的文章
Copyright © 2017-2025  代码网 保留所有权利. 粤ICP备2024248653号
站长QQ:2386932994 | 联系邮箱：2386932994@qq.com