1 *&---------------------------------------------------------------------*
  2 *& Report ZCH004
  3 *&---------------------------------------------------------------------*
  4 *&
  5 *&---------------------------------------------------------------------*
  6 REPORT zch004.
  7 PARAMETERS:p_table TYPE dd02l-tabname DEFAULT 'SPFLI'.
  8 FIELD-SYMBOLS:<dyn_table> TYPE STANDARD TABLE .
  9 CONSTANTS:gv_lines TYPE i VALUE 10.
 10 DATA:gv_absfile TYPE string.
 11 DATA:gv_abspath TYPE string VALUE '/tmp/chenhui/'.
 12 DATA:gv_xstring TYPE xstring.
 13 
 14 PERFORM frm_check_table.
 15 PERFORM frm_mkdir.
 16 PERFORM frm_create_dynamic_table.
 17 PERFORM frm_get_data.
 18 PERFORM frm_upload_file TABLES <dyn_table>.
 19 PERFORM frm_download_file TABLES <dyn_table>.
 20 PERFORM frm_remove_file.
 21 PERFORM frm_deltete_dir.
 22 
 23 FORM frm_check_table.
 24   SELECT SINGLE * INTO @DATA(lv_subrc) FROM dd02l WHERE tabname = @p_table.
 25   IF sy-subrc <> 0.
 26     MESSAGE s001(00) WITH '底表不存在，请检查!'.
 27     STOP.
 28   ENDIF.
 29 
 30 ENDFORM.
 31 
 32 FORM frm_mkdir.
 33   DATA: BEGIN OF tab OCCURS 0,
 34           line(200),
 35         END OF tab.
 36   DATA:command(100).
 37   command = `mkdir -p ` && gv_abspath.
 38   CALL 'SYSTEM' ID 'COMMAND' FIELD command
 39                  ID 'TAB'     FIELD tab-*sys*.
 40   REPLACE ALL OCCURRENCES OF '$' IN gv_abspath WITH p_table.
 41   gv_absfile = |{ gv_abspath }{ p_table }_{ sy-datum }_{ sy-uzeit }.xlsx|.
 42 ENDFORM.
 43 
 44 FORM frm_get_data.
 45   SELECT * FROM (p_table) UP TO @gv_lines ROWS INTO TABLE @<dyn_table>.
 46 ENDFORM.
 47 
 48 FORM frm_create_dynamic_table.
 49   DATA:dy_table TYPE REF TO data.
 50   CREATE DATA dy_table TYPE TABLE OF (p_table).
 51   ASSIGN dy_table->* TO <dyn_table>.
 52 ENDFORM.
 53 
 54 FORM frm_upload_file TABLES data.
 55   TRY.
 56       DATA tablestructure TYPE REF TO cl_abap_structdescr.
 57       DATA:ls_ref TYPE REF TO data.
 58       CREATE DATA ls_ref LIKE LINE OF data.
 59       tablestructure ?= cl_abap_typedescr=>describe_by_data_ref( ls_ref ).
 60       DATA(lo_handling) = cl_ehfnd_xlsx=>get_instance( ).
 61       DATA(lo_document) = lo_handling->create_doc( ).
 62       DATA(lo_sheets) = lo_document->get_sheets( ).
 63       DATA(lo_sheet) = lo_document->get_sheet_by_id( lo_sheets[ 1 ]-sheet_id ).
 64       LOOP AT tablestructure->get_ddic_field_list( ) ASSIGNING FIELD-SYMBOL(<field>).
 65         DATA(lv_column) = sy-tabix.
 66         DATA(lv_rowumn) = 1.
 67         lo_sheet->set_cell_content( iv_row = lv_rowumn iv_column = lv_column iv_value = <field>-reptext ).
 68         lv_rowumn = lv_rowumn + 1.
 69         lo_sheet->set_cell_content( iv_row = lv_rowumn iv_column = lv_column iv_value = <field>-fieldname ).
 70         LOOP AT data ASSIGNING FIELD-SYMBOL(<data>).
 71           LOOP AT tablestructure->components REFERENCE INTO DATA(component) WHERE name = <field>-fieldname.
 72             lv_rowumn = lv_rowumn + 1.
 73             ASSIGN COMPONENT component->name OF STRUCTURE <data> TO FIELD-SYMBOL(<value>).
 74             lo_sheet->set_cell_content( iv_row = lv_rowumn iv_column = lv_column iv_value = <value> ).
 75           ENDLOOP.
 76         ENDLOOP.
 77       ENDLOOP.
 78 
 79       gv_xstring = lo_document->save( ).
 80       FREE:lo_sheet,lo_sheets,lo_document,lo_handling.
 81 
 82       OPEN DATASET gv_absfile FOR OUTPUT IN BINARY MODE.
 83       TRANSFER gv_xstring TO gv_absfile.
 84       CLOSE DATASET gv_absfile.
 85     CATCH cx_root INTO DATA(lo_error).
 86       WRITE:lo_error->get_text( ).
 87   ENDTRY.
 88 ENDFORM.
 89 
 90 FORM frm_download_file TABLES data.
 91   REFRESH:data.
 92   CLEAR:gv_xstring.
 93   TRY.
 94       DATA tablestructure TYPE REF TO cl_abap_structdescr.
 95       DATA:ls_ref TYPE REF TO data.
 96       CREATE DATA ls_ref LIKE LINE OF data.
 97       tablestructure ?= cl_abap_typedescr=>describe_by_data_ref( ls_ref ).
 98 
 99       OPEN DATASET gv_absfile FOR INPUT IN BINARY MODE.
