Creare file Excel su Application Server

Di seguito riporto un semplice programma ABAP, che legge i dati di un OdA, e li scrive in un file excel che salviamo sul server:

In fondo c'è un alternativa al primo programma

In input il programma richiede il percorso del file sul server:

In ouput, sul server, verrà scritto un file excel che può essere scaricato con la transazione CG3Y in formato binario. L'output è il seguente:

Ecco il programma che può essere copiato ed eseguito:

REPORT z_excel_server2.

TYPES: BEGIN OF ty_dati,
         ebeln TYPE ekko-ebeln,
         bsart TYPE ekko-bsart,
         aedat TYPE ekko-aedat,
         ernam TYPE ekko-ernam,
       END OF ty_dati,
       tt_dati TYPE STANDARD TABLE OF ty_dati.

DATA : g_xstring   TYPE xstring,
       gt_bintab   TYPE solix_tab,
       gt_dati     TYPE tt_dati,
       gt_fieldcat TYPE lvc_t_fcat.

SELECTION-SCREEN: BEGIN OF BLOCK block1 WITH FRAME TITLE text1.
PARAMETERS: p_filese TYPE dxlpath LOWER CASE.
SELECTION-SCREEN: END OF BLOCK block1.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_filese.
*Search file in server directory
PERFORM search_server.

START-OF-SELECTION.

FREE: gt_bintab, gt_dati, gt_fieldcat.
CLEAR: g_xstring.

PERFORM estrai_dati TABLES gt_dati.

IF NOT gt_dati[] IS INITIAL.
    PERFORM crea_excel USING gt_dati gt_fieldcat
                       CHANGING g_xstring.

    PERFORM write_excel USING g_xstring.

ELSE.
    MESSAGE e000(db) WITH 'Non esistono dati da estrarre'.
ENDIF.

FREE: gt_dati.

END-OF-SELECTION.

*&---------------------------------------------------------------------*
*&      Form SEARCH_SERVER
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
* --> p1        text
* <-- p2        text
*----------------------------------------------------------------------*
FORM search_server .
CLEAR: p_filese.
CALL FUNCTION '/SAPDMC/LSM_F4_SERVER_FILE'
    IMPORTING
      serverfile       = p_filese
    EXCEPTIONS
      canceled_by_user = 1
      OTHERS           = 2.
ENDFORM.

*&---------------------------------------------------------------------*
*&      Form ESTRAI_DATI
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
* --> p1        text
* <-- p2        text
*----------------------------------------------------------------------*
FORM estrai_dati TABLES pt_dati TYPE tt_dati.

* Estraggo dati da esportare
SELECT ebeln bsart aedat ernam
     INTO TABLE pt_dati
     FROM ekko
     UP TO 10 ROWS
     ORDER BY PRIMARY KEY.

ENDFORM.
*&---------------------------------------------------------------------*
*&      Form CREA_EXCEL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
* --> p1        text
* <-- p2        text
*----------------------------------------------------------------------*
FORM crea_excel USING pt_dati     TYPE tt_dati
                      pt_fieldcat TYPE lvc_t_fcat
             CHANGING p_xstring.

DATA: is_layout     TYPE lvc_s_layo,
        it_sort       TYPE lvc_t_sort,
        it_filt       TYPE lvc_t_filt,
        it_hyperlinks TYPE lvc_t_hype.

DATA(lt_data) = REF #( pt_dati ).

** PER VERSIONI PRECEDENTI DI SAP, INVECE DI USARE L'ISTRUZIONE PRECEDENTE
** USARE QUESTE ISTRUZIONI COMMENTATE, E COMMENTATE QUELLA PRECEDENTE
** DATA: lt_data2 TYPE REF TO data.
** FIELD-SYMBOLS: <ltfs_data2> TYPE STANDARD TABLE.
** CREATE DATA lt_data2 type STANDARD TABLE OF ty_dati.
** aSSIGN lt_data2->* TO <ltfs_data2>.
** <ltfs_data2>[] = pt_dati[].
** PER VERSIONI PRECEDENTI

CLEAR: is_layout.
FREE: it_sort, it_filt, it_hyperlinks.

IF pt_fieldcat IS INITIAL.
    FIELD-SYMBOLS: <tab> TYPE STANDARD TABLE.
    ASSIGN lt_data->* TO <tab>.
