Formatting excel -> freezing top row and thousand separator for amount cells using IF_IXML_ELEMENT

[former_member621378]

Dear Gurus,

I have almost done to create and format an excel file using IF_IXML_ELEMENT interface by Abap programming, then attaching and sending it to recipients by email. Unfortunately, I haven't achieved yet for making only 3 kind of formatting to excel and I really need for your help. Those 3 things are as below:

1. To freeze&fix first top row in the excel table,

2. To display numbers with thousand separators in specific cells in excel table,

3.To display data 's font in a whole row bold and red at the same time, I achieved both of them separated both not at the same time. I need to display both properties at the same time for a specific cell or a specific row in excel file.

I really worked hard and googled many many times but I couldn't find any solution about those 3 problems of excel formatting.

If you help me, I would be really grateful Gurus.

Thanks and Regards.

You can find some piece of code i wrote here below:

SPAN { font-family: "Courier New"; font-size: 10pt; color: #000000; background: #FFFFFF; } .L0S32 { color: #3399FF; } .L0S52 { color: #0000FF; } .L0S55 { color: #800080; }

DATA : l_element_root TYPE REF TO if_ixml_element,
ns_attribute TYPE REF TO if_ixml_attribute,
r_element_properties TYPE REF TO if_ixml_element,
r_element TYPE REF TO if_ixml_element,
r_worksheet TYPE REF TO if_ixml_element,
r_table TYPE REF TO if_ixml_element,
r_column TYPE REF TO if_ixml_element,
r_row TYPE REF TO if_ixml_element,
r_cell TYPE REF TO if_ixml_element,
r_data TYPE REF TO if_ixml_element,
l_value TYPE string,
l_type TYPE string,
l_text(100) TYPE c,
r_styles TYPE REF TO if_ixml_element,

SPAN { font-family: "Courier New"; font-size: 10pt; color: #000000; background: #FFFFFF; } .L0S31 { font-style: italic; color: #808080; } .L0S33 { color: #4DA619; } .L0S52 { color: #0000FF; } .L0S55 { color: #800080; } .L0S70 { color: #808080; }

* Creating a ixml Factory
l_ixml = cl_ixml=>create( ).
* Creating the DOM Object Model
l_document = l_ixml->create_document( ).
* Create Root Node 'Workbook'
l_element_root = l_document->create_simple_element( name = 'Workbook' parent = l_document ).
l_element_root->set_attribute( name = 'xmlns' value = 'urn:schemas-microsoft-com:office:spreadsheet' ).

ns_attribute = l_document->create_namespace_decl( name = 'ss' prefix = 'xmlns' uri = 'urn:schemas-microsoft-com:office:spreadsheet' ).
l_element_root->set_attribute_node( ns_attribute ).

ns_attribute = l_document->create_namespace_decl( name = 'x' prefix = 'xmlns' uri = 'urn:schemas-microsoft-com:office:excel' ).
l_element_root->set_attribute_node( ns_attribute ).

SPAN { font-family: "Courier New"; font-size: 10pt; color: #000000; background: #FFFFFF; } .L0S31 { font-style: italic; color: #808080; } .L0S33 { color: #4DA619; } .L0S52 { color: #0000FF; } .L0S55 { color: #800080; } .L0S70 { color: #808080; }

* Worksheet
r_worksheet = l_document->create_simple_element( name = 'Worksheet' parent = l_element_root ).
r_worksheet->set_attribute_ns( name = 'Name' prefix = 'ss' value = 'Mamül Stok Raporu' ).

* Table
r_table = l_document->create_simple_element( name = 'Table' parent = r_worksheet ).
r_table->set_attribute_ns( name = 'FullColumns' prefix = 'x' value = '1' ).
r_table->set_attribute_ns( name = 'FullRows' prefix = 'x' value = '1' ).

SPAN { font-family: "Courier New"; font-size: 10pt; color: #000000; background: #FFFFFF; } .L0S31 { font-style: italic; color: #808080; } .L0S33 { color: #4DA619; } .L0S52 { color: #0000FF; } .L0S55 { color: #800080; } .L0S70 { color: #808080; }

* Data Table
LOOP AT pt_mamul INTO ps_mamul.
r_row = l_document->create_simple_element( name = 'Row' parent = r_table ).
** Material No.
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ). "110719 burda dur
* r_cell->set_attribute_ns( name = 'Bold' prefix = 'ss' value = '1' ). "160719
l_value = ps_mamul-matnr.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
if not ps_mamul-general_total is initial.
l_value = ''.
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ). " Cell format
else.
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'Number' ). " Cell format
endif.

IF ps_mamul-negative_stock = 'X'.
* r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Red' ).
r_format->set_attribute_ns( name = 'Position' prefix = 'ss' value = 'Center' ).
* r_data->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Red' ).
ELSEIF ps_mamul-general_total = 'X'.
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Yellow' ).
r_format_cell_yellow->set_attribute_ns( name = 'Color' prefix = 'ss' value = '#FF0000').
ENDIF.
* Material Description
r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
l_value = ps_mamul-maktx.
r_data = l_document->create_simple_element( name = 'Data' value = l_value parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ). " Cell format
IF ps_mamul-negative_stock = 'X'.
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Red' )." IT SHOULD BE RED AND BOLD
ELSEIF ps_mamul-general_total = 'X'.
r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Yellow' ).
ENDIF.

[UweFetzer_se38]

Can you please delete the tag ABAP Connectivity? Thank you

[Sandra_Rossi]

Any reason to use that old 2003 XML format instead of 2007 XLSX format? If you use abap2xlsx, pane freezing should work (method FREEZE_PANES of ZCL_EXCEL_WORKSHEET).

[DoanManhQuynh]

in your case, is it not easier to use transformation?

[swarnava]

Hi Mehmut,

i have a similar requirement of dynamically coloring rows in multi tab excel . Could you please post your piece of code by which you have achieved coloring a row ?