全网最全luckysheet协同编辑导出功能，包含单元格样式、超链接、数据验证、条件格式、图片导出、批注、数据透视图等等复杂功能的导出

前言：

        最近公司准备研究使用luckySheet，为了开源节流，在开掉了前端大佬的情况下，这苦差事儿落在了我这个前端菜鸟身上。无奈，只能打开luckysheet官网，仔细研读文档，并照着敲了个在线协同的demo。本以为这事儿算是结束，谁知，公司来了新需求，要做导入导出功能，又是一番查找资料后，决定先做导出，哈哈哈，别问为什么，问就是因为导出相关的文档丰富，可操作性强。

废话结束，进入正题：

        1. 在官网找到了导出代码，但只有基础的功能，标题所描述的复杂功能的导出并没有实现，于是我就拿下来进行二次开发，链接：使用exceljs导出luckysheet表格_excel 转 luckysheet-CSDN博客

        2. 用到导出库excejs和file-saver 

//安装exceljs
npm i exceljs --save
//安装文件保存的库
npm i file-saver --save

        3. 二次开发

        export.js

const Excel = require('exceljs')
import FileSaver from 'file-saver'

import { setConditions } from './export/setConditions'
import { setDataValidation } from './export/setDataValidation'
import { createCellRange } from './export/utils'

