Needed for support with jxl package

Dear all,

I’m using the package as jxl (2.6).
By using this one i’ve got the workbook of the identified excel file.
My excel file has some of formulas in its content as following example:

Ex:

                       (Columns)

                            **A                  B                  C                 D**

(Rows) 1 10 8 0 =SUM(A1:C1)

          **2**                 -                   -                   -                  -

These formulas were set-up and written into the excel file by Java.

The issue was happened when i were reading this excel file and printing out value of each formula in each Cell as D1, it always return as 4.

By the way, if i opened this file and had some of changing into its content then saving again.
After that, re-checking is it return the correct value which is should be as 18.

Any idea or suggestion on this entry ?

Expecting for your all comments !

p.s: I don’t think the error at excel version or by the tool for designing it as MS Office, Open Office, …

Thanks !

···


Hieu.HISPVietnam
Good Health !

Hi Hieu

I know this doesn't really help you, but out of interest I've pasted
the relevant bit of an equivalent openoffice spreadsheet content.xml
below. As you can see it is very similar to html. If you can make
html tables you can make odf spreadsheets. And you don't need a
special library - just write out the xml (and zip it up properly in a
ods file) ... and it gives the correct answer of '18' in openoffice.

  <office:body>
    <office:spreadsheet>
      <table:table table:name="Sheet1">
        <table:table-row>
          <table:table-cell office:value-type="float" office:value="10">
            <text:p>10</text:p>
          </table:table-cell>
          <table:table-cell office:value-type="float" office:value="8">
            <text:p>8</text:p>
          </table:table-cell>
          <table:table-cell office:value-type="float" office:value="0">
            <text:p>0</text:p>
          </table:table-cell>
          <table:table-cell table:formula="oooc:=SUM([.A1:.C1])"
office:value-type="float">
            <text:p></text:p>
          </table:table-cell>
        </table:table-row>
      </table:table>
    </office:spreadsheet>
  </office:body>
</office:document-content>

Regards
Bob

···

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

Dear all,

I'm using the package as jxl (2.6).
By using this one i've got the workbook of the identified excel file.
My excel file has some of formulas in its content as following example:

Ex:

                       \(Columns\)

                            A                  B

C D
(Rows) 1 10 8
0 =SUM(A1:C1)
2 - -
- -

These formulas were set-up and written into the excel file by Java.

The issue was happened when i were reading this excel file and printing out
value of each formula in each Cell as D1, it always return as 4.

By the way, if i opened this file and had some of changing into its content
then saving again.
After that, re-checking is it return the correct value which is should be as
18.

Any idea or suggestion on this entry ?
Expecting for your all comments !

p.s: I don't think the error at excel version or by the tool for designing
it as MS Office, Open Office, ...

Thanks !

--
Hieu.HISPVietnam
Good Health !

_______________________________________________
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp

Hi,

···

From: Hieu Dang Duy hieu.hispvietnam@gmail.com
To: dhis2-devs@lists.launchpad.net
Sent: Tuesday, June 16, 2009 12:31:04 PM
Subject: [Dhis2-devs] Needed for support with jxl package

Dear all,

I’m using the package as jxl (2.6).
By using this one i’ve got the workbook of the identified excel file.
My excel file has some of formulas in its content as following example:

Ex:

                       (Columns)

                            **A                  B                  C                 D**

(Rows) 1 10
8 0 =SUM(A1:C1)

          **2**                 -                   -                   -                  -

These formulas were set-up and written into the excel file by Java.

The issue was happened when i were reading this excel file and printing out value of each formula in each Cell as D1, it always return as 4.

By the way, if i opened this file and had some of changing into its content then saving again.
After that, re-checking is it return the correct value which is should be as 18.

Any idea or suggestion on this entry ?

Expecting for your all comments !

p.s: I don’t think the error at excel version or by the tool for designing it as MS Office, Open Office, …

Thanks !

Try to place formulae first, e.g. before you add data for cells used by that formulae. From my experience with POI (similar java library for excel) this should help.

regards,
murod.


Hieu.HISPVietnam
Good Health !

Thank you for your ardour, Bob.

Hi Murodullo,

You said that “Placed the formulas first” … but by hands or by coding ?
What’s even else, though I’d tried to place the formulas before adding the data on each cell with both of the above manners (handing or coding). That issue’s still existed.

Did I forget something else ?

On the other hands, that problem which is only happening if I read the excel’s workbook by jxl package.

P.S:
Actually that, we (HISP-VN) have being developed the report module generating the excel report file like the HISP-India team’s idea.

Everything is ok up-to-now, we have making a functionality which is known as “Preview Report”. I’ve done it about 90%.
But very very unfortunately, if using this functionality to preview the report as an excel file. We will meet a trouble at the problem that was presented on previously mail - the formulas in each excel file.

