Report in Excel

Former Member · ‎2007 Jul 17

I have made the report which directly opens in the Excel, but my requirement is that i want to protect that sheet so that user can't be able to modify that excel sheet. below is the sample code kindly let me know what code has to be written to protect the sheet.



REPORT  z_test.


INCLUDE ole2incl.

* handles for OLE objects
DATA: h_excel   TYPE ole2_object,        " Excel object
      h_mapl    TYPE ole2_object,        " list of workbooks
      h_map     TYPE ole2_object,        " workbook
      h_cell    TYPE ole2_object,        " cell
      h_f       TYPE ole2_object,        " font
      h_col     TYPE ole2_object,
      h_row     TYPE ole2_object,
      h_int     TYPE ole2_object,
      h_auto    TYPE ole2_object,
      h_select  TYPE ole2_object,
      h_range   TYPE ole2_object,
      h_merge   TYPE ole2_object,
      h_columns TYPE ole2_object,
      h_rows    TYPE ole2_object,
      h_borders TYPE ole2_object,
      h_protect TYPE ole2_object,
      row       TYPE i,
      col       TYPE i,
      v_prog(70),
      v_range1(10),
      v_range2(10),
      v_r1(10),
      v_r2(10).

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


START-OF-SELECTION .

END-OF-SELECTION .
  PERFORM f_start_excel .
  PERFORM f_display_hdr .
  PERFORM f_stop_excel .



*&---------------------------------------------------------------------*
*&      Form  f_start_excel
*&---------------------------------------------------------------------*
FORM f_start_excel .
* start Excel
  CREATE OBJECT h_excel 'EXCEL.APPLICATION'.
  SET PROPERTY OF h_excel  'Visible' = 0.

* get list of workbooks, initially empty
  CALL METHOD OF h_excel 'Workbooks' = h_mapl.
  PERFORM err_hdl.
* add a new workbook
  CALL METHOD OF h_mapl 'Add' = h_map.
  PERFORM err_hdl.
ENDFORM.                    " f_start_excel
*&---------------------------------------------------------------------*
*&      Form  f_stop_excel
*&---------------------------------------------------------------------*
FORM f_stop_excel .
  FREE OBJECT h_cell.

  CALL METHOD OF h_excel 'Cells' = h_cell .
  GET PROPERTY OF h_cell  'Select' = h_select.

  CALL METHOD OF h_cell 'Columns' = h_columns .
  CALL METHOD OF h_columns 'AutoFit' = h_auto .

  CALL METHOD OF h_cell 'Rows' = h_rows .
  CALL METHOD OF h_rows 'AutoFit' = h_auto .

  FREE OBJECT h_cell.

  CALL METHOD OF h_excel 'Cells' = h_cell
    EXPORTING
    #1 = 1
    #2 = 1.
  GET PROPERTY OF h_cell  'Select' = h_select.

*  CALL METHOD OF h_excel 'Protect' = h_protect .
  CALL METHOD OF h_excel 'Workbooks' = h_mapl.
  CALL METHOD OF h_mapl 'Protect' = h_protect .
  SET PROPERTY OF h_protect  'Structure' = 'True'.
  SET PROPERTY OF h_protect  'Windows' = 'False'.

  SET PROPERTY OF h_excel  'Visible' = 1.

  FREE OBJECT h_excel.
  PERFORM err_hdl.
ENDFORM.                    " f_stop_excel
*&---------------------------------------------------------------------*
*&      Form  ERR_HDL
*&---------------------------------------------------------------------*
FORM err_hdl.
  IF sy-subrc <> 0.
    WRITE: / 'OLE Error :'(010), sy-subrc.
    STOP.
  ENDIF.
ENDFORM.                    " ERR_HDL


*&---------------------------------------------------------------------*
*&      Form  f_display_hdr
*&---------------------------------------------------------------------*
FORM f_display_hdr .
  row = 2 .
  PERFORM fill_cell USING row 3 'OHO SHEET'  1 35 1 1 0 16 0 1.
  PERFORM f_merge_cells USING 'A' 'E' -4108 .

  row = row + 2  .
  PERFORM fill_cell USING row 3 'Program Details'  1 43 1 1 0 12 0 1.
  PERFORM f_merge_cells USING 'A' 'E' -4108 .

