ABAP:excel导出,不会乱码,速度比OLE快

FORM EXCEL_EXPORT USING TITLE
                        EXPORT_TAB TYPE TABLE
                        FILEPATH.
*  BREAK FITDEBUG.
  DATA:P_FILEPATH TYPE STRING.
  P_FILEPATH = FILEPATH.
  TYPES: BEGIN OF XML_LINE,
        DATA(256) TYPE X,
       END OF XML_LINE.
  DATA: L_IXML            TYPE REF TO IF_IXML,
  L_STREAMFACTORY   TYPE REF TO IF_IXML_STREAM_FACTORY,
  L_OSTREAM         TYPE REF TO IF_IXML_OSTREAM,
  L_RENDERER        TYPE REF TO IF_IXML_RENDERER,
  L_DOCUMENT        TYPE REF TO IF_IXML_DOCUMENT.
  DATA:
  L_ELEMENT_ROOT    TYPE REF TO IF_IXML_ELEMENT,
  NS_ATTRIBUTE      TYPE REF TO IF_IXML_ATTRIBUTE,
  R_ELEMENT_PROPERTIES  TYPE REF TO IF_IXML_ELEMENT,
  R_ELEMENT         TYPE REF TO IF_IXML_ELEMENT,
  R_WORKSHEET       TYPE REF TO IF_IXML_ELEMENT,
  R_TABLE           TYPE REF TO IF_IXML_ELEMENT,
  R_COLUMN          TYPE REF TO IF_IXML_ELEMENT,
  R_ROW             TYPE REF TO IF_IXML_ELEMENT,
  R_STYLES          TYPE REF TO IF_IXML_ELEMENT,
  R_STYLE           TYPE REF TO IF_IXML_ELEMENT,
  R_FORMAT          TYPE REF TO IF_IXML_ELEMENT,
  R_CELL            TYPE REF TO IF_IXML_ELEMENT,
  R_DATA            TYPE REF TO IF_IXML_ELEMENT,
  L_XML_TABLE       TYPE TABLE OF XML_LINE,
  L_XML_SIZE        TYPE I,
  L_RC              TYPE I,
  L_TEXT(255)       TYPE C,
  L_VALUE           TYPE STRING,
  L_TYPE            TYPE STRING,
  P_TYPE           .
  FIELD-SYMBOLS:
*  <DATA_TAB>       TYPE STANDARD TABLE,
  <DATA_LINE>      TYPE ANY,
  <FIELD>          TYPE ANY.

*  ASSIGN EXPORT_TAB TO <DATA_TAB>.
* Creating a ixml factory
  L_IXML = CL_IXML=>CREATE( ).
* Creating the dom object model
  L_DOCUMENT = L_IXML->CREATE_DOCUMENT( ).
* Create root node 'Workbook'
  L_ELEMENT_ROOT  = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
              NAME = 'Workbook'
*              uri  = 'urn:schemas-microsoft-com:office:spreadsheet'
              PARENT = L_DOCUMENT ).
  L_ELEMENT_ROOT->SET_ATTRIBUTE(
                    NAME = 'xmlns'
                    VALUE = 'urn:schemas-microsoft-com:office:spreadsheet' ).
  NS_ATTRIBUTE = L_DOCUMENT->CREATE_NAMESPACE_DECL(
                    NAME = 'ss'
                    PREFIX = 'xmlns'
                    URI = 'urn:schemas-microsoft-com:office:spreadsheet' ).
  L_ELEMENT_ROOT->SET_ATTRIBUTE_NODE( NS_ATTRIBUTE ).
  NS_ATTRIBUTE = L_DOCUMENT->CREATE_NAMESPACE_DECL(
                             NAME = 'x'
                             PREFIX = 'xmlns'
                             URI = 'urn:schemas-microsoft-com:office:excel' ).
  L_ELEMENT_ROOT->SET_ATTRIBUTE_NODE( NS_ATTRIBUTE ).
  R_STYLES = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
                        NAME = 'Styles'
                        PARENT = L_ELEMENT_ROOT
                        ).
  R_STYLE = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
                        NAME = 'Style'
                        PARENT = R_STYLES
                        ).
  R_STYLE->SET_ATTRIBUTE_NS(
                         NAME = 'ID'
                         PREFIX = 'ss'
                         VALUE = 's62'
                         ).
  R_FORMAT = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
                          NAME = 'NumberFormat'
                          PARENT = R_STYLE
                          ).
  R_FORMAT->SET_ATTRIBUTE_NS(
                          NAME = 'Format'
                          PREFIX = 'ss'
                          VALUE = '0%'
                          ).
  R_STYLE = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
