Download internal table to excel with header

[Former Member]

Hi All,

I have a requirement to download internal table contents to excel with field headings. I tried searching in forums before posting but didn't got much help.

I have used GUI_DOWNLOAD, WS_DOWNLOAD and EXCEL_OLE_STANDARD_DAT.

But unable to download the header in excel..along with data...

EXCEL_OLE_STANDARD_DAT is getting field header in excel but not downloading automatically , need to save manually which is not the requirement.

The data is huge with around 151 columns....I got 2 internal tables.

One for the data and the other with field names.

Many Thanks,

Ravi K

[Former Member]

Hi,

Pls find the link below which contains the code to download the internal table into excel with headings.

Trust this helps.

Warm Regards

R Adarsh

[Former Member]

Hi Adarsh,

The link re-directs to the same Forums page..

Thanks,

Ravi K

[GauthamV]

hi,

check this sample code.

DATA : BEGIN OF it_join_fields OCCURS 0,

field_name(20),

END OF it_join_fields.

CLEAR it_join_fields.

it_join_fields-field_name = 'pernr'.

APPEND it_join_fields.

CLEAR it_join_fields.

it_join_fields-field_name = 'empname'.

APPEND it_join_fields.

CLEAR it_join_fields.

it_join_fields-field_name = 'new_joinee'.

APPEND it_join_fields.

CLEAR it_join_fields.

it_join_fields-field_name = 'begda'.

APPEND it_join_fields.

CLEAR it_join_fields.

it_join_fields-field_name = 'fath_name'.

APPEND it_join_fields.

CLEAR it_join_fields.

CALL FUNCTION 'GUI_DOWNLOAD'

EXPORTING

• BIN_FILESIZE =

filename = file_name

filetype = 'ASC'

append = 'X'

write_field_separator = 'X'

• HEADER = '00'

• TRUNC_TRAILING_BLANKS = ' '

• WRITE_LF = 'X'

• 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_join_det_new

fieldnames = it_join_fields

• 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

• 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.

it_join_det_new is ur internal table with data.

Edited by: gautham chakraverthi on Jun 25, 2008 5:24 AM

[Former Member]

Hi,

I don't find fieldname table parameter in GUI_DOWNLOAD function module.. as I am using 4.6C version.

Anyway out to download column headings in 4.6C.

Thanks,

Ravi K

[former_member181995]

Ravi,

use WS_EXCEL.

Amit.

[Former Member]

Hi,

Through GUI_DOWNLOAD you can download the fieldnames .

you have to pass the paramereter fieldname of that .

[Former Member]

hiiii

use following code

that will help you.

TYPES:
  BEGIN OF type_final,
    string(50) TYPE c,                 " String Value for Title
  END OF type_final.

data:

 wa_final     TYPE type_final.     " Work Area to hold Title Data

*"--------------------------------------------------------------------*
* Internal table to hold Title Data                                   *
*"--------------------------------------------------------------------*
DATA:
  i_final    TYPE STANDARD TABLE OF type_final.


FORM f400_dwnloadplantdata USING p_filepath TYPE any

                             p_table TYPE STANDARD TABLE.
.


  DATA:
      lw_file2 TYPE string .           " File Path
  lw_file2 = p_filepath.


*&--------------------------------------------------------------------*
*&      Form  header
*---------------------------------------------------------------------*
*  This Subroutine gets data for displaying title                     *
*---------------------------------------------------------------------*
*  There are no interface parameters to be passed to this subroutine. *
*---------------------------------------------------------------------*
FORM header .

  wa_final-string = text-021.
  APPEND wa_final TO i_final.
  wa_final-string = text-022.
  APPEND wa_final TO i_final.
  wa_final-string = text-023.
  APPEND wa_final TO i_final.
  wa_final-string = text-024.
  APPEND wa_final TO i_final.
  wa_final-string = text-025.
  APPEND wa_final TO i_final.
  wa_final-string = text-026.
  APPEND wa_final TO i_final.
  wa_final-string = 'Sr.No'.
  APPEND wa_final TO i_final.
