XLSX + LuckySheet + LuckyExcel + Web Worker实现前端的excel预览

文章目录

    • 功能简介
    • 简单代码实现
    • web worker 版本
    • 效果
    • 参考

功能简介

  1. 通过LuckyExcel的transformExcelToLucky方法, 我们可以把一个文件直接转成LuckySheet需要的json字符串, 之后我们就可以用LuckySheet预览excel
  2. LuckyExcel只能解析xlsx格式的excel文件,因此对于xls和csv的格式,我们需要通过XLSX来转化成xlsx格式,但在转化过程中会丢失样式
  3. 对于excel中存在很多的空白行,在显示的时候可能会出现卡顿,所以我们需要将过多的空白行移除

简单代码实现

<!DOCTYPE html>
<html lang="en">
<head><meta charset="UTF-8"><title>Excel File Upload and Preview with Luckysheet</title>
</head>
<body><!-- 文件上传控件 -->
<input type="file" id="fileUpload"/><!-- Luckysheet 的容器 -->
<div id="luckysheet" style="position: relative; width: 100%; height: 500px;"></div>
<script src="https://cdn.jsdelivr.net/npm/xlsx/dist/xlsx.full.min.js"></script><link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/plugins/css/pluginsCss.css'/>
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/plugins/plugins.css'/>
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/css/luckysheet.css'/>
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/assets/iconfont/iconfont.css'/>
<script src="https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/plugins/js/plugin.js"></script>
<script src="https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/luckysheet.umd.js"></script><script src="https://cdn.jsdelivr.net/npm/luckyexcel/dist/luckyexcel.umd.js"></script><script>const _xlsxType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';const  _xlsType = 'application/vnd.ms-excel';const  _csvType = 'text/csv';//如果后端是以流的方式返回,可以调用这个方法const handleExcel = (res, fileName) => {const file = getExcelFile(res, fileName);handleExcelFile(file);}// 获取Excel文件const getExcelFile = (res, fileName) => {// 根据文件后缀名判断文件类型if (fileName.endsWith('.xlsx')) {return new File([res], fileName, {type: _xlsxType});} else if (fileName.endsWith('.xls')) {return new File([res], fileName, {type: _xlsType});} else if (fileName.endsWith('.csv')) {return new File([res], fileName, {type: _csvType});} else {throw new Error("Unsupported file type");}}// 处理Excel文件const handleExcelFile = (file) => {const fileName = file.name;// 根据文件后缀名判断文件类型并进行处理if (fileName.endsWith('.xlsx')) {console.log("handle excel for xlsx type..", fileName);handleExcelForXlsxType(file, fileName);} else if (fileName.endsWith('.xls') || fileName.endsWith('.csv')) {console.log("handle excel for xls or csv type..", fileName);handleExcelForXlsAndCsvType(file, fileName);} else {throw new Error("Unsupported file type");}}// 处理xlsx类型的Excel文件const handleExcelForXlsxType = (file, fileName) => {const reader = new FileReader();reader.onload = function (event) {const data = new Uint8Array(event.target.result);const workbook = XLSX.read(data, {type: 'array'});// 获取Excel文件中的最大行数let maxRowCountFromExcel = getMaxRowCountFromExcel(workbook);// 如果行数大于100000,则处理Excel文件中的空行if (maxRowCountFromExcel > 1000000) {console.log("excel file has too many blank row..", maxRowCountFromExcel);handleBlankRowForExcelWithTooManyBlankRow(workbook);const xlsxFile = toXlsxExcelFile(workbook, fileName);createLuckySheet(xlsxFile);} else {createLuckySheet(file);}};reader.readAsArrayBuffer(file);}// 处理xls和csv类型的Excel文件const handleExcelForXlsAndCsvType = (file, fileName) => {const reader = new FileReader();// 读取文件完成后的回调函数reader.onload = function (event) {const data = new Uint8Array(event.target.result);// 读取Excel文件内容const workbook = XLSX.read(data, {type: 'array'});// 将Excel文件转换为xlsx类型const xlsxFile = toXlsxExcelFile(workbook, fileName);// 处理xlsx类型的Excel文件handleExcelForXlsxType(xlsxFile, fileName);};// 以ArrayBuffer的形式读取文件reader.readAsArrayBuffer(file);}/ 创建Luckysheetconst createLuckySheet = (file) => {// 销毁已存在的Luckysheetwindow.luckysheet.destroy();// 将Excel文件转换为Luckysheet的jsonLuckyExcel.transformExcelToLucky(file, function (exportJson, luckysheetfile) {if (exportJson.sheets == null || exportJson.sheets.length === 0) {throw new Error("Failed to load excel file");}// 创建Luckysheet的配置项const options = {container: 'luckysheet',data: exportJson.sheets, // title: exportJson.info.name,// userInfo: exportJson.info.name.creator,column: 10,row: 10,showinfobar: false,sheetFormulaBar: true,showConfigWindowResize: false};// 创建Luckysheetwindow.luckysheet.create(options);});}// 获取Excel文件中的最大行数const getMaxRowCountFromExcel = (workbook) => {let maxRowCount = 0;if (workbook.SheetNames == null || workbook.SheetNames.length === 0) {return maxRowCount;}// 遍历每个sheet,获取最大行数workbook.SheetNames.forEach(sheetName => {const worksheet = workbook.Sheets[sheetName];if (worksheet['!ref'] === undefined) {return;}const range = XLSX.utils.decode_range(worksheet['!ref']);maxRowCount = maxRowCount + range.e.r;});console.log("max:", maxRowCount)return maxRowCount;}const reduceBlankRow = (row, range, worksheet) => {// 从给定的行开始,向上遍历到工作表的起始行while (row > range.s.r) {// 假设当前行是空的let allEmpty = true;// 遍历当前行的所有列for (let col = range.s.c; col <= range.e.c; col++) {// 获取当前单元格的引用const cell_ref = XLSX.utils.encode_cell({c: col, r: row});// 如果当前单元格不为空,则将allEmpty设置为false并跳出循环if (worksheet[cell_ref]) {allEmpty = false;break;}}// 如果当前行是空的,则将行数减一,否则跳出循环if (allEmpty) {row--;} else {break;}}// 更新工作表范围的结束行range.e.r = row;// 更新工作表的范围引用worksheet['!ref'] = XLSX.utils.encode_range(range.s, range.e);}// 处理Excel文件中的空行const handleBlankRowForExcelWithTooManyBlankRow = (workbook) => {if (workbook.SheetNames == null || workbook.SheetNames.length === 0) {return;}// 遍历每个sheet,处理空行workbook.SheetNames.forEach(sheetName => {const worksheet = workbook.Sheets[sheetName];if (worksheet['!ref'] === undefined) {return;}const range = XLSX.utils.decode_range(worksheet['!ref']);let row = range.e.r;reduceBlankRow(row, range, worksheet);});}// 将Excel文件转换为xlsx类型const toXlsxExcelFile = (workbook, fileName) => {const newWorkbook = XLSX.write(workbook, {bookType: 'xlsx', type: 'binary'});const data = new Uint8Array(newWorkbook.length);for (let i = 0; i < newWorkbook.length; i++) {data[i] = newWorkbook.charCodeAt(i);}return new File([data], fileName, {type: _xlsxType});}// 文件上传控件的change事件处理函数document.getElementById('fileUpload').addEventListener('change', function (e) {// 获取上传的文件const file = e.target.files[0];// 处理Excel文件handleExcelFile(file);});</script></body>
</html>