NAME = 'Style'
PARENT = R_STYLES
).
  R_STYLE->SET_ATTRIBUTE_NS(
                         NAME = 'ID'
                         PREFIX = 'ss'
                         VALUE = 's63'
                         ).
  R_FORMAT = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
                          NAME = 'NumberFormat'
                          PARENT = R_STYLE
                          ).
  R_FORMAT->SET_ATTRIBUTE_NS(
                          NAME = 'Format'
                          PREFIX = 'ss'
                          VALUE = '0.00%'
                          ).

* Create node for document properties.
*  R_ELEMENT_PROPERTIES = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
*              NAME = 'DocumentProperties'
*              PARENT = L_ELEMENT_ROOT ).
*  L_VALUE = SY-UNAME.
*  L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
*                    NAME = 'Author'
*                    VALUE = L_VALUE
*                    PARENT = R_ELEMENT_PROPERTIES  ).
*  R_STYLES = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
*                    NAME = 'Styles'
*                    PARENT = L_ELEMENT_ROOT  ).
** Header row - Yellow, Bold
*  R_STYLE  = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
*                    NAME = 'Style'
*                    PARENT = R_STYLES  ).
*  R_STYLE->SET_ATTRIBUTE_NS(
*                    NAME = 'ID'
*                    PREFIX = 'ss'
*                    VALUE = 'Header' ).

*  R_FORMAT  = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
*                    NAME = 'Font'
*                    PARENT = R_STYLE  ).
*  R_FORMAT->SET_ATTRIBUTE_NS(
*                    NAME = 'Bold'
*                    PREFIX = 'ss'
*                    VALUE = '1' ).
*  R_FORMAT  = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
*                    NAME = 'Interior'
*                    PARENT = R_STYLE  ).
*  R_FORMAT->SET_ATTRIBUTE_NS(
*                    NAME = 'Color'
*                    PREFIX = 'ss'
*                    VALUE = '#FFFF00' ).
*  R_FORMAT->SET_ATTRIBUTE_NS(
*                    NAME = 'Pattern'
*                    PREFIX = 'ss'
*                    VALUE = 'Solid' ).
*  R_FORMAT  = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
*                    NAME = 'Alignment'
*                    PARENT = R_STYLE  ).
*  R_FORMAT->SET_ATTRIBUTE_NS(
*                    NAME = 'Vertical'
*                    PREFIX = 'ss'
*                    VALUE = 'Bottom' ).
*  R_FORMAT->SET_ATTRIBUTE_NS(
*                    NAME = 'WrapText'
*                    PREFIX = 'ss'
*                    VALUE = '1' ).
*  LOOP AT DD03P_TAB INTO DD03P WHERE FIELDNAME <> 'MANDT'.
*    CASE DD03P-INTTYPE.
*      WHEN 'I' OR 'N'.
**       General format
*      WHEN 'P' OR 'F'.
**       Numeric with specific number of decimals
*        R_STYLE  = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
*                          NAME = 'Style'
*                          PARENT = R_STYLES  ).
*        L_VALUE = DD03P-FIELDNAME.
*        R_STYLE->SET_ATTRIBUTE_NS(
*                          NAME = 'ID'
*                          PREFIX = 'ss'
*                          VALUE = L_VALUE ).
*        R_FORMAT  = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
*                          NAME = 'NumberFormat'
*                          PARENT = R_STYLE  ).
*        IF DD03P-DECIMALS > 0.
*          L_VALUE = '0.'.
*          DO DD03P-DECIMALS TIMES.
*            CONCATENATE L_VALUE '0' INTO L_VALUE.
*          ENDDO.
*        ELSE.
*          L_VALUE = ''.
*        ENDIF.
*        R_FORMAT->SET_ATTRIBUTE_NS(
*                          NAME = 'Format'
*                          PREFIX = 'ss'
*                          VALUE = L_VALUE ).
*      WHEN OTHERS.
**       Fixed text
*        R_STYLE  = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
*                          NAME = 'Style'
*                          PARENT = R_STYLES  ).
*        L_VALUE = DD03P-FIELDNAME.
*        R_STYLE->SET_ATTRIBUTE_NS(
*                          NAME = 'ID'
*                          PREFIX = 'ss'
*                          VALUE = L_VALUE ).
*        R_FORMAT  = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
*                          NAME = 'NumberFormat'
*                          PARENT = R_STYLE  ).
*        L_VALUE = '@'.
*        R_FORMAT->SET_ATTRIBUTE_NS(
*                          NAME = 'Format'
*                          PREFIX = 'ss'
*                          VALUE = L_VALUE ).
*    ENDCASE.
*  ENDLOOP.
*  Worksheet
*  <Worksheet ss:Name="Sheet1">
  R_WORKSHEET = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
              NAME = 'Worksheet '
              PARENT = L_ELEMENT_ROOT ).
  R_WORKSHEET->SET_ATTRIBUTE_NS(
NAME = 'Name'
PREFIX = 'ss'
VALUE = 'Sheet1' ).
  R_TABLE = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
              NAME = 'Table'
              PARENT = R_WORKSHEET ).
