*! Вольный перевод класса формирования файла Excel
*! (с) BY NICKS Russia URALS REGION.
*!ЭТО ОРИГИНАЛ DirectExcel v 1.5
*! (с) Oleg A. Rudenko, 2004-05. Moscow, Russia.
*! Oleg_Rudenko@mail.ru
*! Oleg_Rudenko@newmail.ru
*& Include zcl_dexcel_F *
CLASS zcl_dexcel DEFINITION.
  PUBLIC SECTION.
    DATA:
    conv_out TYPE REF TO cl_abap_conv_out_ce,
    gl_rev TYPE i,
    converter TYPE REF TO cl_abap_conv_obj.
    METHODS set_border
    IMPORTING
    !_rightbor  TYPE i
    !_rightst   TYPE i
    !_bottombor TYPE i
    !_bottomst  TYPE i
    !_leftbor   TYPE i
    !_leftst    TYPE i
    !_topbor    TYPE i
    !_topst     TYPE i
    CHANGING
    !borderst TYPE p .
    METHODS set_rowhi
    IMPORTING
    !_row     TYPE i
    !_height  TYPE i
    !_indexxf TYPE i .
    METHODS set_psetup
    IMPORTING
    !_options   TYPE i
    !_papersize TYPE i
    !_scale     TYPE i
    !_pagestart TYPE i
    !_wfit      TYPE i
    !_hfit      TYPE i .
    METHODS add_fx
    IMPORTING
    !_fontindex   TYPE i
    !_formatindex TYPE i
    !_extattr     TYPE i OPTIONAL
    !_alignment   TYPE i
    !_borderline  TYPE p
    !_fillpattern TYPE i OPTIONAL
    !_extattr1    TYPE x OPTIONAL
    !_reqadd      TYPE i OPTIONAL
    CHANGING
    !ind_fx TYPE i .
    METHODS create_xls
    IMPORTING
    !f_name    TYPE string
    !def_font  TYPE c OPTIONAL
    !t_out     TYPE c .
    METHODS wr_string
    IMPORTING
    !row       TYPE i
    !col       TYPE i
    !_val      TYPE c
    !_indexxf  TYPE i OPTIONAL
    !_note     TYPE c OPTIONAL.
    METHODS wr_number
    IMPORTING
    !row       TYPE i
    !col       TYPE i
    !_val      TYPE f
    !_indexxf  TYPE i OPTIONAL
    !_note     TYPE c OPTIONAL.
    METHODS wr_blank
    IMPORTING
    !row       TYPE i
    !col       TYPE i
    !_indexxf  TYPE i OPTIONAL
    !_note     TYPE c OPTIONAL.
    METHODS wr_tab " Вывести таблицу начиная с ячейки
    IMPORTING
    !dat_tab   TYPE STANDARD TABLE
    !_row      TYPE i " строка
    !_col      TYPE i " столбец
    !col_tab   TYPE lvc_t_fcat OPTIONAL " шаблон вывода - перечень и порядок
    !lt_filter TYPE lvc_t_fidx OPTIONAL
    RETURNING
    value(tec_row) TYPE i . " возвращаемая текущая строка
    METHODS set_defrowhi
    IMPORTING
    !height   TYPE i .
    METHODS ins_hpbreak
    IMPORTING
    !col TYPE i .
    METHODS ins_vpbreak
    IMPORTING
    !row TYPE i .
    METHODS set_col_width
    IMPORTING
    !f_col        TYPE i
    !e_col        TYPE i
    !width        TYPE n .
    METHODS close_xls .
    METHODS add_font
    IMPORTING
    !_fontname    TYPE c
    !_fontheight  TYPE i
    !_fontattr    TYPE i OPTIONAL
    !_fontcolor   TYPE x OPTIONAL
    !_add         TYPE i OPTIONAL
    CHANGING
    value(ind_font) TYPE i .
    METHODS add_format
    IMPORTING
    !_picture     TYPE c
    CHANGING
    value(ind_frm) TYPE i .
    METHODS protectsheet
    IMPORTING
    !prot TYPE i .
    METHODS get_xldate " перевести дату в формат Excel
    IMPORTING
    !sapdate TYPE dats
    CHANGING
    value(xldat) TYPE i .
    METHODS get_xltime " перевести время Excel
    IMPORTING
    !saptime TYPE t
    CHANGING
    value(xltime) TYPE f .
    METHODS get_tabl_inf " сформировать шаблон вывода по структуре
    IMPORTING dat_tabl TYPE REF TO data
    EXPORTING t_fcat TYPE lvc_t_fcat.
    METHODS inttohex
    IMPORTING
    !ilen TYPE i
    CHANGING
    !value(chex) TYPE x .

  PRIVATE SECTION.
    CONSTANTS xlsleftalign TYPE i VALUE 1.
    DATA:  nint4 TYPE int4
    , bufff      TYPE xstring
    ,ret_xstring TYPE xstring
    ,filename    TYPE string VALUE 'D:\test1.xls'
    ,deffont     TYPE i VALUE 0
    , defxf(1)   TYPE x .
    CONSTANTS xlsnumber       TYPE i VALUE 1.
    CONSTANTS xlstext         TYPE i VALUE 2.
    CONSTANTS xlsblank        TYPE i VALUE 3.
    CONSTANTS xlsgeneralalign TYPE i VALUE 0.
    CONSTANTS xlscentrealign  TYPE i VALUE 2.
    CONSTANTS xlsrightalign   TYPE i VALUE 3.
    CONSTANTS xlsfillcell     TYPE i VALUE 4.
    CONSTANTS xlsrightborder  TYPE i VALUE 16.
    CONSTANTS xlsleftborder   TYPE i VALUE 8.
    CONSTANTS xlstopborder    TYPE i VALUE 32.
    DATA:
    BEGIN OF wa_fontslist,
    id        TYPE i , " Индекс шрифта
    name(100) TYPE c, " Наименование шрифта
    size      TYPE i VALUE 10, " Размер шрифта
    attr      TYPE i , " Аттрибут шрифта
    color     TYPE i , " Цвет шрифта
    END OF wa_fontslist .
    DATA:
    tb_fontslist LIKE TABLE OF wa_fontslist .
    DATA:
    BEGIN OF wa_xflist,
    id TYPE i , " Индекс стиля
    fontndx(1)     TYPE x , " Индекс шрифта
    fmtndx(1)      TYPE x , " Индекс формата вывода
    extattr(2)     TYPE x , " Дополнительные аттрибуты
    extattr1(1)    TYPE x , " Дополнительные аттрибуты
    alignment(1)   TYPE x , " Выравнивание
    borderstyle(2) TYPE x , " Стиль окантовки
    fillpattern(4) TYPE x , " Стиль закрашивания
    END OF wa_xflist .
    DATA:  BEGIN OF wa_formatslist,
    id(1) TYPE x ," Индекс формата
    picture(50) TYPE c ," Сам формат
    END OF wa_formatslist .
    CONSTANTS: xlsfont0 TYPE i VALUE 0.
    DATA: tb_formatslist LIKE TABLE OF wa_formatslist .
    DATA:
    BEGIN OF wa_pbreaks,
    position TYPE i ," Позиция (колонка или строка)
    END OF wa_pbreaks .
    DATA: hp_breaks LIKE TABLE OF wa_pbreaks .
    DATA: vp_breaks LIKE TABLE OF wa_pbreaks .
    DATA isopen TYPE i .
    DATA: txflist   LIKE TABLE OF wa_xflist ,
          deffontname(30) TYPE c .
    DATA: typeout(2) TYPE c .
    METHODS get_borderline
    IMPORTING
    !_border TYPE p
    CHANGING
    !borderxeh TYPE x .
    METHODS cellnote
    IMPORTING
    !_col TYPE i
    !_row TYPE i
    !_note TYPE c .
    METHODS add_string
    IMPORTING
    !adtext TYPE c .
    METHODS add_number
    IMPORTING
    !adnumb TYPE f .
    METHODS donl_file
    IMPORTING
    !app_mode TYPE char01 .
    METHODS wr_def_formats .
ENDCLASS. "zcl_dexcel DEFINITION
*----------------------------------------------------------------------*
*       CLASS zcl_dexcel IMPLEMENTATION
*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
CLASS zcl_dexcel IMPLEMENTATION.
  METHOD add_font .
    DATA: BEGIN OF font_data,
    opcode(2)      TYPE x VALUE '3102',
    length(2)      TYPE x VALUE '1000',
    fontheight(2)  TYPE x VALUE '2000',                     "! 1/20
    fontattr(2)    TYPE x ," VALUE fontattr-faauto,
    fontcolor(2)   TYPE x ," VALUE fontcolor-fcauto,
    fontnamelen(1) TYPE x,
    END OF font_data,
    loc_fontname   TYPE string,
    loc_rec        TYPE i.
    FIELD-SYMBOLS: <lc_fs> TYPE x.
    IF _fontname IS NOT INITIAL.
      loc_fontname = _fontname.
    ELSE.
      loc_fontname = deffontname.
    ENDIF.
    wa_fontslist-name = loc_fontname.
    IF _fontheight IS NOT INITIAL.
      wa_fontslist-size = _fontheight.
    ELSE.
      wa_fontslist-size = 10.
    ENDIF.
    IF _fontattr IS NOT INITIAL .
      wa_fontslist-attr = _fontattr.
      nint4 = wa_fontslist-attr.
      CALL METHOD inttohex
        EXPORTING
          ilen = 1
        CHANGING
          chex = font_data-fontattr.
    ENDIF.
    IF _fontcolor IS NOT INITIAL .
      font_data-fontcolor = _fontcolor.
    ENDIF.
    READ TABLE tb_fontslist INTO wa_fontslist WITH KEY name = loc_fontname size =
    _fontheight attr = _fontattr
    color = _fontcolor BINARY SEARCH.
    IF sy-subrc NE 0 OR _add = 1 .
      DESCRIBE TABLE tb_fontslist LINES loc_rec.
      IF loc_rec = 255 .
      ELSE.
        nint4 = STRLEN( wa_fontslist-name ).
        CALL METHOD inttohex
          EXPORTING
            ilen = 1
          CHANGING
            chex = font_data-fontnamelen.
        nint4 = 7 + nint4.
        CALL METHOD inttohex
          EXPORTING
            ilen = 1
          CHANGING
            chex = font_data-length.
        nint4 = wa_fontslist-size * 20.
        CALL METHOD inttohex
          EXPORTING
            ilen = 2
          CHANGING
            chex = font_data-fontheight.
        ASSIGN font_data TO <lc_fs> CASTING.
        CONCATENATE bufff <lc_fs> INTO bufff IN BYTE MODE.
        nint4 = STRLEN( loc_fontname ).
        CALL FUNCTION 'SCMS_STRING_TO_XSTRING'
          EXPORTING
            text   = loc_fontname
          IMPORTING
            buffer = ret_xstring "* MIMETYPE = ' '
          EXCEPTIONS
            failed = 1
            OTHERS = 2.
        IF sy-subrc <> 0.
        ENDIF.
        CONCATENATE bufff ret_xstring INTO bufff IN BYTE MODE.
        IF loc_rec < 5.
        ELSE.
          loc_rec = loc_rec + 1.
        ENDIF.
        wa_fontslist-id = loc_rec.
        ind_font = loc_rec.
        APPEND wa_fontslist TO tb_fontslist.
      ENDIF.
    ELSE.
      ind_font = wa_fontslist-id .
    ENDIF.
  ENDMETHOD. "add_font
  METHOD add_format .
    DATA: BEGIN OF format_data,
    opcode(2) TYPE x VALUE '1E04',
    length(2) TYPE x VALUE '0300',
    formatindex(2) TYPE x VALUE '0000',
    formatlen(1) TYPE x,
    END OF format_data,
    i TYPE i,
    l_addsize TYPE i VALUE 3.
