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
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

While downloading into Excel

Former Member

‎2007 Oct 01 1:56 PM

0 Kudos
167

Hi i am using ole concept to donwload data from internal table to Excel.

For example i am giving my logic.

types: begin of x_jobs,

sno(3) type n,

jobname(50) type c,

comment type string,

end of x_jobs.

data: t_jobs type standard table of x_jobs,

w_jobs type x_jobs.

w_jobs-sno = 100.

w_jobs-jobname = 'HAA'.

append w_jobs to t_jobs.

w_jobs-sno = 2.

w_jobs-jobname = 'BAI'.

append w_jobs to t_jobs.

loop at t_jobs into w_jobs.

w_tabix = sy-tabix.

w_jobs-comment = 'What is your name'.

concatenate w_jobs-comment 'My name is Subhani' into w_jobs-comment

modify t_jobs from w_jobs index w_tabix transporting comment.

endloop.

When i am downloading to excel with OLE concpet output is as fallows(ignore doted lines just to separate column i have used)

SNO JOBNAME COMMENT

100--

HAA--

What is your nameMy name is Subhani

2--

BAI--

What is your nameMy name is Subhani

But i want the output as fallows (What is your name and My name is Subhani should be in one cell of excel sheet and in different lines)

SNO JOBNAME COMMENT

100--

HAA
"What is your name--

-

My name is Subhani"----

2--

BAI--

"What is your name

-

My name is Subhani"

in version 6.0 i have one method 'CL_ABAP_CHAR_UTILITIES=>NEWLINE'

If i use this as in fallowing code output is coming according to my requirement.

loop at t_jobs into w_jobs.

w_tabix = sy-tabix.

w_jobs-comment = 'What is your name'.

concatenate w_jobs-comment 'My name is Subhani' into w_jobs-comment

separated by cl_abap_char_utilities=>newline.

modify t_jobs from w_jobs index w_tabix transporting comment

endloop.

But presently i am working in 4.6c where there is no such method.

How can i do this in 4.6c.

Please help me.

Regards,

Subhai.

4 REPLIES 4

Former Member

‎2007 Oct 01 2:05 PM

0 Kudos
99

Hi Subhani,

Refer the code below:

Refer the link:

http://www.sap-img.com/abap/download-to-excel-with-format-border-color-cell-etc.htm

Please use the following FM

CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'

EXPORTING

  • I_FIELD_SEPERATOR =

  • I_LINE_HEADER =

i_filename =

  • I_APPL_KEEP = ' '

tables

i_tab_sap_data =

  • CHANGING

  • I_TAB_CONVERTED_DATA =

  • EXCEPTIONS

  • CONVERSION_FAILED = 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.

Another FM –

MS_EXCEL_OLE_STANDARD_DAT

A simple option:

a) form download_file .

call function 'WS_EXCEL'

importing

filename = excel_name

tables

data = itab.

b) call function 'WS_DOWNLOAD'

exporting

filename = o_file

filetype = 'DAT'

tables

data_tab = i_tab

exceptions

file_open_error = 1

file_write_error = 2

invalid_filesize = 3

invalid_type = 4

no_batch = 5

unknown_error = 6

invalid_table_width = 7

gui_refuse_filetransfer = 8

customer_error = 9

no_authority = 10

others = 11.

if sy-subrc = 0.

message 'File is downloaded successfully' type 'I'.

endif.

DOWN LOAD DATA INTO EXCEL FILE in BACKGROUND

Use this FM RSPO_DOWNLOAD_SPOOLJOB in the Program which is creating the Spool

See the link to Download in Background in Excel Format :-

http://www.sap-img.com/abap/download-in-background-in-excel-format.htm

<b>Reward points if this helps,

Manish</b>

former_member223537
Active Contributor

‎2007 Oct 01 2:06 PM

0 Kudos
99

Hi,

You need to use:

Data : l_newline type c value '09'.

Concatenate l_field1

l_field2

l_newline

into l_string separated by ' '.

Best regards,

Prashant

Former Member

‎2007 Oct 01 2:45 PM

0 Kudos
99

Hi Subhani,

Chk out the standard program for OLE - RSDEMO01 in se 38.And apply the code as per your req.

Hope this will solve your problem.

Reward Points if helpful.

Regards,

Harini.S

Former Member

‎2007 Oct 01 5:10 PM

0 Kudos
99

Dear Subhani,

