bdc

[Former Member]

Hi all,

I have to Create a program to upload data from an excel spreadsheet. The selection screen should contain Parameter for the file name.Checkbox to run in test mode. The program should default the checkbox to true. So the program is not accidentally run.Checkbox to display the excel data, defaulted to true.

Can anybody give me the code ?

Thanks

[Former Member]

Try below code...

&----

---

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

<b>Reward points</b>

Regards