*    l_saverec(1000) .
    FIELD-SYMBOLS: <lc_fs> TYPE x,
    <it_rc> TYPE x.
    CLEAR wa_formatslist.
    IF _picture NE space.
      READ TABLE tb_formatslist INTO wa_formatslist WITH KEY picture = _picture .
      IF sy-subrc NE 0 ."OR tb_formatslist IS INITIAL .
        DESCRIBE TABLE tb_formatslist LINES i.
        IF i = 255.
        ELSE.
          wa_formatslist-id = i.
          format_data-formatlen = STRLEN( _picture ).
          nint4 = format_data-formatlen + l_addsize.
          CALL METHOD inttohex
            EXPORTING
              ilen = 1
            CHANGING
              chex = format_data-length.
          ASSIGN format_data TO <it_rc> CASTING.
          CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
          ASSIGN _picture TO <lc_fs> CASTING TYPE x. " Перевод в HEX вид
          CONCATENATE bufff <lc_fs> INTO bufff IN BYTE MODE.
          wa_formatslist-picture = _picture .
          APPEND wa_formatslist TO tb_formatslist.
          ind_frm = i .
        ENDIF.
      else.
        ind_frm =  wa_formatslist-id.
      ENDIF.

    ENDIF.
  ENDMETHOD. "ADD_FORMAT
  METHOD add_number .
    DATA: lc TYPE i,
    oct TYPE i,
    re_dat(8) TYPE x.
    FIELD-SYMBOLS <fs> TYPE x.
    ASSIGN adnumb TO <fs> CASTING TYPE x.
    CLEAR: re_dat.
    DO 8 TIMES.
      lc = 8 - sy-index. " читать от конца
      oct = sy-index - 1. " писать от начала
      re_dat+oct(1) = <fs>+lc(1).
    ENDDO.
    CONCATENATE bufff re_dat INTO bufff IN BYTE MODE.
  ENDMETHOD. "ADD_NUMBER
  METHOD add_string .
    DATA: s_string TYPE string.
    s_string = adtext.
    CALL METHOD converter->convert
      EXPORTING
        inbuff         = s_string
        inbufflg       = 0
        outbufflg      = 0
      IMPORTING
        outbuff        = ret_xstring
      EXCEPTIONS
        internal_error = 1
        OTHERS         = 2.
    IF sy-subrc <> 0.
      CASE sy-subrc.
        WHEN 1.
        WHEN 2.
      ENDCASE.
    ENDIF.
    CONCATENATE bufff ret_xstring INTO bufff IN BYTE MODE.
  ENDMETHOD. "ADD_STRING
  METHOD add_fx .
    DATA: BEGIN OF xf_data,
    opcode(2)       TYPE x VALUE '4304',
    length(2)       TYPE x VALUE '0C',
    fontindex(1)    TYPE x VALUE '',
    formatindex(1)  TYPE x VALUE '',
    extattr(2)      TYPE x VALUE '01',
    alignment(1)    TYPE x VALUE '20',
    extattr1(1)     TYPE x VALUE '00',
    fillpattern(2)  TYPE x VALUE '00CE',
    borderstyle(4)  TYPE x,
    END OF xf_data,
    countrec          TYPE i,
    fillpattern(2)    TYPE x,
    loc_patt(2)       TYPE x,
    fillcolor(2)      TYPE x,
    patterncolor(2)   TYPE x,
    pos_from          TYPE i,
    pos_to            TYPE i,
    pos_col           TYPE i,
    loc_xf            LIKE wa_xflist.
    FIELD-SYMBOLS: <lc_fs> TYPE x.
    IF isopen IS NOT INITIAL. " OR (Self.XFList &= Null)
      MOVE _fillpattern TO fillpattern.
      MOVE fillpattern+1(1) TO pos_to .
      IF pos_to > 1.
        loc_patt = '07C0'.
        fillcolor = fillpattern BIT-AND loc_patt.
        IF fillcolor EQ '0000'.
          fillcolor = '0640'.
        ENDIF.
        MOVE fillcolor TO pos_from .
        loc_patt = 'F800'.
        patterncolor = fillpattern BIT-AND loc_patt.
        MOVE patterncolor TO pos_col.
        IF patterncolor EQ '0000'.
          patterncolor = '00C0'.
        ENDIF.
        pos_from = pos_from * 32.
        pos_col = pos_col / 32.
        nint4 = pos_to + pos_from + pos_col.
        MOVE nint4 TO loc_patt.
        loc_xf-fillpattern+0(1) = loc_patt+1(1).
        loc_xf-fillpattern+1(1) = loc_patt+0(1).
      ENDIF.
    ENDIF.
    nint4 = _fontindex.
    CALL METHOD inttohex
      EXPORTING
        ilen = 1
      CHANGING
        chex = loc_xf-fontndx.
    nint4 = _formatindex.
    CALL METHOD inttohex
      EXPORTING
        ilen = 1
      CHANGING
        chex = loc_xf-fmtndx.
    nint4 = _extattr.
    CALL METHOD inttohex
      EXPORTING
        ilen = 1
      CHANGING
        chex = loc_xf-extattr.
    IF _alignment IS NOT INITIAL.
      nint4 = _alignment.
      CALL METHOD inttohex
        EXPORTING
          ilen = 1
        CHANGING
          chex = loc_xf-alignment.
    ELSE.
      loc_xf-alignment = '20'.
    ENDIF.
    loc_xf-extattr1 = _extattr1 .
    READ TABLE txflist INTO wa_xflist WITH KEY fontndx = loc_xf-fontndx fmtndx = loc_xf-fmtndx
    extattr = loc_xf-extattr extattr1 = loc_xf-extattr1 alignment = loc_xf-alignment
    borderstyle = loc_xf-borderstyle fillpattern = loc_xf-fillpattern. " BINARY SEARCH.
    IF sy-subrc NE 0 OR _reqadd = 1 .
      DESCRIBE TABLE txflist LINES countrec.
      CLEAR wa_xflist.
      wa_xflist-fontndx     = loc_xf-fontndx.
      wa_xflist-fmtndx      = loc_xf-fmtndx.
      wa_xflist-extattr     = loc_xf-extattr.
      wa_xflist-extattr1    = loc_xf-extattr1.
      wa_xflist-alignment   = loc_xf-alignment.
      wa_xflist-borderstyle = loc_xf-borderstyle.
      xf_data-fillpattern   = loc_xf-fillpattern.
      xf_data-fontindex     = loc_xf-fontndx.
      xf_data-formatindex   = loc_xf-fmtndx.
      xf_data-extattr       = loc_xf-extattr.
      xf_data-extattr1      = loc_xf-extattr1.
      xf_data-alignment     = loc_xf-alignment.
      IF _borderline NE 0.
        CALL METHOD get_borderline
          EXPORTING
            _border   = _borderline
          CHANGING
            borderxeh = xf_data-borderstyle.
      ELSE.
        xf_data-borderstyle = loc_xf-borderstyle.
      ENDIF.
      ASSIGN xf_data TO <lc_fs> CASTING.
      CONCATENATE bufff <lc_fs> INTO bufff IN BYTE MODE.
      ind_fx = countrec.
      wa_xflist-id = countrec.
      APPEND wa_xflist TO txflist.
    ELSE.
      ind_fx = wa_xflist-id.
    ENDIF.
  ENDMETHOD. "add_fx
  METHOD cellnote .
    DATA: BEGIN OF cellnote_data,
    opcode(2)  TYPE x VALUE 'C001',
    length(2)  TYPE x VALUE '0600',
    row(2)     TYPE x ,
    col(2)     TYPE x ,
    notelen(2) TYPE x ,
    END OF cellnote_data.
    FIELD-SYMBOLS: <it_rc> TYPE x.
    nint4 = 28.
    CALL METHOD inttohex
      EXPORTING
        ilen = 1
      CHANGING
        chex = cellnote_data-opcode.
    nint4 = _row - 1.
    CALL METHOD inttohex
      EXPORTING
        ilen = 1
      CHANGING
        chex = cellnote_data-row.
    nint4 = _col - 1.
    CALL METHOD inttohex
      EXPORTING
        ilen = 1
      CHANGING
        chex = cellnote_data-col.
    nint4 = 6 + STRLEN( _note ).
    CALL METHOD inttohex
      EXPORTING
        ilen = 1
      CHANGING
        chex = cellnote_data-length.
    nint4 = STRLEN( _note ).
    CALL METHOD inttohex
      EXPORTING
        ilen = 2
      CHANGING
        chex = cellnote_data-notelen.
    ASSIGN cellnote_data TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
    CALL METHOD add_string( _note ) .
  ENDMETHOD. "cellnote
  METHOD close_xls .
    DATA: BEGIN OF end_file_data,
    opcode(2) TYPE x VALUE '0A',
    length(2) TYPE x VALUE '00',
    END OF end_file_data,
    BEGIN OF v_breaks,
    opcode(2) TYPE x VALUE '1A',
    length(2) TYPE x ,
    pages(2)  TYPE x ,
    END OF v_breaks,
    BEGIN OF h_breaks,
    opcode(2) TYPE x VALUE '1B',
    length(2) TYPE x VALUE '02',
    pages(2)  TYPE x ,
    END OF h_breaks,
    h_length   TYPE i VALUE 2,
    num_breaks TYPE i VALUE 0,
    t_short(2) TYPE x,
    t_len TYPE i ,
    pagesarray TYPE xstring.
    FIELD-SYMBOLS: <it_rc> TYPE x.
    DESCRIBE TABLE hp_breaks LINES num_breaks.
    IF num_breaks NE 0 AND isopen NE 0 .
      nint4 = num_breaks.
      CALL METHOD inttohex
        EXPORTING
          ilen = 2
        CHANGING
          chex = h_breaks-pages.
      LOOP AT hp_breaks INTO wa_pbreaks.
        nint4 = wa_pbreaks-position.
        CALL METHOD inttohex
          EXPORTING
            ilen = 2
          CHANGING
            chex = t_short.
        CONCATENATE pagesarray t_short INTO pagesarray IN BYTE MODE.
        t_len = t_len + 2.
      ENDLOOP.
      h_length = h_length + num_breaks * 2.
      nint4 = h_length.
      CALL METHOD inttohex
        EXPORTING
          ilen = 1
        CHANGING
          chex = h_breaks-length.
      ASSIGN h_breaks TO <it_rc> CASTING.
      CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
      CONCATENATE bufff pagesarray INTO bufff IN BYTE MODE.
    ENDIF .
    DESCRIBE TABLE vp_breaks LINES num_breaks.
    IF num_breaks NE 0 AND isopen NE 0 .
      t_len = 0.
      h_length = 2.
      CLEAR pagesarray.
      SORT vp_breaks BY position.
      nint4 = num_breaks.
      CALL METHOD inttohex
        EXPORTING
          ilen = 2
        CHANGING
          chex = v_breaks-pages.
      LOOP AT vp_breaks INTO wa_pbreaks.
        nint4 = wa_pbreaks-position.
        CALL METHOD inttohex
          EXPORTING
            ilen = 2
          CHANGING
            chex = t_short.
        CONCATENATE pagesarray t_short INTO pagesarray IN BYTE MODE.
        t_len = t_len + 2.
      ENDLOOP.
      h_length = h_length + num_breaks * 2.
      nint4 = h_length.
      CALL METHOD inttohex
        EXPORTING
          ilen = 1
        CHANGING
          chex = v_breaks-length.
      ASSIGN v_breaks TO <it_rc> CASTING.
      CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
      CONCATENATE bufff pagesarray INTO bufff IN BYTE MODE.
    ENDIF .
    ASSIGN end_file_data TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
    CALL METHOD donl_file
      EXPORTING
        app_mode = 'X'.
    IF typeout = 'AC'. " Выгрузка на AP сервер
      CLOSE DATASET filename .
    ENDIF.
  ENDMETHOD. "close_xls
  METHOD create_xls .
    DATA:
    lc_tx(2) TYPE c,
    def_fonth TYPE i,
    def_fontst(30) TYPE c,
    hkey_current_user TYPE i VALUE 1,
    BEGIN OF beg_file_data,
    opcode(2)   TYPE x VALUE '0904',
    length(2)   TYPE x VALUE '0600',
    version(2)  TYPE x VALUE '0000',
    subtype(2)  TYPE x VALUE '1000',
    buildid(2)  TYPE x VALUE '5E1F',
    END OF beg_file_data,
    BEGIN OF codepage_data,
    opcode(2)   TYPE x VALUE '4200',
    length(2)   TYPE x VALUE '0200',
    codepage(2) TYPE x VALUE 'E304',
    END OF codepage_data,
    BEGIN OF calc_mode_data,
    opcode(2)   TYPE x VALUE '0D00',
    length(2)   TYPE x VALUE '0200',
    calcmode(2) TYPE x VALUE '0100',
    END OF calc_mode_data,
    BEGIN OF wind_data,
    opcode(2)   TYPE x VALUE '3D00',
    length(2)   TYPE x VALUE '0A00',
    xpos(2)     TYPE x VALUE '0000',
    ypos(2)     TYPE x VALUE '0000',
    width(2)    TYPE x VALUE '0000',
    height(2)   TYPE x VALUE '0000',
    hidden(2)   TYPE x VALUE '0',
    END OF wind_data,
    lc TYPE i.
    FIELD-SYMBOLS: <it_rc> TYPE x.
    lc = 4660.
    ASSIGN lc TO <it_rc> CASTING.
    CASE <it_rc>+0(1).
      WHEN '00'. " 00
        gl_rev = 1 .
      WHEN OTHERS.
        gl_rev = 2.
    ENDCASE.
    lc = 0.
    CLEAR <it_rc>.
    CLEAR deffontname.
    isopen = 1.
    CLEAR: bufff.
    ASSIGN beg_file_data TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
    ASSIGN codepage_data TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
    ASSIGN calc_mode_data TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
    ASSIGN wind_data TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
*    FREE tfontlist.
    CLEAR wa_fontslist.
    IF def_font IS NOT INITIAL.
      wa_fontslist-name = def_font.
    ELSE.
      CALL FUNCTION 'GUI_GET_REGVALUE'
        EXPORTING
          root           = hkey_current_user
          key            = 'Software\Microsoft\Office\11.0\Excel\Options'
          value_name     = 'Font'
        CHANGING
          string         = def_fontst
        EXCEPTIONS
          registry_error = 1.
      CONDENSE def_fontst.
      def_fonth = STRLEN( def_fontst ).
      IF sy-subrc = 0 AND def_fonth > 0.
        SPLIT def_fontst AT ',' INTO wa_fontslist-name lc_tx .
        CONDENSE wa_fontslist-name.
        CONDENSE lc_tx .
        def_fonth = lc_tx .
      ELSE.
        wa_fontslist-name = 'Arial Cyr'.
        def_fonth = 10.
      ENDIF.
    ENDIF.
    DO 5 TIMES.
      CALL METHOD add_font
        EXPORTING
          _fontname   = wa_fontslist-name
          _fontheight = def_fonth
          _fontattr   = 0
          _fontcolor  = '0000'
          _add        = 1
        CHANGING
          ind_font    = lc.
    ENDDO.
    IF f_name IS NOT INITIAL.
      filename = f_name.
    ENDIF .
    typeout = t_out.
    CALL METHOD wr_def_formats.
    IF typeout = 'AC'. " Выгрузка на AP сервер
      OPEN DATASET filename FOR OUTPUT IN BINARY MODE.
      IF sy-subrc <> 0.
        MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
        WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
      ENDIF.
    ENDIF.
    FREE vp_breaks.
    FREE hp_breaks.
    DATA: prc_conv TYPE REF TO cl_abap_conv_out_ce,
    prc_xstr_no    TYPE xstring,
    prc_i_no       TYPE i,
    prc_sapno      TYPE cpcharno.
    TRY.
        CALL METHOD cl_abap_conv_out_ce=>create
          EXPORTING
            encoding = '4000'
          RECEIVING
            conv     = prc_conv.
      CATCH cx_parameter_invalid_range .
      CATCH cx_sy_codepage_converter_init .
    ENDTRY.
    TRY.
        CALL METHOD prc_conv->write
          EXPORTING
            data = '#'.
      CATCH cx_sy_codepage_converter_init.
      CATCH cx_sy_conversion_codepage.
      CATCH cx_parameter_invalid_type.
      CATCH cx_parameter_invalid_range.
    ENDTRY.
    TRY.
      CALL METHOD prc_conv->get_buffer
        RECEIVING
          buffer = prc_xstr_no.
    ENDTRY.
    prc_i_no = prc_xstr_no.
    prc_sapno = prc_i_no.
    DATA: par_encoding TYPE abap_encoding VALUE '1504',
    prc_codepage(4) TYPE n,
    prc_miss        TYPE c,
    prc_broken      TYPE c.
    prc_miss      = '.'.
    prc_broken    = '.'.
    prc_codepage  = par_encoding.
    CREATE OBJECT converter
    EXPORTING
    outcode = prc_codepage
    miss = prc_miss
    broken = prc_broken
    use_f1 = 'X'
    substc = prc_sapno
    EXCEPTIONS
    invalid_codepage = 1
    internal_error = 2.
    IF sy-subrc <> 0.
      CASE sy-subrc.
        WHEN 1.
        WHEN 2.
      ENDCASE.
    ENDIF.
  ENDMETHOD. "create_xls
  METHOD donl_file .
    DATA:" len TYPE i,
    xlsfsize  TYPE i.