** PER VERSIONI PRECEDENTI DI SAP, INVECE DI USARE L'ISTRUZIONE PRECEDENTE
** USARE QUESTA ISTRUZIONE COMMENTATA, E COMMENTATE QUELLA PRECEDENTE
*    ASSIGN lt_data2->* TO <tab>.
    TRY.
        cl_salv_table=>factory(
        EXPORTING
          list_display = abap_false
        IMPORTING
          r_salv_table = DATA(salv_table)
        CHANGING
          t_table      = <tab> ).

        DATA(lt_fcat) = cl_salv_controller_metadata=>get_lvc_fieldcatalog(
                                 r_columns      = salv_table->get_columns( )
                                 r_aggregations = salv_table->get_aggregations( ) ).
      CATCH cx_salv_msg.
        RETURN.
    ENDTRY.

ELSE.
    lt_fcat = pt_fieldcat.
ENDIF.

cl_salv_bs_lex=>export_from_result_data_table(
    EXPORTING
      is_format            = if_salv_bs_lex_format=>mc_format_xlsx
      ir_result_data_table = cl_salv_ex_util=>factory_result_data_table(
                                              r_data                      = lt_data
                                              s_layout                    = is_layout
                                              t_fieldcatalog              = lt_fcat
                                              t_sort                      = it_sort
                                              t_filter                    = it_filt
                                              t_hyperlinks                = it_hyperlinks )
    IMPORTING
      er_result_file       = p_xstring ).

ENDFORM.
*&---------------------------------------------------------------------*
*&      Form WRITE_EXCEL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_G_XSTRING text
*----------------------------------------------------------------------*
FORM write_excel USING p_xstring.

DATA: lt_rawdata      TYPE solix_tab,
        lv_bytecount    TYPE i,
        lv_bytes_remain TYPE i.

FIELD-SYMBOLS: <rawdata> LIKE LINE OF lt_rawdata.

CLEAR: lv_bytecount, lv_bytes_remain.
FREE: lt_rawdata.

lt_rawdata = cl_bcs_convert=>xstring_to_solix( iv_xstring = p_xstring ).
lv_bytecount = xstrlen( p_xstring ).

OPEN DATASET p_filese FOR OUTPUT IN BINARY MODE.
CHECK sy-subrc = 0.

lv_bytes_remain = lv_bytecount.

UNASSIGN <rawdata>.
LOOP AT lt_rawdata ASSIGNING <rawdata>.

    AT LAST.
      CHECK lv_bytes_remain >= 0.
      TRANSFER <rawdata> TO p_filese LENGTH lv_bytes_remain.
      EXIT.
    ENDAT.

    TRANSFER <rawdata> TO p_filese.
    SUBTRACT 255 FROM lv_bytes_remain. " Solix has length 255
ENDLOOP.

CLOSE DATASET p_filese.

IF sy-repid <> sy-cprog AND sy-cprog IS NOT INITIAL. " no need to display anything if download was selected and report was called for demo purposes
    LEAVE PROGRAM.
ELSE.
    MESSAGE 'File Creato' TYPE 'S'.
ENDIF.

ENDFORM.

