[Excel2007+] Pre-defined pictures are losing the move and size properties

[dw-fel1x]

Hi folks,
thank you for providing this powerful library. It helps us a lot to avoid data exports with formatting the file by each employee :-)
We noticed an issue with the Excel-Reader and templates with inserted graphics, which are losing their move and size property.

Use Case

We are using a pre-defined excel template with some upfront formatting like fonts, basic details, header and footer and a company logo (for this example I took the SAP logo). The library fills in the data from a SAP ALV grid into the excel defined table range.

Issue

A picture is by default always related to a excel cell. In case of resizing this cell (e. g. longer table contents), this will also resize the logo (most likely distorted).

This can be fixed by setting the picture properties to "Move but don't size with cells" or "Don't move or size with cells". However, this setting is not considered properly in abap2xlsx and results in the exported file always to the property value of "Move and size with cells".

Root Cause

The Method LOAD_WORKSHEET_DRAWING is reading the pictures within the template.
The method is checking if it's either a oneCellAnchor (move but don't size) or a twoCellAnchor (move and size) by reading the properties of excelfile.xlsx\xl\drawing\drawing1.xml

However, Excel is using different xml nodes and attributes:
Move and size with cells: <xdr:twoCellAnchor>
Move but don't size with cells: <xdr:twoCellAnchor editAs="oneCell">
Don't move or size with cells: <xdr:twoCellAnchor editAs="absolute">

Solution

I have no clue how to implement a feasible solution, which fulfills all the requirements of an Office Open XML document.
Any feedback, workaround or solution to keep the formatting of the inserted picture is highly appreciated:-)

Best regards,
Felix

[sandraros]

Thanks for notifying about this issue, and for this very detailed analysis.

Reproduced. it works fine for oneCellAnchor which seems to always correspond to "move but don't size", but fails for 2 out of 3 cases with twoCellAnchor, it's always considered as "move and size". The attribute editAs is currently not considered by abap2xlsx.

I did the test on these four files with the demo program zdemo_excel37 which duplicates a file, I did the test with zcl_excel_reader_2007 and zcl_excel_writer_2007:

• issue-1180-drawing-resize-oneCellAnchor-move-but-dont-size.xlsx
• issue-1180-drawing-resize-twoCellAnchor-move-but-dont-size.xlsx
• issue-1180-drawing-resize-twoCellAnchor-move-and-size.xlsx
• issue-1180-drawing-resize-twoCellAnchor-dont-move-or-size.xlsx

Concerned code:

• abap2xlsx/src/zcl_excel_reader_2007.clas.abap

  Lines 3624 to 3654 in 5f784c8

  	drawing = me->get_ixml_from_zip_archive( ip_path ).
  	* one-cell anchor **************
  	anchors = drawing->get_elements_by_tag_name_ns( name = 'oneCellAnchor' uri = namespace-xdr ).
  	coll_length = anchors->get_length( ).
  	iterator = anchors->create_iterator( ).
  	DO coll_length TIMES.
  	anchor_elem ?= iterator->get_next( ).
  	CALL METHOD me->load_drawing_anchor
  	EXPORTING
  	io_anchor_element = anchor_elem
  	io_worksheet = io_worksheet
  	it_related_drawings = rel_drawings.
  	ENDDO.
  	* two-cell anchor ******************
  	anchors = drawing->get_elements_by_tag_name_ns( name = 'twoCellAnchor' uri = namespace-xdr ).
  	coll_length = anchors->get_length( ).
  	iterator = anchors->create_iterator( ).
  	DO coll_length TIMES.
  	anchor_elem ?= iterator->get_next( ).
  	CALL METHOD me->load_drawing_anchor
  	EXPORTING
  	io_anchor_element = anchor_elem
  	io_worksheet = io_worksheet
  	it_related_drawings = rel_drawings.
  	ENDDO.

