跨sheet引用数据_提取多个sheet固定位置的数据[通俗易懂]

function ZALSM_EXCEL_TO_INTERNAL_TABLE .
*”———————————————————————-
*”*”Local interface:
*”  IMPORTING
*”     VALUE(FILENAME) LIKE  RLGRAP-FILENAME
*”     VALUE(I_BEGIN_COL) TYPE  I
*”     VALUE(I_BEGIN_ROW) TYPE  I
*”     VALUE(I_END_COL) TYPE  I
*”     VALUE(I_END_ROW) TYPE  I
*”     VALUE(SHEET_NAME) LIKE  ALSMEX_TABLINE-VALUE OPTIONAL
*”  TABLES
*”      INTERN STRUCTURE  ALSMEX_TABLINE
*”  EXCEPTIONS
*”      INCONSISTENT_PARAMETERS
*”      UPLOAD_OLE
*”———————————————————————-

  data: EXCEL_TAB     type  TY_T_SENDER.
  data: LD_SEPARATOR  type  C.
  data: APPLICATION   type  OLE2_OBJECT,
        WORKBOOK      type  OLE2_OBJECT,
        RANGE         type  OLE2_OBJECT,
        WORKSHEET     type  OLE2_OBJECT.
  data: H_CELL        type  OLE2_OBJECT,
        H_CELL1       type  OLE2_OBJECT.
  data:
    LD_RC             type I.
*   Rückgabewert der Methode “clipboard_export     “

* Makro für Fehlerbehandlung der Methods
  define M_MESSAGE.
    CASE SY-SUBRC.
      WHEN 0.
      WHEN 1.
        MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
                WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
      WHEN OTHERS. RAISE UPLOAD_OLE.
    ENDCASE.
  end-of-definition.

* check parameters
  if I_BEGIN_ROW > I_END_ROW. raise INCONSISTENT_PARAMETERS. endif.
  if I_BEGIN_COL > I_END_COL. raise INCONSISTENT_PARAMETERS. endif.

* Get TAB-sign for separation of fields
  class CL_ABAP_CHAR_UTILITIES definition load.
  LD_SEPARATOR = CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB.

* open file in Excel
  if APPLICATION-HEADER = SPACE or APPLICATION-HANDLE = -1.
    create object APPLICATION ‘Excel.Application’.
    M_MESSAGE.
  endif.
  call method of APPLICATION ‘Workbooks’ = WORKBOOK.
  M_MESSAGE.
  call method of WORKBOOK ‘Open’
    exporting
      #1 = FILENAME.
  M_MESSAGE.

*–ADD HOUZHIYU
**  set property of application ‘Visible’ = 1.
**  m_message.
  if SHEET_NAME = SPACE.”用默认模式
    get property of  APPLICATION ‘ACTIVESHEET’ = WORKSHEET.
    M_MESSAGE.
  else.
*–>可以实现读取多个sheet
    call method of APPLICATION ‘WORKSHEETS’ = WORKSHEET
      exporting
        #1 = SHEET_NAME.

    call method of WORKSHEET ‘Activate’.
    M_MESSAGE.
  endif.
*—END OF————————————————

* mark whole spread sheet
  call method of WORKSHEET ‘Cells’ = H_CELL
    exporting
      #1 = I_BEGIN_ROW
      #2 = I_BEGIN_COL.
  M_MESSAGE.
  call method of WORKSHEET ‘Cells’ = H_CELL1
    exporting
      #1 = I_END_ROW
      #2 = I_END_COL.
  M_MESSAGE.

  call method of WORKSHEET ‘RANGE’ = RANGE
    exporting
      #1 = H_CELL
      #2 = H_CELL1.
  M_MESSAGE.
  call method of RANGE ‘SELECT’.
  M_MESSAGE.

* copy marked area (whole spread sheet) into Clippboard
  call method of RANGE ‘COPY’.
  M_MESSAGE.

* read clipboard into ABAP
  call method CL_GUI_FRONTEND_SERVICES=>CLIPBOARD_IMPORT
    importing
      DATA                 = EXCEL_TAB
    exceptions
      CNTL_ERROR           = 1
*      ERROR_NO_GUI         = 2
*      NOT_SUPPORTED_BY_GUI = 3
      others               = 4
          .
  if SY-SUBRC <> 0.
    message A037(ALSMEX).
  endif.

  perform SEPARATED_TO_INTERN_CONVERT tables EXCEL_TAB INTERN
                                      using  LD_SEPARATOR.

* clear clipboard
  refresh EXCEL_TAB.
  call method CL_GUI_FRONTEND_SERVICES=>CLIPBOARD_EXPORT
     importing
        DATA                 = EXCEL_TAB
     changing
        RC                   = LD_RC
     exceptions
        CNTL_ERROR           = 1
*       ERROR_NO_GUI         = 2
*       NOT_SUPPORTED_BY_GUI = 3
        others               = 4
          .

* quit Excel and free ABAP Object – unfortunately, this does not kill
* the Excel process
  call method of APPLICATION ‘QUIT’.
  M_MESSAGE.

