Formatted Excel in SAP ABAP using XML

Objective: To generate the formatted excel file which can be send or download as per below requirement.

1: User should able to download excel file when using GUI and execute in foreground mode.

2: When sending the email in foreground as well as if email is send in with background job.

3:When the ECC transaction is use as transaction launcher in SAP CRM the excel file should get generated same as it is working in SAP ECC, as in case of Transaction launcher it works as web page.

Advantage of using XML over OLE: As we can use the OLE method in ABAP to generate formatted excel but its work only in GUI foreground mode, where as we can use ‘xml’ concept in foreground as well as in background jobs for sending email etc.

Example:  I have made the transaction in ECC which takes input as Year and display all the dates with weekday and in case of weekly off i.e Saturday and Sunday the background color of the cell will be green.

If user select the first option ‘Download Excel’ system will generate the excel file, presently I have kept the excel file in temp folder and same I have executed if file save successfully and in case of ‘Send email’ system will send the same file as email attachment.

Note :  1:We cannot save the file in any PC folder in background but we can save on app. Server using open data set method.

             2: I have not added the code for email in my example we can check demo program BCS_EXAMPLE_7 to send file as email attachment.

             3: I have made this program by refereeing the existing SDN doc.   

http://wiki.scn.sap.com/wiki/display/Snippets/Formatted+Excel+as+Email+Attachment, I have use same for downloading the file in ECC frontend as well as when same is used in CRM WEB UI as transaction launcher.

              4: For checking this you can upload the attach program in your system and can check the same.

Output:

Source Code :

*&---------------------------------------------------------------------*

*& Report  ZAFAR_XML

*&

*&---------------------------------------------------------------------*

*&

*&

*&---------------------------------------------------------------------*

REPORT  ZAFAR_XML.

PARAMETERS : P_GJAHR   TYPE GJAHR OBLIGATORY,

             C_C1 AS CHECKBOX DEFAULT 'X',

             C_C2 AS CHECKBOX.

DATA : GV_FDATE      TYPE SY-DATUM,

       GV_LDATE      TYPE SY-DATUM.

DATA : GT_CASDAYATTR    TYPE STANDARD TABLE OF CASDAYATTR,

       GS_CASDAYATTR    TYPE CASDAYATTR.

" DATA declaration for XML

TYPES: BEGIN OF xml_line,

        data(255) TYPE x,

       END OF xml_line.

DATA: l_ixml            TYPE REF TO if_ixml,

      l_streamfactory   TYPE REF TO if_ixml_stream_factory,

      l_ostream         TYPE REF TO if_ixml_ostream,

      l_renderer        TYPE REF TO if_ixml_renderer,

      l_document        TYPE REF TO if_ixml_document.

DATA: l_element_root        TYPE REF TO if_ixml_element,

      ns_attribute          TYPE REF TO if_ixml_attribute,

      r_element_properties  TYPE REF TO if_ixml_element,

      r_element             TYPE REF TO if_ixml_element,

      r_worksheet           TYPE REF TO if_ixml_element,

      r_table               TYPE REF TO if_ixml_element,

      r_column              TYPE REF TO if_ixml_element,

      r_row                 TYPE REF TO if_ixml_element,

      r_cell                TYPE REF TO if_ixml_element,

      r_data                TYPE REF TO if_ixml_element,

      l_value               TYPE string,

      l_type                TYPE string,

      l_text(100)           TYPE c,

      r_styles              TYPE REF TO if_ixml_element,

      r_style               TYPE REF TO if_ixml_element,

      r_style1              TYPE REF TO if_ixml_element,

      r_style2              TYPE REF TO if_ixml_element,

      r_style3              TYPE REF TO if_ixml_element,

      r_style4              TYPE REF TO if_ixml_element,

      r_style5              TYPE REF TO if_ixml_element,

      r_style6              TYPE REF TO if_ixml_element,

      r_style7              TYPE REF TO if_ixml_element,

      r_format              TYPE REF TO if_ixml_element,

      r_border              TYPE REF TO if_ixml_element,

      num_rows              TYPE i,

      GV_COLOR              TYPE STRING.

DATA: l_xml_table       TYPE TABLE OF xml_line,

      wa_xml            TYPE xml_line,

      l_xml_size        TYPE i,

      l_rc              TYPE i.

"for binary data

DATA: gt_objbin         LIKE solix OCCURS 10 WITH HEADER LINE.

START-OF-SELECTION.

CHECK P_GJAHR IS NOT  INITIAL.

 CONCATENATE P_GJAHR '0101'  INTO GV_FDATE.

 CONCATENATE P_GJAHR '1231'  INTO GV_LDATE.

 CALL FUNCTION 'DAY_ATTRIBUTES_GET'

  EXPORTING

    DATE_FROM                        = GV_FDATE

    DATE_TO                          = GV_LDATE

    LANGUAGE                         = SY-LANGU

   TABLES

     day_attributes                  = GT_CASDAYATTR

  EXCEPTIONS

    FACTORY_CALENDAR_NOT_FOUND       = 1

    HOLIDAY_CALENDAR_NOT_FOUND       = 2

    DATE_HAS_INVALID_FORMAT          = 3

    DATE_INCONSISTENCY               = 4

    OTHERS                           = 5

           .

 IF sy-subrc <> 0.

* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

 ENDIF.

 CHECK GT_CASDAYATTR[] IS NOT  INITIAL.

 PERFORM CREATE_FILE.

IF C_C1 IS NOT INITIAL.

   PERFORM DOWNLOAD_FILE.

ELSE.

"for email refer BCS_EXAMPLE_7

"attach the table gt_objbin in email attachment

ENDIF.

* BREAK-POINT.

 FORM CREATE_FILE.

* Creating a ixml Factory

  l_ixml = cl_ixml=>create( ).

* Creating the DOM Object Model

  l_document = l_ixml->create_document( ).

* Create Root Node 'Workbook'

  l_element_root  = l_document->create_simple_element( name = 'Workbook'  parent = l_document ).

  l_element_root->set_attribute( name = 'xmlns'  value = 'urn:schemas-microsoft-com:office:spreadsheet' ).

  ns_attribute = l_document->create_namespace_decl( name = 'ss'  prefix = 'xmlns'  uri = 'urn:schemas-microsoft-com:office:spreadsheet' ).

  l_element_root->set_attribute_node( ns_attribute ).

  ns_attribute = l_document->create_namespace_decl( name = 'x'  prefix = 'xmlns'  uri = 'urn:schemas-microsoft-com:office:excel' ).

  l_element_root->set_attribute_node( ns_attribute ).

* Create node for document properties.

  r_element_properties = l_document->create_simple_element( name = 'XML_EXCEL'  parent = l_element_root ).

  l_value = sy-uname.

  l_document->create_simple_element( name = 'Author'  value = l_value  parent = r_element_properties  ).

* Styles Parent for all child styles which control cell style like border, font, color etc.

  r_styles = l_document->create_simple_element( name = 'Styles'  parent = l_element_root  ).

* Style for Header

  r_style  = l_document->create_simple_element( name = 'Style'   parent = r_styles  ).

  r_style->set_attribute_ns( name = 'ID'  prefix = 'ss'  value = 'Header' ).

"below code is to set Font as Bold

  r_format  = l_document->create_simple_element( name = 'Font'  parent = r_style  ).

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

"below code will set the alligment

  r_format  = l_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  = l_document->create_simple_element( name = 'Borders'  parent = r_style ).

  PERFORM XML_BORDERS.

* Style for Data without background color balnk

  r_style1  = l_document->create_simple_element( name = 'Style'   parent = r_styles  ).

  r_style1->set_attribute_ns( name = 'ID'  prefix = 'ss'  value = 'Data1' ).

  r_border  = l_document->create_simple_element( name = 'Borders'  parent = r_style1 ).

  PERFORM XML_BORDERS.

* Style for Data1 background color : green

  r_style2  = l_document->create_simple_element( name = 'Style'   parent = r_styles  ).

  r_style2->set_attribute_ns( name = 'ID'  prefix = 'ss'  value = 'Data2' ).

  r_format  = l_document->create_simple_element( name = 'Interior' parent = r_style2  ).

  r_format->set_attribute_ns( name = 'Color'   prefix = 'ss'  value = '#92D050' ).

  r_format->set_attribute_ns( name = 'Pattern' prefix = 'ss'  value = 'Solid' ).

  r_border  = l_document->create_simple_element( name = 'Borders'  parent = r_style2 ).

  PERFORM XML_BORDERS.

* Worksheet

  r_worksheet = l_document->create_simple_element( name = 'Worksheet'  parent = l_element_root ).

  r_worksheet->set_attribute_ns( name = 'Name'  prefix = 'ss'  value = 'Sheet1' ).

* Table

  r_table = l_document->create_simple_element( name = 'Table'  parent = r_worksheet ).

  r_table->set_attribute_ns( name = 'FullColumns'  prefix = 'x'  value = '1' ).

  r_table->set_attribute_ns( name = 'FullRows'     prefix = 'x'  value = '1' ).

"Here I am creating the 3 columns

  DO 3 TIMES.

  r_column = l_document->create_simple_element( name = 'Column'  parent = r_table ).

  r_column->set_attribute_ns( name = 'Width'  prefix = 'ss'  value = '150' ).

  ENDDO.

* Headers Row

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

  r_row->set_attribute_ns( name = 'AutoFitHeight'  prefix = 'ss'  value = '1' ).

"Header Row data cell wise

"first columns

     r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

     r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Header' ).

     r_data = l_document->create_simple_element( name = 'Data'  value = 'Date'  parent = r_cell ).

     r_data->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

