01-26-2011 2:29 AM
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.
01-27-2011 1:54 AM