ENDFORM.                               " header

  CALL FUNCTION 'GUI_DOWNLOAD'
     EXPORTING
*   BIN_FILESIZE                    = BIN_FILESIZE
       filename                        = lw_file2
       filetype                        = 'DBF'
*   APPEND                          = ' '
    write_field_separator           = con_tab
*   HEADER                          = '00'
*   TRUNC_TRAILING_BLANKS           = 'X'
*   WRITE_LF                        = 'X'
   COL_SELECT                      = 'X'
   COL_SELECT_MASK                 = w_col_sel
*   DAT_MODE                        = ' '
*   CONFIRM_OVERWRITE               = ' '
*   NO_AUTH_CHECK                   = ' '
*   CODEPAGE                        = ' '
*   IGNORE_CERR                     = ABAP_TRUE
*   REPLACEMENT                     = '#'
*   WRITE_BOM                       = ' '
*   TRUNC_TRAILING_BLANKS_EOL       = 'X'
*   WK1_N_FORMAT                    = '0'
*   WK1_N_SIZE                      = ' '
*   WK1_T_FORMAT                    = ' '
*   WK1_T_SIZE                      = ' '
* IMPORTING
*   FILELENGTH                      = FILELENGTH
     TABLES
       data_tab                        = i_output3
       fieldnames                      = i_final
     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
             .
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
            WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.                               " IF sy-subrc EQ 0

ENDFORM.                               " f400_dwnloadplantdata

see i_final is a header table...where i have given header in text elements.& have passes in FM gui...

regards

twinkal

[Former Member]

Hi,

APPEND the first line of the itab with the headings and use the FM DOWNLOAD to get it into a excel file.

regards,

teja.

[Former Member]

Hi Ravi,

you can do this ..

take two internal tables say itab_head and itab_data.

Now send all your headings into the itab_head and data to itab_data.

now call the FM GUI_DOWNLOAD two times 1 for the headings and 2nd for the data. be sure that u have passed the append = X and same file name for both.

it might not be the effective solution but may help u....

[Former Member]

Hi,

Try using the FM " SAP_CONVERT_TO_XLS_FORMAT "

This FM directly downloads the Internal Table data into the Excel File.

Sample Report

REPORT Z_R_TEST_FILE.

tables: vbak.

parameters: p_vkorg like vbak-vkorg,

p_file like rlgrap-filename default 'My Documents\TEST_SALE.xls'.

data: begin of itab occurs 0,

vbeln like vbak-vbeln,

vkorg like vbak-vkorg,

end of itab.

start-of-selection.

select vbeln vkorg from vbak into table itab

where vkorg = p_vkorg.

CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'

EXPORTING

• I_FIELD_SEPERATOR =

• I_LINE_HEADER =

I_FILENAME = p_file

• I_APPL_KEEP = ' '

TABLES

I_TAB_SAP_DATA = itab

• CHANGING

• I_TAB_CONVERTED_DATA =

EXCEPTIONS

CONVERSION_FAILED = 1

OTHERS = 2

.

IF SY-SUBRC <> 0.

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

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

write:/ 'Error while downloading'.

else.

write:/ 'Successful '.

ENDIF.

For headings to appear in the Excel file, APPEND the fieldname to the first line of the Internal Table and pass the table to the FM.

Hope this helps u.

Thanks and Regards,

Ruthra

[kesavadas_thekkillath]

check this code in a sample program

data:begin of it occurs 0,

grp type c,

val type i,

end of it.

data:begin of heading occurs 0,

TEXT(10) type c,

end of heading.

it-grp = 'A'.

it-val = 100.

append it.

it-grp = 'B'.

it-val = 200.

append it.

heading-text = 'GROUP'.

append heading.

heading-text = 'VALUE'.

append heading.

call function 'GUI_DOWNLOAD'

exporting

filename = 'C:\text.xls'

FILETYPE = 'DAT'

WRITE_FIELD_SEPARATOR = '#'

SHOW_TRANSFER_STATUS = 'X'

tables

data_tab = it[]

FIELDNAMES = heading[]

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.

