ZBCC0008-EXCEL Operate-SE38(I類型)

*&---------------------------------------------------------------------*
*& INCLUDE          ZBCC0008
*&---------------------------------------------------------------------*

INCLUDE ole2incl.

DATA:
  o_excel     TYPE ole2_object,
  o_workbooks TYPE ole2_object,
  o_workbook  TYPE ole2_object,
  o_sheet     TYPE ole2_object,
  o_cell      TYPE ole2_object.

DATA  %ole_subrc TYPE sy-subrc.

DATA: %visible VALUE '0'." 1: foreground, 0: background

DATA: %sheet_no TYPE i.





*&---------------------------------------------------------------------*
* macro
*&---------------------------------------------------------------------*
DEFINE err_hdl.
  IF sy-subrc <> 0.
    %ole_subrc = sy-subrc.
    MESSAGE 'Excel OLE ERROR: RETURN CODE =' && sy-subrc
        TYPE 'I'.
    RETURN.
  ENDIF.
END-OF-DEFINITION.




DEFINE fill_cell.
* &1 : row
* &2 : column
* &3 : value

  CALL METHOD OF o_sheet 'Cells' = o_cell
        EXPORTING #1 = &1
                  #2 = &2.
  err_hdl.

  SET PROPERTY OF o_cell 'NumberFormatLocal' = '@'.
  err_hdl.

  SET PROPERTY OF o_cell 'Value' = &3.
  err_hdl.

*  SET PROPERTY OF o_cell 'FormulaR1C1' = &3.
*  ERR_HDL.

*  set PROPERTY OF H_ZL 'Font' = H_F.
*  RR_HDL.
*  SET PROPERTY OF H_F 'Bold' = BOLD .
*  ERR_HDL.

END-OF-DEFINITION.

****
*&---------------------------------------------------------------------*
*&      Form  OPEN_EXCEL
*&---------------------------------------------------------------------*
*       開啟 EXCEL 設定頁籤
*----------------------------------------------------------------------*
*      -->f_sheet_cnt 開啟頁籤數
*----------------------------------------------------------------------*
FORM open_excel  USING    VALUE(f_sheet_cnt).

  DATA: l_more_sheet TYPE i.
  DATA: lo_sheets TYPE ole2_object.
***********************************
  CLEAR: %ole_subrc.


  PERFORM progress USING 'Open Excel...'.


** Start EXCEL
  CREATE OBJECT o_excel 'excel.application'.
  err_hdl.

  SET PROPERTY OF o_excel  'VISIBLE' = %visible."In Foreground Mode
  err_hdl.

***  get list of workbooks, initially empty
  CALL METHOD OF o_excel 'Workbooks' = o_workbooks.
  err_hdl.

*add a new workbook
  CALL METHOD OF o_workbooks 'Add' = o_workbook.
  err_hdl.

  l_more_sheet = f_sheet_cnt - 1.
  DO l_more_sheet TIMES.
    GET PROPERTY OF o_excel 'Sheets' = lo_sheets .
    CALL METHOD OF lo_sheets 'Add' = o_workbook.
  ENDDO.


*  SET PROPERTY OF o_excel 'SheetsInNewWorkbook' = f_sheet_cnt. "No of sheets
*  err_hdl.
*  IF l_more_sheet > 1.
*    SET PROPERTY OF o_excel 'SheetsInNewWorkbook' = l_more_sheet. "No of sheets
*    err_hdl.
*  ENDIF.

  CLEAR: %sheet_no.
ENDFORM.
*&---------------------------------------------------------------------*
*&      Form  ADD_SHEET_CONTENT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM add_sheet_content  USING    VALUE(f_sheet_name)
                                 ft_label TYPE table
                                 ft_data TYPE table.

  DATA: l_row TYPE i.
  DATA: l_column TYPE i.

  FIELD-SYMBOLS: <lfs>.
  FIELD-SYMBOLS: <lfs_wa>.

*********************

  PERFORM progress USING f_sheet_name.


  ADD 1 TO %sheet_no.

  CALL METHOD OF o_excel 'WORKSHEETS' = o_sheet
      EXPORTING #1 = %sheet_no.
  CALL METHOD OF o_sheet 'ACTIVATE'.
  SET PROPERTY OF o_sheet 'NAME' = f_sheet_name.


****  表頭標籤
  IF ft_label IS NOT INITIAL.
    ADD 1 TO l_row.
    LOOP AT ft_label ASSIGNING <lfs_wa>.
      l_column = sy-tabix.
      ASSIGN COMPONENT 1 OF STRUCTURE <lfs_wa> TO <lfs>.

      fill_cell l_row l_column <lfs>.

    ENDLOOP."LOOP AT ft_label
  ENDIF."IF ft_label is not INITIAL.

****  content

  LOOP AT ft_data ASSIGNING <lfs_wa>.

    ADD 1 TO l_row.
    DO.
      l_column = sy-index.
      ASSIGN COMPONENT l_column OF STRUCTURE <lfs_wa> TO <lfs>.
      IF sy-subrc = 0.
        fill_cell l_row l_column <lfs>.

      ELSE.
        EXIT.
      ENDIF.
    ENDDO.

  ENDLOOP."LOOP AT ft_data

ENDFORM.
*&---------------------------------------------------------------------*
*&      Form  CLOSE_EXCEL
*&---------------------------------------------------------------------*
*     for xlsx
*----------------------------------------------------------------------*
*      -->P_L_FILE  text
*----------------------------------------------------------------------*
FORM close_excel  USING    f_file.

  PERFORM progress USING 'Close Excel....'.

* Save the Excel file
  GET PROPERTY OF o_excel 'ActiveWorkbook' = o_workbook.
  CALL METHOD OF o_workbook 'SAVEAS'
    EXPORTING
      #1 = f_file.
  err_hdl.

  CALL METHOD OF o_workbook 'CLOSE'.
  err_hdl.
  CALL METHOD OF o_excel 'QUIT'.
  err_hdl.

  o_excel-handle = -1.

  FREE OBJECT o_excel.
  FREE OBJECT o_workbooks.
  FREE OBJECT o_workbook.
  FREE OBJECT o_sheet.
  FREE OBJECT o_cell.


ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  CLOSE_EXCEL
*&---------------------------------------------------------------------*
*     for xls    excel 97-2003
*----------------------------------------------------------------------*
*      -->P_L_FILE  text
*----------------------------------------------------------------------*
FORM close_excel_xls  USING    f_file.

  PERFORM progress USING 'Close Excel....'.

* Save the Excel file
  GET PROPERTY OF o_excel 'ActiveWorkbook' = o_workbook.
  CALL METHOD OF o_workbook 'SAVEAS'
    EXPORTING
      #1 = f_file    "file name
      #2 = 1.            "fileFormat   .xls for excel 97-2003
  err_hdl.

  CALL METHOD OF o_workbook 'CLOSE'.
  err_hdl.
  CALL METHOD OF o_excel 'QUIT'.
  err_hdl.

  o_excel-handle = -1.

  FREE OBJECT o_excel.
  FREE OBJECT o_workbooks.
  FREE OBJECT o_workbook.
  FREE OBJECT o_sheet.
  FREE OBJECT o_cell.


ENDFORM.



*&---------------------------------------------------------------------*
*&      Form  PROGRESS
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM progress  USING    VALUE(f_text).


  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
    EXPORTING
*     PERCENTAGE       = 0
      text = f_text.


ENDFORM.