【ABAP】 运用OLE 生成多SHEET页的excel文档

 实现思路为 SMWO导入模板-确定存储目录-初始化OLE-导入excel-关闭OLE

FUNCTION zsd_exec_print001 .
*"----------------------------------------------------------------------
*"*"局部接口：
*"  IMPORTING
*"     REFERENCE(I_INT) TYPE  ANY
*"     REFERENCE(I_TYP)
*"  EXPORTING
*"     VALUE(O_TYP) TYPE  BAPI_MTYPE
*"     VALUE(O_MSG) TYPE  BAPI_MSG
*"----------------------------------------------------------------------
  CLEAR: gs_print, gv_typ.
  IF i_int IS INITIAL OR i_typ IS INITIAL .
    o_typ = 'E'.
    o_msg = text-t02.
  ELSE.
    o_typ = 'S'.
    o_msg = text-t01.
    MOVE-CORRESPONDING i_int TO gs_print.
    WRITE i_typ TO gv_typ.
    PERFORM frm_build_data.
  ENDIF.

ENDFUNCTION.

*&---------------------------------------------------------------------*
*&      Form  FRM_BUILD_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_I_INT  text
*----------------------------------------------------------------------*
FORM frm_build_data .

  DATA: c_objid_header TYPE w3objid VALUE . 

* EXCEL模板 .  
  c_objid_header = 'ZDMX_ENCHASE_EXPORT'.

* 文件名称 .
  gv_filename = 'D:\' &&  'Dicastal Mexico, S. DE R.L. DE C.V' 
                      && '_' && sy-datum && '-' && sy-uzeit .   


  CHECK gv_filename IS NOT INITIAL."

*  获取下载文件路径
  PERFORM f_download_path USING gv_filename .

* 下载模板
  PERFORM f_download_excel_fromserver USING c_objid_header  "smw0的文件名"
                                            gv_filename.    "从服务器中下载EXCEL模板到L_FILENAME中

* 初始化OLE  F_OPEN_EXCEL_VISIBLE   F_OPEN_EXCEL_hide
  PERFORM f_open_excel_visible USING gv_filename CHANGING g_excel g_wbook sy-subrc.

* 初始化sheet1 
  PERFORM frm_build_sheet1 CHANGING g_excel g_wbook g_sheet.

* 初始化sheet2 
  PERFORM frm_build_sheet2 CHANGING g_excel g_wbook g_sheet. 

* 关闭退出OLE
  PERFORM f_close_file CHANGING g_excel sy-subrc.

ENDFORM.                    "frm_build_data"

*&---------------------------------------------------------------------*
*&      Form  FRM_DOWNLOAD_EXCEL_FROMSERVER
*&---------------------------------------------------------------------*
*       从服务器下载模板
*----------------------------------------------------------------------*
*    -->PL_OBJID:模板名称
*    -->PL_DEST:模板的保存路径
*----------------------------------------------------------------------*
FORM f_download_excel_fromserver USING pl_objid TYPE wwwdatatab-objid
                                       pl_dest  TYPE rlgrap-filename.

  DATA: l_objdata LIKE wwwdatatab,
        l_mime    LIKE w3mime,
        l_objnam  TYPE string,
        l_rc      LIKE sy-subrc,
        l_errtxt  TYPE string.

  CONCATENATE pl_objid '.XLS' INTO l_objnam.
  CONDENSE l_objnam NO-GAPS.

* 检查模板是否存在
  SELECT SINGLE relid objid FROM wwwdata
    INTO (l_objdata-relid, l_objdata-objid)
    WHERE srtf2    = 0
      AND relid    = 'MI'
      AND objid    = pl_objid.

* 模板不存在
  IF sy-subrc <> 0 OR l_objdata-objid = space.
    MESSAGE e002(zfico) WITH l_objnam.
  ENDIF.

* 下载模板到指定路径
  CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
    EXPORTING
      key         = l_objdata
      destination = pl_dest
    IMPORTING
      rc          = l_rc.

* 下载模板失败
  IF l_rc <> 0.
    MESSAGE e001(zfico) WITH l_objnam.
  ENDIF.

ENDFORM.                    "FRM_DOWNLOAD_EXCEL_FROMSERVER"

