ABAP简单代码实现上载Excel多Sheet页

  1 class ZCL_EXCEL_TOOLS definition
  2   public
  3   final
  4   create public .
  5 
  6 public section.
  7 
  8   methods UPLOAD
  9     importing
 10       !FILE type STRING
 11     returning
 12       value(DATA) type ref to DATA .
 13   methods TEST .
 14 protected section.
 15 private section.
 16 ENDCLASS.
 17 
 18 
 19 
 20 CLASS ZCL_EXCEL_TOOLS IMPLEMENTATION.
 21 
 22 
 23 * <SIGNATURE>---------------------------------------------------------------------------------------+
 24 * | Instance Public Method ZCL_EXCEL_TOOLS->TEST
 25 * +-------------------------------------------------------------------------------------------------+
 26 * +--------------------------------------------------------------------------------------</SIGNATURE>
 27   METHOD test.
 28     DATA(data) = upload( EXPORTING file = conv STRING( 'C:\xxxx.xlsx' ) ).
 29     ASSIGN data->* TO FIELD-SYMBOL(<fs_data>).
 30     BREAK-POINT.
 31   ENDMETHOD.
 32 
 33 
 34 * <SIGNATURE>---------------------------------------------------------------------------------------+
 35 * | Instance Public Method ZCL_EXCEL_TOOLS->UPLOAD
 36 * +-------------------------------------------------------------------------------------------------+
 37 * | [--->] FILE                           TYPE        STRING
 38 * | [<-()] DATA                           TYPE REF TO DATA
 39 * +--------------------------------------------------------------------------------------</SIGNATURE>
 40   METHOD upload.
 41     FIELD-SYMBOLS:<ft_table>  TYPE STANDARD TABLE.
 42     TYPES:BEGIN OF ty_line,
 43             line TYPE x LENGTH 1024,
 44           END OF ty_line.
 45     DATA:lt_xtab TYPE TABLE OF ty_line.
 46     DATA:lv_max_col TYPE i.
 47     DATA lt_comp TYPE abap_component_tab.
 48     DATA lt_comps TYPE abap_component_tab.
 49 
 50     TRY.
 51         DATA(lv_exist) = cl_gui_frontend_services=>file_exist( file ).
 52         CHECK lv_exist = abap_true.
 53         cl_gui_frontend_services=>gui_upload( EXPORTING filename = file filetype = 'BIN' CHANGING data_tab = lt_xtab ).
 54         DATA(lv_xstring) = cl_bcs_convert=>xtab_to_xstring( lt_xtab ).
 55         DATA(lo_documents) = cl_ehfnd_xlsx=>get_instance( )->load_doc( lv_xstring )."
 56         DATA(lt_sheets) = lo_documents->get_sheets( ).
 57 
 58         LOOP AT lt_sheets ASSIGNING FIELD-SYMBOL(<fs_sheet>).
 59           DATA(lo_sheet) = lo_documents->get_sheet_by_id( <fs_sheet>-sheet_id ).
 60           CLEAR:lv_max_col.
 61           DO lo_sheet->get_last_row_number( ) TIMES.
 62             DATA(lv_row) = sy-index.
 63             DATA(lv_col) = lo_sheet->get_last_column_number_in_row( iv_row = lv_row ).
 64             lv_max_col = nmax( val1 = lv_max_col val2 = lv_col ).
 65           ENDDO.
 66 
 67           CLEAR:lt_comp.
 68           DO lv_max_col TIMES.
 69             APPEND INITIAL LINE TO lt_comp ASSIGNING FIELD-SYMBOL(<fs_comp>).
 70             <fs_comp>-name = |COL{ sy-index }|.
 71             <fs_comp>-type ?= cl_abap_typedescr=>describe_by_data_ref( NEW string( ) ).
 72           ENDDO.
 73 
 74           CHECK lt_comp[] IS NOT INITIAL.
 75           DATA(lo_table)  = cl_abap_tabledescr=>create( cl_abap_structdescr=>create( p_components = lt_comp p_strict = '' )  ).
 76           CREATE:DATA data TYPE HANDLE lo_table.
 77           ASSIGN:data->* TO <ft_table>.
 78 
 79           APPEND INITIAL LINE TO lt_comps ASSIGNING FIELD-SYMBOL(<fs_comps>).
 80           <fs_comps>-name = |SheetName_{ <fs_sheet>-name }|.
 81           <fs_comps>-type ?= cl_abap_typedescr=>describe_by_data( <ft_table> ).
 82 
 83         ENDLOOP.
 84 
 85         CHECK lt_comps[] IS NOT INITIAL.
 86         DATA(lo_struct)  = cl_abap_structdescr=>create( p_components = lt_comps ).
 87         CREATE:DATA data TYPE HANDLE lo_struct.
 88         ASSIGN:data->* TO FIELD-SYMBOL(<fs_data>).
 89 
 90         LOOP AT lt_sheets ASSIGNING <fs_sheet>.
 91           lo_sheet = lo_documents->get_sheet_by_id( <fs_sheet>-sheet_id ).
 92           ASSIGN COMPONENT |SheetName_{ <fs_sheet>-name }| OF STRUCTURE <fs_data> TO <ft_table>.
 93           DO lo_sheet->get_last_row_number( ) TIMES.
 94             lv_row = sy-index.
 95             APPEND INITIAL LINE TO <ft_table> ASSIGNING FIELD-SYMBOL(<fs_table>).
 96             DO lo_sheet->get_last_column_number_in_row( iv_row = lv_row ) TIMES.
 97               lv_col = sy-index.
 98               ASSIGN COMPONENT |COL{ sy-index }| OF STRUCTURE <fs_table> TO FIELD-SYMBOL(<fs_value>).
 99               "CHECK xlsx_sheet->has_cell_content( iv_row = lv_row iv_column = lv_col ).
100               <fs_value> = lo_sheet->get_cell_content( iv_row = lv_row iv_column = lv_col ).
101             ENDDO.
102 
103           ENDDO.
104 
105         ENDLOOP.
106         "DATA(filecontent) = LO_DOCUMENTS->save( ).
107 
108       CATCH cx_root INTO DATA(lo_error).
109         DATA(lv_msg) = lo_error->get_text( ).
110         BREAK-POINT.
111     ENDTRY.
112 
113     FREE:lo_documents,lo_sheet.
114   ENDMETHOD.
115 ENDCLASS.

如图：