Skip to Content
3
Aug 22, 2023 at 07:26 AM

Abap2xlsx reads incorrectly decimal value from Excel

143 Views Last edit Aug 22, 2023 at 09:14 AM 3 rev

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

image.png

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

Attachments

image.png (32.2 kB)