使用XML导出到本地EXCEL

小疯子

众所周知,传统的abap把内表的数据导出到本地excel,很多人一下就想到gui_download,当用户要求输出的excel很花俏怎么办,这时ole和doi就派上用场了,其实doi的底层也是ole,这两种方法都可以调用excel宏,在指定的单元格或者区域填写指定的数据,更可以预先自定义模板上传至服务器,下载时填写数据到指定的模板上,然后调用excel下载到本地.本次就不再过多的说gui_download,ole和doi了,如果有需要的话,可以跟帖,我会发一个相关的模板供大家学习. 本次着重介绍使用XML导出到本地excel,
在这个需求各种变的时候,作为一名abaper,必须要自保,想尽办法把自己的代码写活,不变应万变,
大致思路,把需要的excel模板准备好,往模板里造一条假数据,然后保存为xml格式,然后以文本格式打开,编辑xml里个别参数为可动态替换的字符(&A&),把这个xml上传到服务器上,当需要下载数据到本地excel模板的时候,可以采用open dataset读取服务器上的文件(text mode),替换字符&&为要输出的参数,下载至本地即可. ---这种方法相对于ole和doi来说,略微死板,因为他没法控制到具体的某个单元格,这里只是为了拓展大家的知识.

由于视频比较大,上传权限不够,需要的请留下邮箱

以下是代码---

FUNCTION ZGRC_EXPORT_XML2222.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"----------------------------------------------------------------------
  DATA replace2 TYPE string.
  DATA l_xml TYPE string.
  DATA l_string TYPE string.
  DATA w_phgical_path TYPE string.
  DATA:l_string1 TYPE string.
  DATA:l_string2 TYPE string.
  DATA:l_string3 TYPE string.
  DATA lt_string TYPE TABLE OF string.
  DATA ls_string TYPE string.
  FIELD-SYMBOLS <fs> TYPE any.
  FIELD-SYMBOLS <field> TYPE any.
  DATA l_line_itab TYPE i.
  DATA l_line_itab_string TYPE string.
  DATA colline TYPE i VALUE 1.
  DATA colline_t TYPE string.
  DATA colname TYPE string.
  DATA l_tabix TYPE sy-tabix.
  DATA l_replace TYPE string.
  DATA l_line TYPE i.
  DATA l_l_colline_string TYPE string.
  DATA l_colline TYPE i VALUE 1.
  DATA lt_string_out TYPE TABLE OF string.
  DATA ls_string_out TYPE string.
  DATA lt_field TYPE TABLE OF string.
  DATA ls_field TYPE string.
  DATA l_line_field TYPE i.
  DATA l_field_tabix TYPE sy-tabix.

  SPLIT out_string AT '#' INTO TABLE lt_string_out."所有的行 行内逗号区分



  READ TABLE lt_string_out INTO ls_string_out INDEX 1.
  IF sy-subrc = 0.
    SPLIT ls_string_out AT ',' INTO TABLE lt_field.
    DESCRIBE TABLE lt_field LINES l_line_field.
  ENDIF.


  FIELD-SYMBOLS:<dyn_table> TYPE STANDARD TABLE,
                   <dyn_wa> TYPE any,
                   <dyn_field> TYPE any.
  DATA: dy_table TYPE REF TO data,
        dy_line  TYPE REF TO data,
        it_structure TYPE lvc_t_fcat,
        wa_structure TYPE lvc_s_fcat.
  DO l_line_field TIMES.
    colline_t = colline.
    CONCATENATE 'COL' colline_t INTO colline_t.
    wa_structure-fieldname = colline_t.
    APPEND wa_structure TO it_structure.
    CLEAR wa_structure.
    colline = colline + 1.
    CLEAR colline_t.
  ENDDO.

  CALL METHOD cl_alv_table_create=>create_dynamic_table
    EXPORTING
      it_fieldcatalog = it_structure
    IMPORTING
      ep_table        = dy_table.
  ASSIGN dy_table->* TO <dyn_table>.
  DATA:wa_new_line TYPE REF TO data.
  CREATE DATA wa_new_line LIKE LINE OF <dyn_table>.
  ASSIGN wa_new_line->* TO <dyn_wa>.


  FIELD-SYMBOLS <dyn_wa_field> TYPE any.
  LOOP AT lt_string_out INTO ls_string_out.
    REFRESH lt_field.
    SPLIT ls_string_out AT ',' INTO TABLE lt_field.
    LOOP AT lt_field INTO ls_field.
      ASSIGN COMPONENT sy-tabix OF STRUCTURE <dyn_wa> TO <dyn_wa_field>.
      <dyn_wa_field> = ls_field.
    ENDLOOP.
    APPEND <dyn_wa> TO <dyn_table>.
    CLEAR ls_string_out.
    REFRESH lt_field.
    FREE:<dyn_wa>,<dyn_wa_field>.
  ENDLOOP.



