topic Re: Convert to Excel in Application Development and Automation Discussions

Convert to Excel

Former Member — Sat, 03 Feb 2024 20:39:06 GMT

How Do everyone!

I am using the FM SAP_CONVERT_TO_XLS_FORMAT to download

an internal table into an excel spreadsheet. Everything

is working fine apart from the leading zeroes disappearing

on one of the columns

i.e. in ABAP the document no is 0051100001 but after the

import into excel it becomes 51100001.

Does anybody know how this can be prevented on the

ABAP side of things?

Cheers

Re: Convert to Excel

vinod_gunaware2 — Wed, 08 Mar 2006 15:23:05 GMT

Check data type of document no. It should be <b>Char</b>.

WS_EXCEL

Download internal table into file and start Excel for it [and upload changed file into the internal table]. See also see FTBU_START_EXCEL

WS_DOWNLOAD

GUI_DOWNLOAD

regards

vinod

Re: Convert to Excel

RichHeilman — Wed, 08 Mar 2006 15:23:06 GMT

This is Excel formatting the column. I beleive that there is little that you can do on the ABAP side. You may be able to do something using OLE, but I'm not sure that it is worth the time and effort.

Regards,

Rich Heilman

Re: Convert to Excel

Former Member — Wed, 08 Mar 2006 15:26:40 GMT

Hi andy

take the document number as a char field.If it is a char field then it will not disappear leading zeors.

Thanks

Khimavath Vikranth

Re: Convert to Excel

RichHeilman — Wed, 08 Mar 2006 15:34:16 GMT

To clarify, this is not working in my system. Notice that the material number field that is char 18 field and in cases of internal number assignment, is zero filled left, but in excel the zeros are stripped off. I don't believe that there is anything that you can do to correct this using the gui_download function.



report zrich_0004 .

data: imara type table of mara with header line.


select * into table imara from mara up to 100 rows.

call function 'GUI_DOWNLOAD'
     exporting
          write_field_separator = 'X'
          filename              = 'C:test.xls'
     tables
          data_tab              = imara.

Regards,

Rich Heilman

Re: Convert to Excel

Former Member — Wed, 08 Mar 2006 15:49:43 GMT

by default, excel will remove leading zeroes.

so overcome this, just we should make that field as char.

for that we can do the below code in the ABAP program,

DATA : V_CHAR(1) TYPE C VALUE ''''. "this variable will have singlequote as value

CONCATENATE V_CHAR

V_FIELD "This is the field which has to

" appear with leading zeroes.

INTO V_FIELD.

then pass this field to ur internal table.

revert,if you still have concerns.

srikanth

added description to V_CHAR

Re: Convert to Excel

Former Member — Wed, 08 Mar 2006 15:52:49 GMT

HI ANDY

1.just try to enter the zeros now manually and then hit an enter , the values will not be there .

2.goto the excel colomn(where the problem is) and right click and say format cells

in here u'll get a sublist of categories , in that choose text ,say ok

and repeat the procedure of entering the values with zeros manually and now hit enter

. now it will hold .

the thing is in excel it will not hold the values by download process

even though the o/p is showing(in abap) me the padded with zero values.

reverse is possible, when i want to extract from excel it is coming into abap .

regards,

vijay.

Re: Convert to Excel

RichHeilman — Wed, 08 Mar 2006 15:54:02 GMT

Srikanth, That doesn't work in my system.

Regards,

Rich Heilman

Re: Convert to Excel

Former Member — Wed, 08 Mar 2006 15:55:07 GMT

Hi Andy,

This is done on the excel side as pointed out by Rich.

I dont think we can do anything on abap side to control this. (Even if you take the field as char, still you find the leading zeroes getting truncated in excel)

Regards,

Raj

Re: Convert to Excel

Former Member — Wed, 08 Mar 2006 16:07:24 GMT

hi rich,

here is the test program, i have developed. in the output i got matnr (1st & 2nd records) with leading zeroes.

you can check this.

REPORT ZSRIM_TEMP1 .

DATA : V_CHAR(1) TYPE C VALUE ''''.

data : begin of itab occurs 0,

matnr like mara-matnr,

mtart like mara-mtart,

end of itab.

concatenate V_CHAR

'00000123'

into itab-matnr.

itab-mtart = 'abcd'.

append itab.

concatenate V_CHAR

'00000124'

into itab-matnr.

itab-mtart = 'abcd'.

append itab.

itab-matnr = '000125'.

itab-mtart = 'abcd'.

append itab.

itab-matnr = '126'.

itab-mtart = 'abcd'.

append itab.

itab-matnr = '127'.

itab-mtart = 'abcd'.

append itab.

data : v_fname type rlgrap-filename value 'c:\temp.xls'.

CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'

EXPORTING

I_FIELD_SEPERATOR =
I_LINE_HEADER =

I_FILENAME = v_fname

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.

ENDIF.

Re: Convert to Excel

Former Member — Wed, 08 Mar 2006 18:00:04 GMT

hi,

is this logic working or not..pl let me know.

its working for me.

srikanth

Re: Convert to Excel

Former Member — Thu, 09 Mar 2006 07:46:06 GMT

Thanks guys for all your help and comments. It seems

Srikanth had the answer in that concantenating the field

with '''' worked after the import into excel.

Thanks again

Andy