Legacy ooxml-schemas.1.1.jar

classic Classic list List threaded Threaded
28 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Legacy ooxml-schemas.1.1.jar

Eduardo Eduardo
Hi
----
I am new to the list and not sure if my previous post was correctly
received, please apologize if my question was posted already.
----

I have to support both ooxml-schemas-1.1 and ooxml.schemas1.4 in my
application at the same time as I have to support different POI versions
(for legacy purposes). For POI, I used the jarjar utility (
https://github.com/shevek/jarjar ) to repackage the legacy POI
org.apache.poi into org.repackage.org.apache.poi. So the new POI will use
org.apache.poi and the legacy POI will use org.repackage.org.apache.poi.
So far so good.

The problem arrives with the legacy ooxml-schemas-1.1.jar. If I use jarjar
to repackage it, I get a ClassCastException.
I read that this is because of namespace collisions in the XML Schemas, so
I have to recompile ooxml-schemas-1.1.jar with a new namespace that doesn't
collide with the ooxml-schemas.1.4.jar namespace.

In the FAQs I read the following:
"you can run the ant task "compile-ooxml-xsds" to have the OOXML schemas
downloaded and compiled for you".
What would you recommend to change the namespace before compiling them?
What would I have to change and do you have any recommendations for this?
And would I have to change the schema definition in the XLS files too
before working with them?

Best regards,
Eduardo
Reply | Threaded
Open this post in threaded view
|

Re: Legacy ooxml-schemas.1.1.jar

pj.fanning
I replied to original message at
http://apache-poi.1045710.n5.nabble.com/Compiling-ooxml-schemas-1-1-jar-in-a-different-namespace-tc5734173.html

poi-user (this mailing list) is probably best place for this type of query
though.



--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Floating Point Arithmetics, reading 0.1066913 gives 0.10669129999999999

Andreas Reichel
Dear All,

compliments of the day.
We face some challenges with reading values from Spread Sheets.

Example: the numeric cell has the value = 0.1066913 and when reading
that value with POI we receive the double = 0.10669129999999999.

This turns into a problem, when writing these figures into a database,
when the Precision/Scale of the field can't hold that double value,
e.g.

JdbcSQLDataException: Value too long for column """BASE_RATE""
VARCHAR(12)": "'0.10669129999999999' (19)"; SQL statement:
insert /*+PARALLEL APPEND_VALUES*/ into CFE.INTEREST_PERIOD
(ID_INSTRUMENT ,ID_FIXINGMODE ,FIXING_DATE ,CHANGE_DATE ,BASE_RATE
,BASE_MARGIN_RATE ,PAR_RATE ,PAR_MARGIN_RATE ,ID_PAYMENT_CONVENTION
,ID_DAY_COUNT_CONVENTION ,ID_DAY_INCL_CONVENTION ,FIX_AMOUNT
,ID_CURRENCY_FIX_AMOUNT ,ID_SCRIPT)  VALUES (? ,? ,? ,? ,? ,? ,? ,? ,?
,? ,? ,? ,? ,?)  [22001-199]+

Excel itself does not seem to have a problem, because multiplying the
value = 0.1066913 with a large multiplicant 1E15 gives the correct
amount: 0.1066913 x 1E15 = 106691300000000
Furthermore, "similar" values like 0.0813613 or 0.0716913 work well and
will fit.
My question is: How does Excel know the correct value, but POI does
not? And would it not be very very usefull to have a method returning
cell values as BigDecimals instead of Doubles.

At the moment, we work around by advising the users to provide numbers
as Text/String. Although that is counter intuitive and always yields in
having egg on the face.

Thank you already for advise and best regards
Andreas
Reply | Threaded
Open this post in threaded view
|

Re: Floating Point Arithmetics, reading 0.1066913 gives 0.10669129999999999

Dave Fisher
Hi,

You need to format your double when convert to a string. This is basic to using floating point numbers.

Regards,
Dave

Sent from my iPhone

> On Oct 17, 2019, at 7:34 PM, Andreas Reichel <[hidden email]> wrote:
>
> Dear All,
>
> compliments of the day.
> We face some challenges with reading values from Spread Sheets.
>
> Example: the numeric cell has the value = 0.1066913 and when reading
> that value with POI we receive the double = 0.10669129999999999.
>
> This turns into a problem, when writing these figures into a database,
> when the Precision/Scale of the field can't hold that double value,
> e.g.
>
> JdbcSQLDataException: Value too long for column """BASE_RATE""
> VARCHAR(12)": "'0.10669129999999999' (19)"; SQL statement:
> insert /*+PARALLEL APPEND_VALUES*/ into CFE.INTEREST_PERIOD
> (ID_INSTRUMENT ,ID_FIXINGMODE ,FIXING_DATE ,CHANGE_DATE ,BASE_RATE
> ,BASE_MARGIN_RATE ,PAR_RATE ,PAR_MARGIN_RATE ,ID_PAYMENT_CONVENTION
> ,ID_DAY_COUNT_CONVENTION ,ID_DAY_INCL_CONVENTION ,FIX_AMOUNT
> ,ID_CURRENCY_FIX_AMOUNT ,ID_SCRIPT)  VALUES (? ,? ,? ,? ,? ,? ,? ,? ,?
> ,? ,? ,? ,? ,?)  [22001-199]+
>
> Excel itself does not seem to have a problem, because multiplying the
> value = 0.1066913 with a large multiplicant 1E15 gives the correct
> amount: 0.1066913 x 1E15 = 106691300000000
> Furthermore, "similar" values like 0.0813613 or 0.0716913 work well and
> will fit.
> My question is: How does Excel know the correct value, but POI does
> not? And would it not be very very usefull to have a method returning
> cell values as BigDecimals instead of Doubles.
>
> At the moment, we work around by advising the users to provide numbers
> as Text/String. Although that is counter intuitive and always yields in
> having egg on the face.
>
> Thank you already for advise and best regards
> Andreas


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Floating Point Arithmetics, reading 0.1066913 gives 0.10669129999999999

kiwiwings
In reply to this post by Andreas Reichel
Hello Andreas

this issue pops up every now and then. Please have a look at
http://apache-poi.1045710.n5.nabble.com/Floating-point-behaviour-difference-between-POI-and-Excel-td5715765.html

So Excel rounds to 15 digits. I'm undecided about the BigDecimal API, so I
leave it to the others to decide.

Regarding the DB inserts, why do you store numbers in varchars(12)? ... I
would expect the jdbc API to truncate/round the doubles and use the maximum
number type. If the schema is not in your hands, you have to do the rounding
... for being consistent with Excel, you probably need to do the rounding
anyways or use the DataFormatter.

Andi






--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Floating Point Arithmetics, reading 0.1066913 gives 0.10669129999999999

Andreas Reichel
Hi Andi and Team,

thank you for prompt response.

On Thu, 2019-10-17 at 20:11 -0700, kiwiwings wrote:

> this issue pops up every now and then. Please have a look at
> http://apache-poi.1045710.n5.nabble.com/Floating-point-behaviour-difference-between-POI-and-Excel-td5715765.html

I will read through that.

> So Excel rounds to 15 digits. I'm undecided about the BigDecimal API,
> so Ileave it to the others to decide.

Pardon my curiosity: what are the Cons? I might be biased by my
profession, but I believe floating point arithmetic sucks.

> Regarding the DB inserts, why do you store numbers in varchars(12)?
> ... Iwould expect the jdbc API to truncate/round the doubles and use
> the maximumnumber type.

Well, we hit an exceptional field here: Base Rate can be a number (like
5% p.a.) or a reference to a yield curve like LIBOR or EURIBOR.
Would we not hit exactly the same problem with NUMBER(3,9) or
DECIMAL(3,9) when 0.10669129999999999 exceeds the precision/scale?

Thank you again and cheers
Andreas
Reply | Threaded
Open this post in threaded view
|

Re: Floating Point Arithmetics, reading 0.1066913 gives 0.10669129999999999

Dominik Stadler
Hi,

Biggest con: BigInteger are slower by an order of magnitude and use up much
more CPU, think processing files with millions of lines, which users of POI
do.

Also it is more complex to handle and you still need to decide how you
round numbers when you decide to store them as strings.

Dominik

On Fri, Oct 18, 2019, 05:08 Andreas Reichel <[hidden email]>
wrote:

> Hi Andi and Team,
>
> thank you for prompt response.
>
> On Thu, 2019-10-17 at 20:11 -0700, kiwiwings wrote:
>
> > this issue pops up every now and then. Please have a look at
> >
> http://apache-poi.1045710.n5.nabble.com/Floating-point-behaviour-difference-between-POI-and-Excel-td5715765.html
>
> I will read through that.
>
> > So Excel rounds to 15 digits. I'm undecided about the BigDecimal API,
> > so Ileave it to the others to decide.
>
> Pardon my curiosity: what are the Cons? I might be biased by my
> profession, but I believe floating point arithmetic sucks.
>
> > Regarding the DB inserts, why do you store numbers in varchars(12)?
> > ... Iwould expect the jdbc API to truncate/round the doubles and use
> > the maximumnumber type.
>
> Well, we hit an exceptional field here: Base Rate can be a number (like
> 5% p.a.) or a reference to a yield curve like LIBOR or EURIBOR.
> Would we not hit exactly the same problem with NUMBER(3,9) or
> DECIMAL(3,9) when 0.10669129999999999 exceeds the precision/scale?
>
> Thank you again and cheers
> Andreas
>
Reply | Threaded
Open this post in threaded view
|

Re: Floating Point Arithmetics, reading 0.1066913 gives 0.10669129999999999

David Law-2
In reply to this post by Andreas Reichel
Hi Andreas,

I tried to create a Spreadsheet to reproduce this but failed.
(it returned double = 0.1066913)

Could you share an example.xslsx with us to demonstrate the behaviour?

All the best,
DaveLaw

On 18/10/2019 04:34, Andreas Reichel wrote:

> Dear All,
>
> compliments of the day.
> We face some challenges with reading values from Spread Sheets.
>
> Example: the numeric cell has the value = 0.1066913 and when reading
> that value with POI we receive the double = 0.10669129999999999.
>
> This turns into a problem, when writing these figures into a database,
> when the Precision/Scale of the field can't hold that double value,
> e.g.
>
> JdbcSQLDataException: Value too long for column """BASE_RATE""
> VARCHAR(12)": "'0.10669129999999999' (19)"; SQL statement:
> insert /*+PARALLEL APPEND_VALUES*/ into CFE.INTEREST_PERIOD
> (ID_INSTRUMENT ,ID_FIXINGMODE ,FIXING_DATE ,CHANGE_DATE ,BASE_RATE
> ,BASE_MARGIN_RATE ,PAR_RATE ,PAR_MARGIN_RATE ,ID_PAYMENT_CONVENTION
> ,ID_DAY_COUNT_CONVENTION ,ID_DAY_INCL_CONVENTION ,FIX_AMOUNT
> ,ID_CURRENCY_FIX_AMOUNT ,ID_SCRIPT)  VALUES (? ,? ,? ,? ,? ,? ,? ,? ,?
> ,? ,? ,? ,? ,?)  [22001-199]+
>
> Excel itself does not seem to have a problem, because multiplying the
> value = 0.1066913 with a large multiplicant 1E15 gives the correct
> amount: 0.1066913 x 1E15 = 106691300000000
> Furthermore, "similar" values like 0.0813613 or 0.0716913 work well and
> will fit.
> My question is: How does Excel know the correct value, but POI does
> not? And would it not be very very usefull to have a method returning
> cell values as BigDecimals instead of Doubles.
>
> At the moment, we work around by advising the users to provide numbers
> as Text/String. Although that is counter intuitive and always yields in
> having egg on the face.
>
> Thank you already for advise and best regards
> Andreas
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Floating Point Arithmetics, reading 0.1066913 gives 0.10669129999999999

Schene, Chris-3
I think a lot of financial institutions used BCD (Binary Coded Decimal) because it is accurate without round off like floating point.

Christopher Schene
Field Engineer
Fraud & Identity Client Implementations
16260 N. 71st Street, Suite 400 | Scottsdale, Arizona 85254
www.experian.com<http://www.experian.com/>
experian


From: David Law <[hidden email]>
Reply-To: POI Users List <[hidden email]>
Date: Friday, October 18, 2019 at 1:47 PM
To: "[hidden email]" <[hidden email]>
Subject: Re: Floating Point Arithmetics, reading 0.1066913 gives 0.10669129999999999

Hi Andreas,

I tried to create a Spreadsheet to reproduce this but failed.
(it returned double = 0.1066913)

Could you share an example.xslsx with us to demonstrate the behaviour?

All the best,
DaveLaw

On 18/10/2019 04:34, Andreas Reichel wrote:
Dear All,

compliments of the day.
We face some challenges with reading values from Spread Sheets.

Example: the numeric cell has the value = 0.1066913 and when reading
that value with POI we receive the double = 0.10669129999999999.

This turns into a problem, when writing these figures into a database,
when the Precision/Scale of the field can't hold that double value,
e.g.

JdbcSQLDataException: Value too long for column """BASE_RATE""
VARCHAR(12)": "'0.10669129999999999' (19)"; SQL statement:
insert /*+PARALLEL APPEND_VALUES*/ into CFE.INTEREST_PERIOD
(ID_INSTRUMENT ,ID_FIXINGMODE ,FIXING_DATE ,CHANGE_DATE ,BASE_RATE
,BASE_MARGIN_RATE ,PAR_RATE ,PAR_MARGIN_RATE ,ID_PAYMENT_CONVENTION
,ID_DAY_COUNT_CONVENTION ,ID_DAY_INCL_CONVENTION ,FIX_AMOUNT
,ID_CURRENCY_FIX_AMOUNT ,ID_SCRIPT)  VALUES (? ,? ,? ,? ,? ,? ,? ,? ,?
,? ,? ,? ,? ,?)  [22001-199]+

Excel itself does not seem to have a problem, because multiplying the
value = 0.1066913 with a large multiplicant 1E15 gives the correct
amount: 0.1066913 x 1E15 = 106691300000000
Furthermore, "similar" values like 0.0813613 or 0.0716913 work well and
will fit.
My question is: How does Excel know the correct value, but POI does
not? And would it not be very very usefull to have a method returning
cell values as BigDecimals instead of Doubles.

At the moment, we work around by advising the users to provide numbers
as Text/String. Although that is counter intuitive and always yields in
having egg on the face.

Thank you already for advise and best regards
Andreas



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]<mailto:[hidden email]>
For additional commands, e-mail: [hidden email]<mailto:[hidden email]>