• abap2xlsx/src/zcl_excel_writer_2007.clas.abap

  Lines 2833 to 2927 in 5f784c8

  	ls_position = io_drawing->get_position( ).
  	IF ls_position-anchor = 'ONE'.
  	ep_anchor = io_document->create_simple_element( name = lc_xml_node_onecellanchor
  	parent = io_document ).
  	ELSE.
  	ep_anchor = io_document->create_simple_element( name = lc_xml_node_twocellanchor
  	parent = io_document ).
  	ENDIF.
  	* from cell ******************************
  	lo_element_from = io_document->create_simple_element( name = lc_xml_node_from
  	parent = io_document ).
  	lv_col = ls_position-from-col.
  	lv_row = ls_position-from-row.
  	lv_col_offset = ls_position-from-col_offset.
  	lv_row_offset = ls_position-from-row_offset.
  	CONDENSE lv_col NO-GAPS.
  	CONDENSE lv_row NO-GAPS.
  	CONDENSE lv_col_offset NO-GAPS.
  	CONDENSE lv_row_offset NO-GAPS.
  	lo_element = io_document->create_simple_element( name = lc_xml_node_col
  	parent = io_document ).
  	lo_element->set_value( value = lv_col ).
  	lo_element_from->append_child( new_child = lo_element ).
  	lo_element = io_document->create_simple_element( name = lc_xml_node_coloff
  	parent = io_document ).
  	lo_element->set_value( value = lv_col_offset ).
  	lo_element_from->append_child( new_child = lo_element ).
  	lo_element = io_document->create_simple_element( name = lc_xml_node_row
  	parent = io_document ).
  	lo_element->set_value( value = lv_row ).
  	lo_element_from->append_child( new_child = lo_element ).
  	lo_element = io_document->create_simple_element( name = lc_xml_node_rowoff
  	parent = io_document ).
  	lo_element->set_value( value = lv_row_offset ).
  	lo_element_from->append_child( new_child = lo_element ).
  	ep_anchor->append_child( new_child = lo_element_from ).
  	IF ls_position-anchor = 'ONE'.
  	* ext ******************************
  	lo_element_ext = io_document->create_simple_element( name = lc_xml_node_ext
  	parent = io_document ).
  	lv_value = io_drawing->get_width_emu_str( ).
  	lo_element_ext->set_attribute_ns( name = 'cx'
  	value = lv_value ).
  	lv_value = io_drawing->get_height_emu_str( ).
  	lo_element_ext->set_attribute_ns( name = 'cy'
  	value = lv_value ).
  	ep_anchor->append_child( new_child = lo_element_ext ).
  	ELSEIF ls_position-anchor = 'TWO'.
  	* to cell ******************************
  	lo_element_to = io_document->create_simple_element( name = lc_xml_node_to
  	parent = io_document ).
  	lv_col = ls_position-to-col.
  	lv_row = ls_position-to-row.
  	lv_col_offset = ls_position-to-col_offset.
  	lv_row_offset = ls_position-to-row_offset.
  	CONDENSE lv_col NO-GAPS.
  	CONDENSE lv_row NO-GAPS.
  	CONDENSE lv_col_offset NO-GAPS.
  	CONDENSE lv_row_offset NO-GAPS.
  	lo_element = io_document->create_simple_element( name = lc_xml_node_col
  	parent = io_document ).
  	lo_element->set_value( value = lv_col ).
  	lo_element_to->append_child( new_child = lo_element ).
  	lo_element = io_document->create_simple_element( name = lc_xml_node_coloff
  	parent = io_document ).
  	lo_element->set_value( value = lv_col_offset ).
  	lo_element_to->append_child( new_child = lo_element ).
  	lo_element = io_document->create_simple_element( name = lc_xml_node_row
  	parent = io_document ).
  	lo_element->set_value( value = lv_row ).
  	lo_element_to->append_child( new_child = lo_element ).
  	lo_element = io_document->create_simple_element( name = lc_xml_node_rowoff
  	parent = io_document ).
  	lo_element->set_value( value = lv_row_offset ).
  	lo_element_to->append_child( new_child = lo_element ).
  	ep_anchor->append_child( new_child = lo_element_to ).
  	ENDIF.