topic Re: into excel sheet with multiple tabs in Application Development and Automation Discussions

into excel sheet with multiple tabs

Former Member — Fri, 08 Feb 2008 11:06:30 GMT

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.

Re: into excel sheet with multiple tabs

Former Member — Fri, 08 Feb 2008 11:11:36 GMT

Sure, you can use OLE programming for that.

Regards,

John.

Re: into excel sheet with multiple tabs

Former Member — Sat, 16 Feb 2008 12:24:54 GMT

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

Re: into excel sheet with multiple tabs

Former Member — Mon, 18 Feb 2008 05:18:07 GMT

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

Re: into excel sheet with multiple tabs

Former Member — Mon, 18 Feb 2008 05:47:27 GMT

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