关于exceljs的数据有效性验证踩坑记录

最近公司的项目中，需要有下载 excel 模板的功能，这个模板需要在输入时进行有效性判断。我当时听到需求时浑身一震，心想：这都是啥玩意啊 excel 输入限制是啥啊？？？在询问一番该功能的必要性之后，便开始了查看文档之旅。

一开始，我先在npm上搜索热门的 excel in js 模块，发现了xlsx。看了xlsx的文档，使用谷歌浏览器的查询功能，查询关键词 data validation ，没找到。又去谷歌了一番，发现 xlsx 的 pro 版具有这个功能，当然这玩意肯定不是免费的啦，遂查看别的模块。一番查找，看到了 exceljs 模块，查了查关键词，发现这个模块有数据验证功能，又看了看 api 文档，发现有中文版，瞬间心动了起来。

大致翻了一遍文档后，就开工了。

要创建一个带数据验证的 excel 模板，需要以下步骤

1、引用 exceljs 模块，之后实例化一个 exceljs.Workbook ，以下将其称为 wb

2、向 wb 中添加一个 sheet ，以下将其称为 ws

3、向 ws 中添加 columns 。其中 column 为一个对象数组，具体参数详见注1

4、向 ws 的行添加数据验证。其中 dataValidation 为一个对象，具体参数详见注2

5、将 wb 转换成buffer

6、引用 file-saver 模块的 saveAs 方法，将 buffer 保存为文件

写完以上代码后，我下载了一个测试了一下，发现只有我设置的行具有数据验证功能，其他行均没有数据验证，想了一想，没想到好办法，只想到使用 for 循环将尽可能多的行设置上数据验证。谷歌一下，也没搜到更好的方法，便使用 for 循环了。

以下是具体代码

export const export2Excel = async ({
  columns = [
    // {
    //   header: '出生日期（必填）',
    //   key: 'birthday',
    //   width: 20,
    //   style: { numFmt: 'yyyy-mm-dd' },
    // },
  ],
  list = [
    // {
    //   column: 4,
    //   data: ['"12,321,31231"'],
    //   operator: 'lessThan',
    //   showInputMessage: true,
    //   promptTitle: '输入提示标题',
    //   prompt: '输入提示内容',
    //   type: 'list'
    // },
  ],
  filename = 'a',
}) => {
  const wb = new Workbook()
  const ws = wb.addWorksheet('tb')
  ws.columns = columns
  for (let row = 2; row < 5000; row++) {
    // const row = i + 2
    for (let j = 0; j < list.length; j++) {
      const element = list[j]
      ws.getCell(row, element.column).dataValidation = {
        type: element.type,
        operator: element.operator ? element.operator : undefined,
        showInputMessage: element.showInputMessage,
        promptTitle: element.showInputMessage ? element.promptTitle : undefined,
        prompt: element.showInputMessage ? element.prompt : undefined,
        showErrorMessage: true,
        formulae: element.data,
      }
    }
  }

  const buffer = await wb.xlsx.writeBuffer()
  saveAs(
    new Blob([buffer], {
      type: 'application/octet-stream',
    }),
    `${filename}.xlsx`,
  )
}

 

参考文献：[1] exceljs中文文档

注：

1、columns 的参数有：

　　header：表格标题，也就是 excel 第一行每列的具体内容

　　key：用于建立当前列索引

　　width：用于设置列宽

　　style：用于设置列的内容样式，也可以设置内容格式，例如 numFmt 可以设置输入的数字格式，具体格式参考 excel 的单元格格式设置--自定义格式

2、dataValidation 的参数：

　　type：数据验证的类型，常用内容如下，其他详见文档

　　　　list：定义一组数据列表。

　　　　whole：该值必须是整数

　　　　textLength：该值可以是文本，但长度是受控的

　　　　date：该值必须是日期

　　operator：值的区间，常用内容如下，其他详见文档

　　　　between：值必须介于公式结果之间

　　　　equal：值必须等于公式结果

　　　　greaterThan：值必须大于公式结果

　　　　lessThanOrEqual：值必须小于或等于公式结果

　　formulae：指定有效值的列表，常用格式如下，其他详见文档

　　　　['"12,321,31231"']：当 type 为 list 时使用，另外可以使用 [$D$5:$G$5'] 

　　　　[5]：通常在 type 为文本长度、数字且 operator 为等于、不等于、大于、小于时使用

　　　　[1, 5]：通常在 type 为文本长度、数字且 operator 为 between、notBetween 时使用

　　　　[new Date()]：通常在 operator 为等于、不等于、大于、小于时使用

　　　　[new Date(2000,1,1), new Date()]：通常在 operator 为 between、notBetween 时使用

　　showInputMessage：是否显示输入提示，必须为布尔值，默认为false

　　prompTitle：输入提示标题

　　promp：输入提示内容

　　showErrorMessage：是否提示数据错误信息，必须为布尔值