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

Hieu_Dang_Duy · 30 June 2009 03:58

Dear Murod and Bob,

Murod, do you remember this mail ?

Form - tx_bieu_2.xls1245381039109.xls (28 KB)

···

---------- Forwarded message ----------
From: Hieu Dang Duy hieu.hispvietnam@gmail.com

Date: Mon, Jun 22, 2009 at 5:47 PM
Subject: Re: [Dhis2-devs] Needed for support with jxl package
To: Murodullo Latifov murodlatifov@yahoo.com
Cc: bobjolliffe@gmail.com, dhis2-devs@lists.launchpad.net

Hi Murod,

After using your project to generate a random excel file from my template, “tx_bieu_2.xls”.
I’ve got a new file, for example: “Form - tx_bieu_2.xls1245381039109.xls”.

Your code is running very stable.
By the way, I’ve tried to write a new java file for checking this new one’s structure.

And , just only magic can change everything (It means using POI checking the generated excel file’s structure as same as JXL … nothing was new)

You can get more reference with my attached files as below.

Place the template file at the location "C:"

From the POI or JXL Excel API technique, I think the reason why we couldn’t get the real values from the formula cells is that these values which is only calculated by itself tool (MS Office: .xls or Open Office: .odt). I mean we must open the generated file to see the correct answers at that time.

p.s: Did you know how to get the data (value) by input as an string formula with POI ???

Thank you very much for all your ardour !

On Thu, Jun 18, 2009 at 5:19 PM, Murodullo Latifov murodlatifov@yahoo.com wrote:

Hi Hieu,

See attached as sample implementation of POI. Its Eclipse java project, simply unrar and import to Eclipse, place this template in c:, run Runner, see new files created in c:. It uses your template and fills in some random data from list of list of integers. And finally places two formula and styles it. Should help a lot. Also number of stylings are available, some are used as example. For the rest consult http://poi.apache.org/spreadsheet/quick-guide.html.

regards,
murod.

–
Hieu.HISPVietnam
Good Health !

I’ve read your code very carefully, Murod.
And I assure that your coding which is running ok.

The setCellFormula() method had done very well as its own duty. No matters how is here.

After that, I sent to you all these two attached files:
One is a java file which was written with POI.

“TestExcelReportPOI.java”

And the other one is an excel file which was generated by your coding

“Form - tx_bieu_2.xls1245381039109.xls”

In this java file, I used POI to test the structure of the excel file above.

Then, the result is that I always receive all of the value as zero (0) at the each cell formulae.
The same as when using JXL for testing.

Did you remember about our previewing report module that I’d said before ?

It had been done about 90%. And 10% is the formulae in the excel file. It’s too worth.

So, that why I posted this mail to ask you guys that
“Which method in which class, can be used to get the re-calculated value (please attending to re-calculated value) of each formula cell by POI or JXL ?” - That’s the last way which I can do with the formulae cells up-to-now.

Because, if using POI or JXL to get those real values directly is impossible. Don’t you ?

Anything is clearly now ?

Thanks for your linked pdf file.

P.S: The real values that is the calculated value which can be seen when opening its self tool (OpenOffice or MS Office Excel)

–
Hieu.HISPVietnam
Good Health !

Murodullo_Latifov · 30 June 2009 06:06

Hi **Hieu,

Yes, that was answer to your question. If you had chance to look here http://poi.apache.org/spreadsheet/eval.html you could get some helpful tips.
When you clarified what you really want, answer was to use formula from its source, not the reference (e.g. if you had somewhere “A1+B1” placed in C3, to reuse it you have to do “sum(V1/(A1+B1))”, not “sum(V1/C3)”).
If its hard or impossible to do this type of formula, JAVA is there for calculating values.
What I mean by JAVA is you do calculations in plain java and only place values where necessary, in this case there won’t be a formula in excel but only value.

regards,
murod**

···

From: Hieu Dang Duy hieu.hispvietnam@gmail.com
To: Murodullo Latifov murodlatifov@yahoo.com
Cc: bobjolliffe@gmail.com; dhis2-devs@lists.launchpad.net
Sent: Tuesday, June 30, 2009 5:58:01 AM
Subject: Fwd: [Dhis2-devs] How to get the calculated value from an input formula string ?

Dear Murod and Bob,

Murod, do you remember this mail ?

---------- Forwarded message ----------
From: Hieu Dang Duy hieu.hispvietnam@gmail.com

Date: Mon, Jun 22, 2009 at 5:47 PM
Subject: Re: [Dhis2-devs] Needed for support with jxl package
To: Murodullo Latifov murodlatifov@yahoo.com
Cc: bobjolliffe@gmail.com, dhis2-devs@lists.launchpad.net

Hi Murod,

After using your project to generate a random excel file from my template, “tx_bieu_2.xls”.
I’ve got a new file, for example: “Form - tx_bieu_2.xls1245381039109.xls”.

Your code is running very stable.
By the way, I’ve tried to write a new java file for checking this new one’s structure.

And , just only magic can change everything (It means using POI checking the generated excel file’s structure as same as JXL … nothing was new)

You can get more reference with my attached files as below.

Place the template file at the location "C:"

From the POI or JXL Excel API technique, I think the reason why we couldn’t get the real values from the formula cells is that these values which is only calculated by itself tool (MS Office: .xls or Open Office: .odt). I mean we must open the generated file to see the correct answers at that time.

p.s: Did you know how to get the data (value) by input as an string formula with POI ???

Thank you very much for all your ardour !

On Thu, Jun 18, 2009 at 5:19 PM, Murodullo Latifov murodlatifov@yahoo.com wrote:

