Download output to excel in graphical form.

[Former Member]

I have report whose output has to be download to an Excel Sheet with graphs such as Bar charts and Pie Charts. Can anyone tell me how i can do this?

[Former Member]

Hi Sujana,

we make an excel-chart like this:

#####################

• Datenbereich puffern

CALL METHOD OF H_DRANGE 'Copy'.

PERFORM ERR_HDL.

• Chart erstellen, Daten einfügen und aktivieren

CALL METHOD OF H_EXCEL 'Charts' = H_CHARTS.

PERFORM ERR_HDL.

CALL METHOD OF H_CHARTS 'Add' = H_CHART.

PERFORM ERR_HDL.

CALL METHOD OF H_CHART 'SeriesCollection' = H_SECOL.

PERFORM ERR_HDL.

CALL METHOD OF H_SECOL 'Paste'

EXPORTING #1 = 2 "xlSpalten

#2 = 1 "True

#3 = 0. "False

PERFORM ERR_HDL.

CALL METHOD OF H_CHART 'Activate'.

PERFORM ERR_HDL.

• Chart formatieren Typ 52

SET PROPERTY OF H_CHART 'ChartType' = TYP.

PERFORM ERR_HDL.

• Sheet-Name

SET PROPERTY OF H_CHART 'Name' = 'Diagramm'.

PERFORM ERR_HDL.

• Chart Titel

SET PROPERTY OF H_CHART 'HasTitle' = 1.

PERFORM ERR_HDL.

• SET PROPERTY OF H_ACHSE 'HasLegend' = 0.

• PERFORM ERR_HDL.

GET PROPERTY OF H_CHART 'ChartTitle' = H_TITEL.

PERFORM ERR_HDL.

SET PROPERTY OF H_TITEL 'Caption' = TITEL.

PERFORM ERR_HDL.

• Achsenbezeichnung X-Achse

CALL METHOD OF H_CHART 'Axes' = H_ACHSE

EXPORTING #1 = 1.

SET PROPERTY OF H_ACHSE 'HasTitle' = 1.

PERFORM ERR_HDL.

GET PROPERTY OF H_ACHSE 'AxisTitle' = H_XTITEL.

PERFORM ERR_HDL.

SET PROPERTY OF H_XTITEL 'Caption' = XTITEL.

PERFORM ERR_HDL.

SET PROPERTY OF H_ACHSE 'CategoryNames' = H_CRANGE.

PERFORM ERR_HDL.

• Achsenbezeichnung Y-Achse

CALL METHOD OF H_CHART 'Axes' = H_ACHSE

EXPORTING #1 = 2.

SET PROPERTY OF H_ACHSE 'HasTitle' = 1.

PERFORM ERR_HDL.

GET PROPERTY OF H_ACHSE 'AxisTitle' = H_YTITEL.

PERFORM ERR_HDL.

SET PROPERTY OF H_YTITEL 'Caption' = YTITEL.

PERFORM ERR_HDL.

#####################

If you have Problems with OLE write it.

Hope i can help you

Regards, Dieter

Here are a good link:

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/c1d54348-0601-0010-3e98-bd2...

Regards, Dieter

Message was edited by: Dieter Gröhn

[Former Member]

just refer this programm....

copy and paste this to new programm and see the output..

&----

---

*& Report ZNEGI9 *

*& *

&----

---

*& *

*& *

&----

---

REPORT ZNEGI9 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 'Excel Sheet #' 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' = 'KISHAN' .

*--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' = 'Sheet No' .

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' = 'Sample Graph' .

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

[Former Member]

Hi Kishan,

Thanks for your example code. I had a similar requirement and this code helped me to understand the concept for developing my report.

Regards,

NagaRaju.