Re: Excel file on application server

Former Member — Mon, 29 Jan 2007 15:52:49 GMT

peter,

You just give file extension as .xls for your first requirement.

for second requirement check this code.

REPORT ZWPLC_CREST_DATA NO STANDARD PAGE HEADING

LINE-SIZE 255

LINE-COUNT 65.

TABLES:somlreci1.

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

GLOBAL VARIABLES *

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

DATA g_mask(20) TYPE c VALUE ',., ..'.

DATA v_index TYPE sy-index.

DATA p_index TYPE sy-index.

DATA p_error TYPE C.

DATA p_reform TYPE C.

DATA:w_cnt TYPE i,

w_sent_all(1) TYPE c,

gd_error TYPE sy-subrc,

gd_reciever TYPE sy-subrc,

w_doc_data LIKE sodocchgi1.

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

CONSTANTS *

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

CONSTANTS: con_tab type c value cl_abap_char_utilities=>HORIZONTAL_TAB,

con_cret type c value cl_abap_char_utilities=>newline.

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

STRUCTURES *

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

Structure used for flat file data

TYPES :

BEGIN OF x_data,

data(1020) TYPE c,

END OF x_data.

DATA:

BEGIN OF wa_upload,

spart TYPE spart, "Product Line.

matnr TYPE matnr, "Product Number.

zctry(4) TYPE C,"Geographic Hierarchy Country.

zwstdt TYPE char10, "Start Date.

zweddt TYPE char10, "Change date date.

END OF wa_upload.

FIELD-SYMBOLS: <FS>.

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

INTERNAL TABLES *

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

*DATA:t_upload TYPE STANDARD TABLE OF wa_upload.

DATA:t_zwwwgehrdf LIKE zwwwgehrdf OCCURS 0 WITH HEADER LINE.

DATA:t_zwwwgehrdf_hash LIKE HASHED TABLE OF t_zwwwgehrdf

WITH UNIQUE KEY zwishp WITH HEADER LINE.

DATA:t_mara TYPE STANDARD TABLE OF mara WITH HEADER LINE.

DATA:t_mara_hash LIKE HASHED TABLE OF t_mara

WITH UNIQUE KEY matnr WITH HEADER LINE.

DATA:t_error TYPE STANDARD TABLE OF x_data WITH HEADER LINE.

DATA: it_message TYPE STANDARD TABLE OF solisti1 INITIAL SIZE 0

WITH HEADER LINE.

DATA: it_attach TYPE STANDARD TABLE OF solisti1 INITIAL SIZE 0

WITH HEADER LINE.

DATA: t_packing_list LIKE sopcklsti1 OCCURS 0 WITH HEADER LINE,

t_contents LIKE solisti1 OCCURS 0 WITH HEADER LINE,

t_receivers LIKE somlreci1 OCCURS 0 WITH HEADER LINE,

t_attachment LIKE solisti1 OCCURS 0 WITH HEADER LINE,

t_object_header LIKE solisti1 OCCURS 0 WITH HEADER LINE.

DATA:lw_data TYPE STANDARD TABLE OF x_data WITH HEADER LINE.

DATA:

BEGIN OF t_upload OCCURS 0,

matnr TYPE matnr, "Product Number.

zwgehrrg TYPE zwgehrrg, "Geographic Hierarchy Region.

zwgehrct TYPE zwgehrct, "Geographic Hierarchy Country.

zwgbev TYPE zwgbev, "Global Event Code.

zwpldt TYPE char10, "Planned Date.

zwpltm TYPE char8, "Planned Time.

zwacdt TYPE char10, "Actual date.

zwactm TYPE char8, "Actual Time.

zwenfl TYPE zwenfl, "Encrypt Flag

zwcrdt TYPE char10 , "Created date.

zwcrtm TYPE char8 , "Created Time.

zwcrus TYPE zwcrus , "Created User.

zwchdt TYPE char10, "Changed date

zwchtm TYPE char8, "Change Time.

zwchus TYPE zwchus, "Change User.

xloek TYPE xloek, "Item Is Deleted.

END OF t_upload.

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

SELECTION-SCREEN *

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

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001.

PARAMETER:

p_infile TYPE localfile OBLIGATORY,

p_otfile TYPE localfile OBLIGATORY.

SELECT-OPTIONS: p_email FOR somlreci1-receiver.

