ExcelJS数据处理：公式、数据验证与条件格式-优快云博客

ExcelJS数据处理：公式、数据验证与条件格式

本文详细介绍了ExcelJS库在数据处理方面的三大核心功能：公式支持与计算、数据验证规则设置以及条件格式化的实现。文章首先阐述了ExcelJS如何通过FormulaValue类型支持复杂的Excel公式，包括共享公式和数组公式等高级特性。接着讲解了数据验证功能，展示了如何设置各种验证规则来确保数据完整性。最后深入探讨了条件格式化的架构和多种类型，如表达式、颜色刻度和图标集，提供了完整的实现示例和最佳实践。

Excel公式支持与计算

ExcelJS提供了强大的公式支持功能，允许开发者在生成的Excel文件中嵌入复杂的计算公式。虽然ExcelJS本身不执行公式计算（这由Excel应用程序处理），但它提供了完整的公式语法支持和结果预填充机制。

公式值类型

在ExcelJS中，公式通过特殊的FormulaValue类型来表示。每个公式单元格包含两个关键属性：

formula: 字符串形式的Excel公式表达式
result: 公式的计算结果值

// 基本公式设置示例
worksheet.getCell('A3').value = {
  formula: 'SUM(A1:A2)',
  result: 15
};

// 使用数学运算公式
worksheet.getCell('B3').value = {
  formula: 'A1 * B1 + C1',
  result: 42
};

// 使用函数公式
worksheet.getCell('C3').value = {
  formula: 'AVERAGE(C1:C2)',
  result: 7.5
};

公式类型系统

ExcelJS支持多种公式类型，通过formulaType属性进行区分：

mermaid

共享公式（Shared Formulas）

共享公式是ExcelJS的一个重要特性，它可以显著减少XLSX文件的大小，特别是在包含大量相似公式的工作表中。

// 创建主公式
worksheet.getCell('A2').value = {
  formula: 'A1 * 2',
  result: 10,
  shareType: 'master',
  ref: 'A2:B3'
};

// 从属单元格会自动继承并转换公式
// B2单元格的公式会自动转换为 'B1 * 2'
// C2单元格的公式会自动转换为 'C1 * 2'

数组公式（Array Formulas）

数组公式允许单个公式影响多个单元格，是处理数据范围的强大工具：

// 创建数组公式
worksheet.getCell('A2').value = {
  formula: 'A1',
  result: 5,
  shareType: 'array',
  ref: 'A2:B3'
};

// 这将创建一个影响A2:B3范围的数组公式
// 所有受影响单元格都会显示相同的结果

公式便利方法

ExcelJS提供了便捷的getter方法来访问公式属性：

const cell = worksheet.getCell('C5');

// 检查是否为公式单元格
if (cell.formula) {
  console.log('公式:', cell.formula);
  console.log('结果:', cell.result);
  console.log('公式类型:', cell.formulaType);
}

// 直接设置公式
cell.value = { formula: 'SUM(A1:B10)', result: 250 };

支持的公式函数

ExcelJS支持绝大多数Excel内置函数，包括：

函数类别	示例函数	描述
数学函数	SUM, AVERAGE, MAX, MIN	基本数学计算
统计函数	COUNT, STDEV, VAR	统计分析
逻辑函数	IF, AND, OR, NOT	条件判断
查找函数	VLOOKUP, HLOOKUP, MATCH	数据查找
文本函数	CONCATENATE, LEFT, RIGHT	文本处理
日期函数	TODAY, NOW, DATE	日期时间

公式引用模式

ExcelJS支持所有标准的Excel引用模式：

// 相对引用
worksheet.getCell('B2').value = { formula: 'A1', result: 10 };

// 绝对引用
worksheet.getCell('C2').value = { formula: '$A$1', result: 10 };

// 混合引用
worksheet.getCell('D2').value = { formula: 'A$1', result: 10 };
worksheet.getCell('E2').value = { formula: '$A1', result: 10 };

// 跨工作表引用
worksheet.getCell('F2').value = { 
  formula: 'Sheet2!A1', 
  result: 15 
};

公式填充功能

