*&---------------------------------------------------------------------*
*& Report  Z_TEST_ALV
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT  Z_TEST_EXPORT_TO_EXCEL.

type-pools: slis, KKBLO.

data: lv_repid        type SY-REPID.
data: lvc_t_fcat      type LVC_T_FCAT,
      lv_user_command type SLIS_FORMNAME.

DATA  lv_table_text type DD02T-DDTEXT.
DATA  lvc_grid_title type lvc_title.

parameters: p_tabnam type tabname matchcode object DD_DBTB,
            p_rows   type i default 5000.

DATA dref TYPE REF TO data.

field-symbols: <it_data> type standard table.

START-OF-SELECTION.

  translate p_tabnam to upper case.
* Get table title
  select single DDTEXT from DD02T
           into (lv_table_text) where tabname = p_tabnam
                                  and DDLANGUAGE = sy-langu.
  if sy-subrc eq 0.
    lvc_grid_title = lv_table_text.
  else.
    message 'No table with such name was found' type 'E'.
  endif.

  TRY.
    CREATE DATA dref TYPE STANDARD TABLE OF (p_tabnam)
                          WITH NON-UNIQUE DEFAULT KEY.
    ASSIGN dref->* TO <it_data>.
  endtry.
  check <it_data> is assigned.

  select * from (p_tabnam)
    into table <it_data>
    up to p_rows rows.

  if sy-subrc ne 0.
    message 'No records was selected.' type 'S'.
    exit.
  endif.

end-of-SELECTION.

  perform prepare_fieldcat tables lvc_t_fcat.
  lv_repid = sy-repid.
  lv_user_command = 'ALV_USER_COMMAND'.

  data: gt_event_exit type SLIS_T_EVENT_EXIT,
        gs_event_exit type SLIS_EVENT_EXIT.

  gs_event_exit-ucomm  = '&VEXCEL'.
  gs_event_exit-before = 'X'.
  append gs_event_exit to gt_event_exit.

  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY_LVC'
    EXPORTING
      I_CALLBACK_PROGRAM                = lv_repid
      I_CALLBACK_USER_COMMAND           = lv_user_command
      I_GRID_TITLE                      = lvc_grid_title
      IT_FIELDCAT_LVC                   = lvc_t_fcat
      IT_EVENT_EXIT                     = gt_event_exit
    TABLES
      T_OUTTAB                          = <it_data>
    EXCEPTIONS
      PROGRAM_ERROR                     = 1
      OTHERS                            = 2
            .
*&---------------------------------------------------------------------*
*&      Form  PREPARE_FIELDCAT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_LVC_T_FCAT  text
*----------------------------------------------------------------------*
FORM PREPARE_FIELDCAT  TABLES   T_LVC_FCAT STRUCTURE LVC_S_FCAT.
  data: g_structure type DD02L-TABNAME,
        lt_fcat     type lvc_t_fcat.

  g_structure = p_tabnam.
  CALL FUNCTION 'LVC_FIELDCATALOG_MERGE'
    EXPORTING
      I_STRUCTURE_NAME             = g_structure
    CHANGING
      CT_FIELDCAT                  = lt_fcat.
  T_LVC_FCAT[] = lt_fcat[].

ENDFORM.                    " PREPARE_FIELDCAT
*&---------------------------------------------------------------------*
*&      Form  ALV_USER_COMMAND
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_R_UCOMM  text
*      -->P_LS_SELFIELD  text
*----------------------------------------------------------------------*
FORM ALV_USER_COMMAND  USING    P_UCOMM
                                S_SELFIELD.

data: lr_alv_grid type ref to cl_gui_alv_grid.
case p_ucomm.
  when '&VEXCEL'.
    CALL FUNCTION 'ZXXA_EXPORT_TABLE_TO_EXCEL'
        TABLES
          IT_DATA             = <it_data>
          IT_FIELDCAT         = lvc_t_fcat.
    CALL FUNCTION 'GET_GLOBALS_FROM_SLVC_FULLSCR'
        IMPORTING
          E_GRID              = lr_alv_grid.
    CALL METHOD LR_ALV_GRID->SET_USER_COMMAND
      EXPORTING
        I_UCOMM = ''.
endcase.
ENDFORM.                    " ALV_USER_COMMAND

