# How to get the calculated value from an input formula string ?

That’s a question to resolve the problem as example below :

I’ve got three cells as A1, B1 and C1

A1 contains value as 8 (A1 = 8)
B1 contains value as 10 (B1 = 10)

and:

C1 = A1 + B1

So, how can i get the calculated value as 18 from input formula string “A1 + B1” by jxl or POI

p.s: I could not get directly the pre-calculated value from cell C1 by jxl.

···

Hieu.HISPVietnam
Good Health !

in POI place formula like this: A1 + B1 . Note no need for equal = sign.

murod

···

From: Hieu Dang Duy hieu.hispvietnam@gmail.com
Sent: Monday, June 29, 2009 11:23:35 AM
Subject: [Dhis2-devs] How to get the calculated value from an input formula string ?

That’s a question to resolve the problem as example below :

I’ve got three cells as A1, B1 and C1

A1 contains value as 8 (A1 = 8)
B1 contains value as 10 (B1 = 10)

and:

C1 = A1 + B1

So, how can i get the calculated value as 18 from input formula string “A1 + B1” by jxl or POI

p.s: I could not get directly the pre-calculated value from cell C1 by jxl.

Hieu.HISPVietnam
Good Health !

Oh, (so supsire) hi Murod !

I mean which method in which class either POI or JXL using for input formula string ?

Can u give an sample ?

Thank a lot !

···

On Mon, Jun 29, 2009 at 5:26 PM, Murodullo Latifov murodlatifov@yahoo.com wrote:

in POI place formula like this: A1 + B1 . Note no need for equal = sign.

murod

From: Hieu Dang Duy hieu.hispvietnam@gmail.com

Sent: Monday, June 29, 2009 11:23:35 AM
Subject: [Dhis2-devs] How to get the calculated value from an input formula string ?

That’s a question to resolve the problem as example below :

I’ve got three cells as A1, B1 and C1

A1 contains value as 8 (A1 = 8)
B1 contains value as 10 (B1 = 10)

and:

C1 = A1 + B1

So, how can i get the calculated value as 18 from input formula string “A1 + B1” by jxl or POI

p.s: I could not get directly the pre-calculated value from cell C1 by jxl.

Hieu.HISPVietnam
Good Health !

Hieu.HISPVietnam
Good Health !

Hi

I don't know if I have the right understanding of this, but please do
remember that POI and JXL are only wrappers around the file format -
they do not replace the formula engine in the spreadsheet application.
with the JXL or POI library) which has a formula in cell C1, then the
simple act of reading the cell value will not automatically calculate
it for you.

You would have to open the file in a spreadsheet application (excel,
openoffice, gnumeric or something similar) in order to trigger the
evaluation of formulae. When you save again the calculated values
will be saved. Or build your own formula engine which would be a
pretty big undertaking. Though I suppose A1+B1 wouldn't be too bad
...

Regards
Bob

···

2009/6/29 Hieu Dang Duy <hieu.hispvietnam@gmail.com>:

Oh, (so supsire) hi Murod !

I mean which method in which class either POI or JXL using for input formula
string ?

Can u give an sample ?

Thank a lot !

On Mon, Jun 29, 2009 at 5:26 PM, Murodullo Latifov <murodlatifov@yahoo.com> > wrote:

in POI place formula like this: A1 + B1 . Note no need for equal = sign.

murod

________________________________
From: Hieu Dang Duy <hieu.hispvietnam@gmail.com>
Sent: Monday, June 29, 2009 11:23:35 AM
Subject: [Dhis2-devs] How to get the calculated value from an input
formula string ?

That's a question to resolve the problem as example below :

I've got three cells as A1, B1 and C1

A1 contains value as 8 (A1 = 8)
B1 contains value as 10 (B1 = 10)

and:

C1 = A1 + B1

So, how can i get the calculated value as 18 from input formula string "A1
+ B1" by jxl or POI

p.s: I could not get directly the pre-calculated value from cell C1 by
jxl.

--
Hieu.HISPVietnam
Good Health !

