前端使用xlsx庫匯出帶有樣式的excel檔案

2022-08-05 22:00:40

需求分析

最近遇到一個需求：前端匯出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的資料請關注it145.com其它相關文章！