How to Generate a formatted excel in background and send it as an email using ixml method

[JabinGGeorge]

I was working in one Project and there came a requirement that if a customer process any output form , the output should be displayed as formatted excel and if given a print it should send the excel as email in background.

Now we had traditional ways of doing in SAP

1. 1. 1. Without any formatting you could get all the data into an internal table and create an excel.This could be attained by use of this function module . The limitation here is we can’t format make any columns bold or can’t colour the data. If we want to just populate some data in rows and column and want to send it as an email ,we could achieve this easily.
2. 2. 2. Second way of a formatted excel is using OLE method ( Object link Enable ) method in which we have all the functions to create a formatted excel with colors and bold orders. But the limitation here is we have to generate the output in foreground ( presentation server ) , it takes time as contents gets created at your presentation server. So the limitations are no background processing and performance issue as time take in dyamic content creation

Now I had a challenge that what could be the best way to generate a formatted excel and also process the output in background and send it as excel attachment to the desired recipients.

So I had digged out here and there and found that SAP has added the concept of ‘IXML’ in 2010 in their library functions and we could actually generate the formatted excel and also send as an email attachment. Infact SCN was very helpful I could find the generation of excel , however I din’t find a good source code which process the IXML data in background and send as an email attachment.

I hope it would be good for all those who are looking to implement such functionality .

Please let me know your feedback , suggestions and query further to this.

**** Decalaration Please go through the text file for decalartion part *******

*** When ever you write logic for an output form you have to define the entry node , when it is called from NACE.

Get all the data you require based on your functional requirements to process the data

** GT_FINAL is my internal table , where I had all the data which has to populated in my output. There are around 18 columns ( Fields ) in which data is being copied.

** Get file path is when you want the user an option to see the output/save the output in their desktop/presentation server you have to specify where you want to place the file in their system. So we use the below function module **

** Distributing the whole program in a modular way , calling the subroutines for specific functionality , as we do in conventional way.**

*&---------------------------------------------------------------------*
*& Form PROCESSING
*&---------------------------------------------------------------------*

FORM processing.
PERFORM get_data. "

*** PROCESS XML DATA IS THE MOST IMPORTANT SUB ROUTINE WHICH TELL US HOW WE CAN USE OUR INTERNAL TABLE DATA , FORMAT IT and GENERATE THE FORMATTED EXCEl ***

PERFORM process_xml_data. " Create excel through  xml code for sending as an attachment
PERFORM get_file_path. " Get the file path
PERFORM download_excel. “Download the processed excel in desktop

*** HOW TO PROCESS THE FORMATTED EXCEL IN BACKGROUND and SEND as an EXCEL ATTACHMENT ***

PERFORM send_mail1. “Sending the excel output via mail

ENDFORM. " PROCESSING

*&---------------------------------------------------------------------*
*& Form PROCESS_XML_DATA
*&---------------------------------------------------------------------*
* Process the xml data to create an excel
*----------------------------------------------------------------------*
FORM process_xml_data .

* Creating a ixml Factory
g_ixml = cl_ixml=>create( ).

* Creating the DOM Object Model
g_document = g_ixml->create_document( ).

*** Little help on the IXML Hierarchy ****

** Create a workbook **

**Set attributes for the workbook **

** Create Node for document **

** We need to attach a style to every node **

** Attributes of style would be setting up the alignment , font , bold , color etc ***

** Style Syntax : ID and give name , create different styles if properties are different for different rows **

** For every cell of excel you need to specify four borders , right , left , bottom and top **

** Create a table rows * Columns **

** Create Rows , attach the style to the row and define the properties **

** Similarly do for column **

** All syntax given in the document below **

* Create Root Node 'Workbook'
g_element_root  = g_document->create_simple_element( name = 'Workbook'  parent = g_document ).
g_element_root->set_attribute( name = 'xmlns'  value = 'urn:schemas-microsoft-com:office:spreadsheet' ).

