Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Converting Errors (type Time) in Excel Class ( cl_fdt_xl_spreadsheet )

Hey everybody,

i'm using the  cl_fdt_xl_spreadsheet class (SAP 7.3) to upload an excel sheet (xstring) to an internal table :

  DATA  lr_excel  TYPE REF TO cl_fdt_xl_spreadsheet.

  DATA  lr_data   TYPE REF TO data.

  CREATE OBJECT lr_excel

       EXPORTING

         document_name = ls_file_upload-file_name

         xdocument     = ls_file_upload-file_data.

   lr_data = lr_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( lv_name ).

Now we get the excel sheet as itab in lr_data.

But after some test cases i noticed some issues.

The time changes in some cases wrong:

1. The time begins everytime with a whitespace for example: '13:12:00' converts to ' 13:12:00'

2. The time '14:00:00' converts to '13:60:00', '17:00:00' to '16:60:00' (every 3 hour)

3. '00:00:00' converts to '0' or '1900-01-01'

4. 1 second to 9 seconds changes it value to 10 to 90 seconds. That is the biggest problem. For example '13:12:02' converts to '13:12:20'

Some issues i can repair, but the issue with the changing seconds i can only fix between 6 and 9 seconds.

Does anyone now why this issue occurs ? Converting the time as 'Text' in excel helps, but this is not a possible solution.

Has somebody an idea how i could fix it ?

Thank you

Tags:
Former Member
Former Member replied

So after debuging the  cl_fdt_xl_spreadsheet class, we found the issues in the private method "CONVERT_DEC_TIME_TO_HHMMSS". This method is a complete joke. Catching randomly like 1-2 of 3-4 cases. Line 28-47 checks, if the seconds are higher then 59, when true, they add 1 to the minutes. Not checking if minutes then are higher 59 and adding 1 to hour, checking if hour is 24 and so on. Concatenating hours minutes and seconds, they check if minutes and hours are under 10 so they add a '0' before the minute and hour, but not for seconds (why ?).

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question