IMPORT_EXPORT Class

CLASS ycx_impexp_exception DEFINITION CREATE PUBLIC INHERITING FROM cx_static_check.

  PUBLIC SECTION.
    INTERFACES if_t100_message.
    METHODS constructor IMPORTING textid   LIKE if_t100_message=>t100key OPTIONAL
                                  previous LIKE previous OPTIONAL
                                  text1    TYPE sstring OPTIONAL
                                  text2    TYPE sstring OPTIONAL
                                  text3    TYPE sstring OPTIONAL
                                  text4    TYPE sstring OPTIONAL.
    CONSTANTS:
      BEGIN OF instantiaton_failed,
        msgid TYPE symsgid VALUE 'SABAPDEMOS',
        msgno TYPE symsgno VALUE '888',
        attr1 TYPE scx_attrname VALUE 'TEXT1',
        attr2 TYPE scx_attrname VALUE 'TEXT2',
        attr3 TYPE scx_attrname VALUE 'TEXT3',
        attr4 TYPE scx_attrname VALUE 'TEXT4',
      END OF instantiaton_failed,
      BEGIN OF file_not_found,
        msgid TYPE symsgid VALUE 'SABAPDEMOS',
        msgno TYPE symsgno VALUE '888',
        attr1 TYPE scx_attrname VALUE 'TEXT1',
        attr2 TYPE scx_attrname VALUE 'TEXT2',
        attr3 TYPE scx_attrname VALUE 'TEXT3',
        attr4 TYPE scx_attrname VALUE 'TEXT4',
      END OF file_not_found,
      BEGIN OF display_error,
        msgid TYPE symsgid VALUE 'SABAPDEMOS',
        msgno TYPE symsgno VALUE '888',
        attr1 TYPE scx_attrname VALUE 'TEXT1',
        attr2 TYPE scx_attrname VALUE 'TEXT2',
        attr3 TYPE scx_attrname VALUE 'TEXT3',
        attr4 TYPE scx_attrname VALUE 'TEXT4',
      END OF display_error,
      BEGIN OF read_file_failed,
        msgid TYPE symsgid VALUE 'SABAPDEMOS',
        msgno TYPE symsgno VALUE '888',
        attr1 TYPE scx_attrname VALUE 'TEXT1',
        attr2 TYPE scx_attrname VALUE 'TEXT2',
        attr3 TYPE scx_attrname VALUE 'TEXT3',
        attr4 TYPE scx_attrname VALUE 'TEXT4',
      END OF read_file_failed.

    DATA text1 TYPE sstring .
    DATA text2 TYPE sstring .
    DATA text3 TYPE sstring .
    DATA text4 TYPE sstring .
  PROTECTED SECTION.
  PRIVATE SECTION.

ENDCLASS.

CLASS ycx_impexp_exception IMPLEMENTATION.
  METHOD constructor.
    super->constructor(
       EXPORTING
         previous = previous ).

    me->text1 = text1 .
    me->text2 = text2 .
    me->text3 = text3 .
    me->text4 = text4 .

    IF textid IS INITIAL.
      if_t100_message~t100key = if_t100_message=>default_textid.
    ELSE.
      if_t100_message~t100key = textid.
    ENDIF.
  ENDMETHOD.
ENDCLASS.

INTERFACE yif_impexp.
  METHODS:
    imp_data IMPORTING from TYPE c
                       path TYPE string OPTIONAL,
    exp_data IMPORTING to   TYPE c
                       path TYPE string OPTIONAL,
    load_document RAISING ycx_impexp_exception.
  CONSTANTS:
    m_presentation TYPE c VALUE 'P',
    m_application  TYPE c VALUE 'A',
    m_database     TYPE c VALUE 'D',
    m_sc_list      TYPE c VALUE 'C',
    m_sc_alv       TYPE c VALUE 'V'.
ENDINTERFACE.

