Java 读写 Excel 公式：从基础到高级的实战总结

做数据处理的朋友应该都遇到过这种场景需要批量生成带公式的Excel报表或者读取现有表格中的公式进行二次计算。以前我都是手动在Excel里写公式后来发现用Java代码来处理更高效尤其是数据量大的时候。今天整理一下平时用得比较多的几种Excel公式处理方式希望能给有同样需求的朋友一些参考。环境准备使用的库本文示例使用的是Spire.XLS for Java这是一个专门处理Excel文件的Java库。如果你项目中已经在用Apache POI也可以实现类似功能不过API会有些不同。安装方式Maven项目在pom.xml中添加依赖repositoriesrepositoryidcom.e-iceblue/idnamee-iceblue/nameurlhttps://repo.e-iceblue.cn/repository/maven-public//url/repository/repositoriesdependenciesdependencygroupIde-iceblue/groupIdartifactIdspire.xls/artifactIdversion14.12.0/version/dependency/dependenciesGradle项目在build.gradle中添加repositories { maven { url https://repo.e-iceblue.cn/repository/maven-public/ } } dependencies { implementation e-iceblue:spire.xls:14.12.0 }或者直接下载JAR包从官网导入项目。一、最基础的写入和读取公式1. 写入常见公式最常见的就是往单元格里写公式了。比如SUM、AVERAGE这些统计函数importcom.spire.xls.*;publicclassWriteFormulas{publicstaticvoidmain(String[]args){// 创建工作簿WorkbookworkbooknewWorkbook();// 获取第一个工作表Worksheetsheetworkbook.getWorksheets().get(0);// 准备测试数据sheet.getCellRange(B2).setNumberValue(7.3);sheet.getCellRange(C2).setNumberValue(5);sheet.getCellRange(D2).setNumberValue(8.2);// 写入求和公式sheet.getCellRange(E2).setFormula(SUM(B2:D2));// 写入平均值公式sheet.getCellRange(F2).setFormula(AVERAGE(B2:D2));// 保存文件workbook.saveToFile(result.xlsx,ExcelVersion.Version2013);// 释放资源workbook.dispose();System.out.println(文件已生成);}}注意一个小细节如果想在单元格里显示公式文本而不是计算结果需要在前面加个单引号// 这样单元格会显示 SUM(B2:D2) 这个文本sheet.getCellRange(A2).setText(SUM(B2:D2));2. 读取已有公式有时候需要读取现成Excel文件里的公式看看是怎么计算的WorkbookworkbooknewWorkbook();workbook.loadFromFile(existing.xlsx);Worksheetsheetworkbook.getWorksheets().get(0);// 获取公式字符串Stringformulasheet.getCellRange(C14).getFormula();System.out.println(公式: formula);// 获取公式计算结果doublevaluesheet.getCellRange(C14).getFormulaNumberValue();System.out.println(结果: value);这个功能在做公式审计或者模板分析时特别有用。二、跨工作表引用实际项目中经常需要跨Sheet引用数据写法跟Excel里一样// 引用Sheet1的B3单元格sheet.getCellRange(A1).setFormula(Sheet1!$B$3);// 引用某个区域的平均值sheet.getCellRange(A2).setFormula(AVERAGE(Sheet1!$D$3:G$3));绝对引用带$符号和相对引用的区别一定要搞清楚不然复制公式时容易出错。三、日期和时间函数处理时间相关的报表时这几个函数很实用// 当前日期时间sheet.getCellRange(A1).setFormula(NOW());sheet.getCellRange(A1).getCellStyle().setNumberFormat(yyyy-MM-DD HH:mm:ss);// 提取年、月、日sheet.getCellRange(B1).setFormula(YEAR(TODAY()));sheet.getCellRange(C1).setFormula(MONTH(TODAY()));sheet.getCellRange(D1).setFormula(DAY(TODAY()));// 提取时、分、秒sheet.getCellRange(E1).setFormula(HOUR(NOW()));sheet.getCellRange(F1).setFormula(MINUTE(NOW()));sheet.getCellRange(G1).setFormula(SECOND(NOW()));// 星期几sheet.getCellRange(H1).setFormula(WEEKDAY(TODAY()));NOW()函数每次打开文件都会重新计算如果需要固定时间建议计算后转成静态值。四、数学和统计函数除了基本的SUM、AVERAGE还有一些常用的// 最大值、最小值sheet.getCellRange(A1).setFormula(MAX(10,30,50));sheet.getCellRange(A2).setFormula(MIN(5,7,3));// 四舍五入sheet.getCellRange(A3).setFormula(ROUND(3.14159, 2));// 结果: 3.14// 取整sheet.getCellRange(A4).setFormula(INT(9.8));// 结果: 9// 绝对值sheet.getCellRange(A5).setFormula(ABS(-15.6));// 结果: 15.6// 平方根sheet.getCellRange(A6).setFormula(SQRT(144));// 结果: 12// 随机数sheet.getCellRange(A7).setFormula(RAND());// 0-1之间的随机数五、逻辑函数条件判断在报表中很常见// IF函数sheet.getCellRange(A1).setFormula(IF(B160, \及格\, \不及格\));// AND、ORsheet.getCellRange(A2).setFormula(AND(B160, C160));sheet.getCellRange(A3).setFormula(OR(B190, C190));// NOTsheet.getCellRange(A4).setFormula(NOT(TRUE));// 结果: FALSE实际应用用IF嵌套来做成绩等级划分IF(A190, \优秀\, IF(A180, \良好\, IF(A160, \及格\, \不及格\)))六、文本函数处理字符串时也少不了公式// 字符串长度sheet.getCellRange(A1).setFormula(LEN(\Hello World\));// 结果: 11// 截取子串sheet.getCellRange(A2).setFormula(MID(\Hello World\, 7, 5));// 结果: World// 类型转换sheet.getCellRange(A3).setFormula(VALUE(\123\));// 文本转数字七、数组公式高级用法数组公式可以一次性对多个值进行计算适合复杂的数据分析// 准备数据sheet.getCellRange(A1).setNumberValue(1);sheet.getCellRange(A2).setNumberValue(2);sheet.getCellRange(A3).setNumberValue(3);sheet.getCellRange(B1).setNumberValue(4);sheet.getCellRange(B2).setNumberValue(5);sheet.getCellRange(B3).setNumberValue(6);// 设置数组公式线性回归sheet.getCellRange(A5:C6).setFormulaArray(LINEST(A1:A3,B1:B3,TRUE,TRUE));// 计算公式值workbook.calculateAllValue();使用场景财务分析、统计建模时会用到这类高级函数。八、不依赖Excel直接计算公式值有时候不需要生成Excel文件只是想算个公式的结果可以直接计算WorkbookworkbooknewWorkbook();// 直接计算公式的值Objectresult1workbook.calculateFormulaValue(1020*3);System.out.println(result1);// 结果: 70Objectresult2workbook.calculateFormulaValue(SUM(1,2,3,4,5));System.out.println(result2);// 结果: 15// 甚至可以引用单元格workbook.getWorksheets().get(0).getCellRange(A1).setNumberValue(100);Objectresult3workbook.calculateFormulaValue(A1*2);System.out.println(result3);// 结果: 200这个功能在做快速计算或者公式验证时很方便不用真的创建Excel文件。九、移除公式保留计算结果有个实际需求给客户发报表时只想给他们看最终数据不想暴露计算公式。这时候可以把公式转成静态值WorkbookworkbooknewWorkbook();workbook.loadFromFile(with_formulas.xlsx);for(Worksheetsheet:(IterableWorksheet)workbook.getWorksheets()){for(CellRangecell:(IterableCellRange)sheet.getRange()){if(cell.hasFormula()){// 获取公式计算结果Objectvaluecell.getFormulaValue();// 清除公式cell.clear(ExcelClearOptions.ClearContent);// 设置为静态值cell.setValue(value.toString());}}}workbook.saveToFile(without_formulas.xlsx,ExcelVersion.Version2013);应用场景财务报表、对外发布的统计数据等需要保护公式逻辑的场景。十、Excel 2013的新函数新版Excel增加了一些实用函数比如位运算、URL编码等// 位运算sheet.getCellRange(A1).setFormula(BITOR(23,10));// 按位或sheet.getCellRange(A2).setFormula(BITAND(23,10));// 按位与sheet.getCellRange(A3).setFormula(BITLSHIFT(23,2));// 左移sheet.getCellRange(A4).setFormula(BITRSHIFT(23,2));// 右移// URL编码sheet.getCellRange(A5).setFormula(ENCODEURL(\https://example.com\));// ISO周数sheet.getCellRange(A6).setFormula(ISOWEEKNUM(DATE(2024,1,1)));// 精确舍入sheet.getCellRange(A7).setFormula(CEILING.PRECISE(-4.6, 3));sheet.getCellRange(A8).setFormula(FLOOR.MATH(12.758, 2, -1));这些函数在处理特定业务逻辑时很有用比如网络应用开发中的URL处理。十一、命名范围中使用公式如果公式里用到的区域经常变化可以用命名范围来简化// 定义命名范围Namenameworkbook.getNameList().add(SalesData);name.setRefersToRange(sheet.getCellRange(A1:A100));// 在公式中使用命名范围sheet.getCellRange(B1).setFormula(SUM(SalesData));这样做的好处是当数据区域扩展时只需要修改命名范围的定义不用改所有公式。十二、R1C1引用样式除了常见的A1样式Excel还支持R1C1引用方式行号列号// R1C1样式的公式sheet.getCellRange(C3).setR1C1Formula(R[-1]C[-1]R[-1]C[0]);// 意思是上一行左边一格 上一行当前列// 数组形式的R1C1公式sheet.getCellRange(D3:E4).setR1C1FormulaArray(R[-2]C[-3]:R[-1]C[-2]);什么时候用在程序化生成公式时R1C1方式更容易通过坐标计算来动态构建公式。十三、自定义函数加载项函数如果遇到Excel内置函数不够用的情况可以注册自定义函数// 注册加载项函数库workbook.registerAddInFunction(MyFunctions.xll);// 然后就可以像普通函数一样使用sheet.getCellRange(A1).setFormula(MYCUSTOMFUNC(B1,C1));适用场景有特殊计算需求的行业比如金融衍生品定价、工程计算等。十四、SUBTOTAL函数忽略隐藏行做数据筛选时普通的SUM会把隐藏行也算进去用SUBTOTAL可以避免这个问题// 第一个参数3表示COUNTA只统计可见单元格sheet.getCellRange(A1).setFormula(SUBTOTAL(3, B2:E100));常用功能代码1: AVERAGE2: COUNT3: COUNTA9: SUM109: SUM忽略隐藏值十五、实际项目中的综合应用最后分享一个实际场景生成月度销售报表。WorkbookworkbooknewWorkbook();Worksheetsheetworkbook.getWorksheets().get(0);// 1. 写入标题sheet.getCellRange(A1).setValue(月份);sheet.getCellRange(B1).setValue(销售额);sheet.getCellRange(C1).setValue(成本);sheet.getCellRange(D1).setValue(利润);sheet.getCellRange(E1).setValue(利润率);// 2. 写入数据并添加公式for(inti2;i13;i){sheet.getCellRange(Ai).setValue((i-1)月);sheet.getCellRange(Bi).setNumberValue(Math.random()*100000);sheet.getCellRange(Ci).setNumberValue(Math.random()*60000);// 利润 销售额 - 成本sheet.getCellRange(Di).setFormula(Bi-Ci);// 利润率 利润 / 销售额sheet.getCellRange(Ei).setFormula(Di/Bi);sheet.getCellRange(Ei).getCellStyle().setNumberFormat(0.00%);}// 3. 添加汇总行intlastRow14;sheet.getCellRange(AlastRow).setValue(合计);sheet.getCellRange(BlastRow).setFormula(SUM(B2:B13));sheet.getCellRange(ClastRow).setFormula(SUM(C2:C13));sheet.getCellRange(DlastRow).setFormula(SUM(D2:D13));sheet.getCellRange(ElastRow).setFormula(AVERAGE(E2:E13));// 4. 设置格式sheet.getAllocatedRange().autoFitColumns();sheet.getCellRange(A1:E1).getCellStyle().getExcelFont().isBold(true);workbook.saveToFile(monthly_report.xlsx,ExcelVersion.Version2013);这样一个完整的报表就生成了所有计算都是通过公式完成的后续修改原始数据结果会自动更新。小结总结一下几个关键点简单公式直接用setFormula()跟Excel里写法一致跨表引用用SheetName!CellAddress格式数组公式用setFormulaArray()记得调用calculateAllValue()只要计算结果不要公式遍历单元格转换直接计算公式值用calculateFormulaValue()不用创建文件新函数如位运算、URL编码等注意Excel版本兼容性实际使用中最重要的是理解业务需求选择合适的公式类型。不是所有场景都需要复杂的公式有时候简单的SUM、IF就能解决问题。希望这些经验对大家有帮助如果有其他好用的技巧欢迎交流