class ZCLXLS_LOADER definition
  public
  final
  create public .

*"* public components of class ZCLXLS_LOADER
*"* do not include other source files here!!!

public section.

  methods CONSTRUCTOR
    importing
      !_LOG type ref to ZCL_APPLICATION_LOG . "//Тут отдельное  спасибо  C.Королеву

  methods LOAD
    importing
      !_FN type STRING default 'C:\LOAD_NSI\HSI___.xls '
      !_DATA_RANGE type TEXT128 default 'DATA'
    exporting
      !_TABLE type TABLE
      !_FAILED type CHAR1 .

METHOD load.
  CONSTANTS:
   macro_name TYPE char128 VALUE 'RUN'.

  DATA txt_file TYPE char128.
  DATA str TYPE string.

  CALL METHOD cl_gui_frontend_services=>get_sapgui_workdir
    CHANGING
      sapworkdir   = str "m_tmp_dir
    EXCEPTIONS
      cntl_error   = 1
      error_no_gui = 2
      OTHERS       = 3.

  CONCATENATE str  '\~sap_ozm.txt' INTO txt_file.

  export2txt(
    EXPORTING
        _xls_file = _fn
        _macros_name  = macro_name
        _delim  = 9
        _txt_file = txt_file
        _data_range = _data_range ).

  DATA tab TYPE TABLE OF ty_txt_rec.

  str = txt_file.
  CALL FUNCTION 'GUI_UPLOAD'
    EXPORTING
      filename            = str
      has_field_separator = 'X'
    TABLES
      data_tab            = tab.

  DATA i_tmp TYPE i.
  cl_gui_frontend_services=>file_delete(
                                   EXPORTING filename = str
                                   CHANGING  rc = i_tmp ).




  DATA prec TYPE REF TO ty_txt_rec.
  DATA pdata TYPE REF TO data.
  DATA row TYPE i.
  row = 9999.

  DATA failed TYPE char1.

  FIELD-SYMBOLS: <field> TYPE ANY.
  LOOP AT tab REFERENCE INTO prec.
    CONDENSE: prec->field,
              prec->conv,
              prec->val.

    TRANSLATE prec->conv TO UPPER CASE.
    TRANSLATE prec->field TO UPPER CASE.

    IF row NE prec->row.
      APPEND INITIAL LINE TO _table REFERENCE INTO pdata.
      row = prec->row.
    ENDIF.

    ASSIGN pdata->(prec->field) TO <field>.
    CHECK sy-subrc EQ 0.
    convert_data(
                  EXPORTING
                   _type = prec->conv
                   _in = prec->val
                  IMPORTING
                  _failed = failed
                  _out = <field> ).
    IF NOT failed IS INITIAL.
      _failed = 'X'.
    ENDIF.

  ENDLOOP.

ENDMETHOD.

method EXPORT2TXT.
  DATA: app TYPE ole2_object,
          wbook_s TYPE ole2_object,
          wb TYPE ole2_object.
  CREATE OBJECT app 'excel.application'.

  SET PROPERTY OF app 'Visible' = 0.
  SET PROPERTY OF app 'ScreenUpdating' = 0.
  SET PROPERTY OF app 'DisplayAlerts' = 0.

  CALL METHOD OF app 'Workbooks' = wbook_s.
  CALL METHOD OF wbook_s 'Open' = wb EXPORTING #1 = _xls_file.
"Sub run(range_name As String, fn As String, delim As Long)
  CALL METHOD OF app 'Run' EXPORTING
        #1 = _macros_name
        #2 = _data_range
        #3 = _txt_file
        #4 = _delim.

CALL METHOD OF app 'Quit'.
  FREE: wbook_s, app, wb.
endmethod.

Sub run(range_name As String, fn As String, delim As Long)
   
Dim Range  As Range

Application.GoTo reference:=range_name
Set Range = Selection

Dim n_rows As Integer
Dim n_coll As Integer

n_rows = Range.Rows.Count
n_coll = Range.Columns.Count

Open fn For Output As #1

For i = 4 To n_rows
For j = 1 To n_coll
    If Range.Cells(3, j) = "X" _
    Or Range.Cells(3, j) = "x" _
    Or Range.Cells(3, j) = "Х" _
    Or Range.Cells(3, j) = "х" _
    Then
     Print #1, i - 3; Chr(delim); _
               Range.Cells(1, j); Chr(delim); _
               Range.Cells(2, j); Chr(delim); _
               Range.Cells(i, j)
    End If
Next
Next

Close #1    ' Close file.

TYPES : BEGIN OF type_xldata,
string(800),
END OF type_xldata.

data: type_t_xldata TYPE STANDARD TABLE OF type_xldata.

CALL FUNCTION 'FAA_FILE_UPLOAD_EXCEL'
EXPORTING
i_filename = 'D:\2.xls'
I_DELIMITER = ';'
TABLES
et_filecontent = type_t_xldata
EXCEPTIONS
error_accessing_file = 1
OTHERS = 2.

if sy-subrc <> 0.
** MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
**         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
endif.