ABAP 指定表数据的下载、上传

最新推荐文章于 2023-12-28 10:05:33 发布

lijq21

最新推荐文章于 2023-12-28 10:05:33 发布

阅读量515

点赞数

文章标签： ABAP工具 ABAP tools

本文链接：https://blog.csdn.net/sword11/article/details/123111375

版权

*&---------------------------------------------------------------------*
*& Report ZDOWNLOAD_UPLOAD_TABLE
*&---------------------------------------------------------------------*
*&*1 可以下载指定表格的所有数据到txt文本或excel(.xls)表格中.
*&*2 可以将下载的数据上传到目标系统的对应表格中，上传数据需去除抬头描述行.
*&*3 Author: lijq21@qq.com (20211021)
*&---------------------------------------------------------------------*
REPORT ZDOWNLOAD_UPLOAD_TABLE.
DATA:gt_table TYPE ddfields,
gs_table TYPE dfies,
dy_table TYPE REF TO data,
dy_line TYPE REF TO data,
xfc TYPE lvc_s_fcat,
ifc TYPE lvc_t_fcat,
lv_extension TYPE char4,
lv_lines_c TYPE char10.

DATA : gt_excel_t TYPE zalsmex_tabline OCCURS 0 WITH HEADER LINE, "变量结构参照文件 0000--工具说明.docx -7
gs_excel_t LIKE LINE OF gt_excel_t,
gc_fieldvalue TYPE fieldvalue, "字符值
* gc_fldname TYPE fieldname, "字段名

gs_excel TYPE ole2_object,
gs_workbook TYPE ole2_object,
* gs_activesheet TYPE ole2_object,
gs_sheet TYPE ole2_object,
* gs_cell1 TYPE ole2_object,
* gs_cell2 TYPE ole2_object,
gs_cells TYPE ole2_object,
gs_font TYPE ole2_object,
* gs_page TYPE ole2_object,
* gs_range TYPE ole2_object,
gs_borders TYPE ole2_object,
* gs_rows TYPE ole2_object,
gs_columns TYPE ole2_object.

FIELD-SYMBOLS: <dyn_table> TYPE STANDARD TABLE,
<dyn_wa>,
<dyn_field>.

*----------------------------------------------------------------------*
* Selection screen
*----------------------------------------------------------------------*
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME.
SELECTION-SCREEN: BEGIN OF LINE.
SELECTION-SCREEN: COMMENT 1(20) text001.
PARAMETERS: p_tbma TYPE tabname16." OBLIGATORY.
SELECTION-SCREEN: END OF LINE.

SELECTION-SCREEN SKIP.

SELECTION-SCREEN: BEGIN OF LINE.
SELECTION-SCREEN: COMMENT 1(20) text002.
PARAMETERS: p_file LIKE rlgrap-filename.
SELECTION-SCREEN: END OF LINE.

SELECTION-SCREEN END OF BLOCK b1.

SELECTION-SCREEN BEGIN OF BLOCK b2 WITH FRAME TITLE text003.
*1---download
SELECTION-SCREEN: BEGIN OF LINE.
PARAMETERS : r_1 TYPE c RADIOBUTTON GROUP g1 DEFAULT 'X' USER-COMMAND a.
SELECTION-SCREEN: COMMENT 4(40) text004.
SELECTION-SCREEN: END OF LINE.

*2---uplaod
SELECTION-SCREEN: BEGIN OF LINE.
PARAMETERS : r_2 TYPE c RADIOBUTTON GROUP g1.
SELECTION-SCREEN: COMMENT 4(15) text005.

PARAMETERS: c_box TYPE char1 AS CHECKBOX DEFAULT ''.
SELECTION-SCREEN: COMMENT 22(45) text006.
SELECTION-SCREEN: END OF LINE.
SELECTION-SCREEN END OF BLOCK b2.

SELECTION-SCREEN BEGIN OF BLOCK b3 WITH FRAME.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (70) text007.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (70) text008.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (70) text009.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN END OF BLOCK b3.

*---------------------------------------------------------------------------------*
*INITIALIZATION.
*---------------------------------------------------------------------------------*
INITIALIZATION.

text001 = 'Table Name'.
text002 = 'File Path'.
text003 = 'Operation'.
text004 = 'Download'.
text005 = 'Upload'.
text006 = 'Clear the Table before Upload'.
text007 = '注意：1 文件只支持 txt 和 office2003（.xls）格式；'.
text008 = ' 2 代码支持100列 * 50000行的数据文件导入.'.
text009 = ' 3 导入文件需要去掉抬头描述行，直接导入数据.'.

