topic Re: Problem to export from internal table to Excel in Application Development and Automation Discussions

Problem to export from internal table to Excel

Former Member — Mon, 09 Mar 2009 05:36:48 GMT

Hi ,

I am facing the problem to download the data from Internal table to excel.At presently i am using the OLE to down load the data,because to format the excel i have gone to OLE(I mean to set column width and colors),But it is taking lot of time to extract the data from ITAB to EXCEL.Can any body suggest me the function module to format the excel.So that i can use that function module to format and as well as to down load the data.

Thanks,

Mohan Rao

Re: Problem to export from internal table to Excel

Former Member — Mon, 09 Mar 2009 05:59:46 GMT

Use FMs : GUI_DOWNLOAD.

In export parameters input filetype = 'DAT'.

Re: Problem to export from internal table to Excel

Former Member — Mon, 09 Mar 2009 06:05:38 GMT

Hi,

Check this link..http://www.thespot4sap.com/Articles/Download_to_excel.asp

Re: Problem to export from internal table to Excel

Former Member — Mon, 09 Mar 2009 06:41:04 GMT

Hi ,

Thanks for your reply.But i can not format the excel using this FM.Please suggest me which will be usefull.

Re: Problem to export from internal table to Excel

Former Member — Mon, 09 Mar 2009 07:11:37 GMT

you can try this link it may be useful

Re: Problem to export from internal table to Excel

Former Member — Mon, 09 Mar 2009 07:48:58 GMT

Hi,

Refer the given below code........



  CALL FUNCTION 'GUI_DOWNLOAD'
    EXPORTING
*   BIN_FILESIZE                    =
      filename                        = p_err     " fiile path name using parameter
     filetype                        = 'ASC'       "it should be in ASC
*   APPEND                          = ' '
*   WRITE_FIELD_SEPARATOR           = ' '
*   HEADER                          = '00'
     trunc_trailing_blanks           = ' '
     write_lf                        = 'X'         "THIS IS IMPORTANT FOR EXL FORMAT
*   COL_SELECT                      = ' '
*   COL_SELECT_MASK                 = ' '
*   DAT_MODE                        = ' '
*   CONFIRM_OVERWRITE               = ' '
*   NO_AUTH_CHECK                   = ' '
*   CODEPAGE                        = ' '
*   IGNORE_CERR                     = ABAP_TRUE
*   REPLACEMENT                     = '#'
*   WRITE_BOM                       = ' '
     trunc_trailing_blanks_eol       = 'X'
*   WK1_N_FORMAT                    = ' '
*   WK1_N_SIZE                      = ' '
*   WK1_T_FORMAT                    = ' '
*   WK1_T_SIZE                      = ' '
*   WRITE_LF_AFTER_LAST_LINE        = ABAP_TRUE
*   SHOW_TRANSFER_STATUS            = ABAP_TRUE
* IMPORTING
*   FILELENGTH                      =
    TABLES
      data_tab                        = it_temp   "INTERNAL TABLE NAME
*   FIELDNAMES                      =
* EXCEPTIONS
*   FILE_WRITE_ERROR                = 1
*   NO_BATCH                        = 2
*   GUI_REFUSE_FILETRANSFER         = 3
*   INVALID_TYPE                    = 4
*   NO_AUTHORITY                    = 5
*   UNKNOWN_ERROR                   = 6
*   HEADER_NOT_ALLOWED              = 7
*   SEPARATOR_NOT_ALLOWED           = 8
*   FILESIZE_NOT_ALLOWED            = 9
*   HEADER_TOO_LONG                 = 10
*   DP_ERROR_CREATE                 = 11
*   DP_ERROR_SEND                   = 12
*   DP_ERROR_WRITE                  = 13
*   UNKNOWN_DP_ERROR                = 14
*   ACCESS_DENIED                   = 15
*   DP_OUT_OF_MEMORY                = 16
*   DISK_FULL                       = 17
*   DP_TIMEOUT                      = 18
*   FILE_NOT_FOUND                  = 19
*   DATAPROVIDER_EXCEPTION          = 20
*   CONTROL_FLUSH_ERROR             = 21
*   OTHERS                          = 22
            .
  IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
  ENDIF.