*    pos       TYPE i.
    xlsfsize = XSTRLEN( bufff ).
    IF typeout = 'AC'. " Выгрузка на AP сервер
      TRANSFER bufff TO filename LENGTH xlsfsize.
    ELSE.
      DATA: lc_bin TYPE sdok_sdatx,
      l_graphic_conv TYPE i,
      xlsbuffer LIKE STANDARD TABLE OF lc_bin,
      l_graphic_offs TYPE i.
      CHECK xlsfsize > 0.
      l_graphic_conv = xlsfsize.
      l_graphic_offs = 0.
      WHILE l_graphic_conv > 1022.
        lc_bin = bufff+l_graphic_offs(1022).
        APPEND lc_bin TO xlsbuffer.
        l_graphic_offs = l_graphic_offs + 1022.
        l_graphic_conv = l_graphic_conv - 1022.
      ENDWHILE.
      lc_bin = bufff+l_graphic_offs(l_graphic_conv).
      APPEND lc_bin TO xlsbuffer.
      CALL FUNCTION 'GUI_DOWNLOAD'
        EXPORTING
          bin_filesize = xlsfsize
          filename     = filename
          filetype     = 'BIN'
          append       = ' '
        TABLES
          data_tab     = xlsbuffer
        EXCEPTIONS
          OTHERS       = 22.
      IF sy-subrc <> 0.
        MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
        WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
      ENDIF.
    ENDIF.
    REFRESH xlsbuffer.
    CLEAR: xlsbuffer, xlsfsize , bufff.
  ENDMETHOD. "donl_file
  METHOD set_border .
    DATA lc_int TYPE i.
    lc_int = _rightbor + _rightst.
    IF lc_int > 200.
      lc_int = 195.
    ENDIF.
    borderst = borderst + lc_int.
    lc_int = _bottombor + _bottomst.
    IF lc_int > 200.
      lc_int = 195.
    ENDIF.
    borderst = borderst + lc_int * 256 .
    lc_int = _leftbor + _leftst.
    IF lc_int > 200.
      lc_int = 195.
    ENDIF.
    borderst = borderst + lc_int * 256 * 256 .
    lc_int = _topbor + _topst.
    IF lc_int > 200.
      lc_int = 195.
    ENDIF.
    borderst = borderst + lc_int * 256 * 256 * 256.
  ENDMETHOD. "set_border
  METHOD set_col_width .
    DATA : BEGIN OF colwidth,
    opcode(2)   TYPE x VALUE '2400',
    length(2)   TYPE x VALUE '0400',
    col1(1)     TYPE x,
    col2(1)     TYPE x,
    colwidth(2) TYPE x,
    END OF colwidth.
    FIELD-SYMBOLS: <it_rc> TYPE x.
    nint4 = f_col - 1.
    CALL METHOD inttohex
      EXPORTING
        ilen = 1
      CHANGING
        chex = colwidth-col1.
    nint4 = f_col + e_col - 1.
    CALL METHOD inttohex
      EXPORTING
        ilen = 1
      CHANGING
        chex = colwidth-col2.
    nint4 = width * 256.
    CALL METHOD inttohex
      EXPORTING
        ilen = 2
      CHANGING
        chex = colwidth-colwidth.
    ASSIGN colwidth TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
  ENDMETHOD. "SET_COL_WIDTH
  METHOD set_defrowhi .
    DATA : BEGIN OF defheight,
    opcode(2) TYPE x VALUE '2500',
    length(2) TYPE x VALUE '0200',
    rowheight(2) TYPE x ,
    END OF defheight.
    FIELD-SYMBOLS: <it_rc> TYPE x.
    nint4 = height * 20.
    CALL METHOD inttohex
      EXPORTING
        ilen = 2
      CHANGING
        chex = defheight-rowheight.
    ASSIGN defheight TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
  ENDMETHOD. "SET_DEFROWHI
  METHOD set_psetup .
    DATA:
    BEGIN OF ps_data,
    opcode(2)    TYPE x VALUE 'A100',
    length(2)    TYPE x VALUE '2200',
    papersize(2) TYPE x VALUE '0900', " Размер (тип', страницы
    scale(2)     TYPE x VALUE '6400', " Масштаб
    pagestart(2) TYPE x VALUE '0100', " Номер стартовой страницы
    fitwidth(2)  TYPE x VALUE '0100', " Кол-во страниц в ширину
    fitheight(2) TYPE x VALUE '0100', " Кол-во страниц в высоту
    options(2)   TYPE x VALUE '0200', " Доп.битовые флажки свойств
    hres(2)      TYPE x VALUE '6801', " '0168'Горизонтальное разрешение принтера
    vres(2)      TYPE x VALUE '6801', " Вертикальное разрешение принтера
    END OF ps_data,
    headermargin TYPE f VALUE '0.5' , " Размещение верхнего колонтитула, дюймы
    footermargin TYPE f VALUE '0.5' ," Размещение нижнего колонтитула, дюймы
    copies(2)    TYPE x VALUE '0100'. " Кол-во копий
    FIELD-SYMBOLS: <it_rc> TYPE x.
    nint4 = _papersize.
    CALL METHOD inttohex
      EXPORTING
        ilen = 2
      CHANGING
        chex = ps_data-papersize.
    nint4 = _scale.
    CALL METHOD inttohex
      EXPORTING
        ilen = 2
      CHANGING
        chex = ps_data-scale.
    nint4 = _pagestart.
    CALL METHOD inttohex
      EXPORTING
        ilen = 2
      CHANGING
        chex = ps_data-pagestart.
    nint4 = _wfit.
    CALL METHOD inttohex
      EXPORTING
        ilen = 2
      CHANGING
        chex = ps_data-fitwidth.
    nint4 = _hfit.
    CALL METHOD inttohex
      EXPORTING
        ilen = 2
      CHANGING
        chex = ps_data-fitheight.
    nint4 = _options.
    CALL METHOD inttohex
      EXPORTING
        ilen = 2
      CHANGING
        chex = ps_data-options.
    ASSIGN ps_data TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
    CALL METHOD add_number( headermargin ).
    CALL METHOD add_number( footermargin ).
    ASSIGN copies TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
  ENDMETHOD. "set_psetup
  METHOD set_rowhi .
    DATA:
    BEGIN OF row_data , " ! Запись об аттрибутах строки
    opcode(2)     TYPE x VALUE '0802' ,")
    length(2)     TYPE x VALUE '1000',
    rownum(2)     TYPE x ,
    firstcol(2)   TYPE x VALUE '0000',
    lastcol(2)    TYPE x VALUE '0000',
    height(2)     TYPE x ,
    reserved1(2)  TYPE x VALUE '0000',"
    reserved2(2)  TYPE x VALUE '0000',"
    optionflag(2) TYPE x VALUE 'C001',
    indexxf(2)    TYPE x VALUE 'F000',") ! 0-based
    END OF row_data.
    FIELD-SYMBOLS: <it_rc> TYPE x.
    nint4 = _row - 1.
    CALL METHOD inttohex
      EXPORTING
        ilen = 2
      CHANGING
        chex = row_data-rownum.
    nint4 = _height * 20.
    CALL METHOD inttohex
      EXPORTING
        ilen = 2
      CHANGING
        chex = row_data-height.
    IF _indexxf NE 0.
      nint4 = _indexxf.
      CALL METHOD inttohex
        EXPORTING
          ilen = 1
        CHANGING
          chex = row_data-indexxf.
    ENDIF.
    ASSIGN row_data TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
  ENDMETHOD. "set_rowhi
  METHOD get_borderline .
    DATA: lc_bord TYPE p,
    lc_pos TYPE i,
    lc_hex(1) TYPE x.
    CLEAR:lc_hex , lc_bord , lc_pos .
    lc_bord = _border.
    DO 4 TIMES.
      nint4 = FLOOR( lc_bord / 256 ** ( 4 - sy-index ) ).
      lc_bord = lc_bord - nint4 * ( 256 ** ( 4 - sy-index ) ).
      CLEAR lc_hex .
      IF nint4 > 0.
        CALL METHOD inttohex
          EXPORTING
            ilen = 1
          CHANGING
            chex = lc_hex.
      ENDIF.
      borderxeh+lc_pos(1) = lc_hex.
      lc_pos = lc_pos + 1.
    ENDDO.
  ENDMETHOD. "get_borderline
  METHOD ins_hpbreak .
    IF isopen NE 0 .
      READ TABLE hp_breaks INTO wa_pbreaks WITH KEY position = col.
      IF sy-subrc NE 0.
        wa_pbreaks-position = col.
        APPEND wa_pbreaks TO hp_breaks .
      ENDIF.
    ENDIF.
  ENDMETHOD. "ins_hpbreak
  METHOD ins_vpbreak .
    IF isopen NE 0 .
      READ TABLE vp_breaks INTO wa_pbreaks WITH KEY position = row.
      IF sy-subrc NE 0.
        wa_pbreaks-position = row.
        APPEND wa_pbreaks TO vp_breaks .
      ENDIF.
    ENDIF.
  ENDMETHOD. "ins_vpbreak
  METHOD inttohex .
    DATA: pos_from TYPE i,
    pos_to TYPE i,
    nlenhex TYPE i.
    FIELD-SYMBOLS <fs> TYPE x.
    CLEAR chex.
    nlenhex = 4.
    ASSIGN nint4 TO <fs> CASTING.
    CASE gl_rev.
      WHEN 1. " 00001234 " инвертировать
        DO ilen TIMES.
          pos_from = nlenhex - sy-index. " читать от конца
          pos_to = sy-index - 1. " писать от начала
          chex+pos_to(1) = <fs>+pos_from(1).
        ENDDO.
      WHEN 2. " 34120000 " взять iLen байт
        chex = <fs>+0(ilen).
      WHEN OTHERS.
    ENDCASE.
  ENDMETHOD. "INTTOHEX
  METHOD protectsheet .
    DATA: BEGIN OF pr_sheet_record ,
    opcode(2)  TYPE x VALUE '1200',
    length(2)  TYPE x VALUE '0200',
    protect(2) TYPE x VALUE '0000',
    END OF pr_sheet_record.
    FIELD-SYMBOLS: <it_rc> TYPE x.
    IF prot NE 0.
      pr_sheet_record-protect = '0100' .
    ENDIF.
    ASSIGN pr_sheet_record TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
  ENDMETHOD. "protectsheet
  METHOD wr_blank .
    DATA: BEGIN OF label_data,
    opcode(2)  TYPE x VALUE '0102',
    length(2)  TYPE x VALUE '0600',
    row(2)     TYPE x ,
    col(2)     TYPE x ,
    indexxf(2) TYPE x VALUE '0F00',
    END OF label_data.
    FIELD-SYMBOLS: <it_rc> TYPE x.
        CHECK col < 255.
      CHECK row < 64000.
    IF _indexxf IS NOT INITIAL.
      nint4 = _indexxf.
      CALL METHOD inttohex
        EXPORTING
          ilen = 2
        CHANGING
          chex = label_data-indexxf.
    ENDIF.
    nint4 = row - 1.
    CALL METHOD inttohex
      EXPORTING
        ilen = 2
      CHANGING
        chex = label_data-row.
    nint4 = col - 1.
    CALL METHOD inttohex
      EXPORTING
        ilen = 1
      CHANGING
        chex = label_data-col.
    ASSIGN label_data TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
    IF _note IS NOT INITIAL.
      CALL METHOD cellnote( _col = col _row = row _note = _note ).
    ENDIF.
  ENDMETHOD. "wr_blank
  METHOD wr_def_formats .
    DATA ifx TYPE i VALUE 0.
    DATA: BEGIN OF stdstyle_data,
    opcode(2)   TYPE x VALUE '9302',
    length(2)   TYPE x VALUE '0400',
    indexxf(2)  TYPE x VALUE '0080' ,
    buildnum(2) TYPE x VALUE '00FF' ,
    END OF stdstyle_data.
    FIELD-SYMBOLS: <it_rc> TYPE x.
    CLEAR wa_formatslist.
    REFRESH tb_formatslist.
    CALL METHOD add_format
      EXPORTING
        _picture = 'General'
      CHANGING
        ind_frm  = ifx. " 0
    CALL METHOD add_format
      EXPORTING
        _picture = '0;-0'  " 1
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture
        ='0.00;-0.00'  " 2
      CHANGING
        ind_frm       = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture
        ='#,##0;-#,##0'  " 3
      CHANGING
        ind_frm         = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = '#,##0.00;-#,##0.00'  " 4
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = '#,##0\ "$";\-#,##0\ "$"' " 5
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = '#,##0\ "$";[Red]\-#,##0\ "$"'  " 6
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = '#,##0.00\ "$";\-#,##0.00\ "$"' " 7
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = '#,##0.00\ "$";[Red]\-#,##0.00\ "$"' "8
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = '0%'  " 9
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = '0.00%'  " 10
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = '0.00E+00'  " 11
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = 'dd/mm/yy'  " 12
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = 'dd/\ mmm\ yy' " 13
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = 'dd/\ mmm'  " 14
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = 'mmm\ yy'  " 15
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = 'h:mm\ AM/PM'  " 16
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = 'h:mm:ss\ AM/PM'  " 17
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = 'hh:mm' " 18
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = 'hh:mm:ss'  " 19
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = 'dd/mm/yy\ hh:mm'  " 20
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = '##0.0E+0'  " 21
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = 'mm:ss'                                  "' 22
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = '@'  " 23
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = 'dd/mm/yyyy'  " 24
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = '0.0;-0.0'  " 25
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = '0.000;-0.000' " 26
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = '0.0000;-0.0000'  " 27
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = '$#,##0.00;-$#,##0.00' " 28
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = '#,##0.00\ "р.";\-#,##0.00\ "р."'  " 29
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = '#,##0.00\ "E";\-#,##0.00\ "E"'  " 30
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = '#\ ##0;-#\ ##0'  " 31
      CHANGING
        ind_frm  = ifx. "
    CALL METHOD add_format
      EXPORTING
        _picture = '#\ ##0.00;-#\ ##0.00' " 32
      CHANGING
        ind_frm  = ifx. "
    FREE txflist.
    CALL METHOD add_fx
      EXPORTING
        _fontindex   = 0
        _formatindex = 0
        _extattr     = 65525
        _alignment   = 0
        _borderline  = 0
        _fillpattern = 0
        _extattr1    = '00'
        _reqadd      = 1
      CHANGING
        ind_fx       = ifx.
    DO 2 TIMES.
      CALL METHOD add_fx
        EXPORTING
          _fontindex   = 1
          _formatindex = 0
          _extattr     = 65525
          _alignment   = 0
          _borderline  = 0
          _fillpattern = 0
          _extattr1    = 'F4'
          _reqadd      = 1
        CHANGING
          ind_fx       = ifx.
    ENDDO.
    DO 2 TIMES.
      CALL METHOD add_fx
        EXPORTING
          _fontindex   = 2
          _formatindex = 0
          _extattr     = 65525
          _alignment   = 0
          _borderline  = 0
          _fillpattern = 0
          _extattr1    = 'F4'
          _reqadd      = 1
        CHANGING
          ind_fx       = ifx.
    ENDDO.
    DO 10 TIMES.
      CALL METHOD add_fx
        EXPORTING
          _fontindex   = 0
          _formatindex = 0
          _extattr     = 65525
          _alignment   = 0
          _borderline  = 0
          _fillpattern = 0
          _extattr1    = 'F4'
          _reqadd      = 1
        CHANGING
          ind_fx       = ifx.
    ENDDO.
    CALL METHOD add_fx
      EXPORTING
        _fontindex   = 0
        _formatindex = 0
        _extattr     = 1
        _alignment   = 0
        _borderline  = 0
        _fillpattern = 0
        _extattr1
        ='00'
        _reqadd      = 1
      CHANGING
        ind_fx       = ifx.
    ASSIGN stdstyle_data TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
  ENDMETHOD. "wr_def_formats
  METHOD wr_number .
    DATA: BEGIN OF label_data,
    opcode(2)  TYPE x VALUE '0302',
    length(2)  TYPE x VALUE '0E00',
    row(2)     TYPE x ,
    col(2)     TYPE x ,
    indexxf(2) TYPE x VALUE '0F00',
    END OF label_data,
    lc_float TYPE f.
    FIELD-SYMBOLS: <it_rc> TYPE x.
    CHECK col < 255.
      CHECK row < 64000.
    nint4 = row - 1.
    CALL METHOD inttohex
      EXPORTING
        ilen = 2
      CHANGING
        chex = label_data-row.
    nint4 = col - 1.
    CALL METHOD inttohex
      EXPORTING
        ilen = 1
      CHANGING
        chex = label_data-col.
    IF _indexxf IS NOT INITIAL.
      nint4 = _indexxf.
      CALL METHOD inttohex
        EXPORTING
          ilen = 1
        CHANGING
          chex = label_data-indexxf.
    ENDIF.
    ASSIGN label_data TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
    lc_float = _val.
    CALL METHOD add_number
      EXPORTING
        adnumb = lc_float.
    IF _note IS NOT INITIAL.
      CALL METHOD cellnote( _col = col _row = row _note = _note ).
    ENDIF.
  ENDMETHOD. "wr_number
  METHOD wr_string .
    DATA: BEGIN OF label_data,
    opcode(2)     TYPE x VALUE '0402',                         "'204',
    length(2)     TYPE x VALUE '0800',
    row(2)        TYPE x ,
    col(2)        TYPE x ,
    indexxf(2)    TYPE x VALUE '0F00',                         "! 0-based
    END OF label_data,
    lc_length     TYPE i,
    loc_strlen(2) TYPE x,
    lc_data(1)    TYPE x,
    ichar         TYPE i VALUE 0.
    FIELD-SYMBOLS: <it_rc> TYPE x.
       CHECK col < 255.
      CHECK row < 64000.
    IF _val IS INITIAL.
      CALL METHOD wr_blank( row = row col = col
        _indexxf = _indexxf _note = _note ).
    ELSE.
      nint4 = TRUNC( ichar / 2 ) .
      CALL METHOD inttohex
        EXPORTING
          ilen = 1
        CHANGING
          chex = lc_data.
      lc_length = 8 + STRLEN( _val ) .
      nint4 = STRLEN( _val ).
      CALL METHOD inttohex
        EXPORTING
          ilen = 1
        CHANGING
          chex = loc_strlen.
      nint4 = lc_length.
      CALL METHOD inttohex
        EXPORTING
          ilen = 1
        CHANGING
          chex = label_data-length.
      nint4 = row - 1.
      CALL METHOD inttohex
        EXPORTING
          ilen = 2
        CHANGING
          chex = label_data-row.
      nint4 = col - 1.
      CALL METHOD inttohex
        EXPORTING
          ilen = 1
        CHANGING
          chex = label_data-col.
      IF _indexxf IS NOT INITIAL.
        nint4 = _indexxf.
        CALL METHOD inttohex
          EXPORTING
            ilen = 2
          CHANGING
            chex = label_data-indexxf.
      ENDIF.
      ASSIGN label_data TO <it_rc> CASTING.
      CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
      ASSIGN loc_strlen TO <it_rc> CASTING.
      CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
      CALL METHOD add_string
        EXPORTING
          adtext = _val.
      IF _note IS NOT INITIAL.
        CALL METHOD cellnote( _col = col _row = row _note = _note ).
      ENDIF.
    ENDIF.
  ENDMETHOD. "wr_string
  METHOD wr_tab .
    DATA:
    bl_opcode(2)  TYPE x VALUE '0102',
    bl_length(2)  TYPE x VALUE '0600',
    n_opcode(2)   TYPE x VALUE '0302',
    n_length(2)   TYPE x VALUE '0E00',
    st_opcode(2)  TYPE x VALUE '0402',
    loc_strlen(2) TYPE x,
    lctb_col1     TYPE lvc_t_fcat.
    DATA: BEGIN OF mrc_st,
    opcode(2)     TYPE x ,
    length(2)     TYPE x ,
    row(2)        TYPE x ,
    col(2)        TYPE x ,
    indexxf(2)    TYPE x ,
    END OF mrc_st ,
    lc_border TYPE p.
    DATA:  wa_cols TYPE lvc_s_fcat,
    lc_align    TYPE i,
    alborder    TYPE i,
    topborder   TYPE i,
    douborder   TYPE i,
    lc_date     TYPE d,
    ret_date    TYPE i,
    lc_time     TYPE t,
    ret_time    TYPE f.
    DATA: prc_lines_count TYPE i,
    loc_col        TYPE i,
    dat_row        TYPE i,
    lc_val         TYPE i,
    lc_len         TYPE i,
    cnt_prc        TYPE i,
    procent_prc    TYPE i,
    wr_string      TYPE c,
    ts_string(225) TYPE c,
    wr_numb        TYPE f,
    ref_tab1       TYPE REF TO data.
    DATA:   t_filterl TYPE  lvc_t_fidx.
    FIELD-SYMBOLS: " <l_table1> TYPE STANDARD TABLE,
    <it_rc>      TYPE x,
    <field_from> TYPE ANY,
    <rec_val>    TYPE ANY.
    DESCRIBE TABLE dat_tab LINES dat_row .
    prc_lines_count = dat_row .
    tec_row         = _row.
    loc_col         = _col.
    IF col_tab IS INITIAL.
      GET REFERENCE OF dat_tab INTO ref_tab1.
      CALL METHOD me->get_tabl_inf( EXPORTING dat_tabl = ref_tab1  IMPORTING t_fcat = lctb_col1 ).
    ELSE.
      MOVE col_tab[] TO lctb_col1[].
    ENDIF.
    loc_col         = 0.
    LOOP AT lctb_col1 INTO wa_cols.
      nint4 = loc_col.
      CALL METHOD inttohex
        EXPORTING
          ilen = 2
        CHANGING
          chex = wa_cols-style.
      IF wa_cols-style2 IS INITIAL.
        wa_cols-style2+0(2)  = '0F00'.
      ENDIF.
      MODIFY lctb_col1 FROM wa_cols.
      loc_col = loc_col + 1.
    ENDLOOP.
    loc_col        = _col.
