writing internal table contents to excel

[Former Member]

hi,

i need to write data in my internal table to an excel spread sheet which should be similar to my internal table structure.

can someone help me out.

thanks,

ravi.

[RichHeilman]

You can use the function module GUI_DOWNLOAD. Also, you can use the method GUI_DOWNLOAD of class CL_GUI_FRONTENDSERVICES. Make sure the file name has an .xls extension and also that the "HAS_SEPARATOR" parameter is = "X".

You can also do this using OLE, but if you are just needing to do a straight extract from itab to excel, then GUI_DOWNLOAD would be your best bet.

Regards,

Rich Heilman

[Former Member]

Hi Ravi,

My users like 'MS_EXCEL_OLE_STANDARD_DAT'.

This function module opens immediately your internal table

in Excel. It also supports column headers which is pretty

cool.

For your column headers declare an internal table like:

DATA: BEGIN OF xout_fieldnames OCCURS 0,

fieldname(20) type c,

END OF xout_fieldnames.

and fill it as necessary, eg.

xout_fieldnames-fieldname = 'COMPANY_CODE'.

APPEND xout_fieldnames.

xout_fieldnames-fieldname = 'CUSTOMER'.

APPEND xout_fieldnames.

xout_fieldnames-fieldname = 'CUSTOMER_NAME'.

APPEND xout_fieldnames.

xout_fieldnames-fieldname = 'DT'.

APPEND xout_fieldnames.

etc.

Next call the SAP function module and pass your internal table and coulm headers to it:

CALL FUNCTION 'MS_EXCEL_OLE_STANDARD_DAT'

EXPORTING

FILE_NAME = pwsfile

• CREATE_PIVOT = 0

DATA_SHEET_NAME = 'Your internal table'

• PIVOT_SHEET_NAME = ' '

• PASSWORD = ' '

• PASSWORD_OPTION = 0

TABLES

• PIVOT_FIELD_TAB =

DATA_TAB = xout

FIELDNAMES = xout_fieldnames

EXCEPTIONS

FILE_NOT_EXIST = 1

FILENAME_EXPECTED = 2

COMMUNICATION_ERROR = 3

OLE_OBJECT_METHOD_ERROR = 4

OLE_OBJECT_PROPERTY_ERROR = 5

INVALID_FILENAME = 6

INVALID_PIVOT_FIELDS = 7

DOWNLOAD_PROBLEM = 8

OTHERS = 9

.

IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

Cheers,

---Hugo

[Former Member]

Hi,

Try this one

FORM f9008_f4_hlp_for_pc_file.
  DATA: li_filetable TYPE STANDARD TABLE OF file_table,
        lv_return TYPE i,
        lw_filetable TYPE file_table.

  CALL METHOD cl_gui_frontend_services=>file_open_dialog
     EXPORTING
       window_title            = 'Select file for download'
       default_extension       = '.txt'
       initial_directory       =  'C:'
     CHANGING
       file_table              = li_filetable
       rc                      = lv_return
     EXCEPTIONS
       file_open_dialog_failed = 1
       cntl_error              = 2
       error_no_gui            = 3
       OTHERS                  = 4
           .
  IF sy-subrc <> 0.
    MESSAGE e006 WITH text-077.
  ELSE.
    READ TABLE li_filetable INTO lw_filetable INDEX 1.
    v_fnam = lw_filetable-filename.
  ENDIF.

 FORM f9007_download_file TABLES p_output.
  DATA:  lv_size   TYPE i.

  CALL FUNCTION 'WS_DOWNLOAD'
       EXPORTING
            filename                = v_fnam
            filetype                = 'DAT'
       IMPORTING
            filelength              = lv_size
       TABLES
            data_tab                = p_output
       EXCEPTIONS
            file_open_error         = 1
            file_write_error        = 2
            invalid_filesize        = 3
            invalid_type            = 4
            no_batch                = 5
            unknown_error           = 6
            invalid_table_width     = 7
            gui_refuse_filetransfer = 8
            customer_error          = 9
            OTHERS                  = 10.
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
            WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.

