09-08-2009 6:39 AM
Hi all,
In the below excel download program, i am able to download 2 internal tables which will be appended to a single sheet in excel.
But the format is completely lost in the output. How do i retain the format after downloading.
Appreciate your help in this.
REPORT ZXLSDOWNLOAD line-size 400.
types: begin of t_dat,
fld1(30) type c,
fld2(30) type c,
end of t_dat.
TYPES: BEGIN OF ty_heading ,
text(100) TYPE c,
END OF ty_heading.
DATA:i_heading TYPE STANDARD TABLE OF ty_heading,
w_heading TYPE ty_heading.
data: it_dat1 type table of t_dat,
it_dat2 type table of t_dat,
wa_dat type t_dat.
wa_dat-fld1 = '12345678901234567890'.
wa_dat-fld2 = '00000000000000043210'."->ur input
append wa_dat to it_dat1.
wa_dat-fld1 = '12345678901234567890'.
wa_dat-fld2 = '00000000000000543210'."->ur input
append wa_dat to it_dat1.
CLEAR: w_heading.
w_heading-text = 'FIELD1'.
APPEND w_heading TO i_heading.
CLEAR: w_heading.
w_heading-text = 'FIELD2'.
APPEND w_heading TO i_heading.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = 'C:\Documents and Settings\Subba.Krishna\Desktop\fff.xls'
filetype = 'DAT'
write_field_separator = 'X'
append = ' '
wk1_N_format = 'X'
TABLES
data_tab = it_dat1
fieldnames = i_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.
Thanks,
Subba
output
FIELD1 FIELD2
1.23457E+19 43210
1.23457E+19 543210
09-08-2009 12:52 PM
Hi SUBBA,
Download your file into unconverted format and lator from desktop open your file in EXCEL.
During oopeining in Excel, you have the options to convert the data type of any field.
So just change the data type as character for this field as we do in conversion as (TEXT TO COLUMN) in Excel.
Regds,
Anil
09-08-2009 6:46 AM
Hi,
the format didnt change.
Your input is so long, which didnt fit into the excel cell data.
Hence excel automatically converts the data to exponential format. if you increase the cell width, you can see the original format.
Regards,
Jyothi.
09-08-2009 6:48 AM
Hi,
You cannot have the leading zeros in excel which is getting transfered from internal table.
Excel will automatically remove those as it is its default setting.
Two ways you can get the desired output.
1. Download the excel file and then format according to the your requirement.
2. Add ' before the leading zeros and the output in excel ll be like '0000005423.
The second way you cannot use if you are furthur going to process with the data file.
Regards,
Dhasarathy.
09-08-2009 6:49 AM
Hi Subba
You can retain the format of that field in your XL sheet by Formatting the cells (of that field in your Excel Sheet), while formatting
select the Special option in NUmber tab and say OK.
This will retain the format of Field1 which you are using.
The solution may look awkward but it works
Regards,
Syed
09-08-2009 8:13 AM
Hi all,
If I use filetype = 'DBF' i am able to download and get the desired output.
But I have another internal table with append = 'X' , but problem i am facing is only the 2nd internal table gets appended and the first internal table contents do not appear in excel.
Thanks,
Subba
09-08-2009 9:43 AM
For first internal table simply use below code,
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = 'C:\Documents and Settings\Subba.Krishna\Desktop\fff.xls'
write_field_separator = 'X'
TABLES
data_tab = it_dat1. " Table 1
For second internal table use below code,
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = 'C:\Documents and Settings\awi1cob\Desktop\ts.xls'
write_field_separator = 'X'
append = 'X' " Append parameter
TABLES
data_tab = it_dat2. " Table 2
Edited by: Sap Fan on Sep 8, 2009 10:43 AM
09-08-2009 11:49 AM
Hi sap fan,
I tried your suggestion but got the below output,
FIELD1 FIELD2
97 98
99 100
FIELD1 FIELD2
1.23457E+19 44444444440
1.23457E+19 543210
Thanks,
Subba
09-08-2009 12:22 PM
just try below code.
REPORT zxlsdownload LINE-SIZE 400.
TYPES: BEGIN OF t_dat,
fld1(30) TYPE c,
fld2(30) TYPE c,
END OF t_dat.
TYPES: BEGIN OF ty_heading ,
text(100) TYPE c,
END OF ty_heading.
DATA:i_heading TYPE STANDARD TABLE OF ty_heading,
w_heading TYPE ty_heading.
DATA: it_dat1 TYPE TABLE OF t_dat,
it_dat2 TYPE TABLE OF t_dat,
wa_dat TYPE t_dat.
wa_dat-fld1 = '12345678901234567890'. " Table 1
wa_dat-fld2 = '00000000000000043210'."->ur input
APPEND wa_dat TO it_dat1.
wa_dat-fld1 = '12345678901234567890'.
wa_dat-fld2 = '00000000000000543210'."->ur input
APPEND wa_dat TO it_dat1.
CLEAR: w_heading.
w_heading-text = 'FIELD1'.
APPEND w_heading TO i_heading.
CLEAR: w_heading.
w_heading-text = 'FIELD2'.
APPEND w_heading TO i_heading.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = 'C:\Documents and Settings\Subba.Krishna\Desktop\fff.xls'
filetype = 'DAT'
write_field_separator = 'X'
append = ' '
wk1_n_format = 'X'
TABLES
data_tab = it_dat1
fieldnames = i_heading.
wa_dat-fld1 = '345901234567890'. " Table 2
wa_dat-fld2 = '00210'."->ur input
APPEND wa_dat TO it_dat2.
wa_dat-fld1 = '1234567890123456'.
wa_dat-fld2 = '0000000000003210'.
APPEND wa_dat TO it_dat2.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = 'C:\Documents and Settings\Subba.Krishna\Desktop\fff.xls'
filetype = 'DAT'
write_field_separator = 'X'
append = 'X' " Append parameter
wk1_n_format = 'X'
TABLES
data_tab = it_dat2.
Edited by: Sap Fan on Sep 8, 2009 1:22 PM
09-08-2009 12:01 PM
Hi,
In the code you have used just concatenate apostrope ( ' ) the internal table holding the fields. By this the excel will consider it as a text and display the data as it is. Try the following changes in your program.
REPORT ZXLSDOWNLOAD line-size 400.
data : c_quote(4) type c value ''''. " Add this
types: begin of t_dat,
fld1(30) type c,
fld2(30) type c,
end of t_dat.
TYPES: BEGIN OF ty_heading ,
text(100) TYPE c,
END OF ty_heading.
DATA:i_heading TYPE STANDARD TABLE OF ty_heading,
w_heading TYPE ty_heading.
data: it_dat1 type table of t_dat,
it_dat2 type table of t_dat,
wa_dat type t_dat.
wa_dat-fld1 = '12345678901234567890'.
wa_dat-fld2 = '00000000000000043210'. "->ur input
append wa_dat to it_dat1.
wa_dat-fld1 = '12345678901234567890'.
wa_dat-fld2 = '00000000000000543210'. "->ur input
append wa_dat to it_dat1.
CLEAR: w_heading.
w_heading-text = 'FIELD1'.
APPEND w_heading TO i_heading.
CLEAR: w_heading.
w_heading-text = 'FIELD2'.
APPEND w_heading TO i_heading.
" Add this code.
loop at it_dat1.
concatenate c_quotes it_dat1-fld1 to it_dat1-fld1.
concatenate c_quotes it_dat1-fld2 to it_dat1-fld2.
endloop.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = 'C:\Documents and Settings\Subba.Krishna\Desktop\fff.xls'
filetype = 'DAT'
write_field_separator = 'X'
append = ' '
wk1_N_format = 'X'
TABLES
data_tab = it_dat1
fieldnames = i_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.
Regards,
Vikranth
09-08-2009 12:52 PM
Hi SUBBA,
Download your file into unconverted format and lator from desktop open your file in EXCEL.
During oopeining in Excel, you have the options to convert the data type of any field.
So just change the data type as character for this field as we do in conversion as (TEXT TO COLUMN) in Excel.
Regds,
Anil
09-09-2009 5:55 AM
Hi all,
1) If I use the '''' i,e concatenate quote method, after the data has beend downloaded to excel the quote appears to be part of the value itselt which is incorrect.
2) Yes we can use by downloading in .TXT and then open the txt file using excel and by formatting the desired column as "Text". It will work, again depends on user whether they agree to perform these additional steps.
So hoping to try when downloading in excel itself. Please do write if you find any other approach.
Thanks,
Subba
09-09-2009 7:54 AM
Hi all,
i used below code and now it is working.Thanks for your support.
REPORT ZSUB_XLSDWNLD.
types: begin of t_dat,
fld1(30) type C,
fld2(30) type C,
end of t_dat.
TYPES: BEGIN OF ty_heading ,
text(100) TYPE c,
END OF ty_heading.
DATA:i_heading TYPE STANDARD TABLE OF ty_heading,
w_heading TYPE ty_heading.
data: it_dat1 type table of t_dat,
it_dat2 type table of t_dat,
wa_dat type t_dat.
DATA : V_CHAR(1) TYPE C VALUE ''''.
wa_dat-fld1 = '000000000454545454'.
concatenate V_CHAR wa_dat-fld1 INTO wa_dat-fld1.
wa_dat-fld2 = '000000008787877898'."->ur input
concatenate V_CHAR wa_dat-fld2 INTO wa_dat-fld2.
append wa_dat to it_dat1.
wa_dat-fld1 = '99'.
wa_dat-fld2 = '00100'."->ur input
append wa_dat to it_dat1.
CLEAR: w_heading.
w_heading-text = 'FIELD1'.
APPEND w_heading TO i_heading.
CLEAR: w_heading.
w_heading-text = 'FIELD2'.
APPEND w_heading TO i_heading.
data : v_fname type rlgrap-filename value 'C:\Documents and Settings\Subba.Krishna\Desktop\123.xls'.
CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'
EXPORTING
I_FILENAME = v_fname
TABLES
I_TAB_SAP_DATA = it_dat1
EXCEPTIONS
CONVERSION_FAILED = 1
OTHERS = 2
.
Regards,
Subba