Need autofit or increase the column width for the excel by using the string to solix method

[ananth_anni]

Hello experts ,

Below code i have used to create the excel attachment in ABAP report .

Problem : when i try to open the sheet i could see the coulmn value in shrink , once click at right top on the column , i could see the complete value .

Need solution : Could you please help me to autofit or increase the column width for the below code .

Proposed solution : Need some method or parameter to increase my column width which i can view the complete data before creating the String to solix or before attachement.

TRY.

      cl_bcs_convert=>string_to_solix(

        EXPORTING

          iv_string   = lv_string

          iv_codepage = '4103'      "suitable for MS Excel, leave empty for other doc types

          iv_add_bom  = 'X'

        IMPORTING

          et_solix  = binary_content

          ev_size   = size ).

    CATCH cx_bcs.

  ENDTRY.

The above binary content is transformed into excel

•     Add the spread sheet as attachment to document object

      document->add_attachment(

        i_attachment_type    = 'xls'

        i_attachment_subject = 'Excel_Attachment_Name'

        i_attachment_size    = size

        i_att_content_hex    = binary_content).

Regards,

Ananth.

[ananth_anni]

Hello experts ,

Below code i have used to create the excel attachment in ABAP report .

Problem : when i try to open the sheet i could see the coulmn value in shrink , once click at right top on the column , i could see the complete value .

Need solution : Could you please help me to autofit or increase the column width for the below code .

Proposed solution : Need some method or parameter to increase my column width which i can view the complete data before creating the String to solix or before attachement.

TRY.

      cl_bcs_convert=>string_to_solix(

        EXPORTING

          iv_string   = lv_string

          iv_codepage = '4103'      "suitable for MS Excel, leave empty for other doc types

          iv_add_bom  = 'X'

        IMPORTING

          et_solix  = binary_content

          ev_size   = size ).

    CATCH cx_bcs.

  ENDTRY.

The above binary content is transformed into excel

•     Add the spread sheet as attachment to document object

      document->add_attachment(

        i_attachment_type    = 'xls'

        i_attachment_subject = 'Excel_Attachment_Name'

        i_attachment_size    = size

        i_att_content_hex    = binary_content).

Regards,

Ananth.

[rajkumarnarasimman]

Hi ananth,

Using OLE Method, i hope we can format the excel column width, please find the link below.

Formatted Excel as Email Attachment - Code Gallery - SCN Wiki

Regards

Rajkumar Narasimman

[ananth_anni]

Thank you ,

But i dont want to modify my code again to OLE , could you please help me on above my code is there possibility to column width or any other method we can use to autofit .

Regards,

Ananth.

[Former Member]

hi ananth,

If you use ole method then file is created on the user system.

Therefore it is better to use xml creation by following the link mentioned by Rajkumar N

[ananth_anni]

Thank you ,

But i dont want to modify my code again to OLE , could you please help me on above my code is there possibility to column width or any other method we can use to autofit .

Regards,

Ananth.

[Former Member]

hi,

you can refer this code which will help to autofit as well as doens't need much changes.

you can refer sap supplied program BCS_EXAMPLE_7.

I have modified as per your requirement by following a link (SAP Wiki: Abap send email with excel attachment)

May be it helps you.

thanks.

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

*& Report  ZR_EXCEL_EMAIL_XML

*&

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

*&

*&BCS_EXAMPLE_7

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

REPORT  ZR_EXCEL_EMAIL_XML.

* This report provides an example for sending an Excel

* attachment in Unicode Systems

constants:

  gc_tab  type c value cl_bcs_convert=>gc_tab,

  gc_crlf type c value cl_bcs_convert=>gc_crlf.

parameters:

  mailto type ad_smtpadr

   default '[email protected]'.                    "#EC *

data send_request   type ref to cl_bcs.

data document       type ref to cl_document_bcs.

data recipient      type ref to if_recipient_bcs.

data bcs_exception  type ref to cx_bcs.

data main_text      type bcsy_text.

data binary_content type solix_tab.

data size           type so_obj_len.

data sent_to_all    type os_boolean.

start-of-selection.

  perform create_content.

  perform send.

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

*&      Form  send

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

form send.

  try.

*     -------- create persistent send request ------------------------

      send_request = cl_bcs=>create_persistent( ).

*     -------- create and set document with attachment ---------------

