Skip to Content
avatar image
Former Member

Excel formating in email attachment without using XML.

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

4 Answers

  • Best Answer
    Aug 27, 2013 at 04:17 PM

    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:


    Add comment
    10|10000 characters needed characters exceeded

  • Aug 27, 2013 at 02:50 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 24, 2013 at 10:38 AM

    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


    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 28, 2013 at 12:58 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      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.