Thanks

Arun

Re: Problem to export from internal table to Excel

Former Member — Tue, 10 Mar 2009 05:58:51 GMT

Hi,

I have tried with this option but it is not working,basically i need to set column width ,that i am not getting through this functionality.Can you please suggest me if there is any other function module,

Thanks,

Mohan Rao

Re: Problem to export from internal table to Excel

Former Member — Tue, 10 Mar 2009 11:06:32 GMT

Hello,

What i can suggest is to build the excel file from your internal table. try this

FORM f_build_xls_data_table.

DATA: lv_sy_datum TYPE string,

lv_datuv(10) TYPE c,

lv_andat(10) TYPE c,

lv_aedat(10) TYPE c,

lv_rldat(10) TYPE c,

lv_mime_type(34) TYPE c VALUE 'APPLICATION/MSEXCEL; charset=UTF-8',

lv_row_text TYPE string,

lv_row_hex TYPE xstring,

lv_date(10) TYPE c,

lv_emailname(50) TYPE c.

CONSTANTS: lc_name1(25) TYPE c VALUE 'Change number report',

lc_name2(5) TYPE c VALUE '.XLS',

lc_tag TYPE c VALUE '_'.

CLEAR: lv_sy_datum, lv_datuv, lv_andat, lv_andat, lv_aedat, lv_rldat, lv_date, lv_emailname.

CONCATENATE c_cret v_attachment INTO v_attachment.

APPEND v_attachment TO i_attachment2.

CLEAR v_attachment.

CONCATENATE space space space space space

INTO v_attachment SEPARATED BY c_tab.

CONCATENATE c_cret v_attachment INTO v_attachment.

APPEND v_attachment TO i_attachment2.

CLEAR v_attachment.

Creating the document attachment ~ header titles

CONCATENATE text-031

text-032

text-033

text-034

text-035

text-051

text-036

text-037

text-038

text-039

text-040

text-041

text-042

text-044

space

INTO v_attachment SEPARATED BY c_tab.

CONCATENATE c_cret v_attachment INTO v_attachment.

APPEND v_attachment TO i_attachment2.

CLEAR: v_attachment.

*converting the data

LOOP AT i_aenr INTO wa_aenr.

CONCATENATE wa_aenr-aennr

wa_aenr-aetxt

wa_aenr-aegru

lv_datuv

wa_aenr-aenst

wa_aenr-aefun

wa_aenr-rlkey

wa_aenr-aenbe

wa_aenr-annam

lv_andat

wa_aenr-aenam

lv_aedat

lv_rldat

wa_aenr-lvorm

INTO v_attachment SEPARATED BY c_tab RESPECTING BLANKS.

*Special character added so that the aennr are displayed fully in the excel sheet

Example instead of displaying 5E12 the whole number is being displayed '500000000008

CONCATENATE text-060 v_attachment INTO v_attachment.

CONCATENATE c_cret v_attachment INTO v_attachment.

APPEND v_attachment TO i_attachment2.

CLEAR v_attachment.

ENDLOOP.

DESCRIBE TABLE i_attachment2 LINES v_tab_lines.

LOOP AT i_attachment2.

MOVE i_attachment2 TO lv_row_text.

CALL FUNCTION 'SCMS_STRING_TO_XSTRING'

EXPORTING

text = lv_row_text

mimetype = lv_mime_type

IMPORTING

buffer = lv_row_hex

EXCEPTIONS

failed = 1

OTHERS = 2.

IF sy-subrc EQ 0.

MOVE lv_row_hex TO i_attachment3-line.

APPEND i_attachment3 TO i_attachment3.

ENDIF.

CLEAR: lv_row_text,

i_attachment3,

lv_row_hex,

i_attachment2.

ENDLOOP.

your internal table should be in your i_attachment2 normally and all you need to do is downlaod that to your pc as xls file.

gui_download

hope this helps

Cheers

Re: Problem to export from internal table to Excel

Former Member — Tue, 10 Mar 2009 12:33:39 GMT

Hi:

Please generate in the ALV Grid and let it upto the user and the SAP to care automatically.

Regards

Shashi