CLASS ycl_doc_factory DEFINITION CREATE PUBLIC.

  PUBLIC SECTION.
    CLASS-METHODS load_inst IMPORTING isnt_type     TYPE c
                            RETURNING VALUE(r_inst) TYPE REF TO yif_impexp
                            RAISING   ycx_impexp_exception.
    CONSTANTS:
      m_ty_alv  TYPE c VALUE 'A',
      m_ty_xlsx TYPE c VALUE 'X',
      m_ty_list TYPE c VALUE 'L',
      m_alv     TYPE seoclsname VALUE 'YCL_ALV',
      m_list    TYPE seoclsname VALUE 'YCL_CLASSIC',
      m_xlsx    TYPE seoclsname VALUE 'YCL_XLSX',
      m_html    TYPE seoclsname VALUE 'YCL_HTML',
      m_form    TYPE seoclsname VALUE 'YCL_FORM',
      m_json    TYPE seoclsname VALUE 'YCL_JSON'.

  PROTECTED SECTION.
  PRIVATE SECTION.

ENDCLASS.

CLASS ycl_doc_factory IMPLEMENTATION.

  METHOD load_inst.
    DATA(clsnm) = COND seoclsname( WHEN isnt_type = m_ty_alv THEN m_alv
                                   WHEN isnt_type = m_ty_xlsx THEN m_xlsx
                                   WHEN isnt_type = m_ty_list THEN m_list ).

    TRY.
        CREATE OBJECT r_inst TYPE (clsnm).
      CATCH cx_sy_create_object_error.
        RAISE EXCEPTION TYPE ycx_impexp_exception
          EXPORTING
            textid = ycx_impexp_exception=>instantiaton_failed.
    ENDTRY.

  ENDMETHOD.

ENDCLASS.

CLASS ycl_doc DEFINITION CREATE PUBLIC ABSTRACT.

  PUBLIC SECTION.
    INTERFACES yif_impexp.
    ALIASES:
      imp_data FOR yif_impexp~imp_data,
      exp_data FOR yif_impexp~exp_data,
      load_document FOR yif_impexp~load_document.

  PROTECTED SECTION.
    METHODS:
      imp_from_local
        RAISING
          ycx_impexp_exception,
      imp_from_server
        RAISING
          ycx_impexp_exception,
      imp_from_db,
      exp_to_local,
      exp_to_server,
      exp_to_alv
        RAISING
          ycx_impexp_exception,
      exp_to_screen.
    DATA:
      imp_path TYPE string,
      exp_path TYPE string.
  PRIVATE SECTION.

ENDCLASS.

CLASS ycl_doc IMPLEMENTATION.
  METHOD imp_data.
    imp_path = path.
    TRY.
        CASE from.
          WHEN yif_impexp~m_presentation.
            imp_from_local( ).
          WHEN yif_impexp~m_application.
            imp_from_server( ).
          WHEN yif_impexp~m_database.
            imp_from_db( ).
        ENDCASE.
      CATCH ycx_impexp_exception INTO DATA(lcx).
    ENDTRY.
  ENDMETHOD.
  METHOD exp_data.
    exp_path = path.
    TRY.
        CASE to.
          WHEN yif_impexp~m_presentation.
            exp_to_local( ).
          WHEN yif_impexp~m_application.
            exp_to_server( ).
          WHEN yif_impexp~m_sc_alv.
            exp_to_alv( ).
          WHEN yif_impexp~m_sc_list.
            exp_to_screen( ).
        ENDCASE.
      CATCH ycx_impexp_exception INTO DATA(lcx).
    ENDTRY.
  ENDMETHOD.
  METHOD imp_from_local.

  ENDMETHOD.

  METHOD imp_from_server.

  ENDMETHOD.

  METHOD imp_from_db.

  ENDMETHOD.

  METHOD exp_to_local.

  ENDMETHOD.

  METHOD exp_to_server.

  ENDMETHOD.

  METHOD exp_to_alv.

  ENDMETHOD.

  METHOD exp_to_screen.

  ENDMETHOD.

  METHOD load_document.

  ENDMETHOD.

ENDCLASS.

