topic Abap: How to allign spreadsheet in Application Development and Automation Discussions

Abap: How to allign spreadsheet

former_member290783 — Fri, 06 Dec 2019 04:07:34 GMT

Hi All,

I have a query. I have a report where a Email is sent along with an spreadsheet attachment.

Question here is when I open the spreadsheet from mail inbox,the alignment of the spreadsheet is not proper also I need borders with correct font size. Can this be done.If so can you please tell me how.

I know how to generate a new spreadsheet using OLE.But changing the spreadsheet during attachment not sure how to do it. Thanks.

Not Aligned:

Aligned

Re: Abap: How to allign spreadsheet

ThangaPrakash — Fri, 06 Dec 2019 04:19:22 GMT

Premjit Ger

It can be set using 'ColumnWidth' parameter.

Refer to below sample code which I have used in one of custom report. where I have set column width to 30

FORM fill_cell_value USING iv_row TYPE syst_tabix
                           iv_column TYPE syst_tabix
                           iv_value TYPE any.

  DATA:lo_cell     TYPE ole2_object,
       lo_interior TYPE ole2_object,
       lo_font     TYPE ole2_object.

  CALL METHOD OF go_excel 'Cells' = lo_cell EXPORTING #1 = iv_row #2 = iv_column.
  SET PROPERTY OF lo_cell 'Value' = iv_value.
  SET PROPERTY OF lo_cell 'ColumnWidth' = 30.

  IF iv_row EQ 6 AND iv_value EQ 'Mandatory'.
    GET PROPERTY OF lo_cell 'Font' = lo_font.
    SET PROPERTY OF lo_font 'ColorIndex' = 3.
  ENDIF.

  CHECK iv_column LE 3.
  GET PROPERTY OF lo_cell 'Interior' = lo_interior.
  SET PROPERTY OF lo_interior 'ColorIndex' = 43.

ENDFORM.

Re: Abap: How to allign spreadsheet

former_member290783 — Fri, 06 Dec 2019 05:11:39 GMT

Hi,

Thanks.If possible can you send me your code,so that it would be easy for me to compare.

Re: Abap: How to allign spreadsheet

Sandra_Rossi — Fri, 06 Dec 2019 16:43:47 GMT

Please use the COMMENT button for comments, questions, adding details, etc., ANSWER is only to propose a solution, dixit SAP text at the right of the answer area. At the same time, the person will be informed of your comment (currently not).

Re: Abap: How to allign spreadsheet

Sandra_Rossi — Fri, 06 Dec 2019 16:55:10 GMT

You have many questions:

Borders
Font size
"alignment"? (column width?)
(and background color maybe?)
Change the spreadsheet during attachment to email

The questions about the formatting are very simple to manage by yourself, use Excel VBA recorder, and translate manually the recorded VBA code into ABAP (cf my answer and comments here: https://answers.sap.com/questions/287201/como-eliminar-filas-vacias-en-ole-abap.html).

I don't understand what is the issue about changing the spreadsheet during attachment to email, which in fact can be summarized to just changing the spreadsheet (I don't see what you want to do "during attachment to email", it's too late...) With OLE you can open an Excel file, change it and save it. What issue do you have?

BUT

why don't you use abap2xlsx instead of OLE?

Re: Abap: How to allign spreadsheet

ThangaPrakash — Fri, 06 Dec 2019 18:38:54 GMT

Premjit Ger

* Create OLE Excel application
  DATA:lo_mapl      TYPE ole2_object,
       lo_map       TYPE ole2_object,
       lo_worksheet TYPE ole2_object.

  CREATE OBJECT go_excel 'EXCEL.APPLICATION'.
  CALL METHOD OF go_excel 'Workbooks' = lo_mapl.
  SET PROPERTY OF go_excel 'Visible' = 1.
  CALL METHOD OF lo_mapl 'Add' = lo_map.
  GET PROPERTY OF go_excel 'ACTIVESHEET' = lo_worksheet.
  SET PROPERTY OF lo_worksheet 'Name' = 'First Sheet'.

*Then fill the value of every cell by using VALUE property and width by using 'ColumnWidth' property
  CALL METHOD OF go_excel 'Cells' = lo_cell EXPORTING #1 = iv_row #2 = iv_column.
  SET PROPERTY OF lo_cell 'Value' = iv_value.
  SET PROPERTY OF lo_cell 'ColumnWidth' = 30.

Re: Abap: How to allign spreadsheet

former_member290783 — Mon, 09 Dec 2019 03:20:05 GMT

Thank you all...It worked.