Timeout when using ALV build-in functionality: exporting to Excel

[Former Member]

Hello,

I have the following problem within one of my web dynpro abap applications:

I need to be able to display data in a table that preferably should be via alv component.

Then I shall also be able to download the displayed data via Excel.

So I have embedded the ALV component I added to the component within a view and activated the export to excel button.

Downloading small amount of data does no lead to any problem, but when trying to process a larger amount of data (in this case 22500 entries), I get a timeout.

Does anybody have any idea if it is possible to increase performance here without reducing the amount of data to be downloaded?

thanks a lot ion advance!

Ana

[thomas_jung · Accepted solution]

There really isn't much you can do to affect the performance of this operation since the code is all within SAP's components. Make sure that your servers aren't overloaded with requests and have plenty of physical memory. The operation is done via a XML transformation so the amount of physical memory is critical. If you are swapping heavily to disk, a large transformation can take a serious hit. Make sure you system is up to date on Kernel and Support Packages. However these are general things your Basis people should be looking out for.

You might have to consider doing the export yourself. If you do an export as Text Tab Delimited file, it can generally be processed faster and with less memory requirements than an XML transformation. You would have to loop at the data and build the text string yourself. I've done very large tables this way without timeout - although it will still require 10s of seconds to complete. Here is a blog where I show how to do this process in BSP - but the core logic can be adapted to Web Dynpro:

/people/thomas.jung3/blog/2004/09/02/creating-a-bsp-extension-for-downloading-a-table If you know the table structure, you can make this even more performant by using fixed data types instead of all the dynamic processing.

You lose some of the features of XML based download, but you get the core data downloaded all the same.

[Former Member · Accepted solution]

Hello Thomas,

thank you for the detailed answer!

I will check with the basis team and if any performance enhancement is possible here.

If not or not sufficiantly I will try the second step.

I will let you know if and how the problem can be solved

Thank you!

Ana

[Former Member · Accepted solution]

Hello Thomas,

sorry for coming back to this so late, but I got distracted from this issue.

Nevertheless, I just came back to it today and tried to follow your blog but somehow did not manage to download the data.

I prepared the data as described in your blog and then used cl_gui_frontend_services=>gui_download functionality. You did not mention this in your blog, so is it correct to use this service method or shall I use some other functionality?

Nevertheless, using cl_gui_frontend_services=>gui_download I suppose downloading via ASCII format in web dynpro abap context is no good idea!? I tried just to be complete and got unknown error exception.

I also tried the binary call transferring the data into binary mode as given in your blog, but then I have the problem that I do not know how to get the bin filesize, which is, as I understand, crucial for using the download method.

I tried using the ixml stream factory because I did not find anything else, but as expected, I did not succeed. Have you got any idea, how i can actually download the data?

Thanks a lot in advance!!!

Ana

[thomas_jung · Accepted solution]

You can't use cl_gui_frontend_services=>gui_download from Web Dynpro. There is no connection to the SAPGUI in Web Dynpro. For downloading from Web Dynpro you must either use the fileDownlaod UI element:

http://help.sap.com/saphelp_nw70ehp1/helpdata/en/09/a5884121a41c09e10000000a155106/frameset.htm

Or CL_WD_RUNTIME_SERVICES=>ATTACH_FILE_TO_RESPONSE

http://help.sap.com/saphelp_nw70ehp1/helpdata/en/47/a8d95673b12fe2e10000000a42189d/frameset.htm

[Former Member · Accepted solution]

ok, that explains a lot! Thank you!

I now tried the fileDownlaod UI approach.

So I added

-the UI element to my view,

-a context that holds the data that shall be downloaded (which i fill before i get to the view where I placed the UI file download element)

I mapped the data of the UI element to the new context

I added the path to where the data shall be written to the file property

and now when i press the link in the view, I misteriously get a new tab in my explorer that holds a page with chinese letters.

i must say, i was impressed, as usually I have quite some problems generating chines letters;-)

