topic Re: Transfer data from internal table to excel sheet. in Application Development and Automation Discussions

Transfer data from internal table to excel sheet.

former_member130219 — Wed, 06 May 2009 07:00:06 GMT

My code for transfering the data from internal table to excel sheet is running successfully.

But the excel sheet is not being generated.pls help.

Thanks in advance.

Regards

Abhinandan

Re: Transfer data from internal table to excel sheet.

former_member242255 — Wed, 06 May 2009 07:31:29 GMT

see if u r missing any imputs...

call function 'GUI_DOWNLOAD'

exporting

filename = 'c:\abc.xls'

filetype = 'ASC'

write_field_separator = 'X'

IMPORTING
FILELENGTH =

tables

data_tab = itab

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

Re: Transfer data from internal table to excel sheet.

Former Member — Wed, 06 May 2009 08:39:30 GMT

Hai,

sending u the code to save it as pdf i thing this may help u

CALL FUNCTION 'GET_PRINT_PARAMETERS'

EXPORTING

ARCHIVE_ID = C_CHAR_UNKNOWN
ARCHIVE_INFO = C_CHAR_UNKNOWN
ARCHIVE_MODE = C_CHAR_UNKNOWN
ARCHIVE_TEXT = C_CHAR_UNKNOWN
AR_OBJECT = C_CHAR_UNKNOWN
ARCHIVE_REPORT = C_CHAR_UNKNOWN

AUTHORITY = SPACE

COPIES = '1'

COVER_PAGE = SPACE

DATA_SET = SPACE

DEPARTMENT = SPACE

DESTINATION = SPACE

EXPIRATION = '1'

IMMEDIATELY = SPACE

IN_ARCHIVE_PARAMETERS =
IN_PARAMETERS = SPACE

LAYOUT = SPACE

LINE_COUNT = C_INT_UNKNOWN
LINE_SIZE = C_INT_UNKNOWN
LIST_NAME = C_CHAR_UNKNOWN
LIST_TEXT = C_CHAR_UNKNOWN

MODE = SPACE

NEW_LIST_ID = 'X'
PROTECT_LIST = C_CHAR_UNKNOWN

NO_DIALOG = 'X'

RECEIVER = C_CHAR_UNKNOWN
RELEASE = C_CHAR_UNKNOWN
REPORT = C_CHAR_UNKNOWN
SAP_COVER_PAGE = C_CHAR_UNKNOWN
HOST_COVER_PAGE = C_CHAR_UNKNOWN
PRIORITY = C_NUM1_UNKNOWN
SAP_OBJECT = C_CHAR_UNKNOWN
TYPE = C_CHAR_UNKNOWN

USER = SY-UNAME

USE_OLD_LAYOUT = ' '
UC_DISPLAY_MODE = C_CHAR_UNKNOWN
DRAFT = C_CHAR_UNKNOWN
ABAP_LIST = ' '
USE_ARCHIVENAME_DEF = ' '
DEFAULT_SPOOL_SIZE = C_CHAR_UNKNOWN
WITH_STRUCTURE = C_CHAR_UNKNOWN
SUPPRESS_SHADING = C_CHAR_UNKNOWN
PO_FAX_STORE = ' '
NO_FRAMES = C_CHAR_UNKNOWN

IMPORTING

OUT_ARCHIVE_PARAMETERS =

OUT_PARAMETERS = MSTR_PRINT_PARMS

VALID = MC_VALID

VALID_FOR_SPOOL_CREATION =

EXCEPTIONS

ARCHIVE_INFO_NOT_FOUND = 1

INVALID_PRINT_PARAMS = 2

INVALID_ARCHIVE_PARAMS = 3

OTHERS = 4

IF MSTR_PRINT_PARMS-PDEST = SPACE.

MSTR_PRINT_PARMS-PDEST = 'LOCL'. "'ZPDF'.

MSTR_PRINT_PARMS-LINSZ = 241.

ENDIF.

*-- Explicitly set output format so that

*-- the PDF conversion comes out OK

