Conversion of qty field in excel with decimals and commas

[rashmi_purohit2]

Hi Experts,

We have a report in which we are sending a mail as xls attachment to the sender.

In this we have qty field. But they get displayed without the thousand and decimal saperator. e.g 1234 where it should be 1,234.000.

Is there any way to do this. Will WRITE statement help.?

Its urgent.Kndly let me know as soon as possible.

Thanks in advance,

Rashmi.

[former_member585060]

Hi,

Yes, WRITE statement will work. While filling the amount & quantity fields of the excel sheet, don't just move, instead use WRITE statement.

WRITE : wa_outtab-netwr TO i_excel-netwr.

Regards

Bala Krishna

[rashmi_purohit2]

Hi,

Is is that the i_excel-netwr if type netwr?

the field is passed as string...to the excel currently

can you let me know how will i pass it to excel..

Thanks.

[rashmi_purohit2]

This is how the current code is....

LOOP AT i_emailfieldcat1 INTO wf_emailfieldcat.

CLEAR lf_fieldname.

lf_fieldname = wf_emailfieldcat-fieldname.

ASSIGN COMPONENT lf_fieldname OF

STRUCTURE i_mail_final TO <fs>.

IF wa_rec-data IS INITIAL.

CONCATENATE lv_string gc_tab <fs>

INTO lv_string . "wa_rec-data.

ELSE.

CONCATENATE lv_string gc_tab <fs>

INTO lv_string . "wa_rec-data.

ENDIF.

ENDLOOP.

[former_member585060]

Can you clarify which one is the output table which holds the data to be sent to excel? can you tell us the structure of gc_tab and wa_rec.

[rashmi_purohit2]

wa_rec-data(255) is type c ,

gc_tab is type c value '#'

Lv_string is then passed to the class

cl_bcs_convert=>string_to_solix

EXPORTING

iv_string = lv_string

iv_codepage = '4103' "suitable for MS Excel, leave empty

iv_add_bom = 'X' "for other doc types

IMPORTING

et_solix = binary_content

ev_size = size ).

CATCH cx_bcs.

MESSAGE e445(so).

Hope this is helps you.

[former_member585060]

Check the value of lf_fieldname value for the NETWR field, if found, WRITE <FS> to lv_value, then replace the <FS> value with lv_value. in CONCATENATE statement.

[rashmi_purohit2]

Hi,

But while concatenating the lv_value should be either C,N, D, T or string..

So do i define the lv_value type LABST ehich is my field.and the copy it into the string?

Will that help?

Thanks a ton!.

Rashmi

[former_member585060]

Hi,

Declare the lv_value as type c, of the length of your LABST.

suppose if it is length 13

DATA : lv_value(13) TYPE c.

Regards

Bala Krishna

Edited by: Bala Krishna on Jun 21, 2010 6:42 PM

[rashmi_purohit2]

I tried this but still it is not working

Any other way?

[former_member585060]

Hi,

Is the below loop is inside any other LOOP and ENDLOOP? after WRITE statement, have you checked the value of the lv_string in debugger? can you paste the complete Output table loop and endloop.

Regards

Bala Krishna

[rashmi_purohit2]

Thanks a ton to evryone who contributed to this thread.

My query is resolved..

Write statement helped..

Had to build little logic though....but now it is workng...

Thanks once again.

[OttoGold]

In fact that should be easily handled by the Excel. You only need to assign the right Excel type/ display pattern for that field in Excel. How do you create the Excel? OLE? XML?

Otto

[rashmi_purohit2]

HI,

We aresending the excel as an attachment in a mail, so the LABST field is not displayed with thousands and decimal seperators.

How can we do that settings in excel programatically??

Thanks,

Rashmi.

[OttoGold]

HOW do you CREATE the Excel? Depending on the method you can set up the field type to be formatted by Excel.

Otto

[rashmi_purohit2]

wa_rec-data(255) is type c ,

gc_tab is type c value '#'

Lv_string is then passed to the class

This creates the EXCEL from a string...(LV_STRING)

cl_bcs_convert=>string_to_solix

EXPORTING

iv_string = lv_string

iv_codepage = '4103' "suitable for MS Excel, leave empty

iv_add_bom = 'X' "for other doc types

IMPORTING

et_solix = binary_content

ev_size = size ).

CATCH cx_bcs.

MESSAGE e445(so).

[OttoGold]

What about some more advanced approach?

XML Excel: /people/otto.gold/blog/2010/02/11/happy-reporting-with-excel-ii

Otto