Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Capturing ALV layout converting it into XLS file

walkerist
Participant
0 Kudos
1,611

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.

1 ACCEPTED SOLUTION

jens_michaelsen
Participant
1,341

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.
6 REPLIES 6

jens_michaelsen
Participant
1,342

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.

0 Kudos
1,341

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

0 Kudos
1,341

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.

0 Kudos
1,341

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.


0 Kudos
1,341

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

0 Kudos
1,341

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.