*     create document object from internal table with text

      append 'Hello world!' to main_text.                   "#EC NOTEXT

      document = cl_document_bcs=>create_document(

        i_type    = 'RAW'

        i_text    = main_text

        i_subject = 'Test Created By BCS_EXAMPLE_7' ).      "#EC NOTEXT

*     add the spread sheet as attachment to document object

      document->add_attachment(

        i_attachment_type    = 'xls'                        "#EC NOTEXT

        i_attachment_subject = 'ExampleSpreadSheet'         "#EC NOTEXT

        i_attachment_size    = size

        i_att_content_hex    = binary_content ).

*     add document object to send request

      send_request->set_document( document ).

*     --------- add recipient (e-mail address) -----------------------

*     create recipient object

      recipient = cl_cam_address_bcs=>create_internet_address( mailto ).

*     add recipient object to send request

      send_request->add_recipient( recipient ).

*     ---------- send document ---------------------------------------

      sent_to_all = send_request->send( i_with_error_screen = 'X' ).

      commit work.

      if sent_to_all is initial.

        message i500(sbcoms) with mailto.

      else.

        message s022(so).

      endif.

*   ------------ exception handling ----------------------------------

*   replace this rudimentary exception handling with your own one !!!

    catch cx_bcs into bcs_exception.

      message i865(so) with bcs_exception->error_type.

  endtry.

endform.                    "send

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

*&      Form  create_content

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

* Create Example Content

* 1) Write example text into a string

* 2) convert this string to solix_tab

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

form create_content.

  data lv_string type string.

  data ls_t100 type t100.

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

* as example content we use some system messages out of t100

* get them for all installed languages from db

* and write one line for each language into the spread sheet

* columns are separated by TAB and each line ends with CRLF

  concatenate lv_string

  '<?xml version="1.0"?>'

' <?mso-application progid="Excel.Sheet"?>'

' <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"'

'  xmlns:o="urn:schemas-microsoft-com:office:office"'

'  xmlns:x="urn:schemas-microsoft-com:office:excel"'

'  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"'

'  xmlns:html="http://www.w3.org/TR/REC-html40"> <Styles>'

'   <Style ss:ID="Default" ss:Name="Normal">'

'    <Alignment ss:Vertical="Bottom"/>'

'    <Borders/>'

'    <Font/>'

'    <Interior/>'

'    <NumberFormat/>'

'    <Protection/>'

'   </Style>'

'   <Style ss:ID="s1">'

'    <Alignment ss:Vertical="Center"/>'

'    <Borders>'

'     <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>'

'     <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>'

'     <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>'

'     <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>'

'    </Borders>'

'   </Style>'

'   <Style ss:ID="s2">'

'    <Alignment ss:Vertical="Center"/>'

'    <Borders>'

'     <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>'

'     <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>'

'     <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>'

'     <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>'

'    </Borders>'

'   </Style>'

'   <Style ss:ID="s3">'

'    <Borders>'

'     <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>'

'     <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>'

'     <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>'

'     <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>'

'    </Borders>'

'   </Style>'

'   <Style ss:ID="s4">'

'    <Borders>'

'     <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>'

'     <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>'

'     <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>'

'     <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>'

'    </Borders>'

'    <Font x:Family="Swiss" ss:Bold="1"/>'

'    <Interior ss:Color="#CCFFFF" ss:Pattern="Solid"/>'

'   </Style>'

'   <Style ss:ID="s5">'

'    <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>'

'    <Borders>'

'     <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>'

'     <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>'

'     <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>'

'     <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>'

'    </Borders>'

'    <Font x:Family="Swiss" ss:Bold="1"/>'

'    <Interior ss:Color="#CCFFFF" ss:Pattern="Solid"/>'

'   </Style>'

'   <Style ss:ID="s6">'

'    <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>'

'    <Borders>'

'     <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>'

'     <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>'

'     <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>'

'     <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>'

'    </Borders>'

'   </Style>'

'   <Style ss:ID="s7">'

'    <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>'

'   </Style>'

'   <Style ss:ID="s8">'

'    <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>'

'    <Borders>'

'     <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>'

'     <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>'

'     <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>'

'     <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>'

'    </Borders>'

'    <Font x:Family="Swiss" ss:Size="24" ss:Bold="1"/>'

'   </Style>'

'  </Styles>'

'  <Worksheet ss:Name="Sheet1">'

'   <Table ss:ExpandedColumnCount="4" ss:ExpandedRowCount="9" x:FullColumns="1"'

'    x:FullRows="1">'

'    <Column ss:AutoFitWidth="0" ss:Width="135"/>'