*  DESCRIBE TABLE DD03P_TAB LINES NUM_ROWS.
  R_TABLE->SET_ATTRIBUTE_NS(
                      NAME = 'FullColumns'
                      PREFIX = 'x'
                      VALUE = '1' ).
  R_TABLE->SET_ATTRIBUTE_NS(
                    NAME = 'FullRows'
                    PREFIX = 'x'
                    VALUE = '1' ).
** Column formatting
*  LOOP AT DD03P_TAB INTO DD03P WHERE FIELDNAME <> 'MANDT'.
**   Columnn
**   <Column>
*    R_COLUMN = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
*              NAME = 'Column'
*              PARENT = R_TABLE ).
*    CASE DD03P-INTTYPE.
*      WHEN 'I' OR 'N'.
**       General format
*      WHEN 'P' OR 'F'.
*        L_VALUE = DD03P-FIELDNAME.
*        R_COLUMN->SET_ATTRIBUTE_NS(
*                          NAME = 'StyleID'
*                          PREFIX = 'ss'
*                          VALUE = L_VALUE ).
*    ENDCASE.
*    L_VALUE = ( DD03P-OUTPUTLEN + 5 ) * 5.
*    CONDENSE L_VALUE NO-GAPS.
*    R_COLUMN->SET_ATTRIBUTE_NS(
*                      NAME = 'Width'
*                      PREFIX = 'ss'
*                      VALUE = L_VALUE ).
*  ENDLOOP.
* Column headers
*   Row
*   <Row>
*  R_ROW = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
*            NAME = 'Row'
*            PARENT = R_TABLE ).
*  R_ROW->SET_ATTRIBUTE_NS(
*                    NAME = 'StyleID'
*                    PREFIX = 'ss'
*                    VALUE = 'Header' ).
*  R_ROW->SET_ATTRIBUTE_NS(
*                    NAME = 'AutoFitHeight'
*                    PREFIX = 'ss'
*                    VALUE = '1' ).
*  R_CELL = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
*            NAME = 'Cell'
*            PARENT = R_ROW ).
*  L_VALUE = DD03P-DDTEXT.    "fieldname, scrtext_m etc.
*  R_DATA = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
*            NAME = 'Data'
*            VALUE = L_VALUE
*            PARENT = R_CELL ).
*  R_DATA->SET_ATTRIBUTE_NS(
*                    NAME = 'Type'
*                    PREFIX = 'ss'
*                    VALUE = 'String' ).
*    endloop.
* Finally loop at the data table and output the fields
***Header
  R_ROW = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
               NAME = 'Row'
               PARENT = R_TABLE ).

  DO.
*      BREAK FITDEBUG.
    ASSIGN COMPONENT SY-INDEX OF STRUCTURE TITLE TO <FIELD>.
    IF NOT SY-SUBRC IS INITIAL.
      EXIT.
    ENDIF.
    DESCRIBE FIELD <FIELD> TYPE P_TYPE.
    TRANSLATE P_TYPE TO UPPER CASE.
*      CHECK SY-SUBRC IS INITIAL.
*     <Cell>
    R_CELL = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
              NAME = 'Cell'
              PARENT = R_ROW ).
    CASE P_TYPE.
      WHEN 'I' OR 'P' OR 'F' OR 'N'.
        L_TYPE = 'Number'.
        L_VALUE = <FIELD>.
        CONDENSE L_VALUE NO-GAPS.
      WHEN 'D' OR 'T'.
        L_TYPE = 'String'.
        WRITE <FIELD> TO L_TEXT.
        L_VALUE = L_TEXT.
      WHEN OTHERS.
*          l_value = <field>.    "Without conversion exit
        WRITE <FIELD> TO L_TEXT.
        SHIFT L_TEXT LEFT DELETING LEADING SPACE.
        L_VALUE = L_TEXT.
        L_TYPE = 'String'.
    ENDCASE.
*     <Data>
    R_DATA = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
              NAME = 'Data'
              VALUE = L_VALUE
              PARENT = R_CELL ).
