企业官网建设流程全解析

JQuick-Excel 导出 FORMULAS 配置项使用手册

本手册面向程序员，专门讲解 jquick-excel 在 Excel 导出场景中的FORMULAS配置项，包括语法结构、定位规则、支持的公式类型、与样式/流式写入的关系，以及常见问题。

文档示例统一采用 XML / DSL 声明式写法，便于直接落地到jquick-excel.xml。

更多示例: JQuick-Excel 官网示例 https://www.jquick.org/jquick-excel/#demo-formulas0005

目录

• 1. FORMULAS 是什么
• 2. 基础语法
• 3. 三种作用目标
  • 3.1 单元格公式
  • 3.2 行公式
  • 3.3 列公式
• 4. 最小可运行示例
• 5. 公式是如何执行的
• 6. 支持的公式类型
  • 6.1 数学类
  • 6.2 日期时间类
  • 6.3 字符串类
  • 6.4 逻辑类
  • 6.5 自定义公式
• 7. 常见配置示例
  • 7.1 统计汇总
  • 7.2 条件判断
  • 7.3 字符串处理
  • 7.4 日期处理
  • 7.5 整行 / 整列批量套公式
• 8. FORMULAS 与 MAPPING / TRANSFORM / STYLE 的关系
• 9. FORMULAS 与 SXSSF 流式导出的限制
• 10. 常见问题与避坑指南
• 11. 推荐实践

1. FORMULAS 是什么

FORMULAS用于在Excel 导出阶段给目标单元格写入公式。

它解决的不是“Java 里先算好结果再写单元格”，而是“把 Excel 公式本身写进工作表中，让 Excel 打开文件后继续参与计算”。

典型场景：

• 导出报表时增加汇总行，如SUM(D2:D100)
• 生成统计指标，如AVERAGE(E2:E100)、MAX(F2:F100)
• 给某一列批量套公式
• 按条件输出结果，如IF(D2>60,"及格","不及格")
• 使用 Excel 原生公式保留后续人工编辑与自动重算能力

一句话理解：

TRANSFORM 是“导出前先算值” FORMULAS 是“把公式写进 Excel 让 Excel 自己算”

2. 基础语法

FORMULAS的配置结构如下：

FORMULAS = { 目标: '公式表达式', 目标: '公式表达式', ... }

在完整导出 DSL 中通常这样写：

<excelname="exportExcel"returnClass="void"><![CDATA[ EXPORT WITH SHEET="学生表", HEADER=true, MAPPING={ "id":"主键", "name":"姓名", "gender":"性别", "age":"年龄", "enrollmentDate":"入学时间", "className":"班级" }, FORMULAS={ D5:'SUM(D2:D4)' } ]]></excel>

说明：

3. 三种作用目标

从源码解析器和导出处理逻辑看，FORMULAS支持三类目标：

• 单元格公式：给某一个单元格写公式
• 行公式：给某一整行或多行的每一列写同一个公式
• 列公式：给某一整列或多列的每一行写同一个公式

3.1 单元格公式

语法：

FORMULAS={ D5:'SUM(D2:D4)' }

含义：

• 把SUM(D2:D4)写入D5
• D5是 Excel 坐标，列名 + 行号

适合场景：

• 总计行
• 平均值行
• 指定位置插入统计公式

3.2 行公式

语法：

FORMULAS={ ROW 5:'ABS(D2)', ROW 8..10:'TODAY()' }

含义：

• ROW 5：给第 5 行的每一列都写入同一个公式
• ROW 8..10：给第 8 到第 10 行的每一列都写入同一个公式

适合场景：

• 整行输出统一公式结果
• 模板化报表中的计算行
• 给预留计算区批量套公式

3.3 列公式

语法：

FORMULAS={ COL D:'IF(D2>0,"Yes","No")', COL D..F:'NOW()' }

含义：

• COL D：给 D 列所有已遍历到的行写入同一个公式
• COL D..F：给 D 到 F 列所有已遍历到的行写入同一个公式

适合场景：

• 某列批量生成计算结果
• 对多个指标列统一套用某类公式

注意：列目标内部最终是按列号处理的，写法建议使用COL D、COL E..G这种形式，语义更清晰。

4. 最小可运行示例

下面给出一个最小可运行的导出配置：