ENDFORM.                    " f_display_hdr
*---------------------------------------------------------------------*
*       FORM FILL_CELL                                                *
*---------------------------------------------------------------------*
FORM fill_cell USING p_row    p_col    p_val  p_shrink p_bkclr p_pat
                     p_bold   p_italic p_size p_fclr   p_uline .

  CALL METHOD OF h_excel 'Cells' = h_cell
    EXPORTING
    #1 = p_row
    #2 = p_col.

  SET PROPERTY OF h_cell 'Value'       = p_val .
  SET PROPERTY OF h_cell 'ShrinkToFit' = p_shrink .

  GET PROPERTY OF h_cell 'Interior'   = h_int.
  SET PROPERTY OF h_int  'ColorIndex' = p_bkclr .
  SET PROPERTY OF h_int  'Pattern'    = p_pat.

  GET PROPERTY OF h_cell 'Font'    = h_f.
  SET PROPERTY OF h_f 'Bold'       = p_bold .
  SET PROPERTY OF h_f 'Italic'     = p_italic .
  SET PROPERTY OF h_f 'Size'       = p_size .
  SET PROPERTY OF h_f 'ColorIndex' = p_fclr .
  SET PROPERTY OF h_f 'Name'       = 'Arial' .
  SET PROPERTY OF h_f 'Underline'  = p_uline .
ENDFORM.                    "FILL_CELL
*&---------------------------------------------------------------------*
*&      Form  f_merge_cells
*&---------------------------------------------------------------------*
FORM f_merge_cells USING p_r1 p_r2 p_val.
  CLEAR : v_r1, v_r2, v_range1, v_range2 .

  v_range1 = row .
  v_range2 = row .
  SHIFT v_range1 LEFT DELETING LEADING ' ' .
  SHIFT v_range2 LEFT DELETING LEADING ' ' .

  CONCATENATE p_r1 v_range1 INTO v_r1 .
  CONCATENATE p_r2 v_range2 INTO v_r2 .

  CALL METHOD OF h_excel 'Range' = h_range
    EXPORTING
    #1 = v_r1
    #2 = v_r2.
  CALL METHOD OF h_range 'Select' = h_select .
  CALL METHOD OF h_range 'Merge' = h_merge .
  SET PROPERTY OF h_range 'HorizontalAlignment' = p_val.
ENDFORM.                    " f_merge_cells
*&---------------------------------------------------------------------*
*&      Form  f_progress_bar
*&---------------------------------------------------------------------*
FORM f_progress_bar  USING value(p_text) p_val.
  WAIT UP TO p_val SECONDS.
  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
       EXPORTING
*       PERCENTAGE = 0
       text       = p_text
   EXCEPTIONS
        OTHERS     = 1.
  CLEAR v_prog .
ENDFORM.                    " f_progress_bar
*&--------------------------------------------------------------------*
*&      Form  f_make_border
*&--------------------------------------------------------------------*
FORM f_make_border USING p_r2 .
  v_range2 = row .
  SHIFT v_range2 LEFT DELETING LEADING ' ' .

  CONCATENATE p_r2 v_range2 INTO v_r2 .

  CALL METHOD OF h_excel 'Range' = h_range
    EXPORTING
    #1 = v_range1
    #2 = v_r2.
  CALL METHOD OF h_range 'Select' = h_select .
*left
  CALL METHOD OF h_range 'BORDERS' = h_borders
    EXPORTING
    #1 = '1'.
  SET PROPERTY OF h_borders 'LineStyle' = '1'.
  SET PROPERTY OF h_borders 'WEIGHT' = '3'.                 "4=max
  FREE OBJECT h_borders.
* right
  CALL METHOD OF h_range 'BORDERS' = h_borders
    EXPORTING
    #1 = '2'.
  SET PROPERTY OF h_borders 'LineStyle' = '1'.
  SET PROPERTY OF h_borders 'WEIGHT' = '3'.
  FREE OBJECT h_borders.
* top
  CALL METHOD OF h_range 'BORDERS' = h_borders
    EXPORTING
    #1 = '3'.
  SET PROPERTY OF h_borders 'LineStyle' = '1'.
  SET PROPERTY OF h_borders 'WEIGHT' = '3'.
  FREE OBJECT h_borders.
* bottom
  CALL METHOD OF h_range 'BORDERS' = h_borders
    EXPORTING
    #1 = '4'.
  SET PROPERTY OF h_borders 'LineStyle' = '1'.
  SET PROPERTY OF h_borders 'WEIGHT' = '3'.
  FREE OBJECT h_borders.