Hopless and waiting your all best ideas.

Thank you all so much !

···


Hieu.HISPVietnam
Good Health !

Hi,

···

From: Hieu Dang Duy hieu.hispvietnam@gmail.com
To: bobjolliffe@gmail.com; Murodullo Latifov murodlatifov@yahoo.com
Cc: dhis2-devs@lists.launchpad.net
Sent: Wednesday, June 17, 2009 6:22:23 AM
Subject: Re: [Dhis2-devs] Needed for support with jxl package

Thank you for your ardour, Bob.

Hi Murodullo,

You said that “Placed the formulas first” … but by hands or by coding ?
What’s even else, though I’d tried to place the formulas before adding the data on each cell with both of the above manners (handing or coding). That issue’s still existed.

Did I forget something else ?

On the other hands, that problem which is only happening if I read the excel’s workbook by jxl package.

P.S:
Actually that, we (HISP-VN) have being developed the report module generating the excel report file like the HISP-India team’s idea.

Everything is ok up-to-now, we have making a functionality which is known as “Preview Report”. I’ve done it about 90%.
But very very unfortunately, if using this functionality to preview the report as an excel file. We will meet a trouble at the problem that was presented on previously mail - the formulas in each excel file.

Hopless and waiting your all best ideas.

Thank you all so much !


Hieu.HISPVietnam
Good Health !

Of course by coding, when you build excel document objects. I know from POI either this way or in reverse (placing formulae last) I managed it to show correct result without opening and refreshing excel file. Are you using existing template for excel or you creating from scratch? Is formulae pretyped in template or you place it programmatically?

regards,
murod.

Hi Murodullo,

Nice to see your replying !

Ooh, we are using a template excel file for reporting not from scratch.

Some of template files have got the pre-typed formulas (by hands), but
Some of clone files which were written the formulas in their content (coding).

As presented before, we’ve used both of the above ways. The issue’s still a trouble of us.

Did you all get any breaking idea ?!

···

On Wed, Jun 17, 2009 at 3:09 PM, Murodullo Latifov murodlatifov@yahoo.com wrote:

Hi,


Of course by coding, when you build excel document objects. I know from POI either this way or in reverse (placing formulae last) I managed it to show correct result without opening and refreshing excel file. Are you using existing template for excel or you creating from scratch? Is formulae pretyped in template or you place it programmatically?

regards,
murod.


Hieu.HISPVietnam
Good Health !

Hi,

Can you post minimal related files (java code, excel template, call to database or so) so we could look at?

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: Wednesday, June 17, 2009 11:17:56 AM
Subject: Re: [Dhis2-devs] Needed for support with jxl package

Hi Murodullo,

Nice to see your replying !

Ooh, we are using a template excel file for reporting not from scratch.

Some of template files have got the pre-typed formulas (by hands), but
Some of clone files which were written the formulas in their content (coding).

As presented before, we’ve used both of the above ways. The issue’s still a trouble of us.

Did you all get any breaking idea ?!

On Wed, Jun 17, 2009 at 3:09 PM, Murodullo Latifov murodlatifov@yahoo.com wrote:

Hi,


Of course by coding, when you build excel document objects. I know from POI either this way or in reverse (placing formulae last) I managed it to show correct result without opening and refreshing excel file. Are you using existing template for excel or you creating from scratch? Is formulae pretyped in template or you place it programmatically?

regards,
murod.


Hieu.HISPVietnam
Good Health !

Accept Granted

Using for CODING:

  • It is an minimal code to set-up and written the formulas into the clone excel file.

  • Maybe, it’s too short to show on your mind. Please trying to read it.

/---------------------------------------------------------------------------------------------------------------------------------/

__…

