Regarding download into application server

Former Member · ‎10-17-2007

Hi,

I want to download my report program into excel file and place that excel file on my application server. How can I do this.

Thanks and Regards,

Shravan G.

Former Member · ‎10-17-2007

Message was edited by:

Karthikeyan Pandurangan

Former Member · ‎10-17-2007

If a file already exists, it adds a new Tab(Worksheet) in the Excel.

2) If the file doesn't exist, then it creates an excel sheet and downloads into it.

Also, you can name the worksheet(tabname in excel) created. Further following formatting options can be provided.

1) Giving a colour index to a cell: This is particularly useful while you upload a file, and you would like to show the errors in data of the file by changing the background colour of the cell.

2) Bold

3) Vertical Orientation: This will provide you an option to change the vertical orientation of text in the cell. For example, downloading text vertically(90 degrees). This orientation corresponds to the degree at which you want to rotate the data in the cell.

4) Comments: Adding comments to a cell. This is very useful while you use the colour index to show that it is an error and add a comment to say what exactly the error is.

You need to create one structure(named as ZFORMATOPTIONS in the example) with following fields.

Note: The text is in the following format

fieldname(Dataelement)- Description.

ROW(KCD_EX_ROW_N)- Number of the row to be formatted

COL(KCD_EX_COL_N)- Number of the col to be formatted

VERT(NUMC2)- Degree of rotation(0-90)

BOLD(BOOLE_D)- 'X' if you want to make text bold

COLOR(CHAR1)- Values 1 to 9

COMMENTS(CHAR256)- Free text that is added as a comment to the cell

You can also provide headings to the excel sheet through the table parameter 'T_HEADING' to the function module.

The function group's TOP include, function module, Form include(F01) are placed below. Further a test program is also given to show how this function module could be used.

********************TOP INCLUDE Starts********************

FUNCTION-POOL ZTEST_OLE. "MESSAGE-ID ..

TYPE-POOLS: abap.

EXCEL sheet using OLE automation.

INCLUDE OLE2INCL.

DEFINE ole_error.

IF NOT &1 IS INITIAL.

MESSAGE e899(v1) WITH 'OLE Error ='(002) &1

RAISING ole_error.

ENDIF.

END-OF-DEFINITION.

TYPES:

BEGIN OF ty_line,

line(4096) TYPE c,

END OF ty_line.

CONSTANTS:

c_tab TYPE x VALUE 9,

c_bgrw TYPE i VALUE 1,

c_bgcl TYPE i VALUE 1.

*For EXCEL operations through ABAP

DATA:

w_excel TYPE ole2_object, "Holds the excel application

w_wbooks TYPE ole2_object, "Holds Work Books

w_wbook TYPE ole2_object, "Holds Work Book

w_cell TYPE ole2_object, "Holds Cell

w_format TYPE ole2_object, "Object for format

w_font TYPE ole2_object,

w_sheets TYPE ole2_object, "Holds Active Sheet

w_range TYPE ole2_object, "To select a range

*For data processing

it_line TYPE STANDARD TABLE OF ty_line,

wa_line TYPE ty_line,

w_field TYPE ty_line-line,

w_tab TYPE c.

FIELD-SYMBOLS:

<fs_field> TYPE ANY,

<fs_hex> TYPE ANY.

*******************TOP Include Ends***********************

*****************Function Module starts*******************

FUNCTION ztest_ole_single_table.

"----

"*"Local interface:

*" IMPORTING

*" REFERENCE(FILENAME) TYPE RLGRAP-FILENAME

*" REFERENCE(TABNAME) TYPE CHAR16 OPTIONAL

*" TABLES

*" T_DATA

*" T_HEADING STRUCTURE LINE OPTIONAL

*" T_FORMATOPT STRUCTURE ZFORMATOPTIONS OPTIONAL

*" EXCEPTIONS

*" OLE_ERROR

*" DATA_EMPTY

*" CLIPBOARD_EXPORT_ERROR