g_ns_attribute = g_document->create_namespace_decl( name = 'ss'  prefix = 'xmlns'  uri = 'urn:schemas-microsoft-com:office:spreadsheet' ).
g_element_root->set_attribute_node( g_ns_attribute ).   

g_ns_attribute = g_document->create_namespace_decl( name = 'x'  prefix = 'xmlns'  uri = 'urn:schemas-microsoft-com:office:excel' ).
g_element_root->set_attribute_node( g_ns_attribute ).   

* Create node for document properties.
r_element_properties = g_document->create_simple_element( name = 'TEST_REPORT'  parent = g_element_root ).
g_value = sy-uname.
g_document->create_simple_element( name = 'Author'  value = g_value  parent = r_element_properties  ).

* Styles
r_styles = g_document->create_simple_element( name = 'Styles'  parent = g_element_root  ).

* Style for Header
r_style  = g_document->create_simple_element( name = 'Style'  parent = r_styles  ).
r_style->set_attribute_ns( name = 'ID'  prefix = 'ss'  value = 'Header' ).

r_format  = g_document->create_simple_element( name = 'Font'  parent = r_style  ).
r_format->set_attribute_ns( name = 'Bold'  prefix = 'ss'  value = '1' ).

r_format  = g_document->create_simple_element( name = 'Interior' parent = r_style  ).
r_format->set_attribute_ns( name = 'Color'  prefix = 'ss'  value = '#FFFFFF' ).
r_format->set_attribute_ns( name = 'Pattern' prefix = 'ss'  value = 'Solid' ).

r_format  = g_document->create_simple_element( name = 'Alignment'  parent = r_style  ).
r_format->set_attribute_ns( name = 'Vertical'  prefix = 'ss'  value = 'Center' ).
r_format->set_attribute_ns( name = 'WrapText'  prefix = 'ss'  value = '1' ).

r_border  = g_document->create_simple_element( name = 'Borders'  parent = r_style ).
r_format  = g_document->create_simple_element( name = 'Border'  parent = r_border  ).
r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Bottom' ).
r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

r_style  = g_document->create_simple_element( name = 'Style'  parent = r_styles  ).
r_style->set_attribute_ns( name = 'ID'  prefix = 'ss'  value = 'Data' ).


** Create a new style for Delivery Number
r_style5  = g_document->create_simple_element( name = 'Style'  parent = r_styles  ).
r_style5->set_attribute_ns( name = 'ID'  prefix = 'ss'  value = 'Data5' ).

r_format  = g_document->create_simple_element( name = 'Font'  parent = r_style5  ).
r_format->set_attribute_ns( name = 'Bold'  prefix = 'ss'  value = '1' ).

r_format  = g_document->create_simple_element( name = 'Interior' parent = r_style5  ).
r_format->set_attribute_ns( name = 'Color'  prefix = 'ss'  value = '#1E90FF' ).
r_format->set_attribute_ns( name = 'Pattern' prefix = 'ss'  value = 'Solid' ).

r_format  = g_document->create_simple_element( name = 'Alignment'  parent = r_style5  ).
r_format->set_attribute_ns( name = 'Vertical'  prefix = 'ss'  value = 'Center' ).
r_format->set_attribute_ns( name = 'WrapText'  prefix = 'ss'  value = '1' ).
r_border  = g_document->create_simple_element( name = 'Borders'  parent = r_style5 ).

*  * Border Bottom

r_format  = g_document->create_simple_element( name = 'Border'  parent = r_border  ).
r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Bottom' ).
r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

**  Create Borders Bottom Left , top , right in the same way ***

r_format  = g_document->create_simple_element( name = 'Border'  parent = r_border  ).
r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Left' ).
r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

* * Top
r_format  = g_document->create_simple_element( name = 'Border'  parent = r_border  ).
r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Top' ).
r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1').


** Right

r_format  = g_document->create_simple_element( name = 'Border'  parent = r_border  ).
r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Right' ).
r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

