02-08-2008 11:06 AM
Hello,
Is it possible to download data into an excel sheet with multiple tabs
using an ABAP program?
Still hoping someone might have faced this and has found some solution.
02-16-2008 12:24 PM
Hi,
1. create several text files one for each sheet.
2. create an excel macro where you a. insert a new sheet, and b. upload
a file into it (may be you repeat a and b several times
3. research on the net looking for running excel macros from sap (there
are at least two pages which tell you how to do it using ole2).
4. put 1 to 3 together and you'll have what you're looking for.
Cheers,
Chaitanya
02-08-2008 11:11 AM
02-16-2008 12:24 PM
Hi,
1. create several text files one for each sheet.
2. create an excel macro where you a. insert a new sheet, and b. upload
a file into it (may be you repeat a and b several times
3. research on the net looking for running excel macros from sap (there
are at least two pages which tell you how to do it using ole2).
4. put 1 to 3 together and you'll have what you're looking for.
Cheers,
Chaitanya
02-18-2008 5:18 AM
Below program can give you some lead and use accordingly.
INCLUDE ole2incl.
DATA: application TYPE ole2_object,
workbook TYPE ole2_object,
sheet TYPE ole2_object,
sheets TYPE ole2_object,
cells TYPE ole2_object.
CONSTANTS: row_max TYPE i VALUE 256.
DATA index TYPE i.
TYPES: BEGIN OF t_data,
sheet_no TYPE i,
col1(20) TYPE c,
col2(20) TYPE c,
END OF t_data.
DATA: it_data TYPE STANDARD TABLE OF t_data,
wa_data TYPE t_data.
DATA: g_sheet TYPE i VALUE 1,
g_row TYPE i,
g_col TYPE i.
***********************************************************************
*
*START-OF-SELECTION
START-OF-SELECTION.
PERFORM populate_data.
CREATE OBJECT application 'excel.application'.
SET PROPERTY OF application 'visible' = 1.
CALL METHOD OF application 'Workbooks' = workbook.
CALL METHOD OF workbook 'Add' = workbook.
CALL METHOD OF workbook 'Sheets' = sheets.
LOOP AT it_data INTO wa_data.
ON CHANGE OF wa_data-sheet_no.
IF wa_data-sheet_no > 3.
CALL METHOD OF sheets 'Add' = sheet.
SET PROPERTY OF sheets 'active' = 1.
SET PROPERTY OF sheet 'Name' = wa_data-sheet_no.
ENDIF.
CALL METHOD OF workbook 'worksheets' = sheet NO FLUSH
EXPORTING
#1 = g_sheet.
SET PROPERTY OF sheet 'name' = wa_data-sheet_no.
g_sheet = g_sheet + 1.
g_row = 0.
ENDON.
g_row = g_row + 1. g_col = 1.
CALL METHOD OF sheet 'cells' = cells
EXPORTING
#1 = g_row
#2 = g_col.
SET PROPERTY OF cells 'value' = wa_data-col1.
g_col = g_col + 1.
CALL METHOD OF sheet 'cells' = cells
EXPORTING
#1 = g_row
#2 = g_col.
SET PROPERTY OF cells 'value' = wa_data-col2.
ENDLOOP.
* Save excel speadsheet to particular filename
CALL METHOD OF sheet 'SaveAs'
EXPORTING #1 = 'c:\exceldoc1.xls' "filename
#2 = 1.
*ileFormat
CALL METHOD OF sheet 'Close'
EXPORTING #1 = 'c:\exceldoc1.xls' "filename
#2 = 1.
IF sy-subrc EQ 0.
EXIT.
ENDIF.
* Closes excel window, data is lost if not saved
SET PROPERTY OF application 'visible' = 0.
*&---------------------------------------------------------------------*
*& Form populate_data
*&---------------------------------------------------------------------*
FORM populate_data .
DATA: l_sheet TYPE n.
DO 5 TIMES.
l_sheet = l_sheet + 1.
MOVE l_sheet TO wa_data-sheet_no.
CONCATENATE: l_sheet '-row1-col1' INTO wa_data-col1,
l_sheet '-row1-col2' INTO wa_data-col2.
APPEND wa_data TO it_data.
CONCATENATE: l_sheet '-row2-col1' INTO wa_data-col1,
l_sheet '-row2-col2' INTO wa_data-col2.
APPEND wa_data TO it_data.
CONCATENATE: l_sheet '-row3-col1' INTO wa_data-col1,
l_sheet '-row3-col2' INTO wa_data-col2.
APPEND wa_data TO it_data.
CONCATENATE: l_sheet '-row4-col1' INTO wa_data-col1,
l_sheet '-row4-col2' INTO wa_data-col2.
APPEND wa_data TO it_data.
ENDDO.
ENDFORM. " populate_data
Note that this can be quite slow process.
Regards
Eswar
02-18-2008 5:47 AM
Sashi,
Yes , It is,
Run below report .
REPORT ZP_EXCEL .
&----
*& Report ZP_EXCEL *
&----
include ole2incl.
data: application type ole2_object,
workbook type ole2_object,
sheet type ole2_object,
cells type ole2_object,
sheets TYPE ole2_object.
constants: row_max type i value 256.
data index type i.
data : det type ref to CL_ABAP_structDESCR,
wa like line of det->components.
data: begin of i_finaltab occurs 0,
Col1 type i,
col2 type i,
col3 type i,
col4 type i,
end of i_finaltab.
data : count type i,
v_excel_count(3), " type i value 1,
sheet_name(15) .
************************************************************************
*START-OF-SELECTION
start-of-selection.
do 50 times.
count = count + 1.
i_finaltab-col2 = 5 * count.
i_finaltab-col3 = 10 * count.
i_finaltab-col4 = 20 * count.
move : count to i_finaltab-col1.
append i_finaltab.
clear i_finaltab.
enddo.
clear count.
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' = 'EXCEL0'.
count = 1.
index = row_max * ( count - 1 ) + 1.
perform header_details.
loop at i_finaltab.
count = count + 1.
if count LE 6.
index = row_max * ( count - 1 ) + 1. " 1 - column name
call method of sheet 'Cells' = cells
exporting
#1 = index.
set property of cells 'Value' = i_finaltab-col1.
index = index + 1.
call method of sheet 'Cells' = cells
exporting
#1 = index.
set property of cells 'Value' = i_finaltab-col2.
index = index + 1.
call method of sheet 'Cells' = cells
exporting
#1 = index.
set property of cells 'Value' = i_finaltab-col3.
index = index + 1.
call method of sheet 'Cells' = cells
exporting
#1 = index.
set property of cells 'Value' = i_finaltab-col4.
else.
v_excel_count = v_excel_count + 1.
CALL METHOD OF application 'Sheets' = sheets.
CALL METHOD OF sheets 'Add'.
concatenate 'EXCEL' v_excel_count into sheet_name.
clear count.
count = count + 1.
call method of application 'Worksheets' = sheet
exporting
#1 = 1.
set property of sheet 'Name' = sheet_name.
call method of sheet 'Activate'.
index = row_max * ( count - 1 ) + 1.
perform header_details.
count = count + 1.
index = row_max * ( count - 1 ) + 1. " 1 - column name
call method of sheet 'Cells' = cells
exporting
#1 = index.
set property of cells 'Value' = i_finaltab-col1.
index = index + 1.
call method of sheet 'Cells' = cells
exporting
#1 = index.
set property of cells 'Value' = i_finaltab-col2.
index = index + 1.
call method of sheet 'Cells' = cells
exporting
#1 = index.
set property of cells 'Value' = i_finaltab-col3.
index = index + 1.
call method of sheet 'Cells' = cells
exporting
#1 = index.
set property of cells 'Value' = i_finaltab-col4.
endif.
endloop.
&----
*& Form header_details
&----
text
----
--> p1 text
<-- p2 text
----
form header_details.
det ?= cl_abap_typedescr=>describe_by_DATA( i_finaltab ).
loop at det->components into wa.
call method of sheet 'Cells' = cells
exporting
#1 = index.
set property of cells 'Value' = wa-name.
index = index + 1.
endloop.
endform. " header_details
Don't forget to reward if useful...