*--------------------------------------------------------------------------
* AT SELECTION-SCREEN ON VALUE-REQUEST
*--------------------------------------------------------------------------
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
PERFORM frm_get_filepath.

*---------------------------------------------------------------------------------*
*START-OF-SELECTION.
*---------------------------------------------------------------------------------*
START-OF-SELECTION.

IF p_tbma IS INITIAL OR p_file IS INITIAL.
MESSAGE i000(oo) WITH 'Table name and File path is OBLIGATORY!'.
RETURN.
ENDIF.

*--读取表结构，创建动态内表
PERFORM get_tab_strc.

IF r_1 = 'X'."下载
PERFORM show_progress_indicator USING 'Download Processing...'.
IF lv_extension = '.txt'.
PERFORM download_table.
ELSE." lv_extension = '.xls'.
PERFORM create_excel_and_download.
ENDIF.
MESSAGE i000(oo) WITH '下载完成，共下载' lv_lines_c '行数据！' DISPLAY LIKE 'S'.
ELSE. " 上传
PERFORM show_progress_indicator USING 'Upload Processing...'.
IF lv_extension = '.txt'.
PERFORM upload_txt.
ELSE." lv_extension = '.xls'.
PERFORM upload_excel.
ENDIF.

PERFORM update_table.
ENDIF.

END-OF-SELECTION.
*&---------------------------------------------------------------------*
*& Form FRM_GET_FILEPATH
*&---------------------------------------------------------------------*
FORM frm_get_filepath .

DATA : lv_title TYPE string, "标题
lv_fname TYPE string, "名称
lv_path TYPE string, "路径
lv_fpath TYPE string, "路径
lv_dname TYPE string, "名称

file_table TYPE filetable,
wa_file_table TYPE file_table,
rc TYPE i,
lv_len TYPE i.

* CONSTANTS: lc_filter TYPE string VALUE 'Excel(*.xls;*.xlsx)|*.xls;*.xlsx|All(*.*)|*.*|'.

CONSTANTS: lc_filter TYPE string VALUE 'Excel(*.xls)|*.xls|Txt(*.txt)|*.txt|'.

CLEAR : lv_title, lv_fname, lv_path, lv_fpath.

PERFORM get_dynp_value CHANGING p_tbma.

IF r_1 = 'X'.
lv_title = '保存路径选择 :'.
lv_dname = p_tbma.
CONDENSE lv_dname NO-GAPS.
CALL METHOD cl_gui_frontend_services=>file_save_dialog
EXPORTING
window_title = lv_title
default_file_name = lv_dname
default_extension = 'txt'
file_filter = lc_filter
CHANGING
filename = lv_fname
path = lv_path
fullpath = lv_fpath
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.

*---取文件类型
CLEAR: lv_len,lv_extension.
CONDENSE lv_fname NO-GAPS.
lv_len = strlen( lv_fname ).
lv_len = lv_len - 4.
lv_extension = lv_fname+lv_len(4).
TRANSLATE lv_extension TO LOWER CASE.

p_file = lv_fpath.
ELSE.
lv_title = '上传文件路径选择:'.
CALL METHOD cl_gui_frontend_services=>file_open_dialog
EXPORTING
window_title = lv_title
default_extension = 'txt'
CHANGING
file_table = file_table
rc = 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.
CLEAR wa_file_table.
READ TABLE file_table INTO wa_file_table INDEX 1.
p_file = wa_file_table-filename.
ENDIF.
ENDIF.
ENDFORM. " FRM_GET_FILEPATH
*&---------------------------------------------------------------------*
*& Form GET_TAB_STRC
*&---------------------------------------------------------------------*
FORM get_tab_strc .
" CRM系统无该函数
* CALL FUNCTION 'CATSXT_GET_DDIC_FIELDINFO'
* EXPORTING
* im_structure_name = p_tbma
* IMPORTING
* ex_ddic_info = gt_table
* EXCEPTIONS
* failed = 1
* OTHERS = 2.

DATA:lv_tabname TYPE ddobjname.
lv_tabname = p_tbma.
CALL FUNCTION 'DDIF_FIELDINFO_GET'
EXPORTING
tabname = lv_tabname
TABLES
dfies_tab = gt_table
EXCEPTIONS
not_found = 1
internal_error = 2
OTHERS = 3.

LOOP AT gt_table INTO gs_table.
CLEAR xfc.
xfc-fieldname = gs_table-fieldname .
xfc-inttype = gs_table-inttype.
xfc-intlen = gs_table-leng.
xfc-decimals = gs_table-decimals.
APPEND xfc TO ifc.
ENDLOOP.

