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.
So if you create a spreadsheet using your application (in combination
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
...

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

murod

________________________________
From: Hieu Dang Duy <hieu.hispvietnam@gmail.com>
To: dhis2-devs@lists.launchpad.net
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.

Please giving me your experiences about the jxl or POI API !

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.

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.
So if you create a spreadsheet using your application (in combination
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
...

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

murod

________________________________
From: Hieu Dang Duy <hieu.hispvietnam@gmail.com>
To: dhis2-devs@lists.launchpad.net
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.

Please giving me your experiences about the jxl or POI API !

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.
I understood your opinion about this.

"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.

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
simple addition formulae. That would not be too bad (or too
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.

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.

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.
So if you create a spreadsheet using your application (in combination
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
...

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

murod

________________________________
From: Hieu Dang Duy <hieu.hispvietnam@gmail.com>
To: dhis2-devs@lists.launchpad.net
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.

Please giving me your experiences about the jxl or POI API !

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.
I understood your opinion about this.

"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.

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.
So if you create a spreadsheet using your application (in combination
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>
>> To: dhis2-devs@lists.launchpad.net
>> 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.
>>
>> Please giving me your experiences about the jxl or POI API !
>>
>> --
>> 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.

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.
I understood your opinion about this.

"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.

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.
So if you create a spreadsheet using your application (in combination
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>
>> To: dhis2-devs@lists.launchpad.net
>> 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.
>>
>> Please giving me your experiences about the jxl or POI API !
>>
>> --
>> 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.

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

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.
I understood your opinion about this.

"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.

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.
So if you create a spreadsheet using your application (in combination
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>
>> To: dhis2-devs@lists.launchpad.net
>> 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.
>>
>> Please giving me your experiences about the jxl or POI API !
>>
>> --
>> Hieu.HISPVietnam
>> Good Health !
>>