*&---------------------------------------------------------------------*
*& Report  Z_GRAPHIC_IN_EXCEL                                          *
*&                                                                     *
*&---------------------------------------------------------------------*
*& Создание ярких графических элементов с несколькими закладками       *
*&                                                                     *
*&---------------------------------------------------------------------*

REPORT  z_graphics_in_excel  NO STANDARD PAGE HEADING
.

INCLUDE ole2incl .
DATA: gs_excel TYPE ole2_object ,
gs_wbooklist TYPE ole2_object ,
gs_application TYPE ole2_object ,
gs_wbook TYPE ole2_object ,
gs_activesheet TYPE ole2_object ,
gs_sheets TYPE ole2_object ,
gs_newsheet TYPE ole2_object ,
gs_cell1 TYPE ole2_object ,
gs_cell2 TYPE ole2_object ,
gs_cells TYPE ole2_object ,
gs_range TYPE ole2_object ,
gs_font TYPE ole2_object ,
gs_interior TYPE ole2_object ,
gs_columns TYPE ole2_object ,
gs_charts TYPE ole2_object ,
gs_chart TYPE ole2_object ,
gs_charttitle TYPE ole2_object ,
gs_charttitlechar TYPE ole2_object ,
gs_chartobjects TYPE ole2_object .

DATA gv_sheet_name(20) TYPE c .
DATA gv_outer_index LIKE sy-index .
DATA gv_intex(2) TYPE c .
DATA gv_line_cntr TYPE i . "line counter
DATA gv_linno TYPE i . "line number
DATA gv_colno TYPE i . "column number
DATA gv_value TYPE i . "data
PARAMETERS: p_sheets TYPE i .


START-OF-SELECTION .
  DO p_sheets TIMES .
*--Forming sheet name
    gv_intex = sy-index .
    gv_outer_index = sy-index .
    CONCATENATE 'Лист #' gv_intex INTO gv_sheet_name .

*--For the first loop, Excel is initiated and one new sheet is added
    IF sy-index = 1 .
      CREATE OBJECT gs_excel 'EXCEL.APPLICATION' .
      SET PROPERTY OF gs_excel 'Visible' = 1 .
      GET PROPERTY OF gs_excel 'Workbooks' = gs_wbooklist .
      GET PROPERTY OF gs_wbooklist 'Application' = gs_application .
      SET PROPERTY OF gs_application 'SheetsInNewWorkbook' = 1 .
      CALL METHOD OF gs_wbooklist 'Add' = gs_wbook .

      GET PROPERTY OF gs_application 'ActiveSheet' = gs_activesheet .
      SET PROPERTY OF gs_activesheet 'Name' = gv_sheet_name .

*--For the rest of loops, other sheets are added
    ELSE .
      GET PROPERTY OF gs_wbook 'Sheets' = gs_sheets .
      CALL METHOD OF gs_sheets 'Add' = gs_newsheet .
      SET PROPERTY OF gs_newsheet 'Name' = gv_sheet_name .
    ENDIF .
    gv_line_cntr = 1 . "line counter

*--Title
*--Selecting cell area to be merged.
    CALL METHOD OF gs_excel 'Cells' = gs_cell1
      EXPORTING
      #1 = 1
      #2 = 1.
    CALL METHOD OF gs_excel 'Cells' = gs_cell2
      EXPORTING
      #1 = 1
      #2 = 4.
    CALL METHOD OF gs_excel 'Range' = gs_cells
      EXPORTING
      #1 = gs_cell1
      #2 = gs_cell2.
    CALL METHOD OF gs_cells 'Select' .
*--Merging
    CALL METHOD OF gs_cells 'Merge' .
*--Setting title data
    CALL METHOD OF gs_excel 'Cells' = gs_cell1
      EXPORTING
      #1 = gv_line_cntr
      #2 = 1.
    SET PROPERTY OF gs_cell1 'Value' = 'Igor Garanichev'.


