06-21-2010 11:36 AM
HI ,
How to create the excel file which is having multiple sheets in single workbook to transfer two internal table contents.
I've two internal tables and i need to tranfer this to applicationserver with the excel sheet (Each interanl table to each excel sheet). I can able to create SINGLE excel workbook in application server with one sheet and not able to create with two different sheets.
Help me....
Regards,
Krishna.
06-21-2010 12:07 PM
INCLUDE ole2incl.
DATA: h_excel TYPE ole2_object, " Excel object
h_mapl TYPE ole2_object, " list of workbooks
h_sheet TYPE ole2_object, " Sheet
h_map TYPE ole2_object, " workbook
h_zl TYPE ole2_object, " cell
h_f TYPE ole2_object, " font
int TYPE ole2_object, " color
g_worksheet TYPE ole2_object,
g_worksheets TYPE ole2_object.
DATA: g_first_ws VALUE 'Y',
count TYPE i VALUE 1,
h TYPE i ,
g_row TYPE i,
g_col TYPE i,
PERFORM download_excel .
FORM download_excel .
DATA : x TYPE i,
gs_adrc LIKE adrc.
CREATE OBJECT h_excel 'EXCEL.APPLICATION'.
SET PROPERTY OF h_excel 'Visible' = 1.
CALL METHOD OF h_excel 'Workbooks' = h_mapl.
CALL METHOD OF h_mapl 'Add' = h_map.
x = 0.
GET PROPERTY OF h_map 'Worksheets' = g_worksheets.
h = 1.
*for first internal table
PERFORM add_worksheet.
LOOP AT gt_outtab.
h = h + 1.
PERFORM fill_cell USING h 2 1 gt_outtab-matnr.
PERFORM fill_cell USING h 3 0 gt_outtab-maktx.
ENDLOOP.
*for second internal table
PERFORM add_worksheet.
LOOP AT gt_outtab.
h = h + 1.
PERFORM fill_cell USING h 2 1 gt_outtab-matnr.
PERFORM fill_cell USING h 3 0 gt_outtab-maktx.
ENDLOOP.
ENDFORM. "download_excel
FORM fill_cell USING i j bold val.
CALL METHOD OF h_excel 'Cells' = h_zl
EXPORTING
#1 = i
#2 = j.
SET PROPERTY OF h_zl 'Value' = val .
GET PROPERTY OF h_zl 'Font' = h_f.
SET PROPERTY OF h_f 'Bold' = bold .
ENDFORM. "FILL_CELL
FORM add_worksheet.
IF g_first_ws ='N'.
g_first_ws = 'N'.
GET PROPERTY OF h_excel 'ACTIVESHEET' = g_worksheet.
ELSE.
CALL METHOD OF g_worksheets 'Add' = g_worksheet.
ENDIF.
SET PROPERTY OF g_worksheet 'NAME' = count.
h = 1.
g_col = 1.
count = count + 1.
ENDFORM. "add_worksheet
06-21-2010 12:07 PM
INCLUDE ole2incl.
DATA: h_excel TYPE ole2_object, " Excel object
h_mapl TYPE ole2_object, " list of workbooks
h_sheet TYPE ole2_object, " Sheet
h_map TYPE ole2_object, " workbook
h_zl TYPE ole2_object, " cell
h_f TYPE ole2_object, " font
int TYPE ole2_object, " color
g_worksheet TYPE ole2_object,
g_worksheets TYPE ole2_object.
DATA: g_first_ws VALUE 'Y',
count TYPE i VALUE 1,
h TYPE i ,
g_row TYPE i,
g_col TYPE i,
PERFORM download_excel .
FORM download_excel .
DATA : x TYPE i,
gs_adrc LIKE adrc.
CREATE OBJECT h_excel 'EXCEL.APPLICATION'.
SET PROPERTY OF h_excel 'Visible' = 1.
CALL METHOD OF h_excel 'Workbooks' = h_mapl.
CALL METHOD OF h_mapl 'Add' = h_map.
x = 0.
GET PROPERTY OF h_map 'Worksheets' = g_worksheets.
h = 1.
*for first internal table
PERFORM add_worksheet.
LOOP AT gt_outtab.
h = h + 1.
PERFORM fill_cell USING h 2 1 gt_outtab-matnr.
PERFORM fill_cell USING h 3 0 gt_outtab-maktx.
ENDLOOP.
*for second internal table
PERFORM add_worksheet.
LOOP AT gt_outtab.
h = h + 1.
PERFORM fill_cell USING h 2 1 gt_outtab-matnr.
PERFORM fill_cell USING h 3 0 gt_outtab-maktx.
ENDLOOP.
ENDFORM. "download_excel
FORM fill_cell USING i j bold val.
CALL METHOD OF h_excel 'Cells' = h_zl
EXPORTING
#1 = i
#2 = j.
SET PROPERTY OF h_zl 'Value' = val .
GET PROPERTY OF h_zl 'Font' = h_f.
SET PROPERTY OF h_f 'Bold' = bold .
ENDFORM. "FILL_CELL
FORM add_worksheet.
IF g_first_ws ='N'.
g_first_ws = 'N'.
GET PROPERTY OF h_excel 'ACTIVESHEET' = g_worksheet.
ELSE.
CALL METHOD OF g_worksheets 'Add' = g_worksheet.
ENDIF.
SET PROPERTY OF g_worksheet 'NAME' = count.
h = 1.
g_col = 1.
count = count + 1.
ENDFORM. "add_worksheet
06-21-2010 12:14 PM
Hi Thanks for your reply,
I need to create the excel sheets in APPLICATION SERVER ....and i need to transfer the internal table contents to excel sheets which is application server.
Pls help me.
Regards,
Krishnamoorthy V.
06-21-2010 12:24 PM
Hi ,
If you need to deal with application server then
make use of
Open Data Set
Transfer contents to and from file/application server/ internal table work area
Close Data Set
Hope this is helpful.
Regards,
Uma Dave
06-21-2010 12:32 PM
06-21-2010 2:36 PM
Hi ,
Thank for your reply,
I need to transfer two internal contents to two worksheets in single excel workbook ....
for eg:
materialdetails.xls is having two sheets as below and i need to transfer two internal table contents.
Open dataset is creating excelsheet as well but with only one sheet...
lt_matnr = sheet1_matnr.
lt_matnr2 = sheet2_matnr2.
regards,
Krishnamoorthy .
06-21-2010 2:36 PM
Hi ,
Thank for your reply,
I need to transfer two internal contents to two worksheets in single excel workbook ....
for eg:
materialdetails.xls is having two sheets as below and i need to transfer two internal table contents.
Open dataset is creating excelsheet as well but with only one sheet...
lt_matnr = sheet1_matnr.
lt_matnr2 = sheet2_matnr2.
regards,
Krishnamoorthy .
03-31-2021 3:21 PM
Hello,
How can i make this excel generated in background and attach to an email later?
I can see this excel generated with 2 tabs in front end.