In order to manipulate formulas in Apache POI, we use the method
HSSFCell class. Similar to Microsoft Excel, we can use mathematics formulas (A1+B2+C3, B5*A2, …) and Excel’s functions (SUM, COUNT, MAX, …).
When we use excel functions, we must use the original name of the function. Microsoft Excel has translations for functions name, so when an user opens the document in Microsoft Excel, the name of the functions are shown in the user language. However internally Excel stores the English function names. This is why we need to use this original names to manipulate the excel file from Apache POI.
You can see the list of the excel functions supported by Apache POI on the next link (Appendix A):
You get all info about Apache POI formula and functions usage on the project official site.
To accompany this post, we have a simple program, that shows an example of a purchase order. The example generates an excel file like this:
You can download the example source code from my GitHub repository (project Poi-ExcelWithFormula).