Abap2xlsx reads incorrectly decimal value from Excel

[hagit]

Hello experts,

I have the following code, which reads an excel file.

REPORT zabap2xlsx_decimal_problem.

TYPES: ltty_alsmex_tabline TYPE TABLE OF alsmex_tabline
      .

DATA: lo_excel                       TYPE REF TO zcl_excel,
      lo_excel_reader                TYPE REF TO zif_excel_reader,
      row                            TYPE int4,
      cell_value                     TYPE zexcel_cell_value,
      column                         TYPE zexcel_cell_column
      .

row = 3.
CREATE OBJECT lo_excel_reader TYPE zcl_excel_reader_2007.
lo_excel = lo_excel_reader->load_file( EXPORTING i_filename = 'C:\NI.XLSX' ).  "@@@@@@@@@@@@@@@
DATA(lo_worksheet)   = lo_excel->get_active_worksheet( ).
DATA(highest_column) = lo_worksheet->get_highest_column( ).
DATA(highest_row)    = lo_worksheet->get_highest_row( ).
column = 1.
DATA(column_conv) = zcl_excel_common=>convert_column2alpha( column ).
lo_worksheet->get_cell(
  EXPORTING
    ip_column = column_conv
    ip_row    = row
  IMPORTING
    ep_value = cell_value
).

write cell_value. "line 29


*DATA(lo_style) = lo_excel->add_new_style( ).
*lo_style->number_format->format_code = zcl_excel_style_number_format=>c_format_number_00.
*lo_worksheet->set_cell( ip_column = column ip_row = row ip_style = lo_style->get_guid( ) ip_value = 243 ).

DATA(lv_value) = round( val = cell_value dec = 4 ). "line 36

lo_worksheet->set_cell(
    ip_column = column_conv
    ip_row    = row
    ip_value  = lv_value ).


lo_worksheet->get_cell(
  EXPORTING
    ip_column = column_conv
    ip_row    = row
  IMPORTING
    ep_value = cell_value
).

write cell_value.

The Excel file has the value 0.8338

But lo_worksheet->get_cell reads it as 0.83379999999999999 (as you can see in line 29). Why does 0.8338 becomes 0.833799..9 ? I excepted it to become 0.8338000…0.

As workaround I can round the cell as shown above in line 36. The solution is not perfect because in real life there is more than one column in the file, so it is necessary to define for which columns the rounding should be performed.

In summary, my questions are:

1. Why does 0.8338 becomes 0.833799..9 ?

2. Do you have a better solution?

Thank you in advance

Hagit

[abo]

Formatting tip: remember to switch back to display mode before copying the code, else you get "<BR>" elements too 🙂

[hagit]

c5e08e0478aa4727abc4482f5be390b2 thank you.

REPORT zabap2xlsx_decimal_problem.
TYPES: ltty_alsmex_tabline TYPE TABLE OF alsmex_tabline
      .
DATA: lo_excel                       TYPE REF TO zcl_excel,
      lo_excel_reader                TYPE REF TO zif_excel_reader,
      row                            TYPE int4,
      cell_value                     TYPE zexcel_cell_value,
      column                         TYPE zexcel_cell_column
      .
row = 3.
CREATE OBJECT lo_excel_reader TYPE zcl_excel_reader_2007.
lo_excel = lo_excel_reader->load_file( EXPORTING i_filename = 'C:\NI.XLSX' ).  "@@@@@@@@@@@@@@@
DATA(lo_worksheet)   = lo_excel->get_active_worksheet( ).
DATA(highest_column) = lo_worksheet->get_highest_column( ).
DATA(highest_row)    = lo_worksheet->get_highest_row( ).
column = 1.
DATA(column_conv) = zcl_excel_common=>convert_column2alpha( column ).
lo_worksheet->get_cell(
  EXPORTING
    ip_column = column_conv
    ip_row    = row
  IMPORTING
    ep_value = cell_value
).
write cell_value. "line 29
*DATA(lo_style) = lo_excel->add_new_style( ).
*lo_style->number_format->format_code = zcl_excel_style_number_format=>c_format_number_00.
*lo_worksheet->set_cell( ip_column = column ip_row = row ip_style = lo_style->get_guid( ) ip_value = 243 ).
DATA(lv_value) = round( val = cell_value dec = 4 ). "line 36
lo_worksheet->set_cell(
    ip_column = column_conv
    ip_row    = row
    ip_value  = lv_value ).
lo_worksheet->get_cell(
  EXPORTING
    ip_column = column_conv
    ip_row    = row
  IMPORTING
    ep_value = cell_value
).
write cell_value.

[abo]

next free tip: posts may be edited 🙂