*--Formatting the title
    GET PROPERTY OF gs_cell1 'Font' = gs_font .
    SET PROPERTY OF gs_font 'Underline' = 2 .
    SET PROPERTY OF gs_font 'Bold' = 1 .
    SET PROPERTY OF gs_cell1 'HorizontalAlignment' = -4108 .
    GET PROPERTY OF gs_cell1 'Interior' = gs_interior .
    SET PROPERTY OF gs_interior 'ColorIndex' = 15 .
    SET PROPERTY OF gs_interior 'Pattern' = -4124 .
    SET PROPERTY OF gs_interior 'PatternColorIndex' = -4105 .


    gv_line_cntr = gv_line_cntr + 1 .
*--Writing some additional data for the title
    CALL METHOD OF gs_excel 'Cells' = gs_cell1
      EXPORTING
      #1 = gv_line_cntr
      #2 = 1.
    SET PROPERTY OF gs_cell1 'Value' = 'Номер листочка' .
    CALL METHOD OF gs_excel 'Cells' = gs_cell1
      EXPORTING
      #1 = gv_line_cntr
      #2 = 5.
    SET PROPERTY OF gs_cell1 'Value' = ':' .
    CALL METHOD OF gs_excel 'Cells' = gs_cell1
      EXPORTING
      #1 = gv_line_cntr
      #2 = 6.
    SET PROPERTY OF gs_cell1 'Value' = gv_intex .
*--Formatting the area of additional data 1
    CALL METHOD OF gs_excel 'Cells' = gs_cell1
      EXPORTING
      #1 = 1
      #2 = 1.
    CALL METHOD OF gs_excel 'Cells' = gs_cell2
      EXPORTING
      #1 = gv_line_cntr
      #2 = 5.
    CALL METHOD OF gs_excel 'Range' = gs_cells
      EXPORTING
      #1 = gs_cell1
      #2 = gs_cell2.
    CALL METHOD OF gs_cells 'Select' .
    GET PROPERTY OF gs_cells 'Font' = gs_font .
    SET PROPERTY OF gs_font 'Bold' = 1 .

*--Formatting the area of additional data 2
    CALL METHOD OF gs_excel 'Cells' = gs_cell1
      EXPORTING
      #1 = 1
      #2 = 5.
    CALL METHOD OF gs_excel 'Cells' = gs_cell2
      EXPORTING
      #1 = gv_line_cntr
      #2 = 5.
    CALL METHOD OF gs_excel 'Range' = gs_cells
      EXPORTING
      #1 = gs_cell1
      #2 = gs_cell2.
    CALL METHOD OF gs_cells 'Select' .
    GET PROPERTY OF gs_cells 'Columns' = gs_columns .
    CALL METHOD OF gs_columns 'AutoFit' .
*--Bordering title data area
    CALL METHOD OF gs_excel 'Cells' = gs_cell1
      EXPORTING
      #1 = 1
      #2 = 1.
    CALL METHOD OF gs_excel 'Cells' = gs_cell2
      EXPORTING
      #1 = gv_line_cntr
      #2 = 6.
    CALL METHOD OF gs_excel 'Range' = gs_cells
      EXPORTING
      #1 = gs_cell1
      #2 = gs_cell2.
    CALL METHOD OF gs_cells 'Select' .
    CALL METHOD OF gs_cells 'BorderAround'
      EXPORTING
        #1 = 1 "continuous line
        #2 = 4. "thick

*--Putting axis labels
    gv_colno = 2 .
    gv_line_cntr = gv_line_cntr + 5 .
    gv_linno = gv_line_cntr - 1 .
    CALL METHOD OF gs_excel 'Cells' = gs_cell1
      EXPORTING
      #1 = gv_linno
      #2 = 1.
    SET PROPERTY OF gs_cell1 'Value' = 'X' .
    CALL METHOD OF gs_excel 'Cells' = gs_cell1
      EXPORTING
      #1 = gv_line_cntr
      #2 = 1.
    SET PROPERTY OF gs_cell1 'Value' = 'Y' .

