從基礎(chǔ)到高級(jí)詳解Java讀寫Excel公式的實(shí)戰(zhàn)指南
做數(shù)據(jù)處理的朋友應(yīng)該都遇到過(guò)這種場(chǎng)景：需要批量生成帶公式的Excel報(bào)表，或者讀取現(xiàn)有表格中的公式進(jìn)行二次計(jì)算。以前我都是手動(dòng)在Excel里寫公式，后來(lái)發(fā)現(xiàn)用Java代碼來(lái)處理更高效，尤其是數(shù)據(jù)量大的時(shí)候。
今天整理一下平時(shí)用得比較多的幾種Excel公式處理方式，希望能給有同樣需求的朋友一些參考。
環(huán)境準(zhǔn)備
使用的庫(kù)
本文示例使用的是 Spire.XLS for Java，這是一個(gè)專門處理Excel文件的Java庫(kù)。如果你項(xiàng)目中已經(jīng)在用Apache POI，也可以實(shí)現(xiàn)類似功能，不過(guò)API會(huì)有些不同。
安裝方式
Maven項(xiàng)目，在 pom.xml 中添加依賴：
<repositories>
<repository>
<id>com.e-iceblue</id>
<name>e-iceblue</name>
<url>https://repo.e-iceblue.cn/repository/maven-public/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>e-iceblue</groupId>
<artifactId>spire.xls</artifactId>
<version>14.12.0</version>
</dependency>
</dependencies>Gradle項(xiàng)目，在 build.gradle 中添加：
repositories {
maven {
url 'https://repo.e-iceblue.cn/repository/maven-public/'
}
}
dependencies {
implementation 'e-iceblue:spire.xls:14.12.0'
}或者直接下載JAR包從官網(wǎng)導(dǎo)入項(xiàng)目。
一、最基礎(chǔ)的：寫入和讀取公式
1. 寫入常見公式
最常見的就是往單元格里寫公式了。比如SUM、AVERAGE這些統(tǒng)計(jì)函數(shù)：
import com.spire.xls.*;
public class WriteFormulas {
public static void main(String[] args) {
// 創(chuàng)建工作簿
Workbook workbook = new Workbook();
// 獲取第一個(gè)工作表
Worksheet sheet = workbook.getWorksheets().get(0);
// 準(zhǔn)備測(cè)試數(shù)據(jù)
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("文件已生成！");
}
}
注意一個(gè)小細(xì)節(jié)：如果想在單元格里顯示公式文本而不是計(jì)算結(jié)果，需要在前面加個(gè)單引號(hào)：
// 這樣單元格會(huì)顯示 "=SUM(B2:D2)" 這個(gè)文本
sheet.getCellRange("A2").setText("'=SUM(B2:D2)");
2. 讀取已有公式
有時(shí)候需要讀取現(xiàn)成Excel文件里的公式，看看是怎么計(jì)算的：
Workbook workbook = new Workbook();
workbook.loadFromFile("existing.xlsx");
Worksheet sheet = workbook.getWorksheets().get(0);
// 獲取公式字符串
String formula = sheet.getCellRange("C14").getFormula();
System.out.println("公式: " + formula);
// 獲取公式計(jì)算結(jié)果
double value = sheet.getCellRange("C14").getFormulaNumberValue();
System.out.println("結(jié)果: " + value);
這個(gè)功能在做公式審計(jì)或者模板分析時(shí)特別有用。
二、跨工作表引用
實(shí)際項(xiàng)目中經(jīng)常需要跨Sheet引用數(shù)據(jù)，寫法跟Excel里一樣：
// 引用Sheet1的B3單元格
sheet.getCellRange("A1").setFormula("=Sheet1!$B$3");
// 引用某個(gè)區(qū)域的平均值
sheet.getCellRange("A2").setFormula("=AVERAGE(Sheet1!$D$3:G$3)");
絕對(duì)引用（帶$符號(hào)）和相對(duì)引用的區(qū)別一定要搞清楚，不然復(fù)制公式時(shí)容易出錯(cuò)。
三、日期和時(shí)間函數(shù)
處理時(shí)間相關(guān)的報(bào)表時(shí)，這幾個(gè)函數(shù)很實(shí)用：
// 當(dāng)前日期時(shí)間
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())");
// 提取時(shí)、分、秒
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()函數(shù)每次打開文件都會(huì)重新計(jì)算，如果需要固定時(shí)間，建議計(jì)算后轉(zhuǎn)成靜態(tài)值。
四、數(shù)學(xué)和統(tǒng)計(jì)函數(shù)
除了基本的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)"); // 結(jié)果: 3.14
// 取整
sheet.getCellRange("A4").setFormula("=INT(9.8)"); // 結(jié)果: 9
// 絕對(duì)值
sheet.getCellRange("A5").setFormula("=ABS(-15.6)"); // 結(jié)果: 15.6
// 平方根
sheet.getCellRange("A6").setFormula("=SQRT(144)"); // 結(jié)果: 12
// 隨機(jī)數(shù)
sheet.getCellRange("A7").setFormula("=RAND()"); // 0-1之間的隨機(jī)數(shù)
五、邏輯函數(shù)
條件判斷在報(bào)表中很常見：
// IF函數(shù)
sheet.getCellRange("A1").setFormula("=IF(B1>60, \"及格\", \"不及格\")");
// AND、OR
sheet.getCellRange("A2").setFormula("=AND(B1>60, C1>60)");
sheet.getCellRange("A3").setFormula("=OR(B1>90, C1>90)");
// NOT
sheet.getCellRange("A4").setFormula("=NOT(TRUE)"); // 結(jié)果: FALSE
實(shí)際應(yīng)用：用IF嵌套來(lái)做成績(jī)等級(jí)劃分：
"=IF(A1>=90, \"優(yōu)秀\", IF(A1>=80, \"良好\", IF(A1>=60, \"及格\", \"不及格\")))"
六、文本函數(shù)
處理字符串時(shí)也少不了公式：
// 字符串長(zhǎng)度
sheet.getCellRange("A1").setFormula("=LEN(\"Hello World\")"); // 結(jié)果: 11
// 截取子串
sheet.getCellRange("A2").setFormula("=MID(\"Hello World\", 7, 5)"); // 結(jié)果: World
// 類型轉(zhuǎn)換
sheet.getCellRange("A3").setFormula("=VALUE(\"123\")"); // 文本轉(zhuǎn)數(shù)字
七、數(shù)組公式（高級(jí)用法）
數(shù)組公式可以一次性對(duì)多個(gè)值進(jìn)行計(jì)算，適合復(fù)雜的數(shù)據(jù)分析：
// 準(zhǔn)備數(shù)據(jù)
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);
// 設(shè)置數(shù)組公式（線性回歸）
sheet.getCellRange("A5:C6").setFormulaArray("=LINEST(A1:A3,B1:B3,TRUE,TRUE)");
// 計(jì)算公式值
workbook.calculateAllValue();
使用場(chǎng)景：財(cái)務(wù)分析、統(tǒng)計(jì)建模時(shí)會(huì)用到這類高級(jí)函數(shù)。
八、不依賴Excel直接計(jì)算公式值
有時(shí)候不需要生成Excel文件，只是想算個(gè)公式的結(jié)果，可以直接計(jì)算：
Workbook workbook = new Workbook();
// 直接計(jì)算公式的值
Object result1 = workbook.calculateFormulaValue("=10+20*3");
System.out.println(result1); // 結(jié)果: 70
Object result2 = workbook.calculateFormulaValue("=SUM(1,2,3,4,5)");
System.out.println(result2); // 結(jié)果: 15
// 甚至可以引用單元格
workbook.getWorksheets().get(0).getCellRange("A1").setNumberValue(100);
Object result3 = workbook.calculateFormulaValue("=A1*2");
System.out.println(result3); // 結(jié)果: 200
這個(gè)功能在做快速計(jì)算或者公式驗(yàn)證時(shí)很方便，不用真的創(chuàng)建Excel文件。
九、移除公式保留計(jì)算結(jié)果
有個(gè)實(shí)際需求：給客戶發(fā)報(bào)表時(shí)，只想給他們看最終數(shù)據(jù)，不想暴露計(jì)算公式。這時(shí)候可以把公式轉(zhuǎn)成靜態(tài)值：
Workbook workbook = new Workbook();
workbook.loadFromFile("with_formulas.xlsx");
for (Worksheet sheet : (Iterable<Worksheet>) workbook.getWorksheets()) {
for (CellRange cell : (Iterable<CellRange>) sheet.getRange()) {
if (cell.hasFormula()) {
// 獲取公式計(jì)算結(jié)果
Object value = cell.getFormulaValue();
// 清除公式
cell.clear(ExcelClearOptions.ClearContent);
// 設(shè)置為靜態(tài)值
cell.setValue(value.toString());
}
}
}
workbook.saveToFile("without_formulas.xlsx", ExcelVersion.Version2013);
應(yīng)用場(chǎng)景：財(cái)務(wù)報(bào)表、對(duì)外發(fā)布的統(tǒng)計(jì)數(shù)據(jù)等需要保護(hù)公式邏輯的場(chǎng)景。
十、Excel 2013+的新函數(shù)
新版Excel增加了一些實(shí)用函數(shù)，比如位運(yùn)算、URL編碼等：
// 位運(yùn)算
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周數(shù)
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)");
這些函數(shù)在處理特定業(yè)務(wù)邏輯時(shí)很有用，比如網(wǎng)絡(luò)應(yīng)用開發(fā)中的URL處理。
十一、命名范圍中使用公式
如果公式里用到的區(qū)域經(jīng)常變化，可以用命名范圍來(lái)簡(jiǎn)化：
// 定義命名范圍
Name name = workbook.getNameList().add("SalesData");
name.setRefersToRange(sheet.getCellRange("A1:A100"));
// 在公式中使用命名范圍
sheet.getCellRange("B1").setFormula("=SUM(SalesData)");
這樣做的好處是，當(dāng)數(shù)據(jù)區(qū)域擴(kuò)展時(shí)，只需要修改命名范圍的定義，不用改所有公式。
十二、R1C1引用樣式
除了常見的A1樣式，Excel還支持R1C1引用方式（行號(hào)+列號(hào)）：
// R1C1樣式的公式
sheet.getCellRange("C3").setR1C1Formula("=R[-1]C[-1]+R[-1]C[0]");
// 意思是：上一行左邊一格 + 上一行當(dāng)前列
// 數(shù)組形式的R1C1公式
sheet.getCellRange("D3:E4").setR1C1FormulaArray("=R[-2]C[-3]:R[-1]C[-2]");
什么時(shí)候用：在程序化生成公式時(shí)，R1C1方式更容易通過(guò)坐標(biāo)計(jì)算來(lái)動(dòng)態(tài)構(gòu)建公式。
十三、自定義函數(shù)（加載項(xiàng)函數(shù)）
如果遇到Excel內(nèi)置函數(shù)不夠用的情況，可以注冊(cè)自定義函數(shù)：
// 注冊(cè)加載項(xiàng)函數(shù)庫(kù)
workbook.registerAddInFunction("MyFunctions.xll");
// 然后就可以像普通函數(shù)一樣使用
sheet.getCellRange("A1").setFormula("=MYCUSTOMFUNC(B1,C1)");
適用場(chǎng)景：有特殊計(jì)算需求的行業(yè)，比如金融衍生品定價(jià)、工程計(jì)算等。
十四、SUBTOTAL函數(shù)（忽略隱藏行）
做數(shù)據(jù)篩選時(shí)，普通的SUM會(huì)把隱藏行也算進(jìn)去，用SUBTOTAL可以避免這個(gè)問(wèn)題：
// 第一個(gè)參數(shù)3表示COUNTA，只統(tǒng)計(jì)可見單元格
sheet.getCellRange("A1").setFormula("=SUBTOTAL(3, B2:E100)");
常用功能代碼：
- 1: AVERAGE
- 2: COUNT
- 3: COUNTA
- 9: SUM
- 109: SUM（忽略隱藏值）
十五、實(shí)際項(xiàng)目中的綜合應(yīng)用
最后分享一個(gè)實(shí)際場(chǎng)景：生成月度銷售報(bào)表。
Workbook workbook = new Workbook();
Worksheet sheet = workbook.getWorksheets().get(0);
// 1. 寫入標(biāo)題
sheet.getCellRange("A1").setValue("月份");
sheet.getCellRange("B1").setValue("銷售額");
sheet.getCellRange("C1").setValue("成本");
sheet.getCellRange("D1").setValue("利潤(rùn)");
sheet.getCellRange("E1").setValue("利潤(rùn)率");
// 2. 寫入數(shù)據(jù)并添加公式
for (int i = 2; i <= 13; i++) {
sheet.getCellRange("A" + i).setValue((i-1) + "月");
sheet.getCellRange("B" + i).setNumberValue(Math.random() * 100000);
sheet.getCellRange("C" + i).setNumberValue(Math.random() * 60000);
// 利潤(rùn) = 銷售額 - 成本
sheet.getCellRange("D" + i).setFormula("=B" + i + "-C" + i);
// 利潤(rùn)率 = 利潤(rùn) / 銷售額
sheet.getCellRange("E" + i).setFormula("=D" + i + "/B" + i);
sheet.getCellRange("E" + i).getCellStyle().setNumberFormat("0.00%");
}
// 3. 添加匯總行
int lastRow = 14;
sheet.getCellRange("A" + lastRow).setValue("合計(jì)");
sheet.getCellRange("B" + lastRow).setFormula("=SUM(B2:B13)");
sheet.getCellRange("C" + lastRow).setFormula("=SUM(C2:C13)");
sheet.getCellRange("D" + lastRow).setFormula("=SUM(D2:D13)");
sheet.getCellRange("E" + lastRow).setFormula("=AVERAGE(E2:E13)");
// 4. 設(shè)置格式
sheet.getAllocatedRange().autoFitColumns();
sheet.getCellRange("A1:E1").getCellStyle().getExcelFont().isBold(true);
workbook.saveToFile("monthly_report.xlsx", ExcelVersion.Version2013);
這樣一個(gè)完整的報(bào)表就生成了，所有計(jì)算都是通過(guò)公式完成的，后續(xù)修改原始數(shù)據(jù)，結(jié)果會(huì)自動(dòng)更新。
小結(jié)
總結(jié)一下幾個(gè)關(guān)鍵點(diǎn)：
- 簡(jiǎn)單公式直接用
setFormula()，跟Excel里寫法一致 - 跨表引用用
SheetName!CellAddress格式 - 數(shù)組公式用
setFormulaArray()，記得調(diào)用calculateAllValue() - 只要計(jì)算結(jié)果不要公式，遍歷單元格轉(zhuǎn)換
- 直接計(jì)算公式值用
calculateFormulaValue()，不用創(chuàng)建文件 - 新函數(shù)如位運(yùn)算、URL編碼等，注意Excel版本兼容性
實(shí)際使用中，最重要的是理解業(yè)務(wù)需求，選擇合適的公式類型。不是所有場(chǎng)景都需要復(fù)雜的公式，有時(shí)候簡(jiǎn)單的SUM、IF就能解決問(wèn)題。
到此這篇關(guān)于從基礎(chǔ)到高級(jí)詳解Java讀寫Excel公式的實(shí)戰(zhàn)指南的文章就介紹到這了,更多相關(guān)Java讀寫Excel公式內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了在java上使用亞馬遜云儲(chǔ)存方法,首先寫一個(gè)配置類,寫一個(gè)controller接口調(diào)用方法存儲(chǔ)文件,本文結(jié)合示例代碼給大家介紹的非常詳細(xì),需要的朋友參考下吧2024-01-01
避免sql注入_動(dòng)力節(jié)點(diǎn)Java學(xué)院整理
這篇文章主要介紹了避免sql注入，小編覺得挺不錯(cuò)的，現(xiàn)在分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2017-08-08
詳解Java中static關(guān)鍵字和內(nèi)部類的使用
這篇文章主要為大家詳細(xì)介紹了Java中static關(guān)鍵字和內(nèi)部類的使用，文中的示例代碼講解詳細(xì)，感興趣的小伙伴可以跟隨小編一起學(xué)習(xí)一下2022-08-08
ThreadLocal用來(lái)提供線程內(nèi)部的局部變量，不同的線程之間不會(huì)相互干擾，這種變量在多線程環(huán)境下訪問(wèn)時(shí)能保證各個(gè)線程的變量相對(duì)獨(dú)立于其他線程內(nèi)的變量，在線程的生命周期內(nèi)起作用，可以減少同一個(gè)線程內(nèi)多個(gè)函數(shù)或組件之間一些公共變量傳遞的復(fù)雜度2022-08-08
這篇文章主要介紹了將JavaDoc注釋生成API文檔的操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2021-11-11
利用idea快速搭建一個(gè)spring-cloud(圖文)
本文主要介紹了idea快速搭建一個(gè)spring-cloud，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2022-07-07
使用IDEA配置Tomcat和連接MySQL數(shù)據(jù)庫(kù)（JDBC）詳細(xì)步驟
這篇文章主要介紹了使用IDEA配置Tomcat和連接MySQL數(shù)據(jù)庫(kù)（JDBC）詳細(xì)步驟,本文通過(guò)圖文并茂的形式給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2020-12-12
Java 添加、更新和移除PDF超鏈接的實(shí)現(xiàn)方法
PDF超鏈接用一個(gè)簡(jiǎn)單的鏈接包含了大量的信息，滿足了人們?cè)诓徽加锰嗫臻g的情況下渲染外部信息的需求。這篇文章主要介紹了Java 添加、更新和移除PDF超鏈接的實(shí)現(xiàn)方法,需要的朋友可以參考下2019-05-05
Java優(yōu)選算法之位運(yùn)算實(shí)戰(zhàn)例子
這篇文章主要介紹了Java優(yōu)選算法之位運(yùn)算的相關(guān)資料,位運(yùn)算基礎(chǔ)包括左移、右移、取反、按位與、按位或、異或等操作,文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2025-11-11
Java中ArrayIndexOutOfBoundsException 異常報(bào)錯(cuò)的解決方案
本文主要介紹了Java中ArrayIndexOutOfBoundsException 異常報(bào)錯(cuò)的解決方案，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-06-06