Size of comments

[RixarSAP]

Hi,

Is it possible to change the size of a comment?

I've seen the code in demo program ZDEMO_EXCEL_COMMENTS.
But cannot see any way to change the size of a comment.

I'm trying to change the code of method ZCL_EXCEL_WRITER_2007-CREATE_XL_DRAWING_FOR_COMMENTS to change the size of the shape in this line:

    lo_element_shape->set_attribute_ns( : name  = lc_xml_attr_id          value = lv_attr_id ),
                                          name  = lc_xml_attr_type        value = '#_x0000_t202' ),
                                          name  = lc_xml_attr_style       value = 'size:auto;width:auto;height:auto;position:absolute;margin-left:117pt;margin-top:172.5pt;z-index:1;visibility:hidden;cx=300;cy=400' ),
                                          name  = lc_xml_attr_fillcolor   value = '#ffffe1' ),
                                          name  = lc_xml_attr_oinsetmode  value = lc_xml_attr_val_auto ).

But doesnt work.
I've tried adding cx and cy attributes, and changing width and height...

Any tip?

Thanks

[sandraros]

Not sure if I can help. Here is the only thing that Excel changes if I increase the box of a comment (this Excel file was not generated with abap2xlsx):

vmlDrawing#.xml (red=before, green=after):

After change:

<xml xmlns:v="urn:schemas-microsoft-com:vml"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel">
 <o:shapelayout v:ext="edit">
  <o:idmap v:ext="edit" data="1"/>
 </o:shapelayout><v:shapetype id="_x0000_t202" coordsize="21600,21600" o:spt="202"
  path="m,l,21600r21600,l21600,xe">
  <v:stroke joinstyle="miter"/>
  <v:path gradientshapeok="t" o:connecttype="rect"/>
 </v:shapetype><v:shape id="_x0000_s1025" type="#_x0000_t202" style='position:absolute;
  margin-left:59.25pt;margin-top:1.5pt;width:160.5pt;height:95.25pt;z-index:1;
  mso-wrap-style:tight' fillcolor="infoBackground [80]" o:insetmode="auto">
  <v:fill color2="infoBackground [80]"/>
  <v:shadow color="none [81]" obscured="t"/>
  <v:path o:connecttype="none"/>
  <v:textbox style='mso-direction-alt:auto'>
   <div style='text-align:left'></div>
  </v:textbox>
  <x:ClientData ObjectType="Note">
   <x:MoveWithCells/>
   <x:SizeWithCells/>
   <x:Anchor>
    1, 15, 0, 2, 4, 37, 6, 9</x:Anchor>
   <x:AutoFill>False</x:AutoFill>
   <x:Row>0</x:Row>
   <x:Column>0</x:Column>
   <x:Visible/>
  </x:ClientData>
 </v:shape></xml>

[RixarSAP]

hi, @sandraros . How do you get this XML from an excel file?

If I use save as -> XML I cannot see the "anchor" tag, looks all different.
In my developers tab, within XML, the option "Export" is disabled, dont know why is grayed. Is this the option you use to create the xml?

[sandraros]

Open the xlsx file like a ZIP file.

[RixarSAP]

Thank you !
The key is this anchor .

I need to research a bit more about this "anchor" element.
But doing some test I've managed to control the size of the comments.

I've made some changes in the solution:

1. I've created some new attributes in class ZCL_EXCEL_COMMENT

  constants DEFAULT_WIDTH type I value 2. "#EC NOTEXT
  constants DEFAULT_HEIGHT type I value 15. "#EC NOTEXT
  data WIDTH type I .
  data HEIGHT type I .

2. And added these new methods:

methods SET_SIZE
    importing
      !IP_WIDTH type I optional
      !IP_HEIGHT type I optional .
  methods GET_SIZE
    exporting
      !EP_WIDTH type I
      !EP_HEIGHT type I .