* >>>>> Begin of change note 575877
* to kill the Excel process it’s necessary to free all used objects
  free object H_CELL.       M_MESSAGE.
  free object H_CELL1.      M_MESSAGE.
  free object RANGE.        M_MESSAGE.
  free object WORKSHEET.    M_MESSAGE.
  free object WORKBOOK.     M_MESSAGE.
  free object APPLICATION.  M_MESSAGE.
* <<<<< End of change note 575877
endfunction.

*&———————————————————————*

*& Report  ZPMI1224

*&开发人：侯志宇

*&———————————————————————*

*&

*&

*&———————————————————————*

report  ZPMI1224.

tables: ZZPMTTZSML,”通知书抬头表

        ZZPMTTZSSB,”通知书设备表

        ZZPMTTZSXM,”通知书项目表

        ZZPMTTEMPLATE.”模板

data: LT_ZZPMTTZSML like table of ZZPMTTZSML with header line,”通知书抬头表

      LT_ZZPMTTZSSB like table of ZZPMTTZSSB with header line,”通知书设备表

      LT_ZZPMTTZSXM like table of ZZPMTTZSXM with header line,”通知书项目表

      LT_ZZPMTTEMPLATE like table of ZZPMTTEMPLATE with header line.”模板

selection-screen begin of block BLOCK1 with frame title TEXT-001.

parameters: P_FILE like RLGRAP-FILENAME obligatory.

parameters: P_BOX type C as checkbox default ‘X’.”P_BOX为X则是创建，为空则是修改

selection-screen end of block BLOCK1.

*&——————————————————————-*

* AT SELECTION-SCREEN

*&——————————————————————-*

at selection-screen on value-request for P_FILE.

*  选择文件

  perform GET_FILENAME using P_FILE.

start-of-selection.

*—加载文件

*–取得通知书抬头表数据

  perform UPOLOAD_FILE tables LT_ZZPMTTZSML using ‘ZZPMTTZSML’.

*–取得通知书设备表

  perform UPOLOAD_FILE tables LT_ZZPMTTZSSB using ‘ZZPMTTZSSB’.

*–取得通知书项目表

  perform UPOLOAD_FILE tables LT_ZZPMTTZSXM using ‘ZZPMTTZSXM’.

*–取得模板

  perform UPOLOAD_FILE tables LT_ZZPMTTEMPLATE using ‘ZZPMTTEMPLATE’.

*—>导入数据

  perform FRM_SAVE.

*&———————————————————————*

*&      Form  get_filename

*&———————————————————————*

*       text

*———————————————————————-*

*  选择文件

*———————————————————————-*

form GET_FILENAME  using    P_P_FILE.

  “出现打开文件的对话框

  call function ‘WS_FILENAME_GET’

    exporting

      MASK             = ‘,*.*,*.*.’

      MODE             = ‘O’ “S为保存，O为打开

    importing

      FILENAME         = P_P_FILE

    exceptions

      INV_WINSYS       = 1

      NO_BATCH         = 2

      SELECTION_CANCEL = 3

      SELECTION_ERROR  = 4

      others           = 5.

endform.                    “GET_FILENAME

*&———————————————————————*

*&      Form  UPOLOAD_FILE

*&———————————————————————*

*       text

*———————————————————————-*

*  –>  p1        text

*  加载文件

*———————————————————————-*

form UPOLOAD_FILE tables LT_INPUT type standard table using SHEET_NAME.

  data GT_UPLOAD  like table of ALSMEX_TABLINE with header line.

  data: LS_I type I.

  field-symbols <F>.

  call function ‘ZALSM_EXCEL_TO_INTERNAL_TABLE’

    exporting

      FILENAME                = P_FILE

      I_BEGIN_COL             = ‘1’

      I_BEGIN_ROW             = ‘2’

      I_END_COL               = ’14’

      I_END_ROW               = ‘50000’

      SHEET_NAME              = SHEET_NAME”指定SHEET名

    tables

      INTERN                  = GT_UPLOAD

    exceptions

      INCONSISTENT_PARAMETERS = 1

      UPLOAD_OLE              = 2

      others                  = 3.

  if SY-SUBRC = 0.

*—>处理读取出的数据

    sort GT_UPLOAD by ROW COL.

    loop at GT_UPLOAD.

      LS_I = GT_UPLOAD-COL.

      assign component LS_I of structure LT_INPUT to <F>.

      <F> = GT_UPLOAD-VALUE.

      at end of ROW.

        append LT_INPUT to LT_INPUT.

        clear:LT_INPUT.

      endat.

    endloop.

  else.

    write: / ‘EXCEL UPLOAD FAILED ‘, P_FILE, SY-SUBRC.

  endif.

endform.                    ” UPOLOAD_FILE

*&———————————————————————*

*&      Form  FRM_SAVE

*&———————————————————————*

*       text

*———————————————————————-*

*  –>  p1        text

*  导入数据

*———————————————————————-*

form FRM_SAVE .

endform.                    ” FRM_SAVE