엑셀 Multi Sheet Upload/Download Logic

Multi Sheet를 포함하는 특정 엑셀 템플릿에 데이터를 다운로드/업로드 하는 로직.

(반드시 Excel Sheet Name 앞뒤 공백을 제거해야 함!)

참고 URL : http://wiki.scn.sap.com/wiki/display/Snippets/Read+multiple+sheets+of+an+Excel+file+into+SAP+through+ABAP

*--> Excel Download Variable.
DATA: excel     TYPE ole2_object,
      workbook  TYPE ole2_object,
      books     TYPE ole2_object,
      book      TYPE ole2_object,
      sheets    TYPE ole2_object,
      sheet     TYPE ole2_object,
      activesheet TYPE ole2_object,
      cells     TYPE ole2_object,
      cell      TYPE ole2_object,
      row       TYPE ole2_object,
      rowheight TYPE ole2_object,
      buffer    TYPE ole2_object,
      columnwidth TYPE ole2_object,
      rowwidth  TYPE ole2_object.

CREATE OBJECT excel 'EXCEL.APPLICATION'.

DATA: lv_row(5) TYPE i.
CONCATENATE 'c:\temp\' gv_filename_pr '.xlsx' INTO gv_file_dest.
CONDENSE gv_filename_pr NO-GAPS.

PERFORM download_template USING 'ZLD5BIA0040_SAMPLE01' gv_file_dest
                       CHANGING gv_filename_pr.

PERFORM open_excel_template USING gv_filename_pr.

PERFORM change_sheet USING 1.

* InfoCube, MultiCube Text.
  IF pa_mt = 'X'.
    lv_row = c_header2.
    LOOP AT gt_cub_p.
      ADD 1 TO lv_row.
      PERFORM fill_cells USING lv_row  1  gt_cub_p-infocube.
      PERFORM fill_cells USING lv_row  2  gt_cub_p-txtsh_k.
      PERFORM fill_cells USING lv_row  3  gt_cub_p-txtlg_k.
      PERFORM fill_cells USING lv_row  4  gt_cub_p-txtsh_e.
      PERFORM fill_cells USING lv_row  5  gt_cub_p-txtlg_e.
      PERFORM fill_cells USING lv_row  6  gt_cub_p-txtsh_c.
      PERFORM fill_cells USING lv_row  7  gt_cub_p-txtlg_c.
    ENDLOOP.
  ENDIF.

PERFORM change_sheet USING 2.
*  PERFORM copy_line USING line.
*  PERFORM dele_line USING line.

CALL METHOD OF workbook 'SAVE'.
CALL METHOD OF workbook 'CLOSE'.

*&---------------------------------------------------------------------*
*&      Form  DOWNLOAD_TEMPLATE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM download_template USING pv_id        pv_dest
                    CHANGING pv_filename.
DATA:  wwwdata_item LIKE wwwdatatab.
DATA:  rc TYPE i.

CALL FUNCTION 'WS_FILE_DELETE'
  EXPORTING
    file   = pv_filename
  IMPORTING
    return = rc.

SELECT SINGLE * FROM wwwdata
         INTO CORRESPONDING FIELDS OF wwwdata_item
        WHERE objid = pv_id.

CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
  EXPORTING
    key         = wwwdata_item
    destination = pv_dest
  CHANGING
    temp        = pv_filename.

ENDFORM.                    " DOWNLOAD_TEMPLATE
*&---------------------------------------------------------------------*
*&      Form  OPEN_EXCEL_TEMPLATE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM open_excel_template USING p_filename.
  IF sy-subrc NE 0.
    MESSAGE i000(z1) WITH sy-msgli.
    EXIT .
  CALL METHOD OF
    workbook
      'Sheets' = sheets.
  CALL METHOD OF
      sheets
      'item' = sheet
    EXPORTING
      #1     = 1. " sheet number.
  ENDIF.

ENDFORM.                    " OPEN_EXCEL_TEMPLATE
*&---------------------------------------------------------------------*
*&      Form  CHANGE_SHEET
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM change_sheet   USING sheetnum.
  CALL METHOD OF excel 'SHEETS' = sheet
    EXPORTING
      #1       = sheetnum.
  CALL METHOD OF sheet 'SELECT'.

ENDFORM.                    " CHANGE_SHEET
*&---------------------------------------------------------------------*
*&      Form  FILL_CELLS

*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM fill_cells USING i j val.
  DATA : ls_font         TYPE ole2_object.
  DATA : ls_inte         TYPE ole2_object.
  DATA : ls_boder        TYPE ole2_object.
  CALL METHOD OF
      excel
      'CELLS' = cell
    EXPORTING
      #1      = i
      #2      = j.
  SET PROPERTY OF cell 'VALUE' = val.

ENDFORM.                    " FILL_CELLS
*&---------------------------------------------------------------------*
*&      Form  COPY_LINE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM copy_line  USING  p_row.
  DATA i_cnt TYPE i.
  i_cnt = p_row.
  DO 1 TIMES.
    CALL METHOD OF
        excel
        'ROWS' = cell
      EXPORTING
        #1     = i_cnt.
    CALL METHOD OF
      cell
      'SELECT'.
    CALL METHOD OF
        sheet
        'CELL' = rowheight
      EXPORTING
        #1     = 1600.
    CALL METHOD OF
      excel
        'SELECTION' = buffer.
    CALL METHOD OF
      buffer
      'COPY'.
    i_cnt = i_cnt + 1.
    CALL METHOD OF
        excel
        'ROWS' = cell
      EXPORTING
        #1     = i_cnt.
    CALL METHOD OF
      cell
      'SELECT'.
    CALL METHOD OF
      excel
        'ActiveSheet' = sheet.
    CALL METHOD OF
      excel

        'SELECTION' = buffer.
    CALL METHOD OF
      buffer
        'Entirerow' = buffer.
    CALL METHOD OF
      buffer
        'Insert' = buffer.
  ENDDO.
  CLEAR i_cnt.

