topic Re: Capturing ALV layout converting it into XLS file in Application Development and Automation Discussions

Capturing ALV layout converting it into XLS file

walkerist79 — Tue, 16 May 2023 10:01:49 GMT

Hi, this local file button were able to capture the current column layout of the ALV. Now, I was able to save in a layout in a variant named ZALV_LOUT1.

I'm trying to do is

SA38: Execute the program for displaying this ALV> Input the variant, and input the email address in the field wherein the report in XLS/Excel file will be sent> Execute(When executed, the column positions of ALV should be the same in the XLS file> Then the will be displayed.

The default layout of ALV:

Student# | Name | Address | Age

Layout of ALV in variant ZALV_LOUT1:

Name | Student # | Age | Address.

When saving the alv into the local file, the layout is correct but when sending it into my email, it is not.

Re: Capturing ALV layout converting it into XLS file

jens_michaelsen — Tue, 16 May 2023 10:50:49 GMT

I understand, that you want to write a report with 2 input fields. One input field for layout of alv-grid, one input field for e-mail adress. When the report is executed, you want to send an excel file with data and the positions of the columns according to the layout.

You can build an excel file using classes CL_SALV_EX_UTIL=>FACTORY_RESULT_DATA_TABLE and CL_SALV_BS_LEX=>EXPORT_FROM_RESULT_DATA_TABLE.

For CL_SALV_EX_UTIL=>FACTORY_RESULT_DATA_TABLE you need the data and an actual fieldcatalog, which describes the positions of the columns.

If you are use the FM REUSE_ALV_GRID_DISPLAY in your report, you get the fieldcatalog (and the data) using the methods of the class CL_SALV_BS_RUNTIME_INFO. You can use this class, if you start your original report by a second report per SUBMIT.

FIELD-SYMBOLS <fs_itab> TYPE STANDARD TABLE.

    cl_salv_bs_runtime_info=>set(
    display  = abap_false    "supress display of alv_grid
    metadata = abap_true
    data     = abap_true ).

    SUBMIT my_report_with_reuse_alv_grid_display USING SELECTION-SCREEN '1000'
          WITH p_vari = p_vari   "name of the layout
    AND RETURN.

    TRY.
        cl_salv_bs_runtime_info=>get_data_ref(
        IMPORTING r_data = DATA(gr_alv_itab) ).

        ASSIGN gr_alv_itab->* TO <fs_itab>.
        DATA(gs_meta) = cl_salv_bs_runtime_info=>get_metadata( ).
        cl_salv_bs_runtime_info=>clear_all( ).

        DATA(go_salv_ex_res) = cl_salv_ex_util=>factory_result_data_table( r_data          = REF #( <fs_itab> )
                                                                          t_fieldcatalog  = gs_meta-t_fcat ).
        DATA:gv_xls TYPE xstring.
        cl_salv_bs_lex=>export_from_result_data_table( EXPORTING
                                                         is_format            = if_salv_bs_lex_format=>mc_format_xlsx
                                                         ir_result_data_table = go_salv_ex_res
                                                       IMPORTING
                                                         er_result_file       = gv_xls ).

        PERFORM send_mail USING p_email         "mailadress
                           'Hello world'        "mail content
                                  gv_xls .      "excel as xstring for attachment 

      CATCH cx_root INTO DATA(e_txt).
        MESSAGE e_txt->get_text( ) TYPE 'S'.
    ENDTRY.

Re: Capturing ALV layout converting it into XLS file

walkerist79 — Tue, 16 May 2023 14:46:37 GMT

Thanks for this and for the effort.

I'm trying to fix an existing program that were already able to send the attachment in .XLS format when the program is executed and before the ALV shows up on screen. However, I'm having trouble on how the xls is built.

My details is found in table T_FINAL_DATA, and were looped into X_FINAL_DATA. However my T_FINAL_DATA table were coded like this:

DATA:begin of t_data  occurs 0
        werks like ekpo-werks,
        eindt like ekes-eindt,
        uzeit like ekes-uzeit,
        slfdt TYPE eket-slfdt,
        ekgrp like ekko-ekgrp.
     end of t_data.
LOOP AT t_data INTO x_data .
  MOVE-CORRESPONDING x_data  TO x_data_final .
  APPEND x_data_final  TO t_data_final .
ENDLOOP.

CONCATENATE

x_data_final-werks

x_data_final-eindt

x_data_final-uzeit

x_data_final-slfdt

x_data_final-ekgrp

INTO x_attachment-line SEPARATED BY '#'.
APPEND x_attachment TO t_attachment.

Then this t_attachment were used to some FM *XSTRING* to build the XLS.

My dilemna is sometimes the UZEIT is first in column in ALV however, in the attachment, the WERKS is still the first. Is it because of how is it concatenated? if yes, what is the possible solution?

x_data_final-werks

x_data_final-eindt

x_data_final-uzeit

x_data_final-slfdt

x_data_final-ekgrp

CONCATENATE

x_data_final-werks

x_data_final-eindt

x_data_final-uzeit

x_data_final-slfdt

x_data_final-ekgrp
INTO x_attachment-line SEPARATED BY lc_tab.
APPEND x_attachment TO t_attachment.

Then this t_attachment were used to some FM *XSTRING* to build the XLS.

My dilemna is sometimes the UZEIT is first in column in ALV however, in the attachment, the WERKS is still the first. Is it because of how is it concatenated? if yes, what is the possible solution?

x_data_final-werks

x_data_final-eindt

x_data_final-uzeit

x_data_final-slfdt

x_data_final-ekgrp

Re: Capturing ALV layout converting it into XLS file

walkerist79 — Tue, 16 May 2023 14:54:26 GMT

What I thought is this:

MOVE x_data_final TO X_ATTACHMENT-LINE.

However, the arrangement of x_data_final is not aligned with the alv column layout.

Re: Capturing ALV layout converting it into XLS file

jens_michaelsen — Wed, 17 May 2023 04:04:55 GMT

Okay I think you have to concatenate the fixed structure dynamically into x_attachment-line according to the position of columns in the layout of ALV-grid. So you need a list with fieldnames of the column in order by position. In other words you need an actual fieldcatalog.

You can get the fieldcatalog in this way like my example describes. On the other hand you can get the actual fieldcatalog according to the layout also using FM LT_DBDATA_READ_FROM_LTDX.

At first you fetch an LVC-fieldcatalog for your structure using FM LVC_FIELDCATALOG_MERGE. After that you can synchronize it with the result of FM LT_DBDATA_READ_FROM_LTDX. And then you can concatenate dynamically using the fieldcatalog.

  CLEAR gs_ltdx. "gs_ltdx like structure LTDX
  SELECT SINGLE * FROM ltdx INTO gs_ltdx
    WHERE relid = 'LT'
    AND report = sy-repid
    AND variant = p_vari.  "contains name of layout
  MOVE-CORRESPONDING gs_ltdx TO gs_varkey.

  CALL FUNCTION 'LT_DBDATA_READ_FROM_LTDX'
    EXPORTING
*     I_TOOL       = 'LT'
      is_varkey    = gs_varkey  "gs_varkey is like structure LTDXKEY
    TABLES
      t_dbfieldcat = gt_dbfcat "Note: this is not a LVC-fieldcatalog, gt_dbfcat is table with structure LTXDATA
*     T_DBSORTINFO =
*     T_DBFILTER   =
*     T_DBLAYOUT   =
    EXCEPTIONS
      not_found    = 1
      wrong_relid  = 2
      OTHERS       = 3.
  IF sy-subrc <> 0.
* Implement suitable error handling here
  ENDIF.

* Synchronize LVC-fieldcatalog by fieldcatalog from layout
  DELETE gt_dbfcat WHERE param NE 'COL_POS' AND param NE 'NO_OUT'.

  LOOP AT gt_dbfcat ASSIGNING FIELD-SYMBOL(<dbfcat>).
    READ TABLE gt_fieldcat ASSIGNING FIELD-SYMBOL(<fcat>)  "LVC-fieldcatalog getting before with FM LVC_FIELDCATALOG_MERGE 
    WITH KEY fieldname = <dbfcat>-key1.
    IF sy-subrc EQ 0.
      ASSIGN COMPONENT <dbfcat>-param OF STRUCTURE <fcat> TO <target>.
      <target> = <dbfcat>-value.
    ENDIF.
  ENDLOOP.

Re: Capturing ALV layout converting it into XLS file

walkerist79 — Wed, 17 May 2023 05:45:32 GMT

jens_michaelsen Thanks! But I'm not familiar on the the data types of the following:

gs_ltdx like LTDX

gs_varkey like LTDXKEY

gt_dbfcat <--I'm not sure how to declare this. This is what I did: gt_dbfcat LIKE TABLE OF LTDXDATA.

Thanks

Re: Capturing ALV layout converting it into XLS file

walkerist79 — Wed, 17 May 2023 12:20:44 GMT

I'm encountering issues with LT_DBDATA_READ_FROM_LTDX. It returns a result of "WRONG_RELID". How do I know why does that happen?

  DATA: gs_varkey LIKE LTDXKEY,
        gt_dbfcat LIKE TABLE OF LTDXDATA.
  DATA: lt_fieldcat_fg TYPE STANDARD TABLE OF ltdxdata.
  SELECT *
    FROM ltdx
    INTO @DATA(ls_ltdx)
    UP TO 1 ROWS
    WHERE relid = 'LT'
    AND report = @sy-repid
    AND variant = @p_layout.
  ENDSELECT.
  IF sy-subrc = 0.
    DATA(ls_varkey_fg) = CORRESPONDING ltdxkey( ls_ltdx ).
    CALL FUNCTION 'LT_DBDATA_READ_FROM_LTDX'
      EXPORTING
        i_tool       = 'LT'
        is_varkey    = ls_varkey_fg 
      TABLES
        t_dbfieldcat = lt_fieldcat_fg
      EXCEPTIONS
        not_found    = 1
        wrong_relid  = 2
        OTHERS       = 3.