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中存在很多的空白行，在显示的时候可能会出现卡顿，所以我们需要将过多的空白行移除

    简单代码实现

    
    
      
      Excel File Upload and Preview with Luckysheet
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
     
     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 
          // 获取当前单元格的引用
            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  
    web worker 版本
     
    
    
      
      Excel File Upload and Preview with Luckysheet
    
    
    
    
    
    
    
      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  {
      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 
    
    
    
    
    
    
    
    
    
      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.data
        createLuckySheetByFileArrayBuffer(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);
      });
    
    
    
     
    效果
     
     
    参考
     
    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