Ulpoad Internal table to application server in excel format

[Former Member]

Hi All,

I have all my data in an internal table.I want to Upload this data to the Application server in the EXCEL sheet with the Column Header.

How can i do this.Is there any standarad function module or class to to write in xls format.

Can anybody help me.

Thanks,

Swapna

[former_member181995]

Swapana,

open data set

transfer (upload to application server) / Read (dowlnoad from appl. server)

close data set

http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb3ca6358411d1829f0000e829fbfe/frameset.htm

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/e0901cba-f49e-2910-748c-d7ce4c0c...

Amit.

[Former Member]

hi.....

I dont think you can take column headers into your excel file on application server.

you can use open data set close dataset for downloading file to your application server..

First you need to convert the data from your internal tablein CSV frmat and then download the file on application server.

here is the sample code for this purpose.

CALL FUNCTION 'SAP_CONVERT_TO_CSV_FORMAT'

EXPORTING

i_field_seperator = ';'

• I_LINE_HEADER =

• I_FILENAME =

• I_APPL_KEEP = ' '

TABLES

i_tab_sap_data = tfile_data

CHANGING

i_tab_converted_data = it_trans

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

IF sy-subrc NE 0.

MESSAGE e000(yw) WITH 'Data is not available'.

ENDIF.

SELECT SINGLE paramval FROM zmm_params

INTO path

WHERE pgmna = 'ZREP_SAP_TO_CLRTY'

AND paramid = 'PATH'

AND fromdt <= sy-datum

AND todt >= sy-datum.

IF sy-mandt EQ '300'.

path = '/statd40/'.

ENDIF.

CONDENSE path.

CONCATENATE path 'BNP_SAP_SCR_STATUS_' sy-datum '.CSV' INTO dsn.

CONDENSE dsn.

OPEN DATASET dsn FOR OUTPUT IN TEXT MODE.

IF sy-subrc EQ 0.

LOOP AT it_trans INTO wt_trans.

CLEAR line.

TRANSLATE wt_trans-truxs_t_text_data USING ';,'.

MOVE wt_trans-truxs_t_text_data TO line.

TRANSFER line TO dsn.

ENDLOOP.

CLOSE DATASET dsn.

*ENDIF.

CLEAR : cmdtxt.

• concatenate 'chmod 777' '/infocomm/mm/getfile' into

CONCATENATE 'chmod 777' dsn INTO

cmdtxt SEPARATED BY space.

CALL 'SYSTEM' ID 'COMMAND' FIELD cmdtxt

ID 'TAB' FIELD i_tty[].

MESSAGE i001(yw) WITH 'File is downloaded successfully'.

ENDIF.

[Former Member]

Hi Amit,

I am able to upload the file using the open dataset and transfer in text format with out colum headings .But i want to upload theinternal table to Application server not to presentaton server with the Column headings in Excel Format.

Thnank

[Subhankar]

Hi,

Please do it in this way. I think it will be working fine.

Make sure after populating the final table just populate the column name at first row of final table.

data: l_fname type string.

CONSTANTS: c_tab TYPE abap_char1

VALUE cl_abap_char_utilities=>horizontal_tab. "Tab Char

l_fname = p_fname.

• Open the file in application server for writing

OPEN DATASET p_fname FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.

• MESSAGE v_message.

IF sy-subrc = 0.

LOOP AT i_final INTO wa_final.

• Convert error data to tab delimited string

PERFORM sub_convert_to_tab_data USING wa_final

CHANGING l_wa_down_tab-data.

TRANSFER l_wa_down_tab-data TO p_fname.

CLEAR l_wa_down_tab.

ENDLOOP.

ENDIF.

• Close the source file

CLOSE DATASET p_fname.

FORM sub_convert_to_tab_data USING p_wa_final TYPE x_final

CHANGING p_wa_down_tab TYPE any.

CONCATENATE p_wa_final-sum_comm " Lead column

• p_wa_final-racct " Account number

l_fobac " FOB Accrual

l_excess " Excess

l_obsole " Obsolescence

l_ppv " PPV

l_fr_var " Freight Variances

l_prod_or_var " Production Order Variance

l_dep_var " Departmental Variance

l_oth_prod_var " Other Production Variance

l_pr_or_scrp_var " Variance

l_cost_of_acc " Cost of Sales Accounts

l_inv_acc " Inventory Accounts

INTO p_wa_down_tab

SEPARATED BY c_tab.

ENDFORM. " sub_convert_to_tab_data

[jj]

You can use

function module

ARCHIVFILE_TABLE_TO_SERVER to move table lines to application server path

[Former Member]

Use func. module TEXT_CONVERT_XLS_TO_SAP

[Former Member]

I hope you are unable to get the column heading right in EXCEL??

If so use opendata set in the below way.