--
Hieu.HISPVietnam
Good Health !

_______________________________________________

Hi,

Have you looked at this part of sample code I sent you earlier:
String celFormula1 = "C30+C31+C35+C36+C37+C38+C39+C40+C41+C42";

r = s.getRow(42);
c = r.createCell((short)2);
c.setCellFormula(celFormula1);

This will do all, no need to refresh anything to see formula value calculated. I thought you have noticed this earlier. Yes Bob, POI and JXL are wrappers as any other XML parser or technology, but mostly simplified for excel manipulation. If you don't use these two than you have to use some parser for sure if you want to change things programatically (they all are XMLs). Why "A1+B1" or "B1+A1" are too bad? They are simplest addition mathematical formula, same way in excel and openoffice or lotus notes.
If you want to read value of that formulae before it have been opened by Excel or OpenOffice, than it is not wise. You better to give another formula from the root for new column value, otherwise you will get something terrible as Bob explained.

regards,
murod

···

----- Original Message ----
From: Bob Jolliffe <bobjolliffe@gmail.com>
To: Hieu Dang Duy <hieu.hispvietnam@gmail.com>
Sent: Monday, June 29, 2009 1:09:18 PM
Subject: Re: [Dhis2-devs] How to get the calculated value from an input formula string ?

Hi

I don't know if I have the right understanding of this, but please do
remember that POI and JXL are only wrappers around the file format -
they do not replace the formula engine in the spreadsheet application.
with the JXL or POI library) which has a formula in cell C1, then the
simple act of reading the cell value will not automatically calculate
it for you.

You would have to open the file in a spreadsheet application (excel,
openoffice, gnumeric or something similar) in order to trigger the
evaluation of formulae. When you save again the calculated values
will be saved. Or build your own formula engine which would be a
pretty big undertaking. Though I suppose A1+B1 wouldn't be too bad
...

Regards
Bob

2009/6/29 Hieu Dang Duy <hieu.hispvietnam@gmail.com>:

Oh, (so supsire) hi Murod !

I mean which method in which class either POI or JXL using for input formula
string ?

Can u give an sample ?

Thank a lot !

On Mon, Jun 29, 2009 at 5:26 PM, Murodullo Latifov <murodlatifov@yahoo.com> > wrote:

in POI place formula like this: A1 + B1 . Note no need for equal = sign.

murod

________________________________
From: Hieu Dang Duy <hieu.hispvietnam@gmail.com>
Sent: Monday, June 29, 2009 11:23:35 AM
Subject: [Dhis2-devs] How to get the calculated value from an input
formula string ?

That's a question to resolve the problem as example below :

I've got three cells as A1, B1 and C1

A1 contains value as 8 (A1 = 8)
B1 contains value as 10 (B1 = 10)

and:

C1 = A1 + B1

So, how can i get the calculated value as 18 from input formula string "A1
+ B1" by jxl or POI

p.s: I could not get directly the pre-calculated value from cell C1 by
jxl.

--
Hieu.HISPVietnam
Good Health !

--
Hieu.HISPVietnam
Good Health !

_______________________________________________

Oh, Nice to see both of you (Murod and Bob) again at this issue.

[Hehe, I’m so fun]

Thanks for your breaking idea, Bob.

"When you save again the calculated values

will be saved" - Bob said right, the calculated values which is saved after re-open the generated excel file one more time (with a bit of modification or we can say that is a ruse).

Btw, It’s so difficult to understand what is happened inside it (the excel file) …

Another one, If have any report which just only has got the simple SUM formula that will be not a big problem now. Don’t you think about the combination formulas as C1 = SUM( ROUND( DIV( ) ) ) ?
Thank you, thank you so much.

···

On Mon, Jun 29, 2009 at 6:09 PM, Bob Jolliffe bobjolliffe@gmail.com wrote:

Hi

I don’t know if I have the right understanding of this, but please do

remember that POI and JXL are only wrappers around the file format -

they do not replace the formula engine in the spreadsheet application.

