0


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

插件地址:Installation – Univer

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

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

1、安装插件

安装Univer

使用npm

  1. 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

  1. 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

  1. npm i xlsx

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

你需要在项目中引入 Univer 的样式文件、语言包,以及一些必要的插件:

  1. import "@univerjs/design/lib/index.css";
  2. import "@univerjs/ui/lib/index.css";
  3. import "@univerjs/docs-ui/lib/index.css";
  4. import "@univerjs/sheets-ui/lib/index.css";
  5. import "@univerjs/sheets-formula/lib/index.css";
  6. import { LocaleType, Tools, Univer, UniverInstanceType } from "@univerjs/core";
  7. import { defaultTheme } from "@univerjs/design";
  8. import { UniverFormulaEnginePlugin } from "@univerjs/engine-formula";
  9. import { UniverRenderEnginePlugin } from "@univerjs/engine-render";
  10. import { UniverUIPlugin } from "@univerjs/ui";
  11. import { UniverDocsPlugin } from "@univerjs/docs";
  12. import { UniverDocsUIPlugin } from "@univerjs/docs-ui";
  13. import { UniverSheetsPlugin } from "@univerjs/sheets";
  14. import { UniverSheetsFormulaPlugin } from "@univerjs/sheets-formula";
  15. import { UniverSheetsUIPlugin } from "@univerjs/sheets-ui";
  16. import DesignZhCN from "@univerjs/design/locale/zh-CN";
  17. import UIZhCN from "@univerjs/ui/locale/zh-CN";
  18. import DocsUIZhCN from "@univerjs/docs-ui/locale/zh-CN";
  19. import SheetsZhCN from "@univerjs/sheets/locale/zh-CN";
  20. import SheetsUIZhCN from "@univerjs/sheets-ui/locale/zh-CN";

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

  1. const univer = new Univer({
  2. theme: defaultTheme,
  3. locale: LocaleType.ZH_CN,
  4. locales: {
  5. [LocaleType.ZH_CN]: Tools.deepMerge(
  6. SheetsZhCN,
  7. DocsUIZhCN,
  8. SheetsUIZhCN,
  9. UIZhCN,
  10. DesignZhCN,
  11. ),
  12. },
  13. });
  14. univer.registerPlugin(UniverRenderEnginePlugin);
  15. univer.registerPlugin(UniverFormulaEnginePlugin);
  16. univer.registerPlugin(UniverUIPlugin, {
  17. container: 'app',
  18. });
  19. univer.registerPlugin(UniverDocsPlugin, {
  20. hasScroll: false,
  21. });
  22. univer.registerPlugin(UniverDocsUIPlugin);
  23. univer.registerPlugin(UniverSheetsPlugin);
  24. univer.registerPlugin(UniverSheetsUIPlugin);
  25. univer.registerPlugin(UniverSheetsFormulaPlugin);
  26. univer.createUnit(UniverInstanceType.UNIVER_SHEET, {});

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

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

3、获取数据源

上传本地文件

  1. const App = () => {
  2. const handleFileUpload = (e) => {
  3. const file = e.target.files[0];
  4. const reader = new FileReader();
  5. reader.onload = (event) => {
  6. const data = new Uint8Array(event.target.result);
  7. const workbook = XLSX.read(data, { type: "array" }); // 数据源
  8. };
  9. };
  10. return <>
  11. <input type="file" onChange={handleFileUpload} />
  12. </>
  13. }

