前端獲取excel表格數(shù)據(jù)并在瀏覽器展示方法實(shí)例
更新時(shí)間：2024年08月21日 09:33:29 作者：qq_44532387
在開發(fā)過程中,難免會(huì)碰到用前端來處理excel文件的需求,這篇文章主要給大家介紹了關(guān)于前端獲取excel表格數(shù)據(jù)并在瀏覽器展示方的相關(guān)資料,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下
前言
本人是在使用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 的樣式文件、語言包，以及一些必要的插件：
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;總結(jié)
到此這篇關(guān)于前端獲取excel表格數(shù)據(jù)并在瀏覽器展示的文章就介紹到這了,更多相關(guān)前端獲取excel表格數(shù)據(jù)展示內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了微信用戶訪問小程序的登錄過程詳解，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2019-09-09
通過JS自動(dòng)隱藏手機(jī)瀏覽器的地址欄實(shí)現(xiàn)原理與代碼
大家通過手機(jī)自帶瀏覽器打開百度、淘寶，在首頁加載完畢后，會(huì)自動(dòng)隱藏頁面上方的地址欄,感興趣的朋友可以參考下2013-01-01
這篇文章主要為大家詳細(xì)介紹了Javascript的爺孫通信和組件自調(diào)用，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下，希望能夠給你帶來幫助2022-03-03
微信小程序?qū)崿F(xiàn)吸頂效果的方法實(shí)例
在微信小程序的開發(fā)中,經(jīng)常會(huì)有列表分類標(biāo)簽隨著界面滾動(dòng)吸頂?shù)男Ч?下面這篇文章主要給大家介紹了關(guān)于微信小程序?qū)崿F(xiàn)吸頂效果的相關(guān)資料,需要的朋友可以參考下2021-08-08
簡(jiǎn)單談?wù)刯avascript中的變量、作用域和內(nèi)存問題
這篇文章主要介紹了簡(jiǎn)單談?wù)刯avascript中的變量、作用域和內(nèi)存問題的相關(guān)資料,需要的朋友可以參考下2015-08-08
使用JavaScript實(shí)現(xiàn)圖片放大鏡功能
圖片放大鏡（Image?Zoom）效果在許多電子商務(wù)網(wǎng)站、在線畫廊和產(chǎn)品展示頁面中得到廣泛應(yīng)用,它允許用戶通過鼠標(biāo)懸停在圖片上,查看圖片的詳細(xì)局部放大效果,本文將詳細(xì)介紹如何使用?JavaScript?實(shí)現(xiàn)一個(gè)基本的圖片放大鏡功能,需要的朋友可以參考下2024-12-12
近來有客戶要求用table顯示一大串?dāng)?shù)據(jù)，由于滾動(dòng)后就看不到表頭，很不方便，所以想到這個(gè)效果。2009-05-05
JavaScript 未結(jié)束的字符串常量常見解決方法
做JavaScript的時(shí)候,發(fā)現(xiàn)老是出現(xiàn)錯(cuò)誤：“未結(jié)束的字符串常量”. 自己找了下應(yīng)該是傳參數(shù)的時(shí)候,有特殊字符引起的.網(wǎng)上也找了下,也有好多出現(xiàn)這種情況.做下總結(jié),以方便以后查閱.2010-01-01
JavaScript中使用arguments獲得函數(shù)傳參個(gè)數(shù)實(shí)例
這篇文章主要介紹了JavaScript中使用arguments獲得函數(shù)傳參個(gè)數(shù)實(shí)例,本文用了多個(gè)例子來講解arguments的使用,需要的朋友可以參考下2014-08-08
控制圖片大小的方法有很多，在本文將為大家詳細(xì)介紹下使用js實(shí)現(xiàn)縮放圖片，核心代碼如下，感興趣的朋友可以參考下2013-10-10