ENDFORM.                    " COPY_LINE
*&---------------------------------------------------------------------*
*&      Form  DELE_LINE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM dele_line USING p_row.
  CALL METHOD OF
      excel
      'ROWS' = cell
    EXPORTING
      #1     = p_row.
  CALL METHOD OF
    cell
    'SELECT'.
  CALL METHOD OF
    excel
      'SELECTION' = buffer.
  CALL METHOD OF
    buffer
    'DELETE'.

ENDFORM.                    " DELE_LINE
*&---------------------------------------------------------------------*
*&      Form  GET_EXCEL_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM get_excel_data USING pv_file pv_sheet pv_row pv_col. " 파일명, 시트번호, 행, 열
* DEFINE SCREEN CONTAINER
  DATA: lo_container TYPE REF TO cl_gui_custom_container.
  DATA: lo_error       TYPE REF TO i_oi_error,
        lo_control     TYPE REF TO i_oi_container_control,
        lo_document    TYPE REF TO i_oi_document_proxy,
        lo_spreadsheet TYPE REF TO i_oi_spreadsheet,
        lo_retcode(256) TYPE c,
        ls_data        TYPE soi_generic_item.
  DATA: lt_sheets TYPE soi_sheets_table.
  DATA: ls_sheets LIKE LINE OF lt_sheets.
    
* Data declarations.
  DATA: lv_doc_name    TYPE char256,
        lv_changed     TYPE int4,
        lt_ranges      TYPE soi_range_list,
        ls_ranges      TYPE soi_range_item. 

 CLEAR: gt_excel_data, gt_excel_data[].

* Create Instance control for container
  CALL METHOD c_oi_container_control_creator=>get_container_control
    IMPORTING
      control = lo_control
      error   = lo_error. 

  CHECK lo_error->has_failed NE c_x. 

* Create generic container linked to container in screen 100
  CREATE OBJECT lo_container
    EXPORTING
      container_name              = 'CONTAINER'
    EXCEPTIONS
      cntl_error                  = 1
      cntl_system_error           = 2
      create_error                = 3
      lifetime_error              = 4
      lifetime_dynpro_dynpro_link = 5
      OTHERS                      = 6. 

* Establish connection to GUI Control
  CALL METHOD lo_control->init_control
    EXPORTING
      r3_application_name = 'Excel Document Container'
      inplace_enabled     = 'X'
      parent              = lo_container
    IMPORTING
      error               = lo_error.

  CHECK lo_error->has_failed NE c_x. 

* Create Document Proxy
  CALL METHOD lo_control->get_document_proxy
    EXPORTING
      document_type  = soi_doctype_excel_sheet
    IMPORTING
      document_proxy = lo_document
      error          = lo_error. 

  CHECK lo_error->has_failed NE c_x. 

  CONCATENATE 'FILE://' pv_file INTO lv_doc_name.

* Open Spreadsheet in SAPWORKDIR
  CALL METHOD lo_document->open_document
    EXPORTING
      open_inplace   = 'X'
      document_title = 'Excel'
      document_url   = lv_doc_name
      no_flush       = ''
    IMPORTING
      error          = lo_error.

  CHECK lo_error->has_failed NE c_x.

* Open Spreadsheet interface
  CALL METHOD lo_document->get_spreadsheet_interface
    EXPORTING
      no_flush        = ''
    IMPORTING
      sheet_interface = lo_spreadsheet
      error           = lo_error.

  CHECK lo_error->has_failed NE c_x.

* Get Sheets.
  CALL METHOD lo_spreadsheet->get_sheets
    EXPORTING
      no_flush = ''
      updating = ''
    IMPORTING
      sheets = lt_sheets.

  READ TABLE lt_sheets into ls_sheets index pv_sheet.

* Select Sheet.
  CALL METHOD lo_spreadsheet->select_sheet
    EXPORTING
      name    = ls_sheets-sheet_name
    IMPORTING
      error   = lo_error
      retcode = lo_retcode.

  CHECK lo_error->has_failed NE c_x.

* Set selection for 1000 rows
  CALL METHOD lo_spreadsheet->set_selection
    EXPORTING
      top     = 2  <-- 시작 포지션.
      left    = 1  <-- 
      rows    = pv_row
      columns = pv_col.

* Define Range in spreadsheet
  CALL METHOD lo_spreadsheet->insert_range
    EXPORTING
      name     = 'Test'
      rows     = pv_row
      columns  = pv_col
      no_flush = ''
    IMPORTING
      error    = lo_error.

  CHECK lo_error->has_failed NE c_x.

 ls_ranges-name    = 'Test'.
  ls_ranges-rows    = pv_row.
  ls_ranges-columns = pv_col.
  APPEND ls_ranges TO lt_ranges.

* Get data
  CALL METHOD lo_spreadsheet->get_ranges_data
    EXPORTING
      all      = ''
      no_flush = ''
    IMPORTING
      contents = gt_excel_data
      error    = lo_error
    CHANGING
      ranges   = lt_ranges.

CHECK lo_error->has_failed NE c_x.

* Close the document
  CALL METHOD lo_document->close_document
    EXPORTING
      do_save     = ''
      no_flush    = ''
    IMPORTING
      has_changed = lv_changed
      error       = lo_error.

CHECK lo_error->has_failed NE c_x.

* Clear Document Resources
  CALL METHOD lo_document->release_document
    EXPORTING
      no_flush = ''
    IMPORTING
      error    = lo_error.

CHECK lo_error->has_failed NE c_x.

* Clear table of file names
FREE: lo_control.