Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

into excel sheet with multiple tabs

Former Member
0 Kudos

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.

1 ACCEPTED SOLUTION

Former Member
0 Kudos

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

4 REPLIES 4

Former Member
0 Kudos

Sure, you can use OLE programming for that.

Regards,

John.

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

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