..nevertheless, this is not what I actually need... No file was created here.

May be I need to set some other parameter or encoding information here?

Tx a lot

Ana

[uday_gubbala2 · Accepted solution]

Hi Annette,

Not sure as to why you get that tab with chinese letters in it but I can suggest you an workaround for not using the FileDownload ui element.

Below is the solution if you aren't to use the FileDownload UI element.

1) First read the table's data into an internal table.

2) Convert the internal table data to STRING format.

3) Now convert it into tab separated format as how desired.

4) Convert this STRING format to XSTRING format

5) Make use of the attach_file_to_response method.

Regards,

Uday

METHOD onactionon_submit .
  DATA: lv_node TYPE REF TO if_wd_context_node,
        lt_mara TYPE if_main=>elements_mara,
        wa_mara TYPE if_main=>element_mara,
        lead_selection_index TYPE i,
 
        mara_string  TYPE string,
        mara_xstring TYPE xstring.
 
  lv_node = wd_context->get_child_node( name = 'MARA' ).
  CALL METHOD lv_node->get_static_attributes_table
    IMPORTING
      table = lt_mara.
 
  LOOP AT lt_mara INTO wa_mara.
    CONCATENATE mara_string
                wa_mara-matnr
                wa_mara-ersda
                wa_mara-ernam
                wa_mara-matkl
                wa_mara-meins
                cl_abap_char_utilities=>cr_lf INTO mara_string
                                        SEPARATED BY cl_abap_char_utilities=>horizontal_tab.
  ENDLOOP.
 
  CALL FUNCTION 'SCMS_STRING_TO_XSTRING'
    EXPORTING
      text   = mara_string
    IMPORTING
      buffer = mara_xstring.
 
 
  CL_WD_RUNTIME_SERVICES=>attach_file_to_response(  i_filename  = 'TEMP.DOC'
                                                    i_content   = mara_xstring
                                                    i_mime_type = 'WORD' ).
ENDMETHOD.

The above is the code to export the Internal Table to Word Document. You can proceed as shown below for Excel & NOTEPAD formats.

To Export the Internal Table to Text File:

WDR_TASK=>CLIENT_WINDOW->CLIENT->ATTACH_FILE_TO_RESPONSE(
    I_FILENAME    = 'WDP.txt'
    I_CONTENT     =  mara_xstring
    I_MIME_TYPE   = 'NOTEPAD' ).

To Export the Internal Table to Excel File:

WDR_TASK=>CLIENT_WINDOW->CLIENT->ATTACH_FILE_TO_RESPONSE(
    I_FILENAME    = 'Excel.xls'
    I_CONTENT     =  mara_xstring
    I_MIME_TYPE   = 'EXCEL' ).

[thomas_jung · Accepted solution]

I'm not sure why you mixed your suggestions to use CL_WD_RUNTIME_SERVICES=>attach_file_to_response in one part and WDR_TASK=>CLIENT_WINDOW->CLIENT->ATTACH_FILE_TO_RESPONSE in another part - but you should never use WDR_TASK=>CLIENT_WINDOW->CLIENT->ATTACH_FILE_TO_RESPONSE directly. That is why SAP supplies the public API in the form of CL_WD_RUNTIME_SERVICES=>attach_file_to_response. It is not safe to call the inner WDR_TASK object directly and SAP will not be responsible if we change the structure of this object in the future and it breaks your coding.

[Former Member · Accepted solution]

Hello,

thank you far all your replies!

Please excuse for coming back so late to this, but I kind of got stuck up within another project.

To be honest, I am now a bit confused as to how I can properly download the data.

First i would like to know as why I get this chinese lettered tab in the browser. I 've got the feeling that this might be actually the data I want to download, but it is encrypted in the wrong way and instead of loading the data into the file with the name C:\TEMP\CMX.xls the web dynpro application just loads and opens it in the browser (with the wrong encryption). The path name here is:

