03-05-2009 9:44 AM
Hi All,
Can any one help me to create an multiple excel sheet in an work book using OLE. I want to create more than 3 sheets in excel, which is default sheets in excel when we one excel.
03-05-2009 10:24 AM
Hi,
Check the below sample code.
Here I'm downloading the same table twice into 2 different sheets for example purpose.
TYPE-POOLS ole2.
DATA: wf_cell_from TYPE ole2_object,
wf_cell_from1 TYPE ole2_object,
wf_cell_to TYPE ole2_object,
wf_cell_to1 TYPE ole2_object,
wf_excel TYPE ole2_object, " Excel object
wf_mapl TYPE ole2_object, " list of workbooks
wf_map TYPE ole2_object, " workbook
wf_worksheet TYPE ole2_object, " Worksheet
wf_cell TYPE ole2_object, " Cell Range
wf_cell1 TYPE ole2_object,
wf_range TYPE ole2_object, " Range of cells to be formatted
wf_range2 TYPE ole2_object,
wf_column1 TYPE ole2_object. " Column to be Autofit
DATA: BEGIN OF t_hex,
l_tab TYPE x,
END OF t_hex.
DATA: wf_deli(1) TYPE c, "delimiter
wf_action TYPE i,
wf_file TYPE string,
wf_path TYPE string,
wf_fullpath TYPE string.
TYPES: t_data1(1500) TYPE c,
int_ty TYPE TABLE OF t_data1. "line type internal table
*All the data was prepared as line type internal tables for faster
*download
DATA: int_matl TYPE int_ty ,
int_matl1 TYPE int_ty ,
wa_matl TYPE t_data1.
TYPES: BEGIN OF ty_mara,
matnr TYPE matnr,
mtart TYPE mtart,
matkl TYPE matkl,
meins TYPE meins,
END OF ty_mara.
DATA: int_mara TYPE STANDARD TABLE OF ty_mara,
wa_mara TYPE ty_mara.
FIELD-SYMBOLS: <fs> .
DATA: wc_sheets LIKE sy-index. "no.of sheets
DATA: it_tabemp TYPE filetable,
gd_subrcemp TYPE i.
CONSTANTS wl_c09(2) TYPE n VALUE 09.
CLEAR wc_sheets.
DEFINE ole_check_error.
if &1 ne 0.
message e001(zz) with &1.
exit.
endif.
END-OF-DEFINITION.
SELECTION-SCREEN BEGIN OF BLOCK block1 WITH FRAME TITLE text-001.
PARAMETERS: p_file LIKE rlgrap-filename.
SELECTION-SCREEN END OF BLOCK block1.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
REFRESH: it_tabemp.
CALL METHOD cl_gui_frontend_services=>file_save_dialog
EXPORTING
window_title = 'Select File'
* default_extension = 'xls'
default_file_name = 'Material Details'
* with_encoding =
file_filter = '*.xls'
initial_directory = 'C:\'
prompt_on_overwrite = ' '
CHANGING
filename = wf_file
path = wf_path
fullpath = wf_fullpath
user_action = wf_action
* file_encoding =
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4
.
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
IF wf_action EQ 9.
MESSAGE 'No File have been Selected' TYPE 'S'.
ELSE.
p_file = wf_fullpath.
PERFORM create_excel.
ENDIF.
*&---------------------------------------------------------------------*
*& Form create_excel
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM create_excel.
LOOP AT it_tabemp INTO p_file.
ENDLOOP.
* START THE EXCEL APPLICATION
CREATE OBJECT wf_excel 'EXCEL.APPLICATION'.
PERFORM err_hdl.
* PUT EXCEL IN FRONT
SET PROPERTY OF wf_excel 'VISIBLE' = 1.
PERFORM err_hdl.
* CREATE AN EXCEL WORKBOOK OBJECT
CALL METHOD OF wf_excel 'WORKBOOKS' = wf_mapl.
PERFORM err_hdl.
SET PROPERTY OF wf_excel 'SheetsInNewWorkbook' = 3. "no of sheets
PERFORM err_hdl.
CALL METHOD OF wf_mapl 'ADD' = wf_map.
PERFORM err_hdl.
*Assign the Delimiter to field symbol.
ASSIGN wf_deli TO <fs> TYPE 'X'.
t_hex-l_tab = wl_c09.
<fs> = t_hex-l_tab.
CLEAR int_matl.
REFRESH int_matl.
SELECT matnr
mtart
matkl
meins
FROM mara
INTO CORRESPONDING FIELDS OF TABLE int_mara.
*first the headings will be displayed in the excel sheet
CONCATENATE 'Material Number'
'Material type'
'Material Group'
'Base Unit of Measure'
INTO wa_matl
SEPARATED BY wf_deli.
APPEND wa_matl TO int_matl.
LOOP AT int_mara INTO wa_mara.
CONCATENATE wa_mara-matnr
wa_mara-mtart
wa_mara-matkl
wa_mara-meins
INTO wa_matl
SEPARATED BY wf_deli.
APPEND wa_matl TO int_matl.
CLEAR wa_matl.
ENDLOOP.
*Copyng thae same contents to another table to display in
*new sheet
MOVE int_matl TO int_matl1.
PERFORM f_material_details
TABLES int_matl
USING 1.
PERFORM f_material_details
TABLES int_matl
USING 2.
GET PROPERTY OF wf_excel 'ActiveSheet' = wf_map.
GET PROPERTY OF wf_excel 'ActiveWorkbook' = wf_mapl.
CALL FUNCTION 'FLUSH'
EXCEPTIONS
cntl_system_error = 1
cntl_error = 2
OTHERS = 3.
IF sy-subrc = 0.
CALL METHOD OF wf_map 'SAVEAS'
EXPORTING #1 = p_file.
ENDIF.
CALL METHOD OF wf_mapl 'CLOSE'.
CALL METHOD OF wf_excel 'QUIT'.
FREE OBJECT wf_mapl.
FREE OBJECT wf_map.
FREE OBJECT wf_excel.
ENDFORM. "create_excel
*&---------------------------------------------------------------------*
*& Form ERR_HDL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM err_hdl.
IF sy-subrc <> 0.
WRITE: / 'OLE ERROR: RETURN CODE ='(i10), sy-subrc.
STOP.
ENDIF.
ENDFORM. "ERR_HDL
*-- End of Program
*&---------------------------------------------------------------------*
*& Form f_material_details
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM f_material_details
TABLES lint_matl
USING l_sheet_no TYPE i.
DATA: lv_lines TYPE i,
lv_sheet_name(50) TYPE c.
wc_sheets = l_sheet_no.
CASE l_sheet_no.
WHEN 1.
lv_sheet_name = 'Material_sheet1'.
WHEN 2.
lv_sheet_name = 'Material_sheet2'.
ENDCASE.
*-- activating the worksheet and giving a name to it
CALL METHOD OF wf_excel 'WORKSHEETS' = wf_worksheet
EXPORTING
#1 = wc_sheets.
CALL METHOD OF wf_worksheet 'ACTIVATE'.
SET PROPERTY OF wf_worksheet 'NAME' = lv_sheet_name.
*--formatting the cells
CALL METHOD OF wf_excel 'Cells' = wf_cell_from
EXPORTING
#1 = 1
#2 = 1.
DESCRIBE TABLE lint_matl LINES lv_lines.
CALL METHOD OF wf_excel 'Cells' = wf_cell_to
EXPORTING
#1 = lv_lines
#2 = 4.
*--range of cells to be formatted (in this case 1 to 4)
CALL METHOD OF wf_excel 'Range' = wf_cell
EXPORTING
#1 = wf_cell_from
#2 = wf_cell_to.
*--formatting the cells
CALL METHOD OF wf_excel 'Cells' = wf_cell_from1
EXPORTING
#1 = 1
#2 = 1.
DESCRIBE TABLE lint_matl LINES lv_lines.
CALL METHOD OF wf_excel 'Cells' = wf_cell_to1
EXPORTING
#1 = lv_lines
#2 = 1.
CALL METHOD OF wf_excel 'Range' = wf_cell1 " Cell range for first
" column(Material)
EXPORTING
#1 = wf_cell_from1
#2 = wf_cell_to1.
SET PROPERTY OF wf_cell1 'NumberFormat' = '@' . "To disply zeros
"in Material number
DATA l_rc TYPE i.
*DATA download into excel first sheet
CALL METHOD cl_gui_frontend_services=>clipboard_export
IMPORTING
data = lint_matl[]
CHANGING
rc = l_rc
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
OTHERS = 4.
CALL METHOD OF wf_worksheet 'Paste'.
CALL METHOD OF wf_excel 'Columns' = wf_column1.
CALL METHOD OF wf_column1 'Autofit'.
FREE OBJECT wf_column1.
ENDFORM. " f_material_details
Regards,
Manoj Kumar P
Edited by: Manoj Kumar on Mar 5, 2009 11:25 AM
03-05-2009 10:24 AM
Hi,
Check the below sample code.
Here I'm downloading the same table twice into 2 different sheets for example purpose.
TYPE-POOLS ole2.
DATA: wf_cell_from TYPE ole2_object,
wf_cell_from1 TYPE ole2_object,
wf_cell_to TYPE ole2_object,
wf_cell_to1 TYPE ole2_object,
wf_excel TYPE ole2_object, " Excel object
wf_mapl TYPE ole2_object, " list of workbooks
wf_map TYPE ole2_object, " workbook
wf_worksheet TYPE ole2_object, " Worksheet
wf_cell TYPE ole2_object, " Cell Range
wf_cell1 TYPE ole2_object,
wf_range TYPE ole2_object, " Range of cells to be formatted
wf_range2 TYPE ole2_object,
wf_column1 TYPE ole2_object. " Column to be Autofit
DATA: BEGIN OF t_hex,
l_tab TYPE x,
END OF t_hex.
DATA: wf_deli(1) TYPE c, "delimiter
wf_action TYPE i,
wf_file TYPE string,
wf_path TYPE string,
wf_fullpath TYPE string.
TYPES: t_data1(1500) TYPE c,
int_ty TYPE TABLE OF t_data1. "line type internal table
*All the data was prepared as line type internal tables for faster
*download
DATA: int_matl TYPE int_ty ,
int_matl1 TYPE int_ty ,
wa_matl TYPE t_data1.
TYPES: BEGIN OF ty_mara,
matnr TYPE matnr,
mtart TYPE mtart,
matkl TYPE matkl,
meins TYPE meins,
END OF ty_mara.
DATA: int_mara TYPE STANDARD TABLE OF ty_mara,
wa_mara TYPE ty_mara.
FIELD-SYMBOLS: <fs> .
DATA: wc_sheets LIKE sy-index. "no.of sheets
DATA: it_tabemp TYPE filetable,
gd_subrcemp TYPE i.
CONSTANTS wl_c09(2) TYPE n VALUE 09.
CLEAR wc_sheets.
DEFINE ole_check_error.
if &1 ne 0.
message e001(zz) with &1.
exit.
endif.
END-OF-DEFINITION.
SELECTION-SCREEN BEGIN OF BLOCK block1 WITH FRAME TITLE text-001.
PARAMETERS: p_file LIKE rlgrap-filename.
SELECTION-SCREEN END OF BLOCK block1.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
REFRESH: it_tabemp.
CALL METHOD cl_gui_frontend_services=>file_save_dialog
EXPORTING
window_title = 'Select File'
* default_extension = 'xls'
default_file_name = 'Material Details'
* with_encoding =
file_filter = '*.xls'
initial_directory = 'C:\'
prompt_on_overwrite = ' '
CHANGING
filename = wf_file
path = wf_path
fullpath = wf_fullpath
user_action = wf_action
* file_encoding =
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4
.
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
IF wf_action EQ 9.
MESSAGE 'No File have been Selected' TYPE 'S'.
ELSE.
p_file = wf_fullpath.
PERFORM create_excel.
ENDIF.
*&---------------------------------------------------------------------*
*& Form create_excel
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM create_excel.
LOOP AT it_tabemp INTO p_file.
ENDLOOP.
* START THE EXCEL APPLICATION
CREATE OBJECT wf_excel 'EXCEL.APPLICATION'.
PERFORM err_hdl.
* PUT EXCEL IN FRONT
SET PROPERTY OF wf_excel 'VISIBLE' = 1.
PERFORM err_hdl.
* CREATE AN EXCEL WORKBOOK OBJECT
CALL METHOD OF wf_excel 'WORKBOOKS' = wf_mapl.
PERFORM err_hdl.
SET PROPERTY OF wf_excel 'SheetsInNewWorkbook' = 3. "no of sheets
PERFORM err_hdl.
CALL METHOD OF wf_mapl 'ADD' = wf_map.
PERFORM err_hdl.
*Assign the Delimiter to field symbol.
ASSIGN wf_deli TO <fs> TYPE 'X'.
t_hex-l_tab = wl_c09.
<fs> = t_hex-l_tab.
CLEAR int_matl.
REFRESH int_matl.
SELECT matnr
mtart
matkl
meins
FROM mara
INTO CORRESPONDING FIELDS OF TABLE int_mara.
*first the headings will be displayed in the excel sheet
CONCATENATE 'Material Number'
'Material type'
'Material Group'
'Base Unit of Measure'
INTO wa_matl
SEPARATED BY wf_deli.
APPEND wa_matl TO int_matl.
LOOP AT int_mara INTO wa_mara.
CONCATENATE wa_mara-matnr
wa_mara-mtart
wa_mara-matkl
wa_mara-meins
INTO wa_matl
SEPARATED BY wf_deli.
APPEND wa_matl TO int_matl.
CLEAR wa_matl.
ENDLOOP.
*Copyng thae same contents to another table to display in
*new sheet
MOVE int_matl TO int_matl1.
PERFORM f_material_details
TABLES int_matl
USING 1.
PERFORM f_material_details
TABLES int_matl
USING 2.
GET PROPERTY OF wf_excel 'ActiveSheet' = wf_map.
GET PROPERTY OF wf_excel 'ActiveWorkbook' = wf_mapl.
CALL FUNCTION 'FLUSH'
EXCEPTIONS
cntl_system_error = 1
cntl_error = 2
OTHERS = 3.
IF sy-subrc = 0.
CALL METHOD OF wf_map 'SAVEAS'
EXPORTING #1 = p_file.
ENDIF.
CALL METHOD OF wf_mapl 'CLOSE'.
CALL METHOD OF wf_excel 'QUIT'.
FREE OBJECT wf_mapl.
FREE OBJECT wf_map.
FREE OBJECT wf_excel.
ENDFORM. "create_excel
*&---------------------------------------------------------------------*
*& Form ERR_HDL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM err_hdl.
IF sy-subrc <> 0.
WRITE: / 'OLE ERROR: RETURN CODE ='(i10), sy-subrc.
STOP.
ENDIF.
ENDFORM. "ERR_HDL
*-- End of Program
*&---------------------------------------------------------------------*
*& Form f_material_details
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM f_material_details
TABLES lint_matl
USING l_sheet_no TYPE i.
DATA: lv_lines TYPE i,
lv_sheet_name(50) TYPE c.
wc_sheets = l_sheet_no.
CASE l_sheet_no.
WHEN 1.
lv_sheet_name = 'Material_sheet1'.
WHEN 2.
lv_sheet_name = 'Material_sheet2'.
ENDCASE.
*-- activating the worksheet and giving a name to it
CALL METHOD OF wf_excel 'WORKSHEETS' = wf_worksheet
EXPORTING
#1 = wc_sheets.
CALL METHOD OF wf_worksheet 'ACTIVATE'.
SET PROPERTY OF wf_worksheet 'NAME' = lv_sheet_name.
*--formatting the cells
CALL METHOD OF wf_excel 'Cells' = wf_cell_from
EXPORTING
#1 = 1
#2 = 1.
DESCRIBE TABLE lint_matl LINES lv_lines.
CALL METHOD OF wf_excel 'Cells' = wf_cell_to
EXPORTING
#1 = lv_lines
#2 = 4.
*--range of cells to be formatted (in this case 1 to 4)
CALL METHOD OF wf_excel 'Range' = wf_cell
EXPORTING
#1 = wf_cell_from
#2 = wf_cell_to.
*--formatting the cells
CALL METHOD OF wf_excel 'Cells' = wf_cell_from1
EXPORTING
#1 = 1
#2 = 1.
DESCRIBE TABLE lint_matl LINES lv_lines.
CALL METHOD OF wf_excel 'Cells' = wf_cell_to1
EXPORTING
#1 = lv_lines
#2 = 1.
CALL METHOD OF wf_excel 'Range' = wf_cell1 " Cell range for first
" column(Material)
EXPORTING
#1 = wf_cell_from1
#2 = wf_cell_to1.
SET PROPERTY OF wf_cell1 'NumberFormat' = '@' . "To disply zeros
"in Material number
DATA l_rc TYPE i.
*DATA download into excel first sheet
CALL METHOD cl_gui_frontend_services=>clipboard_export
IMPORTING
data = lint_matl[]
CHANGING
rc = l_rc
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
OTHERS = 4.
CALL METHOD OF wf_worksheet 'Paste'.
CALL METHOD OF wf_excel 'Columns' = wf_column1.
CALL METHOD OF wf_column1 'Autofit'.
FREE OBJECT wf_column1.
ENDFORM. " f_material_details
Regards,
Manoj Kumar P
Edited by: Manoj Kumar on Mar 5, 2009 11:25 AM
03-05-2009 11:16 AM
hi,
thnx for the solution.
One more query.
Here number of sheets is set as 3.
Can we dynamically set the number of sheets in the workbook depending on the data we have.
03-05-2009 11:22 AM
Hi,
ya you can.
here for example i called the routine f_material_details as
PERFORM f_material_details
TABLES int_matl
USING 2.
what you can do is you can put this in a loop of table which contains table names and call.
ie.
loop at it_tables into wa_tables.
PERFORM f_material_details
TABLES (wa_tables-tabname) "it_tables contains tbale names in the field tabname
USING sy-tabix. "sy-tabix give the sheet number
endloop.
03-05-2009 11:24 AM
Hi,
Tnx for solution.
Here u have given number of sheets in workbook as 3. Can we dynamically create sheet depending on data we have.
Ie., I want to create 5th sheet dynamically.
03-05-2009 11:31 AM
Hi manoj.
Thnx very much.
Can i contact u personally.
Can u share u r email-id with me.
03-06-2009 3:31 AM
Hi,
Please check my business card to contact me
Edited by: Manoj Kumar on Mar 6, 2009 4:31 AM
03-06-2009 4:36 AM
Hi Preveen,
I did this knid of coding for having 2 internal tables data into 2 sheets of one excel file,
Kindly refer this sample code below:
INCLUDE ole2incl.
DATA: application TYPE ole2_object,
workbook TYPE ole2_object,
sheet TYPE ole2_object,
cells TYPE ole2_object.
CONSTANTS: row_max TYPE i VALUE 256.
DATA index TYPE i.
*DATA: BEGIN OF itab1 OCCURS 0, first_name(10), END OF itab1.
*DATA: BEGIN OF itab2 OCCURS 0, last_name(10), END OF itab2.
DATA: BEGIN OF itab3 OCCURS 0, formula(50), END OF itab3.
*
TABLES: vbap,mara.
TYPES: BEGIN OF itab,
lifnr TYPE lfa1-lifnr,
land1 TYPE lfa1-land1,
* name1 TYPE lfa1-name1,
* ort01 TYPE lfa1-ort01,
END OF itab.
DATA: BEGIN OF itab2 OCCURS 0,
matnr TYPE mara-matnr,
ersda TYPE mara-ersda,
ernam TYPE mara-ernam,
END OF itab2.
data : v_row type sy-tabix.
DATA: itab1 TYPE STANDARD TABLE OF itab WITH HEADER LINE.
DATA: IT_XLSTAB TYPE STANDARD TABLE OF ITAB ,
WA_XLSTAB LIKE LINE OF IT_XLSTAB.
START-OF-SELECTION.
SELECT lifnr land1 fROM lfa1 INTO CORRESPONDING FIELDS OF TABLE itab1 UP TO 5 ROWS.
SELECT matnr
ersda
ernam
FROM mara
INTO CORRESPONDING FIELDS OF
TABLE itab2 UP TO 5 ROWS.
************************************************************************
*START-OF-SELECTION
START-OF-SELECTION.
CREATE OBJECT application 'excel.application'.
SET PROPERTY OF application 'visible' = 1.
CALL METHOD OF application 'Workbooks' = workbook.
CALL METHOD OF workbook 'Add'.
* Create first Excel Sheet
CALL METHOD OF application 'Worksheets' = sheet
EXPORTING #1 = 1.
CALL METHOD OF sheet 'Activate'.
SET PROPERTY OF sheet 'Name' = 'Sheet1'.
clear v_row.
LOOP AT itab1.
v_row = sy-tabix.
perform fill_cell using v_row 1 itab1-lifnr.
perform fill_cell using v_row 2 itab1-land1.
ENDLOOP.
* Create second Excel sheet
CALL METHOD OF application 'Worksheets' = sheet
EXPORTING #1 = 2.
SET PROPERTY OF sheet 'Name' = 'Sheet2'.
CALL METHOD OF sheet 'Activate'.
clear v_row.
LOOP AT itab2.
v_row = sy-tabix.
perform fill_cell using v_row 1 itab2-matnr.
perform fill_cell using v_row 2 itab2-ersda.
perform fill_cell using v_row 3 itab2-ernam.
ENDLOOP.
* Save excel speadsheet to particular filename
CALL METHOD OF sheet 'SaveAs'
EXPORTING #1 = 'c:\temp\excelgeet.xls' "filename
#2 = 1. "fileFormat
* Closes excel window, data is lost if not saved
SET PROPERTY OF application 'visible' = 0.
* call method of sheet 'CLOSE'
*
* EXPORTING #1 = 'YES'.
*&---------------------------------------------------------------------*
*& both the below coding closes the apllication permanently from the task manager also.
*&---------------------------------------------------------------------*
SET PROPERTY OF application 'DisplayAlerts' = 0.
free OBJECT application.
** call method of application 'QUIT'.
**
** FREE OBJECT: APPLICATION,
** SHEET.
form fill_cell using row col val.
CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = row #2 = col.
SET PROPERTY OF cells 'Value' = val.
endform. " fill_cell
Hope it helps
Regrds
Mansi
03-06-2009 5:42 AM
03-06-2009 5:43 AM
Hi,
Making 3 worksheets in one XL
REPORT ZKC_TEST1.
INCLUDE ole2incl.
DATA: w_cell1 TYPE ole2_object,
w_cell2 TYPE ole2_object.
*--- Ole data Declarations
DATA: h_excel TYPE ole2_object, " Excel object
h_mapl TYPE ole2_object, " list of workbooks
h_map TYPE ole2_object, " workbook
h_zl TYPE ole2_object, " cell
h_f TYPE ole2_object, " font
gs_interior TYPE ole2_object, " Pattern
worksheet TYPE ole2_object,
h_cell TYPE ole2_object,
h_cell1 TYPE ole2_object,
range TYPE ole2_object,
h_sheet2 TYPE ole2_object,
h_sheet3 TYPE ole2_object,
gs_font TYPE ole2_object,
e_color TYPE ole2_object,
gs_italic TYPE ole2_object,
flg_stop(1) TYPE c.
*********************************************************************
** Internal table Declaration
*********************************************************************
DATA: BEGIN OF t_excel OCCURS 0,
MATNR type mara-matnr, "(18) type c,
ERSDA type mara-ersda, " (8) type c,
ERNAM type mara-ernam, "(12) type c,
LAEDA type mara-laeda, "(8) type c,
AENAM type mara-aenam, "(12) type c,
VPSTA type mara-vpsta, "(15) type c,
PSTAT type mara-pstat, "(15) type c,
END OF t_excel.
DATA: t_excel_bckord LIKE t_excel OCCURS 0 WITH HEADER LINE,
t_excel_bcklog LIKE t_excel OCCURS 0 WITH HEADER LINE,
t_excel_blkord LIKE t_excel OCCURS 0 WITH HEADER LINE.
data: wa_excel_bckord like line of t_excel.
TYPES: data1(1500) TYPE c,
ty TYPE TABLE OF data1.
DATA: it TYPE ty WITH HEADER LINE,
it_2 TYPE ty WITH HEADER LINE,
it_3 TYPE ty WITH HEADER LINE,
rec TYPE sy-tfill,
deli(1) TYPE c,
l_amt(18) TYPE c.
DATA: BEGIN OF hex,
tab TYPE x,
END OF hex.
FIELD-SYMBOLS: <fs> .
CONSTANTS cns_09(2) TYPE n VALUE 09.
ASSIGN deli TO <fs> TYPE 'X'.
hex-tab = cns_09.
<fs> = hex-tab.
DATA gv_sheet_name(20) TYPE c .
*---selecting into tables
select MATNR
ERSDA
ERNAM
LAEDA
AENAM
VPSTA
PSTAT
from mara into table t_excel_bckord
where pstat = 'KVELBCD'.
wa_excel_bckord-matnr = 'MATNR'.
wa_excel_bckord-ersda = 'ERSDA'.
wa_excel_bckord-ernam = 'ERNAM'.
wa_excel_bckord-laeda = 'LAEDA'.
wa_excel_bckord-aenam = 'AENAM'.
wa_excel_bckord-vpsta = 'VPSTA'.
wa_excel_bckord-pstat = 'PSTAT'.
INSERT wa_excel_bckord
INTO t_excel_bckord INDEX 1 .
select MATNR
ERSDA
ERNAM
LAEDA
AENAM
VPSTA
PSTAT
from mara into table t_excel_bcklog
where pstat = 'KVELBCDP'.
INSERT wa_excel_bckord
INTO t_excel_bcklog INDEX 1 .
select MATNR
ERSDA
ERNAM
LAEDA
AENAM
VPSTA
PSTAT
from mara into table t_excel_blkord
where pstat = 'KEBC'.
INSERT wa_excel_bckord
INTO t_excel_blkord INDEX 1 .
LOOP AT t_excel_bckord.
CONCATENATE
t_excel_bckord-MATNR
t_excel_bckord-ERSDA
t_excel_bckord-ERNAM
t_excel_bckord-laeda
t_excel_bckord-aenam
t_excel_bckord-vpsta
t_excel_bckord-pstat
INTO it
SEPARATED BY deli.
APPEND it.
CLEAR it.
ENDLOOP.
LOOP AT t_excel_bcklog.
CONCATENATE
t_excel_bcklog-matnr
t_excel_bcklog-ersda
t_excel_bcklog-ernam
t_excel_bcklog-laeda
t_excel_bcklog-aenam
t_excel_bcklog-vpsta
t_excel_bcklog-pstat
INTO it_2
SEPARATED BY deli.
APPEND it_2.
CLEAR it_2.
ENDLOOP.
LOOP AT t_excel_blkord.
CONCATENATE
t_excel_blkord-matnr
t_excel_blkord-ersda
t_excel_blkord-ernam
t_excel_blkord-laeda
t_excel_blkord-aenam
t_excel_blkord-vpsta
t_excel_blkord-pstat
INTO it_3
SEPARATED BY deli.
APPEND it_3.
CLEAR it_3.
ENDLOOP.
*--- start Excel
IF h_excel-header = space OR h_excel-handle = -1.
CREATE OBJECT h_excel 'EXCEL.APPLICATION'.
ENDIF.
*--- get list of workbooks, initially empty
CALL METHOD OF h_excel 'Workbooks' = h_mapl.
SET PROPERTY OF h_excel 'Visible' = 1.
CALL METHOD OF h_mapl 'Add' = h_map.
gv_sheet_name = 'Back Orders'.
GET PROPERTY OF h_excel 'ACTIVESHEET' = worksheet.
SET PROPERTY OF worksheet 'Name' = gv_sheet_name .
*--Formatting the area of additional data 1 and doing the BOLD
CALL METHOD OF h_excel 'Cells' = w_cell1
EXPORTING
#1 = 1
#2 = 1.
CALL METHOD OF h_excel 'Cells' = w_cell2
EXPORTING
#1 = 1
#2 = 50.
CALL METHOD OF h_excel 'Range' = h_cell
EXPORTING
#1 = w_cell1
#2 = w_cell2.
GET PROPERTY OF h_cell 'Font' = gs_font .
SET PROPERTY OF gs_font 'Bold' = 1 .
SET PROPERTY OF gs_font 'Name' = 'Arial' .
GET PROPERTY OF h_cell 'Interior' = e_color.
SET PROPERTY OF e_color 'ColorIndex' = 35.
GET PROPERTY OF h_cell 'Font' = gs_italic .
SET PROPERTY OF gs_italic 'Italic' = 1 .
DATA l_rc TYPE i.
CALL METHOD cl_gui_frontend_services=>clipboard_export
IMPORTING
data = it[]
CHANGING
rc = l_rc
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.
CALL METHOD OF h_excel 'Cells' = w_cell1
EXPORTING
#1 = 1
#2 = 1.
CALL METHOD OF h_excel 'Cells' = w_cell2
EXPORTING
#1 = 1
#2 = 1.
CALL METHOD OF h_excel 'Range' = range
EXPORTING
#1 = w_cell1
#2 = w_cell2.
CALL METHOD OF range 'Select'.
CALL METHOD OF worksheet 'Paste'.
gv_sheet_name = 'Backlog'.
GET PROPERTY OF h_excel 'Sheets' = h_sheet2 .
CALL METHOD OF h_sheet2 'Add' = h_map.
SET PROPERTY OF h_map 'Name' = gv_sheet_name .
GET PROPERTY OF h_excel 'ACTIVESHEET' = worksheet.
*--Formatting the area of additional data 1 and doing the BOLD
CALL METHOD OF h_excel 'Cells' = w_cell1
EXPORTING
#1 = 1
#2 = 1.
CALL METHOD OF h_excel 'Cells' = w_cell2
EXPORTING
#1 = 1
#2 = 50.
CALL METHOD OF h_excel 'Range' = h_cell
EXPORTING
#1 = w_cell1
#2 = w_cell2.
GET PROPERTY OF h_cell 'Font' = gs_font .
SET PROPERTY OF gs_font 'Bold' = 1 .
GET PROPERTY OF h_cell 'Interior' = e_color.
SET PROPERTY OF e_color 'ColorIndex' = 40.
CALL METHOD cl_gui_frontend_services=>clipboard_export
IMPORTING
data = it_2[]
CHANGING
rc = l_rc
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.
CALL METHOD OF h_excel 'Cells' = w_cell1
EXPORTING
#1 = 1
#2 = 1.
CALL METHOD OF h_excel 'Cells' = w_cell2
EXPORTING
#1 = 1
#2 = 1.
CALL METHOD OF h_excel 'Range' = range
EXPORTING
#1 = w_cell1
#2 = w_cell2.
CALL METHOD OF range 'Select'.
CALL METHOD OF worksheet 'Paste'.
gv_sheet_name = 'Blocked Orders'.
GET PROPERTY OF h_excel 'Sheets' = h_sheet3 .
CALL METHOD OF h_sheet3 'Add' = h_map.
SET PROPERTY OF h_map 'Name' = gv_sheet_name .
GET PROPERTY OF h_excel 'ACTIVESHEET' = worksheet.
*--Formatting the area of additional data 1 and doing the BOLD
CALL METHOD OF h_excel 'Cells' = w_cell1
EXPORTING
#1 = 1
#2 = 1.
CALL METHOD OF h_excel 'Cells' = w_cell2
EXPORTING
#1 = 1
#2 = 50.
CALL METHOD OF h_excel 'Range' = h_cell
EXPORTING
#1 = w_cell1
#2 = w_cell2.
GET PROPERTY OF h_cell 'Font' = gs_font .
SET PROPERTY OF gs_font 'Bold' = 1 .
GET PROPERTY OF h_cell 'Interior' = e_color.
SET PROPERTY OF e_color 'ColorIndex' = 45.
CALL METHOD cl_gui_frontend_services=>clipboard_export
IMPORTING
data = it_3[]
CHANGING
rc = l_rc
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.
CALL METHOD OF h_excel 'Cells' = w_cell1
EXPORTING
#1 = 1
#2 = 1.
CALL METHOD OF h_excel 'Cells' = w_cell2
EXPORTING
#1 = 1
#2 = 1.
CALL METHOD OF h_excel 'Range' = range
EXPORTING
#1 = w_cell1
#2 = w_cell2.
CALL METHOD OF range 'Select'.
CALL METHOD OF worksheet 'Paste'.
*--- disconnect from Excel
FREE OBJECT h_zl.
FREE OBJECT h_mapl.
FREE OBJECT h_map.
FREE OBJECT h_excel.
Thanks,
Krishna..
03-06-2009 7:25 AM
Try this I guess this will work ..
Call the method to add workbook
CALL METHOD OF h_excel 'Workbooks' = h_mapl.
CALL METHOD OF h_mapl 'Add' = h_map.
CALL METHOD OF h_excel 'Worksheets' = h_sh
EXPORTING
#1 = 3.