*--Generating some data
    DO 3 TIMES .
      gv_value = gv_outer_index * sy-index * 10 .
      CALL METHOD OF gs_excel 'Cells' = gs_cell1
        EXPORTING
        #1 = gv_linno
        #2 = gv_colno.
      SET PROPERTY OF gs_cell1 'Value' = sy-index .
      CALL METHOD OF gs_excel 'Cells' = gs_cell1
        EXPORTING
        #1 = gv_line_cntr
        #2 = gv_colno.
      SET PROPERTY OF gs_cell1 'Value' = gv_value .
      gv_colno = gv_colno + 1 .
    ENDDO .

*--Source data area
    gv_colno = gv_colno - 1 .
    CALL METHOD OF gs_excel 'Cells' = gs_cell1
      EXPORTING
      #1 = gv_linno
      #2 = 1.
    CALL METHOD OF gs_excel 'Cells' = gs_cell2
      EXPORTING
      #1 = gv_line_cntr
      #2 = gv_colno.
    CALL METHOD OF gs_excel 'Range' = gs_cells
      EXPORTING
      #1 = gs_cell1
      #2 = gs_cell2.
    CALL METHOD OF gs_cells 'Select' .

    GET PROPERTY OF gs_application 'Charts' = gs_charts .
    CALL METHOD OF gs_charts 'Add' = gs_chart .
    CALL METHOD OF gs_chart 'Activate' .
    SET PROPERTY OF gs_chart 'ChartType' = '51' . "Vertical bar graph
    CALL METHOD OF gs_chart 'SetSourceData'
      EXPORTING
      #1 = gs_cells
      #2 = 1.
    SET PROPERTY OF gs_chart 'HasTitle' = 1 .
    GET PROPERTY OF gs_chart 'ChartTitle' = gs_charttitle .
    GET PROPERTY OF gs_charttitle 'Characters' = gs_charttitlechar .
    SET PROPERTY OF gs_charttitlechar 'Text' = 'График' .

*--Locate the chart onto the current worksheet
*--Activate current sheet
    CALL METHOD OF gs_excel 'WorkSheets' = gs_activesheet
      EXPORTING
      #1 = gv_sheet_name.
    CALL METHOD OF gs_activesheet 'Activate' .
    CALL METHOD OF gs_chart 'Location'
      EXPORTING
      #1 = 2
      #2 = gv_sheet_name.


*--Reposition the chart on the worksheet (cut&paste)
    CALL METHOD OF gs_activesheet 'ChartObjects' = gs_chartobjects .
    CALL METHOD OF gs_chartobjects 'Select' .
    CALL METHOD OF gs_chartobjects 'Cut' .
*--Select new area
    gv_line_cntr = gv_line_cntr + 2 .
    CALL METHOD OF gs_excel 'Cells' = gs_cell1
      EXPORTING
      #1 = gv_line_cntr
      #2 = 1.
    CALL METHOD OF gs_excel 'Cells' = gs_cell2
      EXPORTING
      #1 = gv_line_cntr
      #2 = 1.
    CALL METHOD OF gs_excel 'Range' = gs_cells
      EXPORTING
      #1 = gs_cell1
      #2 = gs_cell2.
    CALL METHOD OF gs_cells 'Select' .
    CALL METHOD OF gs_activesheet 'Paste' .
  ENDDO.

*--Deallocating memory
  FREE: gs_excel, gs_wbooklist, gs_application, gs_wbook,
  gs_activesheet,gs_sheets, gs_newsheet, gs_cell1,
  gs_cell2, gs_cells, gs_range, gs_font, gs_interior,
  gs_columns, gs_charts, gs_chart, gs_charttitle,
  gs_charttitlechar, gs_chartobjects .