Download web dynpro data to excel with embed images

[alvin_fan]

Hi experts,

When download web dynpro data to excel works fine however the company logo cannot be embed in the excel.

As I cannot find solution anywhere on this topic.

Any guidance is greatly appreciated.

Requirement:

On web dynpro screen, press "download" button, download the data (include company logos(image)) into excel

Solution:

I've tried below 2 solutions, but it didn't work.

Soltion 1->only work for SAP GUI,for web dynpro, when calling function module DOWNLOAD_WEB_OBJECT, a short dump "Exception condition "NOT_SUPPORTED_BY_GUI" triggered" will be triggered.

1) Upload excel template to SAP server with SMW0

2) Calling function module DOWNLOAD_WEB_OBJECT to download the excel template

3) Use OLE2 method write the data to excel

4) Close excel

Solution 2:->it works fine,however the company logo cannot be embed in the download excel

1) Convert excel to XML file (as an Xml Spreadsheet (Excel 2003))

2) Convert XML into string

3) Convert string to Xstring(binary data)

4) Calling method "wdr_task=>client_window->client->attach_file_to_response" to download excel

Related issue/reference:

https://docs.microsoft.com/en-us/previous-versions/office/developer/office-xp/aa140066%28v%3doffice....

https://bytes.com/topic/xml/answers/697249-images-xml-read-into-excel

https://stackoverflow.com/questions/2200056/excel-xml-spreadsheet-is-it-possible-to-embed-images

Current XML:

<Cell ss:Index="2" ss:MergeAcross="2" ss:StyleID="s33" ss:HRef= "C:\Temp\Companylogo.bmp">

<Data ss:Type="String">..\Temp\Companylogo.bmp</Data></Cell>

Below xml also didn't work:

<Cell ss:Index="2" ss:MergeAcross="2" ss:StyleID="s33" ss:HRef= "http://XXX.XXX.com.cn:8000/sap/bc/webdynpro/sap/zexp_report/0050568169F41EE8B8FC90460C4FCB45135835">

<Data ss:Type="String">"http://XXX.XXX.com.cn:8000/sap/bc/webdynpro/sap/zexp_report/0050568169F41EE8B8FC90460C4FCB45135835"</Data></Cell>

BR

Alvin

[alvin_fan]

Issue was resolved.

Solution provided in the below.

Precondition：Download tool "ABAP2XLSX"

1) upload Excel template to SAP server using T-code:SMW0
2) Download template
3) Write data
4) Download Excel to local disk

Sample code:

FIELD-SYMBOLS: <mime> TYPE w3mime.
DATA:
lo_excel_reader TYPE REF TO zif_excel_reader,
lo_excel TYPE REF TO zcl_excel,
lo_writer TYPE REF TO zif_excel_writer,
ls_key TYPE wwwdatatab,
lt_mime TYPE TABLE OF w3mime,
lv_xdata TYPE xstring,
lv_mime_type TYPE string,
lv_mime_file TYPE xstring,
lv_filename TYPE string.

ls_key-relid = 'MI'.
ls_key-objid = 'Z_TEMPLATE'.
CALL FUNCTION 'WWWDATA_IMPORT'
EXPORTING
key = ls_key
TABLES
mime = lt_mime
EXCEPTIONS
OTHERS = 3.
IF sy-subrc <> 0.
* Implement suitable error handling here
RETURN.
ENDIF.

LOOP AT lt_mime ASSIGNING <mime>.
lv_mime_file = lv_mime_file && <mime>-line.
ENDLOOP.

CREATE OBJECT lo_excel_reader TYPE zcl_excel_reader_2007.
lo_excel_reader->load(
EXPORTING
i_excel2007 = lv_mime_file " Excel 2007 data
RECEIVING
r_excel = lo_excel ). " Excel creator

lo_excel->set_active_sheet_index( i_active_worksheet = 1 ).

lo_excel->get_active_worksheet( )->set_cell(
ip_column = 'E'
ip_row = 9
ip_value = 'Fruits 123566666' ).

CREATE OBJECT lo_writer TYPE zcl_excel_writer_2007.
lv_xdata = lo_writer->write_file( lo_excel ).

lv_filename = 'download7878787878.xls'.
lv_mime_type = 'EXCEL'.
wdr_task=>client_window->client->attach_file_to_response(
i_filename = lv_filename
i_content = lv_xdata
i_mime_type = lv_mime_type ).