OLE_自用_0228

OLE

&---------------------------------------------------------------------
*& REPORT ZTEST_L03
&---------------------------------------------------------------------
*&
&---------------------------------------------------------------------
REPORT ztest_l03.

DATA:
wa_excel TYPE ole2_object,
wa_book TYPE ole2_object,
wa_sheet TYPE ole2_object,
wa_cell TYPE ole2_object.

DATA: v_index TYPE i VALUE IS INITIAL,
v_sheet_index TYPE i VALUE IS INITIAL.

DATA: lo_struct TYPE REF TO cl_abap_structdescr,
lt_comp TYPE cl_abap_structdescr=>component_table,
lw_components TYPE i.

TYPES:BEGIN OF ty_typ,
header1 TYPE string,
header2 TYPE string,
header3 TYPE string,
header4 TYPE string,
header5 TYPE string,
END OF ty_typ.

DATA it_fieldcat TYPE STANDARD TABLE OF ty_typ.
DATA wa_fieldcat TYPE ty_typ.

it_fieldcat = VALUE #( ( header1 = ‘header1’ header2 = ‘header1’ header3 = ‘header1’ header4 = ‘header1’ header5 = ‘header5’)
( header1 = ‘DATA1’ header2 = ‘DATA2’ header3 = ‘DATA3’ header4 = ‘DATA4’ header5 = ‘header5’)
( header1 = ‘DATA1’ header2 = ‘DATA2’ header3 = ‘DATA3’ header4 = ‘DATA4’ header5 = ‘header5’)
( header1 = ‘DATA1’ header2 = ‘DATA2’ header3 = ‘DATA3’ header4 = ‘DATA4’ header5 = ‘header5’) ) .

PARAMETERS p_file TYPE string OBLIGATORY.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

CALL METHOD cl_gui_frontend_services=>directory_browse
EXPORTING
window_title = ‘Pelease select the download directory’
CHANGING
selected_folder = p_file
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.

• 打开或者新建一个文件
  CONCATENATE p_file ‘\ XXX表’ ‘’ sy-datum '’ sy-uzeit
  ‘.XLS’
  INTO p_file.

INITIALIZATION.

• 获取工作区的列数
  lo_struct ?= cl_abap_structdescr=>describe_by_data( wa_fieldcat ).
  *读取结果
  lt_comp = lo_struct->get_components( ).
  *项目数
• DESCRIBE TABLE LT_COMP LINES LW_COMPONENTS.
  lw_components = lines( lt_comp ).

START-OF-SELECTION.

PERFORM FRM_CHECK_PATH.

*CALL METHOD cl_gui_frontend_services=>directory_browse

• CHANGING
• selected_folder = P_FILE_STR.

*DATA p_file

*创建一个excel对象
CREATE OBJECT wa_excel ‘EXCEL.APPLICATION’.
SET PROPERTY OF wa_excel ‘VISIBLE’ = 0.

*打开一个工作空间 一个空间可以装载多个SHEET
CALL METHOD OF wa_excel ‘WORKBOOKS’ = wa_book.
*创建两个SHEET
SET PROPERTY OF wa_excel ‘SHEETSINNEWWORKBOOK’ = 2. "如需多个SHEETS,将1改成相应的值
CALL METHOD OF wa_book ‘ADD’ = wa_sheet.

*----------------------------------------------------
*–操作SHEET1
v_sheet_index = 2.
*–打开sheet对象
CALL METHOD OF wa_excel ‘WORKSHEETS’ = wa_sheet
EXPORTING
#1 = v_sheet_index.
*–激活sheet对象并设置sheet属性名称
CALL METHOD OF wa_sheet ‘ACTIVATE’.
SET PROPERTY OF wa_sheet ‘NAME’ = ‘SHEETNAME2’.

CLEAR v_index.
LOOP AT it_fieldcat INTO wa_fieldcat. " EXCEL 抬头
v_index = v_index + 1.

PERFORM frm_fill_cells USING lw_components
wa_fieldcat
v_index.

ENDLOOP.

*GET PROPERTY OF wa_excel ‘ACTIVEWORKBOOK’ = wa_book.

*–保存sheet对象
CALL METHOD OF wa_sheet ‘SAVEAS’
EXPORTING
#1 = p_file
#2 = 1.
*----------------------------------------------------
*–操作SHEET2
v_sheet_index = 1.

CALL METHOD OF wa_excel ‘WORKSHEETS’ = wa_sheet
EXPORTING
#1 = v_sheet_index.

CALL METHOD OF wa_sheet ‘ACTIVATE’.
SET PROPERTY OF wa_sheet ‘NAME’ = ‘SHEETNAME1’.

CLEAR v_index.
LOOP AT it_fieldcat INTO wa_fieldcat. " EXCEL 抬头
v_index = v_index + 1.

PERFORM frm_fill_cells USING lw_components
wa_fieldcat
v_index.

ENDLOOP.

CALL METHOD OF wa_sheet ‘SAVEAS’
EXPORTING
#1 = p_file
#2 = 1.
*关闭工作空间
*GET PROPERTY OF wa_excel ‘ACTIVEWORKBOOK’ = wa_book.
CALL METHOD OF wa_book ‘CLOSE’.
*----------------------------------------------------

• 关闭excel
  CALL METHOD OF wa_excel ‘QUIT’.

MESSAGE ‘导出 ’ && p_file && ’ 已完成！请撒花庆祝！’ TYPE ‘S’.
&---------------------------------------------------------------------
*& Form FRM_FILL_CELLS
&---------------------------------------------------------------------
*& text
&---------------------------------------------------------------------
*& --> LW_COMPONENTS
*& --> WA_FIELDCAT
&---------------------------------------------------------------------
FORM frm_fill_cells USING u_components
u_fieldcat
u_index.
FIELD-SYMBOLS : <fs_fieldcat> TYPE any,
<fs_value> TYPE any.

ASSIGN u_fieldcat TO <fs_fieldcat>.
CHECK <fs_fieldcat> IS ASSIGNED.

• 循环所有的列
  DO u_components TIMES.

  ASSIGN COMPONENT sy-index OF STRUCTURE <fs_fieldcat> TO <fs_value>.

  IF <fs_value> IS ASSIGNED.

  CALL METHOD OF wa_excel 'CELLS' = wa_cell
  EXPORTING #1 = u_index #2 = sy-index.
  SET PROPERTY OF wa_cell 'VALUE' = <fs_value>.
  

  ENDIF.
  ENDDO.

ENDFORM.
&---------------------------------------------------------------------
*& Form FRM_CHECK_PATH
&---------------------------------------------------------------------
*& text
&---------------------------------------------------------------------
*& --> p1 text
*& <-- p2 text
&---------------------------------------------------------------------
FORM frm_check_path .

DATA LW_LENGHT TYPE I.

LW_LENGHT = STRLEN( P_FILE ).

DATA(LW_LENGHT1) = LW_LENGHT - 4.

IF LW_LENGHT < 4.
MESSAGE ‘ERROR’ TYPE ‘S’ DISPLAY LIKE ‘E’.
LEAVE LIST-PROCESSING.
ENDIF.

IF p_file+LW_LENGHT1(4) <> ‘.XLS’.
MESSAGE ‘ERROR1’ TYPE ‘S’ DISPLAY LIKE ‘E’.
LEAVE LIST-PROCESSING.
ENDIF.

ENDFORM.