*&---------------------------------------------------------------------*
*&      Form  FRM_SELECTRANGE
*&---------------------------------------------------------------------*
*&      选择一个excel RANGE
*&---------------------------------------------------------------------*
*    -->PL_TOP   :起始单元格所在的行
*    -->PL_LEFT  :起始单元格所在的列
*    -->PL_RIGHT :结束单元格所在的行
*    -->PL_BUTTOM:结束单元格所在的行
*    -->PL_SHEET :当前操作的Sheet页
*    <--PL_RANGE :操作的范围
*----------------------------------------------------------------------*
FORM f_selectrange USING    pl_left   TYPE i
                            pl_top    TYPE i
                            pl_right  TYPE i
                            pl_buttom TYPE i
                            pl_sheet  TYPE ole2_object
                   CHANGING pl_range  TYPE ole2_object.

  DATA: l_cell_start TYPE ole2_object,
        l_cell_end   TYPE ole2_object.

* 起始单元格
  CALL METHOD OF
      pl_sheet
      'CELLS'  = l_cell_start
    EXPORTING
      #1       = pl_top
      #2       = pl_left.

* 结束单元格
  CALL METHOD OF
      pl_sheet
      'CELLS'  = l_cell_end
    EXPORTING
      #1       = pl_buttom
      #2       = pl_right.

* 选中的范围
  CALL METHOD OF
      pl_sheet
      'RANGE'  = pl_range
    EXPORTING
      #1       = l_cell_start
      #2       = l_cell_end.

  CALL METHOD OF
      pl_range
      'SELECT'.

ENDFORM.                    "FRM_SELECTRANGE"

*&---------------------------------------------------------------------*
*&      Form  FILL_CELL
*&---------------------------------------------------------------------*
*       向指定的单元格填充数据
*----------------------------------------------------------------------*
*    -->PL_ROW        :单元格所在的行
*    -->PL_COL        :单元格所在的列
*    -->PL_VALUE      :单元格的值
*    <--PL_APPLICATION:Excel进程
*----------------------------------------------------------------------*
FORM f_fill_cell USING    pl_row         TYPE i
                          pl_col         TYPE i
                          pl_value       TYPE c
                 CHANGING pl_application TYPE ole2_object.

  DATA l_cells TYPE ole2_object.

* 选中单元格
  CALL METHOD OF
      pl_application
      'Cells'        = l_cells
    EXPORTING
      #1             = pl_row
      #2             = pl_col.

* 设置值
  SET PROPERTY OF l_cells 'value' = pl_value.

ENDFORM.                    "FILL_CELL"

*&---------------------------------------------------------------------*
*&      Form  F_OPEN_EXCEL_VISIBLE
*&---------------------------------------------------------------------*
*&      初始化OLE控件并打开模版文件同时前台可见
*&---------------------------------------------------------------------*
*    -->PL_FILENAME   :要打开的Excel文件
*    <--PL_APPLICATION:Excel进程
*    <--PL_WORKBOOK   :工作簿
*    <--PL_SUBRC      : 执行结果返回值
*----------------------------------------------------------------------*
FORM f_open_excel_visible USING    pl_filename    TYPE rlgrap-filename
                          CHANGING pl_application TYPE ole2_object
                                   pl_workbook    TYPE ole2_object
                                   pl_subrc       TYPE sy-subrc.

* 创建Excel进程
  CREATE OBJECT pl_application 'Excel.Application'.

* 设置前台不可见
  SET PROPERTY OF pl_application 'Visible' = 0.

* 打开已有的Excel文件
  CALL METHOD OF
      pl_application
      'Workbooks'    = pl_workbook.
  CALL METHOD OF
      pl_workbook
      'Open'

    EXPORTING
      #1          = pl_filename.

* 打开Excel文件是否成功的返回值
  pl_subrc = sy-subrc.

* 如果打开成功继续执行后继代码，如果失败退出当前子程序
  CHECK pl_subrc = 0.

* 得到当前活动工作簿
  GET PROPERTY OF pl_application 'ACTIVEWORKBOOK' = pl_workbook.

ENDFORM.                    " F_OPEN_EXCEL_VISIBLE"

