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: 

convert the internal table into XL file

Former Member

‎08-23-2007 6:35 AM

0 Kudos

Hi

I have the data in internal table . Now i want to change this internal table into XL file for sending it into mail .Can anybody help in this.

Thanks.

9 REPLIES 9

Former Member

‎08-23-2007 6:39 AM

0 Kudos

To convert into Excel..

CALL FUNCTION 'GUI_DOWNLOAD'

EXPORTING

filename =

'C:\Documents and Settings\Myexcel.xls'

TABLES

data_tab = i_itab

EXCEPTIONS

file_write_error = 1

no_batch = 2

gui_refuse_filetransfer = 3

invalid_type = 4

no_authority = 5

unknown_error = 6

header_not_allowed = 7

separator_not_allowed = 8

filesize_not_allowed = 9

header_too_long = 10

dp_error_create = 11

dp_error_send = 12

dp_error_write = 13

unknown_dp_error = 14

access_denied = 15

dp_out_of_memory = 16

disk_full = 17

dp_timeout = 18

file_not_found = 19

dataprovider_exception = 20

control_flush_error = 21

OTHERS = 22

TO Convert into XML:

DATA xml_string TYPE string.

DATA source1(10) TYPE c VALUE 'Field1'.

DATA source2(10) TYPE c VALUE 'Field2'.

CALL TRANSFORMATION ...

SOURCE root1 = source1

root2 = source2

RESULT XML xml_string.

For more help see

http://help.sap.com/saphelp_nw04/helpdata/en/e3/7d4719ca581441b6841f1054ff1326/content.htm

Check these standard demo transactions:

SXSLTDEMO1

SSTDEMO1

SSTDEMO2

Here you can see an example how export data from ABAP to XML and XML data to ABAP.

regards,

rewards point

Former Member

‎08-23-2007 6:40 AM

0 Kudos

Hi,

Where do you want to upload the excel file. If you need to upload into any Business Transaction, If the Buisness Objects are available then you can use the FM 'BDS_BUSINESSDOCUMENT_CREA_TAB'.

Create the snapshot of the code. if this is your requirement, let me know i can provide the full code.

  • -- fill file and signature structure -- *

CLEAR: I_FILES, I_SIGNATURE.

REFRESH : I_FILES, I_SIGNATURE.

DESCRIPTION = FILE_NAME.

FILES-DOC_COUNT = 1.

FILES-DIRECTORY = FILE_PATH.

FILES-FILENAME = FILE_NAME.

FILES-MIMETYPE = MIMETYPE.

APPEND FILES TO I_FILES.

  • -- fill components and signature structure -- *

COMPONENTS-DOC_COUNT = 1.

COMPONENTS-COMP_COUNT = 1.

COMPONENTS-MIMETYPE = MIMETYPE.

COMPONENTS-COMP_ID = FILE_NAME.

APPEND COMPONENTS TO I_COMPONENTS.

SIGNATURE-DOC_COUNT = 1.

SIGNATURE-PROP_NAME = 'BDS_DOCUMENTCLASS'.

SIGNATURE-PROP_VALUE = FILE_EXTENSION.

APPEND SIGNATURE TO I_SIGNATURE.

SIGNATURE-PROP_NAME = 'BDS_CONTREP'.

*IF BDS_DOCTYPE_LIST-CONTREP = SPACE.

SIGNATURE-PROP_VALUE = ' '. "#EC NOTEXT

*ELSE.

  • I_SIGNATURE-PROP_VALUE = BDS_DOCTYPE_LIST-CONTREP.

*ENDIF.

APPEND SIGNATURE TO I_SIGNATURE.

SIGNATURE-PROP_NAME = 'BDS_DOCUMENTTYPE'.

SIGNATURE-PROP_VALUE = WA_TOADV-AR_OBJECT. "'ZSRVCXLS'.

APPEND SIGNATURE TO I_SIGNATURE.

SIGNATURE-PROP_NAME = 'DESCRIPTION'.

SIGNATURE-PROP_VALUE = DESCRIPTION.

APPEND SIGNATURE TO I_SIGNATURE.

SIGNATURE-PROP_NAME = 'LANGUAGE'.

SIGNATURE-PROP_VALUE = VERSION_LANGU.

APPEND SIGNATURE TO I_SIGNATURE.

CALL FUNCTION 'BDS_BUSINESSDOCUMENT_CREA_TAB'

EXPORTING

  • LOGICAL_SYSTEM =

CLASSNAME = 'BUS2222' "classname_select

CLASSTYPE = 'BO'" classtype_select

CLIENT = SY-MANDT

OBJECT_KEY = OBJECT_KEY

BINARY_FLAG = 'X'

  • IMPORTING

  • OBJECT_KEY =

TABLES

SIGNATURE = I_SIGNATURE

COMPONENTS = I_COMPONENTS

