★ FEATURED ARTICLE JQuick-Excel 导出 FORMULAS 配置项使用手册本手册面向程序员专门讲解 jquick-excel 在 Excel 导出场景中的FORMULAS配置项包括语法结构、定位规则、支持的公式类型、与样式/流式写入的关系以及常见问题。文档示例统一采用 XML / DSL 声明式写法便于直接落地到jquick-excel.xml。更多示例: JQuick-Excel 官网示例 https://www.jquick.org/jquick-excel/#demo-formulas0005目录FORMULAS 是什么基础语法三种作用目标3.1 单元格公式3.2 行公式3.3 列公式最小可运行示例公式是如何执行的支持的公式类型6.1 数学类6.2 日期时间类6.3 字符串类6.4 逻辑类6.5 自定义公式常见配置示例7.1 统计汇总7.2 条件判断7.3 字符串处理7.4 日期处理7.5 整行 / 整列批量套公式FORMULAS 与 MAPPING / TRANSFORM / STYLE 的关系FORMULAS 与 SXSSF 流式导出的限制常见问题与避坑指南推荐实践1. FORMULAS 是什么FORMULAS用于在Excel 导出阶段给目标单元格写入公式。它解决的不是“Java 里先算好结果再写单元格”而是“把 Excel 公式本身写进工作表中让 Excel 打开文件后继续参与计算”。典型场景导出报表时增加汇总行如SUM(D2:D100)生成统计指标如AVERAGE(E2:E100)、MAX(F2:F100)给某一列批量套公式按条件输出结果如IF(D260,及格,不及格)使用 Excel 原生公式保留后续人工编辑与自动重算能力一句话理解TRANSFORM 是“导出前先算值” FORMULAS 是“把公式写进 Excel 让 Excel 自己算”2. 基础语法FORMULAS的配置结构如下FORMULAS { 目标: 公式表达式, 目标: 公式表达式, ... }在完整导出 DSL 中通常这样写excelnameexportExcelreturnClassvoid![CDATA[ EXPORT WITH SHEET学生表, HEADERtrue, MAPPING{ id:主键, name:姓名, gender:性别, age:年龄, enrollmentDate:入学时间, className:班级 }, FORMULAS{ D5:SUM(D2:D4) } ]]/excel说明部分说明FORMULAS固定关键字表示导出公式配置块目标可以是单元格、行、列公式表达式必须写成字符串形式建议使用单引号包裹多项配置使用英文逗号分隔3. 三种作用目标从源码解析器和导出处理逻辑看FORMULAS支持三类目标单元格公式给某一个单元格写公式行公式给某一整行或多行的每一列写同一个公式列公式给某一整列或多列的每一行写同一个公式3.1 单元格公式语法FORMULAS{ D5:SUM(D2:D4) }含义把SUM(D2:D4)写入D5D5是 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(D20,Yes,No), COL D..F:NOW() }含义COL D给 D 列所有已遍历到的行写入同一个公式COL D..F给 D 到 F 列所有已遍历到的行写入同一个公式适合场景某列批量生成计算结果对多个指标列统一套用某类公式注意列目标内部最终是按列号处理的写法建议使用COL D、COL E..G这种形式语义更清晰。4. 最小可运行示例下面给出一个最小可运行的导出配置excelnameexportWithFormulareturnClassvoid![CDATA[ EXPORT WITH SHEET学生表, HEADERtrue, MAPPING{ id:主键, name:姓名, gender:性别, age:年龄, enrollmentDate:入学时间, className:班级 }, FORMULAS{ D5:SUM(D2:D4) } ]]/excel这个配置的效果是第 1 行输出表头第 2~4 行输出数据第 5 行的D5写入SUM(D2:D4)也就是说D5会成为“年龄列汇总”位置。5. 公式是如何执行的从导出处理逻辑看FORMULAS的执行时机在写表头 → 写数据 → 应用公式 → 应用样式 → 应用合并 → 应用图表也就是说先把数据行写到工作表再根据FORMULAS找到目标位置调用 Apache POI 的cell.setCellFormula(...)写入公式因此它并不是把公式“算完再写值”而是把公式字符串注册到目标单元格中。这意味着Excel 打开文件后可继续重算公式结果会跟随引用单元格变化很适合报表、模板、财务类场景6. 支持的公式类型从JFormulaEnums和测试用例可确认框架内置支持多类公式并且对无法识别的内容会回退到自定义公式处理。6.1 数学类常见支持项公式示例ABSABS(D2)AVERAGEAVERAGE(D2:D4)COUNTCOUNT(D2:D4)MAXMAX(D2:D4)MINMIN(D2:D4)POWERPOWER(D2,2)RANDRAND()RANKRANK(D2,D2:D4)ROUNDROUND(D2,2)SQRTSQRT(D2)STDEVSTDEV(D2:D4)SUMSUM(D2:D4)示例FORMULAS{ D5:SUM(D2:D4), D6:AVERAGE(D2:D4), D7:MAX(D2:D4), D8:MIN(D2:D4) }6.2 日期时间类常见支持项公式示例NOWNOW()TODAYTODAY()DATEDATE(2025,8,8)DATETIMEDATETIME(2025,8,8,10,30,0)DAYDAY(E2)DAYSDAYS(E3,E2)EDATEEDATE(E2,1)EOMONTHEOMONTH(E2,0)HOURHOUR(E2)MINUTEMINUTE(E2)MONTHMONTH(E2)SECONDSECOND(E2)TIMETIME(10,30,0)WEEKDAYWEEKDAY(E2)WEEKNUMWEEKNUM(E2)WORKDAYWORKDAY(E2,5)NETWORKDAYSNETWORKDAYS(E2,E3)YEARYEAR(E2)示例FORMULAS{ E5:TODAY(), F5:NOW(), G5:YEAR(E2), H5:MONTH(E2) }6.3 字符串类常见支持项公式示例CONCATCONCAT(B2,F2)CONCATENATECONCATENATE(B2,-,F2)EXACTEXACT(B2,B3)FINDFIND(张,B2)LEFTLEFT(B2,1)LENLEN(B2)LOWERLOWER(B2)MIDMID(B2,1,2)PROPERPROPER(B2)REPLACEREPLACE(B2,1,1,李)RIGHTRIGHT(B2,1)SEARCHSEARCH(三,B2)SUBSTITUTESUBSTITUTE(B2,张,李)TEXTTEXT(D2,0)TRIMTRIM(B2)UPPERUPPER(B2)VALUEVALUE(D2)示例FORMULAS{ G2:CONCAT(B2,-,F2), G3:LEN(B3), G4:UPPER(B4) }6.4 逻辑类常见支持项公式示例IFIF(D220,成年,未成年)ANDAND(TRUE,FALSE)OROR(TRUE,FALSE)示例FORMULAS{ G2:IF(D218,成年,未成年), G3:AND(D218,D230), G4:OR(D220,D221) }6.5 自定义公式若公式内容不在内置枚举中框架会回退到JCustomFormula。这意味着大多数 Excel 原生公式只要最终能被 POI 接受也可以直接尝试写入FORMULAS{ H2:VLOOKUP(A2,$M$2:$N$10,2,FALSE) }适合场景使用框架未内置封装但 Excel 支持的函数复杂报表中自定义引用表达式迁移已有 Excel 模板中的公式建议先在 Excel 中验证公式本身可用再复制到FORMULAS中。7. 常见配置示例7.1 统计汇总excelnameexportStatisticsreturnClassvoid![CDATA[ EXPORT WITH SHEET学生表, HEADERtrue, 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 条件判断excelnameexportIfCasereturnClassvoid![CDATA[ EXPORT WITH SHEET学生表, HEADERtrue, MAPPING{ name:姓名, age:年龄 }, FORMULAS{ C2:IF(B218,成年,未成年), C3:IF(B318,成年,未成年), C4:IF(B418,成年,未成年) } ]]/excel用途根据年龄生成判定结果适合状态列、风险等级列、业务标签列7.3 字符串处理excelnameexportStringCasereturnClassvoid![CDATA[ EXPORT WITH SHEET学生表, HEADERtrue, MAPPING{ name:姓名, className:班级 }, FORMULAS{ C2:CONCAT(A2,-,B2), C3:LEN(A3), C4:LEFT(B4,3) } ]]/excel用途拼接展示字段生成辅助列模板化字符串处理7.4 日期处理excelnameexportDateCasereturnClassvoid![CDATA[ EXPORT WITH SHEET学生表, HEADERtrue, MAPPING{ enrollmentDate:入学时间 }, FORMULAS{ B2:YEAR(A2), B3:MONTH(A3), B4:DAY(A4), B5:TODAY(), B6:NOW() } ]]/excel用途提取年月日输出系统日期/时间给时间相关报表增加辅助分析列7.5 整行 / 整列批量套公式整行公式excelnameexportRowFormulareturnClassvoid![CDATA[ EXPORT WITH SHEET学生表, HEADERtrue, FORMULAS{ ROW 6:TODAY() } ]]/excel含义给第 6 行所有已使用列写入TODAY()行范围公式excelnameexportRowRangeFormulareturnClassvoid![CDATA[ EXPORT WITH SHEET学生表, HEADERtrue, FORMULAS{ ROW 6..8:NOW() } ]]/excel整列公式excelnameexportColFormulareturnClassvoid![CDATA[ EXPORT WITH SHEET学生表, HEADERtrue, FORMULAS{ COL G:IF(D220,Yes,No) } ]]/excel列范围公式excelnameexportColRangeFormulareturnClassvoid![CDATA[ EXPORT WITH SHEET学生表, HEADERtrue, FORMULAS{ COL G..I:TODAY() } ]]/excel列 / 行批量公式更适合“统一模板公式”。如果不同单元格的引用关系各不相同建议直接写单元格公式。8. FORMULAS 与 MAPPING / TRANSFORM / STYLE 的关系8.1 与 MAPPING 的关系MAPPING只影响表头显示名称数据字段与列标题的映射关系FORMULAS不按字段名定位而是按Excel 坐标定位D5ROW 5COL D所以MAPPING 决定表头叫什么 FORMULAS 决定往哪个坐标写公式8.2 与 TRANSFORM 的关系TRANSFORM是在写数据单元格时对值做预处理。FORMULAS是数据写完之后再给某些位置写入公式。因此两者职责不同配置项作用阶段作用对象TRANSFORM写数据时原始字段值FORMULAS写数据后Excel 单元格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(D220,Yes,No)框架会把完全相同的字符串写到这一列的多个单元格中而不是自动帮你把D2改成D3、D4。因此如果你需要相对引用自动递增请先验证 Excel / POI 的表现是否符合预期更稳妥的方式是按单元格逐个写或者在 Java 端生成不同坐标的公式配置10.7 为什么我用了 FORMULAS 后流式导出没生效因为框架检测到FORMULAS需要随机访问单元格会自动关闭 SXSSF 流式写入降级为 XSSF以避免已刷盘行回写异常。10.8 FORMULAS 适合所有大数据导出吗不适合。如果是几十万行、上百万行场景且公式多、样式多、合并多推荐重新评估方案能前置计算的尽量前置到 Java 里能减少回写的尽量减少真正复杂模板报表优先保证正确性再考虑性能11. 推荐实践站在程序员角度推荐这样使用FORMULAS11.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最实用的工作流先在 Excel 里把公式调通确认引用范围无误再复制到FORMULAS中最后在导出结果里复核一次相关源码位置纯文本说明公式配置解析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.javaXML 示例src/test/resources/jquick-excel.xml测试示例src/test/java/com/github/paohaijiao/export/formulate/
阅读完成 · 觉得有帮助？