*     Cell format
    R_DATA->SET_ATTRIBUTE_NS(
                      NAME = 'Type'
                      PREFIX = 'ss'
                      VALUE = L_TYPE ).
  ENDDO.

 

  LOOP AT EXPORT_TAB ASSIGNING <DATA_LINE>.
*   Row
*   <Row>
    R_ROW = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
              NAME = 'Row'
              PARENT = R_TABLE ).
*    LOOP AT DD03P_TAB INTO DD03P WHERE FIELDNAME <> 'MANDT'.
    DO.
*      BREAK FITDEBUG.
      ASSIGN COMPONENT SY-INDEX OF STRUCTURE <DATA_LINE> TO <FIELD>.
      IF NOT SY-SUBRC IS INITIAL.
        EXIT.
      ENDIF.
      DESCRIBE FIELD <FIELD> TYPE P_TYPE.
      TRANSLATE P_TYPE TO UPPER CASE.
*      CHECK SY-SUBRC IS INITIAL.
*     <Cell>
      R_CELL = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
                NAME = 'Cell'
                PARENT = R_ROW ).
      CASE P_TYPE.
        WHEN 'I' OR 'P' OR 'F' OR 'N'.
          L_TYPE = 'Number'.
          L_VALUE = <FIELD>.
          CONDENSE L_VALUE NO-GAPS.
        WHEN 'D' OR 'T'.
          L_TYPE = 'String'.
          MOVE <FIELD> TO L_TEXT.
          L_VALUE = L_TEXT.
        WHEN OTHERS.
          MOVE <FIELD> TO L_TEXT.
          L_VALUE = L_TEXT.
          L_TYPE = 'String'.

**含有%的不当做字符串来处理
*         BREAK FITDEBUG.
          IF L_VALUE CS '%'.
            SHIFT L_VALUE RIGHT DELETING TRAILING '%'.
            IF SY-SUBRC EQ 0.
              TRY.
                  DATA P_VALUE TYPE P DECIMALS 2.
                  FIND '.' IN L_VALUE.
                  IF SY-SUBRC EQ 0.
                    L_TYPE = 's63'.
                  ELSE.
                    L_TYPE = 's62'.
                  ENDIF.
                  P_VALUE = L_VALUE / 100.
                  L_VALUE = P_VALUE.
                  CONDENSE L_VALUE NO-GAPS.
                  R_CELL->SET_ATTRIBUTE_NS(
                           NAME = 'StyleID'
                           PREFIX = 'ss'
                           VALUE = L_TYPE
                           ).
                CATCH CX_SY_CONVERSION_NO_NUMBER.
                  L_TYPE = 'String'.
                  CONCATENATE L_VALUE '%' INTO L_VALUE.
                CLEANUP.
              ENDTRY.
              L_TYPE = 'Number'.
            ENDIF.

          ELSE.
            L_TYPE = 'String'.
          ENDIF.
      ENDCASE.
*     <Data>
      R_DATA = L_DOCUMENT->CREATE_SIMPLE_ELEMENT(
                NAME = 'Data'
                VALUE = L_VALUE
                PARENT = R_CELL ).
*     Cell format
      R_DATA->SET_ATTRIBUTE_NS(
                        NAME = 'Type'
                        PREFIX = 'ss'
                        VALUE = L_TYPE ).
    ENDDO.
*    ENDLOOP.
  ENDLOOP.
*   Creating a stream factory
  L_STREAMFACTORY = L_IXML->CREATE_STREAM_FACTORY( ).
*   Connect internal XML table to stream factory
  L_OSTREAM = L_STREAMFACTORY->CREATE_OSTREAM_ITABLE( TABLE = L_XML_TABLE ).
*   Rendering the document
  L_RENDERER = L_IXML->CREATE_RENDERER( OSTREAM  = L_OSTREAM
                                        DOCUMENT = L_DOCUMENT ).
  L_RC = L_RENDERER->RENDER( ).
*   Saving the XML document
  L_XML_SIZE = L_OSTREAM->GET_NUM_WRITTEN_RAW( ).
  CALL METHOD CL_GUI_FRONTEND_SERVICES=>GUI_DOWNLOAD
    EXPORTING
      BIN_FILESIZE = L_XML_SIZE
      FILENAME     = P_FILEPATH
      FILETYPE     = 'BIN'
    CHANGING
      DATA_TAB     = L_XML_TABLE
    EXCEPTIONS
      OTHERS       = 24.
  IF SY-SUBRC <> 0.
    MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
               WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
  ENDIF.
ENDFORM.                    "EXCEL_EXPORT