CONTENT = I_FILE_CONTENT

  • ASCII_CONTENT =

  • EXCEPTIONS

  • NOTHING_FOUND = 1

  • PARAMETER_ERROR = 2

  • NOT_ALLOWED = 3

  • ERROR_KPRO = 4

  • INTERNAL_ERROR = 5

  • NOT_AUTHORIZED = 6

  • OTHERS = 7

.

COMMIT WORK AND WAIT.

regards,

rewards point.

Former Member
In response to Former Member

‎08-24-2007 5:20 AM

0 Kudos

I have to send this XL file into a mail attachment.

is this is right function module?

Former Member

‎08-23-2007 6:40 AM

0 Kudos

Hi

I think the new (available from Excel 2002 or so) XML format is a the way to go.

Try the the following manually:

Create a simple Excel spreadsheet with some formatting, and save it as *.xml.

Have a look at the content of the file with notepad.exe or so. The strucure of it is rather simple. You can rename the file later again to .xls to make sure that your PC opens it correctly with Excel.

Note 983411 let´s me think that SAP uses this appraoch also for BI Information Broadcasting.

Basically you can create such files with the very basic ABAP statements. I don´t know yet if there are any FMs or methods for this purpose. I'm currently perparing to do this on 40B

regards,

rewards point

Former Member

‎08-23-2007 6:41 AM

0 Kudos

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

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

  • Don't forget to award points *

Rudolf Lindenmann

Posts: 5

Questions: 0

Registered: 3/15/04

Forum points: 0

Re: Convert an internal table to an Excel file on the server

Posted: Jan 10, 2007 2:56 AM in response to: Daniel Fang Reply E-mail this post

Hi Daniel

If you want to do it in the background try this code (please adjust the DATASET part to your needs; this code is tested eith 40B on UNIX, Excel 2002).

For testing purposes you can use transaction CG3Y to move the file back to your PC. Use a .xls file name and it will look and behave normally.

Let me know if you need to know more.

Regards

Rudolf

REPORT Z_EXCEL_BATCH.

  • Definitions

TABLES USR02.

TYPES XMLLINE(1024) TYPE C.

DATA IT_XML TYPE XMLLINE OCCURS 0.

DATA WA_XML TYPE XMLLINE.

DATA FNAME(60) TYPE C VALUE '/ft/D02/001/test.xls'.

  • Build-up of xml in internal table

    • Opening tags

APPEND '<?xml version="1.0"?>' TO IT_XML.

APPEND '<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"'

TO IT_XML.

APPEND 'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">'

TO IT_XML.

APPEND ' <Worksheet ss:Name="Tabelle1">' TO IT_XML.

APPEND ' <Table>' TO IT_XML.

    • The actual data from table USR02

SELECT * FROM USR02.

APPEND ' <Row>' TO IT_XML.

CONCATENATE ' <Cell><Data ss:Type="String">'

USR02-BNAME '</Data></Cell>' INTO WA_XML.

APPEND WA_XML TO IT_XML.

CONCATENATE ' <Cell><Data ss:Type="String">'

USR02-TRDAT '</Data></Cell>' INTO WA_XML.

APPEND WA_XML TO IT_XML.

APPEND ' </Row>' TO IT_XML.

ENDSELECT.

**Closing tags

APPEND ' </Table>' TO IT_XML.

APPEND ' </Worksheet>' TO IT_XML.

APPEND '</Workbook>' TO IT_XML.

  • Display of internal table on screen

LOOP AT IT_XML INTO WA_XML.

WRITE / WA_XML.

ENDLOOP.

  • Transfer of internal table to file

OPEN DATASET FNAME FOR OUTPUT IN TEXT MODE.

LOOP AT IT_XML INTO WA_XML.

TRANSFER WA_XML TO FNAME.

ENDLOOP.

CLOSE DATASET FNAME.

regards,

rewards point.

Former Member

‎08-23-2007 6:41 AM

0 Kudos

Hi Saurabh, according to my knowledge you can save your internal table to excel format while debugging. to save like that follw the steps below

While you are debugging your program, you can save the content of an internal table.

1.Display the content of the internal table in the debugger.

2.Then press ctrl-F11 or right-mouse and click "Save As Excel Worksheet".

3.Enter the From-index and To-index for the lines you want to save.

4.Choose folder/filename and save.

krishnendu_laha
Active Contributor

‎08-23-2007 6:45 AM

0 Kudos

Hi Saurabh,

to convert a int table into XL file, you just to convert to .XLS format file.

Use this FM SAP_CONVERT_TO_XLS_FORMAT, pass the int table and which file it is going to convert.

Hope it will solve ur problem.

Regards

Krishnendu

Former Member
In response to krishnendu_laha

‎08-24-2007 5:34 AM

0 Kudos

Hi Krishnendu.

Friend, I have to make a report it will be scheduled to run every night . and the result data what I am populating in a internal table . it will go in an attachment through e-mail.

can u send me some more help or any sample report.

Thanks.

Former Member

‎08-26-2007 6:13 AM

0 Kudos

Thanks for help.

if there is any Fm then it will be a great help for me.