with the JXL or POI library) which has a formula in cell C1, then the

simple act of reading the cell value will not automatically calculate

it for you.

You would have to open the file in a spreadsheet application (excel,

openoffice, gnumeric or something similar) in order to trigger the

evaluation of formulae. When you save again the calculated values

will be saved. Or build your own formula engine which would be a

pretty big undertaking. Though I suppose A1+B1 wouldn’t be too bad

Regards

Bob

From: Hieu Dang Duy hieu.hispvietnam@gmail.com

Sent: Monday, June 29, 2009 11:23:35 AM

Subject: [Dhis2-devs] How to get the calculated value from an input

formula string ?

That’s a question to resolve the problem as example below :

I’ve got three cells as A1, B1 and C1

A1 contains value as 8 (A1 = 8)

B1 contains value as 10 (B1 = 10)

and:

C1 = A1 + B1

So, how can i get the calculated value as 18 from input formula string "A1

• B1" by jxl or POI

p.s: I could not get directly the pre-calculated value from cell C1 by

jxl.

Hieu.HISPVietnam

Good Health !

Hieu.HISPVietnam
Good Health !

Hi,

Have you looked at this part of sample code I sent you earlier:
String celFormula1 = "C30+C31+C35+C36+C37+C38+C39+C40+C41+C42";

``````       r = s\.getRow\(42\);
c = r\.createCell\(\(short\)2\);
c\.setCellFormula\(celFormula1\);
``````

This will do all, no need to refresh anything to see formula value calculated. I thought you have noticed this >earlier. Yes Bob, POI and JXL are wrappers as any other XML parser or technology, but mostly simplified for >excel manipulation. If you don't use these two than you have to use some parser for sure if you want to >change things programatically (they all are XMLs).

I think the common xls binary format is not xml. The newer xlsx
format is xml. Not that any of it matters when you are using POI or
JXL. This detail is transparent to the user. Though I think only POI
supports the newer XML based format. Which means this will probably
be the library we end up with, even though JXL might be better.

Why "A1+B1" or "B1+A1" are too bad?

I just meant that you could probably easily enough implement these
difficult). But it could be a slippery slope to go down. The more
general problem of calculating formulae is best left to the
spreadsheet programs. I think we agree Implementing the many
undocumented functions which make up the modern spreadsheet program is
not trivial.

Cheers
Bob

···

2009/6/29 Murodullo Latifov <murodlatifov@yahoo.com>:

They are simplest addition mathematical formula, same way in excel and openoffice or lotus notes.
If you want to read value of that formulae before it have been opened by Excel or OpenOffice, than it is not wise. You better to give another formula from the root for new column value, otherwise you will get something terrible as Bob explained.

regards,
murod

----- Original Message ----
From: Bob Jolliffe <bobjolliffe@gmail.com>
To: Hieu Dang Duy <hieu.hispvietnam@gmail.com>
Sent: Monday, June 29, 2009 1:09:18 PM
Subject: Re: [Dhis2-devs] How to get the calculated value from an input formula string ?

Hi

I don't know if I have the right understanding of this, but please do
remember that POI and JXL are only wrappers around the file format -
they do not replace the formula engine in the spreadsheet application.
with the JXL or POI library) which has a formula in cell C1, then the
simple act of reading the cell value will not automatically calculate
it for you.

You would have to open the file in a spreadsheet application (excel,
openoffice, gnumeric or something similar) in order to trigger the
evaluation of formulae. When you save again the calculated values
will be saved. Or build your own formula engine which would be a
pretty big undertaking. Though I suppose A1+B1 wouldn't be too bad
...

Regards
Bob

2009/6/29 Hieu Dang Duy <hieu.hispvietnam@gmail.com>:

Oh, (so supsire) hi Murod !

I mean which method in which class either POI or JXL using for input formula
string ?

Can u give an sample ?

Thank a lot !

On Mon, Jun 29, 2009 at 5:26 PM, Murodullo Latifov <murodlatifov@yahoo.com> >> wrote:

