a question on output number type data in XML

[Former Member]

Hi all,

I'm doing a Z program to generate a multiple sheet XML file.

Following is part of the testing code. i'm facing 2 issue here.

1. with concatenate syntax, i have to convert the number(quantity,amount) to string which end up i can't do caculation when

open in excel.

2. if you notice i'm using some excel formula below (c_luggweight) to assign negative sign in front when open in excel. This

was working fine if i write the file as xls file. But in xml file, it would appear as a whole string instead doing the formula

convertion.

Appreciate any suggestion and advice to resolve this. Thank you.

*value
  LOOP AT it_sbook[] INTO wa_sbook.

    APPEND ' <Row>' TO it_xml.

    APPEND '     <Cell>' TO it_xml.
    CONCATENATE '<Data ss:Type="String">' wa_sbook-carrid '</Data>' INTO wa_xml.
    APPEND wa_xml TO it_xml.
    APPEND '     </Cell>' TO it_xml.
    APPEND '     <Cell>' TO it_xml.
    CONCATENATE '<Data ss:Type="String">' wa_sbook-connid '</Data>' INTO wa_xml.
    APPEND wa_xml TO it_xml.
    APPEND '     </Cell>' TO it_xml.
    APPEND '     <Cell>' TO it_xml.

*from YYYYMMDD to MM/DD/YYYY
    CALL FUNCTION 'CONVERT_DATE_TO_EXTERNAL'
      EXPORTING
        date_internal            = wa_sbook-fldate
      IMPORTING
        date_external            = v_date
      EXCEPTIONS
        date_internal_is_invalid = 1
        OTHERS                   = 2.
    IF sy-subrc <> 0.
      MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
              WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
    ENDIF.

    CONCATENATE '<Data ss:Type="String">' v_date '</Data>' INTO wa_xml.
    APPEND wa_xml TO it_xml.
    APPEND '     </Cell>' TO it_xml.
    APPEND '     <Cell>' TO it_xml.
    CONCATENATE '<Data ss:Type="String">' wa_sbook-bookid '</Data>' INTO wa_xml.
    APPEND wa_xml TO it_xml.
    APPEND '     </Cell>' TO it_xml.
    APPEND '     <Cell>' TO it_xml.
    CONCATENATE '<Data ss:Type="String">' wa_sbook-customid '</Data>' INTO wa_xml.
    APPEND wa_xml TO it_xml.
    APPEND '     </Cell>' TO it_xml.
    APPEND '     <Cell>' TO it_xml.
    c_luggweight = wa_sbook-luggweight.
    CONDENSE c_luggweight.
*    CONCATENATE '<Data ss:Type="Number">' c_luggweight '</Data>' INTO wa_xml.
*this is to have negative sign in front of the number in excel
    CONCATENATE '<Data ss:Type="String">'
                '=IF(RIGHT("'
                c_luggweight
                '",1)="-",VALUE("-"&LEFT("'
                c_luggweight
                '",LEN("'
                c_luggweight
                '")-1)),VALUE("'
                c_luggweight
                '"))' '</Data>'
                htab
    INTO wa_xml.
*    SEPARATED BY htab.
    APPEND wa_xml TO it_xml.
    APPEND '     </Cell>' TO it_xml.
    APPEND '     <Cell>' TO it_xml.
    c_forcuram = wa_sbook-forcuram.
    CONDENSE c_forcuram.
    CONCATENATE '<Data ss:Type="String">' c_forcuram '</Data>' INTO wa_xml.
    APPEND wa_xml TO it_xml.
    APPEND '     </Cell>' TO it_xml.
    APPEND ' </Row>' TO it_xml.

  ENDLOOP.

[Former Member]

problem resolved by myself