http://<Company Web Server>/sap/bc/webdynpro/sap/zz_ksb_cmx_plan_view/~wd_key/C/TEMP/CMX.xls?sap-contextid=SID%3aANON%3asapc20ms_C20_31%3aTRghdrP3gNzzP1u-EO0BJoRI2gxz7ZwtYT1_zm0j-NEW&sap-wd-resource-id=WD29&sap-wd-filedownload=X

Anybody has an idea as to if there is a chance to get this fixed?

I will look into the response mechanism next, as soon as I get some spare time, which hopefully will be tomorrow.

Thanks in advance for all your help!

Ana

[Former Member]

Hello,

coming back to the UI element download.

I managed to get the data being saved to the desktop, but unfortunately, the data remains being displayed as in chines letters.

Anybody got an idea, what parameters I might have set wrong? Or if I have to do an additional encoding before generating the XString?

Here is the supply method code and the parameters I have set in the UI element:

The Supply method

method GEN_EXCEL_DOWNLOAD_DATA .
  data: wa_cmx_line type zksb_cmxshowline.
  data: lt_excel type zksb_cmx_excel_download_t.
  data: wa_excel type zksb_cmx_excel_download.
  data: l_guid type guid_32.
  data: l_url type string.

* read context-------------------------------------------------------------------------------------
  data lo_nd_z_ksb_get_prcm type ref to if_wd_context_node.
  data lo_nd_exporting type ref to if_wd_context_node.
  data lo_nd_rt_zksb_cmx_show type ref to if_wd_context_node.
  data lo_el_rt_zksb_cmx_show type ref to if_wd_context_element.
  data lt_rt_zksb_cmx_show type table of wd_this->element_rt_zksb_cmx_show.
  data wa_rt_zksb_cmx_show like line of lt_rt_zksb_cmx_show.
*   navigate from <CONTEXT> to <Z_KSB_GET_PRCM> via lead selection
  lo_nd_z_ksb_get_prcm = wd_context->get_child_node( name = wd_this->wdctx_z_ksb_get_prcm ).
*   navigate from <Z_KSB_GET_PRCM> to <EXPORTING> via lead selection
  lo_nd_exporting = lo_nd_z_ksb_get_prcm->get_child_node( name = wd_this->wdctx_exporting ).
*   navigate from <EXPORTING> to <RT_ZKSB_CMX_SHOW> via lead selection
  lo_nd_rt_zksb_cmx_show = lo_nd_exporting->get_child_node( name = wd_this->wdctx_rt_zksb_cmx_show ).
*   @TODO handle not set lead selection
  if lo_nd_rt_zksb_cmx_show is initial.
  endif.
* get static attributes table
  lo_nd_rt_zksb_cmx_show->get_static_attributes_table( importing table = lt_rt_zksb_cmx_show ).

* reduce data to only the data that shall be displayed----------------------------------------
  loop at lt_rt_zksb_cmx_show into wa_rt_zksb_cmx_show.
    move-corresponding wa_rt_zksb_cmx_show to wa_excel.
    append wa_excel to lt_excel.
  endloop.

* populate data as tab separated strings-----------------------------------------------------
  data l_str  type string.
  data lv_struct type extdfiest.
  data itab type ref to zksb_cmx_excel_download_t.
  data r_xstring type xstring.
  data s(256) type c.
  field-symbols <wa_desc> like line of lv_struct.
  field-symbols: <tab> type table.
  field-symbols:  <wa> type any.
  field-symbols:  <f> type any.

* populate the column headers----------------------
  get reference of lt_excel into itab.
* assign lt_excel to <tab>.
  assign itab->* to <tab>.
  lv_struct = wd_this->get_table_structure( itab = itab ).

  loop at lv_struct assigning <wa_desc>.
    concatenate wa_cmx_line
                <wa_desc>-coltitle
                cl_abap_char_utilities=>horizontal_tab
                into wa_cmx_line.
  endloop.
  concatenate wa_cmx_line
              cl_abap_char_utilities=>cr_lf
              into wa_cmx_line.

