佛山专业做网站的公司,一起学网站培训心得,青岛代理记账多少钱,贵阳网站建设1685文章目录 功能简介简单代码实现效果参考 功能简介
通过LuckyExcel的transformExcelToLucky方法#xff0c; 我们可以把一个文件直接转成LuckySheet需要的json字符串#xff0c; 之后我们就可以用LuckySheet预览excelLuckyExcel只能解析xlsx格式的excel文件#xff0c;因此对… 文章目录 功能简介简单代码实现效果参考 功能简介
通过LuckyExcel的transformExcelToLucky方法 我们可以把一个文件直接转成LuckySheet需要的json字符串 之后我们就可以用LuckySheet预览excelLuckyExcel只能解析xlsx格式的excel文件因此对于xls和csv的格式我们需要通过XLSX来转化成xlsx格式但在转化过程中会丢失样式对于excel中存在很多的空白行在显示的时候可能会出现卡顿所以我们需要将过多的空白行移除
简单代码实现
!DOCTYPE html
html langen
headmeta charsetUTF-8titleExcel File Upload and Preview with Luckysheet/title
/head
body!-- 文件上传控件 --
input typefile idfileUpload/!-- Luckysheet 的容器 --
div idluckysheet styleposition: relative; width: 100%; height: 500px;/div
script srchttps://cdn.jsdelivr.net/npm/xlsx/dist/xlsx.full.min.js/scriptlink relstylesheet hrefhttps://cdn.jsdelivr.net/npm/luckysheetlatest/dist/plugins/css/pluginsCss.css/
link relstylesheet hrefhttps://cdn.jsdelivr.net/npm/luckysheetlatest/dist/plugins/plugins.css/
link relstylesheet hrefhttps://cdn.jsdelivr.net/npm/luckysheetlatest/dist/css/luckysheet.css/
link relstylesheet hrefhttps://cdn.jsdelivr.net/npm/luckysheetlatest/dist/assets/iconfont/iconfont.css/
script srchttps://cdn.jsdelivr.net/npm/luckysheetlatest/dist/plugins/js/plugin.js/script
script srchttps://cdn.jsdelivr.net/npm/luckysheetlatest/dist/luckysheet.umd.js/scriptscript srchttps://cdn.jsdelivr.net/npm/luckyexcel/dist/luckyexcel.umd.js/scriptscriptconst _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
效果 参考
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
