前端 - 设置excel下拉框优化？

Question

背景：

技术栈：项目： vue3 + vite

   导出excel库：exceljs

场景: 希望导出的excel的某一列填充下拉框内容，例如

尝试：
相关代码如下

for(let i = 2; i< 500;i++) {    const cellIndex = `B${i}`    _sheet1.getCell(cellIndex).dataValidation = {    type: 'list',    allowBlank: true,    showInputMessage: true,      showErrorMessage: true,      showDropDownArrow: true,      errorTitle: '错误',      error: '请从下拉列表中选择一个有效的项',      promptTitle: '选择',      prompt: '请从下拉列表中选择' ,    formulae: ['"软件一班,软件二班,软件三班"'],  };  }

上述代码一次只能给某一个单元格加下拉框，所以要达到使B列单元格都加上下拉框的目的,就要给一个很大的终止值，去循环遍历。
有没有什么更好的方法去优化？

Answer 1

确实不太友好，文档中只有 getCell().dataValidation 一种示例。
但是可以从仓库 Issue 里面发现还有 ws.dataValidations.add() 这种方式。

更加简单粗暴一些：

worksheet.dataValidations.add(  'A2:A9999',   {    type: 'list',     allowBlank: false,     formulae: ['"One,Two,Three,Four"'],     showErrorMessage: true,     errorStyle: 'error',     error: 'Please select a valid value from the list'   })

[F] Table Data Validation on columns · exceljs/exceljs · Discussion #2461

Answer 2

答案：

在 ExcelJS 中，您可以通过在 Excel 工作表的列上应用数据验证规则来一次性设置整列的下拉框，而无需循环遍历每个单元格。这样可以显著提高性能，并简化代码。

以下是如何优化您的代码以一次性设置 B 列所有单元格的数据验证规则的示例：

const ExcelJS = require('exceljs');// 创建一个新的工作簿const workbook = new ExcelJS.Workbook();// 添加一个工作表const worksheet = workbook.addWorksheet('Sheet 1');// 定义下拉列表的内容const dropDownValues = ['软件一班', '软件二班', '软件三班'];// 将下拉列表的内容转换为 ExcelJS 所需的格式const validationFormula = `"${dropDownValues.join('","')}"`;// 设置 B 列的数据验证规则worksheet.getColumn('B').eachCell({ includeEmpty: true }, function(cell, rowNumber) {    // 注意：由于我们是通过列来设置，所以这里不需要担心行号    cell.dataValidation = {        type: 'list',        allowBlank: true,        showInputMessage: true,        showErrorMessage: true,        showDropDownArrow: true,        errorTitle: '错误',        error: '请从下拉列表中选择一个有效的项',        promptTitle: '选择',        prompt: '请从下拉列表中选择',        formulae: [validationFormula],    };});// 接下来，您可以添加其他数据到工作表，并导出工作簿// ...// 导出工作簿workbook.xlsx.writeFile('example.xlsx')    .then(() => {        console.log('Excel 文件已生成');    })    .catch(error => {        console.error('导出 Excel 文件时出错:', error);    });

在上面的代码中，我们使用了 worksheet.getColumn('B') 来获取 B 列的所有单元格，并通过 eachCell 方法遍历它们。这样，我们就可以一次性为 B 列的所有单元格设置数据验证规则，而无需单独处理每个单元格。

请注意，validationFormula 是通过将下拉列表的值数组转换为以逗号分隔的字符串来创建的，字符串被双引号包围，以适应 Excel 的数据验证公式语法。

这种方法不仅优化了性能，而且代码更简洁，易于维护。