*&---------------------------------------------------------------------*
*&      Form  fRM_OPEN_SHEET
*&---------------------------------------------------------------------*
*&      打开某个Sheet页
*&---------------------------------------------------------------------*
*    -->PL_SHEET_NAME:要打开的Sheet页的名称
*    <--PL_APPLICATION:Excel进程
*    <--PL_WORKBOOK  :工作簿
*    <--PL_SHEET          :Sheet页
*----------------------------------------------------------------------*
FORM frm_open_sheet USING    pl_sheet_name  TYPE string
                  CHANGING pl_application TYPE ole2_object
                           pl_workbook    TYPE ole2_object
                           pl_sheet       TYPE ole2_object.

  CALL METHOD OF
      pl_application
      'SHEETS'       = pl_sheet
    EXPORTING
      #1             = pl_sheet_name.

  CALL METHOD OF
      pl_sheet
      'ACTIVATE'.

ENDFORM.                    "F_OPEN_SHEET"

*&---------------------------------------------------------------------*
*&      Form  f_close_file
*&---------------------------------------------------------------------*
*       保存EXCEL文件
*----------------------------------------------------------------------*
*    <--PL_APPLICATION:Excel进程
*    <--PL_SUBRC      :操作返回值
*----------------------------------------------------------------------*
FORM f_close_file CHANGING pl_application TYPE ole2_object
                           pl_subrc       TYPE sy-subrc.

  DATA l_workbook  TYPE ole2_object.

* 得到当前活动的工作簿
  GET PROPERTY OF pl_application 'ACTIVEWORKBOOK' = l_workbook.

* 保存工作簿
  CALL METHOD OF
      l_workbook
      'SAVE'.

* 关闭工作簿
  CALL METHOD OF
      l_workbook
      'CLOSE'.

* 退出Excel进程
  CALL METHOD OF
      pl_application
      'QUIT'.

* 退出是否成功状态返回值
  pl_subrc = sy-subrc.

ENDFORM.                    "f_close_file"

*&---------------------------------------------------------------------*
*&      Form  F_FRM_DOWNLOAD_EXCEL_MODULE
*&---------------------------------------------------------------------*
*       用户自己选择路径
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM f_download_path USING  value(pv_default_path)  .

  DATA: lv_default_file_name    TYPE string,
        lv_path                 TYPE string,
        lv_fullpath             TYPE string,
        lv_filename             TYPE string,
        lv_desktop_directory TYPE string.

  lv_filename  = 'Dicastal Mexico, S. DE R.L. DE C.V' && '_' &&
               sy-datum && '-' && sy-uzeit && '.xlsx'.

*  "获取文件名称
  CALL METHOD cl_gui_frontend_services=>file_save_dialog
    EXPORTING
      window_title      = lv_filename
      default_extension = 'XLSX'
      default_file_name = lv_filename
    CHANGING
      filename          = lv_filename
      path              = lv_path
      fullpath          = lv_fullpath.
  IF sy-subrc = 0.
    gv_filename = lv_fullpath.
  ENDIF.

ENDFORM.                    "f_download_path"