"second column

     r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

     r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Header' ).

     r_data = l_document->create_simple_element( name = 'Data'  value = 'WeekDay Name'  parent = r_cell ).

     r_data->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

"Third column

     r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

     r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Header' ).

     r_data = l_document->create_simple_element( name = 'Data'  value = 'WeekDay Name string'  parent = r_cell ).

     r_data->set_attribute_ns( name = 'Type'  prefix = 'ss' value = 'String' ).

"now we have created the header row as per adding the code written above

"create new row for line items

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

  r_row->set_attribute_ns( name = 'AutoFitHeight'  prefix = 'ss'  value = '1' ).

"to add all the dates details

   LOOP AT GT_CASDAYATTR INTO GS_CASDAYATTR.

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

"first column ( first cell )

    r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

    IF GS_CASDAYATTR-WEEKDAY = '6' OR GS_CASDAYATTR-WEEKDAY = '7'.

    r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data2' ).

    ELSE.

    r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data1' ).

    ENDIF.

CALL FUNCTION 'CONVERT_DATE_TO_EXTERNAL'

 EXPORTING

   DATE_INTERNAL                  = GS_CASDAYATTR-DATE

 IMPORTING

   DATE_EXTERNAL                  = l_value

 EXCEPTIONS

   DATE_INTERNAL_IS_INVALID       = 1

   OTHERS                         = 2

          .

IF sy-subrc <> 0.

* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

    r_data = l_document->create_simple_element( name = 'Data'  value = l_value   parent = r_cell ).          " Data

    r_data->set_attribute_ns( name = 'Type'  prefix = 'ss'  value = 'String' ).

"second column ( second cell )

    r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

"if sat. or sunday system will apply green as background color for which we have created

"style id ID Data2

    IF GS_CASDAYATTR-WEEKDAY = '6' OR GS_CASDAYATTR-WEEKDAY = '7'.

    r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data2' ).

    ELSE.

    r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data1' ).

    ENDIF.

    l_value = GS_CASDAYATTR-WEEKDAY_L.

    r_data = l_document->create_simple_element( name = 'Data'  value = l_value   parent = r_cell ).          " Data

    r_data->set_attribute_ns( name = 'Type'  prefix = 'ss'  value = 'String' ).

"third column ( third cell )

    r_cell = l_document->create_simple_element( name = 'Cell'  parent = r_row ).

    IF GS_CASDAYATTR-WEEKDAY = '6' OR GS_CASDAYATTR-WEEKDAY = '7'.

    r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data2' ).

    ELSE.

    r_cell->set_attribute_ns( name = 'StyleID'  prefix = 'ss'  value = 'Data1' ).

    ENDIF.

    l_value = GS_CASDAYATTR-DAY_STRING.

    r_data = l_document->create_simple_element( name = 'Data'  value = l_value   parent = r_cell ).          " Data

    r_data->set_attribute_ns( name = 'Type'  prefix = 'ss'  value = 'String' ).

       CLEAR : GS_CASDAYATTR.

   ENDLOOP.

* Creating a Stream Factory

  l_streamfactory = l_ixml->create_stream_factory( ).

* Connect Internal XML Table to Stream Factory

  l_ostream = l_streamfactory->create_ostream_itable( table = l_xml_table ).

* Rendering the Document

  l_renderer = l_ixml->create_renderer( ostream  = l_ostream  document = l_document ).

  l_rc = l_renderer->render( ).

* Saving the XML Document

  l_xml_size = l_ostream->get_num_written_raw( ).

* Creation of the Document Attachment

  LOOP AT l_xml_table INTO wa_xml.

    CLEAR gt_objbin.

    gt_objbin-line = wa_xml-data.

    APPEND gt_objbin.

  ENDLOOP.

 ENDFORM.

 FORM XML_BORDERS.

  r_format  = l_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  = l_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  = l_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  = l_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' ).

ENDFORM.

FORM DOWNLOAD_FILE.

CALL FUNCTION 'GUI_DOWNLOAD'

  EXPORTING

*   BIN_FILESIZE                    =

    filename                        = 'C:\temp\XML_EXCEL.xls'

   FILETYPE                        = 'BIN'

  tables

    data_tab                        = GT_objbin

*   FIELDNAMES                      =

 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

   OTHERS                          = 22

          .

IF sy-subrc <> 0.

* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ELSE.

    WAIT UP TO 1 seconds.

   CALL METHOD cl_gui_frontend_services=>execute

     EXPORTING

       document               = 'C:\temp\XML_EXCEL.xls'

     EXCEPTIONS

       cntl_error             = 1

       error_no_gui           = 2

       bad_parameter          = 3

       file_not_found         = 4

       path_not_found         = 5

       file_extension_unknown = 6

       error_execute_failed   = 7

       synchronous_failed     = 8

       not_supported_by_gui   = 9

       others                 = 10

           .

   IF sy-subrc <> 0.

*    MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

*               WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

   ENDIF.

ENDIF.

ENDFORM.