Ещё один способ прочитать содержимое Excel-файла

Иногда просто удивительно, какой стоянкой велосипедов может быть SAP.

Есть в целом типичная задача — прочитать содержимое Excel-файла. И есть примерно двадцать семь способов её решить. В моём недавнем случае были ограничения: должно работать не только в SAP-GUI, должно уметь работать с несколькими листами, не должно требовать установки отдельных громоздких библиотек. Это отсекло большинство моих предыдущих подходов, и я отправился на поиски новых велосипедов.

И найденный велосипед имеет имя CL_FDT_XL_SPREADSHEET. Работать с ним проще простого:

Создаём экземпляр (необходимо отдать ему бинарный файл):

mo_excel = NEW #( document_name = '' 
                  xdocument     = ix_file ).

1 2	mo_excel = NEW #( document_name = '' xdocument = ix_file ).

Получаем список листов:

mo_excel->if_fdt_doc_spreadsheet~get_worksheet_names( 
  IMPORTING worksheet_names = sheets ).

1 2	mo_excel->if_fdt_doc_spreadsheet~get_worksheet_names( IMPORTING worksheet_names = sheets ).

Считываем содержимое нужного листа:

DATA lr_data TYPE REF TO data.
lr_data = mo_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( iv_name ).

1 2	DATA lr_data TYPE REF TO data. lr_data = mo_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( iv_name ).

На выходе получается таблица с колонками A, B, C и так далее. У всех колонок тип String.

Дальше уже дело техники, первый рабочий черновик обёртки:

class ZCL_EXCEL_READER definition
  public
  final
  create public .

public section.

  data MT_SHEETS type IF_FDT_DOC_SPREADSHEET=>T_WORKSHEET_NAMES read-only .
  constants MODE_BY_FIELDNAMES type CHAR1 value 'F' ##NO_TEXT.
  constants MODE_BY_ORDER type CHAR1 value 'O' ##NO_TEXT.

  methods CONSTRUCTOR
    importing
      !IX_FILE type XSTRING .
  methods LOAD_SHEET
    importing
      !IV_NAME type STRING
      !IV_ROW_HEADER type INT4 default 1
      !IV_READ_MODE type CHAR1 default 'F'
      !IV_ROW_DATA type INT4 default 2
      !IV_COL_DATA type INT4 default 1
    changing
      !CT_DATA type STANDARD TABLE .
protected section.

  methods LOAD_SHEET_BY_FIELDNAMES
    importing
      !IV_NAME type STRING
      !IV_ROW_HEADER type INT4 default 1
      !IV_ROW_DATA type INT4 default 2
      !IV_COL_DATA type INT4 default 1
    changing
      !CT_DATA type STANDARD TABLE .
private section.

  data MO_EXCEL type ref to CL_FDT_XL_SPREADSHEET .
ENDCLASS.



CLASS ZCL_EXCEL_READER IMPLEMENTATION.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_EXCEL_READER->CONSTRUCTOR
* +-------------------------------------------------------------------------------------------------+
* | [--->] IX_FILE                        TYPE        XSTRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD constructor.

  mo_excel = NEW #( document_name = ''
                    xdocument     = ix_file ).

  mo_excel->if_fdt_doc_spreadsheet~get_worksheet_names( 
    IMPORTING worksheet_names = mt_sheets ).


ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_EXCEL_READER->LOAD_SHEET
* +-------------------------------------------------------------------------------------------------+
* | [--->] IV_NAME                        TYPE        STRING
* | [--->] IV_ROW_HEADER                  TYPE        INT4 (default =1)
* | [--->] IV_READ_MODE                   TYPE        CHAR1 (default ='F')
* | [--->] IV_ROW_DATA                    TYPE        INT4 (default =2)
* | [--->] IV_COL_DATA                    TYPE        INT4 (default =1)
* | [<-->] CT_DATA                        TYPE        STANDARD TABLE
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD load_sheet.

  CASE iv_read_mode.
    WHEN mode_by_fieldnames.
      load_sheet_by_fieldnames(
        EXPORTING
          iv_name       = iv_name
          iv_row_header = iv_row_header
          iv_row_data   = iv_row_data
          iv_col_data   = iv_row_data
        CHANGING
          ct_data       = ct_data
      ).
    WHEN OTHERS.
      "Not implemented yet
  ENDCASE.

ENDMETHOD.


* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Protected Method ZCL_EXCEL_READER->LOAD_SHEET_BY_FIELDNAMES
* +-------------------------------------------------------------------------------------------------+
* | [--->] IV_NAME                        TYPE        STRING
* | [--->] IV_ROW_HEADER                  TYPE        INT4 (default =1)
* | [--->] IV_ROW_DATA                    TYPE        INT4 (default =2)
* | [--->] IV_COL_DATA                    TYPE        INT4 (default =1)
* | [<-->] CT_DATA                        TYPE        STANDARD TABLE
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD load_sheet_by_fieldnames.

  DATA lr_data TYPE REF TO data.
  lr_data = mo_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( iv_name ).

  FIELD-SYMBOLS <tab> TYPE STANDARD TABLE.
  FIELD-SYMBOLS <row> TYPE any.

  ASSIGN lr_data->* TO <tab>.

  IF lines( <tab> ) = 0.
    RETURN.
  ENDIF.

  DATA: lr_tgt_tabledescr TYPE REF TO cl_abap_tabledescr.
  lr_tgt_tabledescr ?= cl_abap_tabledescr=>describe_by_data( ct_data ).

  DATA: lr_tgt_structdescr TYPE REF TO cl_abap_structdescr.
  lr_tgt_structdescr ?= lr_tgt_tabledescr->get_table_line_type( ).

  DATA(lt_tgt_components) = lr_tgt_structdescr->get_components( ).

  DATA: lr_src_structdescr TYPE REF TO cl_abap_structdescr.
  lr_src_structdescr ?= cl_abap_structdescr=>describe_by_data( <tab>[ 1 ] ).
  DATA(lt_src_components) = lr_src_structdescr->get_components( ).

  TYPES: BEGIN OF ty_mapping,
           src_index     TYPE int4,
           src_name      TYPE string,
           tgt_index     TYPE int4,
           tgt_name      TYPE string,
           tgt_type_kind TYPE char1,
         END OF ty_mapping.
  DATA: lt_mapping TYPE STANDARD TABLE OF ty_mapping.

  READ TABLE <tab> INDEX iv_row_header ASSIGNING <row>.
  LOOP AT lt_tgt_components INTO DATA(ls_tgt).
    DATA(lv_tgt_index) = sy-tabix.


    DO lines( lt_src_components ) TIMES.
      DATA(lv_src_index) = sy-index.
      ASSIGN COMPONENT lv_src_index OF STRUCTURE <row> 
        TO FIELD-SYMBOL(<src_hdr_fld>).
      IF <src_hdr_fld> = ls_tgt-name.
        APPEND INITIAL LINE TO lt_mapping ASSIGNING FIELD-SYMBOL(<mapping>).
        <mapping>-src_index = lv_src_index.
        <mapping>-src_name  = <src_hdr_fld>.
        <mapping>-tgt_index = lv_tgt_index.
        <mapping>-tgt_name = ls_tgt-name.
        <mapping>-tgt_type_kind = ls_tgt-type->type_kind.
      ENDIF.
    ENDDO.
  ENDLOOP.

  LOOP AT <tab> ASSIGNING FIELD-SYMBOL(<src_row>).
    IF sy-tabix < iv_row_data.
      CONTINUE.
    ENDIF.

    FIELD-SYMBOLS <tgt_row> TYPE any.

    APPEND INITIAL LINE TO ct_data ASSIGNING <tgt_row>.

    LOOP AT lt_mapping INTO DATA(ls_mapping).
      ASSIGN COMPONENT ls_mapping-src_index OF STRUCTURE <src_row> 
        TO FIELD-SYMBOL(<src_fld>).
      ASSIGN COMPONENT ls_mapping-tgt_index OF STRUCTURE <tgt_row> 
        TO FIELD-SYMBOL(<tgt_fld>).

      IF ls_mapping-tgt_type_kind = 'D'.
        IF <src_fld> IS NOT INITIAL.
          TRY.
              "1999-12-31 => 19991231 
              <tgt_fld> = <src_fld>(4) && <src_fld>+5(2) && <src_fld>+8(2). 
            CATCH cx_sy_range_out_of_bounds.
          ENDTRY.
        ELSE.
        ENDIF.
      ELSE.
        <tgt_fld> = <src_fld>.
      ENDIF.
    ENDLOOP.

  ENDLOOP.

ENDMETHOD.
ENDCLASS.

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

class ZCL_EXCEL_READER definition

public

final

create public .

public section.

data MT_SHEETS type IF_FDT_DOC_SPREADSHEET=>T_WORKSHEET_NAMES read-only .

constants MODE_BY_FIELDNAMES type CHAR1 value 'F' ##NO_TEXT.