IF P_PAART IS INITIAL.

P_PAART = 'X_65_255'.

ENDIF.

MSTR_PRINT_PARMS-PAART = P_PAART.

MOVE SY-REPID TO W_REPID.

CONCATENATE 'C:\' W_REPID '.pdf' INTO MC_FILENAME.

SUBMIT (W_REPID) TO SAP-SPOOL

SUBMIT XXXXProg-nameXXXX TO SAP-SPOOL

WITHOUT SPOOL DYNPRO

SPOOL PARAMETERS MSTR_PRINT_PARMS

USING SELECTION-SCREEN 1000

WITH xxx EQ xxx "screen input fields

AND RETURN.

PERFORM GET_SPOOL_NUMBER

USING W_REPID SY-UNAME

CHANGING MI_RQIDENT.

CALL FUNCTION 'CONVERT_ABAPSPOOLJOB_2_PDF'

EXPORTING

SRC_SPOOLID = MI_RQIDENT

NO_DIALOG = SPACE

DST_DEVICE = MSTR_PRINT_PARMS-PDEST

PDF_DESTINATION =

IMPORTING

PDF_BYTECOUNT = MI_BYTECOUNT

PDF_SPOOLID =
LIST_PAGECOUNT =
BTC_JOBNAME =
BTC_JOBCOUNT =

TABLES

PDF = MTAB_PDF

EXCEPTIONS

ERR_NO_ABAP_SPOOLJOB = 1

ERR_NO_SPOOLJOB = 2

ERR_NO_PERMISSION = 3

ERR_CONV_NOT_POSSIBLE = 4

ERR_BAD_DESTDEVICE = 5

USER_CANCELLED = 6

ERR_SPOOLERROR = 7

ERR_TEMSEERROR = 8

ERR_BTCJOB_OPEN_FAILED = 9

ERR_BTCJOB_SUBMIT_FAILED = 10

ERR_BTCJOB_CLOSE_FAILED = 11

OTHERS = 12

*IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

*ENDIF.

CALL FUNCTION 'DOWNLOAD'

EXPORTING

BIN_FILESIZE = MI_BYTECOUNT

CODEPAGE = MC_FILENAME

FILENAME = ' '

FILETYPE = 'BIN'

ITEM = ' '
MODE = ' '
WK1_N_FORMAT = ' '
WK1_N_SIZE = ' '
WK1_T_FORMAT = ' '
WK1_T_SIZE = ' '
FILEMASK_MASK = ' '
FILEMASK_TEXT = ' '
FILETYPE_NO_CHANGE = ' '
FILEMASK_ALL = ' '
FILETYPE_NO_SHOW = ' '
SILENT = 'S'
COL_SELECT = ' '
COL_SELECTMASK = ' '
NO_AUTH_CHECK = ' '

IMPORTING

ACT_FILENAME = MC_FILENAME

ACT_FILETYPE =
FILESIZE =
CANCEL =

TABLES

DATA_TAB = MTAB_PDF

FIELDNAMES =
EXCEPTIONS
INVALID_FILESIZE = 1
INVALID_TABLE_WIDTH = 2
INVALID_TYPE = 3
NO_BATCH = 4
UNKNOWN_ERROR = 5
GUI_REFUSE_FILETRANSFER = 6
OTHERS = 7

*IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

*ENDIF.

Re: Transfer data from internal table to excel sheet.

Former Member — Thu, 07 May 2009 00:34:46 GMT

Hi,

which function module are you using to download it to excel sheet. Execute in debugging mode and check the sy-subrc after executing the function module.

Sruthi

Re: Transfer data from internal table to excel sheet.

Former Member — Thu, 07 May 2009 03:50:58 GMT

Hi,

Refer the link :

http://abaplovers.blogspot.com/2008/05/abap-internal-table-to-excel-sheet.html

Regards,

Sumana

Re: Transfer data from internal table to excel sheet.

Former Member — Thu, 07 May 2009 04:59:13 GMT

Hi,

I think the following link will be useful for you in transfering the data from internal table to excel sheet.