export var exportExcel = function(luckysheet, value) {
  console.log(luckysheet,'sheet数组')
  console.log(luckysheet[0].dataVerification,'sheet1中具体操作')
  // 参数为luckysheet.getluckysheetfile()获取的对象
  // 1.创建工作簿，可以为工作簿添加属性
  const workbook = new Excel.Workbook()
  // 2.创建表格，第二个参数可以配置创建什么样的工作表
  if (Object.prototype.toString.call(luckysheet) === '[object Object]') {
    luckysheet = [luckysheet]
  }
  //遍历sheet，将luckysheet的sheet转换成excel的sheet
  luckysheet.forEach(async function(thesheet) {
    // thesheet为sheet对象数据
    if (thesheet.data.length === 0) return  true
    const worksheet = workbook.addWorksheet(thesheet.name)
    const merge = (thesheet.config && thesheet.config.merge) || {}
    const borderInfo = (thesheet.config && thesheet.config.borderInfo) || {}
    // 3.设置导出操作
    setStyleAndValue(thesheet.data, worksheet)
    setMerge(merge, worksheet)
    setBorder(borderInfo, worksheet)
    setImages(thesheet, worksheet, workbook);
    setHyperlink(thesheet.hyperlink,worksheet)
    setFrozen(thesheet.frozen,worksheet)
    setConditions(thesheet.luckysheet_conditionformat_save,worksheet)
    setFilter(thesheet.filter_select,worksheet)
    setDataValidation(thesheet.dataVerification,worksheet)
    //开启显示数据透视表
    if(thesheet.isPivotTable){
      worksheet.pivotTables = true
      let rmax = 0
      let cmax = 0
      //得到行与列的最大值
      thesheet.celldata.forEach(itemCell=>{
        if(rmax<itemCell.r) rmax = itemCell.r
        if(cmax<itemCell.c) cmax = itemCell.c
      })
      // 循环遍历添加边框
      for(let i=0;i<=rmax;i++){
        for(let j=0;j<=cmax;j++){
          // 添加边框
          worksheet.getCell(i+1,j+1).border = {
            top: {style:'thin'},
            left: {style:'thin'},
            bottom: {style:'thin'},
            right: {style:'thin'}
          }
        }
      }
    }
    return true
  })

  // return
  // 4.写入 buffer
  const buffer = workbook.xlsx.writeBuffer().then(data => {
    const blob = new Blob([data], {
      type: 'application/vnd.ms-excel;charset=utf-8'
    })
    //保存文件
    FileSaver.saveAs(blob, `${value}.xlsx`)
  })
  return buffer
}
//设置合并数据
var setMerge = function(luckyMerge = {}, worksheet) {
  const mergearr = Object.values(luckyMerge)
  mergearr.forEach(function(elem) {
    // elem格式：{r: 0, c: 0, rs: 1, cs: 2}
    // 按开始行，开始列，结束行，结束列合并（相当于 K10:M12）
    worksheet.mergeCells(
      elem.r + 1,
      elem.c + 1,
      elem.r + elem.rs,
      elem.c + elem.cs
    )
  })
}
//重新设置边框
var setBorder = function(luckyBorderInfo, worksheet) {
  if (!Array.isArray(luckyBorderInfo)) return
  luckyBorderInfo.forEach(function(elem) {
    // 现在只兼容到borderType 为range的情况
    if (elem.rangeType === 'range') {
      let border = borderConvert(elem.borderType, elem.style, elem.color)
      let rang = elem.range[0]
      let row = rang.row
      let column = rang.column
      for (let i = row[0] + 1; i < row[1] + 2; i++) {
        for (let y = column[0] + 1; y < column[1] + 2; y++) {
          worksheet.getCell(i, y).border = border
        }
      }
    }
    if (elem.rangeType === 'cell') {
      // col_index: 2
      // row_index: 1
      // b: {
      //   color: '#d0d4e3'
      //   style: 1
      // }
      const { col_index, row_index } = elem.value
      const borderData = Object.assign({}, elem.value)
      delete borderData.col_index
      delete borderData.row_index
      let border = addborderToCell(borderData, row_index, col_index)
      worksheet.getCell(row_index + 1, col_index + 1).border = border
    }
  })
}
// 设置单元格样式和值
var setStyleAndValue = function (cellArr, worksheet) {
  if (!Array.isArray(cellArr)) return;

  cellArr.forEach(function (row, rowid) {
    const dbrow = worksheet.getRow(rowid + 1);
    //设置单元格行高,默认乘以0.8倍
    dbrow.height = luckysheet.getRowHeight([rowid])[rowid] * 0.8;
    row.every(function (cell, columnid) {
      if (!cell) return true;
      if (rowid == 0) {
        const dobCol = worksheet.getColumn(columnid + 1);
        //设置单元格列宽除以8
        dobCol.width = luckysheet.getColumnWidth([columnid])[columnid] / 8;
      }
      let fill = fillConvert(cell.bg);
      let font = fontConvert(
        cell.ff||'Times New Roman',
        cell.fc,
        cell.bl,
        cell.it,
        cell.fs,
        cell.cl,
        cell.ul
      );
      let alignment = alignmentConvert(cell.vt, cell.ht, cell.tb, cell.tr);
      let value;

      var v = "";
      if (cell.ct && cell.ct.t == "inlineStr") {
        var s = cell.ct.s;
        s.forEach(function (val, num) {
          v += val.v;
        });
      } else {
        //导出后取显示值
        v = cell.m;
      }
      if (cell.f) {
        value = { formula: cell.f, result: v };
      } else {
        value = v;
      }
      let target = worksheet.getCell(rowid + 1, columnid + 1);
      //添加批注
      if(cell.ps){
        let ps = cell.ps
        target.note = ps.value
      }
      //单元格填充
      target.fill = fill;
      //单元格字体
      target.font = font;
      target.alignment = alignment;
      target.value = value;
      return true;
    });
  });
};
/**
 * *数据验证
 */
// var setDataValidation =  function(verify,worksheet){
//   if(!verify) return
//   for (const key in verify) {
//     const row_col = key.split('_')
//     let cell =worksheet.getCell(Number(row_col[0])+1,Number(row_col[1])+1)

//   }
// }
/**
 * *数据透视图
 */
/**
 * *筛选导出
 */
var setFilter = function(filter,worksheet){
  if(!filter) return
  worksheet.autoFilter = createCellRange(filter.row,filter.column)
}
/**
 * *冻结视图
 */
var setFrozen = function(frozen,worksheet){
  //不存在冻结或取消冻结，则不执行后续代码
  if(!frozen||frozen.type=='cancel') return
  //执行冻结操作代码
  let views = []
  switch (frozen.type) {
    //冻结首行
    case 'row':
      views = [
        {state: 'frozen', xSplit: 0, ySplit: 1}
      ];
      break;
    //冻结首列
    case 'column':
      views = [
        {state: 'frozen', xSplit: 1, ySplit: 0}
      ];
      break;
    //冻结首行首列
    case 'both':
      views = [
        {state: 'frozen', xSplit: 1, ySplit: 1}
      ];
      break;
    //冻结行至选区
    case 'rangeRow':
      views = [
        {state: 'frozen', xSplit: 0, ySplit: frozen.range.row_focus+1}
      ];
      break;
    //冻结列至选区
    case  'rangeColumn':
      views = [
        {state: 'frozen', xSplit: frozen.range.column_focus+1, ySplit: 0}
      ];
      break;
    //冻结至选区
    case 'rangeBoth':
      views = [
        {state: 'frozen', xSplit: frozen.range.column_focus+1, ySplit: frozen.range.row_focus+1}
      ];
      break;
  }
  worksheet.views = views
}
/**
 * *设置超链接
 */
