02-27-2009 9:09 AM
I have used GUI_DOWNLOAD function module to download an internal table to excel format.
Download the excel file.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = filename
filetype = 'ASC'
write_field_separator = 'X'
TABLES
data_tab = <tab>
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.
And I am able to successfully download the excel file but the problem occurs when I try to open the file.
System throws the warning "The file you are trying to open, '[filename]', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"
And if I click yes, it correctly displays the excel file.
I want to get rid of this warning. Is there any way I can code so that this warning does not occur. Kindly advise.
02-27-2009 9:13 AM
HI,
try this
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = filename
filetype = 'ASC'
write_field_separator = '|'
TABLES
data_tab = <tab>
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.
Regards,
Venkatesh
02-27-2009 9:13 AM
Hi Vineeta,
Try changing the file type to 'XLS'
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = filename
filetype = 'XLS'
Hope this helps.
Regards
Gowthami
02-27-2009 9:14 AM
probably when u give filename the extension should be .xls, did u given
02-27-2009 9:15 AM
HI,
Data:filename type rlgraph-filename value 'c:\temp.xls'.
Specifi the Extension : .XLS
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = filename
*filetype = 'ASC' " Comment this
write_field_separator = 'X'
TABLES
data_tab = <tab>
02-27-2009 9:25 AM
Hi Vineeta,
There are several ways to achieve it.
you can save the file with *.csv extension and the comma separator.
you can use an ALV to perform the download.
and you can use the instructions for
CREATE OBJECT EXCEL 'EXCEL.APPLICATION'.
regards,
Edgar
02-27-2009 9:26 AM
I have given the filename with .xls extension.
Also I tried
- commenting filetype in GUI_DOWNLOAD function
- set filetype to 'XLS'
- set write_field_separator to '|'
but none of this helped. Any further ideas?
02-27-2009 9:36 AM
Hi,
Use FM
'SAP_CONVERT_TO_XLS_FORMAT'
CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'
EXPORTING
i_filename = p_path
TABLES
i_tab_sap_data = p_table.
p_table is your internal table
p_path is the path where ur file is to be saved. Define p_path like
DATA:p_path like rlgrap-filename.
Regards,
Arun.
02-27-2009 10:03 AM
Hi Arun,
I tried a sample program using SFLIGHT data to convert to excel.
DATA: l_filename like rlgrap-filename.
l_filename = 'C:\temp.xls'.
CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'
EXPORTING
i_filename = l_filename
TABLES
i_tab_sap_data = lt_sflight.
But when I try to open the excel file, system throws error - 'temp.xls:: file format is not valid.'
Am I missing anything here?
Regards,
Vineeta.
02-27-2009 10:04 AM
Hi,
Use code, its working:-
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = 'C:/TEST.XLS' "filename.xls
filetype = 'ASC'
write_field_separator = 'X'
TABLES
data_tab = <itab> "internal table
Regards,
Tarun
02-27-2009 10:10 AM
Hi Tarun,
Are you able to open the test.xls file without any warnings?
I get the warning "The file you are trying to open, 'filename', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"
However if I click 'Yes', the file opens correctly. I want to get rid of this warning.
Did you face this issue?
Regards,
Vineeta.
02-27-2009 10:20 AM
Hi,
Are you using Office 2007 to open the excel file ? If yes, it might be giving a compatibility problem. Try installing patches from Microsoft and try again. I dont think variations in input parameters of GUI_DOWNLOAD can help much here.
Thanks.
02-27-2009 10:37 AM
this is a working example:
REPORT zedgar_download.
DATA: lt_t001l TYPE TABLE OF t001l WITH HEADER LINE.
DATA: lt_str TYPE TABLE OF string WITH HEADER LINE.
FIELD-SYMBOLS <fs>.
START-OF-SELECTION.
SELECT * FROM t001l INTO TABLE lt_t001l.
LOOP AT lt_t001l.
CLEAR lt_str.
DO.
ASSIGN COMPONENT sy-index OF STRUCTURE lt_t001l TO <fs>.
IF sy-subrc NE 0.
EXIT.
ENDIF.
CONCATENATE lt_str <fs> INTO lt_str SEPARATED BY ';'.
ENDDO.
APPEND lt_str.
ENDLOOP.
CALL METHOD cl_gui_frontend_services=>gui_download
EXPORTING
filename = 'c:\test.csv'
filetype = 'ASC'
CHANGING
data_tab = lt_str[]
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
not_supported_by_gui = 22
error_no_gui = 23
OTHERS = 24
.
my separator is ';' but you probably should use the most standard csv separator ','.
does this satisfy your need?
02-27-2009 11:38 AM
I'll post you too the code for the excel OLE object, since I like it so much despite being so slow:
REPORT zedgar_excel.
INCLUDE ole2incl. " OLE-specific include file
START-OF-SELECTION.
DATA: excel TYPE ole2_object. " EXCEL object
DATA: books TYPE ole2_object. " collection of books
DATA: book TYPE ole2_object. " book
DATA: cell TYPE ole2_object. " cell
DATA: sheet TYPE ole2_object.
DATA: font TYPE ole2_object. "you can actually set a font if you'd like
DATA: application TYPE ole2_object,
workbook TYPE ole2_object.
DATA: lt_t001l TYPE TABLE OF t001l WITH HEADER LINE.
FIELD-SYMBOLS <fs>.
DATA: l_tabix LIKE sy-tabix.
START-OF-SELECTION.
*inicialize OLE
CREATE OBJECT excel 'EXCEL.APPLICATION'.
CALL METHOD OF excel 'WORKBOOKS' = books.
CALL METHOD OF books 'ADD' = book.
CALL METHOD OF excel 'WORKSHEETS' = sheet.
* CALL METHOD OF sheet 'ADD'.
* SELECT * FROM t001l INTO TABLE lt_t001l.
SELECT * FROM t001l UP TO 3 ROWS INTO TABLE lt_t001l.
LOOP AT lt_t001l.
l_tabix = sy-tabix.
DO.
ASSIGN COMPONENT sy-index OF STRUCTURE lt_t001l TO <fs>.
IF sy-subrc NE 0.
EXIT.
ENDIF.
CALL METHOD OF excel 'CELLS' = cell EXPORTING #1 = l_tabix #2 = sy-index.
SET PROPERTY OF cell 'VALUE' = <fs>.
ENDDO.
ENDLOOP.
* SET PROPERTY OF excel 'VISIBLE' = 1.
*or.....
CALL METHOD OF book 'SaveAs' EXPORTING #1 = 'C:\test2.XLS'
#2 = 1.
FREE OBJECT cell.
FREE OBJECT sheet.
FREE OBJECT book.
FREE OBJECT books.
FREE OBJECT excel.
there you go, quite a bit of fun.
02-27-2009 10:06 AM
Also the same thing works with FM GUI_DOWNLOAD and not with FM SAP_CONVERT_TO_XLS_FORMAT.
Regards,
Vineeta.
02-27-2009 10:56 AM
Hi friend,
Try this simple code..
REPORT zdemodownload .
*Types
TYPES: BEGIN OF g_r_mara,
matnr LIKE mara-matnr,
ersda LIKE mara-ersda,
laeda LIKE mara-laeda,
mtart LIKE mara-mtart,
mbrsh LIKE mara-mbrsh,
END OF g_r_mara.
*Data
DATA: g_t_mara TYPE TABLE OF g_r_mara,
filename TYPE string,
s TYPE i.
*Tables
TABLES: mara, sscrfields.
*Selection Screen
SELECT-OPTIONS: s_matnr FOR mara-matnr.
SELECTION-SCREEN SKIP.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN PUSHBUTTON 10(20) name USER-COMMAND ucom.
SELECTION-SCREEN END OF LINE.
INITIALIZATION.
name = 'Spread Sheet'.
AT SELECTION-SCREEN.
IF sscrfields-ucomm = 'UCOM'.
s = 1.
ENDIF.
*Data retrival and populating internal table
IF s = 1.
SELECT matnr ersda laeda mtart mbrsh
INTO CORRESPONDING FIELDS OF TABLE g_t_mara
FROM mara
WHERE matnr IN s_matnr.
filename = 'C:\Testing.xls'.
*Downloading data from internal table to excel
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = filename
filetype = 'ASC'
write_field_separator = 'X'
TABLES
data_tab = g_t_mara.
ENDIF.
Thanks..
06-21-2013 10:00 AM
Hi,
If SAP_CONVERT_TO_XLS_FORMAT is not working properly then,
you can use this function module EXCEL_OLE_STANDARD_DAT'...
Also, SAP_CONVERT_TO_XLS_FORMAT converts every data into a character format.
If you want to convert the data into required types use EXCEL_OLE_STANDARD_DAT.
12-18-2015 7:14 AM
I was having problems with SAP_CONVERT_TO_XLS_FORMAT, then I used EXCEL_OLE_STANDARD_DAT fm and it worked alright. Thanks Abdul.
06-21-2013 11:20 AM
Hi,
Try Like This
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.
06-21-2013 11:46 AM
hi,
their are so many ways(like GUI_DOWNLOAD,WS_DOWNLOAD.. ) to convert internal table to excel format .
but the thing is you have see the internal table is suitable according to downloading file then your problem is solve.
vali