08-22-2023 8:26 AM
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
08-23-2023 7:11 PM
Here are comments from matthew.billingham sandra.rossi raymond.giuseppi
sandra.rossiInternally, 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)
08-22-2023 9:19 AM
Formatting tip: remember to switch back to display mode before copying the code, else you get "<BR>" elements too 🙂
08-22-2023 9:33 AM
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.
08-22-2023 9:51 AM
08-22-2023 10:04 AM
c5e08e0478aa4727abc4482f5be390b2 thank you for your tips.
Could you please help me with my questions?
08-22-2023 10:08 AM
not right now, unfortunately... I'll come back to the post when I have more time!
08-22-2023 1:20 PM
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?
08-22-2023 1:44 PM
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.
08-22-2023 2:27 PM
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.
08-22-2023 2:50 PM
matthew.billingham Thank you so much for your answer.
Now it is more clear.
How can I read the format of the cell?
08-22-2023 3:08 PM
Did you try to add the ep_style parameter in the get_cell method call.
(Floating-point arithmetic may give inaccurate results in Excel)
08-22-2023 3:26 PM
raymond.giuseppi thank you for your answer.
I add the parameter , but it returns initial
08-22-2023 3:27 PM
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.
08-22-2023 3:59 PM
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)
08-22-2023 4:54 PM
raymond.giuseppi and sandra.rossi thank you for your answer.
I will round it in the real program
08-22-2023 6:43 PM
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.
08-22-2023 8:23 PM
I guess one of the relevant comments could be changed to answer and then be accepted.
08-23-2023 7:29 AM
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>
08-23-2023 7:30 AM
c5e08e0478aa4727abc4482f5be390b2 hagit could create an answer by coping all comments 😉
08-23-2023 7:32 AM
08-23-2023 10:40 AM
sandra.rossi thank you for your great answer.
now it is more clear
08-23-2023 11:48 AM
c5e08e0478aa4727abc4482f5be390b2 If nobody wants to post an answer, I guess it's the responsibility of the Original Poster to help future visitors 😉
08-23-2023 12:04 PM
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)
08-23-2023 7:11 PM
Here are comments from matthew.billingham sandra.rossi raymond.giuseppi
sandra.rossiInternally, 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)
08-23-2023 7:14 PM
sandra.rossi c5e08e0478aa4727abc4482f5be390b2
I created an answer by coping some of the comments