根据接口获取

  1. const App = () => {
  2. const fetchExcelData = async () => {
  3. try {
  4. const response = await axios("http://xxx.xxx.xxx", {
  5. method: "get",
  6. responseType: "arraybuffer",
  7. headers: {
  8. Authorization: "bearer xxx", // 添加你的认证令牌
  9. },
  10. });
  11. const data = new Uint8Array(response.data);
  12. const workbook = XLSX.read(data, { type: "array" }); // 数据源
  13. } catch (error) {
  14. console.error("Failed to fetch Excel data:", error);
  15. }
  16. };
  17. return <></>
  18. }

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

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

  1. workbook // 数据源
  2. const convertWorkbookToJson = (workbook) => {
  3. const sheets = {};
  4. const sheetOrder = [];
  5. workbook.SheetNames.forEach((sheetName, sheetIndex) => {
  6. const worksheet = workbook.Sheets[sheetName];
  7. const jsonSheet = XLSX.utils.sheet_to_json(worksheet, { header: 1 });
  8. console.log(jsonSheet);
  9. const cellData = {};
  10. let maxColumnCount = 0;
  11. jsonSheet.forEach((row, rowIndex) => {
  12. row.forEach((cell, colIndex) => {
  13. if (cell !== null && cell !== undefined && cell !== "") {
  14. if (!cellData[rowIndex]) {
  15. cellData[rowIndex] = [];
  16. }
  17. cellData[rowIndex][colIndex] = { v: cell };
  18. if (colIndex + 1 > maxColumnCount) {
  19. maxColumnCount = colIndex + 1;
  20. }
  21. }
  22. });
  23. });
  24. const sheetId = `sheet_${sheetIndex}`;
  25. sheets[sheetId] = {
  26. id: sheetId,
  27. name: sheetName,
  28. rowCount: jsonSheet.length, // 多少行
  29. columnCount: maxColumnCount, // 多少列
  30. zoomRatio: 1,
  31. defaultColumnWidth: 73,
  32. defaultRowHeight: 23,
  33. cellData: cellData, // 每个单元格的数据
  34. showGridlines: 1,
  35. rowHeader: {
  36. width: 40,
  37. hidden: 0,
  38. },
  39. columnHeader: {
  40. height: 20,
  41. hidden: 0,
  42. },
  43. };
  44. sheetOrder.push(sheetId);
  45. });
  46. return {
  47. id: "workbook",
  48. sheetOrder: sheetOrder,
  49. locale: "zhCN",
  50. sheets: sheets,
  51. };
  52. };

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