CLASS ycl_xlsx DEFINITION CREATE PUBLIC INHERITING FROM ycl_doc.

  PUBLIC SECTION.
    CONSTANTS:
      m_sheet_path  TYPE string VALUE 'xl/worksheets/*.xml',
      m_shared_path TYPE string VALUE 'xl/sharedStrings.xml',
      m_style_path  TYPE string VALUE 'xl/styles.xml',
      m_shared_sst  TYPE string VALUE 'sst',
      m_style_xfs   TYPE string VALUE 'cellXfs',
      m_sheet_dat   TYPE string VALUE 'sheetData',
      m_attr_span   TYPE string VALUE 'spans',
      m_attr_s      TYPE string VALUE 's',
      m_attr_r      TYPE string VALUE 'r',
      m_attr_t      TYPE string VALUE 't',
      m_att_fmid    TYPE string VALUE 'numFmtId',
      m_base_date   TYPE d VALUE '19000101'.

    METHODS load_document REDEFINITION.
  PROTECTED SECTION.
    METHODS:
      imp_from_local    REDEFINITION,
      imp_from_server   REDEFINITION,
      imp_from_db       REDEFINITION,
      exp_to_local      REDEFINITION,
      exp_to_server     REDEFINITION,
      exp_to_alv        REDEFINITION,
      exp_to_screen     REDEFINITION.

  PRIVATE SECTION.
    TYPES:
      BEGIN OF sheet_data_holder,
        sheet TYPE REF TO data,
      END OF sheet_data_holder,

      BEGIN OF shareds_conv,
        index TYPE i,
        value TYPE string,
      END OF shareds_conv,

      BEGIN OF sheet_conv,
        sheet TYPE string,
        row   TYPE string,
        cell  TYPE string,
        type  TYPE string,
        style TYPE REF TO cl_abap_elemdescr,
        value TYPE string,
      END OF sheet_conv,

      BEGIN OF style_conv,
        id     TYPE i,
        fmtid  TYPE i,
        abap_t TYPE REF TO cl_abap_elemdescr,
      END OF style_conv.

    DATA mt_shareds_str   TYPE TABLE OF shareds_conv.
    DATA mt_sheet_str     TYPE TABLE OF sheet_conv.
    DATA mt_style_str     TYPE TABLE OF style_conv.
    DATA sheet_data       TYPE TABLE OF sheet_data_holder.

    METHODS:
      load_doc_xml  RAISING  ycx_impexp_exception,
      load_doc_data,
      load_dyn_struct IMPORTING VALUE(i_col) TYPE i
                      EXPORTING itab         TYPE REF TO cl_abap_tabledescr
                                struct       TYPE REF TO cl_abap_structdescr,
      load_abap_type IMPORTING style      TYPE REF TO cl_abap_elemdescr
                               VALUE(val) TYPE any
                     RETURNING VALUE(ret) TYPE string.

    DATA:
      m_file_xstr   TYPE xstring,
      m_sheets_xml  TYPE TABLE OF xstring,
      m_shareds_xml TYPE xstring,
      m_styles_xml  TYPE xstring.
ENDCLASS.

CLASS ycl_xlsx IMPLEMENTATION.
  METHOD imp_from_local.
    TRY.
        IF imp_path IS INITIAL.
          m_file_xstr = cl_openxml_helper=>load_local_file(
                            cl_openxml_helper=>browse_local_file_open(
                                           iv_title      = 'Select XLSX File'
                                           iv_filename   = ''
                                           iv_extpattern = 'XLSX files(*.xlsx)|*.xlsx' ) ).
        ELSE.
          m_file_xstr = cl_openxml_helper=>load_local_file( imp_path ).
        ENDIF.

      CATCH cx_openxml_not_found.
        RAISE EXCEPTION TYPE ycx_impexp_exception
          EXPORTING
            textid = ycx_impexp_exception=>file_not_found.
    ENDTRY.
  ENDMETHOD.

  METHOD imp_from_server.
    DATA: ls_xfile TYPE x255,
          lt_xfile TYPE TABLE OF x255,
          l_len    TYPE i,
          l_size   TYPE i.

    IF imp_path IS INITIAL.
      DATA _path TYPE dxfields-longpath.

      CALL FUNCTION 'F4_DXFILENAME_TOPRECURSION'
        EXPORTING
          i_location_flag = 'A'
          i_server        = ''