in POI place formula like this: A1 + B1 . Note no need for equal = sign.

murod

________________________________
From: Hieu Dang Duy <hieu.hispvietnam@gmail.com>
Sent: Monday, June 29, 2009 11:23:35 AM
Subject: [Dhis2-devs] How to get the calculated value from an input
formula string ?

That's a question to resolve the problem as example below :

I've got three cells as A1, B1 and C1

A1 contains value as 8 (A1 = 8)
B1 contains value as 10 (B1 = 10)

and:

C1 = A1 + B1

So, how can i get the calculated value as 18 from input formula string "A1
+ B1" by jxl or POI

p.s: I could not get directly the pre-calculated value from cell C1 by
jxl.

--
Hieu.HISPVietnam
Good Health !

--
Hieu.HISPVietnam
Good Health !

_______________________________________________

Oh, Nice to see both of you (Murod and Bob) again at this issue.

[Hehe, I'm so fun]

Thanks for your breaking idea, Bob.

"When you save again the calculated values
will be saved" - Bob said right, the calculated values which is saved after
re-open the generated excel file one more time (with a bit of modification
or we can say that is a ruse).

Btw, It's so difficult to understand what is happened inside it (the excel
file) ...

Another one, If have any report which just only has got the simple SUM
formula that will be not a big problem now. Don't you think about the
combination formulas as C1 = SUM( ROUND( DIV( ) ) ) ?

Yes this is my point. It can be done but you wouldn't really want to.

I know what you can do (if you are just a little bit crazy) is to run
openoffice as a background process with no user interface. A
so-called "headless" openoffice. You can then use this to process
your excel formulae. But maybe it will be using more RAM than it is
worth. This is the approach used by the alfresco document management
system to do conversion between file formats, creating pdfs etc.

Regards
Bob

···

2009/6/29 Hieu Dang Duy <hieu.hispvietnam@gmail.com>:

Thank you, thank you so much.

On Mon, Jun 29, 2009 at 6:09 PM, Bob Jolliffe <bobjolliffe@gmail.com> wrote:

Hi

I don't know if I have the right understanding of this, but please do
remember that POI and JXL are only wrappers around the file format -
they do not replace the formula engine in the spreadsheet application.
with the JXL or POI library) which has a formula in cell C1, then the
simple act of reading the cell value will not automatically calculate
it for you.

You would have to open the file in a spreadsheet application (excel,
openoffice, gnumeric or something similar) in order to trigger the
evaluation of formulae. When you save again the calculated values
will be saved. Or build your own formula engine which would be a
pretty big undertaking. Though I suppose A1+B1 wouldn't be too bad
...

Regards
Bob

___________________________
>> From: Hieu Dang Duy <hieu.hispvietnam@gmail.com>
>> Sent: Monday, June 29, 2009 11:23:35 AM
>> Subject: [Dhis2-devs] How to get the calculated value from an input
>> formula string ?
>>
>>
>> That's a question to resolve the problem as example below :
>>
>> I've got three cells as A1, B1 and C1
>>
>> A1 contains value as 8 (A1 = 8)
>> B1 contains value as 10 (B1 = 10)
>>
>> and:
>>
>> C1 = A1 + B1
>>
>> So, how can i get the calculated value as 18 from input formula string
>> "A1
>> + B1" by jxl or POI
>>
>> p.s: I could not get directly the pre-calculated value from cell C1 by
>> jxl.
>>
>>
>> --
>> Hieu.HISPVietnam
>> Good Health !
>>

--
Hieu.HISPVietnam
Good Health !

In POI you can do it by:
String celFormula1 = "SUM( ROUND( DIV(RAND(WHATEVER) ) ) )";
String celFormula1 = "DAYS360(NOW(),C1, FALSE)";
String celFormula1 = "ANY SUPPORTED EXCEL FORMULA"; depending which version of excel document you are using.
r = s.getRow(42);
c = r.createCell((short)2);
c.setCellFormula(celFormula1);
No need to go crazy, keep things simple, that's why POI and JXL are there taking care of this issues.
When you open excel you will see the result without refreshing, if you point to it not opening excel file again, it maybe problematic, similar to pointer to NULL pointer in c++.