for ( Integer column : colums )
{

                    String columnName = ExcelUtils.convertColNumberToColName( column );

                    String formula = "SUM(" + columnName + (currentGroupRow + 1) + ":" + columnName

                        + (currentGroupRow + memberOfOrganisationUnitGroups.size()) + ")";

                    ExcelUtils.writeFormula( currentGroupRow, column, formula, sheet, number );

                }

                for ( OrganisationUnit orgUnit : memberOfOrganisationUnitGroups )
                {
                    i++;

                    ExcelUtils.writeValue( serialRow + i, serialCol, String.valueOf( serial ), ExcelUtils.NUMBER,

                        sheet, number );

                    ExcelUtils.writeValue( groupRow + i, groupCol, orgUnit.getName(), ExcelUtils.TEXT, sheet,
                        textLeft );

                    /*

tx_bieu_2.xls (34 KB)

···
                     *
                     */

                    for ( ReportItem reportItem : ((ReportExcelGroupListing) reportExcel).getReportItems() )

                    {

                        if ( !reportItem.getItemType().equalsIgnoreCase( ReportItem.TYPE.SERIAL )
                            && !reportItem.getItemType().equalsIgnoreCase( ReportItem.TYPE.ORGANISATION ) )

                        {

                            if ( reportItem.getItemType().equalsIgnoreCase( ReportItem.TYPE.FORMULA_EXCEL ) )
                            {

                                String formula = reportItem.getExpression();

                                formula = formula.replace( "*", (groupRow + i) + "" ).toUpperCase();

                                ExcelUtils.writeFormula( groupRow + i, reportItem.getColumn(), String

                                    .valueOf( formula ), sheet, number );
                            }
                            else if ( reportItem.getItemType().equalsIgnoreCase( ReportItem.TYPE.DATAELEMENT ) )

                            {
                                ExcelUtils.writeValue( groupRow + i, reportItem.getColumn(), String
                                    .valueOf( getDataValue( reportItem, orgUnit ) ), ExcelUtils.NUMBER, sheet,

                                    number );
                            }
                            else if ( reportItem.getItemType().equalsIgnoreCase( ReportItem.TYPE.INDICATOR ) )
                            {

                                ExcelUtils.writeValue( groupRow + i, reportItem.getColumn(), String
                                    .valueOf( getIndicatorValue( reportItem, orgUnit ) ), ExcelUtils.NUMBER, sheet,

                                    number );
                            }
                        }

                    }

                    serial++;
                }

__

/---------------------------------------------------------------------------------------------------------------------------------/

USING for HANDS:

  • I’ve attached an excel file example for illustrating.
  • You should have copied and searched “TỔNG SỐ” to find out the formula cells.

Thanks !

On Wed, Jun 17, 2009 at 4:21 PM, Murodullo Latifov murodlatifov@yahoo.com wrote:

Hi,

Can you post minimal related files (java code, excel template, call to database or so) so we could look at?

regards,

murod



Hieu.HISPVietnam
Good Health !

Hi,

From code as I see you are placing formula before writing values. Did you try both options? Is ExcelUtils created by you guys? Why don’t you use POI, i think it is much easier and straightforward.

murod

···

Hi,

Affirming is we have done both of options. And … nothing is new.
ExcelUtils.java, this file was created by Tri as a long time ago.
Agian, we still can see the correct answer when opening the generated excel file.

The problem which I presented, is just only met when reading the workbook of this one.

I’ve downloaded the poi-src-3.5-beta5-20090219.zip package. But I don’t know how to use it (hoho so stupid).

Would you mind to say a quite cursory about this POI.

What is it using for ?!
What have you done with this POI in your module ?!
Can you give me some of the concrete examples ?!
Somethings else ???

Thanks for help !

···

On Wed, Jun 17, 2009 at 6:14 PM, Murodullo Latifov murodlatifov@yahoo.com wrote:

Hi,

From code as I see you are placing formula before writing values. Did you try both options? Is ExcelUtils created by you guys? Why don’t you use POI, i think it is much easier and straightforward.

murod



Hieu.HISPVietnam
Good Health !

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.

tx_bieu_2.xls (26 KB)

POIExample.rar (1.64 MB)

···

From: Hieu Dang Duy hieu.hispvietnam@gmail.com
To: Murodullo Latifov murodlatifov@yahoo.com
Cc: bobjolliffe@gmail.com; dhis2-devs@lists.launchpad.net
Sent: Thursday, June 18, 2009 6:27:14 AM
Subject: Re: [Dhis2-devs] Needed for support with jxl package

Hi,

Affirming is we have done both of options. And … nothing is new.
ExcelUtils.java, this file was created by Tri as a long time ago.
Agian, we still can see the correct answer when opening the generated excel file.

The problem which I presented, is just only met when reading the workbook of this one.

I’ve downloaded the poi-src-3.5-beta5-20090219.zip package. But I don’t know how to use it (hoho so stupid).

Would you mind to say a quite cursory about this POI.

What is it using for ?!
What have you done with this POI in your module ?!
Can you give me some of the concrete examples ?!
Somethings else ???

Thanks for help !

On Wed, Jun 17, 2009 at 6:14 PM, Murodullo Latifov murodlatifov@yahoo.com wrote:

Hi,

From code as I see you are placing formula before writing values. Did you try both options? Is ExcelUtils created by you guys? Why don’t you use POI, i think it is much easier and straightforward.

murod



Hieu.HISPVietnam
Good Health !

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.

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 !

TestExcelReportPOI.java (2.67 KB)

Form - tx_bieu_2.xls1245381039109.xls (28 KB)

···

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 !