DATA  ty_t_itab   TYPE alsmex_tabline   OCCURS 0.
DATA  TEMP TYPE alsmex_tabline.
DATA: X1 TYPE I VALUE 1,
      Y1 TYPE I VALUE 1,
      X2 TYPE I VALUE 1,
      Y2 TYPE I VALUE 50000.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
     EXPORTING
          FILENAME                = P_FILE
          I_BEGIN_COL             = X1
          I_BEGIN_ROW             = Y1
          I_END_COL               = X2
          I_END_ROW               = Y2
     TABLES
          INTERN                  = ty_t_itab
EXCEPTIONS
   INCONSISTENT_PARAMETERS       = 1
   UPLOAD_OLE                    = 2
   OTHERS                        = 3.

IF SY-SUBRC <> 0.
  MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
          WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.

LOOP AT ty_t_itab INTO TEMP.
  IF TEMP-VALUE NE SPACE.
    ITAB-NAME = TEMP-VALUE.
    APPEND ITAB.
  ENDIF.
ENDLOOP.

  LOOP AT xl_itab.
    CASE xl_itab-COL.
      WHEN 1.
        it_input-N_INV = xl_itab-VALUE.
      WHEN 2.
        it_input-DMBTR = xl_itab-VALUE.
      WHEN 3.
        it_input-AUFNV = xl_itab-VALUE.
      WHEN 4.
        it_input-AUFNL = xl_itab-VALUE.
        APPEND it_input.
    ENDCASE.
  ENDLOOP.

function [b]z_excel_upload_to_inttab[/b].
*"----------------------------------------------------------------------
*"*"Локальный интерфейс:
*"  IMPORTING
*"     REFERENCE([b]P_FILENAME[/b]) LIKE  RLGRAP-FILENAME
*"  EXPORTING
*"     REFERENCE(I_SUBRC) LIKE  SYST-SUBRC
*"  TABLES
*"      [b]P_TAB[/b]
*"----------------------------------------------------------------------
  data: l_intern type alsmex_tabline occurs 0 with header line,
        l_index type i,
        l_start_col type i value '1',
        l_start_row type i value '1',
        l_end_col type i value '256',
        l_end_row type i value '65536'.

  field-symbols : <fs>.

  call function [b]'ALSM_EXCEL_TO_INTERNAL_TABLE'[/b]
      exporting
             filename = [b]p_filename[/b]
             i_begin_col = l_start_col
             i_begin_row = l_start_row
             i_end_col = l_end_col
             i_end_row = l_end_row
         tables
             intern = [b]l_intern[/b]
     exceptions
             inconsistent_parameters = 1
             upload_ole = 2
             others = 3.

  i_subrc = sy-subrc.
  if sy-subrc > 0.
     message i000(zm) with 'Ошибка при открытии файла ... RC =' sy-subrc.
     exit.
  endif.

  if l_intern[] is initial.
     message i000(zm) with 'Данные не загружены!'.
     exit.
  else.
     sort l_intern by row col.
     loop at [b]l_intern[/b].
        move l_intern-col to l_index.
        assign component l_index of structure [b]p_tab [/b]to <fs>.
        move l_intern-value to <fs>.
        at end of row.
           append [b]p_tab[/b].
           clear p_tab.
        endat.
     endloop.
  endif.

endfunction.

CALL METHOD  OF application    'ActiveWorkbook' = ActiveWorkbook.
* выбираем второй лист
CALL METHOD  OF ActiveWorkbook 'Worksheets' = worksheet
   EXPORTING #1 = 2.