ExcelJS提供了fillFormula方法来批量填充公式：

// 使用fillFormula批量创建公式
worksheet.fillFormula('A2:A10', 'ROW()*2', [4, 6, 8, 10, 12, 14, 16, 18, 20]);

// 这将为A2:A10区域填充公式 ROW()*2
// 并预填充对应的计算结果

条件公式应用

公式可以与其他ExcelJS功能结合使用，如条件格式和数据验证：

// 条件格式中使用公式
worksheet.addConditionalFormatting({
  ref: 'A1:A10',
  rules: [
    {
      type: 'expression',
      formulae: ['MOD(ROW(),2)=0'],
      style: { fill: { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFFF00' } } }
    }
  ]
});

// 数据验证中使用公式
worksheet.getCell('B1').dataValidation = {
  type: 'custom',
  formulae: ['B1>10']
};

公式性能优化

对于包含大量公式的工作表，ExcelJS提供了多种优化策略：

共享公式减少文件大小
批量填充提高处理效率
预计算结果避免重复计算
合理使用数组公式

// 性能优化示例：使用共享公式
const masterFormula = {
  formula: 'A1 * 1.1',
  result: 11,
  shareType: 'master',
  ref: 'B1:B1000'
};

worksheet.getCell('B1').value = masterFormula;
// B2:B1000会自动成为共享公式单元格

公式错误处理

ExcelJS支持标准的Excel错误值，确保公式错误能够正确显示：

// 设置公式错误值
worksheet.getCell('C1').value = {
  formula: '1/0',
  result: { error: '#DIV/0!' }
};

// 支持的错误类型包括：
// #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #N/A

通过ExcelJS的公式支持，开发者可以创建功能丰富的电子表格应用，实现复杂的数据计算和分析功能，同时保持与Microsoft Excel的完全兼容性。

数据验证规则设置

ExcelJS提供了强大的数据验证功能，允许开发者在Excel工作表中设置各种数据验证规则，确保数据的完整性和准确性。通过数据验证，可以限制用户输入的内容类型、范围，并提供友好的提示信息。

数据验证基础

在ExcelJS中，数据验证通过单元格的dataValidation属性进行设置。每个单元格可以拥有独立的数据验证规则，支持多种验证类型和操作符。

const ExcelJS = require('exceljs');
const workbook = new ExcelJS.Workbook();
const worksheet = workbook.addWorksheet('数据验证示例');

// 设置单元格的数据验证
worksheet.getCell('B1').dataValidation = {
  type: 'whole',
  operator: 'between',
  allowBlank: true,
  showInputMessage: true,
  showErrorMessage: true,
  formulae: [1, 100]
};

验证类型详解

ExcelJS支持多种数据验证类型，每种类型都有特定的用途和配置选项：

验证类型	描述	适用场景
`whole`	整数验证	年龄、数量等整数值
`decimal`	小数验证	价格、百分比等小数值
`date`	日期验证	出生日期、订单日期等
`textLength`	文本长度验证	用户名、密码长度限制
`list`	下拉列表验证	性别、状态等固定选项
`any`	任意值验证	仅显示提示信息
`custom`	自定义公式验证	复杂业务规则

操作符配置

对于数值类型的验证，可以使用不同的操作符来定义验证条件：

// 等于特定值
worksheet.getCell('C1').dataValidation = {
  type: 'whole',
  operator: 'equal',
  formulae: [5]
};

// 大于特定值  
worksheet.getCell('D1').dataValidation = {
  type: 'decimal',
  operator: 'greaterThan',
  formulae: [10.5]
};

// 范围验证
worksheet.getCell('E1').dataValidation = {
  type: 'date',
  operator: 'between',
  formulae: [new Date(2020, 0, 1), new Date(2023, 11, 31)]
};

支持的操作符包括：between、notBetween、equal、notEqual、greaterThan、lessThan、greaterThanOrEqual、lessThanOrEqual。

下拉列表验证

下拉列表是数据验证中最常用的功能之一，支持三种数据源方式：

// 1. 命名范围
worksheet.getCell('F1').value = '北京';
worksheet.getCell('F1').name = 'Cities';
worksheet.getCell('G1').value = '上海';
worksheet.getCell('G1').name = 'Cities';
worksheet.getCell('H1').dataValidation = {
  type: 'list',
  formulae: ['Cities']
};

// 2. 字面量列表
worksheet.getCell('I1').dataValidation = {
  type: 'list',
  formulae: ['"男,女,未知"']
};

// 3. 单元格范围
worksheet.getCell('J1').value = '选项A';
worksheet.getCell('K1').value = '选项B'; 
worksheet.getCell('L1').value = '选项C';
worksheet.getCell('M1').dataValidation = {
  type: 'list',
  formulae: ['$J$1:$L$1']
};

提示和错误信息

数据验证支持丰富的用户提示和错误信息配置：

worksheet.getCell('N1').dataValidation = {
  type: 'whole',
  operator: 'equal',
  allowBlank: true,
  showInputMessage: true,
  showErrorMessage: true,
  formulae: [5],
  promptTitle: '输入提示',
  prompt: '请输入数字5',
  errorStyle: 'error',
  errorTitle: '输入错误',
  error: '必须输入数字5'
};

错误样式支持三种类型：

stop - 停止（默认）
warning - 警告
information - 信息

高级配置选项

// 允许空值
worksheet.getCell('O1').dataValidation = {
  type: 'whole',
  operator: 'greaterThan',
  allowBlank: true,  // 允许单元格为空
  formulae: [10]
};

// 仅显示输入提示
worksheet.getCell('P1').dataValidation = {
  type: 'any',
  showInputMessage: true,
  promptTitle: '注意事项',
  prompt: '请确保输入正确的数据格式'
};

// 自定义公式验证
worksheet.getCell('Q1').dataValidation = {
  type: 'custom',
  formulae: ['=AND(ISNUMBER(Q1), Q1>0)']  // 必须为正数
};

验证规则优化

ExcelJS会自动优化相同的数据验证规则，将相邻单元格的相同验证合并为范围验证，减少文件大小并提高Excel加载性能：

mermaid

实际应用示例

以下是一个完整的数据验证设置示例：

// 创建员工信息表数据验证
const employeeSheet = workbook.addWorksheet('员工信息');

// 工号验证（5位数字）
employeeSheet.getCell('B2').dataValidation = {
  type: 'whole',
  operator: 'between',
  formulae: [10000, 99999],
  prompt: '请输入5位数字工号'
};

// 姓名验证（文本长度2-10字符）
employeeSheet.getCell('B3').dataValidation = {
  type: 'textLength',
  operator: 'between',
  formulae: [2, 10],
  prompt: '姓名长度2-10个字符'
};

// 性别下拉列表
employeeSheet.getCell('B4').dataValidation = {
  type: 'list',
  formulae: ['"男,女"'],
  showInputMessage: true,
  prompt: '请选择性别'
};

// 年龄验证（18-65岁）
employeeSheet.getCell('B5').dataValidation = {
  type: 'whole',
  operator: 'between',
  formulae: [18, 65],
  errorTitle: '年龄错误',
  error: '年龄必须在18-65岁之间'
};

// 入职日期验证
employeeSheet.getCell('B6').dataValidation = {
  type: 'date',
  operator: 'greaterThan',
  formulae: [new Date(2000, 0, 1)],
  prompt: '入职日期必须晚于2000年'
};

// 薪资验证（正小数）
employeeSheet.getCell('B7').dataValidation = {
  type: 'decimal',
  operator: 'greaterThan',
  formulae: [0],
  errorStyle: 'warning',
  error: '薪资必须为正数'
};

验证规则管理

ExcelJS通过DataValidations类管理工作表的所有数据验证规则：

// 获取工作表的验证管理器
const dataValidations = worksheet.dataValidations;

// 添加验证规则
dataValidations.add('A1', {
  type: 'whole',
  operator: 'greaterThan',
  formulae: [10]
});

// 查询验证规则
const validation = dataValidations.find('A1');
console.log(validation); // 输出验证规则对象

// 删除验证规则
dataValidations.remove('A1');

最佳实践

性能优化：对连续相同验证规则的单元格使用范围引用，而不是为每个单元格单独设置
用户体验：为重要的验证规则提供清晰的提示信息和错误说明
灵活性：合理使用allowBlank选项，确保必填和选填字段的区分
数据完整性：结合业务逻辑设置合适的验证规则，防止无效数据输入

通过ExcelJS的数据验证功能，开发者可以构建出专业级的数据录入界面，确保Excel文档的数据质量和一致性，为用户提供友好的数据输入体验。

条件格式化的实现

ExcelJS提供了强大而灵活的条件格式化功能，允许开发者根据特定规则动态改变单元格的外观样式。条件格式化是现代Excel数据处理中不可或缺的功能，它能够直观地展示数据趋势、突出显示关键信息，并提供数据可视化效果。

条件格式化的核心架构

ExcelJS的条件格式化系统采用了分层架构设计，通过多个XForm组件协同工作来实现完整的条件格式化功能：

mermaid

支持的条件格式化类型

ExcelJS支持多种条件格式化规则类型，每种类型都有其特定的应用场景和配置参数：

类型	描述	主要参数
`expression`	基于公式的条件格式化	`formulae`, `priority`, `style`
`cellIs`	单元格值比较	`operator`, `formulae`, `style`
`top10`	前N项或后N项	`percent`, `bottom`, `rank`
`aboveAverage`	高于或低于平均值	`aboveAverage`, `style`
`colorScale`	颜色刻度	`cfvo`, `color`
`iconSet`	图标集	`iconSet`, `cfvo`
`containsText`	文本包含	`operator`, `text`
`timePeriod`	时间周期	`timePeriod`

条件格式化的实现机制

1. 规则优先级管理

ExcelJS自动管理条件格式化规则的优先级，确保多个规则能够正确叠加和应用：

// 在ConditionalFormattingsXform.prepare方法中自动分配优先级
let nextPriority = model.reduce(
  (p, cf) => Math.max(p, ...cf.rules.map(rule => rule.priority || 0)),
  1
);
model.forEach(cf => {
  cf.rules.forEach(rule => {
    if (!rule.priority) {
      rule.priority = nextPriority++;
    }
  });
});

2. 样式处理与DXF映射

条件格式化使用DXF（差分格式）样式系统，确保样式的高效存储和应用：

// 样式映射处理
if (rule.style) {
  rule.dxfId = options.styles.addDxfStyle(rule.style);
}

// 在解析时还原样式
if (rule.dxfId !== undefined) {
  rule.style = options.styles.getDxfStyle(rule.dxfId);
  delete rule.dxfId;
}

3. 公式生成与处理

对于文本和时间周期类型的条件格式化，ExcelJS能够自动生成相应的Excel公式：

// 文本相关公式生成
const getTextFormula = model => {
  const range = new Range(model.ref);
  const {tl} = range;
  switch (model.operator) {
    case 'containsText':
      return `NOT(ISERROR(SEARCH("${model.text}",${tl})))`;
    case 'containsBlanks':
      return `LEN(TRIM(${tl}))=0`;
    // ... 其他文本操作符
  }
};

具体实现示例

表达式条件格式化

表达式条件格式化允许使用Excel公式来定义格式化规则：

worksheet.addConditionalFormatting({
  ref: 'A1:E7',
  rules: [
    {
      type: 'expression',
      priority: 1,
      formulae: ['MOD(ROW()+COLUMN(),2)=0'],
      style: {font: {bold: true}, fill: {type: 'pattern', pattern: 'solid', bgColor: {argb: 'FFF0F0F0'}}}
    }
  ]
});

颜色刻度条件格式化

颜色刻度提供渐变色的数据可视化效果：

worksheet.addConditionalFormatting({
  ref: 'A1:E7',
  rules: [
    {
      type: 'colorScale',
      cfvo: [
        {type: 'min'}, 
        {type: 'percentile', value: 50}, 
        {type: 'max'}
      ],
      color: [
        {argb: 'FFF8696B'},  // 红色 - 最小值
        {argb: 'FFFFEB84'},  // 黄色 - 中间值
        {argb: 'FF63BE7B'}   // 绿色 - 最大值
      ]
    }
  ]
});

图标集条件格式化

图标集使用可视化图标来表示数据范围：

worksheet.addConditionalFormatting({
  ref: 'A1:E7',
  rules: [
    {
      type: 'iconSet',
      iconSet: '3Arrows',
      cfvo: [
        {type: 'percent', value: 0},
        {type: 'percent', value: 33},
        {type: 'percent', value: 66}
      ]
    }
  ]
});

高级特性

1. 自定义图标集

ExcelJS支持扩展的图标集类型，包括一些非标准的图标：

const extIcons = {
  '3Triangles': true,
  '3Stars': true,
  '5Boxes': true,
};

2. 复杂公式支持

系统能够处理复杂的公式场景，包括相对引用和绝对引用的正确转换：

// 时间周期公式自动生成
case 'thisWeek':
  return `AND(TODAY()-ROUNDDOWN(${tl},0)<=WEEKDAY(TODAY())-1,ROUNDDOWN(${tl},0)-TODAY()<=7-WEEKDAY(TODAY()))`;

3. 性能优化

通过DXF样式系统和优先级管理，ExcelJS确保了条件格式化在大数据量下的性能表现：

mermaid

最佳实践建议

优先级管理：始终明确设置规则的priority属性，避免不可预期的覆盖行为
样式复用：相同的样式定义应该复用，减少DXF样式的数量
范围优化：合理设置ref范围，避免过大范围影响性能
公式简洁：尽量使用简洁的公式，提高计算效率
类型选择：根据数据特性选择合适的条件格式化类型

通过这套完整的条件格式化实现，ExcelJS为开发者提供了强大而灵活的数据可视化能力，能够满足各种复杂的数据展示需求。

数据表格与自动筛选功能

ExcelJS提供了强大的数据表格功能，让开发者能够以编程方式创建和管理Excel表格，包括自动筛选、样式设置和汇总行等高级功能。通过表格功能，您可以轻松实现数据的结构化展示和交互式筛选。

表格创建与基本配置

在ExcelJS中创建表格非常简单，只需要指定表格的名称、引用范围和列定义即可：

const worksheet = workbook.addWorksheet('销售数据');
const table = worksheet.addTable({
  name: 'SalesTable',
  ref: 'A1:D6',
  headerRow: true,
  totalsRow: true,
  style: {
    theme: 'TableStyleMedium2',
    showRowStripes: true,
    showColumnStripes: false
  },
  columns: [
    { name: '日期', filterButton: true },
    { name: '产品', filterButton: true },
    { name: '数量', totalsRowFunction: 'sum', filterButton: false },
    { name: '金额', totalsRowFunction: 'sum', filterButton: true }
  ],
  rows: [
    [new Date('2024-01-01'), '产品A', 10, 1000],
    [new Date('2024-01-02'), '产品B', 15, 1500],
    [new Date('2024-01-03'), '产品A', 8, 800],
    [new Date('2024-01-04'), '产品C', 20, 2000]
  ]
});

自动筛选功能详解

自动筛选是Excel表格的核心功能之一，ExcelJS通过filterButton属性来控制每列的筛选按钮显示：

// 启用第一列的筛选功能
table.columns[0].filterButton = true;

// 禁用第二列的筛选功能  
table.columns[1].filterButton = false;

// 批量设置筛选按钮
table.columns.forEach((column, index) => {
  column.filterButton = index !== 2; // 第三列不显示筛选按钮
});

筛选功能的工作流程

mermaid

表格样式与主题

ExcelJS支持多种表格样式主题，可以创建专业美观的数据表格：

// 可用主题样式示例
const tableThemes = [
  'TableStyleLight1',    // 浅色主题1
  'TableStyleLight21',   // 浅色主题21
  'TableStyleMedium2',   // 中等主题2
  'TableStyleMedium13',  // 中等主题13
  'TableStyleDark3',     // 深色主题3
  'TableStyleDark11'     // 深色主题11
];

// 应用表格样式
table.style = {
  theme: 'TableStyleDark3',
  showFirstColumn: false,    // 不显示首列特殊样式
  showLastColumn: false,     // 不显示末列特殊样式
  showRowStripes: true,      // 显示行条纹
  showColumnStripes: false   // 不显示列条纹
};

汇总行功能

汇总行提供了对表格数据的统计功能，支持多种汇总函数：

汇总函数	描述	公式示例
`none`	无汇总功能	-
`average`	平均值	`SUBTOTAL(101,Table[Column])`
`countNums`	数值计数	`SUBTOTAL(102,Table[Column])`
`count`	非空计数	`SUBTOTAL(103,Table[Column])`
`max`	最大值	`SUBTOTAL(104,Table[Column])`
`min`	最小值	`SUBTOTAL(105,Table[Column])`
`stdDev`	标准差	`SUBTOTAL(106,Table[Column])`
`var`	方差	`SUBTOTAL(107,Table[Column])`
`sum`	求和	`SUBTOTAL(109,Table[Column])`
`custom`	自定义公式	用户定义公式

// 设置汇总行配置
table.columns[0].totalsRowLabel = '总计'; // 第一列显示标签
table.columns[2].totalsRowFunction = 'sum'; // 第三列求和
table.columns[3].totalsRowFunction = 'average'; // 第四列求平均

// 自定义汇总公式
table.columns[4].totalsRowFunction = 'custom';
table.columns[4].totalsRowFormula = 'AVERAGE(Table[Score])*100';

高级表格操作

ExcelJS提供了丰富的表格操作方法，支持动态修改表格结构：

// 添加新行
table.addRow(['2024-01-05', '产品D', 12, 1200]);

// 删除行
table.removeRows(2); // 删除第三行

// 添加新列
table.addColumn(
  { name: '利润率', totalsRowFunction: 'average', filterButton: true },
  [0.1, 0.15, 0.12, 0.18],
  4 // 插入位置
);

// 移动表格位置
table.ref = 'C3:F8'; // 将表格移动到新的位置

// 提交更改
table.commit();

表格数据验证

结合数据验证功能，可以创建更加健壮的表格：

// 为表格列添加数据验证
worksheet.getCell('B2').dataValidation = {
  type: 'list',
  formulae: ['"产品A,产品B,产品C,产品D"'],
  showErrorMessage: true,
  errorTitle: '无效输入',
  error: '请选择列表中的产品'
};

// 批量设置数据验证
for (let i = 2; i <= 5; i++) {
  worksheet.getCell(`B${i}`).dataValidation = {
    type: 'whole',
    operator: 'between',
    formulae: [1, 100],
    showErrorMessage: true,
    error: '数量必须在1-100之间'
  };
}

性能优化建议

对于大型数据表格，建议采用以下优化策略：

// 1. 批量操作数据
const bulkData = [];
for (let i = 0; i < 1000; i++) {
  bulkData.push([`数据${i}`, Math.random() * 100]);
}
table.rows = bulkData;

// 2. 禁用自动计算
workbook.calcProperties.fullCalcOnLoad = false;

// 3. 使用流式写入
const stream = workbook.xlsx.createInputStream();
stream.pipe(fs.createWriteStream('large-table.xlsx'));

// 4. 合理设置表格范围
table.ref = `A1:Z${bulkData.length + 2}`;

通过ExcelJS的表格功能，开发者可以创建功能丰富、样式专业的Excel数据表格，满足各种业务场景的数据展示和分析需求。自动筛选功能特别适合需要交互式数据探索的应用场景，而汇总行则提供了便捷的数据统计能力。

总结

ExcelJS提供了强大而全面的数据处理能力，涵盖了公式计算、数据验证和条件格式化等关键功能。通过详细的代码示例和架构解析，本文展示了如何利用这些功能创建专业级的Excel文档。公式支持允许嵌入复杂计算逻辑，数据验证确保数据质量和一致性，条件格式化则提供直观的数据可视化效果。这些功能的结合使用，使开发者能够构建出功能丰富、用户友好的电子表格应用，满足各种复杂的数据处理和分析需求。

创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考