METHOD set_size.

  IF ip_width IS SUPPLIED.
    width = ip_width.
  ENDIF.

  IF ip_height IS SUPPLIED.
    height = ip_height.
  ENDIF.

ENDMETHOD.

method GET_SIZE.

  IF width IS NOT INITIAL.
    ep_width = width.
  ELSE.
    ep_width = default_width. "Default width
  ENDIF.

  IF height IS NOT INITIAL.
    ep_height = height.
  ELSE.
    ep_height = default_height. "Default height
  ENDIF.

endmethod.

3. Then changed method ZCL_EXCEL_COMMENT of class ZCL_EXCEL_WRITER_2007:

*--------------------------------------------------------------------*
* Ricardo R. - 2024.05.02
*--------------------------------------------------------------------*
    DATA:
       lv_anchor     TYPE string,
       lv_height     type i,
       lv_width      TYPE i,
       lv_height_str type string,
       lv_width_str  TYPE string.

    CONSTANTS:
     lc_anchor_init TYPE string VALUE  '0, 1, 11, 10, &width&, 31, &height&, 9'.

    CALL METHOD lo_comment->get_size
      IMPORTING
        ep_width  = lv_width
        ep_height = lv_height.

    lv_width_str  = lv_width.
    lv_height_str = lv_height.

    lv_anchor = lc_anchor_init.
    REPLACE '&height&' WITH lv_height_str INTO lv_anchor.
    REPLACE '&width&' WITH lv_width_str INTO lv_anchor.
    lo_element_anchor->set_value( lv_anchor ).

*    lo_element_anchor->set_value( '2, 15, 11, 10, 4, 31, 15, 9' ). "Original line
*--------------------------------------------------------------------*

4. Now, you can set the size of the comments:

  DATA:
   lv_comment              TYPE string,
   lo_comment              TYPE REF TO zcl_excel_comment.

  constants:
    crlf(2) value %_CR_LF. "#EC NOTEXT "Salto de línea

  lo_comment = lo_excel->add_new_comment( ).


  CONCATENATE 'Esto es un comentario' crlf crlf 'aa' crlf crlf 'bb' INTO
  lv_comment SEPARATED BY space.

  lo_comment->set_text( ip_ref = 'C10' ip_text = lv_comment ).
  lo_comment->set_size( ip_width = 2 ip_height = 15 ).
  lo_worksheet->add_comment( lo_comment ).


  CONCATENATE 'Comentario de varias líneas con texto muy largo muy largoooooooooooooo y mucho texto' crlf crlf 'Otra línea' crlf crlf 'Otra línea' INTO
  lv_comment SEPARATED BY space.

  lo_comment = lo_excel->add_new_comment( ).
  lo_comment->set_text( ip_ref = 'D10' ip_text = lv_comment ).
  lo_comment->set_size( ip_width = 5 ip_height = 30 ).
  lo_worksheet->add_comment( lo_comment ).

This is a comment with width 2 and height 15

This is a comment with width 5 and height 30

[gregorwolf]

Hi @RixarSAP,

would be great if you can contribute this as a pull request

[RixarSAP]

Hello, @gregorwolf I would like to contribute with this idea.
But i dont know exactly how.
I'm not used to use GitHub and in my system the report ZABAPGIT_STANDALONE doesnt activate due my old Abap version (SAP_ABA 701), it has a lot of syntax errors...

[sandraros]

@RixarSAP It's less convenient but you may directly edit the .abap files in your GitHub fork of abap2xlsx. Please just replace the lines of code corresponding to your changes, to avoid pushing your unrelated ABAP 7.01 changes in the rest of the code.

[gregorwolf]

Any chance that you could get a system that runs: ABAP Cloud Developer Trial 2022

[RixarSAP]

@gregorwolf @sandraros
Created a fork https://github.com/RixarSAP/abap2xlsx

And a pull request:
#1219

First time doing it, I hope its created ok.
Comment any issue or problem.
THanks