*"----

DATA:

file_already_exists TYPE c.

IF t_data[] IS INITIAL.

MESSAGE e899(v1) WITH 'No Data in the internal table'(001)

RAISING data_empty.

ENDIF.

ASSIGN w_tab TO <fs_hex> TYPE 'X'.

<fs_hex> = c_tab.

REFRESH it_line.

PERFORM prepare_int_tab TABLES t_data

t_heading.

PERFORM create_excel_sheet USING filename

tabname

t_data

CHANGING file_already_exists.

CHECK NOT t_formatopt[] IS INITIAL.

PERFORM format_cells TABLES t_formatopt

USING filename

file_already_exists.

ENDFUNCTION.

****************Function Module Ends**********************

****************F01(Form Include) starts******************

*----

***INCLUDE LZTEST_OLEF01 .

*----

*&----

*& Form prepare_int_tab

*&----

text

*----

--> p1 text
<-- p2 text

*----

FORM prepare_int_tab TABLES it_data

it_heading STRUCTURE line.

CLEAR wa_line.

IF NOT it_heading[] IS INITIAL.

LOOP AT it_heading.

CONCATENATE wa_line-line

it_heading-line

w_tab

INTO wa_line-line.

CONDENSE wa_line.

ENDLOOP.

APPEND wa_line TO it_line.

ENDIF.

LOOP AT it_data.

CLEAR wa_line.

DO.

ASSIGN COMPONENT sy-index OF STRUCTURE it_data TO <fs_field>.

IF NOT sy-subrc IS INITIAL.

EXIT.

ENDIF.

w_field = <fs_field>.

CONDENSE w_field.

CONCATENATE wa_line-line

w_field

w_tab

INTO wa_line-line.

CONDENSE wa_line.

ENDDO.

APPEND wa_line TO it_line.

ENDLOOP.

ENDFORM. " prepare_int_tab

*&----

*& Form create_excel_sheet

*&----

text

*----

--> p1 text
<-- p2 text

*----

FORM create_excel_sheet USING p_filename

p_tabname

w_data

CHANGING p_file_already_exists.

DATA:

l_cols TYPE i,

l_rows TYPE i,

l_name TYPE char16,

l_rc TYPE sy-subrc,

l_res TYPE abap_bool,

l_type TYPE c,

l_file TYPE string,

l_from TYPE ole2_object,

l_to TYPE ole2_object,

l_entcol TYPE ole2_object.

CREATE OBJECT w_excel 'Excel.Application'.

ole_error sy-subrc.

CALL METHOD OF w_excel 'Workbooks' = w_wbooks.

ole_error sy-subrc.

SET PROPERTY OF w_excel 'Visible' = 1.

ole_error sy-subrc.

l_file = p_filename.

CLEAR l_res.

CALL METHOD cl_gui_frontend_services=>file_exist

EXPORTING

file = l_file

RECEIVING

result = l_res

EXCEPTIONS

cntl_error = 1

error_no_gui = 2

wrong_parameter = 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 l_res IS INITIAL.

CLEAR p_file_already_exists.

ELSE.

p_file_already_exists = 'X'.

ENDIF.

IF NOT p_file_already_exists IS INITIAL.

Open the existing file in case if it exists

CALL METHOD OF w_wbooks 'Open'

EXPORTING

#1 = p_filename.

ole_error sy-subrc.

CALL METHOD OF w_excel 'Sheets' = w_sheets.

ole_error sy-subrc.

CALL METHOD OF w_sheets 'Add'.

ole_error sy-subrc.

GET PROPERTY OF w_excel 'ActiveSheet' = w_wbook.

ole_error sy-subrc.

ELSE.

CALL METHOD OF w_wbooks 'Add'. " = w_wbook.

ole_error sy-subrc.

GET PROPERTY OF w_excel 'ActiveSheet' = w_wbook.

ole_error sy-subrc.

ENDIF.

IF NOT p_tabname IS INITIAL.

