Autofit columns in excel attachment

[Former Member]

Hi Abap gurus,

           how to do the Auto fit columns in excel which i am sending as a mail attachment?  i am able to send the excel sheet as mail attachment but the columns are not auto fit. suggest me how to proceed?

[Former Member]

Hi abap gurus,

        please find the below code for the above requirement.

TYPE-POOLS: ixml.

*------------------------------------------------------------------------*

*                           Data Declarations                            *

*------------------------------------------------------------------------*

* Structure for Final Internal Table

TYPES: BEGIN OF ty_final,

        srno(3) TYPE n,

        user_id TYPE usr02-bname,

        full_name TYPE bapiaddr3-fullname,

        dept TYPE bapiaddr3-department,

        login(3) TYPE c,

       END OF ty_final.

* Structure for USR02

TYPES: BEGIN OF ty_usr02,

        bname TYPE usr02-bname,

        trdat TYPE usr02-trdat,

       END OF ty_usr02.

* Internal Table & Work Area for Final Internal Table

DATA: it_final TYPE TABLE OF ty_final,

      wa_final TYPE ty_final.

* Internal Table & Work Area for USR02 Internal Table

DATA: it_usr02 TYPE TABLE OF ty_usr02,

      wa_usr02 TYPE ty_usr02.

* Work Area for ADD3_DATA Structre

DATA: wa_addr TYPE bapiaddr3.

DATA: it_return TYPE TABLE OF bapiret2.

DATA: lv_date TYPE d.

DATA: lv_filename TYPE string.

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_format              TYPE REF TO if_ixml_element,

      r_border              TYPE REF TO if_ixml_element,

      num_rows              TYPE i.

DATA: l_xml_table       TYPE TABLE OF xml_line,

      wa_xml            TYPE xml_line,

      l_xml_size        TYPE i,

      l_rc              TYPE i.

*------------------------------------------------------------------------*

*                             Initialization                             *

*------------------------------------------------------------------------*

INITIALIZATION.

  lv_date = sy-datum - 1.

*------------------------------------------------------------------------*

*                           Start of Selection                           *

*------------------------------------------------------------------------*

START-OF-SELECTION.

  PERFORM get_user_data.

  PERFORM process_xml_data.

  PERFORM send_mail.

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

*&      Form  get_user_data

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

*       Fetch User details from USR02

*----------------------------------------------------------------------*

FORM get_user_data.

  REFRESH it_final.

  SELECT DISTINCT bname trdat FROM usr02 INTO TABLE it_usr02.

  SORT it_usr02 BY bname.

  IF NOT it_usr02[] IS INITIAL.

    LOOP AT it_usr02 INTO wa_usr02.

      CLEAR wa_final.

      wa_final-srno = sy-tabix.                   " Serial No.

      wa_final-user_id = wa_usr02-bname.          " User ID

      CALL FUNCTION 'BAPI_USER_GET_DETAIL'

        EXPORTING

          username = wa_usr02-bname

        IMPORTING

          address  = wa_addr

        TABLES

          return   = it_return.

      IF sy-subrc EQ 0.

        wa_final-full_name = wa_addr-fullname.    " Full Name

        wa_final-dept = wa_addr-department.       " Department

      ENDIF.

      IF wa_usr02-trdat EQ lv_date.

        wa_final-login = 'YES'.                   " Login on Previous Day

      ELSE.

        wa_final-login = 'NO'.

      ENDIF.

      APPEND wa_final TO it_final.

    ENDLOOP.

  ENDIF.

ENDFORM.                    " get_user_data

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

*&      Form  SEND_MAIL

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

*       Send Email

*----------------------------------------------------------------------*

FORM send_mail.

  DATA: objpack   LIKE sopcklsti1 OCCURS 2 WITH HEADER LINE.

  DATA: objhead   LIKE solisti1 OCCURS 1 WITH HEADER LINE.

  DATA: objbin    LIKE solix OCCURS 10 WITH HEADER LINE.

  DATA: objtxt    LIKE solisti1 OCCURS 10 WITH HEADER LINE.

  DATA: reclist   LIKE somlreci1 OCCURS 5 WITH HEADER LINE.

  DATA: doc_chng  LIKE sodocchgi1.

  DATA: tab_lines LIKE sy-tabix.

  DATA: l_num(3).

  DATA: subj_date(10) TYPE c.