constants MODE_BY_ORDER type CHAR1 value 'O' ##NO_TEXT.

methods CONSTRUCTOR

importing

!IX_FILE type XSTRING .

methods LOAD_SHEET

importing

!IV_NAME type STRING

!IV_ROW_HEADER type INT4 default 1

!IV_READ_MODE type CHAR1 default 'F'

!IV_ROW_DATA type INT4 default 2

!IV_COL_DATA type INT4 default 1

changing

!CT_DATA type STANDARD TABLE .

protected section.

methods LOAD_SHEET_BY_FIELDNAMES

importing

!IV_NAME type STRING

!IV_ROW_HEADER type INT4 default 1

!IV_ROW_DATA type INT4 default 2

!IV_COL_DATA type INT4 default 1

changing

!CT_DATA type STANDARD TABLE .

private section.

data MO_EXCEL type ref to CL_FDT_XL_SPREADSHEET .

ENDCLASS.

CLASS ZCL_EXCEL_READER IMPLEMENTATION.

* <SIGNATURE>---------------------------------------------------------------------------------------+

* | Instance Public Method ZCL_EXCEL_READER->CONSTRUCTOR

* +-------------------------------------------------------------------------------------------------+

* | [--->] IX_FILE TYPE XSTRING

* +--------------------------------------------------------------------------------------</SIGNATURE>

METHOD constructor.

mo_excel = NEW #( document_name = ''

xdocument = ix_file ).

mo_excel->if_fdt_doc_spreadsheet~get_worksheet_names(

IMPORTING worksheet_names = mt_sheets ).

ENDMETHOD.

* <SIGNATURE>---------------------------------------------------------------------------------------+

* | Instance Public Method ZCL_EXCEL_READER->LOAD_SHEET

* +-------------------------------------------------------------------------------------------------+

* | [--->] IV_NAME TYPE STRING

* | [--->] IV_ROW_HEADER TYPE INT4 (default =1)

* | [--->] IV_READ_MODE TYPE CHAR1 (default ='F')

* | [--->] IV_ROW_DATA TYPE INT4 (default =2)

* | [--->] IV_COL_DATA TYPE INT4 (default =1)

* | [<-->] CT_DATA TYPE STANDARD TABLE

* +--------------------------------------------------------------------------------------</SIGNATURE>

METHOD load_sheet.

CASE iv_read_mode.

WHEN mode_by_fieldnames.