CALL METHOD cl_alv_table_create=>create_dynamic_table
EXPORTING
it_fieldcatalog = ifc
IMPORTING
ep_table = dy_table.

ASSIGN dy_table->* TO <dyn_table>.

CREATE DATA dy_line LIKE LINE OF <dyn_table>.
ASSIGN dy_line->* TO <dyn_wa>.
ENDFORM. " GET_TAB_STRC

*&---------------------------------------------------------------------*
*& Form UPLOAD_TXT
*&---------------------------------------------------------------------*
FORM upload_txt .
DATA temp_path TYPE string.
temp_path = p_file.

CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
filename = temp_path
filetype = 'ASC'
has_field_separator = 'X'
TABLES
data_tab = <dyn_table>[]
EXCEPTIONS
file_open_error = 1
file_read_error = 2
no_batch = 3
gui_refuse_filetransfer = 4
invalid_type = 5
no_authority = 6
unknown_error = 7
bad_data_format = 8
header_not_allowed = 9
separator_not_allowed = 10
header_too_long = 11
unknown_dp_error = 12
access_denied = 13
dp_out_of_memory = 14
disk_full = 15
dp_timeout = 16
OTHERS = 17.

* DELETE <dyn_table> INDEX 1.
ENDFORM.

*&---------------------------------------------------------------------*
*& Form UPLOAD_EXCEL
*&---------------------------------------------------------------------*
FORM upload_excel.
IF p_file IS NOT INITIAL.
*将excel数据传入内表,此处需注意导入导出选的文件格式XLS/XLSX.如果下面FM行列较多,需要注意
** office2003(.xls)最多256列(2的8次方),66536行(2的16次方)
** office2007(.xlsx)最多16384列(2的14次方),1048576行(2的20次方)
CALL FUNCTION 'ZALSM_EXCEL_TO_INTERNAL_TABLE' "COPY ECC FM: ALSM_EXCEL_TO_INTERNAL_TABLE
EXPORTING
filename = p_file
i_begin_col = '1'
i_begin_row = '1'
i_end_col = '100'
i_end_row = '50000'
TABLES
intern = gt_excel_t.
ENDIF.

IF gt_excel_t[] IS NOT INITIAL.
LOOP AT gt_excel_t INTO gs_excel_t.
AT NEW row.
CLEAR <dyn_wa>.
ENDAT.
IF <dyn_wa> IS INITIAL.
ASSIGN COMPONENT gs_excel_t-col OF STRUCTURE <dyn_wa> TO <dyn_field>.
IF sy-subrc = 0.
<dyn_field> = sy-mandt.
ENDIF.
ELSE.
gc_fieldvalue = gs_excel_t-value.
CONDENSE gc_fieldvalue.
ASSIGN COMPONENT gs_excel_t-col OF STRUCTURE <dyn_wa> TO <dyn_field>.
IF sy-subrc = 0.
<dyn_field> = gc_fieldvalue.
ENDIF.
ENDIF.
AT END OF row.
APPEND <dyn_wa> TO <dyn_table>.
ENDAT.
ENDLOOP.
ENDIF.
ENDFORM. " UPLOAD_EXCEL
*&---------------------------------------------------------------------*
*& Form UPDATE_TABLE
*&---------------------------------------------------------------------*
FORM update_table .
DATA: convert_err TYPE REF TO cx_root,
mess(200) TYPE c.
TRY .
IF c_box = 'X'.
DELETE FROM (p_tbma).
ENDIF.
MODIFY (p_tbma) FROM TABLE <dyn_table>.
IF sy-subrc = 0.
MESSAGE i000(oo) WITH 'Upload Success' DISPLAY LIKE 'S'.
ELSE.
MESSAGE i000(oo) WITH 'Upload Failure' DISPLAY LIKE 'E'.
ENDIF.
CATCH cx_root INTO convert_err.
mess = convert_err->get_text( ).
MESSAGE i000(oo) WITH mess DISPLAY LIKE 'E'.
ENDTRY.

ENDFORM." UPDATE_TABLE

*&---------------------------------------------------------------------*
*& Form CREATE_EXCEL_AND_DOWNLOAD
*&---------------------------------------------------------------------*
FORM create_excel_and_download .

DATA:lv_line TYPE i.