**** STYLE CREATION OVER , Different styles created which we will use belwo ***

** Creating borders for the cell
r_border  = g_document->create_simple_element( name = 'Borders'  parent = r_style1 ).
r_format  = g_document->create_simple_element( name = 'Border'  parent = r_border  ).
r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Bottom' ).
r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

r_format  = g_document->create_simple_element( name = 'Border'  parent = r_border  ).
r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Left' ).
r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

r_format  = g_document->create_simple_element( name = 'Border'  parent = r_border  ).
r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Top' ).
r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).

r_format  = g_document->create_simple_element( name = 'Border'  parent = r_border  ).
r_format->set_attribute_ns( name = 'Position'  prefix = 'ss'  value = 'Right' ).
r_format->set_attribute_ns( name = 'LineStyle'  prefix = 'ss'  value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight'  prefix = 'ss'  value = '1' ).


* Worksheet
r_worksheet = g_document->create_simple_element( name = 'Worksheet'  parent = g_element_root ).
r_worksheet->set_attribute_ns( name = 'Name'  prefix = 'ss'  value = 'Proforma Confirmation' ).  " Add the worksheet name (ERDK954626)

*** Define the table , Rows * Coumns. This would automatically created based on the data in our internal table ***

* Table
r_table = g_document->create_simple_element( name = 'Table'  parent = r_worksheet ).  " Set Table properties
r_table->set_attribute_ns( name = 'FullColumns'  prefix = 'x'  value = '1' ).
r_table->set_attribute_ns( name = 'FullRows'    prefix = 'x'  value = '1' ).

* Column Formatting
r_column = g_document->create_simple_element( name = 'Column'  parent = r_table ).
r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '220' ).

r_column = g_document->create_simple_element( name = 'Column'  parent = r_table ).
r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '80' ).


** Repeat based on how many columns in you want in your output excel **

** CREATING ROWS **

* Blank Row
r_row = g_document->create_simple_element( name = 'Row'  parent = r_table ).
r_row = g_document->create_simple_element( name = 'Row'  parent = r_table ).
r_row = g_document->create_simple_element( name = 'Row'  parent = r_table ).

r_cell = g_document->create_simple_element( name = 'Cell'  parent = r_row ).
r_cell->set_attribute_ns( name = 'Index'  prefix = 'ss'  value = '1' ).
r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data5' ).
WRITE g_delivno TO g_delivno NO-ZERO. " removing zeroes from delivery number
CONCATENATE 'ТОВАРНАЯ НАКЛАДНАЯ №'(050) g_delivno INTO g_value SEPARATED BY ' : '.
r_data = g_document->create_simple_element( name = 'Data'  value = g_value  parent = r_cell ).
r_data->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

r_row = g_document->create_simple_element( name = 'Row'  parent = r_table ).
r_row = g_document->create_simple_element( name = 'Row'  parent = r_table ).

** Blank Row after Column Headers
r_row = g_document->create_simple_element( name = 'Row'  parent = r_table ).
r_row->set_attribute_ns( name = 'Height'  prefix = 'ss'  value = '40' ).

* Column Headers Row

** First Column - Material
r_cell = g_document->create_simple_element( name = 'Cell'  parent = r_row ).
r_cell->set_attribute_ns( name = 'Index'  prefix = 'ss'  value = '1' ).
r_cell->set_attribute_ns( name = 'MergeDown'  prefix = 'ss'  value = '1' ).
r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data3' ).
r_data = g_document->create_simple_element( name = 'Data'  value = ' Товар, наименование, характеристика, сорт, артикул товара '  parent = r_cell ).
r_data->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

*e = 'String' ).


** Attribute for filling the cell numbers in a row
** Printing the column numbers in the excel sent via email

r_row = g_document->create_simple_element( name = 'Row'  parent = r_table ).
r_row->set_attribute_ns( name = 'Height'  prefix = 'ss'  value = '20' ).