********
*  CALL FUNCTION 'LVC_FILTER'
*    EXPORTING
*      it_fieldcat = lctb_col1[]
*      is_layout   = is_layout
*    TABLES
*      it_data     = dat_tab[]
*    CHANGING
*      ct_filter   = ct_filter_lvc[]
*    EXCEPTIONS
*      no_change   = 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.
**********************************************************************
    LOOP AT dat_tab  ASSIGNING <rec_val> .
      IF sy-batch IS INITIAL. " покажем индикатор выполнения
        IF dat_row > 100.
          cnt_prc       = dat_row.
          procent_prc   = cnt_prc DIV 10.
          cnt_prc       = sy-tabix MOD procent_prc.
          IF cnt_prc    = 1.
          ENDIF.
        ENDIF.
      ENDIF.
**********************************************************************
      nint4          = tec_row - 1.
      CALL METHOD inttohex
        EXPORTING
          ilen = 2
        CHANGING
          chex = mrc_st-row.
**********************************************************************
      loc_col = _col.
      LOOP AT lctb_col1 INTO wa_cols.
        CHECK loc_col < 255.
        CHECK tec_row < 64000.
        IF wa_cols-col_pos IS NOT INITIAL.
          ASSIGN COMPONENT wa_cols-fieldname OF STRUCTURE <rec_val> TO <field_from> .
          IF sy-subrc = 0.
            CASE wa_cols-inttype.
              WHEN 'C' OR 'X'.
                lc_val = 2.
              WHEN 'P' OR 'N' OR 'I' OR 'F' OR 'D' OR 'T'.
                lc_val = 1.
            ENDCASE.
          ELSE.
            lc_val = xlsblank.
          ENDIF.
        ELSE.
          lc_val = xlsblank.
        ENDIF.
        mrc_st-col     =   wa_cols-style.
        mrc_st-indexxf+0(1) =   wa_cols-style2+1(1).
        mrc_st-indexxf+1(1) =   wa_cols-style2+0(1).
        IF wa_cols-inttype = 'D' AND lc_val = xlsnumber.
          lc_date = <field_from>.
          CALL METHOD: me->get_xldate
          EXPORTING
          sapdate = lc_date
          CHANGING
          xldat = ret_date.
          ASSIGN ret_date TO <field_from> .
        ENDIF.
        IF lc_val = xlsnumber AND wa_cols-inttype = 'T'.
          lc_time = <field_from>.
          CALL METHOD: me->get_xltime
          EXPORTING
          saptime = lc_time
          CHANGING
          xltime = ret_time.
          ASSIGN ret_time TO <field_from> .
        ENDIF.
        IF <field_from> IS INITIAL.
          lc_val = xlsblank.
        ENDIF.
        mrc_st-col = wa_cols-style+0(2) .
        CASE lc_val.
          WHEN xlsnumber.
            mrc_st-opcode = n_opcode.
            mrc_st-length = n_length.
          WHEN xlsblank.
            mrc_st-opcode = bl_opcode.
            mrc_st-length = bl_length.
          WHEN xlstext.
            ts_string = <field_from>.
            SHIFT ts_string RIGHT DELETING TRAILING ' '.
            TRANSLATE ts_string USING ' 0'.
            IF ts_string CO '1234567890'.
              lc_val        = xlsnumber.
              wr_numb       = <field_from>.
              mrc_st-opcode = n_opcode.
              mrc_st-length = n_length.
            ELSE.
              wr_string = <field_from>.
              lc_len = STRLEN( wr_string ).
              nint4 = lc_len + 8.
              mrc_st-opcode = st_opcode.
              CALL METHOD inttohex
                EXPORTING
                  ilen = 1
                CHANGING
                  chex = mrc_st-length.
            ENDIF.
            ASSIGN mrc_st TO <it_rc> CASTING.
            IF lc_val NE xlsnumber.
              CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
            ENDIF.
        ENDCASE.

        CASE lc_val.
          WHEN xlstext.
            nint4 = lc_len.
            CALL METHOD inttohex
              EXPORTING
                ilen = 1
              CHANGING
                chex = loc_strlen.
            CONCATENATE bufff loc_strlen INTO bufff IN BYTE MODE.
            CALL METHOD add_string
              EXPORTING
                adtext = wr_string.
          WHEN xlsnumber.
            MOVE <field_from> TO wr_numb .
*            CALL METHOD: me->wr_number( _val = wr_numb col = loc_col row = tec_row _indexxf = mrc_st-col  ).
            ASSIGN mrc_st TO <it_rc> CASTING.
            CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
            CALL METHOD add_number
              EXPORTING
                adnumb = wr_numb.
          WHEN xlsblank.
            mrc_st-opcode = bl_opcode.
            mrc_st-length = bl_length.
            ASSIGN mrc_st TO <it_rc> CASTING.
            CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
        ENDCASE.
        loc_col = loc_col + 1.
      ENDLOOP.
      tec_row = tec_row + 1 .
    ENDLOOP.
    loc_col = _col.
    LOOP AT lctb_col1 INTO wa_cols.
      IF wa_cols-outputlen NE 0.
        set_col_width( f_col = loc_col e_col = loc_col width = wa_cols-outputlen ).
      ENDIF.
      loc_col = loc_col + 1.
    ENDLOOP.
    CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
      EXPORTING
        percentage = 100
        text       = 'Выгрузка закончена'.
  ENDMETHOD. "write_tab
  METHOD get_xldate.
    DATA: deltdate TYPE dats VALUE '18991230'.
    CLEAR xldat.
    IF sapdate IS NOT INITIAL.
      xldat = sapdate - deltdate.
    ELSE.
      xldat = sapdate .
    ENDIF.
  ENDMETHOD. "get_xldate
  METHOD get_xltime.
    DATA: "hh TYPE i,
    ofs TYPE i,
    del TYPE i VALUE 24 .
    CLEAR: ofs ,xltime .
    IF saptime IS NOT INITIAL.
      DO 3 TIMES.
        xltime = xltime + saptime+ofs(2) / del.
        ofs = ofs + 2.
        del = del * 60 .
      ENDDO.
    ELSE.
      xltime = saptime.
    ENDIF.
  ENDMETHOD. "get_xltime
  METHOD get_tabl_inf .
    DATA: lcl_table_descr TYPE REF TO cl_abap_tabledescr,
    lcl_struc_descr       TYPE REF TO cl_abap_structdescr,
    lcl_f_descr           TYPE REF TO cl_abap_elemdescr, "CL_ABAP_DATADESCR,
    ret_components        TYPE abap_compdescr_tab,
    components            TYPE abap_compdescr_tab,
    ls_dd04t              TYPE dd04t,
    lc_tabix              TYPE i VALUE 1,
    lt_dd04t              TYPE STANDARD TABLE OF dd04t   ,
    ls_fcatalog           TYPE lvc_s_fcat,
    wa_ret_components     TYPE LINE OF abap_compdescr_tab.
    CLEAR: lcl_table_descr , lcl_struc_descr , ret_components ,
        wa_ret_components .
    REFRESH: lt_dd04t[], components[].", p_field_list[].
    DATA: new_line1 TYPE REF TO data."    rel_name        TYPE ddobjname.
    FIELD-SYMBOLS:
    <fs>      TYPE STANDARD TABLE,
    <l_line1> TYPE ANY,
    <fls>     TYPE ANY.
    ASSIGN dat_tabl->* TO <fs>.
    CREATE DATA new_line1 LIKE LINE OF <fs>.
    ASSIGN new_line1->* TO <l_line1>.
    lcl_table_descr     ?= cl_abap_typedescr=>describe_by_data_ref( dat_tabl ).
    lcl_struc_descr     ?= lcl_table_descr->get_table_line_type( ).
    ls_fcatalog-rollname = lcl_struc_descr->get_relative_name( ).
    IF lcl_struc_descr->is_ddic_type( ) = abap_true OR ls_fcatalog-rollname IS NOT INITIAL.
      CALL FUNCTION 'LVC_FIELDCATALOG_MERGE'
        EXPORTING
          i_structure_name       = ls_fcatalog-rollname
        CHANGING
          ct_fieldcat            = t_fcat
        EXCEPTIONS
          inconsistent_interface = 1
          program_error          = 2
          OTHERS                 = 3.
    ELSE.
      ret_components = lcl_struc_descr->components.
      LOOP AT ret_components INTO wa_ret_components .
        CLEAR ls_fcatalog.
        ls_fcatalog-fieldname = wa_ret_components-name.
        ASSIGN COMPONENT wa_ret_components-name OF STRUCTURE <l_line1> TO <fls> .
        lcl_f_descr         ?= cl_abap_elemdescr=>describe_by_data( <fls> ).
        ls_fcatalog-rollname = lcl_f_descr->get_relative_name( ).
        ls_fcatalog-col_pos  = lc_tabix.
**********************************************************************
        ls_fcatalog-decimals  =  wa_ret_components-decimals.
        ls_fcatalog-inttype   =  wa_ret_components-type_kind.
        APPEND ls_fcatalog TO t_fcat.
        CLEAR  ls_fcatalog.
        lc_tabix = lc_tabix + 1 .
      ENDLOOP.
      CHECK t_fcat[] IS NOT INITIAL.
      SELECT * FROM dd04t INTO TABLE lt_dd04t
       FOR ALL ENTRIES IN t_fcat
      WHERE rollname = t_fcat-rollname " <-- !!!
      AND ddlanguage = sy-langu.
      SORT lt_dd04t BY rollname.
      LOOP AT t_fcat INTO ls_fcatalog.
        READ TABLE lt_dd04t INTO ls_dd04t WITH KEY rollname = ls_fcatalog-rollname BINARY SEARCH.
        IF sy-subrc = 0.
          ls_fcatalog-reptext   =   ls_dd04t-reptext.
          ls_fcatalog-scrtext_s =   ls_dd04t-scrtext_s.
          ls_fcatalog-scrtext_m =   ls_dd04t-scrtext_m.
          ls_fcatalog-scrtext_l =   ls_dd04t-scrtext_l.
        ENDIF.
        MODIFY t_fcat FROM ls_fcatalog.
      ENDLOOP.
    ENDIF.
  ENDMETHOD. "REFRESH_param
ENDCLASS. "zcl_dexcel IMPLEMENTA.

*&---------------------------------------------------------------------*
*& Include ZCL_XLWRITE_DAT *
*&---------------------------------------------------------------------*
CONSTANTS: xlsinteger TYPE i VALUE 0,
xlsnumber TYPE i VALUE 1,
xlstext TYPE i VALUE 2,
xlsblank TYPE i VALUE 3,
xlsref TYPE i VALUE 100,
xlsgeneralalign TYPE i VALUE 0,
xlsleftalign TYPE i VALUE 1,
xlscentrealign TYPE i VALUE 2,
xlsrightalign TYPE i VALUE 3,
xlsfillcell TYPE i VALUE 4,
xlsleftborder TYPE i VALUE 8,
xlsrightborder TYPE i VALUE 16,
xlstopborder TYPE i VALUE 32,
xlsbottomborder TYPE i VALUE 64,
xlsshaded TYPE i VALUE 128,
xlsfont0 TYPE i VALUE 0,
xlsfont1 TYPE i VALUE 64,
xlsfont2 TYPE i VALUE 128,
xlsfont3 TYPE i VALUE 192,
xlsnormal TYPE i VALUE 0,
xlslocked TYPE i VALUE 64,
xlshidden TYPE i VALUE 128,
xlsleftmargin TYPE i VALUE 38,
xlsrightmargin TYPE i VALUE 39,
xlstopmargin TYPE i VALUE 40,
xlsbottommargin TYPE i VALUE 41,
xlsnofrm TYPE i VALUE 0,
xlsbold TYPE i VALUE 1,
xlsitalic TYPE i VALUE 2,
xlsunderline TYPE i VALUE 4,
xlsstrikeout TYPE i VALUE 8,
            xlssum                  TYPE i VALUE 4, "  ! Сумма (формула)
            xlsavr                  TYPE i VALUE 5, "  ! Среднее (формула)
            xlsdev                  TYPE i VALUE 6, "  ! Деление
            xlsmul                  TYPE i VALUE 5, "  ! Умножение
            xlssub                  TYPE i VALUE 4, "  ! Вычитание
            xlsadd                  TYPE i VALUE 3. "  ! Сложение
***
TYPES: BEGIN OF ty_cols,
header(100) TYPE c,
fname TYPE fdname,
width TYPE i,
fieldno TYPE i,
datatype TYPE i,
align TYPE i,
fmtindex TYPE i,
fontindex TYPE i ,
convers TYPE char01,
round TYPE i,
do_sum TYPE i,
vne_str TYPE char01,
locked TYPE i,
* BORDER TYPE I,
END OF ty_cols.
TYPES: ty_cols_t TYPE STANDARD TABLE OF ty_cols.
***
DATA: bufff(65000) TYPE x .
DATA: nint4 TYPE int4 ,
on_serv TYPE xfeld,
xlsbuffer LIKE TABLE OF bufff ,
conv_out TYPE REF TO cl_abap_conv_out_ce,
xlsfsize TYPE i,
filename TYPE string VALUE 'd:\test.xls', " шь  Їрщыр
fontar(9) TYPE c VALUE 'Arial Cyr', " дефолтные шрифты
fontnr(25) TYPE c VALUE 'Times New Roman',
BEGIN OF wa_brrows ,
row TYPE i,
END OF wa_brrows ,
hpbrrows LIKE TABLE OF wa_brrows, " Табличка PAGE-breaks
BEGIN OF wa_frm ,
nfr TYPE i,
nfrm(36) TYPE c,
END OF wa_frm,
tb_frm LIKE TABLE OF wa_frm . " Табличка дефолтных форматов
FIELD-SYMBOLS: <it_rc> TYPE x.
DATA: wa_cols TYPE ty_cols,
BEGIN OF wa_params,
ptype TYPE i, " Для формул xlsinteger, xlsnumber, xlsText,xlsRef
value TYPE f,
col TYPE i,
colrel TYPE i,
row TYPE i,
rowrel TYPE i,
separator(1) TYPE c,
svalue(80) TYPE c,
END OF wa_params,
parameters LIKE TABLE OF wa_params.