[hagit]

c5e08e0478aa4727abc4482f5be390b2 thank you for your tips.

Could you please help me with my questions?

[abo]

not right now, unfortunately... I'll come back to the post when I have more time!

[Sandra_Rossi]

Internally, Excel stores as binary floating points, something close to 0.83379999999999999.

Can't you just round it to the number of decimals you need?

[hagit]

sandra.rossi

Thank you for your answer.

1. Sorry but I do not understand why Excel stores 0.8338 as 0.83379999999999999 (and another example – 0.8339 as 0.83389999999999997)

2. I can round it, but as I wrote before: In real life there is more than one column in the file, so it is necessary to define for which columns the rounding should be performed.

[matt]

Excel doesn't store 0.8338 as 0.8337999..999. Excel stores numbers according to IEEE double-precision.

See the best answer here for an explanation of what you're seeing.

This is not a feature of abap2xlsx, it's a feature of Excel. You could read the format of the cell (if set) to find out exactly how many dp you should actually use. Otherwise, you're guaranteed the "correct" number at 15dp.

[hagit]

matthew.billingham Thank you so much for your answer.

Now it is more clear.

How can I read the format of the cell?

[raymond_giuseppi]

Did you try to add the ep_style parameter in the get_cell method call.

(Floating-point arithmetic may give inaccurate results in Excel)

[hagit]

raymond.giuseppi thank you for your answer.

I add the parameter , but it returns initial

[Sandra_Rossi]

It's explained in many places in Excel API and SDK in many languages, e.g. Stack Overflow.

It's how Excel works.

Do as you wish, you just don't have the choice.

In "real life", Excel data is stored in SAP columns which makes it automatically rounded.

[raymond_giuseppi]

So round every numeric amount or quantity type fied to the 15 digits from IEEE 754 specification, or to your expected field definition in Abap dictionnary?

(As already written by matthew.billingham)

[hagit]

raymond.giuseppi and sandra.rossi thank you for your answer.

I will round it in the real program

[Sandra_Rossi]

It depends. It's not only the style, it's also the width of the column. If the style is general and you increase the width, you will see more significant digits.

[abo]

I guess one of the relevant comments could be changed to answer and then be accepted.

[Sandra_Rossi]

To confirm previous comments, I just created Excel with 0.8338, I get this internal format of XLSX file:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac xr xr2 xr3" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2" xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3" xr:uid="{7C46727E-3F5F-46F7-8CDA-7266B2FC90B3}"><dimension ref="A1"/><sheetViews><sheetView tabSelected="1" workbookViewId="0"/></sheetViews><sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25"/><cols><col min="1" max="1" width="16.28515625" customWidth="1"/></cols><sheetData><row r="1" spans="1:1" x14ac:dyDescent="0.25"><c r="A1"><v>0.83379999999999999</v></c></row></sheetData><pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/><pageSetup paperSize="9" orientation="portrait" r:id="rId1"/></worksheet>

[Sandra_Rossi]

c5e08e0478aa4727abc4482f5be390b2 hagit could create an answer by coping all comments 😉

[abo]

true but I'm always for giving credit where it's due

[hagit]

sandra.rossi thank you for your great answer.

now it is more clear

[Sandra_Rossi]

c5e08e0478aa4727abc4482f5be390b2 If nobody wants to post an answer, I guess it's the responsibility of the Original Poster to help future visitors 😉

[Sandra_Rossi]

hagit You can view the XLSX file too. It's like a ZIP file containing mostly XML files. (rename with .zip or open it via your favorite zip application)

[hagit]

Here are comments from matthew.billingham sandra.rossi raymond.giuseppi

sandra.rossi

Internally, Excel stores as binary floating points, something close to 0.83379999999999999.

Can't you just round it to the number of decimals you need?

matthew.billingham

Excel doesn't store 0.8338 as 0.8337999..999. Excel stores numbers according to IEEE double-precision.

See the best answer here for an explanation of what you're seeing.

This is not a feature of abap2xlsx, it's a feature of Excel. You could read the format of the cell (if set) to find out exactly how many dp you should actually use. Otherwise, you're guaranteed the "correct" number at 15dp.

raymond.giuseppi

So round every numeric amount or quantity type fied to the 15 digits from IEEE 754 specification, or to your expected field definition in Abap dictionnary?

sandra.rossi

To confirm previous comments, I just created Excel with 0.8338, I get this internal format of XLSX file:

You can view the XLSX file too. It's like a ZIP file containing mostly XML files. (rename with .zip or open it via your favorite zip application)

[hagit]

sandra.rossi c5e08e0478aa4727abc4482f5be390b2

I created an answer by coping some of the comments