<excelname="exportWithFormula"returnClass="void"><![CDATA[ EXPORT WITH SHEET="学生表", HEADER=true, MAPPING={ "id":"主键", "name":"姓名", "gender":"性别", "age":"年龄", "enrollmentDate":"入学时间", "className":"班级" }, FORMULAS={ D5:'SUM(D2:D4)' } ]]></excel>

这个配置的效果是：

• 第 1 行输出表头
• 第 2~4 行输出数据
• 第 5 行的D5写入SUM(D2:D4)

也就是说，D5会成为“年龄列汇总”位置。

5. 公式是如何执行的

从导出处理逻辑看，FORMULAS的执行时机在：

写表头 → 写数据 → 应用公式 → 应用样式 → 应用合并 → 应用图表

也就是说：

1. 先把数据行写到工作表
2. 再根据FORMULAS找到目标位置
3. 调用 Apache POI 的cell.setCellFormula(...)写入公式

因此它并不是把公式“算完再写值”，而是把公式字符串注册到目标单元格中。

这意味着：

• Excel 打开文件后可继续重算
• 公式结果会跟随引用单元格变化
• 很适合报表、模板、财务类场景

6. 支持的公式类型

从JFormulaEnums和测试用例可确认，框架内置支持多类公式，并且对无法识别的内容会回退到自定义公式处理。

6.1 数学类

常见支持项：

示例：

FORMULAS={ D5:'SUM(D2:D4)', D6:'AVERAGE(D2:D4)', D7:'MAX(D2:D4)', D8:'MIN(D2:D4)' }

6.2 日期时间类

常见支持项：

示例：

FORMULAS={ E5:'TODAY()', F5:'NOW()', G5:'YEAR(E2)', H5:'MONTH(E2)' }

6.3 字符串类

常见支持项：

示例：

FORMULAS={ G2:'CONCAT(B2,"-",F2)', G3:'LEN(B3)', G4:'UPPER(B4)' }

6.4 逻辑类

常见支持项：

示例：

FORMULAS={ G2:'IF(D2>=18,"成年","未成年")', G3:'AND(D2>18,D2<30)', G4:'OR(D2=20,D2=21)' }

6.5 自定义公式

若公式内容不在内置枚举中，框架会回退到JCustomFormula。

这意味着大多数 Excel 原生公式只要最终能被 POI 接受，也可以直接尝试写入：

FORMULAS={ H2:'VLOOKUP(A2,$M$2:$N$10,2,FALSE)' }

适合场景：

• 使用框架未内置封装但 Excel 支持的函数
• 复杂报表中自定义引用表达式
• 迁移已有 Excel 模板中的公式

建议先在 Excel 中验证公式本身可用，再复制到FORMULAS中。

7. 常见配置示例

7.1 统计汇总

<excelname="exportStatistics"returnClass="void"><![CDATA[ EXPORT WITH SHEET="学生表", HEADER=true, MAPPING={ "id":"主键", "name":"姓名", "age":"年龄" }, FORMULAS={ C5:'SUM(C2:C4)', C6:'AVERAGE(C2:C4)', C7:'MAX(C2:C4)', C8:'MIN(C2:C4)', C9:'COUNT(C2:C4)' } ]]></excel>

用途：

• 在导出表尾生成总和、均值、最大值、最小值、计数

7.2 条件判断

<excelname="exportIfCase"returnClass="void"><![CDATA[ EXPORT WITH SHEET="学生表", HEADER=true, MAPPING={ "name":"姓名", "age":"年龄" }, FORMULAS={ C2:'IF(B2>=18,"成年","未成年")', C3:'IF(B3>=18,"成年","未成年")', C4:'IF(B4>=18,"成年","未成年")' } ]]></excel>

用途：

• 根据年龄生成判定结果
• 适合状态列、风险等级列、业务标签列

7.3 字符串处理

<excelname="exportStringCase"returnClass="void"><![CDATA[ EXPORT WITH SHEET="学生表", HEADER=true, MAPPING={ "name":"姓名", "className":"班级" }, FORMULAS={ C2:'CONCAT(A2,"-",B2)', C3:'LEN(A3)', C4:'LEFT(B4,3)' } ]]></excel>

用途：

• 拼接展示字段
• 生成辅助列
• 模板化字符串处理