*"step1 create_excel
* 创建application
CREATE OBJECT gs_excel 'EXCEL.APPLICATION'.
IF sy-subrc NE 0 .
CREATE OBJECT gs_excel 'ET.APPLICATION'.
IF sy-subrc NE 0.
MESSAGE 'No EXCEL Creation Possible' TYPE 'E'.
ENDIF.
ENDIF.

SET PROPERTY OF gs_excel 'DisplayAlerts' = 0.

* Put Excel in background 0
SET PROPERTY OF gs_excel 'VISIBLE' = 0.

* 调用excel.application 里面的方法创建一个 excel.workbook.
CALL METHOD OF gs_excel 'WORKBOOKS' = gs_workbook.

SET PROPERTY OF gs_excel 'SheetsInNewWorkbook' = 1.
CALL METHOD OF gs_workbook 'ADD'.

*""step2 create_sheets .

* 指定要被操作的sheet页
CALL METHOD OF gs_excel 'WORKSHEETS' = gs_sheet
EXPORTING
#1 = 1.

* 给指定的sheet页重新命名
CALL METHOD OF gs_sheet 'ACTIVATE'.

SET PROPERTY OF gs_sheet 'NAME' = p_tbma.

*"--插入一行字段描述
LOOP AT gt_table INTO gs_table.
lv_line = sy-tabix.
PERFORM fill_cell_gs USING:1 lv_line 0 11 1 gs_table-fieldtext."field desc
ENDLOOP.

*---插入表值到excel中
PERFORM download_table.

*""step3 CLOSE_SHEETS
SET PROPERTY OF gs_excel 'VISIBLE' = 0.

GET PROPERTY OF gs_excel 'ActiveSheet' = gs_sheet.
FREE OBJECT gs_sheet.
FREE OBJECT gs_workbook.

* 保存并退出
GET PROPERTY OF gs_excel 'ActiveWorkbook' = gs_workbook.
CALL METHOD OF gs_workbook 'SAVEAS' EXPORTING #1 = p_file #2 = 1.
CALL METHOD OF gs_workbook 'CLOSE'.
CALL METHOD OF gs_excel 'QUIT'.

FREE OBJECT gs_sheet.
FREE OBJECT gs_workbook.
FREE OBJECT gs_excel.

ENDFORM."CREATE_EXCEL_AND_DOWNLOAD

*&---------------------------------------------------------------------*
*& Form FILL_CELL_GS
*&---------------------------------------------------------------------*
FORM fill_cell_gs USING p_r1
p_r2
p_bold
p_size
p_linestyle
p_value.

CALL METHOD OF gs_excel 'CELLS' = gs_cells EXPORTING #1 = p_r1 #2 = p_r2.
** 设置被选中单元格的对齐方式
* " && 水平方向　2左对齐，3居中，4右对齐
* SET PROPERTY OF gs_cells 'horizontalAlignment' = 3.
* "&& 垂直方向　1靠上　，2居中，3靠下
* SET PROPERTY OF gs_cells 'VerticalAlignment' = 2.
* "自动换行
* SET PROPERTY OF gs_cells 'WrapText' = 1.
* "设置边框属性
* GET PROPERTY OF gs_cells 'borders' = gs_borders .
* SET PROPERTY OF gs_borders 'weight' = '2'.
* SET PROPERTY OF gs_borders 'linestyle' = p_linestyle.
"设置字体属性
CALL METHOD OF gs_cells 'FONT' = gs_font.
SET PROPERTY OF gs_font 'BOLD' = p_bold. "1:粗体 0:普通字体
SET PROPERTY OF gs_font 'SIZE' = p_size. "字体大小

"设置单元格的值
SET PROPERTY OF gs_cells 'VALUE' = p_value.

*---设置自适应列宽
CALL METHOD OF gs_excel 'COLUMNS' = gs_columns
EXPORTING
#1 = p_r2.
CALL METHOD OF gs_columns 'AutoFit'.

FREE OBJECT gs_font.
FREE OBJECT gs_borders.
FREE OBJECT gs_cells.
FREE OBJECT gs_columns.

ENDFORM.
*&---------------------------------------------------------------------*
*& Form download_table
*&---------------------------------------------------------------------*
FORM download_table .

DATA: struct_type TYPE REF TO cl_abap_structdescr,
comp_tab TYPE cl_abap_structdescr=>component_table,
itab_type TYPE REF TO cl_abap_tabledescr,
dref TYPE REF TO data,
lv_lines TYPE i,
lv_column TYPE i,
col_i TYPE i,
row_i TYPE i.

FIELD-SYMBOLS : <lt_outtab> TYPE STANDARD TABLE,
<ls_outtab> TYPE any,
<fs_value> TYPE any.

