闲来无事,写了个小程序给业务顾问用
功能有两个:
1.输入数据库表名称,点击下载模板,将数据库表字段和文本描述用OLE输出至EXCEL;
2.输入表名,将刚刚填充完数据的EXCEL模板选择,执行后将模板数据导入对应的数据库表。
下图为选择界面
下图为下载EXCEL模板时OLE输出示例
代码如下:
*&---------------------------------------------------------------------*
*& Report YWTEST002
*&---------------------------------------------------------------------*
*&Date Author Change Req No. Ver.Description
*&2022.03.09 王浩辉 1.Create new version
*&---------------------------------------------------------------------*
REPORT ywtest002.
TABLES:sscrfields.
TYPES:BEGIN OF ty_dntab.
INCLUDE STRUCTURE dntab.
TYPES:END OF ty_dntab.
TYPES:tt_dntab TYPE STANDARD TABLE OF ty_dntab.
"'请勿改动下载模板的格式,最大允许9999行'
SELECTION-SCREEN BEGIN OF BLOCK b01 WITH FRAME TITLE TEXT-001.
PARAMETERS:p_tname TYPE tabname OBLIGATORY,
p_file TYPE localfile.
SELECTION-SCREEN END OF BLOCK b01.
SELECTION-SCREEN FUNCTION KEY 1.
INITIALIZATION.
sscrfields-functxt_01 = '下载EXCEL模板'.
AT SELECTION-SCREEN.
CASE sscrfields-ucomm.
WHEN 'FC01'.
PERFORM frm_download_excel_template.
ENDCASE.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
PERFORM frm_get_excel USING p_file.
START-OF-SELECTION.
PERFORM frm_upload.
*&---------------------------------------------------------------------*
*& Form FRM_DOWNLOAD_EXCEL_TEMPLATE
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_download_excel_template .
DATA: lo_excel TYPE ole2_object,
lo_sheet TYPE ole2_object,
lo_book TYPE ole2_object,
lo_cell TYPE ole2_object,
lo_font TYPE ole2_object,
lo_range TYPE ole2_object,
lo_color TYPE ole2_object,
lo_column TYPE ole2_object,
lo_workbook TYPE ole2_object,
lo_columns TYPE ole2_object,
lo_border TYPE ole2_object,
lt_fcat TYPE lvc_t_fcat.
DATA:lt_table TYPE tt_dntab,
llo_line TYPE i,
llo_col TYPE i.
***宏 需要经常用到的变量
DEFINE %%set_cell.
CALL METHOD OF lo_excel 'CELLS' = lo_cell
EXPORTING
#1 = &1 "第一行
#2 = &2 ."第2列
SET PROPERTY OF lo_cell 'VALUE' = &3. "设置上面第一行 第二列的值 默认值
CALL METHOD OF lo_cell 'FONT' = lo_font.
SET PROPERTY OF lo_font 'BOLD' = &4 . "加粗
SET PROPERTY OF lo_font 'SIZE' = &5. "字体大小
IF &6 = 'X'."居中
SET PROPERTY OF lo_cell 'HORIZONTALALIGNMENT' = -4108.
ENDIF.
END-OF-DEFINITION.
"获取字段
PERFORM frm_set_field CHANGING lt_table.
DESCRIBE TABLE lt_table LINES llo_line.
CREATE OBJECT lo_excel 'EXCEL.APPLICATION'. "创建excel 对象
CALL METHOD OF lo_excel 'WORKBOOKS' = lo_workbook.
CALL METHOD OF lo_workbook 'ADD'. "增加workbook
SET PROPERTY OF lo_excel 'VISIBLE' = 1 . "1 前台 0 后台
CALL METHOD OF lo_range 'SELECT'.
SET PROPERTY OF lo_range 'MERGECELLS' = 1.
"设置所有列为文本格式
CALL METHOD OF lo_sheet 'Columns' = lo_column.
CALL METHOD OF lo_column 'AutoFit'.
SET PROPERTY OF lo_column 'NumberFormatLocal' = '@'.
CLEAR llo_col.
LOOP AT lt_table ASSIGNING FIELD-SYMBOL(<fs_tab>).
llo_col = llo_col + 1.
%%set_cell:1 llo_col <fs_tab>-fieldtext 0 10 'X'."表的标题
%%set_cell:2 llo_col <fs_tab>-fieldname 0 10 'X'."表的字段名
ENDLOOP.
CALL METHOD OF lo_excel 'COLUMNS' = lo_columns.
CALL METHOD OF lo_columns 'AUTOFIT'.
**释放资源:
FREE OBJECT lo_sheet.
FREE OBJECT lo_workbook.
FREE OBJECT lo_excel.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_UPLOAD
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
FORM frm_upload .
DATA:lo_datat TYPE REF TO data,
lt_fcat TYPE lvc_t_fcat,
lt_msgt TYPE ycl_upload=>tt_msgt,
lt_table TYPE tt_dntab,
lv_eor TYPE char1.
FIELD-SYMBOLS:<fs_tab> TYPE STANDARD TABLE.
PERFORM frm_set_field CHANGING lt_table.
*根据取出的字段目录生成参考字段目录
LOOP AT lt_table ASSIGNING FIELD-SYMBOL(<fs_table>).
APPEND VALUE #( fieldname = <fs_table>-fieldname
ref_table = p_tname
ref_field = <fs_table>-fieldname ) TO lt_fcat.
ENDLOOP.
*内表创建
CALL METHOD cl_alv_table_create=>create_dynamic_table
EXPORTING
it_fieldcatalog = lt_fcat
IMPORTING
ep_table = lo_datat.
ASSIGN lo_datat->* TO <fs_tab>.
ycl_upload=>upload( EXPORTING iv_file = p_file
iv_brow = 3
iv_erow = 9999
iv_bcol = 1
IMPORTING et_msgt = lt_msgt
CHANGING ct_data = <fs_tab>
).
IF lt_msgt IS NOT INITIAL.
"展示错误
cl_demo_output=>display( lt_msgt ).
ELSE.
"保存数据
LOOP AT <fs_tab> ASSIGNING FIELD-SYMBOL(<fs_line>).
MODIFY (p_tname) FROM <fs_line>.
IF sy-subrc NE 0.
lv_eor = abap_true.
EXIT.
ENDIF.
ENDLOOP.
IF lv_eor = abap_false.
COMMIT WORK.
MESSAGE '导入成功' TYPE 'S'.
ELSE.
ROLLBACK WORK.
MESSAGE '导入失败' TYPE 'S' DISPLAY LIKE 'E'.
ENDIF.
LEAVE LIST-PROCESSING.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form frm_set_field
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& <-- ct_fcat
*&---------------------------------------------------------------------*
FORM frm_set_field CHANGING ct_table TYPE tt_dntab.
CALL FUNCTION 'NAMETAB_GET'
EXPORTING
langu = sy-langu
tabname = p_tname
TABLES
nametab = ct_table
EXCEPTIONS
no_texts_found = 1.
IF sy-subrc NE 0.
MESSAGE '该表不存在于数据库!' TYPE 'S' DISPLAY LIKE 'E'.
LEAVE LIST-PROCESSING.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form FRM_GET_EXCEL
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> P_FILE
*&---------------------------------------------------------------------*
FORM frm_get_excel USING p_file.
***选择文件对话框
DATA: lt_filetab TYPE filetable,
lv_rc TYPE i.
* Open dialog
CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
default_filename = '*.xls'
initial_directory = 'd:\'
multiselection = ''
CHANGING
file_table = lt_filetab
rc = lv_rc
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.
* Get file path
CHECK lv_rc EQ 1.
READ TABLE lt_filetab INDEX 1 INTO p_file.
ENDFORM.