ENDFORM.                    "f_make_border

thanx

Former Member · ‎2007 Jul 17

Hello,

Try this code. This will move data to excel as pasword protected.

Reward if found helpfull.

Regards,

rakesh.


DATA: it_t001 TYPE t001 OCCURS 0,
it_fields TYPE dfies OCCURS 0 WITH HEADER LINE,

BEGIN OF it_fnames OCCURS 0,
reptext LIKE dfies-reptext,
END OF it_fnames.
* The structure definition required for this internal table.
* The following definition is not working:
* it_fnames like dfies-reptext occurs 0 with header line.
* because Function Module will use the following:
* ASSIGN COMPONENT 1 OF STRUCTURE FIELDNAMES TO <F>.

SELECT * FROM t001 INTO TABLE it_t001.

CALL FUNCTION 'GET_FIELDTAB'
EXPORTING
* LANGU = SY-LANGU
* ONLY = ' '
tabname = 'T001'
* WITHTEXT = 'X'
* IMPORTING
* HEADER =
* RC =
TABLES
fieldtab = it_fields
EXCEPTIONS
internal_error = 1
no_texts_found = 2
table_has_no_fields = 3
table_not_activ = 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.

LOOP AT it_fields.
  it_fnames-reptext = it_fields-reptext.
  APPEND it_fnames.
ENDLOOP.

*CALL FUNCTION 'EXCEL_OLE_STANDARD_DAT'
*EXPORTING
*FILE_NAME = 'D:Oletrial'
** CREATE_PIVOT = 0
*DATA_SHEET_NAME = 'Companies'
** PIVOT_SHEET_NAME = ' '
** PASSWORD = 'NISHA'
** PASSWORD_OPTION = 0
*TABLES
** PIVOT_FIELD_TAB =
*DATA_TAB = it_t001
*FIELDNAMES = it_fnames
*EXCEPTIONS
*FILE_NOT_EXIST = 1
*FILENAME_EXPECTED = 2
*COMMUNICATION_ERROR = 3
*OLE_OBJECT_METHOD_ERROR = 4
*OLE_OBJECT_PROPERTY_ERROR = 5
*INVALID_FILENAME = 6
*INVALID_PIVOT_FIELDS = 7
*DOWNLOAD_PROBLEM = 8
*OTHERS = 9
*.
*IF SY-SUBRC <> 0.
*MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
*ENDIF.
*
BREAK-POINT.
CALL FUNCTION 'MS_EXCEL_OLE_STANDARD_DAT'
EXPORTING
file_name = 'D:New Foldernisha'
* CREATE_PIVOT = 0
data_sheet_name = 'Companies 2'
* PIVOT_SHEET_NAME = 'd:nisha'
 password = 'NISHA'
 password_option = 1
TABLES
* PIVOT_FIELD_TAB =
data_tab = it_t001
*FIELDNAMES = it_fnames
EXCEPTIONS
file_not_exist = 1
filename_expected = 2
communication_error = 3
ole_object_method_error = 4
ole_object_property_error = 5
invalid_filename = 6
invalid_pivot_fields = 7
download_problem = 8
OTHERS = 9
.
IF sy-subrc <> 0.
  MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
  WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.

Former Member · ‎2007 Jul 17

Hi,

Try the following methods:

Get the worksheet, like 'Sheet1'

CALL METHOD OF wf_excel 'WORKSHEETS' = wf_worksheet

EXPORTING

#1 = 'Sheet1'.

By default all cells in the sheet are marked as 'LOCKED' which means
that after protecting the sheet, those cells become locked.
Unlock any cells that need to remain open after protecting

SET PROPERTY OF wf_range 'LOCKED' = 0.

Then protect the worksheet with a password
This will lock all cells which has the 'LOCKED' property ON

CALL METHOD OF wf_worksheet 'PROTECT'

EXPORTING

#1 = 'ANYPWD' " Password

#2 = 1 " Protect Drawing Objects

#3 = 1 " Protect Contents

#4 = 1. " Protect Scenarios

Best Regards,

Rao A

Former Member · ‎2007 Jul 17

Hi,

Add enque function just after u start the excel sheet and add deque function just after the proces of upload is complete.

I hope u know the locking concept.

pls reward points if useful.

regards,

Ameet

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My SAP Profile

My SAP Profile

Report in Excel