Question

sap ABAP EXCEL数据批量导入自建表

手工创建一张自建表后，怎样将外部EXCEL导入自建表

Answer 1

以下是基本程序，根据情况只要稍稍修改就可以了~~
1、 REPORT ZLLLL.
data:begin OF ZMSTUDENT,
ZMSID TYPE ZMSID,
ZMCNO TYPE ZMCNO,
ZMKNO TYPE ZMKNO,
ZMNAME TYPE ZMNAME,
ZMSSEX TYPE ZMSSEX,
ZMSAGE TYPE ZMSAGE,
ZMSTEL TYPE ZMSTEL,
END OF ZMSTUDENT.
DATA:itab LIKE TABLE OF ZMSTUDENT WITH HEADER LINE,（存放表）
bdcdata LIKE bdcdata OCCURS 0 WITH HEADER LINE,（执行的参数传递表）
message_table LIKE bdcmsgcoll OCCURS 0 WITH HEADER LINE.（返回执行结果）
DATA: BEGIN OF it_message OCCURS 0,
msgtp TYPE bdc_mart,
matnr LIKE makt-matnr,
text(40),
END OF it_message.

SELECTION-SCREEN BEGIN OF BLOCK BLK WITH FRAME TITLE TEXT-001.
PARAMETERS:P_TYP TYPE CTU_MODE OBLIGATORY DEFAULT'N',
P_FILE LIKE RLGRAP-FILENAME OBLIGATORY.
SELECTION-SCREEN END OF BLOCK BLK.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.
PERFORM SELECT_FILE.
START-OF-SELECTION.
PERFORM GET_FILE_DATA.
PERFORM pross_data.
*-----------------------------------------------------------------
form SELECT_FILE .
DATA : l_FILETAB TYPE FILETABLE,
L_WAFTAB LIKE LINE OF L_FILETAB,
L_RC TYPE I.
CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_OPEN_DIALOG
EXPORTING
WINDOW_TITLE = '打开文件'
initial_DIRECTORY = 'C:/'
CHANGING
FILE_TABLE = L_FILETAB
RC = L_RC
EXCEPTIONS
file_open_dialog_failed = 1
cntl_error = 2
error_no_gui = 3
not_supported_by_gui = 4
OTHERS = 5
.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
EXIT.
ELSE.
READ TABLE l_filetab INTO l_waftab INDEX 1.
p_file = l_waftab-filename.
CLEAR: l_filetab,
l_waftab.
ENDIF.
endform. " SELECT_FILE
*&----------------------------------------------------------------
form GET_FILE_DATA .
DATA lt_excel TYPE TABLE OF alsmex_tabline WITH HEADER LINE.
DATA l_index LIKE sy-tabix.
FIELD-SYMBOLS .
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = p_file
i_begin_col = '1'
i_begin_row = '2'
i_end_col = '50'
i_end_row = '50000'
TABLES
intern = lt_excel
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.
*&& 将EXCEL格式中的数据导入到内表TAB_LOAD中
LOOP AT lt_excel.
MOVE lt_excel-col TO l_index.
* ASSIGN COMPONENT l_index OF STRUCTURE itab TO .
* MOVE lt_excel-value TO .
* AT END OF row.
* TRANSLATE gt_user-uname TO UPPER CASE.
CASE L_INDEX.
WHEN'1'.
MOVE lT_EXCEL-VALUE TO ZMSTUDENT-ZMSID.
WHEN'2'.
MOVE lT_EXCEL-VALUE TO ZMSTUDENT-ZMCNO.
WHEN'3'.
MOVE lT_EXCEL-VALUE TO ZMSTUDENT-ZMKNO.
WHEN'4'.
MOVE lT_EXCEL-VALUE TO ZMSTUDENT-ZMNAME.
WHEN'5'.
MOVE lT_EXCEL-VALUE TO ZMSTUDENT-ZMSSEX.
WHEN'6'.
move lt_excel-value to zmstudent-zmsage.
when'7'.
move lt_excel-value to zmstudent-zmstel.
endcase.
at end of row.
APPEND itab.
CLEAR itab.
ENDAT.
ENDLOOP.
endform. " GET_FILE_DATA
*&----------------------------------------------------------------
form PROSS_DATA .
DATA: l_line1(6).
data: aa(3), bb(3) ,cc(3).
DATA:LC(5).
LOOP AT itab.
perform bdc_dynpro using 'ZMLL001' '2000'.（需要用到的程序名和屏幕号）
perform bdc_field using 'BDC_CURSOR'
'LS_ZMSTUDENT-ZMSTEL'.
perform bdc_field using 'BDC_OKCODE'
'SAVE'.
MOVE ITAB-ZMCNO TO AA.（因为zmcno字段是num型的，所以这里要把它放大一个char型的字段里）
MOVE ITAB-ZMKNO TO BB.
MOVE ITAB-ZMSAGE TO CC.
*****************以下这段是录屏截得的代码~~~~~*******************
perform bdc_field using 'LS_ZMSTUDENT-ZMSID'
itab-zmsid.
* record-ZMSID_001.
perform bdc_field using 'LS_ZMSTUDENT-ZMCNO'
AA.
* itab-ZMCNO.
* record-ZMCNO_002.
perform bdc_field using 'LS_ZMSTUDENT-ZMKNO'
BB.
* itab-ZMKNO.
* record-ZMKNO_003.
perform bdc_field using 'LS_ZMSTUDENT-ZMNAME'
itab-ZMNAME.
* record-ZMNAME_004.
perform bdc_field using 'LS_ZMSTUDENT-ZMSSEX'
itab-ZMSSEX.
* record-ZMSSEX_005.
perform bdc_field using 'LS_ZMSTUDENT-ZMSAGE'
CC.
* itab-ZMSAGE.
* record-ZMSAGE_006.
perform bdc_field using 'LS_ZMSTUDENT-ZMSTEL'
itab-ZMSTEL.
* record-ZMSTEL_007.
ENDLOOP.
PERFORM bdc_field USING 'BDC_OKCODE'
'=BACK1'.
CALL TRANSACTION 'ZMM' USING bdcdata MODE p_typ
UPDATE 'L'
MESSAGES INTO message_table.
DESCRIBE TABLE message_table LINES l_line1.
READ TABLE message_table INDEX l_line1.
IF sy-subrc = 0.
it_message-msgtp = message_table-msgtyp.
* it_message-matnr = itma-matnr.
SELECT SINGLE text
FROM t100
INTO it_message-text
WHERE sprsl = sy-langu AND
arbgb = message_table-msgid AND
msgnr = message_table-msgnr.
REPLACE FIRST OCCURRENCE OF ' &' IN it_message-text WITH message_table-msgv1.
REPLACE FIRST OCCURRENCE OF ' &' IN it_message-text WITH message_table-msgv2.
REPLACE FIRST OCCURRENCE OF ' &' IN it_message-text WITH message_table-msgv3.
REPLACE FIRST OCCURRENCE OF ' &' IN it_message-text WITH message_table-msgv4.
ENDIF.
APPEND it_message.
CLEAR it_message.
CLEAR:message_table,message_table[],bdcdata,bdcdata[].
endform. " PROSS_DATA
*&----------------------------------------------------------------
form BDC_DYNPRO using program dynpro.
CLEAR bdcdata.
bdcdata-program = program.
bdcdata-dynpro = dynpro.
bdcdata-dynbegin = 'X'.
APPEND bdcdata.
endform. " BDC_DYNPRO
*&----------------------------------------------------------------
form BDC_FIELD using fnam fval.
CLEAR bdcdata.
bdcdata-fnam = fnam.
bdcdata-fval = fval.
APPEND bdcdata.
endform. " BDC_FIELD