*&---------------------------------------------------------------------*
*&  Include           ZCL_DEXCEL_F                                     *
*&---------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*& Include zcl_dexcel_F *
*&---------------------------------------------------------------------*
*-оригинал  DirectExcel v 1.5
* (с) Oleg A. Rudenko, 2004-05. Moscow, Russia.
* перевод Nicks
* CLASS zcl_dexcel DEFINITION
*&---------------------------------------------------------------------*
CLASS zcl_dexcel DEFINITION.
  PUBLIC SECTION.
    DATA:
    conv_out TYPE REF TO cl_abap_conv_out_ce,
    gl_rev TYPE i,
    BEGIN OF gs_format,
      font(25)  TYPE c,
      sizef     TYPE i,
      datform(25)   TYPE c,
      timeform(25)  TYPE c,
      locked        TYPE i,
      border        TYPE i,
      END OF gs_format,
    converter TYPE REF TO cl_abap_conv_obj.
    METHODS set_border
    IMPORTING
    !_rightbor  TYPE i
    !_rightst   TYPE i
    !_bottombor TYPE i
    !_bottomst  TYPE i
    !_leftbor   TYPE i
    !_leftst    TYPE i
    !_topbor    TYPE i
    !_topst     TYPE i
    CHANGING
    !borderst TYPE p .
    METHODS set_rowhi
    IMPORTING
    !_row     TYPE i
    !_height  TYPE i
    !_indexxf TYPE i .
    METHODS set_psetup
    IMPORTING
    !_options   TYPE i
    !_papersize TYPE i
    !_scale     TYPE i
    !_pagestart TYPE i
    !_wfit      TYPE i
    !_hfit      TYPE i .
    METHODS add_fx
    IMPORTING
    !_fontindex   TYPE i
    !_formatindex TYPE i
    !_extattr     TYPE i
    !_alignment   TYPE i
    !_borderline  TYPE p
    !_fillpattern TYPE i
    !_extattr1    TYPE x
    !_reqadd      TYPE i
    CHANGING
    !ind_fx TYPE i .
    METHODS create_xls
    IMPORTING
    !f_name    TYPE string
    !def_font  TYPE c
    !t_out     TYPE c .
    METHODS wr_string
    IMPORTING
    !row       TYPE i
    !col       TYPE i
    !_val      TYPE c
    !_indexxf  TYPE i
    !_note     TYPE c OPTIONAL.
    METHODS wr_number
    IMPORTING
    !row       TYPE i
    !col       TYPE i
    !_val      TYPE f
    !_indexxf  TYPE i
    !_note     TYPE c OPTIONAL.
    METHODS wr_blank
    IMPORTING
    !row       TYPE i
    !col       TYPE i
    !_indexxf  TYPE i
    !_note     TYPE c OPTIONAL.
    METHODS wr_tab " Вывести таблицу начиная с ячейки
    IMPORTING
    !col_tab   TYPE lvc_t_fcat " шаблон вывода - перечень и порядок
    !dat_tab   TYPE STANDARD TABLE
    !_row      TYPE i " строка
    !_col      TYPE i " столбец
    RETURNING
    value(tec_row) TYPE i . " возвращаемая текущая строка
    METHODS set_defrowhi
    IMPORTING
    !height   TYPE i .
    METHODS ins_hpbreak
    IMPORTING
    !col TYPE i .
    METHODS ins_vpbreak
    IMPORTING
    !row TYPE i .
    METHODS set_col_width
    IMPORTING
    !f_col        TYPE i
    !e_col        TYPE i
    !width        TYPE n .
    METHODS close_xls .
    METHODS add_font
    IMPORTING
    !_fontname    TYPE c
    !_fontheight  TYPE i
    !_fontattr    TYPE i
    !_fontcolor   TYPE x
    !_add         TYPE i
    CHANGING
    value(ind_font) TYPE i .
    METHODS add_format
    IMPORTING
    !_picture     TYPE c .
    METHODS protectsheet
    IMPORTING
    !prot TYPE i .
    METHODS get_xldate " перевести дату в формат Excel
    IMPORTING
    !sapdate TYPE dats
    CHANGING
    value(xldat) TYPE i .
    METHODS get_xltime " перевести время Excel
    IMPORTING
    !saptime TYPE t
    CHANGING
    value(xltime) TYPE f .
    METHODS get_tabl_inf " сформировать шаблон вывода по структуре
    IMPORTING dat_tabl TYPE REF TO data
    EXPORTING t_fcat TYPE lvc_t_fcat.


  PRIVATE SECTION.
    CONSTANTS xlsleftalign TYPE i VALUE 1.
    DATA:  nint4 TYPE int4
    , bufff      TYPE xstring
    ,ret_xstring TYPE xstring
    ,filename    TYPE string VALUE 'D:\test1.xls'
    ,deffont     TYPE i VALUE 0
    , defxf(1)   TYPE x .
    CONSTANTS xlsnumber       TYPE i VALUE 1.
    CONSTANTS xlstext         TYPE i VALUE 2.
    CONSTANTS xlsblank        TYPE i VALUE 3.
    CONSTANTS xlsref          TYPE i VALUE 100.
    CONSTANTS xlsgeneralalign TYPE i VALUE 0.
    CONSTANTS xlscentrealign  TYPE i VALUE 2.
    CONSTANTS xlsrightalign   TYPE i VALUE 3.
    CONSTANTS xlsfillcell     TYPE i VALUE 4.
    CONSTANTS xlsrightborder  TYPE i VALUE 16.
    CONSTANTS xlsleftborder   TYPE i VALUE 8.
    CONSTANTS xlstopborder    TYPE i VALUE 32.
    DATA:
    BEGIN OF wa_fontslist,
    id        TYPE i , " Индекс шрифта
    name(100) TYPE c, " Наименование шрифта
    size      TYPE i VALUE 10, " Размер шрифта
    attr      TYPE i , " Аттрибут шрифта
    color     TYPE i , " Цвет шрифта
    END OF wa_fontslist .
    DATA:
    tb_fontslist LIKE TABLE OF wa_fontslist .
    DATA:
    BEGIN OF wa_xflist,
    id TYPE i , " Индекс стиля
    fontndx(1)     TYPE x , " Индекс шрифта
    fmtndx(1)      TYPE x , " Индекс формата вывода
    extattr(2)     TYPE x , " Дополнительные аттрибуты
    extattr1(1)    TYPE x , " Дополнительные аттрибуты
    alignment(1)   TYPE x , " Выравнивание
    borderstyle(2) TYPE x , " Стиль окантовки
    fillpattern(4) TYPE x , " Стиль закрашивания
    END OF wa_xflist .
    DATA:
    tb_xflist LIKE TABLE OF wa_xflist .
    DATA:
    BEGIN OF wa_formatslist,
    id(1) TYPE x ," Индекс формата
    picture(50) TYPE c ," Сам формат
    END OF wa_formatslist .
    CONSTANTS: xlsfont0 TYPE i VALUE 0.
    DATA: tb_formatslist LIKE TABLE OF wa_formatslist .
    DATA:
    BEGIN OF wa_pbreaks,
    position TYPE i ," Позиция (колонка или строка)
    END OF wa_pbreaks .
    DATA: s_fcat TYPE lvc_s_fcat.
    DATA: hp_breaks LIKE TABLE OF wa_pbreaks .
    DATA: vp_breaks LIKE TABLE OF wa_pbreaks .
*   DATA writetable_break TYPE i VALUE -1 .
    DATA isopen TYPE i .
    DATA: tfontlist LIKE TABLE OF wa_fontslist ,
          txflist   LIKE TABLE OF wa_xflist ,
          tfmtlist LIKE TABLE OF wa_formatslist,
          lt_fcat  TYPE  lvc_t_fcat ,
          deffontname(30) TYPE c .
    DATA:
    BEGIN OF wa_frm ,
    nfr TYPE i,
    nfrm(36) TYPE c,
    END OF wa_frm .
    DATA: tb_frm LIKE TABLE OF wa_frm .
    DATA:   BEGIN OF wa_fx ,
    nfx     TYPE i,
    nst     TYPE i,
    END OF wa_fx .
    DATA: tb_fx LIKE TABLE OF wa_fx .
    DATA: typeout(2) TYPE c ,
    rowcurrent TYPE i ,
    nfr TYPE c .
    METHODS get_borderline
    IMPORTING
    !_border TYPE p
    CHANGING
    !borderxeh TYPE x .
    METHODS cellnote
    IMPORTING
    !_col TYPE i
    !_row TYPE i
    !_note TYPE c .
    METHODS add_string
    IMPORTING
    !adtext TYPE c .
    METHODS add_number
    IMPORTING
    !adnumb TYPE f .
    METHODS donl_file
    IMPORTING
    !app_mode TYPE char01 .
    METHODS wr_def_formats .
    METHODS inttohex
    IMPORTING
    !ilen TYPE i
    CHANGING
    !value(chex) TYPE x .
