ABAP中读取EXCEL中不同的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