r_cell = g_document->create_simple_element( name = 'Cell'  parent = r_row ).
r_cell->set_attribute_ns( name = 'Index'  prefix = 'ss'  value = '1' ).
r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data3' ).
r_data = g_document->create_simple_element( name = 'Data'  value = '1'  parent = r_cell ).
r_data->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).


** Loop and pass the contents to this excel ***

* Data Table

LOOP AT gt_final INTO gst_final.
** Creation of a row for data
r_row = g_document->create_simple_element( name = 'Row'  parent = r_table ).
r_row->set_attribute_ns( name = 'Height'  prefix = 'ss'  value = '30' ).

* Material value.
r_cell = g_document->create_simple_element( name = 'Cell'  parent = r_row ).
r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data4' ).
g_value = gst_final-matnr.
r_data = g_document->create_simple_element( name = 'Data'  value = g_value  parent = r_cell ). " Data
r_data->set_attribute_ns( name = 'Type'  prefix = 'ss'  value = 'String' ). " Cell format

x = 'ss'  value = 'String' ).

*** Repeat the same for all values you want to pass **

ENDLOOP.

* Creating a Stream Factory
g_streamfactory = g_ixml->create_stream_factory( ).     

* Connect Internal XML Table to Stream Factory
g_ostream = g_streamfactory->create_ostream_itable( table = g_xml_table ).

* Rendering the Document
g_renderer = g_ixml->create_renderer( ostream  = g_ostream document = g_document ).
g_rc = g_renderer->render( ).

* Saving the XML Document
g_xml_size = g_ostream->get_num_written_raw( ).         

** Moving the ixml data into an internal table **

** Pass the processed xml data to table for download
LOOP AT g_xml_table INTO gst_xml.
CLEAR gt_objbin.
gt_objbin-line = gst_xml-data.
APPEND gt_objbin.
ENDLOOP.

ENDFORM. " PROCESS_XML_DATA

SENDING EMAIL in BACKGROUND the formatted EXCEL

*&---------------------------------------------------------------------*
*& Form  SEND_MAIL1
*&---------------------------------------------------------------------*
FORM send_mail1 .

DATA: l_objpack  LIKE sopcklsti1 OCCURS 2 WITH HEADER LINE.
DATA: l_objhead  LIKE solisti1 OCCURS 1 WITH HEADER LINE.
DATA: l_objbin    LIKE solix OCCURS 10 WITH HEADER LINE.
DATA: l_objtxt    LIKE solisti1 OCCURS 10 WITH HEADER LINE.
DATA: l_reclist  LIKE somlreci1 OCCURS 5 WITH HEADER LINE.
DATA: l_doc_chng  LIKE sodocchgi1.
DATA: l_tab_lines LIKE sy-tabix.
DATA: l_num(3).
DATA: l_subj_date(10) TYPE c.

* Mail Subject

CLEAR l_doc_chng-obj_descr.
WRITE g_delivno TO g_delivno NO-ZERO.
CONCATENATE 'Подтверждение отгрузки Hasbro: ТОРГ-12 №'(025) g_delivno INTO l_doc_chng-obj_descr SEPARATED BY space.

* Mail Contents
l_objtxt = 'Уважаемый Клиент,'(026).
APPEND l_objtxt.

CLEAR l_objtxt.
APPEND l_objtxt.

WRITE g_delivno TO g_delivno NO-ZERO.
CONCATENATE 'В приложении Подтверждение об отгрузке Вашего заказа №'(027)'' INTO l_objtxt SEPARATED BY space. " Mail Contents
APPEND l_objtxt.


CONCATENATE gw_vbkd-bstkd '.' INTO l_objtxt. " Added in  Customer PO in 2nd Line
APPEND l_objtxt.

CONCATENATE 'ТОРГ-12 № '(051) g_delivno INTO l_objtxt SEPARATED BY space. " Added Hasbro Deliv no in 3rd Line
APPEND l_objtxt.

