How to prevent Excel from deleting leading zeros

[I-i-I-i]

Hi Experts,
I'm using ABAP2XLSX for a project and mostly it's working fine, but I'm trying to find a way how to display leading zeros in my xlsx file when it comes to display floating numbers.

So far I tried every number_format available in the ABAP2XLSX package. 

For example it looks like this:

lo_style_numbers->number_format->format_code = zcl_excel_style_number_format=>c_format_number_comma_sep2.

the datatype Im using in my structure is abap.fltp and Im using the fieldcatalog to fill the table:

LOOP AT lt_field_catalog ASSIGNING <fs_field_catalog>.
CASE <fs_field_catalog>-fieldname.
WHEN 'ZAHL'.
<fs_field_catalog>-position = 6.
<fs_field_catalog>-dynpfld = abap_true.
<fs_field_catalog>-style = lo_style_numbers->get_guid( ).
<fs_field_catalog>-scrtext_s = 'Fließkommazahl'.
<fs_field_catalog>-scrtext_m = 'Fließkommazahl'.
<fs_field_catalog>-scrtext_l = 'Fließkommazahl'.
<fs_field_catalog>-totals_function = zcl_excel_table=>totals_function_sum.
WHEN OTHERS.
<fs_field_catalog>-dynpfld = abap_false.
ENDCASE.
ENDLOOP.

 Now, when I create the file with "gs_test-zahl = '09.20'." for example, it always cuts the first zero.

Since I could'nt find any hints via research my hope lies in your hands now ‌‌ 
Thanks a lot so far and please let me know if you need more information to provide any help!

I-i-I-i

[Sandra_Rossi]

It's not really an abap2xlsx question, it's an Excel question. In Excel, if you type "09.20", you will get the number "9.2" (it removes the left and right zeroes).

You solve it by using the adequate Excel Number Format like e.g. "#00.00" if you want a minimum of two zeroes on the left and two zeroes on the right.

More information on Number Formats here: Number format codes - Microsoft Support

With abap2xlsx (9.2 will be displayed 09.20):

lo_style_numbers->number_format->format_code = '#00.00'.

NB: please use the formatting options of the new forum when posting code (here is your code, unchanged):

LOOP AT lt_field_catalog ASSIGNING <fs_field_catalog>.
  CASE <fs_field_catalog>-fieldname.
    WHEN 'ZAHL'.
      <fs_field_catalog>-position = 6.
      <fs_field_catalog>-dynpfld = abap_true.
      <fs_field_catalog>-style = lo_style_numbers->get_guid( ).
      <fs_field_catalog>-scrtext_s = 'Fließkommazahl'.
      <fs_field_catalog>-scrtext_m = 'Fließkommazahl'.
      <fs_field_catalog>-scrtext_l = 'Fließkommazahl'.
      <fs_field_catalog>-totals_function = zcl_excel_table=>totals_function_sum.
    WHEN OTHERS.
      <fs_field_catalog>-dynpfld = abap_false.
  ENDCASE.
ENDLOOP.

[robert_kuhlmann]

Add infront ot the numer a single quote
095 => 95
'095 => 095

[Sandra_Rossi]

IMHO, not a good idea to have the numbers converted into texts, it's impossible to do operations, totals on them. A Number Format lets the numbers be numbers.

NB: you are replying to me, maybe you tried to answer directly to the person who asked the question.

[I-i-I-i]

That was really helpfull, thanks a lot! In future I'll keep the formatting options for code in mind, it was my very first question ever asked here 🙂 

[Sandra_Rossi]

Thanks. But you can still edit your question and improve the formatting of your code to make your question more attractive for all future visitors.

[I-i-I-i]

I'd love to, but when I try to edit my post and insert the code snippets I get the following error message and dunno how to handle it:

Your post has been adjusted to remove unsupported HTML found in the message body. The HTML has been updated. Please review the post and send it when you are satisfied.

---

If there is any guideline or you may know what exactly is messing arround with the found I'll see to it asap.

[Sandra_Rossi]

Usually, Ctrl+Shift+V can be used in Edge to paste without unsupported HTML ("unformatted paste"). If not, paste your code to a basic Notepad-like application, then re-copy it and paste it into the forum.