SELECTION-SCREEN END OF BLOCK b1.

----

At Selection-Screen- -

----

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_infile.

CALL FUNCTION 'TB_LIMIT_WS_FILENAME_GET'

EXPORTING

def_filename = p_infile

mask = g_mask

title = 'INPUT FILE'

IMPORTING

filename = p_infile

EXCEPTIONS

selection_cancel = 1

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

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_otfile.

CALL FUNCTION 'TB_LIMIT_WS_FILENAME_GET'

EXPORTING

def_filename = p_infile

mask = g_mask

title = 'INPUT FILE'

IMPORTING

filename = p_infile

EXCEPTIONS

selection_cancel = 1

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

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

INITIALIZATION *

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

INITIALIZATION.

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

AT SELECTION SCREEN *

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

TOP-OF-PAGE *

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

START-OF-SELECTION *

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

START-OF-SELECTION.

PERFORM Get_file.

PERFORM write_file.

IF p_error = 'X'.

PERFORM populate_message_body.

ENDIF.

IF p_reform = 'X'.

PERFORM send_attachment tables it_message

it_attach

using

'Crest to PLC Error Report'

'XLS'

p_infile

' '

changing gd_error

gd_reciever.

ENDIF.

END-OF-SELECTION.

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

END-OF-SELECTION *

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

&----

*& Form get_file

&----

FORM get_file.

*Local variables.

DATA :l_date(10) TYPE c.

DATA :l_time(8) TYPE c.

DATA : wa_error TYPE x_data.

DATA : p_sep(1) VALUE ',' .

Determine file path

CALL FUNCTION 'Z_W_PREFIX_PATH'

EXPORTING

logical_file = 'ZLF_NT_DATA'

input_file = p_infile

IMPORTING

filename = p_infile

EXCEPTIONS

file_not_found = 1

too_small = 2

OTHERS = 3.

Creted Date and Time Format

l_date = sy-datum.

concatenate l_date4(2) '/' l_date6(2) '/'

l_date+0(4) into l_date.

l_time = sy-uzeit.

concatenate l_time0(2) ':' l_time2(2) ':'

l_time+4(2) into l_time.

*Get Data From Mara Table.

SELECT matnr spart FROM mara INTO CORRESPONDING FIELDS OF TABLE t_mara

t_mara_hash[] = t_mara[].

*Get data From Zwwwgehrdf Table.

SELECT * FROM zwwwgehrdf INTO TABLE t_zwwwgehrdf.

t_zwwwgehrdf_hash[] = t_zwwwgehrdf[].

*Get File From App server and Process the File.

OPEN DATASET p_infile FOR INPUT IN TEXT MODE encoding default.

IF sy-subrc = 0.

DO.

TRY.

READ DATASET p_infile INTO lw_data.

CATCH cx_sy_conversion_codepage cx_sy_codepage_converter_init.

ENDTRY.

IF sy-subrc = 0.

wa_upload = lw_data.

IF NOT wa_upload-matnr = space.

APPEND lw_data.

ENDIF.

*Split The records at comma.

SPLIT wa_upload AT ',' INTO wa_upload-spart wa_upload-matnr

wa_upload-zctry wa_upload-zwstdt

wa_upload-zweddt.

*validate Material Number And Country.

IF NOT wa_upload IS INITIAL.

READ TABLE t_mara_hash WITH KEY matnr = wa_upload-matnr.

IF sy-subrc = 0.

READ TABLE t_mara WITH KEY matnr = wa_upload-matnr

spart = wa_upload-spart.

IF sy-subrc = 0.

READ TABLE t_zwwwgehrdf_hash WITH KEY zwishp =

wa_upload-zctry.

*Reformat The Internal table.

IF sy-subrc = 0.

*Validate start date and format.

IF NOT wa_upload-zwstdt IS INITIAL.

TRANSLATE wa_upload-zwstdt USING ' $'.

IF NOT wa_upload-zwstdt CA '$'.

MOVE-CORRESPONDING wa_upload TO t_upload.

t_upload-zwgehrct = wa_upload-zctry.

t_upload-zwgehrrg = 'ALL'.

t_upload-zwgbev = 'GA'.

t_upload-zwpldt = wa_upload-zwstdt.

t_upload-zwpltm = '06:00:00'.

t_upload-zwacdt = wa_upload-zwstdt.

t_upload-zwactm = '06:00:00'.

