Help extracting WDA table into Excel

[Former Member]

Hi WDA mates,

I need to create a button to extract table data into a excel (xls) spreadsheet.

How do I get the exactly cells configuration from the table into the xls? Also I need to get the header into the excel file.

Here is the table I need to extract. This is the standard WDA component GRFN_SURVEY_RESPONSE (survey_response view).

Here is my code.

method onactionexport_to_excel .

   data lo_nd_questions type ref to if_wd_context_node.

   data lo_el_questions type ref to if_wd_context_element.

   data lt_questions type wd_this->elements_questions.

   data ls_questions type wd_this->element_questions.

   data text   type string.

   data xtext  type xstring.

   data lv_mime type  mimetypes-type.

* navigate from <CONTEXT> to <SFLIGHT> via lead selection

   lo_nd_questions = wd_context->get_child_node( name = wd_this->wdctx_questions ).

* get all declared attributes

   lo_nd_questions->get_static_attributes_table(

     importing

       table = lt_questions ).

   loop at lt_questions into ls_questions.

     concatenate text ls_questions-text

                 ls_questions-comments

                 ls_questions-zcomments_rev

                 cl_abap_char_utilities=>newline

                 "cl_abap_char_utilities=>cr_lf

                 into text. "separated by cl_abap_char_utilities=>horizontal_tab.

   endloop.

   call function 'SDOK_MIMETYPE_GET'

     exporting

       extension = '.XLS'

     importing

       mimetype  = lv_mime.

   call function 'SCMS_STRING_TO_XSTRING'

     exporting

       text     = text

       encoding = '4103'

     importing

       buffer   = xtext.

   concatenate  cl_abap_char_utilities=>byte_order_mark_little  xtext into  xtext in byte mode.

   call method cl_wd_runtime_services=>attach_file_to_response

     exporting

       i_filename      = 'Download.xls'

*     i_filename      = lv_filename

       i_content       = xtext

       i_mime_type     = 'EXCEL'

       i_in_new_window = abap_false

       i_inplace       = abap_false.

endmethod.

Here is what I'm getting into the xls:

Thank you

[vinita_kasliwal · Accepted solution]

Hey Fernando

From what I understand its basically coming to a single excel column instead of being spread across columns?

Can you try the below code and see if it works ?

METHOD onactionsave_excel .

  DATA: lt_cbp_list     TYPE         wd_this->elements_list,

        ls_cbp_list     TYPE         wd_this->element_list,

        lo_nd_list      TYPE REF TO  if_wd_context_node,

        lv_str          TYPE string,

        lv_date(10)     type c,

        lv_xstr         TYPE xstring.

" Read all data shown in a table to the internal table lt_cbp_list

  lo_nd_list        = wd_context->get_child_node( 'LIST' ).

  lo_nd_list->get_static_attributes_table(

    IMPORTING

      table = lt_cbp_list[] ).

** Generate a header line

  CONCATENATE

  Headine_1

  Heading_2

  Heading_3

              cl_abap_char_utilities=>newline

  INTO lv_str

SEPARATED BY cl_abap_char_utilities=>horizontal_tab.

generate the content in a single line by looping through all entries

  LOOP AT lt_cbp_list INTO ls_cbp_list.

CONCATENATE

ls_cbp_list-object_type

ls_cbp_list-object_id

ls_cbp_list-partner_fct

*ls_cbp_list-partner_guid

ls_cbp_list-pd_type

ls_cbp_list-process_type

INTO lv_str

SEPARATED BY cl_abap_char_utilities=>horizontal_tab.

  ENDLOOP.

**** Pass all data here

CALL FUNCTION 'SCMS_STRING_TO_XSTRING'

    EXPORTING

      text     = lv_str

*     MIMETYPE = ' '

*     ENCODING =

    IMPORTING

      buffer   = lv_xstr

    EXCEPTIONS

      failed   = 1

      OTHERS   = 2.

  IF sy-subrc <> 0.

* Implement suitable error handling here

  ENDIF.

  wdr_task=>client_window->client->attach_file_to_response(

    i_filename = 'table.xls'

    i_content =  lv_xstr

    i_mime_type = 'EXCEL' ).

ENDMETHOD.

Regards

Vinita

[Former Member · Accepted solution]

Thank you for your help, Vinita.

I got the header in the excel.

The problem now is at the first column "Pergunta". At the WDA you can see I have multiple lines at the same cell, but when it's exported to excel, it got messed up. Please take a look at the prints below and the code.

As far as I could see, the code is breaking line at '#' on the texts. Please let me know if I made my self clear.

How can I get the cell formated like at the WDA table?

WDA

Same WDA exported to Excel

Code:

"header

concatenate

   'Unidade Organizacional' 'Pergunta' 'Resposta' 'Comentários' 'Comentários de Revisão' 'Status da Revisão'

   cl_abap_char_utilities=>newline

   into text separated by cl_abap_char_utilities=>horizontal_tab.

"columns

concatenate text v_orgunit                  "Unidade organizacional

     cl_abap_char_utilities=>horizontal_tab

     ls_questions-text                           "Texto pergunta

     cl_abap_char_utilities=>horizontal_tab

     zrating                                     "Resposta

     cl_abap_char_utilities=>horizontal_tab

     ls_questions-comments                       "Comentarios

     cl_abap_char_utilities=>horizontal_tab

     zcomments_rev                               "Comentarios da revisao

     cl_abap_char_utilities=>horizontal_tab

     zcomments_rev_var                           "Status da revisao

                 cl_abap_char_utilities=>newline

                 into text.

   endloop.

   call function 'SDOK_MIMETYPE_GET'

     exporting

       extension = '.XLS'

     importing

       mimetype  = lv_mime.

   call function 'SCMS_STRING_TO_XSTRING'

     exporting

       text     = text

       encoding = '4103'

     importing

       buffer   = xtext.

   concatenate  cl_abap_char_utilities=>byte_order_mark_little  xtext into  xtext in byte mode.

   call method cl_wd_runtime_services=>attach_file_to_response

     exporting

       i_filename      = 'Download.xls'

*     i_filename      = lv_filename

       i_content       = xtext

       i_mime_type     = 'EXCEL'

       i_in_new_window = abap_false

       i_inplace       = abap_false.

Thank you

[vinita_kasliwal · Accepted solution]

Hey Fernando

You need to do a hit and trial around using  cl_abap_char_utilities=>newline  and also when you concatenate the internal table entries check your logic and see when you want the text to move to a new line

I could not really understand from your screenshot on whats messed up as some columns do not show up and the order is also changed..

The only difference I see in my code is when I try to populate the internal table data

I use this at the end and I am not using    cl_abap_char_utilities=>horizontal_tab after every column entry like in your code.

concatenate text v_orgunit               

        ls_questions-text                           "Texto pergunta

cl_abap_char_utilities=>newline

INTO text

SEPARATED BY cl_abap_char_utilities=>horizontal_tab.

Regards

Vinita