SET PROPERTY OF w_wbook 'Name' = p_tabname.

ole_error sy-subrc.

ENDIF.

CALL METHOD OF w_wbook 'Cells' = l_from

EXPORTING

#1 = c_bgrw

#2 = c_bgcl.

ole_error sy-subrc.

DESCRIBE FIELD w_data TYPE l_type COMPONENTS l_cols.

DESCRIBE TABLE it_line LINES l_rows.

CALL METHOD OF w_wbook 'Cells' = l_to

EXPORTING

#1 = l_rows

#2 = l_cols.

ole_error sy-subrc.

CALL METHOD OF w_wbook 'Range' = w_range

EXPORTING

#1 = l_from

#2 = l_to.

ole_error sy-subrc.

CALL METHOD cl_gui_frontend_services=>clipboard_export

IMPORTING

data = it_line

CHANGING

rc = l_rc

EXCEPTIONS

cntl_error = 1

error_no_gui = 2

OTHERS = 3

.

IF sy-subrc <> 0

OR NOT l_rc IS INITIAL.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4

RAISING clipboard_export_error.

ENDIF.

CALL METHOD OF w_range 'Select'.

ole_error sy-subrc.

CALL METHOD OF w_wbook 'Paste'.

ole_error sy-subrc.

WHILE l_cols GT 0.

l_rows = 1.

CALL METHOD OF w_excel 'Columns' = w_cell

EXPORTING

#1 = l_cols.

ole_error sy-subrc.

CALL METHOD OF w_cell 'EntireColumn' = l_entcol.

ole_error sy-subrc.

l_cols = l_cols - 1.

CALL METHOD OF l_entcol 'Autofit'.

ole_error sy-subrc.

ENDWHILE.

ENDFORM. " create_excel_sheet

*&----

*& Form format_cells

*&----

text

*----

-->P_FILENAME text
-->P_FILE_ALREADY_EXISTS text

*----

FORM format_cells TABLES it_formatopt STRUCTURE zformatoptions

USING p_filename TYPE rlgrap-filename

p_file_already_exists TYPE c.

DATA:

l_row TYPE i,

l_col TYPE i,

l_entcol TYPE ole2_object,

l_cols TYPE ole2_object,

l_comment TYPE ole2_object.

LOOP AT it_formatopt.

CLEAR: l_row, l_col.

l_row = it_formatopt-row.

l_col = it_formatopt-col.

CALL METHOD OF w_wbook 'Cells' = w_cell

EXPORTING

#1 = l_row

#2 = l_col.

ole_error sy-subrc.

IF NOT it_formatopt-bold IS INITIAL.

CALL METHOD OF w_cell 'Font' = w_font.

ole_error sy-subrc.

SET PROPERTY OF w_font 'Bold' = 1.

ole_error sy-subrc.

CALL METHOD OF w_excel 'Columns' = l_cols

EXPORTING

#1 = l_col.

ole_error sy-subrc.

CALL METHOD OF l_cols 'EntireColumn' = l_entcol.

ole_error sy-subrc.

CALL METHOD OF l_entcol 'Autofit'.

ole_error sy-subrc.

ENDIF.

IF NOT it_formatopt-color IS INITIAL.

CALL METHOD OF w_cell 'Interior' = w_format.

ole_error sy-subrc.

SET PROPERTY OF w_format 'ColorIndex' = it_formatopt-color.

ole_error sy-subrc.

CALL METHOD OF w_excel 'Columns' = l_cols

EXPORTING

#1 = l_col.

ole_error sy-subrc.

CALL METHOD OF l_cols 'EntireColumn' = l_entcol.

ole_error sy-subrc.

CALL METHOD OF l_entcol 'Autofit'.

ole_error sy-subrc.

ENDIF.

IF NOT it_formatopt-vert IS INITIAL.

SET PROPERTY OF w_cell 'Orientation' = it_formatopt-vert.

ole_error sy-subrc.