100       READ DATASET gv_absfile INTO gv_xstring.
101       CLOSE DATASET gv_absfile.
102       DELETE DATASET gv_absfile.
103       DATA(lo_handling) = cl_ehfnd_xlsx=>get_instance( ).
104       DATA(lo_document) = lo_handling->load_doc( gv_xstring ).
105       DATA(lo_sheets) = lo_document->get_sheets( ).
106       DATA(lo_sheet) = lo_document->get_sheet_by_id( lo_sheets[ 1 ]-sheet_id ).
107 
108       DO gv_lines TIMES.
109         DATA(lv_row) = sy-index + 2.
110         APPEND INITIAL LINE TO data ASSIGNING FIELD-SYMBOL(<data>).
111         LOOP AT tablestructure->get_ddic_field_list( ) ASSIGNING FIELD-SYMBOL(<field>).
112           DATA(lv_column) = sy-tabix.
113           IF lo_sheet->has_cell_content( iv_row = lv_row iv_column = lv_column ).
114             LOOP AT tablestructure->components REFERENCE INTO DATA(component) WHERE name = <field>-fieldname.
115               ASSIGN COMPONENT component->name OF STRUCTURE <data> TO FIELD-SYMBOL(<value>).
116               <value> = lo_sheet->get_cell_content( iv_row = lv_row iv_column = lv_column ).
117             ENDLOOP.
118           ENDIF.
119         ENDLOOP.
120       ENDDO.
121       FREE:lo_sheet,lo_sheets,lo_document,lo_handling.
122     CATCH cx_root INTO DATA(lo_error).
123       WRITE:lo_error->get_text( ).
124   ENDTRY.
125 ENDFORM.
126 
127 FORM frm_remove_file.
128 
129   CHECK 1 = 2.
130   "方法一
131   DELETE DATASET gv_absfile.
132 
133   CHECK 1 = 2.
134   "方法二
135   DATA: BEGIN OF tab OCCURS 0,
136           line(200),
137         END OF tab.
138   DATA:command(100).
139   command = `rm -f ` && gv_absfile.
140   CALL 'SYSTEM' ID 'COMMAND' FIELD command
141                  ID 'TAB'     FIELD tab-*sys*.
142 ENDFORM.
143 
144 FORM frm_deltete_dir.
145   DATA: BEGIN OF tab OCCURS 0,
146           line(200),
147         END OF tab.
148   DATA:command(100).
149   command = `rm -rf ` && gv_abspath.
150   CALL 'SYSTEM' ID 'COMMAND' FIELD command
151                  ID 'TAB'     FIELD tab-*sys*.
152 ENDFORM.
View Code