*&---------------------------------------------------------------------*
*&      Form  FRM_BUILD_SHEET1
*&---------------------------------------------------------------------*
*&      初始化sheet1
*&---------------------------------------------------------------------*
*    -->PL_SHEET_NAME:要打开的Sheet页的名称
*    <--PL_APPLICATION:Excel进程
*    <--PL_WORKBOOK  :工作簿
*    <--PL_SHEET          :Sheet页
*----------------------------------------------------------------------*
FORM frm_build_sheet1 CHANGING pl_application TYPE ole2_object
                               pl_workbook    TYPE ole2_object
                               pl_sheet       TYPE ole2_object.

  DATA:l_r      TYPE i, "row
       l_c      TYPE i, "col
       ls_sheet LIKE gs_sheet,
       ls_tline TYPE ty_sheet_tab,
       ls_sline TYPE ty_sheet_tab.

  PERFORM frm_open_sheet USING 'INVOICE' CHANGING g_excel g_wbook g_sheet.
  PERFORM frm_get_sheet1 CHANGING  ls_sheet.
  IF gv_typ = 'DMX_W'.
    PERFORM f_fill_cell USING 19 4 'ALUMINIUM WHEELS (AUTO PARTS) 轮子' CHANGING g_excel .
  ELSE.
    PERFORM f_fill_cell USING 19 4 'ALUMINIUM KNUCKLE ASSEMBLY 铸件' CHANGING g_excel .
  ENDIF.
  PERFORM f_fill_cell USING 8   3 ls_sheet-str01 CHANGING g_excel .      "  S1.1   SHIP TO名称：
  PERFORM f_fill_cell USING 9   3 ls_sheet-str02 CHANGING g_excel .      "  S1.2   SHIP TO地址：
  PERFORM f_fill_cell USING 13  3 ls_sheet-str03 CHANGING g_excel .      "  S1.3   SOLD TO名称：
  PERFORM f_fill_cell USING 14  3 ls_sheet-str04 CHANGING g_excel .      "  S1.4   SOLD TO地址：
  PERFORM f_fill_cell USING 8   9 ls_sheet-str05 CHANGING g_excel .      "  S1.5   INVOICE NO.：
  PERFORM f_fill_cell USING 13  9 ls_sheet-str06 CHANGING g_excel .      "  S1.6   DATE：
  PERFORM f_fill_cell USING 16  3 ls_sheet-str07 CHANGING g_excel .      "  S1.7   FROM：
  PERFORM f_fill_cell USING 16  5 ls_sheet-str08 CHANGING g_excel .      "  S1.8   TO:

  LOOP AT ls_sheet-tab INTO ls_tline.
    ls_sline = ls_tline.
    AT FIRST.
      l_r = 23.
    ENDAT.
    PERFORM f_fill_cell USING l_r 4  ls_sline-str01 CHANGING g_excel .   "  S1.9  DICA P/N：
    PERFORM f_fill_cell USING l_r 6  ls_sline-str02 CHANGING g_excel .   "  S1.10 OEM P/N：
    PERFORM f_fill_cell USING l_r 7  ls_sline-str03 CHANGING g_excel .   "  S1.11 QTY(PCS)：
    PERFORM f_fill_cell USING l_r 8  ls_sline-str04 CHANGING g_excel .   "  S1.12 CURRENCY：
    PERFORM f_fill_cell USING l_r 9  ls_sline-str05 CHANGING g_excel .   "  S1.13 UNIT PRICE：
    PERFORM f_fill_cell USING l_r 11 ls_sline-str06 CHANGING g_excel .   "  S1.14 CURRENCY：
    PERFORM f_fill_cell USING l_r 12 ls_sline-str07 CHANGING g_excel .   "  S1.15 AMOUNT：

    l_r = l_r + 1.
    AT LAST.
      PERFORM f_fill_cell USING 19 9  ls_sline-str08 CHANGING g_excel .   "  S1.16 INCOTERM 1：
      PERFORM f_fill_cell USING 19 11 ls_sline-str09 CHANGING g_excel .   "  S1.17 INCOTERM 2：
      PERFORM f_fill_cell USING 34 7  ls_sline-str10 CHANGING g_excel .   "  S1.18 TOTAL QTY(PCS)：
      PERFORM f_fill_cell USING 34 11 ls_sline-str04 CHANGING g_excel .   "  S1.14 CURRENCY：
      PERFORM f_fill_cell USING 34 12 ls_sline-str11 CHANGING g_excel .   "  S1.19 TOTAL AMOUNT：
    ENDAT.
  ENDLOOP.

ENDFORM.                    "FRM_BUILD_SHEET1"

