topic Re: Converting Errors (type Time) in Excel Class ( cl_fdt_xl_spreadsheet ) in Application Development and Automation Discussions

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

Former Member — Thu, 31 Mar 2016 07:56:08 GMT

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

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

Juwin — Thu, 31 Mar 2016 13:00:19 GMT

I think these issues are related to the conversions happening in the FDT classes. I had faced some other issue also while using FDT classes to read XLSX file. I raised a OSS message for that.

SAP came back saying FDT is specifically designed for Excel files generated by FDT tools only and is not for public use. They said they won't support, because it was used for a purpose that it was not originally designed for.

Yet, you may give it a try - raise a OSS message and see what happens.

Thanks, Juwin

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

Former Member — Wed, 06 Apr 2016 12:29:22 GMT

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 ?).