Reply | Threaded
Open this post in threaded view
|

Re: Floating Point Arithmetics, reading 0.1066913 gives 0.10669129999999999

David Law-2
What we IBM Mainframers call "packed decimal".
Rounding is still an issue: try dividing 2 by 3.

But that doesn't help us here:
Excel calculates using Floating Point
so we just need to learn to live with its advantages & disadvantages.

All the best,
DaveLaw

On 18/10/2019 22:55, Schene, Chris wrote:

> I think a lot of financial institutions used BCD (Binary Coded Decimal) because it is accurate without round off like floating point.
>
> Christopher Schene
> Field Engineer
> Fraud & Identity Client Implementations
> 16260 N. 71st Street, Suite 400 | Scottsdale, Arizona 85254
> www.experian.com<http://www.experian.com/>
> experian
>
>
> From: David Law <[hidden email]>
> Reply-To: POI Users List <[hidden email]>
> Date: Friday, October 18, 2019 at 1:47 PM
> To: "[hidden email]" <[hidden email]>
> Subject: Re: Floating Point Arithmetics, reading 0.1066913 gives 0.10669129999999999
>
> Hi Andreas,
>
> I tried to create a Spreadsheet to reproduce this but failed.
> (it returned double = 0.1066913)
>
> Could you share an example.xslsx with us to demonstrate the behaviour?
>
> All the best,
> DaveLaw
>
> On 18/10/2019 04:34, Andreas Reichel wrote:
> Dear All,
>
> compliments of the day.
> We face some challenges with reading values from Spread Sheets.
>
> Example: the numeric cell has the value = 0.1066913 and when reading
> that value with POI we receive the double = 0.10669129999999999.
>
> This turns into a problem, when writing these figures into a database,
> when the Precision/Scale of the field can't hold that double value,
> e.g.
>
> JdbcSQLDataException: Value too long for column """BASE_RATE""
> VARCHAR(12)": "'0.10669129999999999' (19)"; SQL statement:
> insert /*+PARALLEL APPEND_VALUES*/ into CFE.INTEREST_PERIOD
> (ID_INSTRUMENT ,ID_FIXINGMODE ,FIXING_DATE ,CHANGE_DATE ,BASE_RATE
> ,BASE_MARGIN_RATE ,PAR_RATE ,PAR_MARGIN_RATE ,ID_PAYMENT_CONVENTION
> ,ID_DAY_COUNT_CONVENTION ,ID_DAY_INCL_CONVENTION ,FIX_AMOUNT
> ,ID_CURRENCY_FIX_AMOUNT ,ID_SCRIPT)  VALUES (? ,? ,? ,? ,? ,? ,? ,? ,?
> ,? ,? ,? ,? ,?)  [22001-199]+
>
> Excel itself does not seem to have a problem, because multiplying the
> value = 0.1066913 with a large multiplicant 1E15 gives the correct
> amount: 0.1066913 x 1E15 = 106691300000000
> Furthermore, "similar" values like 0.0813613 or 0.0716913 work well and
> will fit.
> My question is: How does Excel know the correct value, but POI does
> not? And would it not be very very usefull to have a method returning
> cell values as BigDecimals instead of Doubles.
>
> At the moment, we work around by advising the users to provide numbers
> as Text/String. Although that is counter intuitive and always yields in
> having egg on the face.
>
> Thank you already for advise and best regards
> Andreas
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]<mailto:[hidden email]>
> For additional commands, e-mail: [hidden email]<mailto:[hidden email]>
>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Floating Point Arithmetics, reading 0.1066913 gives 0.10669129999999999

Andreas Reichel
In reply to this post by David Law-2
Dear David and Team,

please see a reproducible test case attached:

import java.io.File;
import java.io.IOException;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.*;

public class ExcelTest {
  public static void main(String[] args) {
    File file = new File ("/tmp/ifrsbox_DataCapture.xlsx");
    
    try {
      Workbook wb = WorkbookFactory.create(file);
      Sheet sheet = wb.getSheet("INTEREST_PERIOD");
      
      for (int r=4; r<100; r++) {
        Row row = sheet.getRow(r);
        Cell cell = row.getCell(5);
        
        if (cell.getCellType().equals(CellType.NUMERIC)) {
          double value = cell.getNumericCellValue();
          System.out.println(value);
        }
      }
      
    } catch (IOException ex) {
      Logger.getLogger(ExcelTest.class.getName()).log(Level.SEVERE, null, ex);
    } catch (EncryptedDocumentException ex) {
      Logger.getLogger(ExcelTest.class.getName()).log(Level.SEVERE, null, ex);
    }
  }
}


Output:

0.0813613
0.089153
0.10929
0.097758
0.0716913
0.10669129999999999
0.082126
0.08669129999999999
0.0966913
0.0966913
0.08669129999999999
0.0861688
0.0966913
0.0966913
0.0966913
0.09851880000000002
0.09845000000000001
0.08669129999999999
0.08669129999999999
0.09544380000000001
0.0766913
...


On Fri, 2019-10-18 at 22:47 +0200, David Law wrote:
Hi Andreas,

I tried to create a Spreadsheet to reproduce this but failed.
(it returned double = 0.1066913)

Could you share an example.xslsx with us to demonstrate the behaviour?

All the best,
DaveLaw

On 18/10/2019 04:34, Andreas Reichel wrote:
Dear All,

compliments of the day.
We face some challenges with reading values from Spread Sheets.

Example: the numeric cell has the value = 0.1066913 and when reading
that value with POI we receive the double = 0.10669129999999999.

This turns into a problem, when writing these figures into a database,
when the Precision/Scale of the field can't hold that double value,
e.g.

JdbcSQLDataException: Value too long for column """BASE_RATE""
VARCHAR(12)": "'0.10669129999999999' (19)"; SQL statement:
insert /*+PARALLEL APPEND_VALUES*/ into CFE.INTEREST_PERIOD
(ID_INSTRUMENT ,ID_FIXINGMODE ,FIXING_DATE ,CHANGE_DATE ,BASE_RATE
,BASE_MARGIN_RATE ,PAR_RATE ,PAR_MARGIN_RATE ,ID_PAYMENT_CONVENTION
,ID_DAY_COUNT_CONVENTION ,ID_DAY_INCL_CONVENTION ,FIX_AMOUNT
,ID_CURRENCY_FIX_AMOUNT ,ID_SCRIPT)  VALUES (? ,? ,? ,? ,? ,? ,? ,? ,?
,? ,? ,? ,? ,?)  [22001-199]+

