topic Re: Format Excel cell in Application Development and Automation Discussions

Format Excel cell

Former Member — Tue, 13 Jan 2009 16:31:48 GMT

Hi,

I am writing a program that will send out an email with an excel attachment. When I open the excel file, it does show the leading zeros if the value contents only digits. For example. if the plant is '0005' the file displays just '5'. Do you know how to format the cell to text field?

I am using FM 'SO_DOCUMENT_SEND_API1' for the email sending.

Thanks,

Chuong

Re: Format Excel cell

Former Member — Tue, 13 Jan 2009 16:34:28 GMT

my typo -- does not show the leading zeros.

Re: Format Excel cell

Former Member — Tue, 13 Jan 2009 16:39:19 GMT

It's an excel thing.

The easiest solution is just to place a non numeric character at the beggining or end of the number to treat it like a string.

Something like .005 or {005

Re: Format Excel cell

Former Member — Tue, 13 Jan 2009 17:03:40 GMT

I'm thinking of a single quote but don't know how to define single quote in ABAP. Please help!

Re: Format Excel cell

Pawan_Kesari — Tue, 13 Jan 2009 17:09:23 GMT

DATA LV_SINGLE TYPE CHAR01 VALUE '''' .

Re: Format Excel cell

Former Member — Tue, 13 Jan 2009 17:17:19 GMT

Thanks. However, the single quote does not have the same functionality as in Excel. It does not change to the text format.

Re: Format Excel cell

Pawan_Kesari — Tue, 13 Jan 2009 17:26:31 GMT

I think if you pass data as '0005 then it should display as 0005 in excel.

Could you check whether you are loosing leading zero before you are passing the data to FM (for email)

If you are using CONCATENATE statement to add single quote to plant then check whether you are loosing zeros there.

Re: Format Excel cell

naimesh_patel — Tue, 13 Jan 2009 17:32:35 GMT

Your file contains the leading zeros but when you open it in Excel, Excel is removing those leading zeros. Because excel treats that as a number.

Check this on [Preserve Downloaded data formatting in Excel |http://help-abap.blogspot.com/2008/09/preserve-downloaded-data-formatting-in.html]

Regards,

Naimesh Patel

Re: Format Excel cell

Former Member — Tue, 13 Jan 2009 18:54:57 GMT

I still have the zeors before passing to the FM. In excel, it displayed '0005 instead of 0005.

Re: Format Excel cell

Private_Member_32931 — Tue, 13 Jan 2009 19:25:33 GMT

Hi,

You can configure the Excel to display the cell/collum in text format.

Best regards.

Henrique

Re: Format Excel cell

Former Member — Wed, 14 Jan 2009 14:56:16 GMT

Hi Henrique,

Can we do this in SAP instead of Excel? Thanks!

Re: Format Excel cell

Former Member — Wed, 14 Jan 2009 17:34:47 GMT

Hi,

If you are using OLE automation to create the excel, you should format the cell like this:

 
SET PROPERTY OF cell 'NumberFormat' = '@'.

Regards,

Claudiu