渲染效果界面

  1. import "@univerjs/design/lib/index.css";
  2. import "@univerjs/ui/lib/index.css";
  3. import "@univerjs/docs-ui/lib/index.css";
  4. import "@univerjs/sheets-ui/lib/index.css";
  5. import "@univerjs/sheets-formula/lib/index.css";
  6. import { LocaleType, Tools, Univer, UniverInstanceType } from "@univerjs/core";
  7. import { defaultTheme } from "@univerjs/design";
  8. import { UniverFormulaEnginePlugin } from "@univerjs/engine-formula";
  9. import { UniverRenderEnginePlugin } from "@univerjs/engine-render";
  10. import { UniverUIPlugin } from "@univerjs/ui";
  11. import { UniverDocsPlugin } from "@univerjs/docs";
  12. import { UniverDocsUIPlugin } from "@univerjs/docs-ui";
  13. import { UniverSheetsPlugin } from "@univerjs/sheets";
  14. import { UniverSheetsFormulaPlugin } from "@univerjs/sheets-formula";
  15. import { UniverSheetsUIPlugin } from "@univerjs/sheets-ui";
  16. import DesignZhCN from "@univerjs/design/locale/zh-CN";
  17. import UIZhCN from "@univerjs/ui/locale/zh-CN";
  18. import DocsUIZhCN from "@univerjs/docs-ui/locale/zh-CN";
  19. import SheetsZhCN from "@univerjs/sheets/locale/zh-CN";
  20. import SheetsUIZhCN from "@univerjs/sheets-ui/locale/zh-CN";
  21. import { FUniver } from "@univerjs/facade";
  22. import { useEffect, useRef, useState } from "react";
  23. import * as XLSX from "xlsx";
  24. const App = () => {
  25. const univerAPI = useRef();
  26. const univer = useRef();
  27. useEffect(() => {
  28. // fetchExcelData(); // 接口获取删除此行注释 本地上传点击上传按钮
  29. }, []);
  30. const init = () => {
  31. univer.current = new Univer({
  32. theme: defaultTheme,
  33. locale: LocaleType.ZH_CN,
  34. locales: {
  35. [LocaleType.ZH_CN]: Tools.deepMerge(
  36. SheetsZhCN,
  37. DocsUIZhCN,
  38. SheetsUIZhCN,
  39. UIZhCN,
  40. DesignZhCN
  41. ),
  42. },
  43. });
  44. univer.current.registerPlugin(UniverRenderEnginePlugin);
  45. univer.current.registerPlugin(UniverFormulaEnginePlugin);
  46. univer.current.registerPlugin(UniverUIPlugin, {
  47. container: "excel2",
  48. });
  49. univer.current.registerPlugin(UniverDocsPlugin, {
  50. hasScroll: false,
  51. });
  52. univer.current.registerPlugin(UniverDocsUIPlugin);
  53. univer.current.registerPlugin(UniverSheetsPlugin);
  54. univer.current.registerPlugin(UniverSheetsUIPlugin);
  55. univer.current.registerPlugin(UniverSheetsFormulaPlugin);
  56. univerAPI.current = FUniver.newAPI(univer.current);
  57. // 创建一个空白的表格可删除以下代码注释 并在useEffect中执行init();
  58. // univer.current.createUnit(UniverInstanceType.UNIVER_SHEET, {
  59. // id: "gyI0JO",
  60. // sheetOrder: ["RSfWjJFv4opmE1JaiRj80"],
  61. // name: "",
  62. // appVersion: "0.1.11",
  63. // locale: "zhCN",
  64. // styles: {},
  65. // sheets: {
  66. // RSfWjJFv4opmE1JaiRj80: {
  67. // id: "RSfWjJFv4opmE1JaiRj80",
  68. // name: "测试",
  69. // tabColor: "",
  70. // hidden: 0,
  71. // rowCount: 20,
  72. // columnCount: 10,
  73. // zoomRatio: 1,
  74. // freeze: {
  75. // startRow: -1,
  76. // startColumn: -1,
  77. // ySplit: 0,
  78. // xSplit: 0,
  79. // },
  80. // scrollTop: 0,
  81. // scrollLeft: 0,
  82. // defaultColumnWidth: 73,
  83. // defaultRowHeight: 23,
  84. // mergeData: [],
  85. // cellData: {
  86. // 0: [
  87. // {
  88. // v: "123",
  89. // },
  90. // {
  91. // v: "222",
  92. // },
  93. // ],
  94. // },
  95. // rowData: {
  96. // 0: {
  97. // h: 105,
  98. // hd: 0,
  99. // },
  100. // },
  101. // columnData: {
  102. // 0: {
  103. // w: 105,
  104. // hd: 0,
  105. // },
  106. // 1: {
  107. // w: 100,
  108. // hd: 0,
  109. // },
  110. // 2: {
  111. // w: 125,
  112. // hd: 0,
  113. // },
  114. // 3: {
  115. // w: 125,
  116. // hd: 0,
  117. // },
  118. // 4: {
  119. // w: 125,
  120. // hd: 0,
  121. // },
  122. // 5: {
  123. // w: 125,
  124. // hd: 0,
  125. // },
  126. // 6: {
  127. // w: 125,
  128. // hd: 0,
  129. // },
  130. // 7: {
  131. // w: 125,
  132. // hd: 0,
  133. // },
  134. // 8: {
  135. // w: 125,
  136. // hd: 0,
  137. // },
  138. // 9: {
  139. // w: 125,
  140. // hd: 0,
  141. // },
  142. // },
  143. // showGridlines: 1,
  144. // rowHeader: {
  145. // width: 40,
  146. // hidden: 0,
  147. // },
  148. // columnHeader: {
  149. // height: 20,
  150. // hidden: 0,
  151. // },
  152. // selections: ["B2"],
  153. // rightToLeft: 0,
  154. // },
  155. // },
  156. // resources: [
  157. // {
  158. // name: "SHEET_DEFINED_NAME_PLUGIN",
  159. // data: "",
  160. // },
  161. // ],
  162. // });
  163. };
  164. // 点击上传按钮获取数据
  165. const handleFileUpload = (e) => {
  166. const file = e.target.files[0];
  167. const reader = new FileReader();
  168. reader.onload = (event) => {
  169. const data = new Uint8Array(event.target.result);
  170. const workbook = XLSX.read(data, { type: "array" }); // 数据源
  171. const jsonWorkbook = convertWorkbookToJson(workbook);
  172. univer.current.createUnit(UniverInstanceType.UNIVER_SHEET, jsonWorkbook) // 输入数据生成表格
  173. };
  174. };
  175. // 接口获取
  176. const fetchExcelData = async () => {
  177. try {
  178. const response = await axios("http://xxx.xxx.xxx", {
  179. method: "get",
  180. responseType: "arraybuffer", // 确保以数组缓冲区的形式获取二进制数据
  181. headers: {
  182. Authorization: "bearer xxx", // 添加你的认证令牌
  183. },
  184. });
  185. const data = new Uint8Array(response.data);
  186. const workbook = XLSX.read(data, { type: "array" }); // 数据源
  187. const jsonWorkbook = convertWorkbookToJson(workbook);
  188. univer.current.createUnit(UniverInstanceType.UNIVER_SHEET, jsonWorkbook) // 输入数据生成表格
  189. } catch (error) {
  190. console.error("Failed to fetch Excel data:", error);
  191. }
  192. };
  193. const convertWorkbookToJson = (workbook) => {
  194. const sheets = {};
  195. const sheetOrder = [];
  196. workbook.SheetNames.forEach((sheetName, sheetIndex) => {
  197. const worksheet = workbook.Sheets[sheetName];
  198. const jsonSheet = XLSX.utils.sheet_to_json(worksheet, { header: 1 });
  199. console.log(jsonSheet);
  200. const cellData = {};
  201. let maxColumnCount = 0;
  202. jsonSheet.forEach((row, rowIndex) => {
  203. row.forEach((cell, colIndex) => {
  204. if (cell !== null && cell !== undefined && cell !== "") {
  205. if (!cellData[rowIndex]) {
  206. cellData[rowIndex] = [];
  207. }
  208. cellData[rowIndex][colIndex] = { v: cell };
  209. if (colIndex + 1 > maxColumnCount) {
  210. maxColumnCount = colIndex + 1;
  211. }
  212. }
  213. });
  214. });
  215. const sheetId = `sheet_${sheetIndex}`;
  216. sheets[sheetId] = {
  217. id: sheetId,
  218. name: sheetName,
  219. rowCount: jsonSheet.length,
  220. columnCount: maxColumnCount,
  221. zoomRatio: 1,
  222. defaultColumnWidth: 73,
  223. defaultRowHeight: 23,
  224. mergeData: mergeData,
  225. cellData: cellData,
  226. showGridlines: 1,
  227. rowHeader: {
  228. width: 40,
  229. hidden: 0,
  230. },
  231. columnHeader: {
  232. height: 20,
  233. hidden: 0,
  234. },
  235. };
  236. sheetOrder.push(sheetId);
  237. });
  238. return {
  239. id: "workbook",
  240. sheetOrder: sheetOrder,
  241. locale: "zhCN",
  242. sheets: sheets,
  243. };
  244. };
  245. return (
  246. <>
  247. {/* 表格容器 */}
  248. <div id="excel2" style={{ width: "1000px", height: "800px" }}></div>
  249. {/* 上传按钮 */}
  250. <input type="file" onChange={handleFileUpload} />
  251. </>
  252. );
  253. };
  254. export default App;
标签: 前端 excel javascript

本文转载自: https://blog.csdn.net/qq_44532387/article/details/139992852
版权归原作者 qq_44532387 所有, 如有侵权,请联系我们删除。

“前端获取excel表格数据并在浏览器展示”的评论:

还没有评论