dhis2-devs team mailing list archive

[Bob Jolliffe <bobjolliffe@xxxxxxxxx>]

2009/6/29 Murodullo Latifov <murodlatifov@xxxxxxxxx>:
>
> 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
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.

Cheers
Bob

>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@xxxxxxxxx>
> To: Hieu Dang Duy <hieu.hispvietnam@xxxxxxxxx>
> Cc: Murodullo Latifov <murodlatifov@xxxxxxxxx>; dhis2-devs@xxxxxxxxxxxxxxxxxxx
> 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.
> 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
>
> 2009/6/29 Hieu Dang Duy <hieu.hispvietnam@xxxxxxxxx>:
>> 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@xxxxxxxxx>
>> wrote:
>>>
>>> in POI place formula like this: A1 + B1 . Note no need for equal = sign.
>>>
>>> murod
>>>
>>> ________________________________
>>> 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 !
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~dhis2-devs
>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~dhis2-devs
>> More help   : https://help.launchpad.net/ListHelp
>>
>>
>
>
>
>
>