OPEN DATASET I_PHYSICAL_FNAME FOR OUTPUT IN TEXT MODE ENCODING UTF-8.

• -header beginu2019

Concatenate `"` u2018coulumn_head1u2019 `",`

`"` u2018coulumn_head2u2019 `",`

`"` u2018coulumn_head3u2019 `,` into WA_ITAB_TEXT.

*-head end

Clear WA_ITAB_TEXT.

LOOP AT ITAB into WA_ITAB_TEXT.

**-for each record in ITAB build a CSV row and move to * application serveru2026

Concatenate `"` WA_ITAB_TEXT-coulumn_feild1 `",`

`"` WA_ITAB_TEXT-coulumn_feild2 `",`

`"` WA_ITAB_TEXT-coulumn_feild3 `,` into WA_ITAB_new.

TRANSFER WA_ITAB_TEXT TO I_PHYSICAL_FNAME.

Clear: WA_ITAB_TEXT, WA_ITAB_new.

ENDLOOP.

CLOSE DATASET I_PHYSICAL_FNAME.

If you have issues please write to meu2026

[Former Member]

Hi Raghu,

Can you give me the fulll code for ulpading the intzernal table data to the application server in excel format with colum headings

[Former Member]

You can try with this program.by using this u can create new excel sheet with the content of existing notepad or ecxel sheet i.e. in the output its asking file upload then u can upload notepad data or excel sheet data.

TYPE-POOLS: truxs.

PARAMETERS: p_file TYPE rlgrap-filename.

INCLUDE ole2incl.

DATA: application TYPE ole2_object,

workbook TYPE ole2_object,

sheet TYPE ole2_object,

cells TYPE ole2_object.

TYPES: BEGIN OF t_datatab,

col1(3) TYPE c,

col2(70) TYPE c,

col3(10) TYPE c,

col4(30) TYPE c,

col5(30) TYPE c,

col6(30) TYPE c,

col7(30) TYPE c,

col8(30) TYPE c,

col9(30) TYPE c,

END OF t_datatab.

DATA: it_datatab type standard table of t_datatab with header line,

wa_datatab type t_datatab.

DATA: it_raw TYPE truxs_t_text_data.

CONSTANTS: row_max TYPE i VALUE 256.

DATA index TYPE i.

CALL METHOD OF application 'Workbooks' = workbook.

CALL METHOD OF workbook 'Add'.

• At selection screen

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

field_name = 'P_FILE'

IMPORTING

file_name = p_file.

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

*START-OF-SELECTION.

START-OF-SELECTION.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR = 'X'

• i_line_header = 'X'

i_tab_raw_data = it_raw " WORK TABLE

i_filename = p_file

TABLES

i_tab_converted_data = it_datatab[] "ACTUAL 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.

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

• END-OF-SELECTION.

END-OF-SELECTION.

LOOP AT it_datatab INTO wa_datatab.

WRITE:/ wa_datatab-col1,

wa_datatab-col2,

wa_datatab-col3,

wa_datatab-col4,

wa_datatab-col5,

wa_datatab-col6,

wa_datatab-col7,

wa_datatab-col8,

wa_datatab-col9.

ENDLOOP.

*START-OF-SELECTION

START-OF-SELECTION.

CREATE OBJECT application 'excel.application'.

SET PROPERTY OF application 'visible' = 1.

CALL METHOD OF application 'Workbooks' = workbook.

CALL METHOD OF workbook 'Add'.

CALL METHOD OF application 'Worksheets' = sheet

EXPORTING #1 = 1.

SET PROPERTY OF sheet 'Name' = 'surya'.

CALL METHOD OF sheet 'Activate'.

CALL METHOD OF sheet 'SaveAs'

EXPORTING #1 = 'e:\surya.xls' "filename

#2 = 1. "fileFormat

CALL FUNCTION 'WS_DOWNLOAD'

EXPORTING

filename = 'e:\surya.xls'

filetype = 'DAT'

TABLES

data_tab = it_datatab[]

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

OTHERS = 10.

IF sy-subrc = 0.

MESSAGE i000(zf) WITH text-017.

ELSE.

MESSAGE e939(zf) WITH 'Errors while downloading'.

ENDIF.

*ENDCASE.

*LOOP AT it_datatab INTO wa_datatab.

• WRITE:/ wa_datatab-col1,

• wa_datatab-col2,

• wa_datatab-col3,

• wa_datatab-col4,

• wa_datatab-col5,

• wa_datatab-col6,

• wa_datatab-col7,

• wa_datatab-col8,

• wa_datatab-col9.

*

*ENDLOOP.

if it helps you give me rewardpoints.

Thank you.

[Former Member]

Hello surya narayana,

First of all the read the question for what purpose the thread is.One more thing WS_DOWNLOAD is obsoleted FM.Dont use this Function module any time.