https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/a0bbe145-fe1c-2a10-71a0-d473b23088b6

Thanks & Regards,

Dhanalakshmi

Re: Transfer data from internal table to excel sheet.

Former Member — Thu, 07 May 2009 05:11:51 GMT

Hi,

try this code



DATA: tb_output1 TYPE TABLE OF ty_output1,
      tb_output1_file TYPE TABLE OF ty_output1_file,
      tb_excel_col_header TYPE TABLE OF ty_excel_col_header.

*&---------------------------------------------------------------------*
*       To download in excel format
*&---------------------------------------------------------------------*
FORM download_excel.
  DATA: lv_file_name TYPE rlgrap-filename.
  lv_file_name = pa_filep.

   CALL FUNCTION 'MS_EXCEL_OLE_STANDARD_DAT'
    EXPORTING
      file_name                 = lv_file_name
      data_sheet_name           = 'Sheet1'(046)
    TABLES
      data_tab                  = tb_output1_file
      fieldnames                = tb_excel_col_header
    EXCEPTIONS
      file_not_exist            = 1
      filename_expected         = 2
      communication_error       = 3
      ole_object_method_error   = 4
      ole_object_property_error = 5
      invalid_pivot_fields      = 6
      download_problem          = 7
      OTHERS                    = 8.
  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.                    "download_excel

Regards,

Manish

Re: Transfer data from internal table to excel sheet.

Former Member — Thu, 07 May 2009 05:20:08 GMT

Hi,

Which functional module you are using?

Try this function module...........'XXL_FULL_API'

Often we face situations where we need to download internal table contents onto an Excel sheet. We are familiar with the function module WS_DOWNLOAD. Though this function module downloads the contents onto the Excel sheet, there cannot be any column headings or we cannot differentiate the primary keys just by seeing the Excel sheet.

For this purpose, we can use the function module XXL_FULL_API. The Excel sheet which is generated by this function module contains the column headings and the key columns are highlighted with a different color. Other options that are available with this function module are we can swap two columns or supress a field from displaying on the Excel sheet. The simple code for the usage of this function module is given below.

Program code :

REPORT Excel.

TABLES:

sflight.

header data................................

DATA :

header1 LIKE gxxlt_p-text VALUE 'Suresh',

header2 LIKE gxxlt_p-text VALUE 'Excel sheet'.

Internal table for holding the SFLIGHT data

DATA BEGIN OF t_sflight OCCURS 0.

INCLUDE STRUCTURE sflight.

DATA END OF t_sflight.

Internal table for holding the horizontal key.

DATA BEGIN OF t_hkey OCCURS 0.

INCLUDE STRUCTURE gxxlt_h.

DATA END OF t_hkey .

Internal table for holding the vertical key.

DATA BEGIN OF t_vkey OCCURS 0.

INCLUDE STRUCTURE gxxlt_v.

DATA END OF t_vkey .

Internal table for holding the online text....

DATA BEGIN OF t_online OCCURS 0.

INCLUDE STRUCTURE gxxlt_o.

DATA END OF t_online.

Internal table to hold print text.............

DATA BEGIN OF t_print OCCURS 0.

INCLUDE STRUCTURE gxxlt_p.

DATA END OF t_print.

Internal table to hold SEMA data..............

DATA BEGIN OF t_sema OCCURS 0.

INCLUDE STRUCTURE gxxlt_s.

DATA END OF t_sema.

Retreiving data from sflight.

SELECT * FROM sflight

INTO TABLE t_sflight.

Text which will be displayed online is declared here....

t_online-line_no = '1'.

t_online-info_name = 'Created by'.

t_online-info_value = 'SURESH KUMAR PARVATHANENI'.

APPEND t_online.

Text which will be printed out..........................

t_print-hf = 'H'.

t_print-lcr = 'L'.

t_print-line_no = '1'.

t_print-text = 'This is the header'.

APPEND t_print.

t_print-hf = 'F'.

t_print-lcr = 'C'.

t_print-line_no = '1'.