web worker 版本

<!DOCTYPE html>
<html lang="en">
<head><meta charset="UTF-8"><title>Excel File Upload and Preview with Luckysheet</title>
</head>
<body><!-- 文件上传控件 -->
<input type="file" id="fileUpload"/><!-- Luckysheet 的容器 -->
<div id="luckysheet" style="position: relative; width: 100%; height: 500px;"></div>
<div id="worker" style="display:none">importScripts("https://cdn.jsdelivr.net/npm/xlsx/dist/xlsx.full.min.js")const _xlsxType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';const _xlsType = 'application/vnd.ms-excel';const _csvType = 'text/csv';const _maxRowCount = 5000000;self.addEventListener('message', (e) => {console.log('Worker get message:', e.data)handleExcel(e.data.data, e.data.fileName)});const handleExcel = (res, fileName) => {const file = getExcelFile(res, fileName);handleExcelFile(file);}const getExcelFile = (res, fileName) => {if (fileName.endsWith('.xlsx')) {return new File([res], fileName, {type: _xlsxType});} else if (fileName.endsWith('.xls')) {return new File([res], fileName, {type: _xlsType});} else if (fileName.endsWith('.csv')) {return new File([res], fileName, {type: _csvType});} else {throw new Error("Unsupported file type");}}const handleExcelFile = (file) => {const fileName = file.name;if (fileName.endsWith('.xlsx')) {console.log("handle excel for xlsx type..", fileName);handleExcelForXlsxType(file, fileName);} else if (fileName.endsWith('.xls') || fileName.endsWith('.csv')) {console.log("handle excel for xls or csv type..", fileName);handleExcelForXlsAndCsvType(file, fileName);} else {throw new Error("Unsupported file type");}}const handleExcelForXlsxType = (file, fileName) => {const reader = new FileReader();reader.onload = function (event) {const data = new Uint8Array(event.target.result);const workbook = XLSX.read(data, {type: 'array', cellDates: true});let maxRowCountFromExcel = getMaxRowCountFromExcel(workbook);if (maxRowCountFromExcel > _maxRowCount) {console.log("excel file has too many blank row..", maxRowCountFromExcel);handleBlankRowForExcelWithTooManyBlankRow(workbook);const xlsxFile = toXlsxExcelFile(workbook, fileName);createLuckySheet(xlsxFile);} else {createLuckySheet(file);}};reader.readAsArrayBuffer(file);}const handleExcelForXlsAndCsvType = (file, fileName) => {const reader = new FileReader();reader.onload = function (event) {const data = new Uint8Array(event.target.result);const workbook = XLSX.read(data, {type: 'array', cellDates: true});let maxRowCountFromExcel = getMaxRowCountFromExcel(workbook);if (maxRowCountFromExcel > _maxRowCount) {console.log("excel file has too many blank row..", maxRowCountFromExcel);handleBlankRowForExcelWithTooManyBlankRow(workbook);}const xlsxFile = toXlsxExcelFile(workbook, fileName);handleExcelForXlsxType(xlsxFile, fileName);};reader.readAsArrayBuffer(file);}const createLuckySheet = (file) => {const reader = new FileReader();reader.onload = (event => {postMessage({fileArrayBuffer: event.target.result ,fileName: file.name,})});reader.readAsArrayBuffer(file);}const getMaxRowCountFromExcel = (workbook) => {let maxRowCount = 0;if (workbook.SheetNames == null || workbook.SheetNames.length === 0) {return maxRowCount;}workbook.SheetNames.forEach(sheetName => {const worksheet = workbook.Sheets[sheetName];if (worksheet['!ref'] === undefined) {return;}const range = XLSX.utils.decode_range(worksheet['!ref']);maxRowCount = maxRowCount + range.e.r;});return maxRowCount;}const reduceBlankRow = (row, range, worksheet) => {while (row > range.s.r) {let allEmpty = true;for (let col = range.s.c; col <= range.e.c; col++) {const cell_ref = XLSX.utils.encode_cell({c: col, r: row});if (worksheet[cell_ref]) {allEmpty = false;break;}}if (allEmpty) {row--;} else {break;}}range.e.r = row;worksheet['!ref'] = XLSX.utils.encode_range(range.s, range.e);}const handleBlankRowForExcelWithTooManyBlankRow = (workbook) => {if (workbook.SheetNames == null || workbook.SheetNames.length === 0) {return;}workbook.SheetNames.forEach(sheetName => {const worksheet = workbook.Sheets[sheetName];if (worksheet['!ref'] === undefined) {return;}const range = XLSX.utils.decode_range(worksheet['!ref']);let row = range.e.r;reduceBlankRow(row, range, worksheet);});}const toXlsxExcelFile = (workbook, fileName) => {const newWorkbook = XLSX.write(workbook, {bookType: 'xlsx', type: 'binary'});const data = new Uint8Array(newWorkbook.length);for (let i = 0; i < newWorkbook.length; i++) {data[i] = newWorkbook.charCodeAt(i);}return new File([data], fileName, {type: _xlsxType});}self.addEventListener('error', function (event) {console.log("test....................", event)});</div>
<script src="https://cdn.jsdelivr.net/npm/xlsx/dist/xlsx.full.min.js"></script><link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/plugins/css/pluginsCss.css'/>
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/plugins/plugins.css'/>
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/css/luckysheet.css'/>
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/assets/iconfont/iconfont.css'/>
<script src="https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/plugins/js/plugin.js"></script>
<script src="https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/luckysheet.umd.js"></script><script src="https://cdn.jsdelivr.net/npm/luckyexcel/dist/luckyexcel.umd.js"></script><script>const createLuckySheet = (exportJson) => {console.log(exportJson)window.luckysheet.destroy();const options = {container: 'luckysheet',data: exportJson.sheets, // title: exportJson.info.name,// userInfo: exportJson.info.name.creator,column: 10,row: 10,showinfobar: false,sheetFormulaBar: true,showConfigWindowResize: false};window.luckysheet.create(options);}const createLuckySheetByFileArrayBuffer = (arrayBuffer, fileName) => {const xlsxTtpe = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';const file = new File([arrayBuffer], fileName, {type: xlsxTtpe});LuckyExcel.transformExcelToLucky(file, function (exportJson, luckysheetfile) {createLuckySheet(exportJson);});}var blob = new Blob([document.querySelector('#worker').textContent]);var url = window.URL.createObjectURL(blob);var worker = new Worker(url);worker.addEventListener('message', (event) => {const data = event.datacreateLuckySheetByFileArrayBuffer(data.fileArrayBuffer, data.fileName)})document.getElementById('fileUpload').addEventListener('change', function (e) {const file = e.target.files[0];const reader = new FileReader();reader.onload = (event => {worker.postMessage({data: event.target.result,fileName: file.name})});reader.readAsArrayBuffer(file);});</script></body>
</html>

效果

在这里插入图片描述

参考

https://juejin.cn/post/7211805251216031801
https://segmentfault.com/a/1190000043720845
https://juejin.cn/post/7232524757525659708
https://blog.csdn.net/q2qwert/article/details/130908294
https://www.cnblogs.com/ajaemp/p/12880847.html
https://blog.csdn.net/weixin_40775791/article/details/135409716
https://blog.csdn.net/u013113491/article/details/129106671

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/372703.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

九、Linux二进制安装ElasticSearch集群

目录 九、Linux二进制安装ElasticSearch集群1 下载2 安装前准备(单机&#xff0c;集群每台机器都需要配置)3 ElasticSearch单机&#xff08;7.16.2&#xff09;4 ElasticSearch集群&#xff08;8.14.2&#xff09;4.1 解压文件&#xff08;先将下载文件放到/opt下&#xff09;4…

生物素标记降钙素Biotin-α-CGRP, rat 中间体

生物素标记降钙素Biotin-α-CGRP, rat 中间体是一种特定的生物化学试剂&#xff0c;主要用于科学研究领域。以下是对该产品的详细介绍&#xff1a; 一、基本信息 产品名称&#xff1a;生物素标记降钙素Biotin-α-CGRP, rat 中间体 英文名称&#xff1a;Biotin-α-CGRP, rat 纯度…

AI转绘_animatediff-cli-prompt-travel

这个工具有两种主要模式&#xff1a;它可以直接通过提示创建视频&#xff0c;或者它可以对现有视频进行风格化。还有方法可以提高视频的分辨率。 正如工具名称所示&#xff0c;它的一个主要特点是"提示旅行"。这意味着你可以例如使用特定的提示用于前20帧&#xff0…

koa + http-proxy-middleware 搭建一个带转发的静态服务器

背景 由于工作中碰到写普通页面&#xff08;未使用脚手架&#xff09;&#xff0c;需要发起接口请求&#xff0c;但普通页面又无法对接口发起正常请求&#xff0c;故编写一个Koa搭建的带转发功能的静态服务器。 起步 新建一个文件夹&#xff0c;在文件夹下打开 cmd 或者 git …

手写简单实现IOC

这个小demo是利用反射从最基础一步一步模拟实现了IOC的功能,所有的代码基本都给出了注释,方便大家阅读. 目录结构&#xff1a; 这里需要导入一下junit依赖 <!-- junit测试 --><dependency><groupId>junit</groupId><artifactId>junit</artif…

解决vite 断点调试定位不准确问题

问题&#xff1a;vite构建时&#xff0c;控制台报错行数等信息定位不准确或debugger断点调试定位不准确 解决&#xff1a;F12后打开设置面板&#xff0c;把“JavaScript源代码映射”去掉可临时解决&#xff0c;如需永久解决需升级vite到最新版 还有一种&#xff1a; 参考&…

Unity--射线检测--RayCast

Unity–射线检测–RayCast 1.射线检测的含义 射线检测,根据名称而言,使用一条射线来检测是击中了某个物体/多个物体 射线检测的包含两个部分: 射线和检测 2.射线检测可以用在哪些地方 射击游戏&#xff1a; 玩家的瞄准和射击&#xff1a;检测玩家视线是否与敌人或其他目标…

JRE、JVM、JDK分别是什么。

JDK JDK的英文全称是Java Development Kit。JDK是用于制作程序和Java应用程序的软件开发环境。JDK 是 Java 开发工具包&#xff0c;它是 Java 开发者用来编写、编译、调试和运行 Java 程序的集合。JDK 包括了 Java 编译器&#xff08;javac&#xff09;、Java 运行时环境&…

首席数据官CDO证书报考指南:方式、流程、适考人群与考试难度

在信息泛滥的今天&#xff0c;数据已转变为企业不可或缺的宝贵资源。 面对海量的信息&#xff0c;如何提炼出价值&#xff0c;为企业带来实质性的收益&#xff1f;首席数据官&#xff08;CDO&#xff09;认证的出现正是为了满足这一需求&#xff0c;它不仅是个人专业能力的体现…

【网络安全】这些网络安全知识请牢记!

随着社会信息化深入发展&#xff0c;互联网对人类文明进步将发挥更大促进作用&#xff0c;但与此同时&#xff0c;互联网领域的问题也日益凸显&#xff0c;网络犯罪、网络攻击等时有发生&#xff0c;网络安全与每个人都息息相关&#xff0c;下面一起来了解网络安全知识吧&#…

如何降低电力运维成本,为企业的运维增效、能源数字化和节能降耗提供数据支持?

【电力运维存在问题】 随着全球范围内城镇化、数字化和工业化进程的加速与电力政策的改革&#xff0c;企业用电需求不断攀升&#xff0c;极大冲击了电力企业传统的运维模式&#xff0c;暴露出许多的问题&#xff1a; 变电所较为分散&#xff0c;缺乏统一管理&#xff1b;站内…

从数据仓库到数据湖(下):热门的数据湖开源框架

文章目录 一、前言二、Delta Lake三、Apache Hudi四、Apache Iceberg五、Apache Paimon六、对比七、笔者观点八、总结八、参考资料 一、前言 在上一篇从数据仓库到数据湖(上)&#xff1a;数据湖导论文章中&#xff0c;我们简单讲述了数据湖的起源、使用原因及其本质。本篇文章…

Linux:Ubuntu18.04下开机自启动QT图形化界面

Linux&#xff1a;Ubuntu18.04下开机自启动QT图形化界面 Chapter1 Linux&#xff1a;Ubuntu18.04下开机自启动QT图形化界面一、创建rc.local文件二、建立rc-local.service文件三、启动服务查看启动状态四、重启 Chapter2 将QT应用作为开机自启动&#xff08;Linux系统&#xff…

Simulink生成代码时端口名称乱码问题

写在最前&#xff1a; 在使用Simulink生成代码时发现端口名称与模型中定义的输如输出端口名称不一致&#xff0c;代码生成的端口名称为随机字符名称。 在生成的H文件中发现&#xff0c;端口定义的结构体名称与模型中实际定义的名称不符。 模型中的定义 检查后发现&#xff0c…

【已解决】腾讯云安装了redis,但是本地访问不到,连接不上

汇总了我踩过的所有问题。 查看配置文件redis.conf 1、把bind 127.0.0.1给注释掉&#xff08;前面加个#就是&#xff09;或者改成bind 0.0.0.0&#xff0c;因为刚下载时它是默认只让本地访问。&#xff08;linux查找文档里的内容可以输入/后面加需要匹配的内容&#xff0c;然后…

基于STM主题模型的主题提取分析-完整代码数据

直接看结果: 代码: import re from collections import defaultdict import random import matplotlib.pyplot as plt import numpy as npimport pandas as pd import numpy as np import re from sklearn.feature_extraction.text import CountVectorizer from nltk.corpus…

如何在 Ubuntu上搭建 LAMP

远程登录 Ubuntu系统环境 ssh (User)(IP) # 比如&#xff1a;ssh lennlouis192.168.207.128 为安全起见&#xff0c;建议你使用 root 登录 VPS 后创建一个具有 sudo 权限的帐号。 安装和配置 Apache 2 Apache Http Server 是一个开源的&#xff0c;非常流行&#xff0c;使用…

【Dell R730 折腾记录】风扇调速--在 Ubuntu 系统上开机自启动并每隔30分钟执行一次风扇定速脚本

前段时间升级了一下机柜里的服务器&#xff0c;替换掉了一台旧的 Dell 服务器&#xff0c;换上了这台 R730。但是无奈于噪音的袭扰&#xff0c;搁置了一段时间。我在这台机器上目前安装了一块 Intel Xeon E5-2630v3 芯片以及一张改过散热的 NVIDIA Tesla P4 计算卡。结果就是散…

关于ORACLE单例数据库中的logfile的切换、删除以及添加

一、有关logfile的状态解释 UNUSED&#xff1a; 尚未记录change的空白group&#xff08;一般会出现在loggroup刚刚被添加&#xff0c;或者刚刚使用了reset logs打开数据库&#xff0c;或者使用clear logfile后&#xff09; CURRENT: 当前正在被LGWR使用的gro…

K8S 上部署大数据相关组件

文章目录 一、前言二、Redis 一、前言 Artifact Hub 是一个专注于云原生应用的集中式搜索和发布平台。它旨在简化开发者在 CNCF&#xff08;Cloud Native Computing Foundation&#xff09;项目中寻找、安装和分享包与配置的过程。用户可以通过这个平台方便地发现、安装各类云原…