ENDCLASS. "zcl_dexcel DEFINITION
*----------------------------------------------------------------------*
*       CLASS zcl_dexcel IMPLEMENTATION
*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
CLASS zcl_dexcel IMPLEMENTATION.
  METHOD add_font .
    DATA: BEGIN OF font_data,
    opcode(2)      TYPE x VALUE '3102',
    length(2)      TYPE x VALUE '1000',
    fontheight(2)  TYPE x VALUE '2000',                     "! 1/20
    fontattr(2)    TYPE x ," VALUE fontattr-faauto,
    fontcolor(2)   TYPE x ," VALUE fontcolor-fcauto,
    fontnamelen(1) TYPE x,
    END OF font_data,
    loc_fontname   TYPE string,
    loc_font       LIKE tb_fontslist,
    lc_tmp         TYPE xstring,
    loc_tmp        TYPE int4,
    loc_rec        TYPE i.
    FIELD-SYMBOLS: <lc_fs> TYPE x.
    IF _fontname IS NOT INITIAL.
      loc_fontname = _fontname.
    ELSE.
      loc_fontname = deffontname.
    ENDIF.
    wa_fontslist-name = loc_fontname.
    IF _fontheight IS NOT INITIAL.
      wa_fontslist-size = _fontheight.
    ELSE.
      wa_fontslist-size = 10.
    ENDIF.
    IF _fontattr IS NOT INITIAL .
      wa_fontslist-attr = _fontattr.
      nint4 = wa_fontslist-attr.
      CALL METHOD inttohex
        EXPORTING
          ilen = 1
        CHANGING
          chex = font_data-fontattr.
    ENDIF.
    IF _fontcolor IS NOT INITIAL .
      font_data-fontcolor = _fontcolor.
    ENDIF.
    READ TABLE tb_fontslist INTO wa_fontslist WITH KEY name = loc_fontname size =
    _fontheight attr = _fontattr
    color = _fontcolor BINARY SEARCH.
    IF sy-subrc NE 0 OR _add = 1 .
      DESCRIBE TABLE tb_fontslist LINES loc_rec.
      IF loc_rec = 255 .
      ELSE.
        nint4 = STRLEN( wa_fontslist-name ).
        CALL METHOD inttohex
          EXPORTING
            ilen = 1
          CHANGING
            chex = font_data-fontnamelen.
        nint4 = 7 + nint4.
        CALL METHOD inttohex
          EXPORTING
            ilen = 1
          CHANGING
            chex = font_data-length.
        nint4 = wa_fontslist-size * 20.
        CALL METHOD inttohex
          EXPORTING
            ilen = 2
          CHANGING
            chex = font_data-fontheight.
        ASSIGN font_data TO <lc_fs> CASTING.
        CONCATENATE bufff <lc_fs> INTO bufff IN BYTE MODE.
        nint4 = STRLEN( loc_fontname ).
        CALL FUNCTION 'SCMS_STRING_TO_XSTRING'
          EXPORTING
            text   = loc_fontname
          IMPORTING
            buffer = ret_xstring "* MIMETYPE = ' '
          EXCEPTIONS
            failed = 1
            OTHERS = 2.
        IF sy-subrc <> 0.
        ENDIF.
        CONCATENATE bufff ret_xstring INTO bufff IN BYTE MODE.
        IF loc_rec < 5.
        ELSE.
          loc_rec = loc_rec + 1.
        ENDIF.
        wa_fontslist-id = loc_rec.
        ind_font = loc_rec.
        APPEND wa_fontslist TO tb_fontslist.
      ENDIF.
    ELSE.
      ind_font = wa_fontslist-id .
    ENDIF.
  ENDMETHOD. "add_font
  METHOD add_format .
    DATA: BEGIN OF format_data,
    opcode(2) TYPE x VALUE '1E04',
    length(2) TYPE x VALUE '0300',
    formatindex(2) TYPE x VALUE '0000',
    formatlen(1) TYPE x,
    END OF format_data,
    i TYPE i,
    l_addsize TYPE i VALUE 3,
    l_saverec(1000) .
    FIELD-SYMBOLS: <lc_fs> TYPE x,
    <it_rc> TYPE x.
    CLEAR wa_formatslist.
    IF _picture NE space.
      READ TABLE tb_formatslist INTO wa_formatslist WITH KEY picture = _picture .
      IF sy-subrc NE 0 ."OR tb_formatslist IS INITIAL .
        DESCRIBE TABLE tb_formatslist LINES i.
        IF i = 255.
        ELSE.
          wa_formatslist-id = i.
          format_data-formatlen = STRLEN( _picture ).
          nint4 = format_data-formatlen + l_addsize.
          CALL METHOD inttohex
            EXPORTING
              ilen = 1
            CHANGING
              chex = format_data-length.
          ASSIGN format_data TO <it_rc> CASTING.
          CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
          ASSIGN _picture TO <lc_fs> CASTING TYPE x. " Перевод в HEX вид
          CONCATENATE bufff <lc_fs> INTO bufff IN BYTE MODE.
          wa_formatslist-picture = _picture .
          APPEND wa_formatslist TO tb_formatslist.
        ENDIF.
      ENDIF.
    ENDIF.
  ENDMETHOD. "ADD_FORMAT
  METHOD add_number .
    DATA: lc TYPE i,
    oct TYPE i,
    re_dat(8) TYPE x.
    FIELD-SYMBOLS <fs> TYPE x.
    ASSIGN adnumb TO <fs> CASTING TYPE x.
    CLEAR: re_dat.
    DO 8 TIMES.
      lc = 8 - sy-index. " читать от конца
      oct = sy-index - 1. " писать от начала
      re_dat+oct(1) = <fs>+lc(1).
    ENDDO.
    CONCATENATE bufff re_dat INTO bufff IN BYTE MODE.
  ENDMETHOD. "ADD_NUMBER
  METHOD add_string .
    DATA: lc TYPE i,
    buffer   TYPE xstring,
    s_string TYPE string,
    x_string TYPE xstring.
    FIELD-SYMBOLS: <ctext> TYPE c.
    CLEAR buffer.
    s_string = adtext.
    CALL METHOD converter->convert
      EXPORTING
        inbuff         = s_string
        inbufflg       = 0
        outbufflg      = 0
      IMPORTING
        outbuff        = ret_xstring
      EXCEPTIONS
        internal_error = 1
        OTHERS         = 2.
    IF sy-subrc <> 0.
      CASE sy-subrc.
        WHEN 1.
        WHEN 2.
      ENDCASE.
    ENDIF.
    CONCATENATE bufff ret_xstring INTO bufff IN BYTE MODE.
  ENDMETHOD. "ADD_STRING
  METHOD add_fx .
    DATA: BEGIN OF xf_data,
    opcode(2)       TYPE x VALUE '4304',
    length(2)       TYPE x VALUE '0C',
    fontindex(1)    TYPE x VALUE '',
    formatindex(1)  TYPE x VALUE '',
    extattr(2)      TYPE x VALUE '01',
    alignment(1)    TYPE x VALUE '20',
    extattr1(1)     TYPE x VALUE '00',
    fillpattern(2)  TYPE x VALUE '00CE',
    borderstyle(4)  TYPE x,
    END OF xf_data,
    countrec          TYPE i,
    fillpattern(2)    TYPE x,
    loc_patt(2)       TYPE x,
    style(2)          TYPE x,
    fillcolor(2)      TYPE x,
    patterncolor(2)   TYPE x,
    nlenhex           TYPE i,
    pos_from          TYPE i,
    pos_to            TYPE i,
    pos_col           TYPE i,
    loc_borderstyle(3) TYPE x,
    loc_xf            LIKE wa_xflist.
    FIELD-SYMBOLS: <lc_fs> TYPE x.
    IF isopen IS NOT INITIAL. " OR (Self.XFList &= Null)
      MOVE _fillpattern TO fillpattern.
      MOVE fillpattern+1(1) TO pos_to .
      IF pos_to > 1.
        loc_patt = '07C0'.
        fillcolor = fillpattern BIT-AND loc_patt.
        IF fillcolor EQ '0000'.
          fillcolor = '0640'.
        ENDIF.
        MOVE fillcolor TO pos_from .
        loc_patt = 'F800'.
        patterncolor = fillpattern BIT-AND loc_patt.
        MOVE patterncolor TO pos_col.
        IF patterncolor EQ '0000'.
          patterncolor = '00C0'.
        ENDIF.
        pos_from = pos_from * 32.
        pos_col = pos_col / 32.
        nint4 = pos_to + pos_from + pos_col.
        MOVE nint4 TO loc_patt.
        loc_xf-fillpattern+0(1) = loc_patt+1(1).
        loc_xf-fillpattern+1(1) = loc_patt+0(1).
      ENDIF.
    ENDIF.
    nint4 = _fontindex.
    CALL METHOD inttohex
      EXPORTING
        ilen = 1
      CHANGING
        chex = loc_xf-fontndx.
    nint4 = _formatindex.
    CALL METHOD inttohex
      EXPORTING
        ilen = 1
      CHANGING
        chex = loc_xf-fmtndx.
    nint4 = _extattr.
    CALL METHOD inttohex
      EXPORTING
        ilen = 1
      CHANGING
        chex = loc_xf-extattr.
    IF _alignment IS NOT INITIAL.
      nint4 = _alignment.
      CALL METHOD inttohex
        EXPORTING
          ilen = 1
        CHANGING
          chex = loc_xf-alignment.
    ELSE.
      loc_xf-alignment = '20'.
    ENDIF.
    loc_xf-extattr1 = _extattr1 .
    READ TABLE txflist INTO wa_xflist WITH KEY fontndx = loc_xf-fontndx fmtndx = loc_xf-fmtndx
    extattr = loc_xf-extattr extattr1 = loc_xf-extattr1 alignment = loc_xf-alignment
    borderstyle = loc_xf-borderstyle fillpattern = loc_xf-fillpattern. " BINARY SEARCH.
    IF sy-subrc NE 0 OR _reqadd = 1 .
      DESCRIBE TABLE txflist LINES countrec.
      CLEAR wa_xflist.
      wa_xflist-fontndx     = loc_xf-fontndx.
      wa_xflist-fmtndx      = loc_xf-fmtndx.
      wa_xflist-extattr     = loc_xf-extattr.
      wa_xflist-extattr1    = loc_xf-extattr1.
      wa_xflist-alignment   = loc_xf-alignment.
      wa_xflist-borderstyle = loc_xf-borderstyle.
      xf_data-fillpattern   = loc_xf-fillpattern.
      xf_data-fontindex     = loc_xf-fontndx.
      xf_data-formatindex   = loc_xf-fmtndx.
      xf_data-extattr       = loc_xf-extattr.
      xf_data-extattr1      = loc_xf-extattr1.
      xf_data-alignment     = loc_xf-alignment.
      IF _borderline NE 0.
        CALL METHOD get_borderline
          EXPORTING
            _border   = _borderline
          CHANGING
            borderxeh = xf_data-borderstyle.
      ELSE.
        xf_data-borderstyle = loc_xf-borderstyle.
      ENDIF.
      ASSIGN xf_data TO <lc_fs> CASTING.
      CONCATENATE bufff <lc_fs> INTO bufff IN BYTE MODE.
      ind_fx = countrec.
      wa_xflist-id = countrec.
      APPEND wa_xflist TO txflist.
    ELSE.
      ind_fx = wa_xflist-id.
    ENDIF.
  ENDMETHOD. "add_fx
  METHOD cellnote .
    DATA: BEGIN OF cellnote_data,
    opcode(2)  TYPE x VALUE 'C001',
    length(2)  TYPE x VALUE '0600',
    row(2)     TYPE x ,
    col(2)     TYPE x ,
    notelen(2) TYPE x ,
    END OF cellnote_data.
    FIELD-SYMBOLS: <it_rc> TYPE x.
    nint4 = 28.
    CALL METHOD inttohex
      EXPORTING
        ilen = 1
      CHANGING
        chex = cellnote_data-opcode.
    nint4 = _row - 1.
    CALL METHOD inttohex
      EXPORTING
        ilen = 1
      CHANGING
        chex = cellnote_data-row.
    nint4 = _col - 1.
    CALL METHOD inttohex
      EXPORTING
        ilen = 1
      CHANGING
        chex = cellnote_data-col.
    nint4 = 6 + STRLEN( _note ).
    CALL METHOD inttohex
      EXPORTING
        ilen = 1
      CHANGING
        chex = cellnote_data-length.
    nint4 = STRLEN( _note ).
    CALL METHOD inttohex
      EXPORTING
        ilen = 2
      CHANGING
        chex = cellnote_data-notelen.
    ASSIGN cellnote_data TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
    CALL METHOD add_string( _note ) .
  ENDMETHOD. "cellnote
  METHOD close_xls .
    DATA: BEGIN OF end_file_data,
    opcode(2) TYPE x VALUE '0A',
    length(2) TYPE x VALUE '00',
    END OF end_file_data,
    BEGIN OF v_breaks,
    opcode(2) TYPE x VALUE '1A',
    length(2) TYPE x ,
    pages(2)  TYPE x ,
    END OF v_breaks,
    BEGIN OF h_breaks,
    opcode(2) TYPE x VALUE '1B',
    length(2) TYPE x VALUE '02',
    pages(2)  TYPE x ,
    END OF h_breaks,
    h_length   TYPE i VALUE 2,
    num_breaks TYPE i VALUE 0,
    t_short(2) TYPE x,
    t_len TYPE i ,
    pagesarray TYPE xstring.
    FIELD-SYMBOLS: <it_rc> TYPE x.
    DESCRIBE TABLE hp_breaks LINES num_breaks.
    IF num_breaks NE 0 AND isopen NE 0 .
      nint4 = num_breaks.
      CALL METHOD inttohex
        EXPORTING
          ilen = 2
        CHANGING
          chex = h_breaks-pages.
      LOOP AT hp_breaks INTO wa_pbreaks.
        nint4 = wa_pbreaks-position.
        CALL METHOD inttohex
          EXPORTING
            ilen = 2
          CHANGING
            chex = t_short.
        CONCATENATE pagesarray t_short INTO pagesarray IN BYTE MODE.
        t_len = t_len + 2.
      ENDLOOP.
      h_length = h_length + num_breaks * 2.
      nint4 = h_length.
      CALL METHOD inttohex
        EXPORTING
          ilen = 1
        CHANGING
          chex = h_breaks-length.
      ASSIGN h_breaks TO <it_rc> CASTING.
      CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
      CONCATENATE bufff pagesarray INTO bufff IN BYTE MODE.
    ENDIF .
    DESCRIBE TABLE vp_breaks LINES num_breaks.
    IF num_breaks NE 0 AND isopen NE 0 .
      t_len = 0.
      h_length = 2.
      CLEAR pagesarray.
      SORT vp_breaks BY position.
      nint4 = num_breaks.
      CALL METHOD inttohex
        EXPORTING
          ilen = 2
        CHANGING
          chex = v_breaks-pages.
      LOOP AT vp_breaks INTO wa_pbreaks.
        nint4 = wa_pbreaks-position.
        CALL METHOD inttohex
          EXPORTING
            ilen = 2
          CHANGING
            chex = t_short.
        CONCATENATE pagesarray t_short INTO pagesarray IN BYTE MODE.
        t_len = t_len + 2.
      ENDLOOP.
      h_length = h_length + num_breaks * 2.
      nint4 = h_length.
      CALL METHOD inttohex
        EXPORTING
          ilen = 1
        CHANGING
          chex = v_breaks-length.
      ASSIGN v_breaks TO <it_rc> CASTING.
      CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
      CONCATENATE bufff pagesarray INTO bufff IN BYTE MODE.
    ENDIF .
    ASSIGN end_file_data TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
    CALL METHOD donl_file
      EXPORTING
        app_mode = 'X'.
    IF typeout = 'AC'. " Выгрузка на AP сервер
      CLOSE DATASET filename .
    ENDIF.
  ENDMETHOD. "close_xls
  METHOD create_xls .
    DATA:
    lc_tx(2) TYPE c,
    def_fonth TYPE i,
    def_fontst(30) TYPE c,
    hkey_current_user TYPE i VALUE 1,
    BEGIN OF beg_file_data,
    opcode(2)   TYPE x VALUE '0904',
    length(2)   TYPE x VALUE '0600',
    version(2)  TYPE x VALUE '0000',
    subtype(2)  TYPE x VALUE '1000',
    buildid(2)  TYPE x VALUE '5E1F',
    END OF beg_file_data,
    BEGIN OF codepage_data,
    opcode(2)   TYPE x VALUE '4200',
    length(2)   TYPE x VALUE '0200',
    codepage(2) TYPE x VALUE 'E304',
    END OF codepage_data,
    BEGIN OF calc_mode_data,
    opcode(2)   TYPE x VALUE '0D00',
    length(2)   TYPE x VALUE '0200',
    calcmode(2) TYPE x VALUE '0100',
    END OF calc_mode_data,
    BEGIN OF wind_data,
    opcode(2)   TYPE x VALUE '3D00',
    length(2)   TYPE x VALUE '0A00',
    xpos(2)     TYPE x VALUE '0000',
    ypos(2)     TYPE x VALUE '0000',
    width(2)    TYPE x VALUE '0000',
    height(2)   TYPE x VALUE '0000',
    hidden(2)   TYPE x VALUE '0',
    END OF wind_data,
    BEGIN OF stdstyle_data,
    opcode(2)   TYPE x VALUE '3029',
    length(2)   TYPE x VALUE '4000',
    indexxf(2)  TYPE x VALUE '0008',
    buildnum(2) TYPE x VALUE '0FF0',
    END OF stdstyle_data,
    lc TYPE i.
    FIELD-SYMBOLS: <it_rc> TYPE x.
    lc = 4660.
    ASSIGN lc TO <it_rc> CASTING.
    CASE <it_rc>+0(1).
      WHEN '00'. " 00
        gl_rev = 1 .
      WHEN OTHERS.
        gl_rev = 2.
    ENDCASE.
    lc = 0.
    CLEAR <it_rc>.
    CLEAR deffontname.
    isopen = 1.
    CLEAR: bufff.
    ASSIGN beg_file_data TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
    ASSIGN codepage_data TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
    ASSIGN calc_mode_data TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
    ASSIGN wind_data TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
    FREE tfontlist.
    CLEAR wa_fontslist.
    IF def_font IS NOT INITIAL.
      wa_fontslist-name = def_font.
    ELSE.
      CALL FUNCTION 'GUI_GET_REGVALUE'
        EXPORTING
          root           = hkey_current_user
          key            = 'Software\Microsoft\Office\11.0\Excel\Options'
          value_name     = 'Font'
        CHANGING
          string         = def_fontst
        EXCEPTIONS
          registry_error = 1.
      CONDENSE def_fontst.
      def_fonth = STRLEN( def_fontst ).
      IF sy-subrc = 0 AND def_fonth > 0.
        SPLIT def_fontst AT ',' INTO wa_fontslist-name lc_tx .
        CONDENSE wa_fontslist-name.
        CONDENSE lc_tx .
        def_fonth = lc_tx .
      ELSE.
        wa_fontslist-name = 'Arial Cyr'.
        def_fonth = 10.
      ENDIF.
    ENDIF.
    DO 5 TIMES.
      CALL METHOD add_font
        EXPORTING
          _fontname   = wa_fontslist-name
          _fontheight = def_fonth
          _fontattr   = 0
          _fontcolor  = '0000'
          _add        = 1
        CHANGING
          ind_font    = lc.
    ENDDO.
    IF f_name IS NOT INITIAL.
      filename = f_name.
    ENDIF .
    typeout = t_out.
    CALL METHOD wr_def_formats.
    IF typeout = 'AC'. " Выгрузка на AP сервер
      OPEN DATASET filename FOR OUTPUT IN BINARY MODE.
      IF sy-subrc <> 0.
        MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
        WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
      ENDIF.
    ENDIF.
    FREE vp_breaks.
    FREE hp_breaks.
    DATA: prc_conv TYPE REF TO cl_abap_conv_out_ce,
    prc_xstr_no    TYPE xstring,
    prc_i_no       TYPE i,
    prc_sapno      TYPE cpcharno,
    prc_space      TYPE string.
    TRY.
        CALL METHOD cl_abap_conv_out_ce=>create
          EXPORTING
            encoding = '4000'
          RECEIVING
            conv     = prc_conv.
      CATCH cx_parameter_invalid_range .
      CATCH cx_sy_codepage_converter_init .
    ENDTRY.
    TRY.
        CALL METHOD prc_conv->write
          EXPORTING
            data = '#'.
      CATCH cx_sy_codepage_converter_init.
      CATCH cx_sy_conversion_codepage.
      CATCH cx_parameter_invalid_type.
      CATCH cx_parameter_invalid_range.
    ENDTRY.
    TRY.
      CALL METHOD prc_conv->get_buffer
        RECEIVING
          buffer = prc_xstr_no.
    ENDTRY.
    prc_i_no = prc_xstr_no.
    prc_sapno = prc_i_no.
    DATA: par_encoding TYPE abap_encoding VALUE '1504',
    prc_codepage(4) TYPE n,
    prc_miss        TYPE c,
    prc_broken      TYPE c.
    prc_miss      = '.'.
    prc_broken    = '.'.
    prc_codepage  = par_encoding.
    CREATE OBJECT converter
    EXPORTING
    outcode = prc_codepage
    miss = prc_miss
    broken = prc_broken
    use_f1 = 'X'
    substc = prc_sapno
    EXCEPTIONS
    invalid_codepage = 1
    internal_error = 2.
    IF sy-subrc <> 0.
      CASE sy-subrc.
        WHEN 1.
        WHEN 2.
      ENDCASE.
    ENDIF.
  ENDMETHOD. "create_xls
  METHOD donl_file .
    DATA: len TYPE i,
    xlsfsize  TYPE i,
    pos       TYPE i.
    xlsfsize = XSTRLEN( bufff ).
    IF typeout = 'AC'. " Выгрузка на AP сервер
      TRANSFER bufff TO filename LENGTH xlsfsize.
    ELSE.
      DATA: lc_bin TYPE sdok_sdatx,
      l_graphic_conv TYPE i,
      xlsbuffer LIKE STANDARD TABLE OF lc_bin,
      l_graphic_offs TYPE i.
      CHECK xlsfsize > 0.
      l_graphic_conv = xlsfsize.
      l_graphic_offs = 0.
      WHILE l_graphic_conv > 1022.
        lc_bin = bufff+l_graphic_offs(1022).
        APPEND lc_bin TO xlsbuffer.
        l_graphic_offs = l_graphic_offs + 1022.
        l_graphic_conv = l_graphic_conv - 1022.
      ENDWHILE.
      lc_bin = bufff+l_graphic_offs(l_graphic_conv).
      APPEND lc_bin TO xlsbuffer.
      CALL FUNCTION 'GUI_DOWNLOAD'
        EXPORTING
          bin_filesize = xlsfsize
          filename     = filename
          filetype     = 'BIN'
          append       = ' '
        TABLES
          data_tab     = xlsbuffer
        EXCEPTIONS
          OTHERS       = 22.
      IF sy-subrc <> 0.
        MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
        WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
      ENDIF.
    ENDIF.
    REFRESH xlsbuffer.
    CLEAR: xlsbuffer, xlsfsize , bufff.
  ENDMETHOD. "donl_file
  METHOD set_border .
    DATA lc_int TYPE i.
    lc_int = _rightbor + _rightst.
    IF lc_int > 200.
      lc_int = 195.
    ENDIF.
    borderst = borderst + lc_int.
    lc_int = _bottombor + _bottomst.
    IF lc_int > 200.
      lc_int = 195.
    ENDIF.
    borderst = borderst + lc_int * 256 .
    lc_int = _leftbor + _leftst.
    IF lc_int > 200.
      lc_int = 195.
    ENDIF.
    borderst = borderst + lc_int * 256 * 256 .
    lc_int = _topbor + _topst.
    IF lc_int > 200.
      lc_int = 195.
    ENDIF.
    borderst = borderst + lc_int * 256 * 256 * 256.
  ENDMETHOD. "set_border
  METHOD set_col_width .
    DATA : BEGIN OF colwidth,
    opcode(2)   TYPE x VALUE '2400',
    length(2)   TYPE x VALUE '0400',
    col1(1)     TYPE x,
    col2(1)     TYPE x,
    colwidth(2) TYPE x,
    END OF colwidth.
    FIELD-SYMBOLS: <it_rc> TYPE x.
    nint4 = f_col - 1.
    CALL METHOD inttohex
      EXPORTING
        ilen = 1
      CHANGING
        chex = colwidth-col1.
    nint4 = f_col + e_col - 1.
    CALL METHOD inttohex
      EXPORTING
        ilen = 1
      CHANGING
        chex = colwidth-col2.
    nint4 = width * 256.
    CALL METHOD inttohex
      EXPORTING
        ilen = 2
      CHANGING
        chex = colwidth-colwidth.
    ASSIGN colwidth TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
  ENDMETHOD. "SET_COL_WIDTH
  METHOD set_defrowhi .
    DATA : BEGIN OF defheight,
    opcode(2) TYPE x VALUE '2500',
    length(2) TYPE x VALUE '0200',
    rowheight(2) TYPE x ,
    END OF defheight.
    FIELD-SYMBOLS: <it_rc> TYPE x.
    nint4 = height * 20.
    CALL METHOD inttohex
      EXPORTING
        ilen = 2
      CHANGING
        chex = defheight-rowheight.
    ASSIGN defheight TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
  ENDMETHOD. "SET_DEFROWHI
  METHOD set_psetup .
    DATA:
    BEGIN OF ps_data,
    opcode(2)    TYPE x VALUE 'A100',
    length(2)    TYPE x VALUE '2200',
    papersize(2) TYPE x VALUE '0900', " Размер (тип', страницы
    scale(2)     TYPE x VALUE '6400', " Масштаб
    pagestart(2) TYPE x VALUE '0100', " Номер стартовой страницы
    fitwidth(2)  TYPE x VALUE '0100', " Кол-во страниц в ширину
    fitheight(2) TYPE x VALUE '0100', " Кол-во страниц в высоту
    options(2)   TYPE x VALUE '0200', " Доп.битовые флажки свойств
    hres(2)      TYPE x VALUE '6801', " '0168'Горизонтальное разрешение принтера
    vres(2)      TYPE x VALUE '6801', " Вертикальное разрешение принтера
    END OF ps_data,
    headermargin TYPE f VALUE '0.5' , " Размещение верхнего колонтитула, дюймы
    footermargin TYPE f VALUE '0.5' ," Размещение нижнего колонтитула, дюймы
    copies(2)    TYPE x VALUE '0100'. " Кол-во копий
    FIELD-SYMBOLS: <it_rc> TYPE x.
    nint4 = _papersize.
    CALL METHOD inttohex
      EXPORTING
        ilen = 2
      CHANGING
        chex = ps_data-papersize.
    nint4 = _scale.
    CALL METHOD inttohex
      EXPORTING
        ilen = 2
      CHANGING
        chex = ps_data-scale.
    nint4 = _pagestart.
    CALL METHOD inttohex
      EXPORTING
        ilen = 2
      CHANGING
        chex = ps_data-pagestart.
    nint4 = _wfit.
    CALL METHOD inttohex
      EXPORTING
        ilen = 2
      CHANGING
        chex = ps_data-fitwidth.
    nint4 = _hfit.
    CALL METHOD inttohex
      EXPORTING
        ilen = 2
      CHANGING
        chex = ps_data-fitheight.
    nint4 = _options.
    CALL METHOD inttohex
      EXPORTING
        ilen = 2
      CHANGING
        chex = ps_data-options.
    ASSIGN ps_data TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
    CALL METHOD add_number( headermargin ).
    CALL METHOD add_number( footermargin ).
    ASSIGN copies TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
  ENDMETHOD. "set_psetup
  METHOD set_rowhi .
    DATA:
    BEGIN OF row_data , " ! Запись об аттрибутах строки
    opcode(2)     TYPE x VALUE '0802' ,")
    length(2)     TYPE x VALUE '1000',
    rownum(2)     TYPE x ,
    firstcol(2)   TYPE x VALUE '0000',
    lastcol(2)    TYPE x VALUE '0000',
    height(2)     TYPE x ,
    reserved1(2)  TYPE x VALUE '0000',"
    reserved2(2)  TYPE x VALUE '0000',"
    optionflag(2) TYPE x VALUE 'C001',
    indexxf(2)    TYPE x VALUE 'F000',") ! 0-based
    END OF row_data.
    FIELD-SYMBOLS: <it_rc> TYPE x.
    nint4 = _row - 1.
    CALL METHOD inttohex
      EXPORTING
        ilen = 2
      CHANGING
        chex = row_data-rownum.
    nint4 = _height * 20.
    CALL METHOD inttohex
      EXPORTING
        ilen = 2
      CHANGING
        chex = row_data-height.
    IF _indexxf NE 0.
      nint4 = _indexxf.
      CALL METHOD inttohex
        EXPORTING
          ilen = 1
        CHANGING
          chex = row_data-indexxf.
    ENDIF.
    ASSIGN row_data TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
  ENDMETHOD. "set_rowhi
  METHOD get_borderline .
    DATA: lc_bord TYPE p,
    lc_pos TYPE i,
    lc_hex(1) TYPE x.
    CLEAR:lc_hex , lc_bord , lc_pos .
    lc_bord = _border.
    DO 4 TIMES.
      nint4 = FLOOR( lc_bord / 256 ** ( 4 - sy-index ) ).
      lc_bord = lc_bord - nint4 * ( 256 ** ( 4 - sy-index ) ).
      CLEAR lc_hex .
      IF nint4 > 0.
        CALL METHOD inttohex
          EXPORTING
            ilen = 1
          CHANGING
            chex = lc_hex.
      ENDIF.
      borderxeh+lc_pos(1) = lc_hex.
      lc_pos = lc_pos + 1.
    ENDDO.
  ENDMETHOD. "get_borderline
  METHOD ins_hpbreak .
    IF isopen NE 0 .
      READ TABLE hp_breaks INTO wa_pbreaks WITH KEY position = col.
      IF sy-subrc NE 0.
        wa_pbreaks-position = col.
        APPEND wa_pbreaks TO hp_breaks .
      ENDIF.
    ENDIF.
  ENDMETHOD. "ins_hpbreak
  METHOD ins_vpbreak .
    IF isopen NE 0 .
      READ TABLE vp_breaks INTO wa_pbreaks WITH KEY position = row.
      IF sy-subrc NE 0.
        wa_pbreaks-position = row.
        APPEND wa_pbreaks TO vp_breaks .
      ENDIF.
    ENDIF.
  ENDMETHOD. "ins_vpbreak
  METHOD inttohex .
    DATA: " nflagint TYPE i,
    pos_from TYPE i,
    lc_xstr TYPE xstring,
    pos_to TYPE i,
    nlenhex TYPE i.
    FIELD-SYMBOLS <fs> TYPE x.
    CLEAR chex.
    nlenhex = 4.
    ASSIGN nint4 TO <fs> CASTING.
    CASE gl_rev.
      WHEN 1. " 00001234 " инвертировать
        DO ilen TIMES.
          pos_from = nlenhex - sy-index. " читать от конца
          pos_to = sy-index - 1. " писать от начала
          chex+pos_to(1) = <fs>+pos_from(1).
        ENDDO.
      WHEN 2. " 34120000 " взять iLen байт
        chex = <fs>+0(ilen).
      WHEN OTHERS.
    ENDCASE.
  ENDMETHOD. "INTTOHEX
  METHOD protectsheet .
    DATA: BEGIN OF pr_sheet_record ,
    opcode(2)  TYPE x VALUE '1200',
    length(2)  TYPE x VALUE '0200',
    protect(2) TYPE x VALUE '0000',
    END OF pr_sheet_record.
    FIELD-SYMBOLS: <it_rc> TYPE x.
    IF prot NE 0.
      pr_sheet_record-protect = '0100' .
    ENDIF.
    ASSIGN pr_sheet_record TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
  ENDMETHOD. "protectsheet
  METHOD wr_blank .
    DATA: BEGIN OF label_data,
    opcode(2)  TYPE x VALUE '0102',
    length(2)  TYPE x VALUE '0600',
    row(2)     TYPE x ,
    col(2)     TYPE x ,
    indexxf(2) TYPE x VALUE '0F00',
    END OF label_data.
    FIELD-SYMBOLS: <it_rc> TYPE x.
    IF _indexxf IS NOT INITIAL.
      nint4 = _indexxf.
      CALL METHOD inttohex
        EXPORTING
          ilen = 2
        CHANGING
          chex = label_data-indexxf.
    ENDIF.
    nint4 = row - 1.
    CALL METHOD inttohex
      EXPORTING
        ilen = 2
      CHANGING
        chex = label_data-row.
    nint4 = col - 1.
    CALL METHOD inttohex
      EXPORTING
        ilen = 1
      CHANGING
        chex = label_data-col.
    ASSIGN label_data TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
    IF _note IS NOT INITIAL.
      CALL METHOD cellnote( _col = col _row = row _note = _note ).
    ENDIF.
  ENDMETHOD. "wr_blank
  METHOD wr_def_formats .
    DATA ifx TYPE i VALUE 0.
    DATA: BEGIN OF stdstyle_data,
    opcode(2)   TYPE x VALUE '9302',
    length(2)   TYPE x VALUE '0400',
    indexxf(2)  TYPE x VALUE '0080' ,
    buildnum(2) TYPE x VALUE '00FF' ,
    END OF stdstyle_data.
    FIELD-SYMBOLS: <it_rc> TYPE x.
    CLEAR wa_formatslist.
    REFRESH tb_formatslist.
    CALL METHOD add_format( 'General' ). " 0
    CALL METHOD add_format( '0;-0' ). " 1
    CALL METHOD add_format( '0.00;-0.00' ). " 2
    CALL METHOD add_format( '#,##0;-#,##0' ). " 3
    CALL METHOD add_format( '#,##0.00;-#,##0.00' ). " 4
    CALL METHOD add_format( '#,##0\ "$";\-#,##0\ "$"' )." 5
    CALL METHOD add_format( '#,##0\ "$";[Red]\-#,##0\ "$"' ). " 6
    CALL METHOD add_format( '#,##0.00\ "$";\-#,##0.00\ "$"' ). " 7
    CALL METHOD add_format( '#,##0.00\ "$";[Red]\-#,##0.00\ "$"' )."8
    CALL METHOD add_format( '0%' ). " 9
    CALL METHOD add_format( '0.00%' ). " 10
    CALL METHOD add_format( '0.00E+00' ). " 11
    CALL METHOD add_format( 'dd/mm/yy' ). " 12
    CALL METHOD add_format( 'dd/\ mmm\ yy' ). " 13
    CALL METHOD add_format( 'dd/\ mmm' ). " 14
    CALL METHOD add_format( 'mmm\ yy' ). " 15
    CALL METHOD add_format( 'h:mm\ AM/PM' ). " 16
    CALL METHOD add_format( 'h:mm:ss\ AM/PM' ). " 17
    CALL METHOD add_format( 'hh:mm' ). " 18
    CALL METHOD add_format( 'hh:mm:ss' ). " 19
    CALL METHOD add_format( 'dd/mm/yy\ hh:mm' ). " 20
    CALL METHOD add_format( '##0.0E+0' ). " 21
    CALL METHOD add_format( 'mm:ss' ). "' 22
    CALL METHOD add_format( '@' ). " 23
    CALL METHOD add_format( 'dd/mm/yyyy' ). " 24
    CALL METHOD add_format( '0.0;-0.0' ). " 25
    CALL METHOD add_format( '0.000;-0.000' ). " 26
    CALL METHOD add_format( '0.0000;-0.0000' ). " 27
    CALL METHOD add_format( '$#,##0.00;-$#,##0.00' ). " 28
    CALL METHOD add_format( '#,##0.00\ "р.";\-#,##0.00\ "р."' ). " 29
    CALL METHOD add_format( '#,##0.00\ "E";\-#,##0.00\ "E"' ). " 30
    CALL METHOD add_format( '#\ ##0;-#\ ##0' ). " 31
    CALL METHOD add_format( '#\ ##0.00;-#\ ##0.00' ). " 32
    FREE txflist.
    CALL METHOD add_fx
      EXPORTING
        _fontindex   = 0
        _formatindex = 0
        _extattr     = 65525
        _alignment   = 0
        _borderline  = 0
        _fillpattern = 0
        _extattr1    = '00'
        _reqadd      = 1
      CHANGING
        ind_fx       = ifx.
    DO 2 TIMES.
      CALL METHOD add_fx
        EXPORTING
          _fontindex   = 1
          _formatindex = 0
          _extattr     = 65525
          _alignment   = 0
          _borderline  = 0
          _fillpattern = 0
          _extattr1    = 'F4'
          _reqadd      = 1
        CHANGING
          ind_fx       = ifx.
    ENDDO.
    DO 2 TIMES.
      CALL METHOD add_fx
        EXPORTING
          _fontindex   = 2
          _formatindex = 0
          _extattr     = 65525
          _alignment   = 0
          _borderline  = 0
          _fillpattern = 0
          _extattr1    = 'F4'
          _reqadd      = 1
        CHANGING
          ind_fx       = ifx.
    ENDDO.
    DO 10 TIMES.
      CALL METHOD add_fx
        EXPORTING
          _fontindex   = 0
          _formatindex = 0
          _extattr     = 65525
          _alignment   = 0
          _borderline  = 0
          _fillpattern = 0
          _extattr1    = 'F4'
          _reqadd      = 1
        CHANGING
          ind_fx       = ifx.
    ENDDO.
    CALL METHOD add_fx
      EXPORTING
        _fontindex   = 0
        _formatindex = 0
        _extattr     = 1
        _alignment   = 0
        _borderline  = 0
        _fillpattern = 0
        _extattr1
        ='00'
        _reqadd      = 1
      CHANGING
        ind_fx       = ifx.
    ASSIGN stdstyle_data TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
  ENDMETHOD. "wr_def_formats
  METHOD wr_number .
    DATA: BEGIN OF label_data,
    opcode(2)  TYPE x VALUE '0302',
    length(2)  TYPE x VALUE '0E00',
    row(2)     TYPE x ,
    col(2)     TYPE x ,
    indexxf(2) TYPE x VALUE '0F00',
    END OF label_data,
    lc_float TYPE f.
    FIELD-SYMBOLS: <it_rc> TYPE x.
    nint4 = row - 1.
    CALL METHOD inttohex
      EXPORTING
        ilen = 2
      CHANGING
        chex = label_data-row.
    nint4 = col - 1.
    CALL METHOD inttohex
      EXPORTING
        ilen = 1
      CHANGING
        chex = label_data-col.
    IF _indexxf IS NOT INITIAL.
      nint4 = _indexxf.
      CALL METHOD inttohex
        EXPORTING
          ilen = 1
        CHANGING
          chex = label_data-indexxf.
    ENDIF.
    ASSIGN label_data TO <it_rc> CASTING.
    CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
    lc_float = _val.
    CALL METHOD add_number
      EXPORTING
        adnumb = lc_float.
    IF _note IS NOT INITIAL.
      CALL METHOD cellnote( _col = col _row = row _note = _note ).
    ENDIF.
  ENDMETHOD. "wr_number
  METHOD wr_string .
    DATA: BEGIN OF label_data,
    opcode(2)  TYPE x VALUE '0402',                         "'204',
    length(2)  TYPE x VALUE '0800',
    row(2)     TYPE x ,
    col(2)     TYPE x ,
    indexxf(2) TYPE x VALUE '0F00',                         "! 0-based
    END OF label_data,
    BEGIN OF rstring_data,
    opcode(2)  TYPE x VALUE 'D600',
    length(2)  TYPE x VALUE '0800',
    row(2)     TYPE x ,
    col(2)     TYPE x ,
    indexxf(2) TYPE x VALUE '0F',                           " ! 0-based
    strlen(2)  TYPE x ,
    END OF rstring_data,
    lc_length     TYPE i,
    lc_text(20)   TYPE c ,
    lc_text1(5)   TYPE c ,
    lc_text2(5)   TYPE c ,
    razd(1)       TYPE c,
    loc_strlen(2) TYPE x,
    lc_data(1)    TYPE x,
    loc_mfinfo    TYPE i VALUE 0,
    lc_mfinfo     TYPE xstring,
    ilen          TYPE i   VALUE 0,
    beglen        TYPE i VALUE 0,
    ipos          TYPE i VALUE 0,
    ichar         TYPE i VALUE 0,
    lenpoz        TYPE i VALUE 0.
    FIELD-SYMBOLS: <it_rc> TYPE x.
    IF _val IS INITIAL.
      CALL METHOD wr_blank( row = row col = col
        _indexxf = _indexxf _note = _note ).
    ELSE.
      loc_mfinfo = 0.
      nint4 = TRUNC( ichar / 2 ) .
      CALL METHOD inttohex
        EXPORTING
          ilen = 1
        CHANGING
          chex = lc_data.
      lc_length = 8 + STRLEN( _val ) .
      nint4 = STRLEN( _val ).
      CALL METHOD inttohex
        EXPORTING
          ilen = 1
        CHANGING
          chex = loc_strlen.
      nint4 = lc_length.
      CALL METHOD inttohex
        EXPORTING
          ilen = 1
        CHANGING
          chex = label_data-length.
      nint4 = row - 1.
      CALL METHOD inttohex
        EXPORTING
          ilen = 2
        CHANGING
          chex = label_data-row.
      nint4 = col - 1.
      CALL METHOD inttohex
        EXPORTING
          ilen = 1
        CHANGING
          chex = label_data-col.
      IF _indexxf IS NOT INITIAL.
        nint4 = _indexxf.
        CALL METHOD inttohex
          EXPORTING
            ilen = 2
          CHANGING
            chex = label_data-indexxf.
      ENDIF.
      ASSIGN label_data TO <it_rc> CASTING.
      CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
      ASSIGN loc_strlen TO <it_rc> CASTING.
      CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
      CALL METHOD add_string
        EXPORTING
          adtext = _val.
      IF _note IS NOT INITIAL.
        CALL METHOD cellnote( _col = col _row = row _note = _note ).
      ENDIF.
    ENDIF.
  ENDMETHOD. "wr_string
  METHOD wr_tab .
    DATA:
    bl_opcode(2)  TYPE x VALUE '0102',
    bl_length(2)  TYPE x VALUE '0600',
    n_opcode(2)   TYPE x VALUE '0302',
    n_length(2)   TYPE x VALUE '0E00',
    st_opcode(2)  TYPE x VALUE '0402',
    st_length(2)  TYPE x VALUE '0800',
    loc_strlen(2) TYPE x,