Hi Hieu,

See attached as sample implementation of POI. Its Eclipse java project, simply unrar and import to Eclipse, place this template in c:, run Runner, see new files created in c:. It uses your template and fills in some random data from list of list of integers. And finally places two formula and styles it. Should help a lot. Also number of stylings are available, some are used as example. For the rest consult http://poi.apache.org/spreadsheet/quick-guide.html.

regards,
murod.

–
Hieu.HISPVietnam
Good Health !

I’ve read your code very carefully, Murod.
And I assure that your coding which is running ok.

The setCellFormula() method had done very well as its own duty. No matters how is here.

After that, I sent to you all these two attached files:
One is a java file which was written with POI.

“TestExcelReportPOI.java”

And the other one is an excel file which was generated by your coding

“Form - tx_bieu_2.xls1245381039109.xls”

In this java file, I used POI to test the structure of the excel file above.

Then, the result is that I always receive all of the value as zero (0) at the each cell formulae.
The same as when using JXL for testing.

Did you remember about our previewing report module that I’d said before ?

It had been done about 90%. And 10% is the formulae in the excel file. It’s too worth.

So, that why I posted this mail to ask you guys that
“Which method in which class, can be used to get the re-calculated value (please attending to re-calculated value) of each formula cell by POI or JXL ?” - That’s the last way which I can do with the formulae cells up-to-now.

Because, if using POI or JXL to get those real values directly is impossible. Don’t you ?

Anything is clearly now ?

Thanks for your linked pdf file.

P.S: The real values that is the calculated value which can be seen when opening its self tool (OpenOffice or MS Office Excel)

–
Hieu.HISPVietnam
Good Health !

Hieu_Dang_Duy · 30 June 2009 06:18

What’s wrong with these excel files ?

I’ve just attached two files:

“Form - tx_bieu_2.xls1246341439640.xls” ← : Generated by POI (Code from Murod)

“admin30.06.2009.12.59.36.PMtx_bieu_2.xls” ← : Generated by JXL.

I opened one by one, and then do nothing on their contain.
After that, I closed each one and received a warning message (or a confirmative message).

See more at the attached files below.

Form - tx_bieu_2.xls1246341439640.xls (28 KB)

admin30.06.2009.12.59.36.PMtx_bieu_2.xls (29.5 KB)

···

On Tue, Jun 30, 2009 at 10:58 AM, Hieu Dang Duy hieu.hispvietnam@gmail.com wrote:

Dear Murod and Bob,

Murod, do you remember this mail ?

---------- Forwarded message ----------
From: Hieu Dang Duy hieu.hispvietnam@gmail.com

Date: Mon, Jun 22, 2009 at 5:47 PM
Subject: Re: [Dhis2-devs] Needed for support with jxl package
To: Murodullo Latifov murodlatifov@yahoo.com
Cc: bobjolliffe@gmail.com, dhis2-devs@lists.launchpad.net

Hi Murod,

After using your project to generate a random excel file from my template, “tx_bieu_2.xls”.
I’ve got a new file, for example: “Form - tx_bieu_2.xls1245381039109.xls”.

Your code is running very stable.
By the way, I’ve tried to write a new java file for checking this new one’s structure.

And , just only magic can change everything (It means using POI checking the generated excel file’s structure as same as JXL … nothing was new)

You can get more reference with my attached files as below.

Place the template file at the location "C:"

From the POI or JXL Excel API technique, I think the reason why we couldn’t get the real values from the formula cells is that these values which is only calculated by itself tool (MS Office: .xls or Open Office: .odt). I mean we must open the generated file to see the correct answers at that time.

p.s: Did you know how to get the data (value) by input as an string formula with POI ???

Thank you very much for all your ardour !

On Thu, Jun 18, 2009 at 5:19 PM, Murodullo Latifov murodlatifov@yahoo.com wrote:

Hi Hieu,

See attached as sample implementation of POI. Its Eclipse java project, simply unrar and import to Eclipse, place this template in c:, run Runner, see new files created in c:. It uses your template and fills in some random data from list of list of integers. And finally places two formula and styles it. Should help a lot. Also number of stylings are available, some are used as example. For the rest consult http://poi.apache.org/spreadsheet/quick-guide.html.

regards,
murod.

–
Hieu.HISPVietnam
Good Health !

I’ve read your code very carefully, Murod.
And I assure that your coding which is running ok.

The setCellFormula() method had done very well as its own duty. No matters how is here.

After that, I sent to you all these two attached files:
One is a java file which was written with POI.

“TestExcelReportPOI.java”

And the other one is an excel file which was generated by your coding

“Form - tx_bieu_2.xls1245381039109.xls”

In this java file, I used POI to test the structure of the excel file above.

Then, the result is that I always receive all of the value as zero (0) at the each cell formulae.
The same as when using JXL for testing.

Did you remember about our previewing report module that I’d said before ?

It had been done about 90%. And 10% is the formulae in the excel file. It’s too worth.

So, that why I posted this mail to ask you guys that
“Which method in which class, can be used to get the re-calculated value (please attending to re-calculated value) of each formula cell by POI or JXL ?” - That’s the last way which I can do with the formulae cells up-to-now.

Because, if using POI or JXL to get those real values directly is impossible. Don’t you ?

Anything is clearly now ?

Thanks for your linked pdf file.

P.S: The real values that is the calculated value which can be seen when opening its self tool (OpenOffice or MS Office Excel)

–
Hieu.HISPVietnam
Good Health !

–
Hieu.HISPVietnam
Good Health !