CLEAR l_objtxt.
APPEND l_objtxt.

CLEAR l_objtxt.
APPEND l_objtxt.

CLEAR l_objtxt.
APPEND l_objtxt.

l_objtxt = 'С уважением и благодарностью,'(028).
APPEND l_objtxt.

l_objtxt = 'от лица ООО «Хасбро Раша»'(052). ":sy-uname.
APPEND l_objtxt.


CLEAR l_objtxt.
APPEND l_objtxt.

l_objtxt = 'Сообщение было сформировано и отправлено автоматически, пожалуйста, не отвечайте на него.'(054) . "  ERDK954925
APPEND l_objtxt.

DESCRIBE TABLE l_objtxt LINES l_tab_lines.
READ TABLE l_objtxt INDEX l_tab_lines.
l_doc_chng-doc_size = ( l_tab_lines - 1 ) * 255 + STRLEN( l_objtxt ).

* Packing List For the E-mail Body
l_objpack-head_start = 1.
l_objpack-head_num  = 0.
l_objpack-body_start = 1.
l_objpack-body_num  = l_tab_lines.
l_objpack-doc_type  = 'RAW'.
APPEND l_objpack.

* Creation of the Document Attachment
LOOP AT g_xml_table INTO gst_xml1.
CLEAR l_objbin.
l_objbin-line = gst_xml1-data.
APPEND l_objbin.
ENDLOOP.

DESCRIBE TABLE l_objbin LINES l_tab_lines.
l_objhead = 'Proforma Confirmation '.
APPEND l_objhead.

* Packing List For the E-mail Attachment
l_objpack-transf_bin = 'X'.
l_objpack-head_start = 1.
l_objpack-head_num  = 0.
l_objpack-body_start = 1.
l_objpack-body_num = l_tab_lines.


** Create attachment name always as the previous condition fails in case of multiple deliveries
PERFORM create_attachment_name. “ Name of the worksheet what you are trying to create **


*  CONCATENATE 'Proforma Confirmation for Delivery '(033) l_subj_date INTO l_objpack-obj_descr SEPARATED BY space.
l_objpack-obj_descr = g_excel_file1.
l_objpack-doc_type = 'XLS'.
l_objpack-doc_size = l_tab_lines * 255.
APPEND l_objpack.

* Target Recipent

IF gt_email[] IS NOT INITIAL.

LOOP AT gt_email INTO gst_email. "  changed the internal table for multiple emails's
CLEAR l_reclist.
IF gst_email-smtp_addr IS NOT INITIAL.    "  pass the email address only to work area
* create recipient object
l_reclist-receiver = gst_email-smtp_addr . " Appending the email address
l_reclist-rec_type = 'U'.
APPEND l_reclist.
ENDIF.
CLEAR gst_email. "  Added a clear statement for the new work area
ENDLOOP.
ENDIF.

*** This is the FM which faciiates the data and send it to targeted recipients **

* Sending the document
CALL FUNCTION 'SO_NEW_DOCUMENT_ATT_SEND_API1'
EXPORTING
document_data              = l_doc_chng
put_in_outbox              = 'X'
TABLES
packing_list              = l_objpack
object_header              = l_objhead
contents_txt              = l_objtxt
contents_hex              = l_objbin
receivers                  = l_reclist
EXCEPTIONS
too_many_receivers        = 1
document_not_sent          = 2
operation_no_authorization = 4
OTHERS = 99.

ENDFORM. " SEND_MAIL1

Output :

[paolo_sartor2]

On your example, I've created a function to generate the excel form in XML format.

Its use is simple and the realization of the report is very fast.

I enclose:

• A sample report
• The excel product module (.xml)
• The excel product module (XLS extension). it is the same as before
• The function module to generate the excel form
• The function module to send mail

For sending the mail you can also use the standard function module SO_NEW_DOCUMENT_ATT_SEND_API1

Thanks to all the people who will be using my programs

