前端使用xlsx庫導出帶有樣式的excel文件

Posted on by

需求分析

最近遇到一個需求：前端導出excel文件，其中有部分數據用戶不能操作，部分列數據可以篩選，並且存在前一列的數據值會影響後一列數據值的輸入范圍的情況。

需要導出的前端表格如上圖所示，其中：

  • Group、Type、Region可篩選
  • 紅色框內的數據用戶不可操作，綠色框內用戶可以操作
  • 當Type的值為BOOL時，Region的有效輸入為：["Holding Register","Input Register"],否則為：["Coil","Discrete Input"]
  • Address的輸入范圍為：[0,65535]

項目使用的是React + AntD

常用的庫

在這個需求出來之前，前端導入導出excel文件時我使用的是xlsx這個庫。但是，如果想要修改excel表格樣式的話，是需要使用收費的專業版本。帶著開源第一，絕不花錢的基本原則，本人就找到瞭ExcleJS這個庫。

ExcleJS

ExcleJS不僅完全開源，還配備著中文文檔這可真的是用著放心也開心！

具體實現

安裝：

npm install exceljs
npm install file-saver

創建workbook，添加名為Demo的sheet，設置默認行高為20，設置列（表頭）；

添加行信息（allData前端頁面表格中的數據）；

最後給表頭添加顏色。

    // 創建工作簿
    const workbook = new ExcelJs.Workbook();
    // 添加sheet
    const worksheet = workbook.addWorksheet('Demo');
    // 設置 sheet 的默認行高
    worksheet.properties.defaultRowHeight = 20;
    // 設置列
    worksheet.columns = [
      { header: 'Index', key: 'index', width: 10 },
      { header: 'Name', key: 'name', width: 25 },
      { header: 'Type', key: 'group', width: 25, outlineLevel: 1 },
      { header: 'Group', key: 'type', width: 25, outlineLevel: 1 },
      { header: 'Region', key: 'modbusRegion', width: 25, outlineLevel: 1 },
      { header: 'Address', key: 'modbusAddress', width: 25, outlineLevel: 1 },
    ];
    // 添加行
    worksheet.addRows(allData);
    // 給表頭添加背景色
    let headerRow = worksheet.getRow(1);
    headerRow.eachCell((cell) => {
      cell.fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: {argb: 'dde0e7'},
      }
    })
  • 將自動篩選器設置為從 A2 到 F1 （Group、Type、Region）
    // 將自動篩選器設置為從 A2 到 F1
    worksheet.autoFilter = {
      from: 'C1',
      to: 'E1',
    }
  • 鎖定整個excel表格，可篩選但不能選中鎖定的單元格
    // 鎖定工資表
    await worksheet.protect('the-password', 
                             {
                              autoFilter:true,
                              selectLockedCells:false,
                              });
  • 通過循環判斷，哪些單元格可以被用戶操作，並且判斷該單元格的輸入限制是什麼
   const allData = [...this.state.dataSource]
   let length = allData.length
   for(let i = 0 ;i < length; i++){
      // Region的輸入范圍
      let coilArr = ['"Coil,Discrete Input"']
      let registerArr = ['"Holding Register,Input Register"']
      let listArr = []
      if(allData[i].type === 'BOOL'){
        listArr = coilArr
      }
      else{
        listArr = registerArr
      }
      // 可編輯的單元格在E、F中
      worksheet.getCell(`E${i+2}`).protection = {
        locked: false,
      };
      // Region的輸入校驗
      worksheet.getCell(`E${i+2}`).dataValidation = {
        type: 'list',
        allowBlank: true,
        formulae: listArr,
        showErrorMessage: true,
        errorTitle: '非法輸入',
        error: '取值范圍為:'+listArr
      };
      worksheet.getCell(`F${i+2}`).protection = {
        locked: false,
      };
      // Address的輸入校驗
      worksheet.getCell(`F${i+2}`).dataValidation = {
        type: 'whole',
        operator: 'between',
        allowBlank: true,
        showErrorMessage: true,
        formulae: [0,65535],
        errorTitle: '非法輸入',
        error: '取值范圍為:[0,65535]'
      };
    }

