Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Excel format is completely lost after download?

vsubbakrishna
Participant
0 Kudos

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

1 ACCEPTED SOLUTION

Former Member
0 Kudos

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

11 REPLIES 11

Former Member
0 Kudos

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.

Former Member
0 Kudos

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.

Former Member
0 Kudos

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

0 Kudos

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

0 Kudos

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

0 Kudos

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

0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

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

0 Kudos

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

0 Kudos

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