topic Excel formating in email attachment without using XML. in Application Development and Automation Discussions

Excel formating in email attachment without using XML.

former_member867213 — Tue, 27 Aug 2013 06:35:27 GMT

Hello Eperts,

I have a requirement where in I am sending an excel as an attachment, now the requirement is to format this excel.

Scenario: An email is to be sent with attached excel, the column headings should be in BOLD, the columns should be emphasized/optimized,

few of the columns are to be colored and so on...

Please do suggest an approach to ahcieve this, I am using below code...

TRY.
      cl_bcs_convert=>string_to_solix(
        EXPORTING
          iv_string   = lv_string
          iv_codepage = '4103' "suitable for MS Excel, leave empty
          iv_add_bom = 'X'     "for other doc types
        IMPORTING
          et_solix = binary_content
          ev_size   = size ).
    CATCH cx_bcs.
      MESSAGE e445(so).
ENDTRY.

*     add the spread sheet as attachment to document object
TRY.
      go_email_body->add_attachment(
           i_attachment_type    = 'xls'                     "#EC NOTEXT
           i_attachment_subject = 'Process' "#EC NOTEXT
           i_attachment_size    = size
           i_att_content_hex    = binary_content ).
    CATCH cx_document_bcs.
      MESSAGE e898(so).
ENDTRY.

I have tried to achieve it using FM: SO_NEW_DOCUMENT_ATT_SEND_API1

Note: Please don't provide links which are saying XML to excel conversion, there is an issue with this.

Warm Regards,

Aabid Khan

Re: Excel formating in email attachment without using XML.

former_member201275 — Tue, 27 Aug 2013 14:50:19 GMT

If I were you I would have a look at the ABAP2XLSX project here on SCN code Exchange. You can do all this and more with this code... it is amazing!

https://cw.sdn.sap.com/cw/groups/abap2xlsx

Re: Excel formating in email attachment without using XML.

rosenberg_eitan — Tue, 27 Aug 2013 16:17:46 GMT

Hi,

I am uploading a program that use cl_bcs ro send html mail .

Your user can copy paste the html to excel with all the html attributes.

Regards.

Sample output:

Re: Excel formating in email attachment without using XML.

former_member867213 — Wed, 28 Aug 2013 12:44:47 GMT

Thanks Rosenberg,

I want these kind of fomatings in excel which would go as attachment, your code is dealing with mail body.

Regards,

Aabid Khan

Re: Excel formating in email attachment without using XML.

former_member867213 — Wed, 28 Aug 2013 12:47:42 GMT

Thanks Anthony,

Putting myself in your shoes, I tried accesing your link, though I accepted terms of use I could not get what is required of that link.

Re: Excel formating in email attachment without using XML.

former_member182379 — Wed, 28 Aug 2013 12:57:28 GMT

Hi,

As your requirement is ,formated excel without XML, if you are sending the email in forground mode you can use OLE excel concept, in that first created your formated excel file in your temp folder with dynamic file name as per your requirement set visible property as 0 of OLE excelsheet , so when your are executing the report excel file will not be directly open or visible, then after saving your file in temp folder , then write the logic to upload that file and send as attachment.

without OLE or XML you cannot make the formated excel , normal excel file can be made easily.

Regards,

Zafar

Re: Excel formating in email attachment without using XML.

Former Member — Wed, 28 Aug 2013 12:58:59 GMT

Hi Aabid,

you can go for CALL TRANSFORMATION and create desired formatted excel. Then using XML to excel conversion you can use. Please let me know if you have any issue in this so that i can check from my end.

Thanks.

Re: Excel formating in email attachment without using XML.

rosenberg_eitan — Wed, 28 Aug 2013 13:40:38 GMT

Hi ,

The picture is of an attachment named: "attachment HTM (htm)"

Regards.

Re: Excel formating in email attachment without using XML.

former_member867213 — Thu, 29 Aug 2013 04:33:02 GMT

Thanks Dear Rosenberg,

Your code is perfect for htm formating, but when I see your CSV files these are not formatted can you please tell me about formating the excel attachments.

Regards,

Aabid Khan

Re: Excel formating in email attachment without using XML.

former_member867213 — Thu, 29 Aug 2013 04:42:48 GMT

Bro Fasiullah,

I did that thats why I mentioned without using XML because when you do it with XML the size of attachment gets drastcally increased and creates a problem in sending, if you can suggest me an approach which will keep the size same I will adopt that.

Thanks,

Aabid Khan

Re: Excel formating in email attachment without using XML.

rosenberg_eitan — Thu, 29 Aug 2013 05:29:30 GMT

Hi,

CSV is a plain text type so no formatting is available.

I guess you upload y_r_eitan_test_10_02 to your system.

Try to save attachment "attachment HTM (htm)" to your desktop and then right click and open with excel .

I know this is not perfect but it is a very good cost effective solution.