var setHyperlink = function(hyperlink,worksheet){
  if (!hyperlink) return;
  for (const key in hyperlink) {
    const row_col = key.split('_')
    let cell =worksheet.getCell(Number(row_col[0])+1,Number(row_col[1])+1)
    let font = cell.style.font
    //设置导出后超链接的样式
    // cell.font= fontConvert(font.name,'#0000ff',font.bold,font.italic,font.size,font.strike,true)
    cell.font= fontConvert(font.name,'#0000ff',0,0,font.size,0,true)
    if(hyperlink[key].linkType=="external"){
      //外部链接
      cell.value = {
        text: cell.value,
        hyperlink: hyperlink[key].linkAddress,
        tooltip: hyperlink[key].linkTooltip
      }
    }else{
      // 内部链接
      const linkArr = hyperlink[key].linkAddress.split('!')
      let hyper = '#\\'+linkArr[0]+'\\'+'!'+linkArr[1]
      cell.value = {
        text: cell.value,
        hyperlink:hyper,
        tooltip: hyperlink[key].linkTooltip
      }
    }
    
  }
}
/**
 * *设置图片
 */
var setImages = function (thesheet, worksheet, workbook) {
  let {
    images,//图片对象或者数组
    visibledatacolumn, //所有行的位置
    visibledatarow, //所有列的位置
  } = { ...thesheet };
  if (typeof images != "object") return;
  for (let key in images) {
    // 通过 base64  将图像添加到工作簿
    const myBase64Image = images[key].src;
    //开始行 开始列 结束行 结束列
    const item = images[key];
    const imageId = workbook.addImage({
      base64: myBase64Image,
      extension: "png",
    });

    const col_st = getImagePosition(item.default.left, visibledatacolumn);
    const row_st = getImagePosition(item.default.top, visibledatarow);

    //模式1，图片左侧与luckysheet位置一样，像素比例保持不变，但是，右侧位置可能与原图所在单元格不一致
    worksheet.addImage(imageId, {
      tl: { col: col_st, row: row_st },
      ext: { width: item.default.width, height: item.default.height },
    });
    //模式2,图片四个角位置没有变动，但是图片像素比例可能和原图不一样
    // const w_ed = item.default.left+item.default.width;
    // const h_ed = item.default.top+item.default.height;
    // const col_ed = getImagePosition(w_ed,visibledatacolumn);
    // const row_ed = getImagePosition(h_ed,visibledatarow);
    // worksheet.addImage(imageId, {
    //   tl: { col: col_st, row: row_st},
    //   br: { col: col_ed, row: row_ed},
    // });
  }
};
//获取图片在单元格的位置
var getImagePosition = function (num, arr) {
  let index = 0;
  let minIndex;
  let maxIndex;
  for (let i = 0; i < arr.length; i++) {
    if (num < arr[i]) {
      index = i;
      break;
    }
  }

  if (index == 0) {
    minIndex = 0;
    maxIndex = 1;
    return Math.abs((num - 0) / (arr[maxIndex] - arr[minIndex])) + index;
  } else if (index == arr.length - 1) {
    minIndex = arr.length - 2;
    maxIndex = arr.length - 1;
  } else {
    minIndex = index - 1;
    maxIndex = index;
  }
  let min = arr[minIndex];
  let max = arr[maxIndex];
  let radio = Math.abs((num - min) / (max - min)) + index;
  return radio;
};
//单元格背景填充色处理
var fillConvert = function (bg) {
  if (!bg) {
    return null;
    // return {
    // 	type: 'pattern',
    // 	pattern: 'solid',
    // 	fgColor:{argb:'#ffffff'.replace('#','')}
    // }
  }
  bg = bg.indexOf("rgb") > -1 ? rgb2hex(bg) : bg;
  let fill = {
    type: "pattern",
    pattern: "solid",
    fgColor: { argb: bg.replace("#", "") },
  };
  return fill;
};
//转换颜色
var rgb2hex = function (rgb) {
  if (rgb.charAt(0) == "#") {
    return rgb;
  }

  var ds = rgb.split(/\D+/);
  var decimal = Number(ds[1]) * 65536 + Number(ds[2]) * 256 + Number(ds[3]);
  return "#" + zero_fill_hex(decimal, 6);

  function zero_fill_hex(num, digits) {
    var s = num.toString(16);
    while (s.length < digits) s = "0" + s;
    return s;
  }
};
//字体转换处理
var fontConvert = function (
  ff = 0,
  fc = "#000000",
  bl = 0,
  it = 0,
  fs = 10,
  cl = 0,
  ul = 0
) {
  // luckysheet：ff(样式), fc(颜色), bl(粗体), it(斜体), fs(大小), cl(删除线), ul(下划线)
  const luckyToExcel = {
    0: "微软雅黑",
    1: "宋体（Song）",
    2: "黑体（ST Heiti）",
    3: "楷体（ST Kaiti）",
    4: "仿宋（ST FangSong）",
    5: "新宋体（ST Song）",
    6: "华文新魏",
    7: "华文行楷",
    8: "华文隶书",
    9: "Arial",
    10: "Times New Roman",
    11: "Tahoma ",
    12: "Verdana",
    num2bl: function (num) {
      return num === 0||false ? false : true;
    },
  };
  // let color = fc ? "" : (fc + "").indexOf("rgb") > -1 ? util.rgb2hex(fc) : fc;
  // let color = fc ? fc : (fc + "").indexOf("rgb") > -1 ? util.rgb2hex(fc) : fc;

  let font = {
    name: ff,
    family: 1,
    size: fs,
    color: { argb: fc.replace("#", "") },
    bold: luckyToExcel.num2bl(bl),
    italic: luckyToExcel.num2bl(it),
    underline: luckyToExcel.num2bl(ul),
    strike: luckyToExcel.num2bl(cl),
  };

  return font;
};
//对齐转换
var alignmentConvert = function(
  vt = 'default',
  ht = 'default',
  tb = 'default',
  tr = 'default'
) {
  // luckysheet:vt(垂直), ht(水平), tb(换行), tr(旋转)
  const luckyToExcel = {
    vertical: {
      0: 'middle',
      1: 'top',
      2: 'bottom',
      default: 'top'
    },
    horizontal: {
      0: 'center',
      1: 'left',
      2: 'right',
      default: 'left'
    },
    wrapText: {
      0: false,
      1: false,
      2: true,
      default: false
    },
    textRotation: {
      0: 0,
      1: 45,
      2: -45,
      3: 'vertical',
      4: 90,
      5: -90,
      default: 0
    }
  }

  let alignment = {
    vertical: luckyToExcel.vertical[vt],
    horizontal: luckyToExcel.horizontal[ht],
    wrapText: luckyToExcel.wrapText[tb],
    textRotation: luckyToExcel.textRotation[tr]
  }
  return alignment
}
//边框转换
var borderConvert = function(borderType, style = 1, color = '#000') {
  // 对应luckysheet的config中borderinfo的的参数
  if (!borderType) {
    return {}
  }
  const luckyToExcel = {
    type: {
      'border-all': 'all',
      'border-top': 'top',
      'border-right': 'right',
      'border-bottom': 'bottom',
      'border-left': 'left'
    },
    style: {
      0: 'none',
      1: 'thin',
      2: 'hair',
      3: 'dotted',
      4: 'dashDot', // 'Dashed',
      5: 'dashDot',
      6: 'dashDotDot',
      7: 'double',
      8: 'medium',
      9: 'mediumDashed',
      10: 'mediumDashDot',
      11: 'mediumDashDotDot',
      12: 'slantDashDot',
      13: 'thick'
    }
  }
  let template = {
    style: luckyToExcel.style[style],
    color: { argb: color.replace('#', '') }
  }
  let border = {}
  if (luckyToExcel.type[borderType] === 'all') {
    border['top'] = template
    border['right'] = template
    border['bottom'] = template
    border['left'] = template
  } else {
    border[luckyToExcel.type[borderType]] = template
  }
  return border
}
//向单元格添加边框
function addborderToCell(borders, row_index, col_index) {
  let border = {}
  const luckyExcel = {
    type: {
      l: 'left',
      r: 'right',
      b: 'bottom',
      t: 'top'
    },
    style: {
      0: 'none',
      1: 'thin',
      2: 'hair',
      3: 'dotted',
      4: 'dashDot', // 'Dashed',
      5: 'dashDot',
      6: 'dashDotDot',
      7: 'double',
      8: 'medium',
      9: 'mediumDashed',
      10: 'mediumDashDot',
      11: 'mediumDashDotDot',
      12: 'slantDashDot',
      13: 'thick'
    }
  }
  for (const bor in borders) {
    if (borders[bor].color.indexOf('rgb') === -1) {
      border[luckyExcel.type[bor]] = {
        style: luckyExcel.style[borders[bor].style],
        color: { argb: borders[bor].color.replace('#', '') }
      }
    } else {
      border[luckyExcel.type[bor]] = {
        style: luckyExcel.style[borders[bor].style],
        color: { argb: borders[bor].color }
      }
    }
  }

  return border
}

        4. 涉及到的其他文件 

        setConditions.js