Bob, yes earlier excel format was binary, but my point is in any way you will need some tool to work with material (binary, text, xml, html), modern tools, not stone but hammer. Why I give preference to POI for reporting compared to BIRT is its lightwiegthness, no need to maintain another server application, no need to run openoffice headless.

Hieu I recommend you to look at sites I earlier pointed you: http://poi.apache.org/spreadsheet/formula.html. and http://sc.openoffice.org/excelfileformat.pdf. if you want more information.

murod

···

----- Original Message ----
From: Bob Jolliffe <bobjolliffe@gmail.com>
To: Hieu Dang Duy <hieu.hispvietnam@gmail.com>
Sent: Monday, June 29, 2009 2:26:06 PM
Subject: Re: [Dhis2-devs] How to get the calculated value from an input formula string ?

2009/6/29 Hieu Dang Duy <hieu.hispvietnam@gmail.com>:

Oh, Nice to see both of you (Murod and Bob) again at this issue.

[Hehe, I'm so fun]

Thanks for your breaking idea, Bob.

"When you save again the calculated values
will be saved" - Bob said right, the calculated values which is saved after
re-open the generated excel file one more time (with a bit of modification
or we can say that is a ruse).

Btw, It's so difficult to understand what is happened inside it (the excel
file) ...

Another one, If have any report which just only has got the simple SUM
formula that will be not a big problem now. Don't you think about the
combination formulas as C1 = SUM( ROUND( DIV( ) ) ) ?

Yes this is my point. It can be done but you wouldn't really want to.

I know what you can do (if you are just a little bit crazy) is to run
openoffice as a background process with no user interface. A
so-called "headless" openoffice. You can then use this to process
your excel formulae. But maybe it will be using more RAM than it is
worth. This is the approach used by the alfresco document management
system to do conversion between file formats, creating pdfs etc.

Regards
Bob

Thank you, thank you so much.

On Mon, Jun 29, 2009 at 6:09 PM, Bob Jolliffe <bobjolliffe@gmail.com> wrote:

Hi

I don't know if I have the right understanding of this, but please do
remember that POI and JXL are only wrappers around the file format -
they do not replace the formula engine in the spreadsheet application.
with the JXL or POI library) which has a formula in cell C1, then the
simple act of reading the cell value will not automatically calculate
it for you.

You would have to open the file in a spreadsheet application (excel,
openoffice, gnumeric or something similar) in order to trigger the
evaluation of formulae. When you save again the calculated values
will be saved. Or build your own formula engine which would be a
pretty big undertaking. Though I suppose A1+B1 wouldn't be too bad
...

Regards
Bob

___________________________
>> From: Hieu Dang Duy <hieu.hispvietnam@gmail.com>
>> Sent: Monday, June 29, 2009 11:23:35 AM
>> Subject: [Dhis2-devs] How to get the calculated value from an input
>> formula string ?
>>
>>
>> That's a question to resolve the problem as example below :
>>
>> I've got three cells as A1, B1 and C1
>>
>> A1 contains value as 8 (A1 = 8)
>> B1 contains value as 10 (B1 = 10)
>>
>> and:
>>
>> C1 = A1 + B1
>>
>> So, how can i get the calculated value as 18 from input formula string
>> "A1
>> + B1" by jxl or POI
>>
>> p.s: I could not get directly the pre-calculated value from cell C1 by
>> jxl.
>>
>>
>> --
>> Hieu.HISPVietnam
>> Good Health !
>>

--
Hieu.HISPVietnam
Good Health !

Hi

In POI you can do it by:
String celFormula1 = "SUM( ROUND( DIV(RAND(WHATEVER) ) ) )";
String celFormula1 = "DAYS360(NOW(),C1, FALSE)";
String celFormula1 = "ANY SUPPORTED EXCEL FORMULA"; depending which version of excel document you are using.
r = s.getRow(42);
c = r.createCell((short)2);
c.setCellFormula(celFormula1);
No need to go crazy, keep things simple, that's why POI and JXL are there taking care of this issues.
When you open excel you will see the result without refreshing, if you point to it not opening excel file again, it maybe problematic, similar to pointer to NULL pointer in c++.