7.4 日期处理

<excelname="exportDateCase"returnClass="void"><![CDATA[ EXPORT WITH SHEET="学生表", HEADER=true, MAPPING={ "enrollmentDate":"入学时间" }, FORMULAS={ B2:'YEAR(A2)', B3:'MONTH(A3)', B4:'DAY(A4)', B5:'TODAY()', B6:'NOW()' } ]]></excel>

用途：

• 提取年月日
• 输出系统日期/时间
• 给时间相关报表增加辅助分析列

7.5 整行 / 整列批量套公式

整行公式

<excelname="exportRowFormula"returnClass="void"><![CDATA[ EXPORT WITH SHEET="学生表", HEADER=true, FORMULAS={ ROW 6:'TODAY()' } ]]></excel>

含义：

• 给第 6 行所有已使用列写入TODAY()

行范围公式

<excelname="exportRowRangeFormula"returnClass="void"><![CDATA[ EXPORT WITH SHEET="学生表", HEADER=true, FORMULAS={ ROW 6..8:'NOW()' } ]]></excel>

整列公式

<excelname="exportColFormula"returnClass="void"><![CDATA[ EXPORT WITH SHEET="学生表", HEADER=true, FORMULAS={ COL G:'IF(D2>20,"Yes","No")' } ]]></excel>

列范围公式

<excelname="exportColRangeFormula"returnClass="void"><![CDATA[ EXPORT WITH SHEET="学生表", HEADER=true, FORMULAS={ COL G..I:'TODAY()' } ]]></excel>

列 / 行批量公式更适合“统一模板公式”。如果不同单元格的引用关系各不相同，建议直接写单元格公式。

8. FORMULAS 与 MAPPING / TRANSFORM / STYLE 的关系

8.1 与 MAPPING 的关系

MAPPING只影响：

• 表头显示名称
• 数据字段与列标题的映射关系

FORMULAS不按字段名定位，而是按Excel 坐标定位：

• D5
• ROW 5
• COL D

所以：

MAPPING 决定表头叫什么 FORMULAS 决定往哪个坐标写公式

8.2 与 TRANSFORM 的关系

TRANSFORM是在写数据单元格时对值做预处理。

FORMULAS是数据写完之后，再给某些位置写入公式。

因此两者职责不同：

8.3 与 STYLE 的关系

执行顺序上，FORMULAS先于STYLE。

这意味着：

• 先把公式写进去
• 再把样式应用到对应单元格

通常这是好事，因为公式单元格最终也能被样式覆盖。

但要注意：

• 如果样式涉及整行、整列、范围回写
• 又配合流式导出
• 就可能触发 SXSSF 已刷盘行不可回写问题

这一点在大数据量导出时尤其需要注意。

9. FORMULAS 与 SXSSF 流式导出的限制

这是FORMULAS最重要的限制之一。

jquick-excel 为支持大数据量导出，引入了 SXSSF 流式写入。但FORMULAS 往往需要“回头修改某个单元格”，这与流式写入机制天然冲突。

典型问题：

Attempting to write a row[...] that is already written to disk

当前框架已经做了保护：

• 当配置中存在FORMULAS、STYLE、MERGE、GRAPH等需要随机访问行的能力时
• 会通过needsRandomRowAccess(config)自动禁用 SXSSF
• 回退到XSSFWorkbook

这就是为什么带FORMULAS的导出通常不会走纯流式模式。

你需要记住两个结论：

结论一：FORMULAS 与纯流式大批量导出并不天然兼容

尤其是这种写法：

FORMULAS={ D5:'ABS(D2)' }

如果前面的数据行已经被刷盘，再回写D5就会报错。

结论二：大数据量场景下，优先考虑两种方案

方案 A：关闭流式导出

适合：

• 公式较多
• 模板较复杂
• 需要回写指定位置

方案 B：减少需要回头修改的公式设计

适合：

• 超大数据量
• 对内存敏感
• 更看重吞吐而不是复杂报表能力

例如：

• 避免在前部单元格写依赖后续数据的公式
• 尽量把汇总公式放到最后一行
• 能在 Java 里提前算好的值，尽量用TRANSFORM或业务代码先算好

10. 常见问题与避坑指南

10.1 公式一定要加引号吗？

建议加，并统一使用单引号：

