Apache POI-Excel公式與FormulaEvaluator

16th鐵人賽

團隊抓挖幫嘎

2024-10-12 10:38:57

141 瀏覽

分享至

公式是使用Excel最常使用的核心功能之一，Apache POI能夠在匯出Excel的時候為Cell建立公式，讓我們的Excel即使在資料有手動調整的情況下，也能動態計算出最新的結果。
這一篇就來設定Excel的公式，目前的學生成績資料表如下圖：

設定公式

我想在資料表的旁邊建立一個小結的資料，以公式計算各個課程的平均分數(如下圖)。

因此我在匯出學生成績資料表的方法中另外做下列設定：

getRow()： 要注意的是，一開始建立資料的時候，是用sheet.createRow()，但是我已經建立過學生成績資料表的資料了，在(第2~6列)Row已經被建立出來後，要使用sheet.getRow()取得該列資料，再進行後續處理。如果還是用sheet.createRow()會導致原先Row的資料被新創建的Row覆蓋掉，同理Cell的部分也是一樣，只是我的小結直行的位置(I和J行)確實是沒建立過Cell的資料，因此仍使用createCell()
setCellFormula()： 設定公式可以用cell.setCellFormula()，公式直接以字串作為參數就好，但是不要加上「=」，那是Excel本身的語法。在這個範例中，資料有對課程排序過，為了方便示範我就直接以AVERAGE()公式並指定好該課程的Cell座標範圍

// 建立小結欄位名稱
Row summaryRow = sheet.getRow(1);
Cell courseNameCell = summaryRow.createCell(8);
courseNameCell.setCellValue("課程名稱");
courseNameCell.setCellStyle(columnTitleStyle);

Cell averageScoreCell = summaryRow.createCell(9);
averageScoreCell.setCellValue("平均成績");
averageScoreCell.setCellStyle(columnTitleStyle);

// 國文
Row chineseRow = sheet.getRow(2);
Cell chineseCell = chineseRow.createCell(8);
chineseCell.setCellValue("國文");
Cell chineseAvgCell = chineseRow.createCell(9);
chineseAvgCell.setCellFormula("AVERAGE(F3:F127)");

// 數學
Row mathRow = sheet.getRow(3);
Cell mathCell = mathRow.createCell(8);
mathCell.setCellValue("數學");
Cell mathAvgCell = mathRow.createCell(9);
mathAvgCell.setCellFormula("AVERAGE(F128:F252)");

// 英文
Row englishRow = sheet.getRow(4);
Cell englishCell = englishRow.createCell(8);
englishCell.setCellValue("英文");
Cell englishAvgCell = englishRow.createCell(9);
englishAvgCell.setCellFormula("AVERAGE(F253:F377)");

// 體育
Row sportRow = sheet.getRow(5);
Cell sportCell = sportRow.createCell(8);
sportCell.setCellValue("體育");
Cell sportAvgCell = sportRow.createCell(9);
sportAvgCell.setCellFormula("AVERAGE(F378:F502)");

匯出的結果：

雖然上述的匯出結果看起來很正常，但如果當Excel文件是從網路下載或作為電子郵件附件接收到時，Windows會將這些文件標記為不安全的來源。因此當第一次打開這類文件時，Excel會以保護模式打開，並顯示「啟用編輯」按鈕。
這時候再檢查一下設定公式的Cell，會發現有可能什麼都沒顯示，或是顯示「#VALUE 」。

FormulaEvaluator

為什麼要使用FormulaEvaluator

當文件被保護模式打開時，為了避免可能存在的惡意攻擊在未經驗證的情況下執行，Excel不會自動更新公式的結果。
Excel為了使用者體驗，有對公式計算的結果進行快取，為了打開Excel時不用花時間計算每個包含公式的Cell，但是如此一來不論是否為保護模式，顯示的結果都可能不是最新計算的結果。

其實在點選「啟用編輯」之後公式就會計算出最新結果了，但有些user會希望一打開就能看到最新結果，這時候可以使用FormulaEvaluator。

After making changes with Apache POI to either Formula Cells themselves, or those that they depend on, you should normally perform a Formula Evaluation to have these "cached" results updated. This is normally done after all changes have been performed, but before you write the file out.

根據上述官方的說明，FormulaEvaluator執行的時機是在對Cell做完一切設定之後，匯出檔案之前，因此不會影響開啟Excel時的使用者體驗。

使用FormulaEvaluator後

FormulaEvaluator的使用非常簡單，只要以createFormulaEvaluator()創建之後，用FormulaEvaluator計算指定的Cell就好了。

// 計算公式
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
evaluator.evaluateFormulaCell(chineseAvgCell);
evaluator.evaluateFormulaCell(mathAvgCell);
evaluator.evaluateFormulaCell(englishAvgCell);
evaluator.evaluateFormulaCell(sportAvgCell);

這樣在匯出的Excel在保護模式下打開時，也能看到最新的公式計算結果了：