FUNCTION Z_EXCEL_LOAD.
*"----------------------------------------------------------------------
*"*"Локальный интерфейс:
*"  IMPORTING
*"     REFERENCE(FILE_NAME) TYPE  RLGRAP-FILENAME
*"  TABLES
*"      IT_DATA
*"  EXCEPTIONS
*"      ERROR
*"----------------------------------------------------------------------

  Data:
    Begin of Char_Tab,
      X type X value '09',
    End of Char_Tab,
    App type OLE2_OBJECT,
    Wbs type OLE2_OBJECT,
    Wbk type OLE2_OBJECT,
    FileTxt type RLGRAP-FILENAME,
    it_StringData type standard table of String
      with header line,
    it_Header type standard table of String
      with header line,
    it_Line type standard table of String
      with header line,
    TempDir    type RLGRAP-FILENAME,
    Type_Fld,
    Error type sy-subrc.

  Field-symbols: <Fld>.

  CALL FUNCTION 'GUI_GET_DESKTOP_INFO'
       EXPORTING
            TYPE   = 4  "TmpDir
       CHANGING
            RETURN = TempDir.

  Concatenate TempDir '\' sy-repid '-' sy-TIMLO '.txt'
    into FileTxt.

  Create object App 'Excel.Application'.
  Set property of App 'DisplayAlerts' = 0.
  Call method of App 'Workbooks' = Wbs.
  Call method of Wbs 'Open'
    exporting #1 = File_Name.
  Call method of App 'ActiveWorkbook' = Wbk.
  Call method of Wbk 'SaveAs'
    exporting #1 = FileTxt
              #2 = -4158. "xlText
  Call method of App 'Quit'.
  Free object App.

  If sy-subrc = 0.
    Data: FileStr type string.
    FileStr = FileTxt.
    Call function 'GUI_UPLOAD'
         EXPORTING
              FILENAME = FileStr
         TABLES
              DATA_TAB = it_StringData
         EXCEPTIONS
              OTHERS   = 17.

    If sy-subrc = 0.
      Read table it_StringData index 1.
      If sy-subrc = 0.
        Split it_StringData at Char_Tab into table it_Header.

        Loop at it_StringData from 2.
          Split it_StringData at Char_Tab into table it_Line.

          Loop at it_Header.
            Assign component it_Header of structure it_Data to <Fld>.
            If sy-subrc = 0.
              Read table it_Line index sy-tabix.
              Describe field <Fld> type Type_Fld.
              If Type_Fld <> 'D'.
                <Fld> = it_Line.
              Else.
                Data: Day(2), Month(2), Year(4).
                Split it_Line at '/' into Month Day Year.
                <Fld>(4)   = Year.
                <Fld>+4(2) = Month.
                <Fld>+6(2) = Day.
              EndIf.
            EndIf.
          EndLoop.
          If not it_Data is initial.
            Append it_Data.
          EndIf.
        EndLoop.
      Else.
        Error = 2.
      EndIf.
    Else.
      Error = 1.
    EndIf.
  Else.
    Error = 1.
  EndIf.

  Call function 'GUI_DELETE_FILE'
       EXPORTING
            FILE_NAME = FileTxt
       EXCEPTIONS
            others    = 1.

  If Error <> 0.
    Raise Error.
  EndIf.

ENDFUNCTION.

              DESCRIBE FIELD <fld> TYPE type_fld.
              IF type_fld <> 'D'.
                <fld> = it_line.
              ELSE.
                Data: Day(2), Month(2), Year(4).
                Split it_Line at '/' into Month Day Year.
                <Fld>(4)   = Year.
                <Fld>+4(2) = Month.
                <Fld>+6(2) = Day.
              ENDIF.

              DESCRIBE FIELD <fld> TYPE type_fld.
              IF type_fld <> 'D'.
                <fld> = it_line.
              ELSE.
                DATA: day(2), month(2), year(4).

                FIND '/' IN it_line.
                IF sy-subrc EQ 0.
                  SPLIT it_line AT '/' INTO month day year.
                ENDIF.

                FIND '.' IN it_line.
                IF sy-subrc EQ 0.
                  SPLIT it_line AT '.' INTO day month year.
                ENDIF.

                <fld>(4)   = year.
                <fld>+4(2) = month.
                <fld>+6(2) = day.

              ENDIF.