*&---------------------------------------------------------------------*
*&      Form  FRM_BUILD_SHEET2
*&---------------------------------------------------------------------*
*&      初始化sheet2 (packing list)
*&---------------------------------------------------------------------*
*    -->PL_SHEET_NAME:要打开的Sheet页的名称
*    <--PL_APPLICATION:Excel进程
*    <--PL_WORKBOOK  :工作簿
*    <--PL_SHEET          :Sheet页
*----------------------------------------------------------------------*
FORM frm_build_sheet2 CHANGING pl_application TYPE ole2_object
                               pl_workbook    TYPE ole2_object
                               pl_sheet       TYPE ole2_object.

  DATA:l_r      TYPE i, "row
       l_c      TYPE i, "col
       ls_sheet LIKE gs_sheet,
       ls_tline TYPE ty_sheet_tab,
       ls_sline TYPE ty_sheet_tab.

  PERFORM frm_open_sheet USING 'PACKING LIST' CHANGING g_excel g_wbook g_sheet.
  PERFORM frm_get_sheet2 CHANGING  ls_sheet.
  IF gv_typ = 'DMX_W'.
    PERFORM f_fill_cell USING 15 1 'ALUMINIUM WHEELS (AUTO PARTS) 轮子' CHANGING g_excel .
  ELSE.
    PERFORM f_fill_cell USING 16 1 'ALUMINIUM KNUCKLE ASSEMBLY 铸件' CHANGING g_excel .
  ENDIF.
  PERFORM f_fill_cell USING 7   2 ls_sheet-str01 CHANGING g_excel .      "1.01  SHIP TO名称
  PERFORM f_fill_cell USING 8   2 ls_sheet-str02 CHANGING g_excel .      "1.02  SHIP TO地址：
  PERFORM f_fill_cell USING 10  2 ls_sheet-str03 CHANGING g_excel .      "1.03  CONSIGNEE名称
  PERFORM f_fill_cell USING 11  2 ls_sheet-str04 CHANGING g_excel .      "1.04  CONSIGNEE地址
  PERFORM f_fill_cell USING 7   8 ls_sheet-str05 CHANGING g_excel .      "1.05  INVOICE NO.
  PERFORM f_fill_cell USING 10  8 ls_sheet-str06 CHANGING g_excel .      "1.06  DATE

  LOOP AT ls_sheet-tab INTO ls_tline.
    ls_sline = ls_tline.
    AT FIRST.
      l_r = 19.
      l_c = 12.
    ENDAT.

    PERFORM f_fill_cell USING l_r 2  ls_sline-str01 CHANGING g_excel .   "1.07  DICA P/N
    PERFORM f_fill_cell USING l_r 3  ls_sline-str02 CHANGING g_excel .   "1.08  OEM P/N
    PERFORM f_fill_cell USING l_r 7  ls_sline-str03 CHANGING g_excel .   "1.09  QUANTITY(PCS)
    PERFORM f_fill_cell USING l_r 8  ls_sline-str04 CHANGING g_excel .   "1.10  NET WEIGHT (KG)
    PERFORM f_fill_cell USING l_r 9  ls_sline-str05 CHANGING g_excel .   "1.11  GROSS WEIGHT(KG)

    PERFORM f_fill_cell USING 36 l_c ls_sline-str14 CHANGING g_excel .   "1.20  DICA P/N
    PERFORM f_fill_cell USING 37 l_c ls_sline-str15 CHANGING g_excel .   "1.21  Customer P/N
    PERFORM f_fill_cell USING 38 l_c ls_sline-str16 CHANGING g_excel .   "1.22  Quantity：

    l_r = l_r + 1.
    l_c = l_c + 1.

    AT LAST.
      PERFORM f_fill_cell USING 30 7 ls_sline-str13 CHANGING g_excel .   "1.12  TOTAL 01
      PERFORM f_fill_cell USING 30 8 ls_sline-str10 CHANGING g_excel .   "1.12  TOTAL 02
      PERFORM f_fill_cell USING 30 9 ls_sline-str11 CHANGING g_excel .   "1.12  TOTAL 03
      PERFORM f_fill_cell USING 34 3 ls_sline-str07 CHANGING g_excel .   "1.13  TOTAL PACKAGES：
      PERFORM f_fill_cell USING 38 1 ls_sline-str08 CHANGING g_excel .   "1.14  CONTAINER/TRUCK
      PERFORM f_fill_cell USING 38 3 ls_sline-str09 CHANGING g_excel .   "1.15  SEAL NO.
      PERFORM f_fill_cell USING 38 4 ls_sline-str10 CHANGING g_excel .   "1.16  NET WEIGHT(KG)：
      PERFORM f_fill_cell USING 38 5 ls_sline-str11 CHANGING g_excel .   "1.17  GROSS WEIGHT(KG)
      PERFORM f_fill_cell USING 38 6 ls_sline-str12 CHANGING g_excel .   "1.18  PACKAGES：
      PERFORM f_fill_cell USING 38 7 ls_sline-str13 CHANGING g_excel .   "1.19  QUANTITY(PCS)
    ENDAT.
  ENDLOOP.

ENDFORM.                    "FRM_BUILD_SHEET2"