load_sheet_by_fieldnames(

EXPORTING

iv_name = iv_name

iv_row_header = iv_row_header

iv_row_data = iv_row_data

iv_col_data = iv_row_data

CHANGING

ct_data = ct_data

WHEN OTHERS.

"Not implemented yet

ENDCASE.

ENDMETHOD.

* <SIGNATURE>---------------------------------------------------------------------------------------+

* | Instance Protected Method ZCL_EXCEL_READER->LOAD_SHEET_BY_FIELDNAMES

* +-------------------------------------------------------------------------------------------------+

* | [--->] IV_NAME TYPE STRING

* | [--->] IV_ROW_HEADER TYPE INT4 (default =1)

* | [--->] IV_ROW_DATA TYPE INT4 (default =2)

* | [--->] IV_COL_DATA TYPE INT4 (default =1)

* | [<-->] CT_DATA TYPE STANDARD TABLE

* +--------------------------------------------------------------------------------------</SIGNATURE>

METHOD load_sheet_by_fieldnames.

DATA lr_data TYPE REF TO data.

lr_data = mo_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( iv_name ).

FIELD-SYMBOLS <tab> TYPE STANDARD TABLE.

FIELD-SYMBOLS <row> TYPE any.

ASSIGN lr_data->* TO <tab>.

IF lines( <tab> ) = 0.

RETURN.

ENDIF.

DATA: lr_tgt_tabledescr TYPE REF TO cl_abap_tabledescr.

lr_tgt_tabledescr ?= cl_abap_tabledescr=>describe_by_data( ct_data ).

DATA: lr_tgt_structdescr TYPE REF TO cl_abap_structdescr.

lr_tgt_structdescr ?= lr_tgt_tabledescr->get_table_line_type( ).

DATA(lt_tgt_components) = lr_tgt_structdescr->get_components( ).

DATA: lr_src_structdescr TYPE REF TO cl_abap_structdescr.

lr_src_structdescr ?= cl_abap_structdescr=>describe_by_data( <tab>[ 1 ] ).

DATA(lt_src_components) = lr_src_structdescr->get_components( ).

TYPES: BEGIN OF ty_mapping,

src_index TYPE int4,

src_name TYPE string,

tgt_index TYPE int4,

tgt_name TYPE string,

tgt_type_kind TYPE char1,

END OF ty_mapping.

DATA: lt_mapping TYPE STANDARD TABLE OF ty_mapping.

READ TABLE <tab> INDEX iv_row_header ASSIGNING <row>.

LOOP AT lt_tgt_components INTO DATA(ls_tgt).

DATA(lv_tgt_index) = sy-tabix.

DO lines( lt_src_components ) TIMES.

DATA(lv_src_index) = sy-index.

ASSIGN COMPONENT lv_src_index OF STRUCTURE <row>

TO FIELD-SYMBOL(<src_hdr_fld>).

IF <src_hdr_fld> = ls_tgt-name.

APPEND INITIAL LINE TO lt_mapping ASSIGNING FIELD-SYMBOL(<mapping>).

<mapping>-src_index = lv_src_index.

<mapping>-src_name = <src_hdr_fld>.

<mapping>-tgt_index = lv_tgt_index.

<mapping>-tgt_name = ls_tgt-name.

<mapping>-tgt_type_kind = ls_tgt-type->type_kind.

ENDIF.

ENDDO.

ENDLOOP.

LOOP AT <tab> ASSIGNING FIELD-SYMBOL(<src_row>).

IF sy-tabix < iv_row_data.

CONTINUE.

ENDIF.

FIELD-SYMBOLS <tgt_row> TYPE any.

APPEND INITIAL LINE TO ct_data ASSIGNING <tgt_row>.

LOOP AT lt_mapping INTO DATA(ls_mapping).

ASSIGN COMPONENT ls_mapping-src_index OF STRUCTURE <src_row>

TO FIELD-SYMBOL(<src_fld>).

ASSIGN COMPONENT ls_mapping-tgt_index OF STRUCTURE <tgt_row>

TO FIELD-SYMBOL(<tgt_fld>).

IF ls_mapping-tgt_type_kind = 'D'.

IF <src_fld> IS NOT INITIAL.

TRY.

"1999-12-31 => 19991231

<tgt_fld> = <src_fld>(4) && <src_fld>+5(2) && <src_fld>+8(2).

CATCH cx_sy_range_out_of_bounds.

ENDTRY.

ELSE.

ENDIF.

ELSE.

<tgt_fld> = <src_fld>.

ENDIF.

ENDLOOP.

ENDMETHOD.

ENDCLASS.

И использование обёртки сводится к такому коду:

DATA(lo_excel) = NEW zcl_excel_reader( lx_file ).

TYPES: BEGIN OF ty_master,
         id        TYPE char10,
         name      TYPE string,
       END OF ty_master.

TYPES: BEGIN OF ty_detail,
         id        TYPE char10,
         ldate     TYPE d,
         amount    TYPE wertv12,
       END OF ty_detail.

DATA: lt_master TYPE STANDARD TABLE OF ty_master.
DATA: lt_detail TYPE STANDARD TABLE OF ty_detail.

lo_excel->load_sheet( EXPORTING iv_name = 'List1'
                                iv_read_mode = lo_excel->mode_by_fieldnames
                      CHANGING ct_data = lt_master ).

lo_excel->load_sheet( EXPORTING iv_name = 'List2'
                                iv_read_mode = lo_excel->mode_by_fieldnames
                      CHANGING ct_data = lt_detail ).

DATA(lo_excel) = NEW zcl_excel_reader( lx_file ).

TYPES: BEGIN OF ty_master,

id TYPE char10,

name TYPE string,

END OF ty_master.

TYPES: BEGIN OF ty_detail,

id TYPE char10,

ldate TYPE d,

amount TYPE wertv12,

END OF ty_detail.

DATA: lt_master TYPE STANDARD TABLE OF ty_master.

DATA: lt_detail TYPE STANDARD TABLE OF ty_detail.

lo_excel->load_sheet( EXPORTING iv_name = 'List1'

iv_read_mode = lo_excel->mode_by_fieldnames

CHANGING ct_data = lt_master ).

lo_excel->load_sheet( EXPORTING iv_name = 'List2'

iv_read_mode = lo_excel->mode_by_fieldnames

CHANGING ct_data = lt_detail ).

Поживём-увидим, может пригодится в продуктивном использовании.

Иван Болховитинов

Ещё один способ прочитать содержимое Excel-файла

Добавить комментарий Отменить ответ