Excel itself does not seem to have a problem, because multiplying the
value = 0.1066913 with a large multiplicant 1E15 gives the correct
amount: 0.1066913 x 1E15 = 106691300000000
Furthermore, "similar" values like 0.0813613 or 0.0716913 work well and
will fit.
My question is: How does Excel know the correct value, but POI does
not? And would it not be very very usefull to have a method returning
cell values as BigDecimals instead of Doubles.

At the moment, we work around by advising the users to provide numbers
as Text/String. Although that is counter intuitive and always yields in
having egg on the face.

Thank you already for advise and best regards
Andreas



---------------------------------------------------------------------
To unsubscribe, e-mail: 
[hidden email]

For additional commands, e-mail: 
[hidden email]





---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

ifrsbox_DataCaptureOct.xlsx (634K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Floating Point Arithmetics, reading 0.1066913 gives 0.10669129999999999 --> Problem only with Excel, NOT with LibreOffice

Andreas Reichel
All,

it gets interesting: I actually did not want to send the original file and so I edited that file in LibreOffice, removed all the unneeded worksheets and saved it.
Guess what?! The test output looks correct now:

0.0813613
0.089153
0.10929
0.097758
0.0716913
0.1066913
0.082126
0.0866913
0.0966913
0.0966913
0.0866913
0.0861688
0.0966913
0.0966913
0.0966913
0.0985188
0.09845
0.0866913
0.0866913
0.0954438
0.0766913
0.09845

(I have no MS Excel and can't play with it. I believe, the client who sent the original files uses MS Excel 2013, but I do not know for sure.)

Cheers
Andreas


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

ifrsbox_DataCapture.xlsx (43K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Floating Point Arithmetics, reading 0.1066913 gives 0.10669129999999999 --> Problem only with Excel, NOT with LibreOffice

Andreas Reichel
Libre Office writes:

                <row r="10" customFormat="false" ht="12.75"
hidden="false" customHeight="false" outlineLevel="0" collapsed="false">
                        <c r="B10" s="1" t="s">
                                <v>48</v>
                        </c>
                        <c r="C10" s="1" t="s">
                                <v>40</v>
                        </c>
                        <c r="D10" s="5" t="n">
                                <v>43622</v>
                        </c>
                        <c r="F10" s="6" t="n">
                                <v>0.1066913</v>
                        </c>
                        <c r="J10" s="1" t="s">
                                <v>41</v>
                        </c>
                        <c r="K10" s="1" t="s">
                                <v>42</v>
                        </c>
                </row>

while Excel wrote:

                <row r="10" customFormat="false" ht="12.8"
hidden="false" customHeight="false" outlineLevel="0" collapsed="false">
                        <c r="B10" s="1" t="s">
                                <v>26</v>
                        </c>
                        <c r="C10" s="1" t="s">
                                <v>507</v>
                        </c>
                        <c r="D10" s="6" t="n">
                                <v>43622</v>
                        </c>
                        <c r="F10" s="10" t="s">
                                <v>510</v>
                        </c>
                        <c r="J10" s="1" t="s">
                                <v>508</v>
                        </c>
                        <c r="K10" s="1" t="s">
                                <v>509</v>
                        </c>
                </row>

Sorry, I am lost here?! How to interprete that "510"?!

On Sat, 2019-10-19 at 13:53 +0700, Andreas Reichel wrote:

> All,
>
> it gets interesting: I actually did not want to send the original
> file and so I edited that file in LibreOffice, removed all the
> unneeded worksheets and saved it.
> Guess what?! The test output looks correct now:
>
> 0.0813613
> 0.089153
> 0.10929
> 0.097758
> 0.0716913
> 0.1066913
> 0.082126
> 0.0866913
> 0.0966913
> 0.0966913
> 0.0866913
> 0.0861688
> 0.0966913
> 0.0966913
> 0.0966913
> 0.0985188
> 0.09845
> 0.0866913
> 0.0866913
> 0.0954438
> 0.0766913
> 0.09845
>
> (I have no MS Excel and can't play with it. I believe, the client who
> sent the original files uses MS Excel 2013, but I do not know for
> sure.)
>
> Cheers
> Andreas
>
> -------------------------------------------------------------------
> --To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Floating Point Arithmetics, reading 0.1066913 gives 0.10669129999999999 --> Problem only with Excel, NOT with LibreOffice

Andreas Reichel
In reply to this post by Andreas Reichel
Apologies, wrong file.

Correction, Excel wrote:

                 <row r="10" spans="1:15">
                        <c r="B10" s="7" t="s">
                                <v>26</v>
                        </c>
                        <c r="C10" s="1" t="s">
                                <v>376</v>
                        </c>
                        <c r="D10" s="6">
                                <v>43622</v>
                        </c>
                        <c r="F10" s="12">
                                <v>0.10669129999999999</v>
                        </c>
                        <c r="J10" s="1" t="s">
                                <v>377</v>
                        </c>
                        <c r="K10" s="1" t="s">
                                <v>378</v>
                        </c>
                </row>

Looks to me like Excel is to blame.
Although I still wonder: How does it know internally, that the actual
value is 0.1066913 but not 0.10669129999999999?!

Best regards
Andreas



Reply | Threaded
Open this post in threaded view
|

Re: Floating Point Arithmetics, reading 0.1066913 gives 0.10669129999999999

David Law-2
In reply to this post by Andreas Reichel
Hello Andreas,

I think if you check the original XML of your *.xlsx,
you'll find that it contains something like:
<v>0.10669129999999999</v>

This value can be mapped EXACTLY to a double in Java.
So that's what you're getting.

In other cases you may find XML values like:
<v>8.6168800000000004E-2</v>

There is NO exact Java double which matches that!!
(it actually has greater precision than is possible with a Java double)
The closest is 0.0861688

Which raises the questions:
Q) What floating-point convention does Microsoft use in Excel?
Q) Why is Excel displaying it as 0.1066913?

Maybe the answer to that is here:
https://en.wikipedia.org/wiki/Extended_precision#x86_extended_precision_format

I've enclosed some output from a little test proggy (see later)
showing some possible Test values from an XML Excel
and the closest Java doubles to them.

Working with Spreadsheets & Floating-Point you will always get such values.

I would suggest, depending on the application,
Varchar(12) may not an appropriate Datatype for storage of this value in
the DB?
Its also not cheap to convert backwards & forwards between String & Numeric.
(parsing a String to numeric is particularly costly)

Maybe Numeric(nn.6, say) might be more appropriate?
You'll obviously need to round or truncate as necessary.
Either with some proprietary code (via int?) or via BigDecimal?
(but remember, as Dominik Stadler pointed out, BigDecimal is pretty slow)

Not sure what your options are getting your value into JDBC.
You might well find BigDecimal under the bonnet in JDBC anyway.

Anyway, as promised, some Test-Cases, showing the original Excel values
and the closest Java doubles to them with the internal raw hex of the
doubles:
Excel value (XML)..:    0.10669129999999999
----------------------------------------------------------------------
Java double (Init).:    0.10669129999999998      3fbb501efc44899b (raw)
Java double (Next).:    0.10669129999999999      3fbb501efc44899c (raw)
Java double (Next).:    0.1066913                3fbb501efc44899d (raw)

Excel value (XML)..:    0.086168800000000004 (8.6168800000000004E-2)
----------------------------------------------------------------------
Java double (Init).:    0.08616879999999999      3fb60f2891ef812c (raw)
Java double (Next).:    0.0861688                3fb60f2891ef812d (raw)
Java double (Next).:    0.08616880000000002      3fb60f2891ef812e (raw)

Excel value (XML)..:    0.086691299999999985 (8.6691299999999985E-2)
----------------------------------------------------------------------
Java double (Init).:    0.08669129999999997      3fb63166aa59047c (raw)
Java double (Next).:    0.08669129999999999      3fb63166aa59047d (raw)
Java double (Next).:    0.0866913                3fb63166aa59047e (raw)

Excel value (XML)..:    0.096691299999999994 (9.6691299999999994E-2)
----------------------------------------------------------------------
Java double (Init).:    0.09669129999999998      3fb8c0c2d34ec70c (raw)
Java double (Next).:    0.0966913                3fb8c0c2d34ec70d (raw)
Java double (Next).:    0.09669130000000001      3fb8c0c2d34ec70e (raw)

All the best,
DaveLaw


On 18/10/2019 04:34, Andreas Reichel wrote:

> Dear All,
>
> compliments of the day.
> We face some challenges with reading values from Spread Sheets.
>
> Example: the numeric cell has the value = 0.1066913 and when reading
> that value with POI we receive the double = 0.10669129999999999.
>
> This turns into a problem, when writing these figures into a database,
> when the Precision/Scale of the field can't hold that double value,
> e.g.
>
> JdbcSQLDataException: Value too long for column """BASE_RATE""
> VARCHAR(12)": "'0.10669129999999999' (19)"; SQL statement:
> insert /*+PARALLEL APPEND_VALUES*/ into CFE.INTEREST_PERIOD
> (ID_INSTRUMENT ,ID_FIXINGMODE ,FIXING_DATE ,CHANGE_DATE ,BASE_RATE
> ,BASE_MARGIN_RATE ,PAR_RATE ,PAR_MARGIN_RATE ,ID_PAYMENT_CONVENTION
> ,ID_DAY_COUNT_CONVENTION ,ID_DAY_INCL_CONVENTION ,FIX_AMOUNT
> ,ID_CURRENCY_FIX_AMOUNT ,ID_SCRIPT)  VALUES (? ,? ,? ,? ,? ,? ,? ,? ,?
> ,? ,? ,? ,? ,?)  [22001-199]+
>
> Excel itself does not seem to have a problem, because multiplying the
> value = 0.1066913 with a large multiplicant 1E15 gives the correct
> amount: 0.1066913 x 1E15 = 106691300000000
> Furthermore, "similar" values like 0.0813613 or 0.0716913 work well and
> will fit.
> My question is: How does Excel know the correct value, but POI does
> not? And would it not be very very usefull to have a method returning
> cell values as BigDecimals instead of Doubles.
>
> At the moment, we work around by advising the users to provide numbers
> as Text/String. Although that is counter intuitive and always yields in
> having egg on the face.
>
> Thank you already for advise and best regards
> Andreas
>

Reply | Threaded
Open this post in threaded view
|

Re: Floating Point Arithmetics, reading 0.1066913 gives 0.10669129999999999

Andreas Reichel
Good Morning David and All,

thank you so much for your time and effort! I appreciate.

At this point, I believe it is only MS Excel to blame for:

On Sat, 2019-10-19 at 18:45 +0200, David Law wrote:
> I think if you check the original XML of your *.xlsx,
> you'll find that it contains something like:
> <v>0.10669129999999999</v>
>
> This value can be mapped EXACTLY to a double in Java.
> So that's what you're getting.

(Minor objection here: Edit this spreadsheet and multiply the
0.10669129999999999 with 1E15 and you will see that Excel actually uses
0.1066913 internally instead.)

>
> In other cases you may find XML values like:
> <v>8.6168800000000004E-2</v>
>

It obviously writes different values like 0.10669129999999999 into the
file then it shown/used for its own calculations (like 0.1066913). I
only wonder how Excel itself knows the difference.
Using LibreOffice fixed the problem immediately. Perhaps newer versions
of MS Excel also fixed that, although I am not even keen to try.

As for the JDBC/database issue, I believe it is a complete different
topic. The definition of VARCHAR(12) is there for a good reason
(because both TEXT and or NUMBER can be written into that field).
Same problem would arise with any other column type when precision was
set to less than 15 digits.

It is actully more like a feature, because the solution was to deliver
the figures as text in the spreadsheet (capturing text '0.1066913
instead of number 0.1066913).

We can close this case as it is not a POI related challenge.
Thank you again and cheers

Andreas
Reply | Threaded
Open this post in threaded view
|

Re: Floating Point Arithmetics, reading 0.1066913 gives 0.10669129999999999 --> GNUMERIC vs LibreOffice vs Excel

Andreas Reichel
Guys, I am sorry: just one more for the fun.

Opening the file in an XML Text Editor, I get 0.10669129999999999.
Opening the file in GNUMERIC, I get 0.10669129999999999. (Both the
shown cell content as well as the editable text box).
Opening the file in LibreOffice, I get 0.1066913. (Same file, I have
tried 3 times. Both the shown cell content as well as the editable text
box).
Opening the file in MS Excel, I get 0.1066913.

I can guarantee that the end-user has captured 0.1066913 (because its a
nominal interest rate, nobody would negotiate or type
0.10669129999999999).

So, while it is still an Excel problem, I wonder how/why LibreOffice
and Excel know/decide about interpreting the value as  0.1066913?!
And as annoying as this all is, could POI follow that interpretation,
simply because that is what a simple end-user would have expected? At
least LibreOffice seems to know and do something, that POI does not.

Cheers
Andreas
Reply | Threaded
Open this post in threaded view
|

Re: Floating Point Arithmetics, reading 0.1066913 gives 0.10669129999999999

David Law-2
In reply to this post by Andreas Reichel
Hi Folks,

I've written a little proggy to demonstrate how to get the Raw Cell
Value into a BigDecimal.

Please find attached, together with a (new!!) Excel with Testdata.

This seems to workaround the problem fairly well.

Best regards,
DaveLaw

On 18/10/2019 04:34, Andreas Reichel wrote:

> Dear All,
>
> compliments of the day.
> We face some challenges with reading values from Spread Sheets.
>
> Example: the numeric cell has the value = 0.1066913 and when reading
> that value with POI we receive the double = 0.10669129999999999.
>
> This turns into a problem, when writing these figures into a database,
> when the Precision/Scale of the field can't hold that double value,
> e.g.
>
> JdbcSQLDataException: Value too long for column """BASE_RATE""
> VARCHAR(12)": "'0.10669129999999999' (19)"; SQL statement:
> insert /*+PARALLEL APPEND_VALUES*/ into CFE.INTEREST_PERIOD
> (ID_INSTRUMENT ,ID_FIXINGMODE ,FIXING_DATE ,CHANGE_DATE ,BASE_RATE
> ,BASE_MARGIN_RATE ,PAR_RATE ,PAR_MARGIN_RATE ,ID_PAYMENT_CONVENTION
> ,ID_DAY_COUNT_CONVENTION ,ID_DAY_INCL_CONVENTION ,FIX_AMOUNT
> ,ID_CURRENCY_FIX_AMOUNT ,ID_SCRIPT)  VALUES (? ,? ,? ,? ,? ,? ,? ,? ,?
> ,? ,? ,? ,? ,?)  [22001-199]+
>
> Excel itself does not seem to have a problem, because multiplying the
> value = 0.1066913 with a large multiplicant 1E15 gives the correct
> amount: 0.1066913 x 1E15 = 106691300000000
> Furthermore, "similar" values like 0.0813613 or 0.0716913 work well and
> will fit.
> My question is: How does Excel know the correct value, but POI does
> not? And would it not be very very usefull to have a method returning
> cell values as BigDecimals instead of Doubles.
>
> At the moment, we work around by advising the users to provide numbers
> as Text/String. Although that is counter intuitive and always yields in
> having egg on the face.
>
> Thank you already for advise and best regards
> Andreas
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

ApachePoiExcelReadManticoreScaledTest.java (3K) Download Attachment
ifrsbox_DataCaptureExample.xlsx (33K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Floating Point Arithmetics, reading 0.1066913 gives 0.10669129999999999 --> GNUMERIC vs LibreOffice vs Excel

Nick Burch-2
In reply to this post by Andreas Reichel
On Sun, 20 Oct 2019, Andreas Reichel wrote:

> Opening the file in an XML Text Editor, I get 0.10669129999999999.
> Opening the file in GNUMERIC, I get 0.10669129999999999. (Both the
> shown cell content as well as the editable text box).
> Opening the file in LibreOffice, I get 0.1066913. (Same file, I have
> tried 3 times. Both the shown cell content as well as the editable text
> box).
> Opening the file in MS Excel, I get 0.1066913.
>
> So, while it is still an Excel problem, I wonder how/why LibreOffice
> and Excel know/decide about interpreting the value as  0.1066913?!

If you ask POI to format the value to a string based on the formatting
rules applied to the cell, using DataFormatter or similar, do you get the
value you expect?

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Floating Point Arithmetics, reading 0.1066913 gives 0.10669129999999999 --> GNUMERIC vs LibreOffice vs Excel

David Law-2
In reply to this post by Andreas Reichel
Hi Nick,

the Cells have no Format.  Take a look at the attached File, cell F10.
Andreas tells me it was entered as 0.1066913 & that's how its displayed
too, although it has no format.

Internally, in the XML, its saved as:
<v>0.10669129999999999</v>
which has an exact Java double representation.

POI returns 0.10669129999999999.

But Excel & LibreOffice both somehow manage to show 0.1066913 !

In columns P-Q I've tried various permutations.
But Excel is not able to format that value in full length (see Cell T10).

Take a look at Cell F12, 0.0866913.  Internally its stored as:
<v>8.6691299999999985E-2</v>
This value is NOT representable as a Java double.

POI returns 0.08669129999999999.

Excel value (XML)..:    0.086691299999999985 (8.6691299999999985E-2)
----------------------------------------------------------------------
Java double (Init).:    0.08669129999999997 3fb63166aa59047c (raw)
Java double (Next).:    0.08669129999999999 3fb63166aa59047d (raw)
Java double (Next).:    0.0866913 3fb63166aa59047e (raw)

Internally, Excel seems to be storing some values in an extended precision?

Can you give us some insight?

All the best,
DaveLaw

On 20/10/2019 18:21, Nick Burch wrote:

> On Sun, 20 Oct 2019, Andreas Reichel wrote:
>> Opening the file in an XML Text Editor, I get 0.10669129999999999.
>> Opening the file in GNUMERIC, I get 0.10669129999999999. (Both the
>> shown cell content as well as the editable text box).
>> Opening the file in LibreOffice, I get 0.1066913. (Same file, I have
>> tried 3 times. Both the shown cell content as well as the editable text
>> box).
>> Opening the file in MS Excel, I get 0.1066913.
>>
>> So, while it is still an Excel problem, I wonder how/why LibreOffice
>> and Excel know/decide about interpreting the value as 0.1066913?!
>
> If you ask POI to format the value to a string based on the formatting
> rules applied to the cell, using DataFormatter or similar, do you get
> the value you expect?
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

ifrsbox_DataCaptureExample.xlsx (33K) Download Attachment
12