http://www.informatica2005.it/Documenti/SAP_Excel_xml/z_ut_crea_excel_xml.zip

[JabinGGeorge]

Thanks Paolo , Appreciate you for awesome work !! :smile:

[paolo_sartor2]

If you like you can set of LEDs on the excel sheet created.

  

    CASE w_msgtype.

      WHEN 'I' OR 'S'.

* Green led

        CLEAR s_excel.

        MOVE 1          TO s_excel-sheet_nro.

        MOVE w_row      TO s_excel-row_nro.

        MOVE 7          TO s_excel-column_nro.

        MOVE 'n'        TO s_excel-value.

        MOVE 'Webdings' TO s_excel-fontname.

        MOVE '99CC00'   TO s_excel-char_color.  “Green

        APPEND s_excel  TO it_excel.

      WHEN 'W'.

* Yellow led

        CLEAR s_excel.

        MOVE 1          TO s_excel-sheet_nro.

        MOVE w_row      TO s_excel-row_nro.

        MOVE 7          TO s_excel-column_nro.

        MOVE 'n'        TO s_excel-value.

        MOVE 'Webdings' TO s_excel-fontname.

        MOVE 'FFFF00'   TO s_excel-char_color.  “Yellow

        APPEND s_excel  TO it_excel.

      WHEN OTHERS.

* Red led

        CLEAR s_excel.

        MOVE 1          TO s_excel-sheet_nro.

        MOVE w_row      TO s_excel-row_nro.

        MOVE 7          TO s_excel-column_nro.

        MOVE 'n'        TO s_excel-value.

        MOVE 'Webdings' TO s_excel-fontname.

        MOVE 'FF0000'   TO s_excel-char_color.   “Red

        APPEND s_excel  TO it_excel.

    ENDCASE.

[former_member196331]

Oh Great ..........

[JabinGGeorge]

Paolo Superlike (Y)....Great !!

Thank you for sharing this :smile:

[Former Member]

Hi Jabin,

 

Helpful blog .

Indeed i am looking for coloring the particular cell as you mentioned  above , passing values to it_excel .

Could you please help me how i can insert this cell coloring logic in the above code " In the loop gt_final , if i want to give back ground color " Green,red and yellow based message type in a particular cell .

 

 

Thanks and Regards,

Manohar.

[former_member574546]

Hi ,

In this method, i am getting large file size can you please help me to do zip file before sending email with piece of code.

regards,

Rajeshwari

[VenuMadhavan]

Hi Friend

 

Need your hep with your example

 

we were missing declaration part for below lines. can you please send me to MADHAV1979@gmail.com


DATA: it_excel TYPE ztt_excel_data,
* ztt_excel_data type table of ZTR_EXCEL_DATA
  s_excel  TYPE ztr_excel_data.

 

Thanks IN Advance,

Venu M B

[VenuMadhavan]

Hello Jabin

 

We were missing declaration part with this blog, can you please again. or email to madhav1979@gmail.com.

 

Thanks in Advance.

[former_member661707]

Hello Jabin,

Could you please also send me the missing declaration part of shown below on chetan.adhikari0808@gmail.com?

DATA: it_excel TYPE ztt_excel_data,
* ztt_excel_data type table of ZTR_EXCEL_DATA
  s_excel  TYPE ztr_excel_data.

Regards,

Chetan Adhikari

[former_member661707]

Hello Venu,

If you have received the missing data declarations, could you please send it out to me as well on chetan.adhikari0808@gmail.com?

Regards,

Chetan Adhikari

[former_member696995]

Hi,

Is it possible to make cell locking/Protected??

[paolo_sartor2]

Hello
The new version, available on my site, manages the cell locking and formula hidden.
Regards.

[paolo_sartor2]

CASE msgtype.
WHEN 'S'.  MOVE 'CCFFCC' TO s_excel-back_color.
WHEN 'W'. MOVE 'FFFF99'   TO s_excel-back_color.
WHEN 'E'.  MOVE 'FF9900'   TO s_excel-back_color.
WHEN OTHERS. CLEAR s_excel-back_color.
ENDCASE.

