[abap2xlsx] Change cell format from string to number with decimals with thousands separator

[Former Member]

Hi,

I want to write a string value in the form of 1.000,00 to excel but it should be explicitely a number on the excel sheet.

So far I'm using the 'set_cell'-method without optional parameters.

I write to a template that has the cell pre-formatted as number.
It comes down to excel with the message that the number is formatted as text:

I want it to be a number and it is important to me not to lose the thousands separator i. e. the point.

Is there a way to do this?
Kind regards
Kai

[Sandra_Rossi]

Based on your screenshot, I guess you mean 522.643,72 and not 1.000,00

PT_CONTENTS-VALUE should be of type P (few other numeric types also work, but not N which is a character format), abap2xlsx should then format correctly the cell. If not, maybe you have defined the cell as being of Text format instead of General or numeric.

If not, make sure you use the latest version of abap2xlsx.

In the demos repository of abap2xlsx, you can find examples where numbers are written with the correct cell formats.

[Former Member]

Many thanks!

pt_contents-value is of type C.

I managed to get the desired result with adding optional parameter ip_abap_type:

[Sandra_Rossi]

Thanks for the feedback.

PS: better change the type of PT_CONTENTS-VALUE. It's best to always have numeric variables in ABAP when the variables store numbers. You need a variable of type C only temporarily when you convert from a "source" to a "target" (as rule-of-thumb, never have a structure or internal table with a component of type C if it's to contain a number).

[AnuragSri]

Hi Kai,

How did you fill the ip_column values in the set_cell method ? Since it takes the values as A, B, C..... can you please let me the know the logic for it.

Thanks,

Anurag

[MarcoK]

Hi Anurag,

I'm not Kai, but first: please consider opening a new thread if you have a new question and

second: take a look at zcl_excel_common=>convert_column2alpha(  ) 😉

[AnuragSri]

Thanks Marco for your suggestion. Also, apologies for extending this thread. Will keep your recommendations in mind 😊

[Sandra_Rossi]

I would not have answered here because you'd better post a new question, but as @MarcoK answered 😉 it's even more simple: just pass the column number (1, 2, 3) instead of the column alpha (A, B, C), it accepts both.

[AnuragSri]

Hi Sandra,

Thanks, and as you said it accepts both 😊

Also, apologies for extending this thread instead of opening a new one 😕

Thanks.