/**
 * *条件格式设置
*/
import { createCellRange } from './utils'
export const setConditions = function(conditions,worksheet){
    //条件格式不存在，则不执行后续代码
    if(conditions==undefined) return

    //循环遍历规则列表
    conditions.forEach(item => {
        let ruleObj = {
            ref:createCellRange(item.cellrange[0].row,item.cellrange[0].column),
            rules:[]
        }
        //lucksheet对应的为----突出显示单元格规则和项目选区规则
        if(item.type=='default'){
            //excel中type为cellIs的条件下
            if(item.conditionName=='equal'||'greaterThan'||'lessThan'||'betweenness'){
                    ruleObj.rules = setDefaultRules({
                        type:'cellIs',
                        operator:item.conditionName=='betweenness'?'between':item.conditionName,
                        condvalue:item.conditionValue,
                        colorArr:[item.format.cellColor,item.format.textColor]
                    })
                    worksheet.addConditionalFormatting(ruleObj)
                }
            //excel中type为containsText的条件下
            if(item.conditionName=='textContains'){
                ruleObj.rules = [
                        {
                            type:'containsText',
                            operator:'containsText', //表示如果单元格值包含在text 字段中指定的值，则应用格式
                            text:item.conditionValue[0],
                            style: setStyle([item.format.cellColor,item.format.textColor])
                        }
                    ]
                worksheet.addConditionalFormatting(ruleObj)
            }
            //发生日期--时间段
            if(item.conditionName=='occurrenceDate'){
                ruleObj.rules = [
                        {
                            type:'timePeriod',
                            timePeriod:'today', //表示如果单元格值包含在text 字段中指定的值，则应用格式
                            style: setStyle([item.format.cellColor,item.format.textColor])
                        }
                    ]
                worksheet.addConditionalFormatting(ruleObj)
            }
            //重复值--唯一值
            // if(item.conditionName=='duplicateValue'){
            //     ruleObj.rules = [
            //             {
            //                 type:'expression',
            //                 formulae:'today', //表示如果单元格值包含在text 字段中指定的值，则应用格式
            //                 style: setStyle([item.format.cellColor,item.format.textColor])
            //             }
            //         ]
            //     worksheet.addConditionalFormatting(ruleObj)
            // }
            //项目选区规则--top10前多少项的操作
            if(item.conditionName=='top10'||'top10%'||'last10'||'last10%'){
                ruleObj.rules = [
                        {
                            type:'top10',
                            rank:item.conditionValue[0], //指定格式中包含多少个顶部（或底部）值
                            percent:item.conditionName=='top10'||'last10'?false:true,
                            bottom:item.conditionName=='top10'||'top10%'?false:true,
                            style: setStyle([item.format.cellColor,item.format.textColor])
                        }
                    ]
                worksheet.addConditionalFormatting(ruleObj)
            }
            //项目选区规则--高于/低于平均值的操作
            if(item.conditionName=='AboveAverage'||'SubAverage'){
                ruleObj.rules = [
                        {
                            type:'aboveAverage',
                            aboveAverage:item.conditionName=='AboveAverage'?true:false,
                            style: setStyle([item.format.cellColor,item.format.textColor])
                        }
                    ]
                worksheet.addConditionalFormatting(ruleObj)
            }
            return
        }
            
        //数据条
        if(item.type == 'dataBar'){
            ruleObj.rules = [
                {
                    type:'dataBar',
                    style:{}
                }
            ]
            worksheet.addConditionalFormatting(ruleObj)
            return
        }
        //色阶
        if(item.type == 'colorGradation'){
            ruleObj.rules = [
                {
                    type:'colorScale',
                    color:item.format,
                    style:{}
                }
            ]
            worksheet.addConditionalFormatting(ruleObj)
            return
        }
        //图标集
        if(item.type == 'icons'){
            ruleObj.rules = [
                    {
                        type:'iconSet',
                        iconSet:item.format.len
                    }
                ]
            worksheet.addConditionalFormatting(ruleObj)
            return
        }
    });
  }