[Former Member]

Hi Ravi ,

              i saw your post , i am facing the same problem which you faced long back, i am using 4.6c and need to download in excel both header and data, it not getting getting populated . how did you resolve this issue.

[Former Member]

Hi Ravi,

Load the internal table and pass it to the Function module GUI_DOWNLOAD

TYPES:begin of TY_ITEMS,

       value1 type c,

       value2 type i,

      end of TY_ITEMS.

TYPES:begin of TY_HEADER,

       TEXT(10) type c,

      end of TY_HEADER.

DATA:IT_ITEMS type standard table of ty_items,

           it_header type standard table of ty_header.

DATA: wa_items type ty_items,

       wa_header type ty_header.

      wa_items-value1 = 'X'.

      wa_items-value2 = 100.

      append wa_items to it_items.

      wa_items-value1 = 'Z'.

      wa_items-value2 = 200.

      append wa_items to it_items.

      wa_header-text = 'NAME'.

      append wa_header to it_header.

      wa_header-text = 'VALUE'.

      append wa_header to it_header.

      call function 'GUI_DOWNLOAD'

        exporting

           filename                        = 'C:\Users\acer\Desktop\backup\text.xls'

          FILETYPE                        = 'DAT'

          WRITE_FIELD_SEPARATOR           = '#'

          SHOW_TRANSFER_STATUS            = 'X'

        tables

          data_tab                        = it_items

         FIELDNAMES                      = it_header[]

       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.

[Former Member]

Hi Ravi,

You need to have 2 different internal tables for achieving the needful. One internal table would be having your data and another would store your table field names i.e., declare a structure of length 100 characters,

TYPES : BEGIN OF GTY_FIELDNAMES,
                TITLE(100),
              END   OF GTY_FIELDNAMES.

DATA: GIT_FIELDNAMES TYPE STANDARD TABLE OF GTY_FIELDNAMES,

            GWA_FIELDNAMES TYPE GTY_FIELDNAMES.

DATA : GD_FILENAME TYPE STRING,
            GD_PATH     TYPE STRING,
            GD_FULLPATH TYPE STRING,
            GD_RESULT   TYPE I.

Now have a subroutine where by you append your headings into the internal table i.e.,GIT_FIELDNAMES

   CLEAR GWA_FIELDNAMES.
   GWA_FIELDNAMES-TITLE = 'Material Number'.
   APPEND GWA_FIELDNAMES TO GIT_FIELDNAMES.

   CLEAR GWA_FIELDNAMES.
   GWA_FIELDNAMES-TITLE = 'Material Description'.
   APPEND GWA_FIELDNAMES TO GIT_FIELDNAMES.

Once you are done with it you can call up the save dialog

* Display save dialog window
   CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_SAVE_DIALOG
     EXPORTING
       WINDOW_TITLE           = 'Save File As...'
       DEFAULT_EXTENSION  = 'XLS'
       DEFAULT_FILE_NAME  = 'SalesPlan'
       INITIAL_DIRECTORY      = 'C:\'
     CHANGING
       FILENAME                      = GD_FILENAME
       PATH                               = GD_PATH
       FULLPATH                      = GD_FULLPATH
       USER_ACTION               = GD_RESULT.

* Check user did not cancel request
   CHECK GD_RESULT EQ '0'.

   CALL FUNCTION 'GUI_DOWNLOAD'
     EXPORTING
       FILENAME              = GD_FULLPATH
       FILETYPE                = 'ASC'
*     APPEND                = 'X'
       WRITE_FIELD_SEPARATOR = 'X'
*     CONFIRM_OVERWRITE     = 'X'
     TABLES
       DATA_TAB                 = GIT_FINAL                  " Internal table having data
       FIELDNAMES            = GIT_FIELDNAMES     " Internal table having headings
     EXCEPTIONS
       FILE_OPEN_ERROR       = 1                         "#EC ARGCHECKED
       FILE_WRITE_ERROR      = 2
       OTHERS                           = 3.

Hope this gets sorted your problem.

Thanks & Regards,

Varun Kumar Sahu