***************************************从服务器上读取xml数据
  DATA l_path TYPE string.
  DATA log_path1 TYPE string ."VALUE '/usr/sap/GA0/SYS/global/'.
  "获取逻辑路径
  SELECT SINGLE config_remarks INTO log_path1 FROM zgrc_config_main
    WHERE config_id = 'TMP'.
  w_phgical_path = log_path1."'/USR/SAP/GA0/SYS/GLOBAL/'.
  CONCATENATE w_phgical_path tmp INTO w_phgical_path.

  OPEN DATASET w_phgical_path FOR INPUT IN TEXT MODE ENCODING DEFAULT.
  IF sy-subrc <> 0.

  ENDIF.

  DO.
    READ DATASET w_phgical_path INTO l_string.
    IF sy-subrc = 0.
      CONCATENATE l_xml l_string INTO l_xml.

    ELSE.
      EXIT.
    ENDIF.
  ENDDO.
  CLOSE DATASET w_phgical_path.



  SPLIT l_xml AT '</Table>' INTO l_string1 l_string2."根据实际的xml分析 拆分xml 以便追加数据
  SPLIT l_string1 AT    '<Row ss:AutoFitHeight="0">' INTO  TABLE lt_string.


  DESCRIBE TABLE <dyn_table> LINES l_line_itab.
  l_line_itab = l_line_itab + 10.
  l_line_itab_string = l_line_itab.
  CONDENSE l_line_itab_string.
  REPLACE '&ALLROWS&' WITH l_line_itab_string INTO l_string1."替换掉我之前做的那个&ALLROWS&


  DESCRIBE TABLE lt_string LINES l_line.
  READ TABLE lt_string INTO ls_string INDEX l_line.
  IF sy-subrc = 0.

    LOOP AT <dyn_table> ASSIGNING <fs>.
      IF sy-tabix = 1.
        DO.
          ASSIGN COMPONENT l_colline  OF STRUCTURE <fs> TO <field>.
          IF sy-subrc <> 0.
            EXIT.
          ELSE.
*            IF <field> IS INITIAL.
*              <field> = 'null'.
*            ENDIF.
            l_l_colline_string = l_colline.
            CONDENSE l_l_colline_string.
            CONCATENATE '&' l_l_colline_string '&' INTO l_replace."替换掉&1&等数据
            replace2 = <field>.
            CONDENSE replace2.
            REPLACE l_replace WITH replace2 INTO l_string1.
            l_colline = l_colline + 1.
          ENDIF.
        ENDDO.
      ELSE.
        CONCATENATE l_string1  '<Row ss:AutoFitHeight="0">' ls_string INTO l_string1.
        DO.
          ASSIGN COMPONENT l_colline  OF STRUCTURE <fs> TO <field>.

          IF sy-subrc <> 0.
            EXIT.
          ELSE.
*            IF <field> IS INITIAL.
*              <field> = 'null'.
*            ENDIF.
            l_l_colline_string = l_colline.
            CONDENSE l_l_colline_string.
            CONCATENATE '&' l_l_colline_string '&' INTO l_replace.
            replace2 = <field>.
            CONDENSE replace2.
            REPLACE l_replace WITH replace2 INTO l_string1.
            l_colline = l_colline + 1.
          ENDIF.
        ENDDO.
      ENDIF.

      CLEAR: l_l_colline_string,l_replace,replace2.
      l_colline = 1.
    ENDLOOP.

  ENDIF.

  CONCATENATE l_string1 '</Table>' l_string2 INTO new_xml.


ENDFUNCTION.