Excel formating in email attachment without using XML.

[former_member867213]

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

[former_member201275]

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

[former_member867213]

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.

[former_member201275]

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.

[rosenberg_eitan]

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:

[former_member867213]

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

[former_member182379]

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

[rosenberg_eitan]

Hi ,

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

Regards.

[former_member867213]

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

[rosenberg_eitan]

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 .

[Former Member]

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.

[former_member867213]

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

[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.

[former_member864989]

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