FUNCTION ZXXA_EXPORT_TABLE_TO_EXCEL.
*"----------------------------------------------------------------------
*"*"Локальный интерфейс:
*"  IMPORTING
*"     REFERENCE(I_ROW) TYPE  I DEFAULT 1
*"     REFERENCE(I_COLUMN) TYPE  I DEFAULT 1
*"     REFERENCE(WITH_HEADER) TYPE  BOOLEAN DEFAULT 'X'
*"     REFERENCE(TECH_NAMES) TYPE  BOOLEAN DEFAULT ''
*"     REFERENCE(FIT_WIDEST) TYPE  BOOLEAN DEFAULT 'X'
*"     REFERENCE(NO_FORMAT) TYPE  BOOLEAN OPTIONAL
*"  EXPORTING
*"     REFERENCE(E_DOCUMENT_PROXY) TYPE REF TO  I_OI_DOCUMENT_PROXY
*"     REFERENCE(E_SPREADSHEET) TYPE REF TO  I_OI_SPREADSHEET
*"     REFERENCE(E_ERROR) TYPE REF TO  I_OI_ERROR
*"     REFERENCE(E_RETCODE)
*"  TABLES
*"      IT_DATA TYPE  TABLE
*"      IT_FIELDCAT TYPE  LVC_T_FCAT
*"----------------------------------------------------------------------

types: ty_range_name type char20.

constants: datatable_range_name  type ty_range_name value 'DataTable',
           header_range_name     type ty_range_name value 'Header',
           wholetable_range_name type ty_range_name value 'WholeTable',
           lines_per_range type i value 9999. " 9999 - maximum

data: lf_range_name type ty_range_name.
data: wa_fieldcat type lvc_s_fcat,
      lt_fieldcat type sorted table of lvc_s_fcat
                       with unique key col_pos,
      lf_fieldname type char060.

data: lf_lines   type i,
      lf_columns type i.

data: cursor_row type i,
      cursor_column type i.

data: num_of_ranges  type i,
      lf_tmp         type i,
      current_range  type i,
      current_row    type i,
      current_column type i,
      index          type i,
      lf_rows        type i,
      lf_current_cycle type i.

data: n(2) type n. " temporary variable

data: lt_ranges  type soi_range_list,
      ls_ranges  type soi_range_item,
      lt_content type soi_generic_table,
      ls_content type soi_generic_item.

DATA: spreadsheet    TYPE REF TO i_oi_spreadsheet,
      document_proxy type ref to i_oi_document_proxy.
data: is_available type i,
      ret_value    type i.

data: lf_percent type p decimals 2.

field-symbols: <ls_data>       type any,
               <lf_fieldvalue> type any.

if gr_control is initial.
  CALL METHOD c_oi_container_control_creator=>get_container_control
    IMPORTING control = gr_control
              retcode = retcode.
  CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
endif.
if gr_container is INITIAL.
  CREATE OBJECT gr_container EXPORTING container_name = 'CC_EXCEL'.
  CALL METHOD gr_control->init_control
    EXPORTING r3_application_name = 'Excel Document Container'
*             inplace_enabled     = 'X'
              parent = gr_container
              register_on_close_event = 'X'
    IMPORTING retcode = retcode.
  CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
endif.

CALL METHOD gr_control->get_document_proxy
  EXPORTING document_type      = 'Excel.Sheet.8'
            register_container = 'X'
  IMPORTING document_proxy     = document_proxy
            retcode            = retcode
            error              = error.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.

SET HANDLER gcl_xls_handler=>close_event FOR document_proxy.
e_document_proxy = document_proxy.

CALL METHOD document_proxy->create_document
  EXPORTING open_inplace     = ''
            document_title   = 'Выгрузка в Excel'
  IMPORTING  error            = error
             retcode          = retcode.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.



* Get spreadsheet interface
CALL METHOD document_proxy->get_spreadsheet_interface
          IMPORTING sheet_interface = spreadsheet
                    error           = error
                    retcode         = retcode.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
e_spreadsheet = spreadsheet.

* Define number of lines of the table
describe table it_data     lines lf_lines.