t_upload-zwcrdt = l_date.

t_upload-zwcrtm = l_time.

t_upload-zwcrus = sy-uname.

APPEND t_upload.

ELSE.

CONCATENATE wa_upload-matnr 'Invalid Date Format'

INTO wa_error SEPARATED BY SPACE .

ENDIF.

*Validate End date and format.

IF NOT wa_upload-zweddt IS INITIAL.

TRANSLATE wa_upload-zweddt USING' $'.

IF NOT wa_upload-zweddt CA '$'.

MOVE-CORRESPONDING wa_upload TO t_upload.

t_upload-zwgehrct = wa_upload-zctry.

t_upload-zwgehrrg = 'ALL'.

t_upload-zwgbev = 'ES'.

t_upload-zwpldt = wa_upload-zweddt.

t_upload-zwpltm = '06:00:00'.

t_upload-zwacdt = wa_upload-zweddt.

t_upload-zwactm = '06:00:00'.

t_upload-zwcrdt = l_date.

t_upload-zwcrtm = l_time.

t_upload-zwcrus = sy-uname.

APPEND t_upload.

*Error Handling.

ELSE.

CONCATENATE wa_upload-matnr 'Invalid Date Format'

INTO wa_error SEPARATED BY SPACE .

ENDIF.

ENDIF..

ELSE.

READ TABLE t_zwwwgehrdf_hash WITH KEY zwatctgp =

wa_upload-zctry.

IF sy-subrc = 0.

LOOP AT t_zwwwgehrdf_hash WHERE zwatctgp = wa_upload-zctry.

IF NOT wa_upload-zweddt IS INITIAL.

TRANSLATE wa_upload-zweddt USING' $'.

IF NOT wa_upload-zweddt CA '$'.

MOVE-CORRESPONDING wa_upload TO t_upload.

t_upload-zwgehrct = t_zwwwgehrdf_hash-zwishp.

t_upload-zwgehrrg = 'ALL'.

t_upload-zwgbev = 'GA'.

t_upload-zwpldt = wa_upload-zwstdt.

t_upload-zwpltm = '06:00:00'.

t_upload-zwacdt = wa_upload-zwstdt.

t_upload-zwactm = '06:00:00'.

t_upload-zwcrdt = l_date.

t_upload-zwcrtm = l_time.

t_upload-zwcrus = sy-uname.

APPEND t_upload.

ELSE.

CONCATENATE wa_upload-matnr 'Invalid Date Format'

INTO wa_error SEPARATED BY SPACE .

ENDIF.

*Validate End date and format.

IF NOT wa_upload-zweddt IS INITIAL.

TRANSLATE wa_upload-zweddt USING' $'.

IF NOT wa_upload-zweddt CA '$'.

MOVE-CORRESPONDING wa_upload TO t_upload.

t_upload-zwgehrct = t_zwwwgehrdf_hash-zwishp.

t_upload-zwgehrrg = 'ALL'.

t_upload-zwgbev = 'ES'.

t_upload-zwpldt = wa_upload-zweddt.

t_upload-zwpltm = '06:00:00'.

t_upload-zwacdt = wa_upload-zweddt.

t_upload-zwactm = '06:00:00'.

t_upload-zwcrdt = l_date.

t_upload-zwcrtm = l_time.

t_upload-zwcrus = sy-uname.

APPEND t_upload.

ENDIF.

ENDLOOP.

ELSE.

CONCATENATE wa_upload-zctry

'Invalid Country Code' p_sep INTO wa_error SEPARATED BY

SPACE.

ENDIF.

ELSE.

CONCATENATE wa_upload-spart

'Invalid Product Line ' INTO wa_error SEPARATED BY

SPACE.

READ TABLE t_zwwwgehrdf_hash WITH KEY ZWISHP =

wa_upload-zctry.

IF sy-subrc <> 0.

READ TABLE t_zwwwgehrdf_hash WITH KEY zwatctgp =

wa_upload-zctry.

IF sy-subrc <> 0.

CONCATENATE wa_error p_sep wa_upload-zctry

'Invalid Country Code' INTO wa_error SEPARATED BY SPACE.

ENDIF.

ELSE.

CONCATENATE wa_upload-matnr

'Invalid Material Number '

INTO wa_error SEPARATED BY SPACE.

READ TABLE t_mara WITH KEY matnr = wa_upload-matnr