t_print-text = 'This is the footer'.

APPEND t_print.

Defining the vertical key columns.......

t_vkey-col_no = '1'.

t_vkey-col_name = 'MANDT'.

APPEND t_vkey.

t_vkey-col_no = '2'.

t_vkey-col_name = 'CARRID'.

APPEND t_vkey.

t_vkey-col_no = '3'.

t_vkey-col_name = 'CONNID'.

APPEND t_vkey.

t_vkey-col_no = '4'.

t_vkey-col_name = 'FLDATE'.

APPEND t_vkey.

Header text for the data columns................

t_hkey-row_no = '1'.

t_hkey-col_no = 1.

t_hkey-col_name = 'PRICE'.

APPEND t_hkey.

t_hkey-col_no = 2.

t_hkey-col_name = 'CURRENCY'.

APPEND t_hkey.

t_hkey-col_no = 3.

t_hkey-col_name = 'PLANETYPE'.

APPEND t_hkey.

t_hkey-col_no = 4.

t_hkey-col_name = 'SEATSMAX'.

APPEND t_hkey.

t_hkey-col_no = 5.

t_hkey-col_name = 'SEATSOCC'.

APPEND t_hkey.

t_hkey-col_no = 6.

t_hkey-col_name = 'PAYMENTSUM'.

APPEND t_hkey.

populating the SEMA data..........................

t_sema-col_no = 1.

t_sema-col_typ = 'STR'.

t_sema-col_ops = 'DFT'.

APPEND t_sema.

t_sema-col_no = 2.

APPEND t_sema.

t_sema-col_no = 3.

APPEND t_sema.

t_sema-col_no = 4.

APPEND t_sema.

t_sema-col_no = 5.

APPEND t_sema.

t_sema-col_no = 6.

APPEND t_sema.

t_sema-col_no = 7.

APPEND t_sema.

t_sema-col_no = 8.

APPEND t_sema.

t_sema-col_no = 9.

APPEND t_sema.

t_sema-col_no = 10.

t_sema-col_typ = 'NUM'.

t_sema-col_ops = 'ADD'.

APPEND t_sema.

CALL FUNCTION 'XXL_FULL_API'

EXPORTING

DATA_ENDING_AT = 54
DATA_STARTING_AT = 5

filename = 'TESTFILE'

header_1 = header1

header_2 = header2

no_dialog = 'X'

no_start = ' '

n_att_cols = 6

n_hrz_keys = 1

n_vrt_keys = 4

sema_type = 'X'

SO_TITLE = ' '

TABLES

data = t_sflight

hkey = t_hkey

online_text = t_online

print_text = t_print

sema = t_sema

vkey = t_vkey

EXCEPTIONS

cancelled_by_user = 1

data_too_big = 2

dim_mismatch_data = 3

dim_mismatch_sema = 4

dim_mismatch_vkey = 5

error_in_hkey = 6

error_in_sema = 7

file_open_error = 8

file_write_error = 9

inv_data_range = 10

inv_winsys = 11

inv_xxl = 12

OTHERS = 13

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.

Edited by: Jaya123 on May 7, 2009 7:20 AM

Re: Transfer data from internal table to excel sheet.

Former Member — Thu, 07 May 2009 05:33:37 GMT

Hi,

What FM you are using & what are the inputs you are passing to the FM.

While downloading data to internal table, in that any text that starts with double quotes and ending with not double quotes, the data is not downloading properly and all the data cramped into one cell. To solve this issue you need to use functinal module CONVERT_SAP_TO_EXCEL_FORMAT .

Else you can use GUI_DOWNLOAD.

Re: Transfer data from internal table to excel sheet.

former_member130219 — Thu, 07 May 2009 07:17:58 GMT

Thanks for helping me.

Got very good helps from u all.

Re: Transfer data from internal table to excel sheet.

Former Member — Mon, 29 Jun 2009 11:09:10 GMT

Hi,

Can u plz sent me that code (internal table to excel sheet). its great help full to me.

thanks.

sankar