[Former Member]

use datasets to pass data to application server.

inbetween open and close data sets use transfer statement.

to send data to application server in excel format we need to use column separater as '09'.

FORM download_data.

DATA:

l_str TYPE string, " String to hold itab line

l_losmenge(13) TYPE c. " Inspection Lot Quantity

CONSTANTS: lc_tab TYPE x VALUE '09'. " Tab delimitor

CLEAR g_true.

• Open data set for the given path

OPEN DATASET p_afile FOR OUTPUT IN TEXT MODE

MESSAGE l_msg.

• Input data into file

IF g_true EQ c_true.

LOOP AT report_tab.

l_losmenge = report_tab-losmenge.

CONCATENATE report_tab-prueflos

report_tab-line

report_tab-matnr

l_losmenge

report_tab-pastrterm

report_tab-ersteller

report_tab-lagortchrg

INTO l_str SEPARATED BY lc_tab.

TRANSFER l_str TO p_afile.

CLEAR: l_str,l_losmenge,report_tab.

ENDLOOP. " LOOP AT REPORT_TAB

• Close data set

CLOSE DATASET p_afile.

ENDIF. " IF G_TRUE EQ C_TRUE

ENDFORM. " DOWNLOAD_DATA

[Former Member]

Hi Please find code....

to move heading

PERFORM form_col_heading.

Item data moving

LOOP AT it_final_data INTO wa_final_data.

PERFORM concatenate_to_csv.

ENDLOOP.

*to move applicatio server

OPEN DATASET i_physical_fname FOR OUTPUT IN TEXT MODE ENCODING UTF-8.

LOOP AT itb_csv.

TRANSFER itb_csv TO i_physical_fname.

ENDLOOP.

CLOSE DATASET i_physical_fname.

*&----

---

**& Form form_col_heading

*&----

---

FORM form_col_heading .

CONCATENATE :

`"` text-004 `",` " User id.

`"` text-005 `",` " User e-mail address

`"` text-006 `",` " Workplace Code

`"` text-007 `",` " Data type

`"` text-008 `",` " Data creation date

`"` text-009 `",` " Program name

`"` text-074 `"` " Split delivery due NLT

INTO itb_csv-rec_txt.

APPEND itb_csv.

CLEAR itb_csv.

ENDFORM. " form_col_heading

*&----

---

**& Form concatenate_to_csv

*&----

---

FORM concatenate_to_csv .

CONCATENATE :

`"` wa_final_data-userid `",`

`"` wa_final_data-mailid `",`

`"` wa_final_data-dept `",`

`"` wa_final_data-auart `",`

`"` wa_final_data-date_time `",`

`"` wa_final_data-datetime1 `"`

INTO itb_csv1-rec_txt.

APPEND itb_csv.

CLEAR itb_csv.

ENDFORM. " concatenate_to_csv

still you have any doubt, please post here...

please reward points if useful...

[Former Member]

Hi...

The funda is simple, for excel if u pass any sting in "" it will take it as per one cell.

So u need to pad the "" for header as well as item data and thats what i am doing below.

and the type of itb_csv is..

DATA: BEGIN OF itb_csv OCCURS 0,

rec_txt(10000) TYPE c,

END OF itb_csv .

to move heading

PERFORM form_col_heading.

Item data moving

LOOP AT it_final_data INTO wa_final_data.

PERFORM concatenate_to_csv.

ENDLOOP.

*to move applicatio server

OPEN DATASET i_physical_fname FOR OUTPUT IN TEXT MODE ENCODING UTF-8.

LOOP AT itb_csv.

TRANSFER itb_csv TO i_physical_fname.

ENDLOOP.

CLOSE DATASET i_physical_fname.

*&----

---

**& Form form_col_heading

*&----

---

FORM form_col_heading .

CONCATENATE :

`"` text-004 `",` " User id.

`"` text-005 `",` " User e-mail address

`"` text-006 `",` " Workplace Code

`"` text-007 `",` " Data type

`"` text-008 `",` " Data creation date

`"` text-009 `",` " Program name

`"` text-074 `"` " Split delivery due NLT

INTO itb_csv-rec_txt.

APPEND itb_csv.

CLEAR itb_csv.

ENDFORM. " form_col_heading

*&----

---

**& Form concatenate_to_csv

*&----

---

FORM concatenate_to_csv .

CONCATENATE :

`"` wa_final_data-userid `",`

`"` wa_final_data-mailid `",`

`"` wa_final_data-dept `",`

`"` wa_final_data-auart `",`

`"` wa_final_data-date_time `",`

`"` wa_final_data-datetime1 `"`

INTO itb_csv1-rec_txt.

APPEND itb_csv.

CLEAR itb_csv.

ENDFORM. " concatenate_to_csv