CALL METHOD OF w_excel 'Columns' = l_cols

EXPORTING

#1 = l_col.

ole_error sy-subrc.

CALL METHOD OF l_cols 'EntireColumn' = l_entcol.

ole_error sy-subrc.

CALL METHOD OF l_entcol 'Autofit'.

ole_error sy-subrc.

ENDIF.

IF NOT it_formatopt-comments IS INITIAL.

CALL METHOD OF w_excel 'Range' = w_range
EXPORTING
#1 = l_row
#2 = l_col.
ole_error sy-subrc.
CALL METHOD OF w_range 'Select'.
ole_error sy-subrc.

CALL METHOD OF w_cell 'AddComment' = l_comment.

ole_error sy-subrc.

CALL METHOD OF l_comment 'Text'

EXPORTING

#1 = it_formatopt-comments.

ole_error sy-subrc.

ENDIF.

ENDLOOP.

PERFORM save_and_close USING p_filename

p_file_already_exists.

ENDFORM. " format_cells

*&----

*& Form save_and_close

*&----

text

*----

-->P_P_FILENAME text
-->P_P_FILE_ALREADY_EXISTS text

*----

FORM save_and_close USING p_filename

p_file_already_exists.

IF p_file_already_exists IS INITIAL.

CALL METHOD OF w_wbook 'Saveas'

EXPORTING

#1 = p_filename.

ole_error sy-subrc.

ELSE.

CALL METHOD OF w_excel 'ActiveWorkbook' = w_wbooks.

ole_error sy-subrc.

CALL METHOD OF w_wbooks 'Save'.

ole_error sy-subrc.

ENDIF.

CALL METHOD OF w_wbooks 'Close'.

ole_error sy-subrc.

ENDFORM. " save_and_close

**************Form Include Ends***************************

*********************Test Progam**************************

REPORT ztest_ole.

TABLES mara.

SELECT-OPTIONS:

s_matnr FOR mara-matnr.

PARAMETERS:

p_file TYPE rlgrap-filename,

p_tabnm TYPE char16.

DATA:

BEGIN OF it_mara OCCURS 1,

matnr TYPE mara-matnr, "Material No.

mtart TYPE mara-mtart, "Material Type

matkl TYPE mara-matkl, "Material Group

groes TYPE mara-groes, "Size/Dimension

END OF it_mara,

it_heading TYPE STANDARD TABLE OF line,

wa_heading TYPE line,

it_formatopt TYPE STANDARD TABLE OF zformatoptions,

wa_format TYPE zformatoptions,

l_col TYPE zformatoptions-col.

START-OF-SELECTION.

SELECT matnr

mtart

matkl

groes

FROM mara

UP TO 100 ROWS

INTO TABLE it_mara.

wa_heading-line = 'Material No.'. APPEND wa_heading TO it_heading.

wa_heading-line = 'Material Type'. APPEND wa_heading TO it_heading.

wa_heading-line = 'Material Group'. APPEND wa_heading TO it_heading.

wa_heading-line = 'Size/Dimension'. APPEND wa_heading TO it_heading.

DO 4 TIMES.

CLEAR wa_format.

wa_format-row = 1.

wa_format-col = l_col + 1.

l_col = l_col + 1.

wa_format-bold = 'X'.

wa_format-color = '6'.

wa_format-vert = 45.

wa_format-comments = 'This is a heading'.

APPEND wa_format TO it_formatopt.

ENDDO.

CALL FUNCTION 'ZTEST_OLE_SINGLE_TABLE'

EXPORTING

filename = p_file

tabname = p_tabnm

TABLES

t_data = it_mara

t_heading = it_heading

t_formatopt = it_formatopt

EXCEPTIONS

ole_error = 1

data_empty = 2

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

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

PERFORM get_file CHANGING p_file.

*&----

*& Form get_file

*&----

text

*----

<--P_P_FILE text

*----

FORM get_file CHANGING p_file.

DATA:

l_file TYPE string,

