文章目錄
- 功能簡介
- 簡單代碼實現
- 效果
- 參考
功能簡介
- 通過LuckyExcel的transformExcelToLucky方法， 我們可以把一個文件直接轉成LuckySheet需要的json字符串， 之后我們就可以用LuckySheet預覽excel
- LuckyExcel只能解析xlsx格式的excel文件，因此對于xls和csv的格式，我們需要通過XLSX來轉化成xlsx格式，但在轉化過程中會丟失樣式
- 對于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>效果
參考
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
)
)
