Query as an Excel Attachment

[Former Member]

Hello Friends,

Currently there exists one customized report, where the user can give the query name and the variant and the Query output is sent as an HTML attachment.

The client wishes to have the attachment in Excel format now. The below code I have already added. Where first I get the list from the memory and then I convert this Hex data into ASCII. The data is converted, but I get the output in below format. Can't we get the Query output in tabular format?

Looking forward to your answers!

Thanks,

Shaheen

  CALL FUNCTION 'LIST_FROM_MEMORY'                          "#EC *
       TABLES
            listobject = lt_listobject
       EXCEPTIONS
            not_found  = 1
            OTHERS     = 2.
  IF sy-subrc <> 0.
    WRITE  'Error in list_from_memory.'(030).
*   -- Switch for Performing PERFORM SEND.
    g_nooutput = 'X'.                 "DE13143-05022004
  ENDIF.

  CALL FUNCTION 'LIST_TO_ASCI'
    TABLES
      listasci           = itab2
      listobject         = lt_listobject
    EXCEPTIONS
      empty_list         = 1
      list_index_invalid = 2
      OTHERS             = 3.

Output I am getting now:

-

---

List of Vendors

-

---

|Vendor |Delet.|Cty|Name 1 |City

-

---

|102123950 | |FR |FINORGA |CHASSE SUR RHONE

|102123950 | |FR |FINORGA |CHASSE SUR RHONE

|302304952 | |DK |NOVOZYMES A/S |BAGSVAERD

|302304952 | |DK |NOVOZYMES A/S |BAGSVAERD

|1003046035| |DE |AGRO HANDELSGESELLSCHAFT MBH |BARSINGHAUSEN OT G

|1003046035| |DE |AGRO HANDELSGESELLSCHAFT MBH |BARSINGHAUSEN OT G

|1003046035| |DE |AGRO HANDELSGESELLSCHAFT MBH |BARSINGHAUSEN OT G

|902061309 | |PT |HOVIONE FARMACIENCIA SA |LOURES

[Former Member]

Hello Friends,

Currently there exists one customized report, where the user can give the query name and the variant and the Query output is sent as an HTML attachment.

The client wishes to have the attachment in Excel format now. The below code I have already added. Where first I get the list from the memory and then I convert this Hex data into ASCII. The data is converted, but I get the output in below format. Can't we get the Query output in tabular format?

Looking forward to your answers!

Thanks,

Shaheen

  CALL FUNCTION 'LIST_FROM_MEMORY'                          "#EC *
       TABLES
            listobject = lt_listobject
       EXCEPTIONS
            not_found  = 1
            OTHERS     = 2.
  IF sy-subrc <> 0.
    WRITE  'Error in list_from_memory.'(030).
*   -- Switch for Performing PERFORM SEND.
    g_nooutput = 'X'.                 "DE13143-05022004
  ENDIF.

  CALL FUNCTION 'LIST_TO_ASCI'
    TABLES
      listasci           = itab2
      listobject         = lt_listobject
    EXCEPTIONS
      empty_list         = 1
      list_index_invalid = 2
      OTHERS             = 3.

Output I am getting now:

-

---

List of Vendors

-

---

|Vendor |Delet.|Cty|Name 1 |City

-

---

|102123950 | |FR |FINORGA |CHASSE SUR RHONE

|102123950 | |FR |FINORGA |CHASSE SUR RHONE

|302304952 | |DK |NOVOZYMES A/S |BAGSVAERD

|302304952 | |DK |NOVOZYMES A/S |BAGSVAERD

|1003046035| |DE |AGRO HANDELSGESELLSCHAFT MBH |BARSINGHAUSEN OT G

|1003046035| |DE |AGRO HANDELSGESELLSCHAFT MBH |BARSINGHAUSEN OT G

|1003046035| |DE |AGRO HANDELSGESELLSCHAFT MBH |BARSINGHAUSEN OT G

|902061309 | |PT |HOVIONE FARMACIENCIA SA |LOURES

[Sandra_Rossi]

Hi,

As your report format looks simple, if CSV is sufficient, then you could convert the | to commas, and delete useless horizontal lines?

Sandra

[Amarpreet]

or u can split the data at | and put it into corresponding fields of an internal table and use a function module to convert that internal table contents into an excel sheet

eg:

loop at it into wa

split wa at '|' into wa_data-fild1 wa_data-fld2 ......wa_data-fld n .

append wa_data to it_data .

end loop .

and then use a function module such as

MS_EXCEL_OLE_STANDARD_DAT , or some other to put the data into an xls file

good luck

Edited by: alcoholix on Aug 29, 2011 8:04 PM

[Former Member]

Hello Sandra & Alcoholix,

Thanks a lot for your valuable responses. As mentioned in my first post, this Report is a general one and it can be used for any query. The query name and the variant is input to this report, the query is first then SUBMITted and then the list is recalled from the memory. So, the no. of columns can vary. And secondly this excel file will be used for Analysis purpose, so CSV format is definitely not that readable. (Note: Header should also be there).

Waiting for your replies again.

Thanks & Regards,

Shaheen

Edited by: Shaheen_ABAP on Aug 30, 2011 10:26 AM

[Sandra_Rossi]

Hi,

I still don't understand why you can't use CSV? When you start CSV, Excel automatically starts and display it as any Excel. The number of columns is not an issue too. Nor the headings.

So, could you give more clues please

Sandra

[Amarpreet]

if u don't have fixed number of fields i don't see how u'll comfortably convert it into an xls file, cause i think as far as i have sen its important to have data in an internal table's fields .

so i think what sandra said is the best way .

try this .

replace | with comas,

then transfer those lines into a file file and name it .CSV and when u open this CSV with ms excel u'll be able to see it just like an excel sheet .

try it,

unless ofcrse u have the data divided into fields of internal table already then u can create an .xls .

Edited by: alcoholix on Aug 30, 2011 10:03 PM

[Former Member]

Hello Sandra and Alcoholix,

The problem is resolved. And I managed to have it in XLS format. I replaced | with horizonal tabs and at the end of each line, I placed a new line seperator.

Then I wrote this entire table in a single string and passed it through the Function modules SCMS_STRING_TO_XSTRING and SCMS_XSTRING_TO_BINARY and finally the output to the function module SO_DOCUMENT_SEND_API1. The lines are not then truncated anymore.

Thanks a lot!

Regards,

Shaheen