*&---------------------------------------------------------------------*
*&      Form  FRM_GET_SHEET1
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
FORM frm_get_sheet1 CHANGING ps_sheet LIKE gs_sheet.

  DATA:BEGIN OF ls_kna1,
       kunnr  TYPE kna1-kunnr,
       name1  TYPE kna1-name1,
       street TYPE adrc-street,
       house  TYPE adrc-house_num1,
       adrnr  TYPE kna1-adrnr,
       ktokd  TYPE kna1-ktokd,
      END OF ls_kna1,
      lt_kna1 LIKE TABLE OF ls_kna1.

  DATA:ls_line  LIKE gs_line,
       ls_sline TYPE ty_sheet_tab,
       lt_sline TYPE TABLE OF ty_sheet_tab.

  DATA:lv_kunnr TYPE kna1-kunnr,
       lv_kunag TYPE kna1-kunnr,
       lv_waerk TYPE likp-waerk,
       lv_sum1 TYPE p DECIMALS 2,
       lv_sum2 TYPE p DECIMALS 2,
       lv_sum3 TYPE p DECIMALS 2.

  CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
    EXPORTING
      input  = gs_print-kunnr
    IMPORTING
      output = lv_kunnr.

  CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
    EXPORTING
      input  = gs_print-kunag
    IMPORTING
      output = lv_kunag.

  SELECT kna1~kunnr
         kna1~name1
         adrc~street
         adrc~house_num1
         kna1~adrnr
         kna1~ktokd
    FROM kna1
    INNER JOIN adrc ON kna1~adrnr = adrc~addrnumber
    INTO TABLE lt_kna1
    WHERE kunnr = lv_kunnr
       OR kunnr = lv_kunag.

  SELECT SINGLE waerk FROM likp INTO lv_waerk WHERE vbeln = gs_print-vbeln.

*<!--- 数据取值 ---------------------------------------------------------------------
  " SHIP TO名称 && SHIP TO地址：
  READ TABLE lt_kna1 INTO ls_kna1 WITH KEY kunnr = lv_kunnr.
  IF sy-subrc = 0.
    ps_sheet-str01 = ls_kna1-name1.
    ps_sheet-str02 = ls_kna1-street && ls_kna1-house.
  ENDIF.

  " SOLD TO名称 && SOLD TO地址：
  READ TABLE lt_kna1 INTO ls_kna1 WITH KEY kunnr = lv_kunag.
  IF sy-subrc = 0.
    CASE ls_kna1-ktokd.
      WHEN 'Z009'.
        ps_sheet-str03 = 'CITIC DICASTAL  CO.,LTD. '.
        ps_sheet-str04 = '185 LONGHAI AVE. QINHUANGDAO ECONOMIC & ' &&
                         'TECHNOLOGICAL DEVELOPMENT ZONE HEBEI 066011'.
      WHEN 'Z002'.
        ps_sheet-str03 = ls_kna1-name1.
        ps_sheet-str04 = ls_kna1-street && ls_kna1-house.
      WHEN OTHERS.
    ENDCASE.
  ENDIF.

  " INVOICE NO. && DATE：
  ps_sheet-str05 = gs_print-tpbez.
  ps_sheet-str06 = gs_print-erdat.

  LOOP AT gs_print-tab INTO gs_line.
    CLEAR ls_line.
    ls_line = gs_line.

    " FROM： && TO:
    AT FIRST.
      ps_sheet-str07 = ls_line-port_shipment.
      ps_sheet-str08 = ls_line-port_arrival.
    ENDAT.

    " DICA P/N：
    ls_sline-str01 = ls_line-matnr.

    " OEM P/N：
    ls_sline-str02 = ls_line-kdmat.

    " QTY(PCS)：
    ls_sline-str03 = ls_line-lfimg.
    CONDENSE ls_sline-str03 NO-GAPS.
    lv_sum1 = lv_sum1 + ls_sline-str03.

    " CURRENCY：
    ls_sline-str04 = lv_waerk.

    " UNIT PRICE：
    ls_sline-str05 = '999.99'.

    " CURRENCY：
    ls_sline-str06 = lv_waerk.

    " AMOUNT：
    ls_sline-str07 = '999.99'.
    CONDENSE ls_sline-str07 NO-GAPS.
    lv_sum2 = lv_sum2 + ls_sline-str07.

    " INCOTERM 1：
    ls_sline-str08 = ls_line-text1.

    " INCOTERM 2：
    ls_sline-str09 = ls_line-text2.

    AT LAST.
      " TOTAL QTY(PCS)：
      ls_sline-str10 = lv_sum1.
      CONDENSE ls_sline-str10 NO-GAPS.

      " TOTAL AMOUNT：
      ls_sline-str11 = lv_sum2.

      CONDENSE ls_sline-str11 NO-GAPS.
      CLEAR:lv_sum1,lv_sum2,lv_sum3.
    ENDAT.


    APPEND ls_sline TO ps_sheet-tab[].
  ENDLOOP.