* loop through the data table-----------------------
  loop at <tab> assigning <wa>.
    loop at lv_struct assigning <wa_desc>.
      assign component sy-tabix of structure <wa> to <f>.
      check sy-subrc = 0.
      if <wa_desc> is assigned and <wa_desc>-convexit is not initial.
*  Process any output conversion routines
        concatenate 'CONVERSION_EXIT_' <wa_desc>-convexit '_OUTPUT' into wa_cmx_line.
        call function wa_cmx_line
          exporting
            input  = <f>
          importing
            output = s.
      else.
        concatenate wa_cmx_line
                 <f>
                  cl_abap_char_utilities=>horizontal_tab
                  into wa_cmx_line.
      endif.
    endloop.
    concatenate wa_cmx_line
                cl_abap_char_utilities=>cr_lf
                into wa_cmx_line.
  endloop.

*Convert the strings to Binary strings (UTF-16le)------------------------------------------------
  call function 'SCMS_STRING_TO_XSTRING'
    exporting
      text     = wa_cmx_line
      mimetype = ''
    importing
      buffer   = r_xstring.
*Add the UTF-16 Little Endian Byte Order Mark to the begining of the file
  concatenate  cl_abap_char_utilities=>byte_order_mark_little
               r_xstring
               into r_xstring in byte mode.

* set context
  DATA lo_nd_excel_download TYPE REF TO if_wd_context_node.
  DATA lo_el_excel_download TYPE REF TO if_wd_context_element.
  DATA ls_excel_download TYPE wd_this->element_excel_download.
  DATA lv_excel_download_data LIKE ls_excel_download-excel_download_data.
* navigate from <CONTEXT> to <EXCEL_DOWNLOAD> via lead selection
  lo_nd_excel_download = wd_context->get_child_node( name = wd_this->wdctx_excel_download ).
* get element via lead selection
  lo_el_excel_download = lo_nd_excel_download->get_element(  ).
* set single attribute
  lo_el_excel_download->set_attribute( name =  `EXCEL_DOWNLOAD_DATA`  value = r_xstring ).

endmethod.

and the parameters to the UI are as follows

behaviour: allowSave
data: <data that was created by supply method>
fileName: CMX.xls          also tried CMX.cvs
mime-type: ms-excel     also tried  /vnd.ms-excel
text: $OTR:SOTR_VOCABULARY_BASIC/DOWNLOAD
textDirection: inherit
type: navigation
visible: Visible

Any suggestion of what I might have to change would be great!

Tx

Ana

[Former Member]

Hi,

thanks to everybody.

I just wanted to give the information, that I solved the problem.

What I did:

within the component controller, I created

a context node with a supply function and a context attribute, the context attribute being of type XString

Within the supply function I added the code for populating the attribute with the desired data as described above, leaving out the endian bite order mark, as this seems to be the problem of displaying data in chinese letters. So this leaves to:

method supply_file .
  data: wa_cmx_line type zksb_cmxshowline.
  data: lt_excel type zksb_cmx_excel_download_t.
  data: wa_excel type zksb_cmx_excel_download.
  data: l_guid type guid_32.
  data: l_url type string.
  data: conv_out type ref to cl_abap_conv_out_ce.

* read context-------------------------------------------------------------------------------------
  data lo_nd_z_ksb_get_prcm type ref to if_wd_context_node.
  data lo_nd_exporting type ref to if_wd_context_node.
  data lo_nd_rt_zksb_cmx_show type ref to if_wd_context_node.
  data lo_el_rt_zksb_cmx_show type ref to if_wd_context_element.
  data lt_rt_zksb_cmx_show type table of wd_this->element_rt_zksb_cmx_show.
  data wa_rt_zksb_cmx_show like line of lt_rt_zksb_cmx_show.