*         i_path          = ''
*         filemask        = '*.*'
*         fileoperation   = 'R'
        IMPORTING
          o_path          = _path
        EXCEPTIONS
          rfc_error       = 1
          error_with_gui  = 2
          OTHERS          = 3.
      IF sy-subrc <> 0.
        RAISE EXCEPTION TYPE ycx_impexp_exception
          EXPORTING
            textid = ycx_impexp_exception=>file_not_found.
      ELSE.
        imp_path = _path.
      ENDIF.
    ENDIF.

    CLEAR ls_xfile.
    OPEN DATASET imp_path FOR INPUT IN BINARY MODE.
    IF sy-subrc <> 0.
      RAISE EXCEPTION TYPE ycx_impexp_exception
        EXPORTING
          textid = ycx_impexp_exception=>file_not_found.
    ENDIF.
    DO.
      READ DATASET imp_path INTO ls_xfile LENGTH l_len.
      IF sy-subrc <> 0.
        IF l_len > 0.
          l_size = l_size + l_len.
          APPEND ls_xfile TO lt_xfile.
        ENDIF.
        EXIT.
      ENDIF.
      l_size = l_size + l_len.
      APPEND ls_xfile TO lt_xfile.
    ENDDO.

    IF sy-subrc > 10.
      RAISE EXCEPTION TYPE ycx_impexp_exception
        EXPORTING
          textid = ycx_impexp_exception=>file_not_found.
    ENDIF.

    CLOSE DATASET imp_path.

    IF lt_xfile IS NOT INITIAL.
      RAISE EXCEPTION TYPE ycx_impexp_exception
        EXPORTING
          textid = ycx_impexp_exception=>file_not_found.
    ENDIF.

*   CL_BCS_CONVERT
    cl_scp_change_db=>xtab_to_xstr( EXPORTING  im_xtab    = lt_xfile
                                               im_size    = l_size
                                    IMPORTING  ex_xstring = m_file_xstr ).

  ENDMETHOD.

  METHOD imp_from_db.
  ENDMETHOD.

  METHOD exp_to_local.
  ENDMETHOD.

  METHOD exp_to_server.
  ENDMETHOD.

  METHOD exp_to_alv.

    FIELD-SYMBOLS <fdata> TYPE table.
    DATA(_layout) = VALUE slis_layout_alv( colwidth_optimize = 'X' zebra = 'X' ).
    DATA(_events) = VALUE slis_t_event( ( ) ).

    CALL FUNCTION 'REUSE_ALV_BLOCK_LIST_INIT'
      EXPORTING
        i_callback_program = sy-repid.

    LOOP AT sheet_data REFERENCE INTO DATA(sheet_line).

      ASSIGN sheet_line->sheet->* TO <fdata>.

      ASSIGN <fdata>[ 1 ] TO FIELD-SYMBOL(<fline>).
      DATA(fcats) = VALUE slis_t_fieldcat_alv(
                      FOR comp IN CAST cl_abap_structdescr( cl_abap_typedescr=>describe_by_data( <fline> ) )->components
                      ( fieldname = comp-name ) ).

      CALL FUNCTION 'REUSE_ALV_BLOCK_LIST_APPEND'
        EXPORTING
          is_layout                  = _layout
          it_fieldcat                = fcats
          i_tabname                  = '<fdata>'
          it_events                  = _events
        TABLES
          t_outtab                   = <fdata>
        EXCEPTIONS
          program_error              = 1
          maximum_of_appends_reached = 2
          OTHERS                     = 3.
      IF sy-subrc <> 0.
        RAISE EXCEPTION TYPE ycx_impexp_exception
          EXPORTING
            textid = ycx_impexp_exception=>display_error.
      ENDIF.
    ENDLOOP.

    CALL FUNCTION 'REUSE_ALV_BLOCK_LIST_DISPLAY'
      EXCEPTIONS
        program_error = 1
        OTHERS        = 2.
    IF sy-subrc <> 0.
      RAISE EXCEPTION TYPE ycx_impexp_exception
        EXPORTING
          textid = ycx_impexp_exception=>display_error.
    ENDIF.
  ENDMETHOD.

  METHOD exp_to_screen.
  ENDMETHOD.

  METHOD load_doc_xml.
    DATA(lr_zip) = NEW cl_abap_zip( ).
    lr_zip->load( m_file_xstr ).

    TRY.
        "zip way
        LOOP AT lr_zip->files INTO DATA(file) WHERE name CP m_sheet_path.
          lr_zip->get( EXPORTING name = file-name
                       IMPORTING content = DATA(lv_xml_data) ).
          APPEND lv_xml_data TO m_sheets_xml.
        ENDLOOP.

        lr_zip->get( EXPORTING name = m_shared_path
                     IMPORTING content = m_shareds_xml ).

        lr_zip->get( EXPORTING name = m_style_path
                     IMPORTING content = m_styles_xml ).

        "xlsx way