*!--- >
ENDFORM.                    "FRM_GET_SHEET1"

*&---------------------------------------------------------------------*
*&      Form  FRM_GET_SHEET1
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
FORM frm_get_sheet2 CHANGING ps_sheet LIKE gs_sheet.

  DATA:BEGIN OF ls_kna1,
      kunnr  TYPE kna1-kunnr,
      name1  TYPE kna1-name1,
      street TYPE adrc-street,
      house  TYPE adrc-house_num1,
      adrnr  TYPE kna1-adrnr,
      ktokd  TYPE kna1-ktokd,
     END OF ls_kna1,
     lt_kna1 LIKE TABLE OF ls_kna1.

  DATA:ls_line  LIKE gs_line,
       ls_sline TYPE ty_sheet_tab,
       lt_sline TYPE TABLE OF ty_sheet_tab.

  DATA:lv_kunnr TYPE kna1-kunnr,
       lv_kunag TYPE kna1-kunnr,
       lv_sum1 TYPE p DECIMALS 2,
       lv_sum2 TYPE p DECIMALS 2,
       lv_sum3 TYPE p DECIMALS 2.

  CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
    EXPORTING
      input  = gs_print-kunnr
    IMPORTING
      output = lv_kunnr.

  CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
    EXPORTING
      input  = gs_print-kunag
    IMPORTING
      output = lv_kunag.

  SELECT kna1~kunnr
         kna1~name1
         adrc~street
         adrc~house_num1
         kna1~adrnr
         kna1~ktokd
    FROM kna1
    INNER JOIN adrc ON kna1~adrnr = adrc~addrnumber
    INTO TABLE lt_kna1
    WHERE kunnr = lv_kunnr
       OR kunnr = lv_kunag.

*<!--- 数据取值 ---------------------------------------------------------------------
  " SHIP TO名称  && SHIP TO地址：
  READ TABLE lt_kna1 INTO ls_kna1 WITH KEY kunnr = lv_kunnr.
  IF sy-subrc = 0.
    ps_sheet-str01 = ls_kna1-name1.
    ps_sheet-str02 = ls_kna1-street && ls_kna1-house.
  ENDIF.

  " CONSIGNEE名称 && CONSIGNEE地址
  READ TABLE lt_kna1 INTO ls_kna1 WITH KEY kunnr = lv_kunag.
  IF sy-subrc = 0.
    CASE ls_kna1-ktokd.
      WHEN 'Z009'.
        ps_sheet-str03 = 'CITIC DICASTAL  CO.,LTD. '.
        ps_sheet-str04 = '185 LONGHAI AVE. QINHUANGDAO ECONOMIC & ' &&
                         'TECHNOLOGICAL DEVELOPMENT ZONE HEBEI 066011'.
      WHEN 'Z002'.
        ps_sheet-str03 = ls_kna1-name1.
        ps_sheet-str04 = ls_kna1-street && ls_kna1-house.
      WHEN OTHERS.
    ENDCASE.
  ENDIF.

  " INVOICE NO. && DATE
  ps_sheet-str05 = gs_print-tpbez.
  ps_sheet-str06 = gs_print-erdat.

  LOOP AT gs_print-tab INTO gs_line.
    CLEAR ls_line.
    ls_line = gs_line.

    " DICA P/N
    ls_sline-str01 = ls_line-matnr.

    " OEM P/N
    ls_sline-str02 = ls_line-kdmat.

    " QUANTITY(PCS)
    ls_sline-str03 = ls_line-lfimg.
    CONDENSE ls_sline-str03 NO-GAPS.
    lv_sum1 = lv_sum1 + ls_line-lfimg.

    " NET WEIGHT (KG)
    ls_sline-str04 = ls_line-ntgew.
    CONDENSE ls_sline-str04 NO-GAPS.
    lv_sum2 = lv_sum2 + ls_line-ntgew.

    " GROSS WEIGHT(KG)
    ls_sline-str05 = ls_line-brgew.
    CONDENSE ls_sline-str05 NO-GAPS.
    lv_sum3 = lv_sum3 + ls_line-brgew.

    " TOTAL PACKAGES：
    ls_sline-str07 = ls_line-ztps.

    " CONTAINER/TRUCK
    ls_sline-str08 = ls_line-vhilm_ku.

    " SEAL NO.
    ls_sline-str09 = ls_line-signi.

    " PACKAGES：
    ls_sline-str12 = ls_sline-str07.

    " DICA P/N
    ls_sline-str14 = ls_sline-str01.

    " Customer P/N
    ls_sline-str15 = ls_sline-str02.

    " Quantity：
    ls_sline-str16 = ls_sline-str03.

    AT LAST.
      " NET WEIGHT(KG)：
      ls_sline-str10 = lv_sum2.
      CONDENSE ls_sline-str10 NO-GAPS.
      " GROSS WEIGHT(KG)
      ls_sline-str11 = lv_sum3.
      CONDENSE ls_sline-str11 NO-GAPS.
      " QUANTITY(PCS)
      ls_sline-str13 = lv_sum1.
      CONDENSE ls_sline-str13 NO-GAPS.
      " TOTAL
      ls_sline-str06 = lv_sum1 && '/' && lv_sum2 && '/' && lv_sum3.
      CLEAR:lv_sum1,lv_sum2,lv_sum3.
    ENDAT.

    APPEND ls_sline TO ps_sheet-tab[].
  ENDLOOP.