I'm providing you with the following piece of code ... Its working fine for me ... hopefully it suits your requirement ...

************************************************************************

  • D A T A D E C L A R A T I O N *

************************************************************************

TYPES: BEGIN OF TY_EXCEL,

CELL_01(80) TYPE C,

CELL_02(80) TYPE C,

CELL_03(80) TYPE C,

CELL_04(80) TYPE C,

CELL_05(80) TYPE C,

CELL_06(80) TYPE C,

CELL_07(80) TYPE C,

CELL_08(80) TYPE C,

CELL_09(80) TYPE C,

CELL_10(80) TYPE C,

END OF TY_EXCEL.

DATA: IT_EXCEL TYPE STANDARD TABLE OF TY_EXCEL,

WA_EXCEL TYPE TY_EXCEL..

************************************************************************

  • E V E N T : S T A R T - O F - S E L E C T I O N *

************************************************************************

START-OF-SELECTION.

  • Here you populate the Internal Table.

  • -------------------------------------

  • Display - Top of the Page.

  • --------------------------------------

PERFORM DISPLAY_TOP_OF_PAGE.

************************************************************************

  • E V E N T : E N D - O F - S E L E C T I O N *

************************************************************************

END-OF-SELECTION.

SET PF-STATUS 'GUI_STATUS'.

************************************************************************

  • E V E N T : A T U S E R - C O M M AN D *

************************************************************************

AT USER-COMMAND.

CASE SY-UCOMM.

WHEN 'EXPORT'.

  • Exporting the report data to Excel.

PERFORM EXPORT_TO_EXCEL.

ENDCASE.

&----

*& Form DISPLAY_TOP_OF_PAGE

&----

  • text

----

  • --> p1 text

  • <-- p2 text

----

FORM DISPLAY_TOP_OF_PAGE .

SKIP.

WRITE: /05(128) SY-ULINE,

/05 SY-VLINE,

06(127) 'O R I C A'

CENTERED COLOR 1,

132 SY-VLINE.

WRITE: /05(128) SY-ULINE,

/05 SY-VLINE,

06(127) 'Shift Asset Depreciation - Period/Year-wise Report.'

CENTERED COLOR 4 INTENSIFIED OFF,

132 SY-VLINE.

WRITE: /05(128) SY-ULINE.

*----

  • E X C E L O P E R A T I O N

*----

CLEAR: IT_EXCEL[],

WA_EXCEL.

PERFORM APPEND_BLANK_LINE USING 1.

WA_EXCEL-cell_02 = ' XYZ Ltd. '.

APPEND WA_EXCEL TO IT_EXCEL.

CLEAR: WA_EXCEL.

WA_EXCEL-cell_02 = 'Shift Asset Depreciation - Period/Year-wise Report.'.

APPEND WA_EXCEL TO IT_EXCEL.

PERFORM APPEND_BLANK_LINE USING 1.

ENDFORM. " DISPLAY_TOP_OF_PAGE

&----

*& Form APPEND_BLANK_LINE

&----

  • text

----

  • -->P_1 text

----

FORM APPEND_BLANK_LINE USING P_LINE TYPE I.

DO P_LINE TIMES.

CLEAR: WA_EXCEL.

APPEND WA_EXCEL TO IT_EXCEL.

enddo.

ENDFORM.

&----

*& Form EXPORT_TO_EXCEL

&----

  • text

----

  • --> p1 text

  • <-- p2 text

----

FORM EXPORT_TO_EXCEL .

DATA: L_FILE_NAME(60) TYPE C.

  • Create a file name

CONCATENATE 'C:\' 'Shift_Depn_' SY-DATUM6(2) '.' SY-DATUM4(2)

'.' SY-DATUM+0(4) INTO L_FILE_NAME.

  • Pass the internal table (it_excel which is already populated )

  • to the function module for excel download.

CALL FUNCTION 'WS_EXCEL'

exporting

filename = L_FILE_NAME

tables

data = IT_EXCEL

exceptions

unknown_error = 1

others = 2.

if sy-subrc <> 0.

message e001(ymm) with 'Error in exporting to Excel.'.

endif.

ENDFORM. " EXPORT_TO_EXCEL

*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

When you click the button - Export to Excel ( GUI-Status) you'll be able to export the content of the Internal Table to an Excel file .......

Regards,

Abir

********************************************

  • Please don't forget to award points *