spart = wa_upload-spart.

IF sy-subrc <> 0.

CONCATENATE wa_error p_sep wa_upload-spart

'Invalid Product Line ' INTO wa_error SEPARATED BY

SPACE .

ENDIF.

READ TABLE t_zwwwgehrdf_hash WITH KEY zwishp =

wa_upload-zctry.

IF sy-subrc <> 0.

READ TABLE t_zwwwgehrdf_hash WITH KEY zwatctgp =

wa_upload-zctry.

IF sy-subrc <> 0.

CONCATENATE wa_error p_Sep wa_upload-zctry

'Invalid Country Code ' INTO wa_error SEPARATED BY SPACE.

ENDIF.

ELSE.

CONTINUE.

ENDIF.

ELSE.

EXIT.

ENDIF.

CLEAR t_upload.

CLEAR wa_upload.

IF wa_error IS NOT INiTIAL.

APPEND wa_error TO t_error.

p_reform = 'X'.

ENDIF.

ENDDO.

SORT t_error BY data.

DELETE ADJACENT DUPLICATES FROM t_error COMPARING data.

it_message = 'Error in File'.

Append it_message.

LOOP AT t_error.

it_attach = t_error.

CONCATENATE con_cret it_attach INTO it_attach .

APPEND it_attach.

ENDLOOP.

ELSE.

CONCATENATE 'Error opening file ' p_infile INTO it_message.

APPEND it_message.

WRITE:/ it_message.

p_error = 'X'.

ENDIF.

ENDFORM. "get_file.

&----

*& Form write_file

&----

text

----

FORM write_file.

DATA l_index TYPE sy-index.

Determine file path

CALL FUNCTION 'Z_W_PREFIX_PATH'

EXPORTING

logical_file = 'ZLF_NT_DATA'

input_file = p_otfile

IMPORTING

filename = p_otfile

EXCEPTIONS

file_not_found = 1

too_small = 2

OTHERS = 3.

*Send Reformated file to App server.

DESCRIBE TABLE t_error LINES p_index.

DESCRIBE TABLE lw_data LINES l_index.

v_index = l_index - p_index.

IF t_upload[] IS NOT INITIAL.

OPEN DATASET p_otfile FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.

IF sy-subrc = 0.

LOOP AT t_upload.

TRANSFER t_upload TO p_otfile.

ENDLOOP.

WRITE 😕 'PLC Crest Reformated File data is downloaded sucessfully to',

60 p_otfile COLOR 7 INVERSE,

/ 'Total number of records Reformated are:',

60 v_index COLOR 7 INVERSE,

/ 'Total number of Error records are:',

60 p_index COLOR 7 INVERSE.

SKIP.

CLOSE DATASET p_otfile.

ELSE.

MESSAGE e398(00) WITH 'Error in opening output file - ' p_otfile.

SKIP.

ENDIF.

ELSE.

WRITE:/ 'PLC Crest Reformated File data is downloaded sucessfully to',

60 p_otfile COLOR 7 INVERSE,

/ 'Total number of records Reformated are:',

60 v_index COLOR 7 INVERSE,

/ 'Total number of Error records are:',

60 p_index COLOR 7 INVERSE.

ENDIF.

ENDFORM. "write_file

&----

*& Form populate_message_body

&----

text

----

FORM populate_message_body.

*Email Message only For File opening Errors.

w_doc_data-obj_name = 'TEST'.

w_doc_data-obj_descr = 'Crest to Plc Data'.

w_doc_data-obj_langu = sy-langu.

loop at p_email.

t_receivers-receiver = p_email-low.

t_receivers-rec_type = 'U'.

append t_receivers.

endloop.

call function 'SO_NEW_DOCUMENT_SEND_API1'

EXPORTING

document_data = w_doc_data

document_type = 'RAW'

put_in_outbox = 'X'

TABLES

object_header = it_message

object_content = it_message

receivers = t_receivers

EXCEPTIONS

too_many_receivers = 1

document_not_sent = 2

document_type_not_exist = 3

operation_no_authorization = 4

parameter_error = 5

x_error = 6

enqueue_error = 7

others = 8.

if sy-subrc = 0.

commit work.

SUBMIT rsconn01 USING SELECTION-SET 'SAP&CONNECTINT' AND RETURN.

else.

MESSAGE s027(vv) WITH 'E-mail not sent'.

endif.

