dhis2-devs team mailing list archive

[Murodullo Latifov <murodlatifov@xxxxxxxxx>]

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@xxxxxxxxx>
To: Hieu Dang Duy <hieu.hispvietnam@xxxxxxxxx>
Cc: Murodullo Latifov <murodlatifov@xxxxxxxxx>; dhis2-devs@xxxxxxxxxxxxxxxxxxx
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@xxxxxxxxx>:
> 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.

Regards
Bob

> Thank you, thank you so much.
>
> On Mon, Jun 29, 2009 at 6:09 PM, Bob Jolliffe <bobjolliffe@xxxxxxxxx> 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.
>> 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@xxxxxxxxx>
>> >> To: dhis2-devs@xxxxxxxxxxxxxxxxxxx
>> >> 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 !
>> >>
>>
>
> --
> Hieu.HISPVietnam
> Good Health !
>