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

[padchalingam]

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

[padchalingam]

Data type is CURR

[RameshGulipall]

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

[padchalingam]

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]

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]

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

[padchalingam]

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.

[Sandra_Rossi]

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.

[padchalingam]

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