FUNCTION Z_EXCEL_LOAD.
*"----------------------------------------------------------------------
*"*"Локальный интерфейс:
*"  IMPORTING
*"     VALUE(FILE_NAME) TYPE  RLGRAP-FILENAME
*"     VALUE(SHEET_NAME) TYPE  CHAR30 OPTIONAL
*"     VALUE(HEADER_ROW) TYPE  SY-TABIX DEFAULT 1
*"     VALUE(DECIMAL_SEPARATOR) TYPE  CHAR1 DEFAULT ''
*"     VALUE(DATE_SEPARATOR) TYPE  CHAR1 DEFAULT ''
*"     VALUE(DATE_FORMAT) TYPE  CHAR10 DEFAULT ''
*"  TABLES
*"      IT_DATA
*"  EXCEPTIONS
*"      ERROR
*"----------------------------------------------------------------------

  Data:
    Char_Tab,
    X_Tab type X value '09',
    App type OLE2_OBJECT,
    Wbs type OLE2_OBJECT,
    Wbk type OLE2_OBJECT,
    Wsh type OLE2_OBJECT,
    FileTxt type RLGRAP-FILENAME,
    it_StringData type standard table of String
      with header line,
    it_Header type standard table of String
      with header line,
    it_Line type standard table of String
      with header line,
    TempDir    type RLGRAP-FILENAME,
    Type_Fld,
    NextRow type sy-tabix,
    Error type sy-subrc,
    Begin of Excel_Parameters,
     DECIMAL_SEPARATOR,
     DATE_SEPARATOR,
     DATE_FORMAT(10),
    End of Excel_Parameters,
    Date1(4),
    Date2(4),
    Date3(4),
    OLE_subrc type sy-subrc,
    CHAR_A value 'A',
    CHAR_B value 'B'.

  Field-symbols:
    <Fld>,
    <Day>,
    <Month>,
    <Year>.
  Field-symbols:
    <X>, <EI>.

