Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

[ABAP2XLSX] number format zcl_excel_style_number_format=>C_FORMAT_XLSX40 is not working as intended

padchalingam
Explorer
0 Kudos
3,384

Hi,

Number format zcl_excel_style_number_format=>C_FORMAT_XLSX40 is not working as intended.

('#,##0.00_);[Red](#,##0.00)')

Desired results -

But this what it is showing in the Excel

This is the code, I am using

" Create cell style for editable fields

lo_style_editable = lo_excel->add_new_style( ).

lo_style_editable->font = lo_style_arial8->font.

lo_style_editable->fill->filltype = zcl_excel_style_fill=>c_fill_solid.

lo_style_editable->fill->fgcolor-rgb = zcl_excel_style_color=>c_CREAM.

lo_style_editable->number_format->format_code = zcl_excel_style_number_format=>C_FORMAT_XLSX40.

lo_style_editable->protection->locked = zcl_excel_style_protection=>c_protection_unlocked.

lo_style_editable->borders->right = lo_border_dark.

lo_style_editable->borders->down = lo_border_dark.

lo_style_editable->borders->left = lo_border_dark.

lo_style_editable->borders->top = lo_border_dark.

lo_style_editable->alignment->WRAPTEXT = abap_true..

lo_style_editable->alignment->horizontal = zcl_excel_style_alignment=>C_HORIZONTAL_RIGHT.

lo_style_editable->alignment->vertical = zcl_excel_style_alignment=>c_vertical_center..

Thanks

Prabha

8 REPLIES 8

padchalingam
Explorer
0 Kudos
2,005

Data type is CURR

RameshGulipall
Active Participant
0 Kudos
2,005

Hi Prabha,

You can achieve this transformation logic in mapping in integration layer if it is inbound /outbound scenario to SAP.

If it is integration system as SAP PO/CPI then you use message mapping for this conversion.

If it is integration system as Ariba CIG then you use custom mapping for this conversion.

Regards,
Ramesh

0 Kudos
2,005

Thanks Ramesh.

I modified the code with ip_abap_type = cl_abap_typedescr=>TYPEKIND_FLOAT and it has improved the formatting.

lo_worksheet->set_cell( ip_column = 'G' ip_row = ip_row ip_value = lv_content ip_style = lo_style_tmp->get_guid( ) ip_abap_type = cl_abap_typedescr=>TYPEKIND_FLOAT ).

Sandra_Rossi
Active Contributor
0 Kudos
2,005

I just tested. The negative number -660000 is displayed in red and parentheses around (660,000). Why do you think C_FORMAT_XLSX40 #,##0.00_);[Red](#,##0.00) should display -660,000.00?

To reproduce, I copied ZDEMO_EXCEL_22, inserted the line below (...=>c_format_xlsx40), ran and typed -660000 in the Airfare column of the displayed Excel file:

  " Create cell style for editable fields
  lo_style_editable = lo_excel->add_new_style( ).
  lo_style_editable->protection->locked = zcl_excel_style_protection=>c_protection_unlocked.
  lo_style_editable->number_format->format_code = zcl_excel_style_number_format=>c_format_xlsx40.

  lt_field_catalog = zcl_excel_common=>get_fieldcatalog( ip_table = lt_test ).

NB: the exact rendering comma/dot/thousands depend on MS Office (region settings).

Sandra_Rossi
Active Contributor
0 Kudos
2,005

In case you want to know more about the number format codes, see there in Microsoft Web site: Number format codes

padchalingam
Explorer
0 Kudos
2,005

Thanks Sandra,

I supplied the parameter value ip_abap_type = cl_abap_typedescr=>TYPEKIND_FLOAT in the method set_cell and resolved the issue. I have the desired results now.

0 Kudos
2,005

If you use IP_ABAP_TYPE = 'F' and Excel now considers the value as a number (so the number format applies), then I guess you had assigned to parameter IP_VALUE a variable of type C or string (LV_CONTENT) containing a number-like text. It's always good to store values in ABAP in the right type, that avoids issues.

2,005

Sandra,

You pinpointed the issue correctly. Yes I assigned String type value to IP_VALUE. Had I assigned number type value(instead of string), I wouldn't have needed ip_abap_type = cl_abap_typedescr=>TYPEKIND_FLOAT in the SET_CELL method. I learned something today.

Thanks

Prabha