Protected(read-only) Excel document as e-mail attachment

[Former Member]

Hello everyone,

I have a program that sends email with Excel attachment. My request is to send this attachment as protected or/and read-only document. I am using cl_bcs class to send mail.

I found similar 2-3 topics about this issue but all useless.

Thank you in advance.

[Former Member]

Hi Kerim,

I understand that the program is generating the data from some internal table, and then attaching it to the email.

In that case, the attachment is really just a text file (with XLS extension) and gets opened in excel. Hence it truly does not have any excel features such as read only / protected.

For those things we may have to use OLE concept. The steps could be like this:

a) create the text file, and save on local machine/front-end.

b) then open the same file using commands of OLE

c) then pass OLE command for read only / protect

d) save the file again, now as true .XLS format, using OLE command. (this will be a new file)

e) Now, attach the NEW XLS file from the front end to the email. This file will be a binary file now in MS EXCEL format.

I have not done exactly this before but I had used OLE for test purposes. Hope it helps.

Regards,

Amit Mittal.

[Former Member]

Still no answers?

[Former Member]

After reading your post, I looked at all the BCS-associated methods I could find and searched SCN. I don't find any reference to setting a read-only indicator on the attachment unfortunately. I was hoping to find a "no change" parameter, but had no luck.

[Former Member]

Hi Kerim,

I understand that the program is generating the data from some internal table, and then attaching it to the email.

In that case, the attachment is really just a text file (with XLS extension) and gets opened in excel. Hence it truly does not have any excel features such as read only / protected.

For those things we may have to use OLE concept. The steps could be like this:

a) create the text file, and save on local machine/front-end.

b) then open the same file using commands of OLE

c) then pass OLE command for read only / protect

d) save the file again, now as true .XLS format, using OLE command. (this will be a new file)

e) Now, attach the NEW XLS file from the front end to the email. This file will be a binary file now in MS EXCEL format.

I have not done exactly this before but I had used OLE for test purposes. Hope it helps.

Regards,

Amit Mittal.

[Former Member]

Hi again,

There is another method also, apart from OLE. It is using XML / XSLT concept.

I just tried it and it works fine.

a) the file opens in READ Mode preferred

b) It is protected, i.e. we cannot change the cells

1. Make one Z program (code is given below as "SOURCE1")

2. Inside the code, double-click on "ztest_np_xls" which will take you to new screen of XSLT. Inside that, there is one tab "Source code". Inside that remove the old code of 5-7 lines, and copy-paste my code. "SOURCE2"

SOURCE1

*&---------------------------------------------------------------------*
*& Report  ZTEST_NP_EXCEL_XML
*&
*& Download the formatted excel file using XML
*&---------------------------------------------------------------------*

REPORT  ztest_np_excel_xml.

TYPES: BEGIN OF ty_mara,
       matnr TYPE matnr,
       maktx TYPE char30,
       END   OF ty_mara.

DATA: itab TYPE STANDARD TABLE OF ty_mara,
      la_tab LIKE LINE OF itab,
      xmlstr TYPE string.

START-OF-SELECTION.

*---------
* Test table
*---------
  la_tab-matnr = 'TEST1'.
  la_tab-maktx = 'Test description'.
  APPEND la_tab TO itab.

  la_tab-matnr = 'TEST2'.
  la_tab-maktx = 'Test description 2'.
  APPEND la_tab TO itab.

*---------
* Get the XML data excel
*---------
  CALL TRANSFORMATION ztest_np_xls
    SOURCE table = itab
    RESULT XML xmlstr.

*---------
* Download the file
*---------

* Fill the table
  DATA: xml_table TYPE STANDARD TABLE OF string.

  APPEND xmlstr TO xml_table.

  DATA: window_title TYPE string,
        fullpath TYPE string,
        path TYPE string,
        user_action TYPE i,
        default_extension TYPE string,
        default_file_name TYPE string,
        file_filter TYPE  string,
        filename TYPE string,
        initialpath TYPE string.

* File selection
  MOVE '.XLS' TO default_extension.
  MOVE 'XLS files (*.XLS)|*.XLS' TO file_filter.

  CALL METHOD cl_gui_frontend_services=>file_save_dialog
    EXPORTING
      default_extension = default_extension
      default_file_name = default_file_name
      file_filter       = file_filter
      initial_directory = initialpath
    CHANGING
      filename          = filename
      path              = path
      fullpath          = fullpath
      user_action       = user_action
    EXCEPTIONS
      cntl_error        = 1
      error_no_gui      = 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.

* download file
  CALL FUNCTION 'GUI_DOWNLOAD'
    EXPORTING
      filename                = fullpath
      filetype                = 'ASC'
    TABLES
      data_tab                = xml_table
    EXCEPTIONS
      file_write_error        = 1
      no_batch                = 2
      gui_refuse_filetransfer = 3
      invalid_type            = 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.

-

---

SOURCE2 in next

regards,

Amit Mittal.

Edited by: Amit Mittal on Feb 1, 2012 3:59 PM

Edited by: Amit Mittal on Feb 1, 2012 4:00 PM

Edited by: Amit Mittal on Feb 1, 2012 4:01 PM

Edited by: Amit Mittal on Feb 1, 2012 4:01 PM

Edited by: Amit Mittal on Feb 1, 2012 4:02 PM

Edited by: Amit Mittal on Feb 1, 2012 4:03 PM

[Former Member]

SOURCE2 (as per previus reply)

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<tt:transform xmlns:tt='http://www.sap.com/transformation-templates'>

<tt:root name='table'/>

<tt:template>


<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">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>authorname</Author>
  <LastAuthor>authorname</LastAuthor>
  <Created>2012-02-01T10:12:47Z</Created>
  <Company></Company>
  <Version>12.00</Version>
 </DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <ReadOnlyRecommended/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>11055</WindowHeight>
  <WindowWidth>19155</WindowWidth>
  <WindowTopX>0</WindowTopX>
  <WindowTopY>120</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s62">
   <Protection/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1" ss:Protected="1">
  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="3" x:FullColumns="1"
   x:FullRows="1" ss:StyleID="s62" ss:DefaultRowHeight="15">
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">ColA</Data></Cell>
    <Cell><Data ss:Type="String">ColB</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">d1</Data></Cell>
    <Cell><Data ss:Type="String">db</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">dc</Data></Cell>
    <Cell><Data ss:Type="String">dd</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <Unsynced/>
   <Print>
    <ValidPrinterInfo/>
    <HorizontalResolution>600</HorizontalResolution>
    <VerticalResolution>600</VerticalResolution>
   </Print>
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>22</ActiveRow>
     <ActiveCol>2</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>True</ProtectObjects>
   <ProtectScenarios>True</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>


</tt:template>

</tt:transform>

regards,

amit mittal.

Edited by: Amit Mittal on Feb 2, 2012 2:29 PM

Edited by: Amit Mittal on Feb 2, 2012 2:30 PM