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

文章目录

- 功能简介
- 简单代码实现
- 效果
- 参考

功能简介

通过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);
  }

/ 创建Luckysheet
  const createLuckySheet = (file) => {
  // 销毁已存在的Luckysheet
    window.luckysheet.destroy();
     // 将Excel文件转换为Luckysheet的json
    LuckyExcel.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
      };
      // 创建Luckysheet
      window.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

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

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！