Generate Excel Template file in application server for downloading in UI5

[agrawalaadhar8]

Hi All,

I need to download an excel template file on click of a download button in front-end.

I am calling my service using 'POST' and in back-end in the DPC_EXT CLASS~GET_STREAM method I am creating a file.

The problem is I am getting a tab delimited file instead of a proper excelsheet.

I require one header row(which shows the headings for columns) for around 50 columns in template.

No other data needs to be present in the excel.

Please help on how I can generate an excel file instead of delimited files.

Thanks!

Code:

DATA conv TYPE REF TO cl_abap_conv_out_ce.

CONCATENATE text-G01 text-G02 text-G03 text-G04 text-G05 text-G06 INTO f_data-data.
REPLACE ALL OCCURRENCES OF ',' IN f_data-data WITH cl_abap_char_utilities=>horizontal_tab.

conv = cl_abap_conv_out_ce=>create( encoding = 'UTF-8' endian = 'L' ).
conv->convert( EXPORTING data = f_data-data IMPORTING buffer = ls_stream-value ).

ls_header-name = 'Content-Disposition'.  
ls_stream-mime_type = 'application/vnd.ms-excel'.

CONCATENATE 'attachment; filename=' 'TEMPLATE.xls' INTO ls_header-value.
me->set_header( is_header = ls_header ).

[DoanManhQuynh · Accepted solution]

if your data is simple, you can try this:

TRY.
 cl_salv_table=>factory(
 IMPORTING
 r_salv_table = DATA(lr_table)
 CHANGING
 t_table = lt_tab ). " your data in this table
 CATCH cx_salv_msg .
 EXIT.
ENDTRY.

TRY.
 lt_fcat = cl_salv_controller_metadata=>get_lvc_fieldcatalog(
 r_columns = lr_table->get_columns( )
 r_aggregations = lr_table->get_aggregations( ) ).

 lr_result = cl_salv_ex_util=>factory_result_data_table(
 r_data = REF #( lt_tab )
 t_fieldcatalog = lt_fcat ).

 cl_salv_bs_lex=>export_from_result_data_table(
 EXPORTING
 is_format = if_salv_bs_lex_format=>mc_format_xlsx " based on sap version
 ir_result_data_table = lr_result
 IMPORTING
 er_result_file = DATA(lr_xstring) ).
 CATCH cx_salv_unexpected_param_value .
 EXIT.
ENDTRY.

then you can download lr_xstring by gui_download or cl_salv_export_xml_dialog=>download too.

another option is save your template in xml spreedsheet format, open it in notepad or notepad++ you will get the xml code of your template. copy its to transformation and use CALL TRANSFORMATION to set data to file and download (pls search sample on internet)

[agrawalaadhar8 · Accepted solution]

Thanks a lot

I tried this and it worked.

But I am now using MIME Repository to fetch the XLSX template(since it also has some highlighting in some columns and bold text).

[DoanManhQuynh · Accepted solution]

agrawalaadhar8 : transformation can handle hightlight and bold text too.

[ArthurParisius]

Alternatively you can checkout this solution creating xlsx file in background

[Tomas_Buryanek]

I recommend to use ABAP2XLSX to create proper XLSX format Excel documents in ABAP.

[agrawalaadhar8]

Hi Tomas

Thanks for response.

I am new to this. How I can implement the method from the git.

Is this the only way or I can do it some other way also?

Thanks

[Tomas_Buryanek]

agrawalaadhar8 installation is described on the GitHub page of ABAP2XLSX. And there are many DEMO programs in installation.

It is not the only way to create XLSX, but I recommend it.

[agrawalaadhar8]

Hi there is a new challenge I am facing now..

When this XLSX template file is uploaded I need to split the details of each column into a variable.

If this was CSV it would work fine, but I have multi-lingual characters and CSV doesn't understand and convert them to '?????'.

So how do I read the uploaded file in the application server.

I think I am receiving the file in XSTRING type in CREATE_STREAM method.

Please advise.

Thanks a lot

[DoanManhQuynh]

I think if you got ???? after upload its mean your system dont have that font so you may try to install font first. btw, you should close thread after get the answer and open new one for new issue