Leading zeros gets removed while downloading to excel

[former_member242512]

Hi All

There is an issue when we download any value which has leading zeros it gets removed in downloaded file.

i.e 000345  becomes 345.

Solution i found was to add '   before the filed value so in csv file we get data as '000345, but i want simple 000345.

Also using character data type or other doen't helps.

Is there any good solution we can get data as required in excel or csv file?

Regards

Ujjwal

[kesavadas_thekkillath]

Hi,

Adding a quotation is a temporary solution, instead use OLE for download & upload,

Follow the steps below: ( The below code is not checked for syntax and its only a part of code written, it is only for your reference ).

Get the field descriptions of your local structure or global structure used using the available options( please search in SCN ). Assume the details are available in it_fields and the contents in it_data. The symbol @ represents excel format "text". In case of numeric/date fields we have to do it because the file will be downloaded in different regions and the users expect it to be in their number/date representation. The download code and variable mv_number_format / mv_date_format is set as below:

LOOP at it_data into wa_data.
lv_row_index = st-tabix.
at first.
 LOOP AT it_fields INTO ls_fcat.
      lv_column_index = sy-tabix.
        CALL METHOD OF lo_sheet  'Cells'  = lo_cells EXPORTING #1  = 1  #2  = lv_column_index.
        SET PROPERTY OF lo_cells 'Value' = ls_fcat-scrtext_m .
        CALL METHOD OF lo_application 'Columns'      = lo_column  EXPORTING  #1   = lv_column_index.
        IF ls_fcat-inttype = 'C'.     "Character
        SET PROPERTY OF lo_column 'NumberFormat' = '@'.
       ELSEIF ls_fcat-inttype = 'N'. "Numeric
        SET PROPERTY OF lo_column 'NumberFormat' = '@'.
      ELSEIF ls_fcat-inttype = 'P'.  "Packed
        SET PROPERTY OF lo_column 'NumberFormat' = mv_number_format.
       ELSEIF ls_fcat-inttype = 'D'.  "Date
        SET PROPERTY OF lo_column 'NumberFormat' = mv_date_format.
       ELSEIF ls_fcat-inttype = 'I'.  "Integer
        SET PROPERTY OF lo_column 'NumberFormat' = '0'.
       ELSE.   "Set it as "Text" Format
        SET PROPERTY OF lo_column 'NumberFormat' = '@'.
      ENDIF.
      GET PROPERTY OF lo_cells 'Interior' = lo_color.
      SET PROPERTY OF lo_color 'ColorIndex' = 6. "Color Yellow
    ENDLOOP.
endat.
" here move your data record
DO.
      lv_column_index = sy-index.
      assign component lv_column_index of structure wa_data to <fs>.
      if not <fs> is assigned.
         exit.
      endif.
       CALL METHOD OF lo_sheet  'Cells'  = lo_cells EXPORTING #1  = lv_rowindex  #2  = lv_column_index.
       SET PROPERTY OF lo_cells 'Value' = <fs>. 
 ENDDO.
ENDLOOP.

  me->set_date_format( ).
  me->set_number_format( ).

 DATA: lv_datfm TYPE xudatfm,
        lv_format TYPE char10,
        lv_delimeter TYPE char01,
        lt_form TYPE TABLE OF string,
        ls_form TYPE string.
  CHECK mv_dmy IS INITIAL
        AND mv_date_format IS INITIAL.
  lv_datfm = cl_abap_datfm=>get_datfm( ).
 TRY.
      CALL METHOD cl_abap_datfm=>get_date_format_des
        EXPORTING
          im_datfm      = lv_datfm
          im_langu      = sy-langu
          im_plain      = abap_false
          im_long       = abap_false
        IMPORTING
          ex_dateformat = lv_format.
    CATCH cx_abap_datfm_format_unknown .
      RETURN.
  ENDTRY.
 TRY.
      CALL METHOD cl_abap_datfm=>get_delimiter
        EXPORTING
          im_datfm     = lv_datfm
        IMPORTING
          ex_delimiter = lv_delimeter.
    CATCH cx_abap_datfm_format_unknown .
      RETURN.
  ENDTRY.
 IF lv_format IS NOT INITIAL.
    SPLIT lv_format AT lv_delimeter INTO TABLE lt_form.
   LOOP AT lt_form INTO ls_form.
      IF ls_form(1) = 'Y'.
        CONCATENATE mv_dmy 'J' INTO mv_dmy.
      ELSEIF ls_form(1) = 'M'.
        CONCATENATE mv_dmy 'M' INTO mv_dmy.
      ELSEIF ls_form(1) = 'D'.
        CONCATENATE mv_dmy 'T' INTO mv_dmy.
      ENDIF.
    ENDLOOP.
  ENDIF.
  IF mv_dmy = 'TMJ'.
    mv_date_format = 'DD.MM.YYYY;@'.
  ELSEIF mv_dmy = 'MTJ'.
    mv_date_format = 'MM/DD/YYYY;@'.
  ELSEIF mv_dmy = 'JMT'.
    mv_date_format = 'YYYY/MM/DD;@'.
  ENDIF.

 DATA:ls_usr01 TYPE usr01.
  CHECK mv_thousand_seperator IS INITIAL AND
        mv_decimal_seperator IS INITIAL AND
        mv_number_format IS INITIAL.
  SELECT SINGLE * FROM usr01 INTO ls_usr01 WHERE bname = sy-uname.
 IF ls_usr01-dcpfm = ' '.
    mv_thousand_seperator = '.'.
    mv_decimal_seperator = ','.
    mv_number_format = '#.##0,00'.
  ELSEIF ls_usr01-dcpfm = 'X'.
    mv_thousand_seperator = ','.
    mv_decimal_seperator = '.'.
    mv_number_format = '#,##0.00'.
  ELSEIF ls_usr01-dcpfm = 'Y'.
    mv_thousand_seperator = ' '.
    mv_decimal_seperator = ','.
    mv_number_format = '# ##0,00'.
  ENDIF.