ENDFORM.                    " f9007_download_file

<b>While downloading file, u wont get the header part, u have to append the header part</b>. Then u pass the final output table with header to the function module.

So that it will read the first line that will be header and then remaning data.

<i>Sample code:</i>U can create an internal table.

TYPES: begin of ty_itab , 
            name(4) TYPE c,  
            sex(3)  Type c,
        end of ty_itab.
DATA: i_itab type standard table of ty_itab,               
      w_itab type ty_itab.
Then do as below. 
w_itab-name = 'NAME'. 
w_itab-sex  = 'SEX'. 
APPEND w_itab to i_output_final. 
i_output[] = i_output_final.
 APPEND i_output_final.

Now pass this final table to the functionmodule, so it is now with header.

Try this out.

Get back to me if u want any clarification.

[Former Member]

Hi,

Please see the link for sample code:

http://www.sapdevelopment.co.uk/file/file_updownpop.htm

Regards,

Anjali

[Former Member]

Hi,

Find the below code to download the internal table content in to EXCEL.

REPORT ztablexls.

TABLES: USR03,DD02L.
DATA: ZX030L LIKE X030L.
DATA BEGIN OF ZDFIES OCCURS 0.
     INCLUDE STRUCTURE DFIES.
DATA END OF ZDFIES.
DATA: BEGIN OF FLDITAB OCCURS 0,
      FLDNAME(11) TYPE C,
      END OF FLDITAB.
DATA ITABUSR03 LIKE USR03 OCCURS 0 WITH HEADER LINE.
DATA TNAME LIKE DD02L-TABNAME.
SELECT * FROM USR03 INTO TABLE ITABUSR03.
TNAME = 'USR03'.
PERFORM GETFIELEDS.
PERFORM SHOW123.
********************************************
FORM GETFIELEDS.
     CALL FUNCTION 'GET_FIELDTAB'
      EXPORTING
          LANGU              = SY-LANGU
          ONLY               = SPACE
          TABNAME            = TNAME
          WITHTEXT           = 'X'
      IMPORTING
          HEADER             = ZX030L
      TABLES
          FIELDTAB           = ZDFIES
      EXCEPTIONS
          INTERNAL_ERROR      = 01
          NO_TEXTS_FOUND      = 02
          TABLE_HAS_NO_FIELDS = 03
          TABLE_NOT_ACTIV     = 04.
     CASE SY-SUBRC.
        WHEN 0.
          LOOP AT ZDFIES.
               FLDITAB-FLDNAME = ZDFIES-FIELDNAME.
               APPEND FLDITAB.
          ENDLOOP.
        WHEN OTHERS.
             MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
              with  SY-SUBRC.
      ENDCASE.
ENDFORM.
***********************************
FORM SHOW123.
CALL FUNCTION 'EXCEL_OLE_STANDARD_DAT'
     EXPORTING
          FILE_NAME                 = 'C:USR03.XLS'
          DATA_SHEET_NAME           = 'USER LIST'
    TABLES
         DATA_TAB                  =  ITABUSR03
         FIELDNAMES                =  FLDITAB
    EXCEPTIONS
         FILE_NOT_EXIST            = 1
         FILENAME_EXPECTED         = 2
         COMMUNICATION_ERROR       = 3
         OLE_OBJECT_METHOD_ERROR   = 4
         OLE_OBJECT_PROPERTY_ERROR = 5
         INVALID_FILENAME          = 6
         INVALID_PIVOT_FIELDS      = 7
         DOWNLOAD_PROBLEM          = 8
         OTHERS                    = 9.
IF SY-SUBRC <> 0.
 MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
ENDFORM.

Regs,

Venkat

[Former Member]

Hi Ravi,

I don't see you awarding points to all these folks who have answered your question correctly in more than 1 way. I thought of placing a sample code which is different from all the above ones but then felt it not worth if you happen to just ignore it like you did for others.

At least, you can update whether thier contributions were useful or not rt?

Cheers,

Sam