*!--- >
ENDFORM.                    "FRM_GET_SHEET1"


*TYPES:BEGIN OF ty_sheet_tab,
*       str01 TYPE char255,  "S2.07  DICA P/N           S1.9  DICA P/N：
*       str02 TYPE char255,  "S2.08  OEM P/N            S1.10 OEM P/N：
*       str03 TYPE char255,  "S2.09  QUANTITY(PCS)      S1.11 QTY(PCS)：
*       str04 TYPE char255,  "S2.10  NET WEIGHT (KG)    S1.12 CURRENCY：
*       str05 TYPE char255,  "S2.11  GROSS WEIGHT(KG)   S1.13 UNIT PRICE：
*       str06 TYPE char255,  "S2.12  TOTAL              S1.14 CURRENCY：
*       str07 TYPE char255,  "S2.13  TOTAL PACKAGES：   S1.15 AMOUNT：
*       str08 TYPE char255,  "S2.14  CONTAINER/TRUCK    S1.16 INCOTERM 1：
*       str09 TYPE char255,  "S2.15  SEAL NO.           S1.17 INCOTERM 2：
*       str10 TYPE char255,  "S2.16  NET WEIGHT(KG)：   S1.18 TOTAL QTY(PCS)：
*       str11 TYPE char255,  "S2.17  GROSS WEIGHT(KG)   S1.19 TOTAL AMOUNT：
*       str12 TYPE char255,  "S2.18  PACKAGES：
*       str13 TYPE char255,  "S2.19  QUANTITY(PCS)
*       str14 TYPE char255,  "S2.20  DICA P/N
*       str15 TYPE char255,  "S2.21  Customer P/N
*       str16 TYPE char255,  "S2.22  Quantity：
*       str17 TYPE char255,  "
*       str18 TYPE char255,  "
*       str19 TYPE char255,  "
*       str20 TYPE char255,  "
*    END OF ty_sheet_tab.
*
*TYPES:BEGIN OF ty_sheet,
*       str01 TYPE char255,  "S2.01  SHIP TO名称        S1.1   SHIP TO名称：
*       str02 TYPE char255,  "S2.02  SHIP TO地址        S1.2   SHIP TO地址：
*       str03 TYPE char255,  "S2.03  CONSIGNEE名称      S1.3   SOLD TO名称：
*       str04 TYPE char255,  "S2.04  CONSIGNEE地址      S1.4   SOLD TO地址：
*       str05 TYPE char255,  "S2.05  INVOICE NO.        S1.5   INVOICE NO.：
*       str06 TYPE char255,  "S2.06  DATE               S1.6   DATE：
*       str07 TYPE char255,  "                          S1.7   FROM：
*       str08 TYPE char255,  "                          S1.8   TO:
*       str09 TYPE char255,  "
*       str10 TYPE char255,  "
*    END OF ty_sheet.
*
*DATA:BEGIN OF gs_sheet.
*        INCLUDE TYPE ty_sheet.
*DATA:tab  TYPE STANDARD TABLE OF ty_sheet_tab,
*    END OF gs_sheet.
*
*DATA: gv_typ      TYPE char10,
*      gv_filename TYPE rlgrap-filename,
*      g_excel     TYPE ole2_object,
*      g_wbook     TYPE ole2_object,
*      g_sheet     TYPE ole2_object,
*      g_cell      TYPE ole2_object..