*        DO mo_doc->get_workbookpart( )->get_worksheetparts( )->get_count( ) TIMES.
*          DATA(lv_xml_data) = mo_doc->get_workbookpart(
*                                 )->get_worksheetparts(
*                                 )->get_part( sy-index )->get_data( ).
*          APPEND lv_xml_data TO mt_sheet_xml.
*        ENDDO.
*        mv_shareds_xml = mo_doc->get_workbookpart( )->get_sharedstringspart( )->get_data( ).
*        mv_styles_xml = mo_doc->get_workbookpart( )->get_stylespart( )->get_data( ).

      CATCH cx_openxml_format cx_openxml_not_found.
        RAISE EXCEPTION TYPE ycx_impexp_exception
          EXPORTING
            textid = ycx_impexp_exception=>read_file_failed.
    ENDTRY.
  ENDMETHOD.

  METHOD load_doc_data.

    DATA sheet_line       TYPE REF TO data.
    FIELD-SYMBOLS <sheet_data> TYPE table.
    FIELD-SYMBOLS <sheet_line> TYPE any.

    "Shared string data
    DATA(shared_xml) = NEW cl_xml_document( ).
    shared_xml->parse_xstring( stream = m_shareds_xml ).
    DATA(sst_node) = CAST if_ixml_node( shared_xml->find_node( m_shared_sst ) ).
    REFRESH mt_shareds_str.
    DO sst_node->get_children( )->get_length( ) TIMES.
      DATA(si) = CAST if_ixml_element( sst_node->get_children( )->get_item( sy-index - 1 ) ).

      mt_shareds_str = VALUE #( BASE mt_shareds_str
                                ( index = sy-index - 1
                                  value = REDUCE #( INIT value TYPE string
                                                    FOR i = 0 THEN i + 1 UNTIL i = si->get_children( )->get_length( )
                                                    NEXT value = value && si->get_children( )->get_item( i )->get_value( ) ) ) ).
    ENDDO.

    "Cell styles
    DATA(style_xml) = NEW cl_xml_document( ).
    style_xml->parse_xstring( stream = m_styles_xml ).
    DATA(xfs) = style_xml->find_node( m_style_xfs ).
    REFRESH mt_style_str.
    DO xfs->get_children( )->get_length( ) TIMES.
      DATA(xf) = CAST if_ixml_element( xfs->get_children( )->get_item( sy-index - 1 ) ).

      mt_style_str = VALUE #( LET fmtid = xf->get_attribute_ns( m_att_fmid ) IN
                              BASE mt_style_str ( id     = sy-index - 1
                                                  fmtid  = fmtid
                                                  abap_t = SWITCH #( fmtid
                                                                     WHEN 3  THEN cl_abap_elemdescr=>get_i( )
                                                                     WHEN 4  THEN cl_abap_elemdescr=>get_f( )
                                                                     WHEN 14 THEN cl_abap_elemdescr=>get_d( )
                                                                     ELSE cl_abap_elemdescr=>get_string( )
                                                                     ) ) ).
    ENDDO.

    "Sheets
    REFRESH mt_sheet_str.
    LOOP AT m_sheets_xml INTO DATA(sheet_stream).
      DATA(sheet_xml) = NEW cl_xml_document( ).
      sheet_xml->parse_xstring( stream = sheet_stream ).

      DATA(rows) = CAST if_ixml_node_list( sheet_xml->find_node( m_sheet_dat )->get_children( ) ).

      "Read rows & create table
      DO rows->get_length( ) TIMES.
        DATA(cells) = CAST if_ixml_node_list( rows->get_item( sy-index - 1 )->get_children( ) ).

        IF sy-index = 1.
          DATA(col_count) = CONV i( substring_after( val = CAST if_ixml_element( rows->get_item( 0 ) )->get_attribute_ns( m_attr_span )
                                                     sub = ':' ) ).

          load_dyn_struct( EXPORTING i_col = col_count
                           IMPORTING itab = DATA(tab_typ)
                                     struct = DATA(struct_typ) ).

          CREATE DATA sheet_line TYPE HANDLE struct_typ.
          APPEND INITIAL LINE TO sheet_data ASSIGNING FIELD-SYMBOL(<fdata>).
          CREATE DATA <fdata>-sheet TYPE HANDLE tab_typ.
          ASSIGN <fdata>-sheet->* TO <sheet_data>.
          ASSIGN sheet_line->* TO <sheet_line>.
        ENDIF.

        DO cells->get_length( ) TIMES.
          DATA(cell) = CAST if_ixml_element( cells->get_item( sy-index - 1 ) ).

          ASSIGN COMPONENT sy-index OF STRUCTURE <sheet_line> TO FIELD-SYMBOL(<line_col>).

          TRY.
              <line_col> = COND string( LET type  = cell->get_attribute_ns( m_attr_t )
                                            style = VALUE #( mt_style_str[ id = cell->get_attribute_ns( m_attr_s ) ]-abap_t OPTIONAL )
                                            r_val = VALUE #( mt_shareds_str[ index = cell->get_children( )->get_item( 0 )->get_value( ) ]-value OPTIONAL )
                                            c_val = cell->get_children( )->get_item( 0 )->get_value( )
                                        IN WHEN type = m_attr_s THEN load_abap_type( style = style val = r_val )
                                                                ELSE load_abap_type( style = style val = c_val ) ) .
            CATCH cx_sy_ref_is_initial.
              CONTINUE.
          ENDTRY.
        ENDDO.

        APPEND <sheet_line> TO <sheet_data>.
        CLEAR <sheet_line>.

        mt_sheet_str = VALUE #( BASE mt_sheet_str
                                FOR i = 0 UNTIL i = cells->get_length( )
                                    ( sheet = |sheet{ sy-tabix }|
                                      row   = |row{ sy-index }|
                                      cell  = CAST if_ixml_element( cells->get_item( i ) )->get_attribute_ns( m_attr_r )
                                      type  = CAST if_ixml_element( cells->get_item( i ) )->get_attribute_ns( m_attr_t )
                                      style = VALUE #( mt_style_str[ id = cell->get_attribute_ns( m_attr_s ) ]-abap_t OPTIONAL )
                                      value = cell->get_children( )->get_item( 0 )->get_value( ) ) ).
      ENDDO.
    ENDLOOP.

  ENDMETHOD.

  METHOD load_dyn_struct.
    struct = cl_abap_structdescr=>get(
                        VALUE #( FOR i = 1 UNTIL i > i_col
                                 ( name = |col{ i }|
                                   type = CAST #( cl_abap_datadescr=>describe_by_name( 'STRING' ) ) ) ) ).
    itab   = cl_abap_tabledescr=>create( struct ).
  ENDMETHOD.

  METHOD load_abap_type.
    CASE style->type_kind.
      WHEN cl_abap_elemdescr=>typekind_int.
        ret = |{ CONV i( val ) }|.
      WHEN cl_abap_elemdescr=>typekind_date.
        DATA date TYPE d.
        date = m_base_date + val - 2.
        ret = |{ date DATE = ENVIRONMENT }|.
      WHEN cl_abap_elemdescr=>typekind_float.
*        ret = |{ CONV f( val ) }|.
        ret = |{ val }|.
      WHEN cl_abap_elemdescr=>typekind_string.
        ret = val.
      WHEN OTHERS.
    ENDCASE.
  ENDMETHOD.

  METHOD load_document.
    TRY.
        load_doc_xml( ).
        load_doc_data( ).
      CATCH ycx_impexp_exception.
        RAISE EXCEPTION TYPE ycx_impexp_exception
          EXPORTING
            textid = ycx_impexp_exception=>read_file_failed.
    ENDTRY.
  ENDMETHOD.

ENDCLASS.

START-OF-SELECTION.
  TRY.
      DATA(lcl_xlsx) = ycl_doc_factory=>load_inst( isnt_type = ycl_doc_factory=>m_ty_xlsx ).

      lcl_xlsx->imp_data(
        EXPORTING
          from = yif_impexp=>m_presentation ).

      lcl_xlsx->load_document( ).

      lcl_xlsx->exp_data(
        EXPORTING
          to   = yif_impexp=>m_sc_alv ).
    CATCH ycx_impexp_exception INTO DATA(lcx).
      MESSAGE lcx->get_text( ) TYPE 'I'.
  ENDTRY.