l_path TYPE string,

l_fpath TYPE string.

CALL METHOD cl_gui_frontend_services=>file_save_dialog

CHANGING

filename = l_file

path = l_path

fullpath = l_fpath

EXCEPTIONS

cntl_error = 1

error_no_gui = 2

OTHERS = 3

.

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

p_file = l_fpath.

ENDFORM. " get_file

*******************Test Program Ends**********************

Please reward points if helpful.

amit_khare · ‎10-17-2007

Refer the links -

Regards,

Amit

Reward all helpful replies.

former_member223537 · ‎10-17-2007

DATA: file   TYPE string VALUE `test.dat`, 
      result TYPE string. 

OPEN DATASET file FOR OUTPUT IN TEXT MODE 
                             ENCODING DEFAULT 
                             WITH SMART LINEFEED. 
TRANSFER `1234567890` TO file. 
CLOSE DATASET file. 

OPEN DATASET file FOR UPDATE IN TEXT MODE 
                             ENCODING DEFAULT 
                             WITH SMART LINEFEED 
                             AT POSITION 2. 
TRANSFER `ABCD` TO file. 
CLOSE DATASET file.

==============================================================================

DATA: file TYPE string VALUE `flights.dat`,

wa TYPE spfli.

FIELD-SYMBOLS TYPE x.

OPEN DATASET file FOR OUTPUT IN BINARY MODE.

SELECT *

FROM spfli

INTO wa.

ASSIGN wa TO CASTING.

TRANSFER TO file.

ENDSELECT.

CLOSE DATASET file.

Former Member · ‎10-17-2007

Hi

use the FM

EXCEL_OLE_STANDARD_DAT

to extract the report program in to excel sheet

and this is the program whete excel sheet to application server

&----

*& Report ZSD_EXCEL_INT_APP

*&

&----

*&

&----

REPORT ZSD_EXCEL_INT_APP.

parameter: file_nm type localfile.

types : begin of it_tab1,

f1(20),

f2(40),

f3(20),

end of it_tab1.

data : it_tab type table of ALSMEX_TABLINE with header line,

file type rlgrap-filename.

data : it_tab2 type it_tab1 occurs 1,

wa_tab2 type it_tab1,

w_message(100) TYPE c.

at selection-screen on value-request for file_nm.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

PROGRAM_NAME = SYST-REPID
DYNPRO_NUMBER = SYST-DYNNR
FIELD_NAME = ' '

STATIC = 'X'

MASK = ' '

CHANGING

file_name = file_nm

EXCEPTIONS

MASK_TOO_LONG = 1

OTHERS = 2

.

IF sy-subrc <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

start-of-selection.

refresh it_tab2[].clear wa_tab2.

file = file_nm.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = file

i_begin_col = '1'

i_begin_row = '1'

i_end_col = '10'

i_end_row = '35'

tables

intern = it_tab

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3

.

IF sy-subrc <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

loop at it_tab.

case it_tab-col.

when '002'.

wa_tab2-f1 = it_tab-value.

when '004'.

wa_tab2-f2 = it_tab-value.

when '008'.

wa_tab2-f3 = it_tab-value.

endcase.

at end of row.

append wa_tab2 to it_tab2.

clear wa_tab2.

endat.

endloop.

data : p_file TYPE rlgrap-filename value 'TEST3.txt'.

OPEN DATASET p_file FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.

*--- Display error messages if any.

IF sy-subrc NE 0.

MESSAGE e001(zsd_mes).

EXIT.

ELSE.

*---Data is downloaded to the application server file path

LOOP AT it_tab2 INTO wa_tab2.

TRANSFER wa_tab2 TO p_file.

ENDLOOP.

ENDIF.

*--Close the Application server file (Mandatory).

CLOSE DATASET p_file.

loop at it_tab2 into wa_tab2.

write : / wa_tab2-f1,wa_tab2-f2,wa_tab2-f3.

endloop.

<b>Reward if usefull</b>