* Define number of columns of the table
lt_fieldcat[] = it_fieldcat[].
delete lt_fieldcat where no_out eq 'X' or tech eq 'X'.
describe table lt_fieldcat lines lf_columns.

* Set cursor to the left top corner of the table.
cursor_row    = i_row.
cursor_column = i_column.
CALL METHOD spreadsheet->set_selection
  EXPORTING top       = cursor_row
            left      = cursor_column
            rows      = 1
            columns   = 1
*            updating  = 0
  IMPORTING error     = error
            retcode   = retcode.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.

* Handling of header
if with_header eq 'X'.
* Insert range for the table header
  CALL METHOD spreadsheet->insert_range
    EXPORTING name      = header_range_name
              rows      = 1
              columns   = lf_columns
    IMPORTING error     = error
              retcode   = retcode.
  CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
* Insert range for the whole table header
  lf_rows = lf_lines + 1.
  CALL METHOD spreadsheet->insert_range
    EXPORTING name      = wholetable_range_name
              rows      = lf_rows
              columns   = lf_columns
    IMPORTING error     = error
              retcode   = retcode.
  CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
  index = 0.
  loop at lt_fieldcat into wa_fieldcat. "where no_out eq space.
    index = index + 1.
    clear ls_content.
    ls_content-column = index.
    ls_content-row    = 1.
    if tech_names eq 'X'.
      ls_content-value  = wa_fieldcat-FIELDNAME.
    else.
      ls_content-value  = wa_fieldcat-SCRTEXT_L.
    endif.
    append ls_content to lt_content.
  endloop.
  ls_ranges-name    = header_range_name.
  ls_ranges-rows    = 1.
  ls_ranges-columns = index.
  append ls_ranges to lt_ranges.
* Set the data from the table into the range
  call method spreadsheet->set_ranges_data
    exporting ranges   = lt_ranges
              contents = lt_content.
  CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
* Set the cursor to the line next after the last one
  cursor_row = cursor_row + 1.
  CALL METHOD spreadsheet->set_selection
    EXPORTING top       = cursor_row
              left      = cursor_column
              rows      = 1
              columns   = 1
     IMPORTING error     = error
               retcode   = retcode.
  CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
*  Set range name to fit wide of columns
  lf_range_name = wholetable_range_name.
else.
  lf_range_name = datatable_range_name.
endif.

* Insert range for the data table
CALL METHOD spreadsheet->insert_range
  EXPORTING name      = datatable_range_name
            rows      = lf_lines
            columns   = lf_columns
  IMPORTING error     = error
            retcode   = retcode.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.

*   There are only 9999 records can be tranferred in a range, becase ROW field of
* SOI_RANGE_ITEM structure has length of 4 symbols. So it is neccessary to split the range.
num_of_ranges = lf_lines div lines_per_range.
lf_tmp = num_of_ranges * lines_per_range.
* check if remainder of dividing is not equal 0
if lf_lines gt lf_tmp.
  num_of_ranges = num_of_ranges + 1.
endif.

index = 1. "cursor of data table
* The main cycle - insert ranges with records
do num_of_ranges times.
  current_range = sy-index.
  refresh: lt_ranges, lt_content.
*  lf_percent = ( current_range - 1 ) * lines_per_range / lf_lines * 100.
*  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
*   EXPORTING
*     PERCENTAGE       = lf_percent
*     TEXT             = 'Выгрузка в Excel'.

* Loop at the data table using cursor
  loop at it_data assigning <ls_data> from index.
    lf_current_cycle = sy-tabix. "current line of data table
*   get current row of the current range
*   (splitted by steps to avoid strange calculating error..)
    current_row = current_range - 1.
    current_row = current_row * lines_per_range.
    current_row = lf_current_cycle - current_row.