ENDFORM. "populate_message_body

&----

*& Form send_attachment

&----

text

----

-->PIT_MESSAGEtext
-->PIT_ATTACH text
-->P_EMAIL text
-->P_MTITLE text
-->P_FORMAT text
-->P_FILENAME text
-->P_ATTDESCRItextN
-->P_SENDER_ADtextS
-->P_SENDER_ADtext_TYPE
-->P_ERROR text
-->P_RECIEVER text

----

FORM send_attachment tables pit_message

pit_attach

using

p_mtitle

p_format

p_filename

p_attdescription

p_sender_address

p_sender_addres_type

changing p_error

p_reciever.

*Send Error File via Email With Attachment.

DATA:

ld_mtitle LIKE sodocchgi1-obj_descr,

ld_format TYPE so_obj_tp ,

ld_attdescription TYPE so_obj_nam ,

ld_attfilename TYPE so_obj_des ,

ld_sender_address LIKE soextreci1-receiver,

ld_sender_address_type LIKE soextreci1-adr_typ.

ld_mtitle = p_mtitle.

ld_format = p_format.

ld_attdescription = p_filename.

ld_attfilename = p_filename.

ld_sender_address = p_sender_address.

ld_sender_address_type = p_sender_addres_type.

w_doc_data-doc_size = 1.

w_doc_data-obj_langu = sy-langu.

w_doc_data-obj_name = 'SAPRPT'.

w_doc_data-obj_descr = ld_mtitle .

w_doc_data-sensitivty = 'F'.

Fill the document data and get size of attachment

CLEAR w_doc_data.

READ TABLE it_attach INDEX w_cnt.

w_doc_data-doc_size =

( w_cnt - 1 ) * 255 + STRLEN( it_attach ).

w_doc_data-obj_langu = sy-langu.

w_doc_data-obj_name = 'SAPRPT'.

w_doc_data-obj_descr = ld_mtitle.

w_doc_data-sensitivty = 'F'.

CLEAR t_attachment.

REFRESH t_attachment.

t_attachment[] = pit_attach[].

Describe the body of the message

CLEAR t_packing_list.

REFRESH t_packing_list.

t_packing_list-transf_bin = space.

t_packing_list-head_start = 1.

t_packing_list-head_num = 0.

t_packing_list-body_start = 1.

DESCRIBE TABLE it_message LINES t_packing_list-body_num.

t_packing_list-doc_type = 'RAW'.

APPEND t_packing_list.

Create attachment notification

t_packing_list-transf_bin = 'X'.

t_packing_list-head_start = 1.

t_packing_list-head_num = 1.

t_packing_list-body_start = 1.

DESCRIBE TABLE t_attachment LINES t_packing_list-body_num.

t_packing_list-doc_type = ld_format.

t_packing_list-obj_descr = p_filename.

t_packing_list-obj_name = ld_attfilename.

t_packing_list-doc_size = t_packing_list-body_num * 255.

APPEND t_packing_list.

Add the recipients email address

CLEAR t_receivers.

REFRESH t_receivers.

LOOP AT p_email.

t_receivers-receiver = p_email-low.

t_receivers-rec_type = 'U'.

t_receivers-com_type = 'INT'.

APPEND t_receivers.

ENDLOOP.

CALL FUNCTION 'SO_DOCUMENT_SEND_API1'

EXPORTING

document_data = w_doc_data

put_in_outbox = 'X'

commit_work = 'X'

IMPORTING

sent_to_all = w_sent_all

TABLES

packing_list = t_packing_list

contents_bin = t_attachment

contents_txt = it_message

receivers = t_receivers

EXCEPTIONS

too_many_receivers = 1

document_not_sent = 2

document_type_not_exist = 3

operation_no_authorization = 4

parameter_error = 5

x_error = 6

enqueue_error = 7

OTHERS = 8.

if sy-subrc = 0.

commit work.

SUBMIT rsconn01 USING SELECTION-SET 'SAP&CONNECTINT' AND RETURN.

else.

MESSAGE s027(vv) WITH 'E-mail not sent'.

endif.

ENDFORM. "send_attachment

for ur 3 rd requirement . use open dataset and store data in internal table then use gui_download.

Hope this sloves your problem.

topic Re: Excel file on application server in Application Development and Automation Discussions

Excel file on application server

Re: Excel file on application server

Re: Excel file on application server