2o PROGRAMMA (SOLO SE NEL VOSTRO SISTEMA E' INSTALLATO il pacchetto ZABAP2XLSX) che deve essere scaricato

Questo programma fa la stessa cosa di quello precedente, ma l'output del file excel creato sul server è il seguente:

Ecco il 2o programma che può essere copiato ed eseguito:

REPORT z_excel_server.

* Definizione Classe
CLASS lcl_output DEFINITION CREATE PRIVATE.
PUBLIC SECTION.
    CLASS-METHODS:
      output         IMPORTING cl_excel            TYPE REF TO zcl_excel
                               iv_writerclass_name TYPE clike OPTIONAL.

PRIVATE SECTION.
    METHODS:
      download_backend.

    DATA: xdata     TYPE xstring,             " Will be used for sending as email
          t_rawdata TYPE solix_tab,           " Will be used for downloading or open directly
          bytecount TYPE i.                   " Will be used for downloading or open directly
ENDCLASS.                    "lcl_output DEFINITION

* Dichiarazione variabili
TYPES: BEGIN OF ty_dati,
         ebeln TYPE ekko-ebeln,
         bsart TYPE ekko-bsart,
         aedat TYPE ekko-aedat,
         ernam TYPE ekko-ernam,
       END OF ty_dati,
       tt_dati TYPE STANDARD TABLE OF ty_dati.

DATA: lo_excel         TYPE REF TO zcl_excel,
      lo_worksheet     TYPE REF TO zcl_excel_worksheet,
      column_dimension TYPE REF TO zcl_excel_worksheet_columndime,
      gt_dati          type tt_dati.

SELECTION-SCREEN: BEGIN OF BLOCK block1 WITH FRAME TITLE text1.
PARAMETERS: p_filese TYPE dxlpath LOWER CASE.
SELECTION-SCREEN: END OF BLOCK block1.

* Implementazione Classe
CLASS lcl_output IMPLEMENTATION.
METHOD output.

    DATA: cl_output TYPE REF TO lcl_output,
          cl_writer TYPE REF TO zif_excel_writer.

    IF iv_writerclass_name IS INITIAL.
      CREATE OBJECT cl_output.
      CREATE OBJECT cl_writer TYPE zcl_excel_writer_2007.
    ELSE.
      CREATE OBJECT cl_output.
      CREATE OBJECT cl_writer TYPE (iv_writerclass_name).
    ENDIF.
    cl_output->xdata = cl_writer->write_file( cl_excel ).

* After 6.40 via cl_bcs_convert
    cl_output->t_rawdata = cl_bcs_convert=>xstring_to_solix( iv_xstring = cl_output->xdata ).
    cl_output->bytecount = xstrlen( cl_output->xdata ).

    cl_output->download_backend( ).

ENDMETHOD.                    "output

METHOD download_backend.
    DATA: bytes_remain TYPE i.
    FIELD-SYMBOLS: <rawdata> LIKE LINE OF t_rawdata.

    OPEN DATASET p_filese FOR OUTPUT IN BINARY MODE.
    CHECK sy-subrc = 0.

    bytes_remain = bytecount.

    LOOP AT t_rawdata ASSIGNING <rawdata>.

      AT LAST.
        CHECK bytes_remain >= 0.
        TRANSFER <rawdata> TO p_filese LENGTH bytes_remain.
        EXIT.
      ENDAT.

      TRANSFER <rawdata> TO p_filese.
      SUBTRACT 255 FROM bytes_remain. " Solix has length 255

    ENDLOOP.

    CLOSE DATASET p_filese.

    IF sy-repid <> sy-cprog AND sy-cprog IS NOT INITIAL. " no need to display anything if download was selected and report was called for demo purposes
      LEAVE PROGRAM.
    ELSE.
      MESSAGE 'File Creato' TYPE 'S'.
    ENDIF.
ENDMETHOD.                    "download_backend

ENDCLASS.                    "lcl_output IMPLEMENTATION

* Scegliere percorso dove scriver ei lfile sul server
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_filese.
PERFORM search_server.

* Main
START-OF-SELECTION.
free: gt_dati.
PERFORM estrai_dati TABLES gt_dati.

IF NOT gt_dati[] IS INITIAL.
    PERFORM crea_excel USING gt_dati.
ELSE.
    MESSAGE e000(db) WITH 'Non esistono dati da estrarre'.
ENDIF.

END-OF-SELECTION.

*&---------------------------------------------------------------------*
*&      Form SEARCH_SERVER
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
* --> p1        text
* <-- p2        text
*----------------------------------------------------------------------*
FORM search_server .
CLEAR: p_filese.
CALL FUNCTION '/SAPDMC/LSM_F4_SERVER_FILE'
    IMPORTING
      serverfile       = p_filese
    EXCEPTIONS
      canceled_by_user = 1
      OTHERS           = 2.
ENDFORM.

*&---------------------------------------------------------------------*
*&      Form ESTRAI_DATI
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
* --> p1        text
* <-- p2        text
*----------------------------------------------------------------------*
FORM estrai_dati TABLES pt_dati TYPE tt_dati.

* Estraggo dati da esportare
SELECT ebeln bsart aedat ernam
     INTO TABLE pt_dati
     FROM ekko
     UP TO 10 ROWS
     ORDER BY PRIMARY KEY.

ENDFORM.
*&---------------------------------------------------------------------*
*&      Form CREA_EXCEL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
* --> p1        text
* <-- p2        text
*----------------------------------------------------------------------*
FORM crea_excel USING pt_dati TYPE tt_dati.
DATA: ls_table_settings       TYPE zexcel_s_table_settings.

CHECK NOT pt_dati[] IS INITIAL.

CLEAR: ls_table_settings.

" Creates active sheet
CREATE OBJECT lo_excel.

" Get active sheet
lo_worksheet = lo_excel->get_active_worksheet( ).
lo_worksheet->set_title( ip_title = 'Dati EKKO').

ls_table_settings-table_style       = zcl_excel_table=>builtinstyle_medium2.
ls_table_settings-show_row_stripes = abap_true.
ls_table_settings-nofilters         = abap_true.

lo_worksheet->bind_table( ip_table          = pt_dati
                            is_table_settings = ls_table_settings ).

lo_worksheet->freeze_panes( ip_num_rows = 3 ). "freeze column headers when scrolling
column_dimension = lo_worksheet->get_column_dimension( ip_column = 'E' ). "make date field a bit wider
column_dimension->set_width( ip_width = 11 ).

*** Create output
lcl_output=>output( lo_excel ).

ENDFORM.