以上的代碼中，worksheet.getCell(E${i+2}).dataValidation是進行單元格數據驗證的函數，具體的使用可參考官方文檔。

  • 導出名為"xlsx-demo.xlsx"的excel文件
    // 導出excel
    this.saveWorkbook(workbook, 'xlsx-demo.xlsx');

結果展示

  • Group、Type、Region可篩選（✅）

  • 紅色框內的數據，用戶不可操作，藍色框內用戶可以操作（✅）

  • 當Type的值為BOOL時，Region的有效輸入為：["Holding Register","Input Register"],否則為：["Coil","Discrete Input"]（✅）

用戶輸入錯誤給出錯誤提醒，並且不保存錯誤數據。

  • Address的輸入范圍為：[0,65535]（✅）

用戶輸入錯誤給出錯誤提醒，並且不保存錯誤數據。

整個函數展示

  // 導出xls
  exportXLS = async () =>{
    const allData = [...this.state.dataSource]
    let length = allData.length
    // 創建工作簿
    const workbook = new ExcelJs.Workbook();
    // 添加sheet
    const worksheet = workbook.addWorksheet('demo');
    // 設置 sheet 的默認行高
    worksheet.properties.defaultRowHeight = 20;
    // 設置列
    worksheet.columns = [
      { header: 'Index', key: 'index', width: 10 },
      { header: 'Name', key: 'name', width: 25 },
      { header: 'Group', key: 'group', width: 25, outlineLevel: 1 },
      { header: 'Type', key: 'type', width: 25, outlineLevel: 1 },
      { header: 'Region', key: 'modbusRegion', width: 25, outlineLevel: 1 },
      { header: 'Address', key: 'modbusAddress', width: 25, outlineLevel: 1 },
    ];
    // 添加行
    worksheet.addRows(allData);
    // 給表頭添加背景色
    let headerRow = worksheet.getRow(1);
    // 通過 cell 設置背景色，更精準
    headerRow.eachCell((cell) => {
      cell.fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: {argb: 'dde0e7'},
      }
    })
    // 將自動篩選器設置為從 C1 到 F1
    worksheet.autoFilter = {
      from: 'C1',
      to: 'E1',
    }
    // 鎖定工資表
    await worksheet.protect('the-password', 
                             {
                              autoFilter:true,
                              selectLockedCells:false,
                              });
    // 判斷哪些單元格可以被用戶操作，並且判斷該單元格的輸入限制是什麼
    for(let i = 0 ;i < length; i++){
      // 根據不同類型選擇篩選的框
      let coilArr = ['"Coil,Discrete Input"']
      let registerArr = ['"Holding Register,Input Register"']
      let listArr = []
      if(allData[i].type === 'BOOL'){
        listArr = coilArr
      }
      else{
        listArr = registerArr
      }
      // 可編輯的單元格在E、F中
      worksheet.getCell(`E${i+2}`).protection = {
        locked: false,
      };
      worksheet.getCell(`E${i+2}`).dataValidation = {
        type: 'list',
        allowBlank: true,
        formulae: listArr,
        showErrorMessage: true,
        errorTitle: '非法輸入',
        error: '取值范圍為:'+listArr
      };
      worksheet.getCell(`F${i+2}`).protection = {
        locked: false,
      };
      worksheet.getCell(`F${i+2}`).dataValidation = {
        type: 'whole',
        operator: 'between',
        allowBlank: true,
        showErrorMessage: true,
        formulae: [0,65535],
        errorTitle: '非法輸入',
        error: '取值范圍為:[0,65535]'
      };
    }
    // 導出excel
    this.saveWorkbook(workbook, 'xlsx-demo.xlsx');
  }

最後

ExcelJS 功能很強大，如合並單元格、合並行和列、修改單元格的樣式、設置頁眉頁腳、操作視圖、添加公式、使用富文本等功能都是可以實現的。

官方文檔十分詳細，大傢有需求的話直接看官方文檔。

以上就是前端使用xlsx庫導出帶有樣式的excel文件的詳細內容，更多關於前端xlsx庫導出excel的資料請關註WalkonNet其它相關文章！

推薦閱讀：