/**
 * 
 * @param {
 *  type:lucketsheet对应的条件导出类型；
 *  operator：excel对应的条件导入类型；
 *  condvalue：1个公式字符串数组，返回要与每个单元格进行比较的值；
 *  colorArr：颜色数组，第一项为单元格填充色，第二项为单元格文本颜色
 * } obj 
 * @returns 
 */
function setDefaultRules(obj){
    let rules = [
        {
            type:obj.type,
            operator:obj.operator,
            formulae:obj.condvalue,
            style:setStyle(obj.colorArr)
        }
    ]
    return rules
}
/**
 * 
 * @param {颜色数组，第一项为单元格填充色，第二项为单元格文本颜色} colorArr 
 */
function setStyle(colorArr){
    return {
        fill: {type: 'pattern', pattern: 'solid', bgColor: {argb: colorArr[0].replace("#", "")}},
        font: {color:{ argb: colorArr[1].replace("#", "")},}
    }
}

 setDataValidation.js

/**
 * *数据验证
 */
export const setDataValidation = function (verify, worksheet) {
  // 不存在不执行
  if (!verify) return;
  // 存在则有以下逻辑
  for (const key in verify) {
    const row_col = key.split("_");
    let cell = worksheet.getCell(
      Number(row_col[0]) + 1,
      Number(row_col[1]) + 1
    );
    let { type, type2, value1, value2 } = verify[key];
    //下拉框--list
    if (type == "dropdown") {
      cell.dataValidation = {
        type: "list",
        allowBlank: true,
        formulae: [`${value1}`],
      };
      continue;
    }
    //整数--whole
    if (type == "number_integer") {
      cell.dataValidation = {
        type: "whole",
        operator: setOperator(type2),
        showErrorMessage: true,
        formulae: value2 ? [Number(value1), Number(value2)] : [Number(value1)],
        errorStyle: "error",
        errorTitle: "警告",
        error: errorMsg(type2, type, value1, value2),
      };
      continue;
    }
    //小数-数字--decimal
    if (type == "number_decimal" || "number") {
      cell.dataValidation = {
        type: "decimal",
        operator: setOperator(type2),
        allowBlank: true,
        showInputMessage: true,
        formulae: value2 ? [Number(value1), Number(value2)] : [Number(value1)],
        promptTitle: "警告",
        prompt: errorMsg(type2, type, value1, value2),
      };
      continue;
    }
    //长度受控的文本--textLength
    if (type == "text_length") {
      cell.dataValidation = {
        type: "textLength",
        operator: setOperator(type2),
        showErrorMessage: true,
        allowBlank: true,
        formulae: value2 ? [Number(value1), Number(value2)] : [Number(value1)],
        promptTitle: "错误提示",
        prompt: errorMsg(type2, type, value1, value2),
      };
      continue;
    }
    //文本的内容--text_content
    if (type == "text_content") {
      cell.dataValidation = {};
      continue;
    }
    //日期--date
    if (type == "date") {
      cell.dataValidation = {
        type: "date",
        operator: setDateOperator(type2),
        showErrorMessage: true,
        allowBlank: true,
        promptTitle: "错误提示",
        prompt: errorMsg(type2, type, value1, value2),
        formulae: value2
          ? [new Date(value1), new Date(value2)]
          : [new Date(value1)],
      };
      continue;
    }
    //有效性--custom;type2=="phone"/"card"
    if (type == "validity") {
      
      // cell.dataValidation = {
      //   type: 'custom',
      //   allowBlank: true,
      //   formulae: [type2]
      // };
      continue;
    }
    //多选框--checkbox
    if (type == "checkbox") {
      cell.dataValidation = {};
      continue;
    }
  }
};
//类型type值为"number"/"number_integer"/"number_decimal"/"text_length"时，type2值可为
function setOperator(type2) {
  let transToOperator = {
    bw: "between",
    nb: "notBetween",
    eq: "equal",
    ne: "notEqual",
    gt: "greaterThan",
    lt: "lessThan",
    gte: "greaterThanOrEqual",
    lte: "lessThanOrEqual",
  };
  return transToOperator[type2];
}
//数字错误性提示语
function errorMsg(type2, type, value1 = "", value2 = "") {
  const tip = "你输入的不是";
  const tip1 = "你输入的不是长度";
  let errorTitle = {
    bw: `${
      type == "text_length" ? tip1 : tip
    }介于${value1}和${value2}之间的${numType(type)}`,
    nb: `${
      type == "text_length" ? tip1 : tip
    }不介于${value1}和${value2}之间的${numType(type)}`,
    eq: `${type == "text_length" ? tip1 : tip}等于${value1}的${numType(type)}`,
    ne: `${type == "text_length" ? tip1 : tip}不等于${value1}的${numType(
      type
    )}`,
    gt: `${type == "text_length" ? tip1 : tip}大于${value1}的${numType(type)}`,
    lt: `${type == "text_length" ? tip1 : tip}小于${value1}的${numType(type)}`,
    gte: `${type == "text_length" ? tip1 : tip}大于等于${value1}的${numType(
      type
    )}`,
    lte: `${type == "text_length" ? tip1 : tip}小于等于${value1}的${numType(
      type
    )}`,
    //日期
    bf: `${type == "text_length" ? tip1 : tip}早于${value1}的${numType(type)}`,
    nbf: `${type == "text_length" ? tip1 : tip}不早于${value1}的${numType(type)}`,
    af: `${type == "text_length" ? tip1 : tip}晚于${value1}的${numType(
      type
    )}`,
    naf: `${type == "text_length" ? tip1 : tip}不晚于${value1}的${numType(
      type
    )}`,
  };

  return errorTitle[type2];
}
// 数字类型（整数，小数，十进制数）
function numType(type) {
  let num = {
    number_integer: "整数",
    number_decimal: "小数",
    number: "数字",
    text_length: "文本",
    date:'日期'
  };
  return num[type];
}
//类型type值为date时
function setDateOperator(type2) {
  let transToOperator = {
    bw: "between",
    nb: "notBetween",
    eq: "equal",
    ne: "notEqual",
    bf: "greaterThan",
    nbf: "lessThan",
    af: "greaterThanOrEqual",
    naf: "lessThanOrEqual",
  };
  return transToOperator[type2];
}

 utils.js

/**
 * *创建单元格所在列的列的字母
 * @param {列数的index值} n 
 * @returns 
 */
export const createCellPos = function(n) {
    let ordA = 'A'.charCodeAt(0)
  
    let ordZ = 'Z'.charCodeAt(0)
    let len = ordZ - ordA + 1
    let s = ''
    while (n >= 0) {
      s = String.fromCharCode((n % len) + ordA) + s
  
      n = Math.floor(n / len) - 1
    }
    return s
  }
  /**
   * *创建单元格范围，期望得到如：A1:D6
   * @param {单元格行数组（例如：[0,3]）} rowArr 
   * @param {单元格列数组（例如：[5,7]）} colArr 
   * */
export const createCellRange = function(rowArr,colArr){
    const startCell = createCellPos(colArr[0])+(rowArr[0]+1)
    const endCell = createCellPos(colArr[1])+(rowArr[1]+1)
  
    return startCell+':'+endCell
  }

最后 ：

附上exceljs文档的参考地址：

GitHub： GitHub - exceljs/exceljs: Excel Workbook Manager

gitee：exceljs: exceljs不更新了在exceljs@4.3.0基础上优化了内存占用，增加了新的api