*    h_row(2)      TYPE x,
    lctb_col1     TYPE lvc_t_fcat.
    DATA: BEGIN OF mrc_st,
    opcode(2)     TYPE x ,
    length(2)     TYPE x ,
    row(2)        TYPE x ,
    col(2)        TYPE x ,
    indexxf(2)    TYPE x ,
    END OF mrc_st ,
    lc_border TYPE p.
    DATA:  wa_cols TYPE lvc_s_fcat,
    lc_align    TYPE i,
    alborder    TYPE i,
    topborder   TYPE i,
    douborder   TYPE i,
    lc_date     TYPE d,
    ret_date    TYPE i,
    lc_time     TYPE t,
    lc_fmtindex TYPE i,
    ret_time    TYPE f.
    DATA: prc_lines_count TYPE i,
*   prc_column_idx TYPE i,
    loc_col        TYPE i,
    dat_row        TYPE i,
    lc_val         TYPE i,
    lc_len         TYPE i,
    cnt_prc        TYPE i,
    procent_prc    TYPE i,
    wr_string      TYPE c,
    lc_fx          TYPE i,
    ts_string(225) TYPE c,
    wr_numb        TYPE f,
    ref_tab1       TYPE REF TO data.
    FIELD-SYMBOLS: " <l_table1> TYPE STANDARD TABLE,
    <it_rc>      TYPE x,
    <field_from> TYPE ANY,
    <rec_val>    TYPE ANY.
    DESCRIBE TABLE dat_tab LINES dat_row .
    prc_lines_count = dat_row .
    tec_row         = _row.
    loc_col         = _col.
    IF col_tab IS INITIAL.
      GET REFERENCE OF dat_tab INTO ref_tab1.
      CALL METHOD me->get_tabl_inf( EXPORTING dat_tabl = ref_tab1  IMPORTING t_fcat = lctb_col1 ).
    ELSE.
      MOVE col_tab[] TO lctb_col1[].
    ENDIF.
    loc_col         = 0.
    LOOP AT lctb_col1 INTO wa_cols.
      nint4 = loc_col.
      CALL METHOD inttohex
        EXPORTING
          ilen = 2
        CHANGING
          chex = wa_cols-style.
      MODIFY lctb_col1 FROM wa_cols.
      loc_col = loc_col + 1.
    ENDLOOP.
    loc_col        = _col.
    LOOP AT dat_tab  ASSIGNING <rec_val> .
      IF sy-batch IS INITIAL. " покажем индикатор выполнения
        IF dat_row > 100.
          cnt_prc       = dat_row.
          procent_prc   = cnt_prc DIV 10.
          cnt_prc       = sy-tabix MOD procent_prc.
          IF cnt_prc    = 1.
          ENDIF.
        ENDIF.
      ENDIF.
