将EXCEL数据导入SAP数据库表
将EXCEL数据导入SAP数据库表
日期格式转换
EXCEL中的日期格式和SAP数据库中的日期格式不兼容,要将日期格式转换为SAP数据库类型匹配的类型。
*日期格式转换
IF <wa_itab>-value IS NOT INITIAL AND <wa_itab>-value CP '*/*/*'.
CLEAR: zyear, zmon, zday.
SPLIT <wa_itab>-value AT '/' INTO zyear zmon zday.
CONDENSE zyear NO-GAPS.
CONDENSE zmon NO-GAPS.
CONDENSE zday NO-GAPS.
IF strlen( zmon ) = 1.
CONCATENATE '0' zmon INTO zmon.
ENDIF.
IF strlen( zday ) = 1.
CONCATENATE '0' zday INTO zday.
ENDIF.
REPLACE ALL OCCURRENCES OF '-' IN <wa_itab>-value WITH space.
REPLACE ALL OCCURRENCES OF '.' IN <wa_itab>-value WITH space.
REPLACE ALL OCCURRENCES OF '/' IN <wa_itab>-value WITH space.
CONCATENATE zyear zmon zday INTO <wa_itab>-value.
ENDIF.
通过上传EXCEL文件,将文件中的数据导入到数据库表 ZHQ_INCOME_01
1、定义我们要上上传的内容的字段(这些字段要与EXCEL对应);
TABLES: zhq_income_01.
"假设我们要上传的内容有三个字段:学校ID,名称,地址。然后定义类型。 重点:与EXCEL表的要完全对应上"
TYPES:BEGIN OF ty_zhq_income_01,
mandt TYPE sy-mandt ,
zyear TYPE zhq_income_01-zyear ,
company TYPE zhq_income_01-company,
zmonth TYPE zhq_income_01-zmonth ,
income TYPE zhq_income_01-income ,
waers TYPE zhq_income_01-waers ,
END OF ty_zhq_income_01.
"定义参照TY_ZHQ_INCOME_01类型的内表和工作区,用于暂存取到的Excel内容"
DATA:lt_zhq_income_01 TYPE TABLE OF ty_zhq_income_01,
ls_zhq_income_01 TYPE ty_zhq_income_01.
"定义内表,用于insert或modify透明表内容"
DATA:gt_zhq_income_01 TYPE TABLE OF zhq_income_01, "直接参照透明表”
gs_zhq_income_01 TYPE zhq_income_01.
2、设置选择屏幕
"选择屏幕"
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
PARAMETERS p_file(80). "地址栏"
SELECTION-SCREEN END OF BLOCK b1.
3、设置F4搜索帮助,显示选择文件界面
INITIALIZATION.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
PERFORM open_dialog."调用选择文件函数"
AT SELECTION-SCREEN."用于屏幕输入检验"
IF p_file IS INITIAL.
MESSAGE '文件地址不可为空!' TYPE 'S' DISPLAY LIKE 'E'.
STOP.
ENDIF.
START-OF-SELECTION.
PERFORM upload.
END-OF-SELECTION.
FORM open_dialog."选择文件会话。"
DATA:lt_file_table TYPE filetable.
DATA:lw_file_table TYPE file_table.
DATA:l_rc TYPE i.
CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
window_title = '选择文件'
default_filename = '*.XLSX' "默认excel文件"
initial_directory = 'D:\ ' "默认打开D盘,也可以默认空"
multiselection = '' "文件单选"
CHANGING
file_table = lt_file_table
rc = l_rc
EXCEPTIONS
file_open_dialog_failed = 1
cntl_error = 2
error_no_gui = 3
not_supported_by_gui = 4
OTHERS = 5.
CHECK l_rc EQ 1."因为文件单选,所以这里判断一下选择的数量为1"
READ TABLE lt_file_table INDEX 1 INTO p_file. "将选择的文件地址写入到地址栏"
ENDFORM.
4、开始导入EXCEL文件的数据
"最后也是最关键的一步"
FORM upload.
"定义表格结构内表,ALSMEX_TABLINE是具有Excel数据的表行,有三个组件row col value"
DATA: i_excel TYPE TABLE OF alsmex_tabline, "表格结构"
w_excel TYPE alsmex_tabline.
DATA:get_file TYPE rlgrap-filename.
"这里参照系统中的结构字段。"
MOVE p_file TO get_file."将地址栏的值赋值给get_file"
"调用此函数,将excel中的内容以类似坐标的形式存储到i_excel内表中。"
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = get_file
i_begin_col = 1 "起始列"
i_begin_row = 1 "起始行"
i_end_col = 6 "列数"
i_end_row = 21 "行数"
TABLES
intern = i_excel
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.
"删除第一行抬头,如果excel文件中不存在抬头的话,可不写此句。"
* DELETE i_excel WHERE row = 1.
IF i_excel IS INITIAL.
CALL FUNCTION 'POPUP_TO_CONFIRM_WITH_MESSAGE'
EXPORTING
defaultoption = 'Y'
diagnosetext1 = '数据错误'
diagnosetext2 = ' '
diagnosetext3 = ' '
textline1 = 'Excel表格中没有内容!'
titel = '提示'.
RETURN.
ENDIF.
CLEAR:lt_zhq_income_01.
FIELD-SYMBOLS:<f_s>. "这里涉及到SAP内部字段的使用。"
SORT i_excel BY row col.
DATA:num_col TYPE i.
LOOP AT i_excel INTO w_excel.
num_col = w_excel-col."w_excel-col是当前EXCEL行对应的列,则num_col也表示列数"
"assign将结构ls_zhq_income_01的第num_col个字段的值分配给<f_s>,则<f_s>的值改变,ls_zhq_income_01的第num_col个字段的值也改变。"
ASSIGN COMPONENT num_col OF STRUCTURE ls_zhq_income_01 TO <f_s>.
<f_s> = w_excel-value.
"在本行的最后一列值赋值给ls_zhq_income_01后,将ls_zhq_income_01的值append到内表 lt_zhq_income_01"
AT END OF row.
APPEND ls_zhq_income_01 TO lt_zhq_income_01.
CLEAR:ls_zhq_income_01.
ENDAT.
CLEAR:w_excel.
ENDLOOP.
LOOP AT lt_zhq_income_01 INTO ls_zhq_income_01.
gs_zhq_income_01-mandt = sy-mandt."客户端号,创建透明表必须包含的字段。"
gs_zhq_income_01-zyear = ls_zhq_income_01-zyear .
gs_zhq_income_01-company = ls_zhq_income_01-company .
gs_zhq_income_01-zmonth = ls_zhq_income_01-zmonth .
gs_zhq_income_01-income = ls_zhq_income_01-income .
gs_zhq_income_01-waers = ls_zhq_income_01-waers .
APPEND gs_zhq_income_01 TO gt_zhq_income_01.
CLEAR:gs_zhq_income_01.
CLEAR:ls_zhq_income_01.
ENDLOOP.
"将数据库中内容更新"
"这里使用modify,兼顾增加和修改,若主键存在则为修改,否则增加"
MODIFY zhq_income_01 FROM TABLE gt_zhq_income_01.
IF sy-subrc = 0.
COMMIT WORK AND WAIT."成功提交,或者最后程序结束系统也会自动提交"
DATA:num_tab(6) TYPE c.
DESCRIBE TABLE gt_zhq_income_01 LINES num_tab.
CONDENSE num_tab NO-GAPS.
DATA:str_line1 TYPE string .
CONCATENATE '成功导入数据' num_tab '行' INTO str_line1 .
"读出excel中的数据条数,用于消息提示"
CALL FUNCTION 'POPUP_TO_CONFIRM_WITH_MESSAGE'
EXPORTING
diagnosetext1 = '成功'
textline1 = str_line1
titel = '提示'.
ELSE.
ROLLBACK WORK."否则失败”
ENDIF.
ENDFORM.
若要只修改某个字段,则可以用update
LOOP AT lt_zhq_income_01 INTO ls_zhq_income_01.
gs_zhq_income_01-mandt = sy-mandt."客户端号,创建透明表必须包含的字段。"
gs_zhq_income_01-zyear = ls_zhq_income_01-zyear .
gs_zhq_income_01-company = ls_zhq_income_01-company .
gs_zhq_income_01-zmonth = ls_zhq_income_01-zmonth .
gs_zhq_income_01-income = ls_zhq_income_01-income .
* gs_zhq_income_01-waers = ls_zhq_income_01-waers .
UPDATE zhq_income_01 SET income = ls_zhq_income_01-income
WHERE zyear = ls_zhq_income_01-zyear
AND company = ls_zhq_income_01-company
AND zmonth = ls_zhq_income_01-zmonth .
CLEAR:ls_zhq_income_01.
ENDLOOP.
IF sy-subrc = 0.
COMMIT WORK AND WAIT."成功提交,或者最后程序结束系统也会自动提交"
DATA:num_tab(6) TYPE c.
DESCRIBE TABLE lt_zhq_income_01 LINES num_tab.
CONDENSE num_tab NO-GAPS.
DATA:str_line1 TYPE string .
CONCATENATE '成功导入数据' num_tab '行' INTO str_line1 .
"读出excel中的数据条数,用于消息提示"
CALL FUNCTION 'POPUP_TO_CONFIRM_WITH_MESSAGE'
EXPORTING
diagnosetext1 = '成功'
textline1 = str_line1
titel = '提示'.
ELSE.
ROLLBACK WORK."否则失败”
ENDIF.
5、源码
可直接运行(前提是把更新表换成自己需要导入数据的表)
*&---------------------------------------------------------------------*
*& Report Z_TEST_WJ03
*& Description:
*&---------------------------------------------------------------------*
*& Author:
*& Date:
*&---------------------------------------------------------------------*
REPORT z_test_wj03.
TABLES: zhq_income_01.
"假设我们要上传的内容有三个字段:学校ID,名称,地址。然后定义类型。 重点:与EXCEL表的要完全对应上"
TYPES:BEGIN OF ty_zhq_income_01,
mandt TYPE sy-mandt ,
zyear TYPE zhq_income_01-zyear ,
company TYPE zhq_income_01-company,
zmonth TYPE zhq_income_01-zmonth ,
income TYPE zhq_income_01-income ,
waers TYPE zhq_income_01-waers ,
END OF ty_zhq_income_01.
"定义参照TY_ZHQ_INCOME_01类型的内表和工作区,用于暂存取到的Excel内容"
DATA:lt_zhq_income_01 TYPE TABLE OF ty_zhq_income_01,
ls_zhq_income_01 TYPE ty_zhq_income_01.
"定义内表,用于insert或modify透明表内容"
DATA:gt_zhq_income_01 TYPE TABLE OF zhq_income_01, "直接参照透明表"
gs_zhq_income_01 TYPE zhq_income_01.
"选择屏幕"
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
PARAMETERS p_file(80). "地址栏"
SELECTION-SCREEN END OF BLOCK b1.
INITIALIZATION.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
PERFORM open_dialog."调用选择文件函数"
AT SELECTION-SCREEN."用于屏幕输入检验"
IF p_file IS INITIAL.
MESSAGE '文件地址不可为空!' TYPE 'S' DISPLAY LIKE 'E'.
STOP.
ENDIF.
START-OF-SELECTION.
PERFORM upload.
END-OF-SELECTION.
*&---------------------------------------------------------------------*
*& Form OPEN_DIALOG
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM open_dialog."选择文件会话。"
DATA:lt_file_table TYPE filetable.
DATA:lw_file_table TYPE file_table.
DATA:l_rc TYPE i.
CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
window_title = '选择文件'
default_filename = '*.XLSX' "默认excel文件"
initial_directory = 'D:\ ' "默认打开D盘,也可以默认空"
multiselection = '' "文件单选"
CHANGING
file_table = lt_file_table
rc = l_rc
EXCEPTIONS
file_open_dialog_failed = 1
cntl_error = 2
error_no_gui = 3
not_supported_by_gui = 4
OTHERS = 5.
CHECK l_rc EQ 1."因为文件单选,所以这里判断一下选择的数量为1"
READ TABLE lt_file_table INDEX 1 INTO p_file. "将选择的文件地址写入到地址栏"
ENDFORM.
*&---------------------------------------------------------------------*
*& Form UPLOAD
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
"最后也是最关键的一步"
FORM upload.
"定义表格结构内表,ALSMEX_TABLINE是具有Excel数据的表行,有三个组件,row col value 。"
DATA: i_excel TYPE TABLE OF alsmex_tabline, "表格结构。"
w_excel TYPE alsmex_tabline.
DATA:get_file TYPE rlgrap-filename.
"这里参照系统中的结构字段。"
MOVE p_file TO get_file."将地址栏的值赋值给get_file"
"调用此函数,将excel中的内容以类似坐标的形式存储到i_excel内表中。"
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = get_file
i_begin_col = 1
i_begin_row = 1
i_end_col = 6
i_end_row = 21
TABLES
intern = i_excel
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.
"删除第一行抬头。如55果excel文件中不存在抬头的话,可不写此句。"
DELETE i_excel WHERE row = 1.
IF i_excel IS INITIAL.
CALL FUNCTION 'POPUP_TO_CONFIRM_WITH_MESSAGE'
EXPORTING
defaultoption = 'Y'
diagnosetext1 = '数据错误'
diagnosetext2 = ' '
diagnosetext3 = ' '
textline1 = 'Excel表格中没有内容!'
titel = '提示'.
RETURN.
ENDIF.
CLEAR:lt_zhq_income_01.
FIELD-SYMBOLS:<f_s>. "这里涉及到SAP内部字段的使用。"
SORT i_excel BY row col.
DATA:num_col TYPE i.
LOOP AT i_excel INTO w_excel.
num_col = w_excel-col.
ASSIGN COMPONENT num_col OF STRUCTURE ls_zhq_income_01 TO <f_s>.
<f_s> = w_excel-value.
AT END OF row.
APPEND ls_zhq_income_01 TO lt_zhq_income_01.
CLEAR:ls_zhq_income_01.
ENDAT.
CLEAR:w_excel.
ENDLOOP.
LOOP AT lt_zhq_income_01 INTO ls_zhq_income_01.
gs_zhq_income_01-mandt = sy-mandt."客户端号,创建透明表必须包含的字段。"
gs_zhq_income_01-zyear = ls_zhq_income_01-zyear .
gs_zhq_income_01-company = ls_zhq_income_01-company .
gs_zhq_income_01-zmonth = ls_zhq_income_01-zmonth .
gs_zhq_income_01-income = ls_zhq_income_01-income .
gs_zhq_income_01-waers = ls_zhq_income_01-waers .
APPEND gs_zhq_income_01 TO gt_zhq_income_01.
CLEAR:gs_zhq_income_01.
CLEAR:ls_zhq_income_01.
ENDLOOP.
"将数据库中内容更新"
"这里使用modify,兼顾增加和修改,若主键存在则为修改,否则增加"
MODIFY zhq_income_01 FROM TABLE gt_zhq_income_01.
IF sy-subrc = 0.
COMMIT WORK AND WAIT."成功提交,或者最后程序结束系统也会自动提交"
DATA:num_tab(6) TYPE c.
DESCRIBE TABLE gt_zhq_income_01 LINES num_tab.
CONDENSE num_tab NO-GAPS.
DATA:str_line1 TYPE string .
CONCATENATE '成功导入数据' num_tab '行' INTO str_line1 .
"读出excel中的数据条数,用于消息提示"
CALL FUNCTION 'POPUP_TO_CONFIRM_WITH_MESSAGE'
EXPORTING
diagnosetext1 = '成功'
textline1 = str_line1
titel = '提示'.
ELSE.
ROLLBACK WORK."否则失败"
ENDIF.
ENDFORM.
6、结果图
post SAP-Garson
原文链接:https://blog.csdn.net/qq_42427590/article/details/120060307文章来自于网络,如果侵犯了您的权益,请联系站长删除!