*  Assign Char_Tab to <Fld> type 'X'.
*  <Fld> = X_Tab.

  Assign CHAR_A to <X>  type 'X'.
  Assign CHAR_B to <EI> type 'X'.
  <EI> = <EI> - <X>.
  Assign CHAR_TAB to <X> type 'X'.
  Clear <X>.
  <X> = X_TAB * <EI>.


  CALL FUNCTION 'GUI_GET_DESKTOP_INFO'
    EXPORTING
      TYPE   = 4  "TmpDir
    CHANGING
      RETURN = TempDir.

  Concatenate TempDir '\' sy-repid '-' sy-TIMLO '.txt'
    into FileTxt.

  Create object App 'Excel.Application'.
  Set property of App 'DisplayAlerts' = 0.
  Call method of App 'Workbooks' = Wbs.
  Call method of Wbs 'Open'
    EXPORTING
      #1 = File_Name.
  Call method of App 'ActiveWorkbook' = Wbk.
  If not SHEET_NAME is initial.
    Call method of Wbk 'WorkSheets' = Wsh
      EXPORTING
        #1 = SHEET_NAME.
    Call method of Wsh 'Select'.
  EndIf.
  Call method of Wbk 'SaveAs'
    EXPORTING
      #1  = FileTxt
      #2  = -4158 "xlText
      #3  = ''
      #4  = ''
      #5  = 0
      #6  = 0
      #7  = 0
      #8  = 0
      #9  = 0
      #10 = 0
      #11 = 0
      #12 = 1.
  Call method of App 'Quit'.
  OLE_subrc = sy-subrc.

  Free object: Wsh, Wbk, Wbs, App.

  If OLE_subrc = 0.
    Data: FileStr type string.
    FileStr = FileTxt.
    Call function 'GUI_UPLOAD'
      EXPORTING
        FILENAME = FileStr
      TABLES
        DATA_TAB = it_StringData
      EXCEPTIONS
        OTHERS   = 17.

    If sy-subrc = 0.
      Call function 'ZGET_EXCEL_PARAMETERS'
        IMPORTING
          DECIMAL_SEPARATOR = Excel_Parameters-DECIMAL_SEPARATOR
          DATE_SEPARATOR    = Excel_Parameters-DATE_SEPARATOR
          DATE_FORMAT       = Excel_Parameters-DATE_FORMAT.

      If DECIMAL_SEPARATOR is initial.
        DECIMAL_SEPARATOR = Excel_Parameters-DECIMAL_SEPARATOR.
      EndIf.

      If DATE_SEPARATOR is initial.
        DATE_SEPARATOR = Excel_Parameters-DATE_SEPARATOR.
      EndIf.

      If DATE_FORMAT is initial.
        DATE_FORMAT = Excel_Parameters-DATE_FORMAT.
      EndIf.

      Translate DATE_FORMAT to upper case.
      Split DATE_FORMAT at DATE_SEPARATOR into Date1 Date2 Date3.
      Case 'DD'.
        when Date1(2).
          Assign Date1 to <Day>.
        when Date2(2).
          Assign Date2 to <Day>.
        when Date3(2).
          Assign Date3 to <Day>.
      EndCase.

      Case 'MM'.
        when Date1(2).
          Assign Date1 to <Month>.
        when Date2(2).
          Assign Date2 to <Month>.
        when Date3(2).
          Assign Date3 to <Month>.
      EndCase.

      Case 'YY'.
        when Date1(2).
          Assign Date1 to <Year>.
        when Date2(2).
          Assign Date2 to <Year>.
        when Date3(2).
          Assign Date3 to <Year>.
      EndCase.

      Read table it_StringData index HEADER_ROW.
      If sy-subrc = 0.
        Split it_StringData at Char_Tab into table it_Header.

        NextRow = HEADER_ROW + 1.
        Loop at it_StringData from NextRow.
          Split it_StringData at Char_Tab into table it_Line.

          Clear it_Data.
          Loop at it_Header.
            Assign component it_Header of structure it_Data to <Fld>.
            If sy-subrc = 0.
              Read table it_Line index sy-tabix.
              If sy-subrc = 0.
                Describe field <Fld> type Type_Fld.
                Case Type_Fld.
                  when 'D'.
                    Data: Day(2), Month(2), Year(4).
                    Split it_Line at DATE_SEPARATOR into Date1 Date2 Date3.

                    If <Year> < 1900.
                      If <Year> > 53.
                        Concatenate '19' <Year> into <Year>.
                      Else.
                        Concatenate '20' <Year> into <Year>.
                      EndIf.
                    EndIf.

                    <Fld>(4)   = <Year>.
                    <Fld>+4(2) = <Month>.
                    <Fld>+6(2) = <Day>.
                  when 'P'.
                    Replace DECIMAL_SEPARATOR with '.' into it_Line.
                    <Fld> = it_Line.
                  when others.
                    Perform PrepareString using it_Line.
                    <Fld> = it_Line.
                EndCase.
              EndIf.
            EndIf.
          EndLoop.
          If not it_Data is initial.
            Append it_Data.
          EndIf.
        EndLoop.
      Else.
        Error = 2.
      EndIf.
    Else.
      Error = 1.
    EndIf.
  Else.
    Error = 1.
  EndIf.

  Call function 'GUI_DELETE_FILE'
    EXPORTING
      FILE_NAME = FileTxt
    EXCEPTIONS
      others    = 1.

  If Error <> 0.
    Raise Error.
  EndIf.

ENDFUNCTION.

FUNCTION ZGET_EXCEL_PARAMETERS.
*"----------------------------------------------------------------------
*"*"Локальный интерфейс:
*"  EXPORTING
*"     REFERENCE(DECIMAL_SEPARATOR) TYPE  CHAR1
*"     REFERENCE(DATE_SEPARATOR) TYPE  CHAR1
*"     REFERENCE(DATE_FORMAT)
*"----------------------------------------------------------------------


  Perform AssignCharX.

  PERFORM GET_EXCEL_DECIMAL_SEPARATOR
    USING DECIMAL_SEPARATOR.

  Perform GET_SYSTEM_DATE_SEPARATOR
    USING DATE_SEPARATOR.

  Perform GET_SYSTEM_DATE_FORMAT
    USING DATE_FORMAT.