* Mail Subject

  CONCATENATE lv_date+6(2) '-' lv_date+4(2) '-' lv_date+0(4) INTO subj_date.

  CONCATENATE 'SAP Application Usage Report ' subj_date INTO doc_chng-obj_descr SEPARATED BY space.

* Mail Contents

  objtxt = 'Dear User,'.

  APPEND objtxt.

  CLEAR objtxt.

  APPEND objtxt.

  CONCATENATE 'Please find the attached SAP Application Usage Report for ' subj_date INTO objtxt SEPARATED BY space.              " Mail Contents

  APPEND objtxt.

  CLEAR objtxt.

  APPEND objtxt.

  objtxt = 'Thanks & Regards,'.

  APPEND objtxt.

  objtxt = 'Himanshu Kanekar'.

  APPEND objtxt.

  DESCRIBE TABLE objtxt LINES tab_lines.

  READ TABLE objtxt INDEX tab_lines.

  doc_chng-doc_size = ( tab_lines - 1 ) * 255 + STRLEN( objtxt ).

* Packing List For the E-mail Body

  objpack-head_start = 1.

  objpack-head_num   = 0.

  objpack-body_start = 1.

  objpack-body_num   = tab_lines.

  objpack-doc_type   = 'RAW'.

  APPEND objpack.

* Creation of the Document Attachment

  LOOP AT l_xml_table INTO wa_xml.

    CLEAR objbin.

    objbin-line = wa_xml-data.

    APPEND objbin.

  ENDLOOP.

  DESCRIBE TABLE objbin LINES tab_lines.

  objhead = 'SAP Login Details'.

  APPEND objhead.

* Packing List For the E-mail Attachment

  objpack-transf_bin = 'X'.

  objpack-head_start = 1.

  objpack-head_num   = 0.

  objpack-body_start = 1.

  objpack-body_num = tab_lines.

  CONCATENATE 'SAP_Login_Details' subj_date INTO objpack-obj_descr SEPARATED BY space.

  objpack-doc_type = 'XLS'.

  objpack-doc_size = tab_lines * 255.

  APPEND objpack.

* Target Recipent

  CLEAR reclist.

  reclist-receiver = 'user@company.com'.

  reclist-rec_type = 'U'.

  APPEND reclist.

* Sending the document

  CALL FUNCTION 'SO_NEW_DOCUMENT_ATT_SEND_API1'

    EXPORTING

      document_data              = doc_chng

      put_in_outbox              = 'X'

    TABLES

      packing_list               = objpack

      object_header              = objhead

      contents_txt               = objtxt

      contents_hex               = objbin

      receivers                  = reclist

    EXCEPTIONS

      too_many_receivers         = 1

      document_not_sent          = 2

      operation_no_authorization = 4

      OTHERS                     = 99.

ENDFORM.                    " SEND_MAIL

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

*&      Form  process_xml_data

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

*       Process XML Data

*----------------------------------------------------------------------*

FORM process_xml_data .

* 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 = 'TEST_REPORT'  parent = l_element_root ).

  l_value = sy-uname.

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

* Styles

  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' ).

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

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

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

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

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

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

  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' ).

* Style for Data

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

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

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

  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' ).

* 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' ).

* Column Formatting

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

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

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

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

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

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

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

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

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

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

* Blank Row

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

* Column 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' ).

* Sr. No.

  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 = 'Sr. No.'  parent = r_cell ).

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

* User Name

  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 = 'User Name'  parent = r_cell ).

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

* Full Name

  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 = 'Full Name'  parent = r_cell ).

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

* Department

  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 = 'Department'  parent = r_cell ).

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

* Login

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

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

  CONCATENATE 'Login - ' lv_date+6(2) '/' lv_date+4(2) '/' lv_date+0(4) INTO l_value.

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

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

* Blank Row after Column Headers

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

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

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

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

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

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

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

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

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

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

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

* Data Table

  LOOP AT it_final INTO wa_final.

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

* Sr. No.

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

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

    l_value = sy-tabix.

    CONDENSE l_value NO-GAPS.

    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 = 'Number' ).                               " Cell format

* User Name

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

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

    l_value = wa_final-user_id.

    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' ).                               " Cell format

* Full Name

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

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

    l_value = wa_final-full_name.

    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' ).                               " Cell format

* Department

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

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

    l_value = wa_final-dept.

    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' ).                               " Cell format

* Login

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

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

    l_value = wa_final-login.

    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' ).                              " Cell format

  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( ).

ENDFORM.                    " process_xml_data

[former_member696995]

how to use cell locking using this method