0

While writing excel code in java, I am currently looking for Standard Deviation Population formula in Apache Poi v4.1.1, which is not implemented hence exception is throwing, then appeared to have 2 solutions in my hand, #1) Implement custom formula #2) Tweak / Override existing formula

I am not sure how to override existing formula hence moved to solution 1, while doing this, I have some or the other issues

Problem: Let us say, in my excel, column B has 250 rows (dynamic no. of rows, in some cases 240 rows) and according to std dev formula, I need to first find the mean, so my query here is

Q #1) how to know or select the non-empty values (column or rows) to write my own custom code.?

FayazMd
  • 386
  • 2
  • 22

2 Answers2

0

The formula can be added as a string.

So it must not be implemented in Apache Poi, but must be implemented in the excel running your xlsx file.

You can simply add it as follow:

String strFormula= ...; // For example for a sum can be the string SUM(A1:A10)
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula(strFormula);
Davide Lorenzo MARINO
  • 26,420
  • 4
  • 39
  • 56
  • I did and code failed with an exception, NotImplemented, then I realized formula I have used was not in poi api, hence want to write custom class to my formula – FayazMd Apr 18 '19 at 20:35
0

I found solution that, instead of implementing Standard deviation population formula, in Apache POI, there is a separate class available for Standard Deviation, we need to use it.

This link also useful Standard deviation with Apache Commons Math

FayazMd
  • 386
  • 2
  • 22