**********************************************************************
          nint4          = tec_row - 1.
    CALL METHOD inttohex
      EXPORTING
        ilen = 2
      CHANGING
        chex = mrc_st-row.
**********************************************************************
      loc_col = _col.
      LOOP AT lctb_col1 INTO wa_cols.
        CHECK loc_col < 255.
        CHECK tec_row < 64000.
        IF wa_cols-col_pos IS NOT INITIAL.
          ASSIGN COMPONENT wa_cols-fieldname OF STRUCTURE <rec_val> TO <field_from> .
          IF sy-subrc = 0.
            CASE wa_cols-inttype.
              WHEN 'C' OR 'X'.
                lc_val = 2.
              WHEN 'P' OR 'N' OR 'I' OR 'F' OR 'D' OR 'T'.
                lc_val = 1.
            ENDCASE.
          ELSE.
            lc_val = xlsblank.
          ENDIF.
        ELSE.
          lc_val = xlsblank.
        ENDIF.
        mrc_st-col     =   wa_cols-style.
        mrc_st-indexxf =   '0F00'.
        IF wa_cols-inttype = 'D' AND lc_val = xlsnumber.
          lc_date = <field_from>.
          CALL METHOD: me->get_xldate
          EXPORTING
          sapdate = lc_date
          CHANGING
          xldat = ret_date.
          ASSIGN ret_date TO <field_from> .
        ENDIF.
        IF lc_val = xlsnumber AND wa_cols-inttype = 'T'.
          lc_time = <field_from>.
          CALL METHOD: me->get_xltime
          EXPORTING
          saptime = lc_time
          CHANGING
          xltime = ret_time.
          ASSIGN ret_time TO <field_from> .
        ENDIF.
        IF <field_from> IS INITIAL.
          lc_val = xlsblank.
        ENDIF.
        mrc_st-col = wa_cols-style+0(2) .
        CASE lc_val.
          WHEN xlsnumber.
            mrc_st-opcode = n_opcode.
            mrc_st-length = n_length.
*            ASSIGN mrc_st TO <it_rc> CASTING.
*            IF lc_val = xlsnumber.
*              CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
*            ENDIF.
          WHEN xlsblank.
            mrc_st-opcode = bl_opcode.
            mrc_st-length = bl_length.
*            ASSIGN mrc_st TO <it_rc> CASTING.
*            IF lc_val NE xlsnumber.
*              CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
*            ENDIF.
          WHEN xlstext.
            ts_string = <field_from>.
            SHIFT ts_string RIGHT DELETING TRAILING ' '.
            TRANSLATE ts_string USING ' 0'.
            IF ts_string CO '1234567890'.
              lc_val        = xlsnumber.
              wr_numb       = <field_from>.
              mrc_st-opcode = n_opcode.
              mrc_st-length = n_length.
            ELSE.
              wr_string = <field_from>.
              lc_len = STRLEN( wr_string ).
              nint4 = lc_len + 8.
              mrc_st-opcode = st_opcode.
              CALL METHOD inttohex
                EXPORTING
                  ilen = 1
                CHANGING
                  chex = mrc_st-length.
            ENDIF.
            ASSIGN mrc_st TO <it_rc> CASTING.
            IF lc_val NE xlsnumber.
              CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
            ENDIF.
        ENDCASE.

        CASE lc_val.
          WHEN xlstext.
            nint4 = lc_len.
            CALL METHOD inttohex
              EXPORTING
                ilen = 1
              CHANGING
                chex = loc_strlen.
            CONCATENATE bufff loc_strlen INTO bufff IN BYTE MODE.
            CALL METHOD add_string
              EXPORTING
                adtext = wr_string.
          WHEN xlsnumber.
            MOVE <field_from> TO wr_numb .
*            CALL METHOD: me->wr_number( _val = wr_numb col = loc_col row = tec_row _indexxf = 0  ).
            ASSIGN mrc_st TO <it_rc> CASTING.
            CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
            CALL METHOD add_number
              EXPORTING
                adnumb = wr_numb.

          WHEN xlsblank.
            mrc_st-opcode = bl_opcode.
            mrc_st-length = bl_length.
            ASSIGN mrc_st TO <it_rc> CASTING.
            CONCATENATE bufff <it_rc> INTO bufff IN BYTE MODE.
        ENDCASE.
        loc_col = loc_col + 1.
      ENDLOOP.
      tec_row = tec_row + 1 .
    ENDLOOP.
    loc_col = _col.
    LOOP AT lctb_col1 INTO wa_cols.
      IF wa_cols-outputlen NE 0.
        set_col_width( f_col = loc_col e_col = loc_col width = wa_cols-outputlen ).
      ENDIF.
      loc_col = loc_col + 1.
    ENDLOOP.
    CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
      EXPORTING
        percentage = 100
        text       = 'Выгрузка закончена'.
  ENDMETHOD. "write_tab
  METHOD get_xldate.
    DATA: deltdate TYPE dats VALUE '18991230'.
    CLEAR xldat.
    IF sapdate IS NOT INITIAL.
      xldat = sapdate - deltdate.
    ELSE.
      xldat = sapdate .
    ENDIF.
  ENDMETHOD. "get_xldate
  METHOD get_xltime.
    DATA: "hh TYPE i,
    ofs TYPE i,
    del TYPE i VALUE 24 .
    CLEAR: ofs ,xltime .
    IF saptime IS NOT INITIAL.
      DO 3 TIMES.
        xltime = xltime + saptime+ofs(2) / del.
        ofs = ofs + 2.
        del = del * 60 .
      ENDDO.
    ELSE.
      xltime = saptime.
    ENDIF.
  ENDMETHOD. "get_xltime
  METHOD get_tabl_inf .
    DATA: lcl_table_descr TYPE REF TO cl_abap_tabledescr,
    lcl_struc_descr       TYPE REF TO cl_abap_structdescr,
    lcl_f_descr           TYPE REF TO cl_abap_elemdescr, "CL_ABAP_DATADESCR,
    ret_components        TYPE abap_compdescr_tab,
    components            TYPE abap_compdescr_tab,
    ls_dd04t              TYPE dd04t,
    ls_field_list         TYPE dfies,
    lc_tabix              TYPE i VALUE 1,
    p_field_list          TYPE TABLE OF dfies,
    lt_dd04t              TYPE STANDARD TABLE OF dd04t   ,
    lt_fcatalog           TYPE lvc_t_fcat,
    ls_fcatalog           TYPE lvc_s_fcat,
    wa_ret_components     TYPE LINE OF abap_compdescr_tab.
    CLEAR: lcl_table_descr , lcl_struc_descr , ret_components ,
     ls_field_list,   wa_ret_components .
    REFRESH: lt_dd04t[], components[], p_field_list[].
    DATA: new_line1 TYPE REF TO data,    rel_name        TYPE ddobjname.
    FIELD-SYMBOLS:
    <fs>      TYPE STANDARD TABLE,
    <l_line1> TYPE ANY,
    <fls>     TYPE ANY.
    ASSIGN dat_tabl->* TO <fs>.
    CREATE DATA new_line1 LIKE LINE OF <fs>.
    ASSIGN new_line1->* TO <l_line1>.
    lcl_table_descr     ?= cl_abap_typedescr=>describe_by_data_ref( dat_tabl ).
    lcl_struc_descr     ?= lcl_table_descr->get_table_line_type( ).
    ls_fcatalog-rollname = lcl_struc_descr->get_relative_name( ).
    IF lcl_struc_descr->is_ddic_type( ) = abap_true OR ls_fcatalog-rollname IS NOT INITIAL.
      CALL FUNCTION 'LVC_FIELDCATALOG_MERGE'
        EXPORTING
          i_structure_name       = ls_fcatalog-rollname
        CHANGING
          ct_fieldcat            = t_fcat
        EXCEPTIONS
          inconsistent_interface = 1
          program_error          = 2
          OTHERS                 = 3.
    ELSE.
      ret_components = lcl_struc_descr->components.
      LOOP AT ret_components INTO wa_ret_components .
        CLEAR ls_fcatalog.
        ls_fcatalog-fieldname = wa_ret_components-name.
        ASSIGN COMPONENT wa_ret_components-name OF STRUCTURE <l_line1> TO <fls> .
        lcl_f_descr         ?= cl_abap_elemdescr=>describe_by_data( <fls> ).
        ls_fcatalog-rollname = lcl_f_descr->get_relative_name( ).
        ls_fcatalog-col_pos  = lc_tabix.
**********************************************************************
        ls_fcatalog-decimals  =  wa_ret_components-decimals.
        ls_fcatalog-inttype   =  wa_ret_components-type_kind.
        APPEND ls_fcatalog TO t_fcat.
        CLEAR  ls_fcatalog.
        lc_tabix = lc_tabix + 1 .
      ENDLOOP.
      CHECK t_fcat[] IS NOT INITIAL.
      SELECT * FROM dd04t INTO TABLE lt_dd04t
       FOR ALL ENTRIES IN t_fcat
      WHERE rollname = t_fcat-rollname " <-- !!!
      AND ddlanguage = sy-langu.
      SORT lt_dd04t BY rollname.
      LOOP AT t_fcat INTO ls_fcatalog.
        READ TABLE lt_dd04t INTO ls_dd04t WITH KEY rollname = ls_fcatalog-rollname BINARY SEARCH.
        IF sy-subrc = 0.
          ls_fcatalog-reptext   =   ls_dd04t-reptext.
          ls_fcatalog-scrtext_s =   ls_dd04t-scrtext_s.
          ls_fcatalog-scrtext_m =   ls_dd04t-scrtext_m.
          ls_fcatalog-scrtext_l =   ls_dd04t-scrtext_l.
        ENDIF.
        MODIFY t_fcat FROM ls_fcatalog.
      ENDLOOP.
    ENDIF.
  ENDMETHOD. "REFRESH_param
ENDCLASS. "zcl_dexcel IMPLEMENTA

REPORT  zxxxx_bseg_upload                       .
INCLUDE zcl_dexcel_f.
*****
START-OF-SELECTION.
  TYPES   lc_xlswrite TYPE REF TO zcl_dexcel.
  DATA    lctb_col1  TYPE lvc_t_fcat.
  DATA:   lc_xls     TYPE  lc_xlswrite,
          lt_flter   TYPE lvc_t_fidx,
          ls_cols    TYPE lvc_s_fcat,
*  lc_row    TYPE i,
          albord     TYPE i,
          bord     TYPE p,
          ref_tab1   TYPE REF TO data,
          lc_align   TYPE i ,
          ind_heh(2) TYPE x,
          lc_style   TYPE i ,
  BEGIN OF lc_bseg,
belnr   TYPE  belnr_d,
gjahr   TYPE  gjahr,
buzei   TYPE  buzei,
buzid   TYPE  buzid,
augdt   TYPE  augdt,
augcp   TYPE  augcp,
END OF lc_bseg.
  DATA lt_spfli LIKE STANDARD TABLE OF bseg.
  REFRESH lt_spfli.
  SELECT * FROM bseg INTO CORRESPONDING FIELDS OF TABLE lt_spfli WHERE bukrs = 'XXXX' AND gjahr = 2008
        AND belnr = '1000000005' .
  CREATE OBJECT: lc_xls .
CALL METHOD: lc_xls->set_border
   exporting _rightbor = 6  _rightst = 0
    _bottombor  = 6
    _bottomst   = 0
    _leftbor    = 6
    _leftst     = 0
    _topbor     = 6
    _topst      = 0 changing borderst = bord .
  CALL METHOD: lc_xls->create_xls( f_name = 'd:\test1.xls' def_font = '' t_out = '') .
  CALL METHOD: lc_xls->set_defrowhi( height = 18 ).
  CALL METHOD: lc_xls->ins_vpbreak( row = 4 ) .
  CALL METHOD: lc_xls->ins_hpbreak( col = 4 ).
  CALL METHOD: lc_xls->set_col_width( f_col = 3 e_col = 5 width = 17 ).
  CALL METHOD: lc_xls->wr_string( row = 3 col = 3 _val = ' TYPE c ' _note = 'ghjghj TYPE c' ).
  CALL METHOD: lc_xls->wr_number( row = 3 col = 5 _val = '1000.011'  _note = 'цифра  c').
  CALL METHOD: lc_xls->set_rowhi( _row = 3 _height = 22 _indexxf = 0 ) .
  CALL METHOD: lc_xls->set_psetup( _options = 0   _papersize = 13 _scale  = 80 _pagestart = 2 _wfit  = 1 _hfit = 1 ) .
  GET REFERENCE OF lt_spfli INTO ref_tab1.
  CALL METHOD lc_xls->get_tabl_inf( EXPORTING dat_tabl = ref_tab1
   IMPORTING t_fcat = lctb_col1 ).
*  delete lctb_col1 WHERE col_pos > 230.
  CALL METHOD   lc_xls->add_format EXPORTING _picture =  '[$-419]dd.mmmm.yyyy'    " [$-FC19]   $-F800       "  '[$-0409]dd/mmmm/yyyy'
    CHANGING ind_frm = albord .
    CALL METHOD   lc_xls->add_fx EXPORTING _fontindex  = 0
     _formatindex = albord    _alignment   = 0   _borderline  = bord
     CHANGING ind_fx  = lc_style .
   move  lc_style to ind_heh.
  LOOP AT lctb_col1 INTO  ls_cols.
    if  ls_cols-INTTYPE = 'D'.
       move  lc_style to ls_cols-STYLE2+0(2).
     MODIFY  lctb_col1 FROM ls_cols    .
    ENDIF.
    CALL METHOD: lc_xls->wr_string( row = 4 col = sy-tabix _val = ls_cols-scrtext_l _indexxf = 0 ).
  ENDLOOP.
  lc_align = lc_xls->wr_tab( col_tab  =   lctb_col1 dat_tab  = lt_spfli _row = 6 _col = 1 ).
    data : ind TYPE i,
            indf TYPE f.
cALL METHOD: lc_xls->get_xldate exporting sapdate  = lc_bseg-augcp CHANGING xldat = ind . " перевести дату в формат Excel
indf = ind.
CALL METHOD: lc_xls->wr_number( row  = 10 col = 1 _val = indf  _indexxf = 16 ). " LюЁьрЄ,
CALL METHOD: lc_xls->protectsheet( prot = 1 ).
CALL METHOD: lc_xls->close_xls(  ).

CALL METHOD: lc_xls->set_border
  EXPORTING _rightbor   = 1 "6
            _rightst    = 0
            _bottombor  = 1
            _bottomst   = 0
            _leftbor    = 1
            _leftst     = 0
            _topbor     = 1
            _topst      = 0
  CHANGING  borderst = bord .

    CALL METHOD   lc_xls->add_fx
    EXPORTING _fontindex  = 1 "albord
             _formatindex = 1
             _extattr    = 0
             _alignment   = 4108
             _borderline  = bord
            _fillpattern = 0
            _extattr1 = '0'
            _reqadd = 0
     CHANGING ind_fx  = lc_style .

CALL METHOD: lc_xls->set_border
  EXPORTING _rightbor   = 1 " board
            _rightst    = 0
            _bottombor  = 1
            _bottomst   = 0
            _leftbor    = 1
            _leftst     = 0
            _topbor     = 1
            _topst      = 0
  CHANGING  borderst = bord .

  CALL METHOD: lc_xls->add_font
    EXPORTING
      _fontname    = 'Verdana'
      _fontheight  = 10 " размер шрифита
      _fontattr    = 1 " 1- BOLD
      _fontcolor   = '0'
      _add         = 0
    CHANGING ind_font = ind_font .

    CALL METHOD   lc_xls->add_fx
    EXPORTING  _fontindex  = ind_font "1 "albord
               _formatindex = 1
               _extattr    = 0
               _alignment   = 4106 " 4106 - center
               _borderline  = bord
               _fillpattern = 0
               _extattr1 = '0'
               _reqadd = 0
     CHANGING ind_fx  = lc_style .

Sub Макрос1()
    Range("A3:C3").Select
    Selection.Merge
End Sub