前端导出Excel文件

安装插件：npm install exceljs  npm install file-saver

表头集合：

//单元格垂直 居中    numFmt: '@',文本格式
 let style = {
            alignment: {
              horizontal: 'center',
              vertical: 'center',
            },
            numFmt: '@',
          };
//表头字段
let headerList = [
              {header: '序号', key: 'sequence', width: 5, style: style},
              {header: '优先级', key: 'priority', width: 10, style: style},
              {
                header: '样本条形码',
                key: 'barCode',
                width: 10,
                style: style,
              },
              {
                header: '家系号',
                key: 'familyCode',
                width: 10,
                style: style,
              },
              {header: '检测项目', key: 'projectName', width: 20, style: style},
              {header: '项目编号', key: 'projectNo', width: 20, style: style},
              {header: '样本选用类型', key: 'selectionType', width: 15, style: style},
              {header: '分子标签', key: 'molecularTag', width: 15, style: style},
              {header: 'DNA浓度测量法', key: 'concentrationQuantificationMethod', width: 15, style: style},
              {header: 'DNA浓度', key: 'concentration', width: 10, style: style},
              {header: 'OD260_280', key: 'odEighty', width: 10, style: style},
              {header: 'OD260_230', key: 'odThirty', width: 10, style: style},
              {header: '洗脱体积', key: 'elutionVolume', width: 10, style: style},
              {header: 'DNA库位置', key: 'locationDna', width: 15, style: style},
              {header: '归属订单', key: 'orderNo', width: 25, style: style},
              {header: 'id', key: 'id'},]
//个别特殊的表头
  let sheetView = ['序号', '分子标签', 'DNA浓度测量法', 'DNA浓度', '洗脱体积', 'DNA库位置'];

 表格数据集合：

//表格数据整合

let list = result.map((item) => ({
              sequence: item.sequence,
              priority: item.sample.priority,
              barCode: item.sample.barCode,
              familyCode: item.sample.familyCode,
              projectName: item.saleItem.projectName,
              projectNo: item.saleItem.projectNo,
              selectionType: item.selectionType,
              molecularTag: item.molecularTag,
              concentrationQuantificationMethod: item.concentrationQuantificationMethod,
              concentration: item.concentration,
              odEighty: item.odEighty,
              odThirty: item.odThirty,
              elutionVolume: item.elutionVolume,
              locationDna: item.locationDna,
              orderNo: item.orderNo,
              id: item.id,
            }));
          }
//导出
  exported() {
       //表格需要下拉的数据
        let formulae = {concentrationQuantificationMethod: ['"Qubit,Onedrop"']};
        let val = [];//拿到特殊表头所在的下标
        headerList.forEach((_, index) => {
          if (sheetView.includes(headerList[index].header)) {
            let idx = index + 1;
            val.push(idx);
          }
        });
          let selectNum = [];//拿到需要下拉所在列的列数(第几列)
          let selectList = ['concentrationQuantificationMethod'];//声明下拉所在列的key值
          headerList.forEach((_, index) => {
            if (selectList.includes(headerList[index].key)) {
              let idx = index + 1;
              selectNum.push(idx);
            }
          });
//调用封装的函数
          exportExcel('表的name',headerList, list, val, selectNum, formulae);
        
      },

 封装函数：

//封装方法
// name:表 名称;columns：表头字段,list：数据集合,val：个别表头索引集合,(num：某一列,formulae:下拉数据)非必要不传
const ExcelJS = require('exceljs');
const FileSaver = require('file-saver');
export async function exportExcel(name, columns, list, val, num, formulae) {
  const wb = new ExcelJS.Workbook();
  const Sheet1 = wb.addWorksheet(name);
  Sheet1.columns = columns;
  Sheet1.addRows(list);
  // 全部表头背景色
  columns.forEach((_, index) => {
    Sheet1.getRow(1).getCell(index + 1).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: {argb: 'ff99ffff'},
    };
  });
  // 个别表头背景色并覆盖;
  val.forEach((item) => {
    Sheet1.getRow(1).getCell(Number(item)).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: {argb: 'FFFF8040'},
    };
  });

  let cum = columns.map((item) => item.header).indexOf('id') + 1;
  if (cum && cum > 0) {
    Sheet1.getColumn(cum).hidden = true;
    Sheet1.getColumn(cum).width = 0;
  }

  if (num && num.length > 0) {
    new Array(1000).fill(0).forEach((_, idx) => {
      const row = idx + 2;
      num.forEach((item, index) => {
        let keyName = columns[item - 1].key;
        Sheet1.getCell(row, Number(item)).dataValidation = {
          type: 'list',
          formulae: formulae[keyName],
          showDropDown: true, // 设置显示下拉框
          showErrorMessage: true,
        };
      });
    });
  }
  const buffer = await wb.xlsx.writeBuffer();
  FileSaver.saveAs(new Blob([buffer]), `${name}.xlsx`);
}