Skip to Content
avatar image
Former Member

Ulpoad Internal table to application server in excel format

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

11 Answers

  • Best Answer
    Jul 01, 2008 at 08:31 AM
    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 01, 2008 at 09:41 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

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

  • avatar image
    Former Member
    Jul 01, 2008 at 08:46 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 01, 2008 at 08:48 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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

  • Jul 01, 2008 at 08:52 AM

    You can use

    function module

    ARCHIVFILE_TABLE_TO_SERVER to move table lines to application server path

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 01, 2008 at 09:18 AM

    Use func. module TEXT_CONVERT_XLS_TO_SAP

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 01, 2008 at 09:23 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 01, 2008 at 09:40 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 01, 2008 at 09:52 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 01, 2008 at 10:43 AM

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

    Add comment
    10|10000 characters needed characters exceeded