2013 Aug 27 7:35 AM
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
2013 Aug 27 5: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:
2013 Aug 27 3: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!
2013 Aug 28 1:47 PM
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.
2013 Aug 29 2:34 PM
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.
2013 Aug 27 5: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:
2013 Aug 28 1:44 PM
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
2013 Aug 28 1:57 PM
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
2013 Aug 28 2:40 PM
Hi ,
The picture is of an attachment named: "attachment HTM (htm)"
Regards.
2013 Aug 29 5:33 AM
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
2013 Aug 29 6:29 AM
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 .
2013 Aug 28 1: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.
2013 Aug 29 5:42 AM
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
2013 Sep 19 10:32 AM
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.
2013 Sep 24 11: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