Download Internal Table to Excel

[Former Member]

Hi All,

I m new to Webdynpro for ABAP and i m trying to download the data as it is in the Internal Table to excel file using FM 'CRM_IC_XML_STRING2XSTRING' ( to convert string to xstring) and CLASS 'cl_wd_runtime_services=>attach_file_to_response' ( to attach the content in excel).

The export is working fine, but it also has some extra rows and columns with data "/asx:abap".

Also the header is appended with data like "/@version/#agg".

The columns were sorted by its name and gets interchanged and sometimes repeated too.

Is there a way to get the header formatted and trim the unwanted rows and columns appearing.

Thanks in advance,

Raju J

[Former Member · Accepted solution]

Follow these steps; -> First read the table's data into an internal table. -> Convert the internal table data to STRING format. -> Now convert it into tab separated format as how desired. -> Convert this STRING format to XSTRING format -> Make use of the attach_file_to_response method. Check this code. It will help!

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.
 
** Now you need to add the column headers & the desired extra information through coding to 
** mara_string
 
  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.XLS'
                                                    i_content   = mara_xstring
                                                    i_mime_type = 'EXCEL' ).
ENDMETHOD.

Regards, Radhika.

[Former Member · Accepted solution]

Thanks Radhika,

This helped in finishing my requirement.But only thing is while I am downloading the filed names at the Bottom of the records fetched.How I can get the Field Names at the top.

[Former Member]

this code provide a excel file but the format is TAB Delimiter not excel workbook. both are same in looking but i want it as excel workbook.

can you help me.??

thankx in advns.

[Former Member]

hi,

After getting data into internal table which u have bind with ALV Data node, u can configure ALV using following code :

suppose i have used SALV as used component for SALV_WD_TABLE in my component than,

DATA LO_INTERFACECONTROLLER TYPE REF TO IWCI_SALV_WD_TABLE .

LO_INTERFACECONTROLLER = WD_THIS->WD_CPIFC_SALV( ).

DATA: LO_VALUE TYPE REF TO cl_salv_wd_config_table.

CALL METHOD LO_INTERFACECONTROLLER->GET_MODEL

RECEIVING

VALUE = LO_VALUE.

now using this LO_VALUE u can make visible EXPORT Button in ALV which exports your ALV Data to Excel directly:

CALL METHOD LO_VALUE->IF_SALV_WD_STD_FUNCTIONS~SET_EXPORT_ALLOWED

EXPORTING

VALUE = abap_true.

now run your application and click on export button , which will ask u where u want to export as EXCEL, give location and it would download it.

Plz do the needful ...

Thanks ,

Saurin Shah

[Former Member]

Thank you very much Martina & Radhika for your reply.

Its good idea of using ALV.

My code was similar to Radhika's suggestion. But still the problem persisted. Then i tried creating an XSLT object and used it in the CALL TRANSFORMATION and it worked fine.

Earlier i dint use any XSLT in CALL TRANSFORMATION. So that was the problem.

Thanks,

Raju J

Edited by: Raju Jayaraman on Aug 25, 2009 8:34 AM

[Former Member]

Hi Raju,

I am also facing same problem to download excel sheet.

Can you share your approach how did you solve your problem.

Thanks

Venkat.

[Former Member]

Hi Vankat,

In last thread i have posted that creating an XLST for the xml will solve the problem. But still it was working fine in MS Excel 2007 and not in the older versions in my case.

Again, i thought of following the method adviced by Radhika(in the previous post).

Loop your internal table and concatenate the content to a string variable SEPARATED BY cl_abap_char_utilities=>horizontal_tab.

Then convert the string to XSTRING using the below FM.

CALL FUNCTION 'SCMS_STRING_TO_XSTRING'

EXPORTING

text = l_outstream "Concatenated string

IMPORTING

buffer = l_content. "Resulted xstring

Then call the cl_wd_runtime_services as mentioned below.

cl_wd_runtime_services=>attach_file_to_response(

i_filename = 'filename.xls'

i_content = l_content "Resulted xstring

i_mime_type = 'EXCEL'

i_in_new_window = abap_true ).

This is one way and the other is,

If you want to do it using

CALL TRANSFORMATION ZXSLT "While creating ZXSLT select the prog. type as XSLT program.

SOURCE tab = internal_table[]

RESULT XML l_outstream.

Then convert the l_outstream to xstring and attach to cl_wd_runtime_services.

ZXSLT will contain code like this,

<xsl:transform version="1.0"

xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

xmlns:asx="http://www.sap.com/abapxml"

xmlns:x="urn:schemas-microsoft-com:office:excel">

<xsl:strip-space elements="*"/>

<xsl:template match="asx:abap/asx:values/TAB">

<items>

<xsl:for-each select = "item">

<it>

<OBJECT_ID><xsl:value-of select="OBJECT_ID"/></OBJECT_ID>

<DESCRIPTION><xsl:value-of select="DESCRIPTION"/></DESCRIPTION>

.

.

.

.

</it>

</xsl:for-each>

</items>

</xsl:template>

</xsl:transform>

I think this will solve your problem.

Regards,

Raju J

[Former Member]

Hi Raju,

Thank you for reply. It helped to me.

I have created XSLT program and parsing data through XSLT.

i have a small problem here. when i open excel file there is a pop-up message before open excel file like below.

Open XML

Please select how you would like to open this file:

As an XML table

As a erad-only workbook

Use the XML source task pane.

When i choose option As an XML table i could open data in excel file.

Is there any way that i can avoid Open XML pop-up.

Thanks in Advance

Venkat

[Former Member]

Hi Venkat,

As i told, i also faced the same problem.

That is why i switched to the first method of loop the table and appending to string.

I m not sure about how to fix this problem.

Regards,

Raju J

[Former Member]

Hi Raju,

Check the below link to display your table output using ALV which has an inbuilt option to export to excel sheet.

/people/kamalkumar.ramakrishnan/blog/2007/01/11/integrating-alv-in-the-web-dynpro-for-abapwda-application

Thanks

Martina