*   navigate from <CONTEXT> to <Z_KSB_GET_PRCM> via lead selection
  lo_nd_z_ksb_get_prcm = wd_context->get_child_node( name = wd_this->wdctx_z_ksb_get_prcm ).
*   navigate from <Z_KSB_GET_PRCM> to <EXPORTING> via lead selection
  lo_nd_exporting = lo_nd_z_ksb_get_prcm->get_child_node( name = wd_this->wdctx_exporting ).
*   navigate from <EXPORTING> to <RT_ZKSB_CMX_SHOW> via lead selection
  lo_nd_rt_zksb_cmx_show = lo_nd_exporting->get_child_node( name = wd_this->wdctx_rt_zksb_cmx_show ).
*   @TODO handle not set lead selection
  if lo_nd_rt_zksb_cmx_show is initial.
  endif.
* get static attributes table
  lo_nd_rt_zksb_cmx_show->get_static_attributes_table( importing table = lt_rt_zksb_cmx_show ).

* reduce data to only the data that shall be displayed----------------------------------------
  loop at lt_rt_zksb_cmx_show into wa_rt_zksb_cmx_show.
    move-corresponding wa_rt_zksb_cmx_show to wa_excel.
    append wa_excel to lt_excel.
  endloop.

* populate data as tab separated strings-----------------------------------------------------
  data l_str  type string.
  data lv_struct type extdfiest.
  data itab type ref to zksb_cmx_excel_download_t.
  data r_xstring type xstring.
  data s(256) type c.
  field-symbols <wa_desc> like line of lv_struct.
  field-symbols: <tab> type table.
  field-symbols:  <wa> type any.
  field-symbols:  <f> type any.

* populate the column headers----------------------
  get reference of lt_excel into itab.
* assign lt_excel to <tab>.
  assign itab->* to <tab>.
  lv_struct = wd_this->get_table_structure( itab = itab ).

  loop at lv_struct assigning <wa_desc>.
    concatenate wa_cmx_line
                <wa_desc>-coltitle
                cl_abap_char_utilities=>horizontal_tab
                into wa_cmx_line.
  endloop.
  concatenate wa_cmx_line
              cl_abap_char_utilities=>cr_lf
              into wa_cmx_line.

* loop through the data table-----------------------
  loop at <tab> assigning <wa>.
    loop at lv_struct assigning <wa_desc>.
      assign component sy-tabix of structure <wa> to <f>.
      check sy-subrc = 0.
      if <wa_desc> is assigned and <wa_desc>-convexit is not initial.
*  Process any output conversion routines
        concatenate 'CONVERSION_EXIT_' <wa_desc>-convexit '_OUTPUT' into wa_cmx_line.
        call function wa_cmx_line
          exporting
            input  = <f>
          importing
            output = s.
      else.
        concatenate wa_cmx_line
                 <f>
                  cl_abap_char_utilities=>horizontal_tab
                  into wa_cmx_line.
      endif.
    endloop.
    concatenate wa_cmx_line
                cl_abap_char_utilities=>cr_lf
                into wa_cmx_line.
  endloop.

*Convert the strings to Binary strings (UTF-16le)------------------------------------------------
  call function 'SCMS_STRING_TO_XSTRING'
    exporting
      text     = wa_cmx_line
      mimetype = 'application/vnd.ms-excel'
    importing
      buffer   = r_xstring.

* fill the file content
  data l_struct type if_componentcontroller=>element_excel_download.

  conv_out = cl_abap_conv_out_ce=>create( encoding = 'UTF-8' ).

  conv_out->convert(
    exporting
      data = r_xstring
    importing
      buffer = l_struct-excel_download_data ).

* bind node
  node->bind_structure( l_struct ).

Within the view I added a file download element with the following specific properties:

behaviour = auto
data = <attribute as created above>
filename = xyz.xls
mimetype = application/vnd.ms-excel
type = navigation

cheers

Ana