[former_member706942]

Thanks a lot  paolo.sartor2

 

[former_member706942]

Hi  paolo.sartor2

is it possible to add logo/image into excel using this concept?? please tell.

[paolo_sartor2]

No, I'm sorry.
In the excel xml format you can't insert images or logos.

[former_member706942]

Hi Paolo,

Thank you so much for info, I appreciate if you can help for this issue.

I am facing new issue with XML file generated as excel, with the extension as .xls excel is throwing pop-up with message' file may me unsafe /corrupted ' . If I proceed to open 'file opens without any issue' .

But is there any way to suppress this message while opening excel file (XLS) or how to change excel extension to XLSX. Because end users are afraid to open attachments that we sent.

Thanks

Pavan

[paolo_sartor2]

Hi Reddy.

I create my attachments with the extension .xml
In the file type association, the .xml program is associated with Microsoft Excel as with .xlsx.

I'm afraid I have not responded to you earlier, but I'm not getting alerts on questions

[mohammadaamir_khan]

Hi paolo.sartor2,

I tried your program, it is creating XML file only in Email not the Excel file. Is there some thing missing in link or i am missing some thing.

http://www.informatica2005.it/Documenti/SAP_Excel_xml/z_ut_crea_excel_xml.zip

Thanks

Aamir

[paolo_sartor2]

Hi Aamir.

I thank you for using my utility.
I invite you to take the version I just posted, it is the same with a few less bugs.

To download the output to a local PC file you can do this

CALL FUNCTION 'Z_UT_CREA_EXCEL_XML'
EXPORTING
* I_EXCEL_NAME = ''
* I_EXCEL_SHEET = I_EXCEL_SHEET
* I_EXCEL_COLUMN = I_EXCEL_COLUMN
* I_EXCEL_ROW = I_EXCEL_ROW
i_excel_data = it_excel
IMPORTING
e_doc_type = w_doc_type
e_xml_table = w_xml_table
e_xml_size = w_xml_size
e_rc = w_rc
EXCEPTIONS
posizione_mancante = 1
posizione_ripetuta = 2
OTHERS = 3
.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.

IF w_rc <> 0.
*_101_Errore chiamata FM & - rc = &
MESSAGE e101(zinsi) WITH 'Z_UT_CREA_EXCEL_XML' w_rc.
ENDIF.

CALL METHOD cl_gui_frontend_services=>file_save_dialog
EXPORTING
* multiselection = space
default_extension = 'xml'
CHANGING
* file_table = gt_filetab
path = w_path
filename = w_filename
fullpath = w_fullpath
* rc = gv_rc.
user_action = w_rc.

IF w_rc <> 0.
*_006_Azione interrotta
MESSAGE s006(zut).
EXIT.
ENDIF.

CALL METHOD cl_gui_frontend_services=>gui_download
EXPORTING
filename = w_fullpath
filetype = 'BIN'
bin_filesize = w_xml_size
* write_lf = ' '
* trunc_trailing_blanks_eol = ' '
show_transfer_status = ' '
* write_lf_after_last_line = ' '
CHANGING
data_tab = w_xml_table
EXCEPTIONS
file_write_error = 1
no_batch = 2
gui_refuse_filetransfer = 3
invalid_type = 4
no_authority = 5
unknown_error = 6
header_not_allowed = 7
separator_not_allowed = 8
filesize_not_allowed = 9
header_too_long = 10
dp_error_create = 11
dp_error_send = 12
dp_error_write = 13
unknown_dp_error = 14
access_denied = 15
dp_out_of_memory = 16
disk_full = 17
dp_timeout = 18
file_not_found = 19
dataprovider_exception = 20
control_flush_error = 21
not_supported_by_gui = 22
error_no_gui = 23
OTHERS = 24.
IF sy-subrc = 0.
MOVE w_fullpath TO m_file.
*_005_Modulo salvato in & & & &
MESSAGE s005(zut) WITH m_file(40) m_file+40(40) m_file+80(40) m_file+120(8).
ELSE.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.

 