*   Filling the LS_CONTENT structure for each cell of data table
*   Warning: the maximum length of any field of data table shouldn't be grater than 256!
    do lf_columns times.
      current_column = sy-index.
      read table lt_fieldcat into wa_fieldcat
           index current_column transporting FIELDNAME inttype.
      check sy-subrc eq 0.
      ls_content-column = current_column.
      ls_content-row    = current_row.
      concatenate '<ls_data>-' wa_fieldcat-FIELDNAME into lf_fieldname.
      assign (lf_fieldname) to <lf_fieldvalue>.
      check <lf_fieldvalue> is assigned.
      if wa_fieldcat-inttype eq 'D'.
        write <lf_fieldvalue> to ls_content-value DD/MM/YYYY .
      else.
        ls_content-value = <lf_fieldvalue>.
      endif.
      append ls_content to lt_content.
    enddo.
    if current_row eq lines_per_range.
      index = current_row * current_range + 1.
      exit.
    endif.
  endloop.
  n = current_range.
  concatenate 'Range' n into ls_ranges-name.
  ls_ranges-rows    = current_row.
  ls_ranges-columns = lf_columns.
*  ls_ranges-code    = e_spreadsheet->SPREADSHEET_INSERTALL.
  append ls_ranges to lt_ranges.
* Insert range for a current part of data table
  CALL METHOD spreadsheet->insert_range
    EXPORTING name      = ls_ranges-name
              rows      = current_row
              columns   = lf_columns
    IMPORTING error     = error
              retcode   = retcode.
  CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
* Set the data from the table into the range
  call method spreadsheet->set_ranges_data
    exporting ranges   = lt_ranges
              contents = lt_content
              updating = 0.
  CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
* Set the cursor to the line next after the last one
  cursor_row = cursor_row + current_row.
  CALL METHOD spreadsheet->set_selection
    EXPORTING top       = cursor_row
              left      = cursor_column
              rows      = 1
              columns   = 1
     IMPORTING error     = error
               retcode   = retcode.
  CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
enddo.

* Formatting
if no_format eq space.
* Set frame of table
CALL METHOD spreadsheet->set_frame
  EXPORTING rangename = wholetable_range_name
            typ       = 127
            color     = 1
  IMPORTING error     = error
            retcode   = retcode.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
* Set bold font of header
CALL METHOD spreadsheet->set_font
  EXPORTING rangename = header_range_name
            family    = '-1'
            size      = '-1'
            bold      =  '1'
            italic    = '-1'
            align     = '-1'
  IMPORTING error     = error
            retcode   = retcode.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
* Set color of header
CALL METHOD spreadsheet->set_color
  EXPORTING rangename = header_range_name
            front     = '-1'
            back      = '6'
  IMPORTING error     = error
            retcode   = retcode.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
endif.
* Set left-justification align
CALL METHOD spreadsheet->set_font
  EXPORTING rangename = wholetable_range_name
            family    = '-1'
            size      = '-1'
            bold      = '-1'
            italic    = '-1'
            align     =  '0'
  IMPORTING error     = error
            retcode   = retcode.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
* Fit optimal width
if fit_widest eq 'X'.
  CALL METHOD spreadsheet->fit_widest
    EXPORTING name     = lf_range_name
    IMPORTING error    = error
              retcode  = retcode.
  CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
endif.
* Finally, update screen with handled table
CALL METHOD spreadsheet->screen_update
       EXPORTING updating = 'X'
       IMPORTING error    = error
                 retcode  = retcode.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.

ENDFUNCTION.

FUNCTION-POOL ZXXA_TEST_TOOLS.              "MESSAGE-ID ..

type-pools: soi.

data: retcode type SOI_RET_STRING,
      error   type ref to I_OI_ERROR.

CLASS gcl_xls_handler DEFINITION.
  PUBLIC SECTION.
    CLASS-METHODS:
      close_event  FOR EVENT ON_CLOSE_DOCUMENT OF I_OI_DOCUMENT_PROXY
                   IMPORTING DOCUMENT_PROXY Has_changed,
      custom_event FOR EVENT ON_CUSTOM_EVENT OF I_OI_DOCUMENT_PROXY.
*              IMPORTING (...)
ENDCLASS.

CLASS gcl_xls_handler IMPLEMENTATION.
  METHOD close_event.
    CALL METHOD document_proxy->close_document
      IMPORTING error = error
                retcode = retcode .
    CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
    CALL METHOD document_proxy->release_document
      IMPORTING error       = error
                retcode     = retcode.
    CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
    free document_proxy.

  ENDMETHOD.

  METHOD custom_event.
  ENDMETHOD.

ENDCLASS.

DATA  gr_control   type ref to I_OI_CONTAINER_CONTROL.
DATA  gr_container type ref to cl_gui_custom_container.