I have done some private testing with java using "Apache POI" (http://poi.apache.org/) but too many things can go wrong so

I prefer using "KISS" principle.(http://en.wikipedia.org/wiki/KISS_principle)

Regards .

Re: Excel formating in email attachment without using XML.

former_member201275 — Thu, 29 Aug 2013 13:34:42 GMT

Well if you go to the code exchange

https://cw.sdn.sap.com/cw/groups/abap2xlsx

and click on 'existing projects' near the bottom of the page, it takes u to:

https://cw.sdn.sap.com/cw/codex/projects

the project at the top of the list is 'abap2xlsx'

You have then various tabs which contain the source code and files you have to download etc., and all instructions, also installation guide.

It is simpler to use than ole route, in my opinion.

Re: Excel formating in email attachment without using XML.

Former Member — Thu, 19 Sep 2013 09:32:21 GMT

I am not sure I am fully getting what is happening here. My first question is where is the excel file coming from? Is it uploaded by the user and then emailed? Is it generated by SAP and emailed? Is it standard SAP generating the excel?

There are also very easy ways to format excel documents. You will need to write code to read the excel file and then format. I have given an example below which could be adapted to your needs. You could add the following code to set font / colour styling of individual cells.

SET PROPERTY OF cells 'Bold' = lc_bold.

SET PROPERTY OF cells 'Font' = lc_font.

Hope this helps!

Writing an excel file

METHOD output_excel.

INCLUDE ole2incl. " Contains classes we need for excel spreadsheet creation.

FIELD-SYMBOLS: <fs_structure> TYPE any,

<fs_comp> TYPE abap_compdescr,

<fs_field> TYPE any.

DATA: lcl_application TYPE ole2_object, " Excel application class

lcl_workbook TYPE ole2_object, " Workbook class

lcl_sheet TYPE ole2_object, " worksheet class

lcl_cells TYPE ole2_object, " Cells class

lv_structure_row TYPE sy-tabix VALUE 1,

lv_component_row TYPE sy-tabix VALUE 2,

lv_value_row TYPE sy-tabix VALUE 3, " local variable to track current row being processed in spreadsheet.

lv_col TYPE sy-tabix, " local variable to track current row being processed in spreadsheet.

lcl_file_contents TYPE REF TO zcl_tree_structure, " Local file contents currently being processed.

lt_file_contents TYPE ztree_structure_tt,

ls_file_contents TYPE ztree_structure,

lv_ref TYPE REF TO data, " Dynamic data object

lcl_descr_ref TYPE REF TO cl_abap_structdescr,

lcl_tree_structure TYPE REF TO zcl_tree_structure,

lv_field TYPE ecm_tabfld.

CONSTANTS: row_max TYPE i VALUE 256. " Max amount of rows we are allowing to write.

* Create instance of our excel application class.

IF lcl_application IS INITIAL.

CREATE OBJECT lcl_application 'excel.application'.

ENDIF.

* Allow the excel application to be visible in the foreground for data population.

SET PROPERTY OF lcl_application 'visible' = 1.

* Generate required instance of workbook.

CALL METHOD OF

lcl_application

'Workbooks' = lcl_workbook.

* Prepare to add a worksheet

CALL METHOD OF

lcl_workbook

'Add'.

* Create the new worksheet

CALL METHOD OF

lcl_application

'Worksheets' = lcl_sheet

EXPORTING

#1 = 1.

* Activate the worksheet and name it.

CALL METHOD OF

lcl_sheet

'Activate'.

SET PROPERTY OF lcl_sheet 'Name' = worksheet_name.

* --------------------------------------------------------------------------------------------------------------------------------------

* Begin populating the worksheet. Start by writing the generic parts of the header

* --------------------------------------------------------------------------------------------------------------------------------------

* Get objects

IF lcl_tree_structure IS INITIAL.

CREATE OBJECT lcl_tree_structure.

ENDIF.

lcl_tree_structure = object.

CALL METHOD lcl_tree_structure->get_tree_structure

RECEIVING

object = lt_file_contents.

LOOP AT lt_file_contents INTO ls_file_contents.

CREATE DATA lv_ref TYPE (ls_file_contents-structure_name).

ASSIGN lv_ref->* TO <fs_structure>.

* Deseralise the object - depending on structure

CALL TRANSFORMATION id

SOURCE XML ls_file_contents-object

RESULT para = <fs_structure>.

* Loop through components, assiging actual value

lcl_descr_ref ?= cl_abap_typedescr=>describe_by_data( <fs_structure> ).

LOOP AT lcl_descr_ref->components ASSIGNING <fs_comp>.

CALL METHOD OF

lcl_sheet

'Cells' = lcl_cells

EXPORTING

#1 = lv_structure_row

#2 = lv_col.

SET PROPERTY OF lcl_cells 'Value' = ls_file_contents-structure_name.

CALL METHOD OF

lcl_sheet

'Cells' = lcl_cells

EXPORTING

#1 = lv_component_row

#2 = lv_col.

SET PROPERTY OF lcl_cells 'Value' = <fs_comp>-name.

CONCATENATE '<fs_structure>' '-' <fs_comp>-name INTO lv_field.

ASSIGN (lv_field) TO <fs_field>.

CALL METHOD OF

lcl_sheet

'Cells' = lcl_cells

EXPORTING

#1 = lv_value_row

#2 = lv_col.

SET PROPERTY OF lcl_cells 'Value' = <fs_field>.

lv_col = lv_col + 1.

ENDLOOP.

lv_structure_row = lv_structure_row + 3.

lv_component_row = lv_component_row + 3.

lv_value_row = lv_value_row + 3.

lv_col = 1.

ENDLOOP.

* --------------------------------------------------------------------------------------------------------------------------------------

* finished writing spreadsheet values

* --------------------------------------------------------------------------------------------------------------------------------------

* Save excel speadsheet with a given file name

CALL METHOD OF

lcl_sheet

'SaveAs'

EXPORTING

#1 = file " file name

#2 = 1. " format (.xls)

* Close workbook and quit application.

CALL METHOD OF

lcl_workbook

'Close'.

CALL METHOD OF

lcl_application

'Quit'.

* clean up objects.

FREE OBJECT lcl_application.

FREE OBJECT lcl_workbook.

FREE OBJECT lcl_sheet.

FREE OBJECT lcl_cells.

ENDMETHOD.

Re: Excel formating in email attachment without using XML.

former_member864989 — Tue, 24 Sep 2013 10:38:30 GMT

Hi Aabid,

You can use class CL_IXML for this. It is using XML to create excel. i dont think so it will create more size. let me know limitation size while sending mail.

Please find file in the attachment. it may help you.

Regards,

Lokesh