将Excel file上转到Internal table的方法.doc

將Excel file上轉到Internal table的方法1 定義輸入文件(Local file for upload/download)SELECTION-SCREEN BEGIN OF BLOCK b2 WITH FRAME TITLE text-002.PARAMETERS p_file LIKE rlgrap-filename OBLIGATORY MODIF ID fin.SELECTION-SCREEN END OF BLOCK b2.2 Search help for get download path from PCAT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.PERFORM get_pc_download_path(zbar00001) USING XCHANGING p_file.ENDIF.*&-*& Search help for get download path form PC*-*FORM get_pc_download_path USING value(p_value)CHANGING p_pcfile LIKE rlgrap-filename.CALL FUNCTION KD_GET_FILENAME_ON_F4EXPORTINGstatic = p_valueCHANGINGfile_name = p_pcfile.ENDFORM. get_pc_download_path3 Transfer Excel record to internal tableFORM transfer_to_it_file .DATA : lt_intern TYPE alsmex_tabline OCCURS 0 WITH HEADER LINE.DATA : l_row TYPE i,l_col TYPE i,l_tabix TYPE i.CONSTANTS: c_linesize TYPE i VALUE 132.DATA: l_row_st TYPE i VALUE 2,l_row_ed TYPE i VALUE 65535,l_col_st TYPE i VALUE 1,l_col_ed TYPE i VALUE 15.*&fuction to upload excel data into internal table lt_internCALL FUNCTION ALSM_EXCEL_TO_INTERNAL_TABLEEXPORTINGfilename = p_filei_begin_col = l_col_sti_begin_row = l_row_sti_end_col = l_col_edi_end_row = l_row_edTABLESintern = lt_internEXCEPTIONSinconsistent_parameters = 1upload_ole = 2OTHERS = 3.*&if file format is wrong etc., show error message.IF sy-subrc 0.MESSAGE s240(s#) WITH Error upload to internal table!.EXIT.ENDIF.*&no data in excel fileIF lt_intern IS INITIAL.MESSAGE s240(s#) WITH No data be upload!.EXIT.ELSE.SORT lt_intern BY row col.LOOP AT lt_intern.l_row = lt_intern-row.l_col = lt_intern-col + 1.l_tabix = sy-tabix.ASSIGN COMPONENT l_col OF STRUCTURE it_file TO . = lt_intern-value.AT END OF row.* when kpein = 0 have errorIF it_tab-kpein = 0.it_tab-kpein = 1.ENDIF.it_file-zzsite = p_site.it_file-zzerdat = g_erdat.it_file-erdat = sy-datum.it_file-erzet = sy-uzeit.it_file-zzusdid = g_userid.* Check DN number and item exists or not.PERFORM convert_alpha_input USING it_file-vbeln.PERFORM convert_alpha_input USING it_file-posnr.PERFORM check_dn USING it_file-vbeln it_file-posnr l_row.* Check date formatPERFORM check_date USING it_file-exdat l_row.* Check currencyTRANSLATE it_file-waers TO UPPER CASE.PERFORM check_currency USING it_file-waers l_row.* Check amount formatPERFORM check_amount USING it_file-zzamont l_row.* Check price unit valuePERFORM check_unit USING it_file-kpein l_row.MOVE-CORRESPONDING it_file TO it_duplicate.it_duplicate-count = 1.COLLECT it_duplicate.IF it_err IS INITIAL.MOVE-CORRESPONDING it_file TO it_tab.APPEND it_tab.ENDIF.CLEAR: it_tab, it_file.ENDAT.ENDLOOP.ENDIF.*&Check Record duplicate or notPERFORM check_duplicate_record.ENDFORM. transfer_to_it_file4 Check Record duplicate or notFORM check_duplicate_record .DATA : l_count TYPE i,l_message(50).CLEAR: l_message.LOOP AT it_duplicate.