ExcelJS 前端的excel导入导出

读取，操作并写入电子表格数据和样式到 XLSX 和 JSON 文件。一个 Excel 电子表格文件逆向工程项目。

安装

  npm install exceljs

读取excel表格

  <input  accept=".xlsx"  type="file" ref={refInputFile}  onChange={importExcel}/>

const importExcel = (e) => {
  const reader = new FileReader();
  reader.onload = async(evt) => {
    const lines = await readExcel(evt.target.result);
    refInputFile.current.value=''//读取后清空input file
  };
  reader.readAsBinaryString(e.target.files[0]);
};

const readExcel = (file) => {
  return new Promise(async(resolve,reject)=>{
    try{
      //
      const workbook = new Excel.Workbook();
      //await workbook.xlsx.readFile(filename);从文件读取
      //await workbook.xlsx.read(stream);从流读取
      //await workbook.xlsx.load(data);从 buffer 加载
      const result = await workbook.xlsx.load(file);
      // 按 name 提取工作表 workbook.getWorksheet('My Sheet');
      // 按 id 提取工作表 workbook.getWorksheet(1);
      const worksheet = result.getWorksheet();
      const lines = []
      //遍历工作表中具有值的所有行
      worksheet.eachRow(function(row) {
        const [empty,...line] = row.values
        if(line.filter(f=>f).length>0){
          lines.push(line.map(m=>m?m.toString():''))
        }
      });
      resolve(lines)
    }catch(e){
      reject(e)
    }
  })
};

写出excel

const columns = [
  { header: "序号", key: "num",style:{numFmt:"@"}},
  { header: "员工姓名", key: "personName",width:15,style:{numFmt:"@"}},
  { header: "证件类型", key: "idcardType",width:15,style:{numFmt:"@"}},
  { header: "证件号码", key: "idcard",width:25,style:{numFmt:"@"}},
  { header: "员工银行卡账号（仅支持本人银行卡号，非本人的银行卡或存折将导致打款失败）", key: "userBankNo",width:35,style:{numFmt:"@"}},
  { header: "手机号", key: "userPhone",width:25,style:{numFmt:"@"}}
]
/**
 * 
 * @param {*} data 
 * @param {error|write} columns 
 * @param {*} title 
 */
const writeExcel = async (data,columns,title) => {
  return new Promise(async(resolve, reject)=>{
    try{
      const workbook = new Excel.Workbook();
      workbook.creator = "test";
      let sheet = workbook.addWorksheet("报表");
      sheet.columns = columns;
      sheet.addRows(data);
      //配置表格枚举
      for (let index = 2; index < data.length+1000; index++) {
        sheet.getCell(`C${index}`).dataValidation={
          type: 'list',
          allowBlank: true,
          showErrorMessage:true,
          showInputMessage: true,
          errorStyle:'error',
          formulae: ['"身份证,港澳台居住证,护照,其他"'],
          prompt: '证件类型只可以是"身份证,港澳台居住证,护照,其他"',
          error:'证件类型只可以是"身份证,港澳台居住证,护照,其他"',
        }
      }
      const buffer = await workbook.xlsx.writeBuffer();
      FileSaver.saveAs(new Blob([buffer]), `${title}.xlsx`);
      resolve(true)
    }catch(e){
      reject('导出失败请重试')
    }

  })
};

更多详细内容可查看官网
https://github.com/exceljs/exceljs/blob/HEAD/README_zh.md

最后编辑于：2021.09.14 10:29:16