struct_type ?= cl_abap_typedescr=>describe_by_name( p_tbma ).
comp_tab = struct_type->get_components( ).
struct_type = cl_abap_structdescr=>create( comp_tab ).
itab_type = cl_abap_tabledescr=>create( struct_type ).

CREATE DATA dref TYPE HANDLE itab_type.
ASSIGN dref->* TO <lt_outtab>.

CREATE DATA dref TYPE HANDLE struct_type.
ASSIGN dref->* TO <ls_outtab>.

SELECT * FROM (p_tbma) INTO CORRESPONDING FIELDS OF TABLE <lt_outtab>.

DESCRIBE TABLE <lt_outtab> LINES lv_lines."行数.
lv_lines_c = lv_lines.

DESCRIBE TABLE gt_table LINES lv_column."列数.

IF lv_extension = '.txt'.
DATA:g_tab,
lv_tabname TYPE ddobjname,
temp_path TYPE string,
ls_dfies_tab TYPE dfies,
lt_dfies_tab TYPE TABLE OF dfies.

DATA: BEGIN OF it_field OCCURS 0 ,
field(50),
END OF it_field.

lv_tabname = p_tbma.
CALL FUNCTION 'DDIF_FIELDINFO_GET'
EXPORTING
tabname = lv_tabname
TABLES
dfies_tab = lt_dfies_tab
EXCEPTIONS
not_found = 1
internal_error = 2
OTHERS = 3.
LOOP AT lt_dfies_tab INTO ls_dfies_tab.
APPEND ls_dfies_tab-fieldname TO it_field.
ENDLOOP.

g_tab = cl_abap_char_utilities=>horizontal_tab.

temp_path = p_file.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = temp_path
filetype = 'DAT'
write_field_separator = g_tab
TABLES
data_tab = <lt_outtab>[]
fieldnames = it_field
EXCEPTIONS
file_write_error = 1
no_batch = 2
gui_refuse_filetransfer = 3
invalid_type = 4
no_authority = 5
unknown_error = 6
header_not_allowed = 7
separator_not_allowed = 8
filesize_not_allowed = 9
header_too_long = 10
dp_error_create = 11
dp_error_send = 12
dp_error_write = 13
unknown_dp_error = 14
access_denied = 15
dp_out_of_memory = 16
disk_full = 17
dp_timeout = 18
file_not_found = 19
dataprovider_exception = 20
control_flush_error = 21
OTHERS = 22.
ELSE." lv_extension = '.xls'.
row_i = 1.
LOOP AT <lt_outtab> INTO <ls_outtab>.
CLEAR:col_i.
row_i = row_i + 1.
DO lv_column TIMES.
col_i = col_i + 1.
ASSIGN COMPONENT col_i OF STRUCTURE <ls_outtab> TO <fs_value>.
PERFORM fill_cell_gs USING: row_i col_i 0 11 1 <fs_value>.
ENDDO.
ENDLOOP.
ENDIF.

ENDFORM.

*&---------------------------------------------------------------------*
*& Form SHOW_PROGRESS_INDICATOR
*&---------------------------------------------------------------------*
FORM show_progress_indicator USING ls_text TYPE c.
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
percentage = 100
text = ls_text.
ENDFORM. " SHOW_PROGRESS_INDICATOR

*&---------------------------------------------------------------------*
*& Form get_dynp_value
*&---------------------------------------------------------------------*
FORM get_dynp_value CHANGING p_h0734.
DATA: BEGIN OF lt_dynpfields OCCURS 0.
INCLUDE STRUCTURE dynpread.
DATA: END OF lt_dynpfields.
CLEAR lt_dynpfields.
REFRESH lt_dynpfields.
lt_dynpfields-fieldname = 'P_TBMA'.
APPEND lt_dynpfields.
CALL FUNCTION 'DYNP_VALUES_READ'
EXPORTING
dyname = sy-repid
dynumb = sy-dynnr
TABLES
dynpfields = lt_dynpfields
EXCEPTIONS
invalid_abapworkarea = 1
invalid_dynprofield = 2
invalid_dynproname = 3
invalid_dynpronummer = 4
invalid_request = 5
no_fielddescription = 6
invalid_parameter = 7
undefind_error = 8
double_conversion = 9
stepl_not_found = 10
OTHERS = 11.
IF sy-subrc = 0.
CLEAR lt_dynpfields.
READ TABLE lt_dynpfields INDEX 1.
p_h0734 = lt_dynpfields-fieldvalue.
ENDIF.
ENDFORM. "get_dynp_value