ENDFUNCTION.

Constants:
  X_TAB(1) TYPE X VALUE '09',
  X_0D(1)  TYPE X VALUE '0D',
  X_0A(1)  TYPE X VALUE '0A'.

DATA:
  CHAR_TAB type C,
  CHAR_0D  TYPE C,
  CHAR_0A  TYPE C,

  DECIMAL_POINT.

FORM AssignCharX .
  Data: L type i,
        CHAR_A value 'A',
        CHAR_B value 'B'.
  Field-symbols:
    <X>, <EI>.

  Assign CHAR_A to <X>  type 'X'.
  Assign CHAR_B to <EI> type 'X'.
  <EI> = <EI> - <X>.

  Assign CHAR_TAB to <X> type 'X'.
  Clear <X>.
  <X> = X_TAB * <EI>.
  Assign CHAR_0D to <X> type 'X'.
  Clear <X>.
  <X> = X_0D * <EI>.
  Assign CHAR_0A to <X> type 'X'.
  Clear <X>.
  <X> = X_0A * <EI>.
ENDFORM.                    " AssignCharX

FORM GET_EXCEL_DECIMAL_SEPARATOR
  USING RESULT.

  DATA:
    APP TYPE OLE2_OBJECT,
    USESYSTEMSEPARATORS(10).

  CREATE OBJECT APP 'Excel.Application'.
  IF SY-SUBRC = 0.
    GET PROPERTY OF APP
      'UseSystemSeparators' = USESYSTEMSEPARATORS.
    CALL FUNCTION 'FLUSH'.

    IF SY-SUBRC <> 0 OR
     ( SY-SUBRC = 0 AND USESYSTEMSEPARATORS <> 0 ).

      PERFORM GET_SYSTEM_DECIMAL_SEPARATOR
        USING RESULT.
    ELSE.
      GET PROPERTY OF APP
        'DecimalSeparator' = RESULT.
      CALL FUNCTION 'FLUSH'.
    ENDIF.

    FREE OBJECT APP.
    CALL FUNCTION 'FLUSH'.
  ENDIF.
ENDFORM.                    "GET_EXCEL_DECIMAL_SEPARATOR

FORM GET_SYSTEM_DECIMAL_SEPARATOR
  USING RESULT.

  DATA:
    HKEY_CURRENT_USER TYPE I VALUE 1.

  CALL FUNCTION 'GUI_GET_REGVALUE'
    EXPORTING
      ROOT           = HKEY_CURRENT_USER
      KEY            = 'Control Panel\International'
      VALUE_NAME     = 'sDecimal'
    CHANGING
      STRING         = RESULT
    EXCEPTIONS
      REGISTRY_ERROR = 1.
ENDFORM.                    "GET_SYSTEM_DECIMAL_SEPARATOR

FORM GET_SYSTEM_DATE_SEPARATOR
  USING RESULT.

  DATA:
    HKEY_CURRENT_USER TYPE I VALUE 1.

  CALL FUNCTION 'GUI_GET_REGVALUE'
    EXPORTING
      ROOT           = HKEY_CURRENT_USER
      KEY            = 'Control Panel\International'
      VALUE_NAME     = 'sDate'
    CHANGING
      STRING         = RESULT
    EXCEPTIONS
      REGISTRY_ERROR = 1.
ENDFORM.                    "GET_SYSTEM_DATE_SEPARATOR

FORM GET_SYSTEM_DATE_FORMAT
  USING RESULT.

  DATA:
    HKEY_CURRENT_USER TYPE I VALUE 1.

  CALL FUNCTION 'GUI_GET_REGVALUE'
    EXPORTING
      ROOT           = HKEY_CURRENT_USER
      KEY            = 'Control Panel\International'
      VALUE_NAME     = 'sShortDate'
    CHANGING
      STRING         = RESULT
    EXCEPTIONS
      REGISTRY_ERROR = 1.
ENDFORM.                    "GET_SYSTEM_DATE_FORMAT