In order to manipulate formulas in Apache POI, we use the method setCellFormula("formula") of 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.

Supported functions

You can see the list of the excel functions supported by Apache POI on the next link (Appendix A):

Apache POI: Developing Formula Evaluation

More info

You get all info about Apache POI formula and functions usage on the project official site.

Source code

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:

For Subtotal column (column D) it’s used a simple math formula (Bx*Cx). To show the total sum of the order (D22), we use the SUM excel function (SUM(D2:D21)).

You can download the example source code from my GitHub repository (project Poi-ExcelWithFormula).

Regards!