[mohammadaamir_khan]

Hi paolo.sartor2,

Where is the new version available? is it available at the below link?

http://www.informatica2005.it/Documenti/SAP_Excel_xml/z_ut_crea_excel_xml.zip

Also, I needed XLS in the background so I changed below line in FUNCTION z_ut_crea_excel_xml

*  MOVE 'XML' TO e_doc_type.
MOVE 'XLS' TO e_doc_type.

It is generating EXCEL but when opening, it is giving warning, "The file format and Extension don't match".

How to avoid the above warning?

Thanks

Aamir

[paolo_sartor2]

Hi Aamir.

Yes, I have overwritten the link with the new version.

This works for me.
Since my server is AIX the transfer to local PC I do it in binary mode.

REPORT zpsartor_prova_xml.

* Preparazione email
DATA: it_excel TYPE ztt_excel_data,
s_excel TYPE ztr_excel_data.

DATA: w_border(4) TYPE c. "Bottom Left Right Top

DATA: w_doc_type TYPE char3,
w_rc TYPE i,
w_xml_table TYPE solix_tab,
w_xml_size TYPE i.

* convert from solix to xstring
DATA: w_xstring TYPE xstring.

DATA: c_dataset TYPE string VALUE '/FSSAPCNS/TEMP/Prova.xml'.

START-OF-SELECTION.

DEFINE cella. " Alimento una cella
clear s_excel.
move 1 to s_excel-sheet_nro.
move &1 to s_excel-row_nro.
move &2 to s_excel-column_nro.
move &3 to s_excel-value.
move &4 to s_excel-bold.
move &5 to s_excel-justify.
move &6 to s_excel-numberformat.
move &7 to w_border.
move w_border+0(1) to s_excel-border_top.
move w_border+1(1) to s_excel-border_right.
move w_border+2(1) to s_excel-border_bottom.
move w_border+3(1) to s_excel-border_left.
move &8 to s_excel-back_color.
move &9 to s_excel-char_color.
if s_excel-value = 'n'.
move 'Webdings' to s_excel-fontname.
endif.
append s_excel to it_excel.
END-OF-DEFINITION.

cella 1 1 sy-repid 'X' 'C' ' ' ' ' ' ' ' ' .
cella 1 2 sy-datum 'X' 'L' 'D' ' ' ' ' ' ' .
cella 1 3 sy-title 'X' ' ' ' ' ' ' ' ' ' ' .
cella 2 2 sy-uzeit 'X' 'L' 'T' ' ' ' ' ' ' .

CALL FUNCTION 'Z_UT_CREA_EXCEL_XML'
EXPORTING
i_excel_data = it_excel
IMPORTING
e_doc_type = w_doc_type
e_xml_table = w_xml_table
e_xml_size = w_xml_size
e_rc = w_rc
EXCEPTIONS
posizione_mancante = 1
posizione_ripetuta = 2
OTHERS = 3.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.

IF w_rc <> 0.
*_101_Errore chiamata FM & - rc = &
MESSAGE e101(zinsi) WITH 'Z_UT_CREA_EXCEL_XML' w_rc.
ENDIF.

CALL METHOD cl_bcs_convert=>solix_to_xstring(
EXPORTING
it_solix = w_xml_table
iv_size = w_xml_size
RECEIVING
ev_xstring = w_xstring ).

OPEN DATASET c_dataset FOR OUTPUT IN BINARY MODE.
TRANSFER w_xstring TO c_dataset LENGTH w_xml_size.
CLOSE DATASET c_dataset.

[mohammadaamir_khan]

Thanks Paolo Sartor for help

[caozhenliang]

Could you provide the complete source code for us to learn?

Thank you!