Bob, yes earlier excel format was binary, but my point is in any way you will need some tool to work with material (binary, text, >xml, html), modern tools, not stone but hammer. Why I give preference to POI for reporting compared to BIRT is its >lightwiegthness, no need to maintain another server application, no need to run openoffice headless.

I think this is really important point of departure. Maybe worth its
own thread .... What are the pro's of using birt for reporting? What
are the cons? How does POI make it better?

Cheers
Bob

BTW I think using xslt is a more modern approach than MS Excel binary
files ···

2009/6/29 Murodullo Latifov <murodlatifov@yahoo.com>:

Hieu I recommend you to look at sites I earlier pointed you: http://poi.apache.org/spreadsheet/formula.html. and http://sc.openoffice.org/excelfileformat.pdf. if you want more information.

murod

----- Original Message ----
From: Bob Jolliffe <bobjolliffe@gmail.com>
To: Hieu Dang Duy <hieu.hispvietnam@gmail.com>
Sent: Monday, June 29, 2009 2:26:06 PM
Subject: Re: [Dhis2-devs] How to get the calculated value from an input formula string ?

2009/6/29 Hieu Dang Duy <hieu.hispvietnam@gmail.com>:

Oh, Nice to see both of you (Murod and Bob) again at this issue.

[Hehe, I'm so fun]

Thanks for your breaking idea, Bob.

"When you save again the calculated values
will be saved" - Bob said right, the calculated values which is saved after
re-open the generated excel file one more time (with a bit of modification
or we can say that is a ruse).

Btw, It's so difficult to understand what is happened inside it (the excel
file) ...

Another one, If have any report which just only has got the simple SUM
formula that will be not a big problem now. Don't you think about the
combination formulas as C1 = SUM( ROUND( DIV( ) ) ) ?

Yes this is my point. It can be done but you wouldn't really want to.

I know what you can do (if you are just a little bit crazy) is to run
openoffice as a background process with no user interface. A
so-called "headless" openoffice. You can then use this to process
your excel formulae. But maybe it will be using more RAM than it is
worth. This is the approach used by the alfresco document management
system to do conversion between file formats, creating pdfs etc.

Regards
Bob

Thank you, thank you so much.

On Mon, Jun 29, 2009 at 6:09 PM, Bob Jolliffe <bobjolliffe@gmail.com> wrote:

Hi

I don't know if I have the right understanding of this, but please do
remember that POI and JXL are only wrappers around the file format -
they do not replace the formula engine in the spreadsheet application.
with the JXL or POI library) which has a formula in cell C1, then the
simple act of reading the cell value will not automatically calculate
it for you.

You would have to open the file in a spreadsheet application (excel,
openoffice, gnumeric or something similar) in order to trigger the
evaluation of formulae. When you save again the calculated values
will be saved. Or build your own formula engine which would be a
pretty big undertaking. Though I suppose A1+B1 wouldn't be too bad
...

Regards
Bob

___________________________
>> From: Hieu Dang Duy <hieu.hispvietnam@gmail.com>
>> Sent: Monday, June 29, 2009 11:23:35 AM
>> Subject: [Dhis2-devs] How to get the calculated value from an input
>> formula string ?
>>
>>
>> That's a question to resolve the problem as example below :
>>
>> I've got three cells as A1, B1 and C1
>>
>> A1 contains value as 8 (A1 = 8)
>> B1 contains value as 10 (B1 = 10)
>>
>> and:
>>
>> C1 = A1 + B1
>>
>> So, how can i get the calculated value as 18 from input formula string
>> "A1
>> + B1" by jxl or POI
>>
>> p.s: I could not get directly the pre-calculated value from cell C1 by
>> jxl.
>>