D5:'SUM(D2:D4)'

这样与 DSL 语法最稳定，也更符合现有解析器习惯。

10.2 可以直接写 Excel 原生公式吗？

可以。只要最终是 Excel / POI 可识别的公式字符串，通常都可以尝试。

例如：

FORMULAS={ G2:'VLOOKUP(A2,$M$2:$N$10,2,FALSE)' }

10.3 FORMULAS 写的是结果值还是公式本身？

写的是公式本身，不是计算结果。

比如：

D5:'SUM(D2:D4)'

最终写进单元格的是SUM(D2:D4)，不是一个已经算好的数字。

10.4 FORMULAS 里的坐标从 0 开始还是从 1 开始？

按 Excel 习惯，从1 开始。

• A1表示第 1 行第 1 列
• D5表示第 5 行第 4 列
• ROW 5表示第 5 行

10.5 有表头时，公式坐标怎么算？

有表头时：

• 第 1 行是表头
• 数据通常从第 2 行开始

所以如果你有 3 行数据，常见统计位置会是：

D2:D4 → 数据区 D5 → 汇总区

10.6 为什么列公式里引用D2，但应用到整列时看起来不一定“自动变”成 D3 / D4？

因为框架做的事情是：把同一个公式字符串写到每个单元格。

也就是说，如果你写：

COL G:'IF(D2>20,"Yes","No")'

框架会把完全相同的字符串写到这一列的多个单元格中，而不是自动帮你把D2改成D3、D4。

因此：

• 如果你需要相对引用自动递增，请先验证 Excel / POI 的表现是否符合预期
• 更稳妥的方式是按单元格逐个写，或者在 Java 端生成不同坐标的公式配置

10.7 为什么我用了 FORMULAS 后流式导出没生效？

因为框架检测到FORMULAS需要随机访问单元格，会自动关闭 SXSSF 流式写入，降级为 XSSF，以避免已刷盘行回写异常。

10.8 FORMULAS 适合所有大数据导出吗？

不适合。

如果是几十万行、上百万行场景，且公式多、样式多、合并多，推荐重新评估方案：

• 能前置计算的尽量前置到 Java 里
• 能减少回写的尽量减少
• 真正复杂模板报表优先保证正确性，再考虑性能

11. 推荐实践

站在程序员角度，推荐这样使用FORMULAS：

11.1 把 FORMULAS 用在“报表型导出”，不要滥用在“明细型海量导出”

适合：

• 汇总表
• 对账表
• 分析报表
• 模板化导出

不太适合：

• 超大明细表
• 极致吞吐导出
• 纯流式低内存导出

11.2 汇总公式尽量放在数据区后面

推荐：

FORMULAS={ D10001:'SUM(D2:D10000)' }

不推荐：

FORMULAS={ D2:'SUM(D3:D10000)' }

前者更符合导出顺序，也更容易理解和维护。

11.3 复杂逻辑优先在业务层算好，FORMULAS 用于增强 Excel 可用性

例如：

• 复杂业务规则判断先在 Java 算好
• Excel 中只保留最终展示或简单汇总公式

这样更稳，也更利于测试。

11.4 单元格公式优先于整列公式

如果公式引用关系比较复杂，优先使用：

D5:'SUM(D2:D4)' E5:'AVERAGE(E2:E4)'

而不是粗暴套整列，因为整列统一公式更容易出现引用不符合预期的问题。

11.5 先在 Excel 验证公式，再复制到 DSL

最实用的工作流：

1. 先在 Excel 里把公式调通
2. 确认引用范围无误
3. 再复制到FORMULAS中
4. 最后在导出结果里复核一次

相关源码位置（纯文本说明）：

• 公式配置解析：src/main/java/com/github/paohaijiao/visitor/JQuickExcelExportFormulateVisitor.java
• 公式应用入口：src/main/java/com/github/paohaijiao/handler/JExcelExportHandler.java
• 公式实例创建与写入：src/main/java/com/github/paohaijiao/formula/context/JExcelFormulaContext.java
• 内置公式枚举：src/main/java/com/github/paohaijiao/formula/enums/JFormulaEnums.java
• XML 示例：src/test/resources/jquick-excel.xml
• 测试示例：src/test/java/com/github/paohaijiao/export/formulate/