Write excel formula using Java Apache POI

Question

I've been searching around a lot for this but couldn't find a solution. Hope someone can help me here. I am using Apache POI to create a simple tool. Formulas will be inputted by user, and the result will be written on file. Im stuck at extending/filling the formula.

Suppose you have a simple excel formula:

IF(A2=B2,True,False)

A drag downwards on excel would result in:

IF(A2=B2,True,False)
IF(A3=B3,True,False)
IF(A4=B4,True,False)
IF(A5=B5,True,False)
         . .
          .

Now I want to do the same in my program. I will know which row to end. i just can't get the row index to increment. I have already done it manually (writing formula in program), but now I need to use this when formula is passed by user.

I have something like this for the manual part:

for (int ind = 2; ind < rownumb ; ind++)
{
sheet.getRow(i).createCell(12).setCellFormula("IF(K" + ind + "=L" + ind + ",FALSE,TRUE)");
}

Now the user will input:

 =IF(K=L, False, True) OR  =IF(K0=L0, False, True)

I want to add this formula auto-filling (incrementing) all the way till rownumb. How can I do this?

If there is no direct way, can someone suggest some other approach, however the requirement is that the formula will be passed by user.

Thanks.

Nikolas Nikolas · Accepted Answer · 2017-03-02T13:02:23

Excel does not store it's function "in a sheet's cell" but as a VBA function. In order to be able to properly utilize excel function yourself you will need to emulate that. Check this article about user defined functions, this might get you going.

Write excel formula using Java Apache POI

1 Answers