'    <Column ss:Index="3" ss:StyleID="s7" ss:AutoFitWidth="0" ss:Width="66.75"/>'

'    <Row ss:Height="30">'

'     <Cell ss:MergeAcross="3" ss:StyleID="s8"><Data ss:Type="String">Title</Data></Cell>'

'    </Row>'

'    <Row>'

'     <Cell ss:StyleID="s4"><Data ss:Type="String">Column1</Data></Cell>'

'     <Cell ss:StyleID="s4"><Data ss:Type="String">Column2</Data></Cell>'

'     <Cell ss:StyleID="s5"><Data ss:Type="String">Column3</Data></Cell>'

'     <Cell ss:StyleID="s4"><Data ss:Type="String">Column4</Data></Cell>'

'    </Row>'

'    <Row ss:Height="76.5">'

'     <Cell ss:MergeDown="1" ss:StyleID="s1"><Data ss:Type="String">Row 3 and Row 4 Merged</Data></Cell>'

'     <Cell ss:StyleID="s3"><Data ss:Type="String">Red</Data></Cell>'

'     <Cell ss:StyleID="s6"><Data ss:Type="String">Wrap text when width of the text exceeds the specified width</Data></Cell>'

'     <Cell ss:StyleID="s3"/>'

'    </Row>'

'    <Row>'

'     <Cell ss:Index="2" ss:StyleID="s3"><Data ss:Type="String">Black</Data></Cell>'

'     <Cell ss:StyleID="s6"/>'

'     <Cell ss:StyleID="s3"/>'

'    </Row>'

'    <Row>'

'     <Cell ss:StyleID="s3"><Data ss:Type="String">Row 4</Data></Cell>'

'     <Cell ss:StyleID="s3"><Data ss:Type="String">Black</Data></Cell>'

'     <Cell ss:StyleID="s6"/>'

'     <Cell ss:StyleID="s3"/>'

'    </Row>'

'    <Row>'

'     <Cell ss:StyleID="s3"><Data ss:Type="String">Row 5</Data></Cell>'

'     <Cell ss:StyleID="s3"><Data ss:Type="String">Red</Data></Cell>'

'     <Cell ss:StyleID="s6"/>'

'     <Cell ss:StyleID="s3"/>'

'    </Row>'

'    <Row>'

'     <Cell ss:StyleID="s3"><Data ss:Type="String">Row 6</Data></Cell>'

'     <Cell ss:StyleID="s3"><Data ss:Type="String">Black</Data></Cell>'

'     <Cell ss:StyleID="s6"/>'

'     <Cell ss:StyleID="s3"/>'

'    </Row>'

'    <Row>'

'     <Cell ss:MergeDown="1" ss:StyleID="s2"><Data ss:Type="String">Row 8 and 9 Merged</Data></Cell>'

'     <Cell ss:StyleID="s3"><Data ss:Type="String">Green</Data></Cell>'

'     <Cell ss:StyleID="s6"/>'

'     <Cell ss:StyleID="s3"/>'

'    </Row>'

'    <Row>'

'     <Cell ss:Index="2" ss:StyleID="s3"><Data ss:Type="String">Red</Data></Cell>'

'     <Cell ss:StyleID="s6"/>'

'     <Cell ss:StyleID="s3"/>'

'    </Row>'

'   </Table>  <DataValidation xmlns="urn:schemas-microsoft-com:office:excel">'

'    <Range>R2C2:R1000C2</Range>'

'    <Type>List</Type>'

'    <CellRangeList/>'

'    <Value>"Red, Black, Green"</Value>'

'   </DataValidation>'

'  </Worksheet></Workbook> '

into lv_string.

* data lines

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

* convert the text string into UTF-16LE binary data including

* byte-order-mark. Mircosoft Excel prefers these settings

* all this is done by new class cl_bcs_convert (see note 1151257)

  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.

endform.                    "create_content

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

* NOTES:

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

* UTF-16LE including the BOM (Byte order mark)

* is preferred by Microsoft Excel. If you want to create

* other binary content you may choose another codepage (e.g.

* '4110' (UTF-8) which is standard for e-mails).

* Find SAP codepage names in the drop down list

* for the codepage setting of node SMTP in transaction SCOT.

* Or: leave iv_codepage and iv_add_bom empty. Then the target

* codepage is set according to SAPconnect settings

*

* Important:

* SAP neither guarantees that the attachment created

* by this report can be opened by all Excel Versions nor

* that it can be opened by any 3rd party software at all