************************************************************************ * Program Name : ole test * Descriptions : * T-Code : * Updates Tables : * Input Parameters : * Output Parameters : * Return Codes : * Special Logic : * Includes : ************************************************************************ * Modification Log ************************************************************************ * Date Ver. Programmer Descriptions * -------- ---- ------------ ------------------------------------------- * 20210401 Original Create ************************************************************************ REPORT ZCORTEST01 NO STANDARD PAGE HEADING MESSAGE-ID 00 LINE-SIZE 177 LINE-COUNT 200. *********************************************************************** * Tables Definitions ************************************************************************ *TABLES:. TABLES:bkpf,bseg,lfa1. DATA: BEGIN OF gt_bseg OCCURS 0, gjahr LIKE bseg-gjahr,"年度 monat LIKE bkpf-monat,"期间 bukrs LIKE bseg-bukrs,"公司 belnr LIKE bseg-belnr,"文件号码 lifnr LIKE bseg-lifnr,"供应商 dmbtr LIKE bseg-dmbtr,"金额 sgtxt LIKE bseg-sgtxt,"内文 hkont LIKE bseg-hkont,"总账科目 budat LIKE bkpf-budat,"过账日期 blart LIKE bkpf-blart,"文件类型 bktxt LIKE bkpf-bktxt,"表头内文 name1 LIKE lfa1-name1,"供应商名称 sortl LIKE lfa1-sortl,"供应商简称 bankl LIKE but0bk-bankl,"银行码 bankn LIKE but0bk-bankn,"银行账号 bkref LIKE but0bk-bkref,""银行账号2 bankn2(30) ,"最终银行账号 banka LIKE bnka-banka,"银行名称 remarks(50),"备注 agricultural_bake(2),"是否农行账号 END OF gt_bseg. *--->供应商相关信息 DATA:BEGIN OF gt_lfa1 OCCURS 0, lifnr LIKE lfa1-lifnr,"供应商 name1 LIKE lfa1-name1,"供应商名称 sortl LIKE lfa1-sortl,"供应商简称 bankl LIKE but0bk-bankl,"银行码 bankn LIKE but0bk-bankn,"银行账号 bkref LIKE but0bk-bkref,""银行账号2 banka LIKE bnka-banka,"银行名称 END OF gt_lfa1. *--> ALV TYPE-POOLS: slis, icon, truxs. DATA: wa_layout TYPE slis_layout_alv, wa_setting TYPE lvc_s_glay, gt_fieldcat TYPE slis_t_fieldcat_alv, wa_fieldcat TYPE slis_fieldcat_alv. *--> 路径变量 DATA: l_fpath TYPE string, " file path l_len type i. " file path length *-->ole 变量 *INCLUDE: zric_ole2_utils. DATA:go_application TYPE ole2_object, go_workbook TYPE ole2_object, go_workbooks TYPE ole2_object, go_worksheet TYPE ole2_object. DATA:gs_cell1 TYPE ole2_object, gs_cell2 TYPE ole2_object, gs_cells TYPE ole2_object, gs_excel TYPE ole2_object. DATA: gv_lines TYPE i. "Lines printed by the moment * Data to be printed. * You must to concatenate the fields of the line you want to print * separated by cl_abap_char_utilities=>horizontal_tab. * Use the subrutine add_line2print for fill the tabla. TYPES: ty_data(1500) TYPE c. DATA: gt_data TYPE TABLE OF ty_data, gs_data LIKE LINE OF gt_data. * Data to be printed. * Fill the table with the text you want to print in a line. * Use the subrutine add_line2print_from_table to pass the * table. TYPES: BEGIN OF ty_line, value TYPE char255, END OF ty_line. DATA: gt_lines TYPE TABLE OF ty_line, gs_lines LIKE LINE OF gt_lines. * Fields to be printed * Use the subrutine print_data_fieldcat. TYPES: BEGIN OF ty_fieldcat, field LIKE dd03d-fieldname, "Field name in your internal table text LIKE dd03p-ddtext, "Description of the column width TYPE i, "Width of the column END OF ty_fieldcat. DATA: BEGIN OF gt_spfli OCCURS 0, serial_num TYPE n LENGTH 4,"编号 card(30), " 卡号 Receive_name(100), "收款方姓名 Agricultural_bake(2),"是否农行账号 Branch_name(150),"开户支行名称 Amount TYPE p LENGTH 16 DECIMALS 2,"金额 Remarks(50),"用途（附言） END OF gt_spfli. * Use the subrutine set_soft_colour.使用子例程set_soft colour。 CONSTANTS: c_theme_col_white TYPE i VALUE 1, c_theme_col_light_blue TYPE i VALUE 5. * Align: 对齐 CONSTANTS: c_center TYPE i VALUE -4108, c_left TYPE i VALUE -4131, c_right TYPE i VALUE -4152. DATA: lv_selected_folder TYPE string, lv_complete_path TYPE char256, lv_title TYPE string. ************************************************************************ * Data Definitions ************************************************************************ ************************************************************************ * Includes Module ************************************************************************ ************************************************************************ * Selection Screen ************************************************************************ SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-b01. PARAMETERS:p_bukrs LIKE bkpf-bukrs DEFAULT '1000',"公司 p_blart LIKE bkpf-blart DEFAULT 'KQ'."文件类型 SELECT-OPTIONS:s_gjahr FOR bkpf-gjahr,"年度 s_budat FOR bkpf-budat,"过账日期 s_lifnr FOR lfa1-lifnr,"供应商 s_belnr FOR bkpf-belnr,"文件号码 s_hkont FOR bseg-hkont,"总账科目 s_hkon2 FOR bseg-hkont."付款号码 " 檔案路徑 PARAMETERS: p_fpath LIKE rlgrap-filename DEFAULT 'Z:\' OBLIGATORY. SELECTION-SCREEN END OF BLOCK b1. ************************************************************************ * Initialization ************************************************************************ INITIALIZATION. ************************************************************************ * At Selection Screen ************************************************************************ AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_fpath. PERFORM get_fpath. ************************************************************************ * At Selection Screen Output ************************************************************************ AT SELECTION-SCREEN OUTPUT. ************************************************************************ * Report Format ************************************************************************ TOP-OF-PAGE. END-OF-PAGE. ************************************************************************ * Main Process ************************************************************************ START-OF-SELECTION. PERFORM get_data. PERFORM display_data. END-OF-SELECTION. *&---------------------------------------------------------------------* *& Form GET_DATA *&---------------------------------------------------------------------* *& text *&---------------------------------------------------------------------* *& --> p1 text *& <-- p2 text *&---------------------------------------------------------------------* FORM get_data . DATA:ls_o_datum(15),"银行码有效时间 l_index TYPE sy-tabix. DATA:g_p TYPE p DECIMALS 3 , g_error TYPE REF TO cx_root, g_message TYPE string. DATA:lt_bseg LIKE gt_bseg OCCURS 0 WITH HEADER LINE, ls_bseg LIKE gt_bseg. *--->付款文件明细 SELECT bseg~gjahr bkpf~monat bseg~bukrs bseg~belnr bseg~lifnr bseg~dmbtr bseg~hkont bseg~sgtxt bkpf~budat bkpf~blart bkpf~bktxt INTO CORRESPONDING FIELDS OF TABLE lt_bseg FROM bseg INNER JOIN bkpf ON bkpf~belnr = bseg~belnr AND bkpf~bukrs = bseg~bukrs AND bkpf~gjahr = bseg~gjahr WHERE bkpf~budat IN s_budat AND bseg~gjahr IN s_gjahr AND bseg~belnr IN s_belnr AND bseg~hkont IN s_hkont AND bseg~bukrs EQ p_bukrs AND bkpf~blart EQ p_blart. SORT lt_bseg BY hkont lifnr. gt_bseg[] = lt_bseg[]. DELETE gt_bseg WHERE hkont NOT IN s_hkon2. DELETE lt_bseg WHERE lifnr EQ ''. CONCATENATE sy-datum '000000' INTO ls_o_datum. *--->供应商主档明细 SELECT lfa1~lifnr lfa1~sortl lfa1~name1 but0bk~bankl but0bk~bankn but0bk~bkref bnka~banka INTO CORRESPONDING FIELDS OF TABLE gt_lfa1 FROM lfa1 INNER JOIN but0bk ON but0bk~partner = lfa1~lifnr INNER JOIN bnka ON bnka~bankl = but0bk~bankl WHERE lfa1~lifnr IN s_lifnr AND but0bk~bk_valid_from <= ls_o_datum AND but0bk~bk_valid_to >= ls_o_datum. SORT gt_lfa1 BY lifnr. "总账科目(1103201001 批量付款) LOOP AT gt_bseg. l_index = sy-tabix. CLEAR:g_message. READ TABLE lt_bseg WITH KEY belnr = gt_bseg-belnr bukrs = gt_bseg-bukrs gjahr = gt_bseg-gjahr monat = gt_bseg-monat. IF sy-subrc = 0. READ TABLE gt_lfa1 WITH KEY lifnr = lt_bseg-lifnr. IF sy-subrc = 0. gt_bseg-name1 = gt_lfa1-name1. gt_bseg-sortl = gt_lfa1-sortl. gt_bseg-bankl = gt_lfa1-bankl. gt_bseg-bankn = gt_lfa1-bankn. gt_bseg-bkref = gt_lfa1-bkref. gt_bseg-banka = gt_lfa1-banka. gt_bseg-lifnr = gt_lfa1-lifnr. * "检查是否为非数字 * TRY . * g_p = gt_bseg-bkref . * CATCH cx_sy_conversion_no_number INTO g_error. * g_mesSage = g_error->get_longtext( ). ** MESSAGE g_message TYPE 'S' DISPLAY LIKE 'E' * ENDTRY. * IF g_message IS INITIAL. IF gt_bseg-bkref IS NOT INITIAL. gt_bseg-bankn2 = gt_bseg-bankn && gt_bseg-bkref. ELSE. gt_bseg-bankn2 = gt_bseg-bankn. ENDIF. ENDIF. ENDIF. *---->是否农业银行 IF gt_bseg-banka CS '农行' OR gt_bseg-banka CS '农业银行' OR gt_bseg-banka CS '中国农业银行' OR gt_bseg-banka CS '農行' OR gt_bseg-banka CS '農業銀行' OR gt_bseg-banka CS '中國農業銀行'. gt_bseg-agricultural_bake = ''. ELSE. gt_bseg-agricultural_bake = '否'. ENDIF. *----> 备注 CONCATENATE '货款' gt_bseg-belnr INTO gt_bseg-remarks. MODIFY gt_bseg INDEX l_index. ENDLOOP. ENDFORM. *----> 自定义按钮 FORM ALV_PF_STATUS USING RT_EXTAB TYPE SLIS_T_EXTAB. SET PF-STATUS 'STATUS_PRINT' . ENDFORM. *&---------------------------------------------------------------------* *& Form ALV_SHOW *&---------------------------------------------------------------------* *& text *&---------------------------------------------------------------------* *& --> p1 text *& <-- p2 text *&---------------------------------------------------------------------* FORM display_data . PERFORM get_col. *--> ALV Layout Setting wa_layout-colwidth_optimize = 'X'. " 最佳欄寬 wa_layout-zebra = 'X'. CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY' EXPORTING i_callback_program = sy-repid is_layout = wa_layout it_fieldcat = gt_fieldcat i_callback_pf_status_set = 'ALV_PF_STATUS' i_callback_user_command = 'ALV_USER_COMMAND' i_save = 'A' TABLES t_outtab = gt_bseg. ENDFORM. *&---------------------------------------------------------------------* *& Form GET_COL *&---------------------------------------------------------------------* *& text *&---------------------------------------------------------------------* *& --> p1 text *& <-- p2 text *&---------------------------------------------------------------------* FORM get_col. DATA: l_repid TYPE sy-repid, l_inclname TYPE trdir-name, l_idx LIKE sy-tabix. CLEAR: gt_fieldcat[], wa_fieldcat. l_repid = l_inclname = sy-cprog. CALL FUNCTION 'REUSE_ALV_FIELDCATALOG_MERGE' EXPORTING i_program_name = l_repid i_internal_tabname = 'GT_BSEG' i_inclname = l_inclname CHANGING ct_fieldcat = gt_fieldcat EXCEPTIONS inconsistent_interface = 1 program_error = 2 OTHERS = 3. LOOP AT gt_fieldcat INTO wa_fieldcat. l_idx = sy-tabix. CASE wa_fieldcat-fieldname. * WHEN 'EKGRP'. * WA_FIELDCAT-NO_OUT = 'X'. " NO_OUT 不显示 WHEN 'AGRICULTURAL_BAKE'. wa_fieldcat-seltext_s = wa_fieldcat-seltext_m = wa_fieldcat-seltext_l = '是否农业银行'. wa_fieldcat-ddictxt = 'S'. WHEN 'REMARKS'. wa_fieldcat-seltext_s = wa_fieldcat-seltext_m = wa_fieldcat-seltext_l = '用途(附言)'. wa_fieldcat-ddictxt = 'S'. WHEN 'BANKN2'. wa_fieldcat-seltext_s = wa_fieldcat-seltext_m = wa_fieldcat-seltext_l = '最终银行账号'. wa_fieldcat-ddictxt = 'S'. ENDCASE. MODIFY gt_fieldcat FROM wa_fieldcat INDEX l_idx. ENDLOOP. ENDFORM. FORM alv_user_command USING in_ucomm LIKE sy-ucomm in_selfield type slis_selfield. DATA: lo_columns1 TYPE ole2_object, lo_columns TYPE ole2_object, l_amount TYPE p DECIMALS 2, l_index TYPE sy-tabix, l_doid(3), l_serial_num TYPE p. CASE in_ucomm. WHEN '&DOWN_EXCE'."下载网银附件 CLEAR:l_doid. DO. l_doid = l_doid + 1. CLEAR:gt_lines[],l_amount,gt_spfli[],gt_spfli, gs_data,gt_data[],gt_data,l_serial_num. * Create the document; PERFORM create_document. ** Fill a header with some data of the passenger: 数据填写标题 gs_data = 'OLE 测试'. APPEND gs_data TO gt_data. * First a Header with the column's names 首先是带有列名称的标题 gs_lines-value = '编号'. APPEND gs_lines TO gt_lines. gs_lines-value = '收款方账号'. APPEND gs_lines TO gt_lines. gs_lines-value = '收款方户名'. APPEND gs_lines TO gt_lines. gs_lines-value = '账户类型'. APPEND gs_lines TO gt_lines. gs_lines-value = '开户行支行名称'. APPEND gs_lines TO gt_lines. gs_lines-value = '金额'. APPEND gs_lines TO gt_lines. gs_lines-value = '备注'. APPEND gs_lines TO gt_lines. * Add the header to data to be printed 标题添加到要打印的数据 PERFORM add_line2print_from_table. * Print the rest of the data: 打印其余数据 LOOP AT gt_bseg WHERE lifnr IS NOT INITIAL. l_index = sy-tabix. l_serial_num = l_serial_num + 1. *--->编号 gt_spfli-serial_num = l_serial_num. *--->账号 gt_spfli-card = gt_bseg-bankn2. *--->收款方户名 gt_spfli-receive_name = gt_bseg-name1. *--->是否农行账户 gt_spfli-agricultural_bake = gt_bseg-agricultural_bake. gt_spfli-branch_name = gt_bseg-banka. *--->金额 gt_spfli-amount = gt_bseg-dmbtr. *--->备注 gt_spfli-remarks = gt_bseg-remarks. PERFORM add_line2print USING gt_spfli 0. ENDLOOP. CALL METHOD OF go_application 'COLUMNS' = lo_columns1 EXPORTING #1 = 2. "the column number "设置列宽 * SET PROPERTY OF lo_columns1 'ColumnWidth' = 40. SET PROPERTY OF lo_columns1 'NumberFormat' = '@'. * Copy-paste the data from cell A1 从单元格复制粘贴数据 PERFORM paste_clipboard USING 1 1. " 将列设置文本格式 CALL METHOD OF go_application 'COLUMNS' = lo_columns1 EXPORTING #1 = 2. "the column number SET PROPERTY OF lo_columns1 'NumberFormat' = '@'. CALL METHOD OF go_application 'COLUMNS' = lo_columns1 EXPORTING #1 = 8. "the column number SET PROPERTY OF lo_columns1 'NumberFormat' = '@'. *Selecting cell area to be merged 合并单元格 * 合并开始的行 列 结束行 列 PERFORM selecting_merged USING 1 1 1 9. * Bold the header: 加粗标题 PERFORM change_format USING 1 1 2 9 "Range of cells 0 space "Font Colour 0 space "Background Colour 12 'X' "Size 1 'X'. "Bold * Change the colour of the item's header. 更改项目标题的颜色 PERFORM set_soft_colour USING 2 1 2 9 "Range of cells c_theme_col_white 'X' "Font Colour 0 space "Font TintAndShade c_theme_col_light_blue 'X' "Background Colour '0.49' 'X'. "Bkg Col. TintAndShade * Align centered the two first columns of the item table 居中 " 开始 行 列 结束 行 列 PERFORM align_cells USING 1 1 9999 9 c_center. * Add borders 添加边框 "开始 行 列 结束 行 列 PERFORM add_border USING 2 2 2 9. * Adjust the width of the cells to content 调整单元格的宽度以适应 CALL METHOD OF go_application 'Columns' = lo_columns. CALL METHOD OF lo_columns 'Autofit'. * Set the width to the second column 设置第二列的宽度为 10 * PERFORM column_width USING 2 30. ** Change the name of the worksheet:更改工作表的名称 SET PROPERTY OF go_worksheet 'Name' = '中国农业银行批量转账'. * File name 保存路径及文件名 CONCATENATE p_fpath sy-datum '_' l_doid '批量付款-网银.xls' INTO lv_complete_path. * Save the document 保存文件 CALL METHOD OF go_workbook 'SaveAs' EXPORTING #1 = lv_complete_path. IF sy-subrc EQ 0. MESSAGE 'File downloaded successfully' TYPE 'S'. ELSE. MESSAGE 'Error downloading the file' TYPE 'E'. ENDIF. * Close the document and free memory 关闭并释放 PERFORM close_document. ENDDO. ENDCASE. ENDFORM. *&---------------------------------------------------------------------* *& Form GET_FPATH *&---------------------------------------------------------------------* *& text *&---------------------------------------------------------------------* *& --> p1 text *& <-- p2 text *&---------------------------------------------------------------------* FORM get_fpath . CALL METHOD cl_gui_frontend_services=>directory_browse * EXPORTING * window_title = 'FIND OUTPUT PATH' * initial_folder = l_finit CHANGING selected_folder = l_fpath EXCEPTIONS cntl_error = 1 error_no_gui = 2 not_supported_by_gui = 3 OTHERS = 4. * -最後一個字元不為'\'，補上'\'- IF l_fpath IS NOT INITIAL. l_len = STRLEN( l_fpath ) - 1. IF L_FPATH+L_LEN(1) NE '\'. p_fpath = l_fpath && |\\|. ELSE. p_fpath = l_fpath. ENDIF. ENDIF. ENDFORM. * Instanciate the application, workbook and the first worksheet. *实例化应用程序，工作簿和第一个工作表。 *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM create_document. CREATE OBJECT go_application 'Excel.Application'. SET PROPERTY OF go_application 'Visible' = 0. CALL METHOD OF go_application 'Workbooks' = go_workbooks. CALL METHOD OF go_workbooks 'Add' = go_workbook. GET PROPERTY OF go_application 'ACTIVESHEET' = go_worksheet. ENDFORM. " create_document *&---------------------------------------------------------------------* *& Form CLOSE_DOCUMENT *&---------------------------------------------------------------------* * Close the document and free memory objects. *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM close_document. CALL METHOD OF go_application 'QUIT'. FREE OBJECT go_worksheet. FREE OBJECT go_workbook. FREE OBJECT go_workbooks. FREE OBJECT go_application. ENDFORM. " close_document *&---------------------------------------------------------------------* *& Form add_line2print_from_table *&---------------------------------------------------------------------* *& Add line to be printed in subrutine PASTE_CLIPBOARD from a table. *&---------------------------------------------------------------------* FORM add_line2print_from_table. CLEAR gs_data. LOOP AT gt_lines INTO gs_lines. CONCATENATE gs_data gs_lines-value INTO gs_data SEPARATED BY cl_abap_char_utilities=>horizontal_tab. ENDLOOP. * Quit the first horizontal_tab: SHIFT gs_data BY 1 PLACES LEFT. APPEND gs_data TO gt_data. CLEAR gs_data. ENDFORM. "add_line2print_from_table *&---------------------------------------------------------------------* *& Form PASTE_CLIPBOARD *&---------------------------------------------------------------------* *& Paste Clipboard from the cell passed by parameter *&---------------------------------------------------------------------* * --> p_row * --> p_col *&---------------------------------------------------------------------* FORM paste_clipboard USING p_row TYPE i p_col TYPE i. DATA: lo_cell TYPE ole2_object. * Copy to clipboard into ABAP CALL FUNCTION 'CONTROL_FLUSH' EXCEPTIONS OTHERS = 3. CALL FUNCTION 'CLPB_EXPORT' TABLES data_tab = gt_data EXCEPTIONS clpb_error = 1 OTHERS = 2. * Select the cell A1 CALL METHOD OF go_worksheet 'Cells' = lo_cell EXPORTING #1 = p_row #2 = p_col. * Paste clipboard from cell A1 CALL METHOD OF lo_cell 'SELECT'. CALL METHOD OF go_worksheet 'PASTE'. ENDFORM. " PASTE_CLIPBOARD *&---------------------------------------------------------------------* *& Form Column_width *&---------------------------------------------------------------------* * Adjust column width *----------------------------------------------------------------------* * --> p_column Column numbe * --> p_width Width *----------------------------------------------------------------------* FORM column_width USING p_column TYPE i p_width TYPE i. DATA: lo_cellstart TYPE ole2_object, lo_cellend TYPE ole2_object, lo_selection TYPE ole2_object, lo_column TYPE ole2_object. * Select the Column CALL METHOD OF go_worksheet 'Columns' = lo_column EXPORTING #1 = p_column. CALL METHOD OF lo_column 'select'. CALL METHOD OF go_application 'selection' = lo_selection. SET PROPERTY OF lo_column 'ColumnWidth' = p_width. ENDFORM. "Column_width *&---------------------------------------------------------------------* *& Form SELECTING_MERGED *&---------------------------------------------------------------------* *& text Selecting cell area to be merged 合并单元格 *&---------------------------------------------------------------------* *& --> P_1 *& --> P_1 *& --> P_1 *& --> P_9 *&---------------------------------------------------------------------* FORM selecting_merged USING VALUE(in_brow) VALUE(in_bcloumn) VALUE(in_erow) VALUE(in_ecloumn). CALL METHOD OF go_application 'Cells' = gs_cell1 EXPORTING #1 = in_erow"合并结束行 #2 = in_bcloumn."合并起始列 CALL METHOD OF go_application 'Cells' = gs_cell2 EXPORTING #1 = in_brow "合并起始行 #2 = in_ecloumn."合并结束列 CALL METHOD OF go_application 'Range' = gs_cells EXPORTING #1 = gs_cell1 #2 = gs_cell2. CALL METHOD OF gs_cells 'Select'. *--Merging 开始合并中 CALL METHOD OF gs_cells 'Merge'. ENDFORM. *&---------------------------------------------------------------------* *& Form add_line2print *&---------------------------------------------------------------------* *& Add line to be printed in subrutine PASTE_CLIPBOARD *&---------------------------------------------------------------------* * --> p_data Data to print * --> p_num_cols Number of fields to be printed, if 0 all the field * will be printed *&---------------------------------------------------------------------* FORM add_line2print USING p_data TYPE any p_num_cols TYPE i. FIELD-SYMBOLS: <field> TYPE ANY. DATA: lv_cont TYPE i, lv_char TYPE char128. CONSTANTS: gc_tab TYPE c VALUE cl_bcs_convert=>gc_tab. DATA: lo_abap_typedescr TYPE REF TO cl_abap_typedescr. CLEAR gs_data. DO. ADD 1 TO lv_cont. ASSIGN COMPONENT lv_cont OF STRUCTURE p_data TO <field>. IF sy-subrc NE 0. EXIT. ENDIF. * Convert data depend on the kind type. CALL METHOD cl_abap_typedescr=>describe_by_data EXPORTING p_data = <field> RECEIVING p_descr_ref = lo_abap_typedescr. CASE lo_abap_typedescr->type_kind. * Char WHEN lo_abap_typedescr->typekind_char. CONCATENATE gs_data <field> INTO gs_data SEPARATED BY gc_tab."cl_abap_char_utilities=>horizontal_tab. * Date WHEN lo_abap_typedescr->typekind_date. WRITE <field> TO lv_char DD/MM/YYYY. CONCATENATE gs_data lv_char INTO gs_data SEPARATED BY cl_abap_char_utilities=>horizontal_tab. * Time WHEN lo_abap_typedescr->typekind_time. CONCATENATE <field>(2) <field>+2(2) <field>+4(2) INTO lv_char SEPARATED BY ':'. CONCATENATE gs_data lv_char INTO gs_data SEPARATED BY cl_abap_char_utilities=>horizontal_tab. * Others WHEN OTHERS. WRITE <field> TO lv_char. CONCATENATE gs_data lv_char INTO gs_data SEPARATED BY cl_abap_char_utilities=>horizontal_tab. ENDCASE. * Exit the loop? IF lv_cont EQ p_num_cols. EXIT. ENDIF. ENDDO. * Quit the first horizontal_tab: SHIFT gs_data BY 1 PLACES LEFT. APPEND gs_data TO gt_data. CLEAR gs_data. ENDFORM. "add_line2print *&---------------------------------------------------------------------* *& Form change_format *&---------------------------------------------------------------------* *& Change cell format *&---------------------------------------------------------------------* * --> p_rowini p_colini Initial Range Cell * --> p_rowend p_colend End Range Cell * --> p_colour Colour of the font * --> p_colourx Set to X if want to change the Colour * --> p_bkg_col Background colour of the cell * --> p_bkg_colx Set to X if want to change the Background colour * --> p_size Size of the font * --> p_sizex Set to X if want to change the Size * --> p_bold Bold * --> p_boldx Set to X if want to change to Bold *&---------------------------------------------------------------------* FORM change_format USING p_rowini p_colini p_rowend p_colend p_colour TYPE i p_colourx TYPE char1 p_bkg_col TYPE i p_bkg_colx TYPE char1 p_size TYPE i p_sizex TYPE char1 p_bold TYPE i p_boldx TYPE char1. DATA: lo_cellstart TYPE ole2_object, lo_cellend TYPE ole2_object, lo_selection TYPE ole2_object, lo_range TYPE ole2_object, lo_font TYPE ole2_object, lo_interior TYPE ole2_object. * Select the Range of Cells: CALL METHOD OF go_worksheet 'Cells' = lo_cellstart EXPORTING #1 = p_rowini #2 = p_colini. CALL METHOD OF go_worksheet 'Cells' = lo_cellend EXPORTING #1 = p_rowend #2 = p_colend. CALL METHOD OF go_worksheet 'Range' = lo_range EXPORTING #1 = lo_cellstart #2 = lo_cellend. * Format: CALL METHOD OF lo_range 'FONT' = lo_font. * Colour: IF p_colourx EQ 'X'. SET PROPERTY OF lo_font 'ColorIndex' = p_colour. ENDIF. * Background colour; IF p_bkg_colx EQ 'X'. CALL METHOD OF lo_range 'Interior' = lo_interior. SET PROPERTY OF lo_interior 'ColorIndex' = p_bkg_col. ENDIF. * Size IF p_sizex EQ 'X'. SET PROPERTY OF lo_font 'SIZE' = p_size. ENDIF. * Bold IF p_boldx EQ 'X'. SET PROPERTY OF lo_font 'BOLD' = p_bold. ENDIF. ENDFORM. "change_format *&---------------------------------------------------------------------* *& Form set_soft_colour *&---------------------------------------------------------------------* *& Set a theme colour. *& For colour and bkgcolour use the theme colour constants. *& Shade and bkg_shade values : from -1 to 1. *&---------------------------------------------------------------------* * --> p_rowini p_colini Initial Range Cell * --> p_rowend p_colend End Range Cell * --> p_colour Colour of the font * --> p_colourx Set to X if want to change the Colour * --> p_shade Tint and Shade * --> p_shadex Set to X if want to change the shade * --> p_bkg_col Background colour of the cell * --> p_bkg_colx Set to X if want to change the Background colour * --> p_bkg_shade Tint and Shade * --> p_bkg_shadex Set to X if want to change the shade *&---------------------------------------------------------------------* FORM set_soft_colour USING p_rowini p_colini p_rowend p_colend p_colour TYPE i p_colourx TYPE char1 p_shade TYPE float p_shadex TYPE char1 p_bkg_col TYPE i p_bkg_colx TYPE char1 p_bkg_shade TYPE float p_bkg_shadex TYPE char1. DATA: lo_cellstart TYPE ole2_object, lo_cellend TYPE ole2_object, lo_selection TYPE ole2_object, lo_range TYPE ole2_object, lo_font TYPE ole2_object, lo_interior TYPE ole2_object. * Select the Range of Cells: CALL METHOD OF go_worksheet 'Cells' = lo_cellstart EXPORTING #1 = p_rowini #2 = p_colini. CALL METHOD OF go_worksheet 'Cells' = lo_cellend EXPORTING #1 = p_rowend #2 = p_colend. CALL METHOD OF go_worksheet 'Range' = lo_range EXPORTING #1 = lo_cellstart #2 = lo_cellend. * Format: CALL METHOD OF lo_range 'FONT' = lo_font. * Colour: IF p_colourx EQ 'X'. SET PROPERTY OF lo_font 'ThemeColor' = p_colour. IF p_shadex EQ 'X'. SET PROPERTY OF lo_font 'TintAndShade' = p_shade. ENDIF. ENDIF. * BackGround Colour: IF p_bkg_colx EQ 'X'. CALL METHOD OF lo_range 'Interior' = lo_interior. SET PROPERTY OF lo_interior 'ThemeColor' = p_bkg_col. IF p_bkg_shadex EQ 'X'. SET PROPERTY OF lo_interior 'TintAndShade' = p_bkg_shade. ENDIF. ENDIF. ENDFORM. "set_soft_colour *&---------------------------------------------------------------------* *& Form align Cells *&---------------------------------------------------------------------* * Align Cells *----------------------------------------------------------------------* * --> p_rowini p_colini Initial Range Cell * --> p_rowend p_colend End Range Cell * --> p_align Align: c_center, c_left, c_right. *----------------------------------------------------------------------* FORM align_cells USING p_rowini p_colini p_rowend p_colend p_align. DATA: lo_cellstart TYPE ole2_object, lo_cellend TYPE ole2_object, lo_selection TYPE ole2_object, lo_range TYPE ole2_object. * Select the Range of Cells: CALL METHOD OF go_worksheet 'Cells' = lo_cellstart EXPORTING #1 = p_rowini #2 = p_colini. CALL METHOD OF go_worksheet 'Cells' = lo_cellend EXPORTING #1 = p_rowend #2 = p_colend. CALL METHOD OF go_worksheet 'Range' = lo_range EXPORTING #1 = lo_cellstart #2 = lo_cellend. CALL METHOD OF lo_range 'select'. SET PROPERTY OF lo_range 'HorizontalAlignment' = p_align. ENDFORM. "align_cells *&---------------------------------------------------------------------* *& Form Add Border *&---------------------------------------------------------------------* * Add Border *----------------------------------------------------------------------* * --> p_rowini p_colini Initial Range Cell * --> p_rowend p_colend End Range Cell *----------------------------------------------------------------------* FORM add_border USING p_rowini p_colini p_rowend p_colend. DATA: lo_cellstart TYPE ole2_object, lo_cellend TYPE ole2_object, lo_selection TYPE ole2_object, lo_range TYPE ole2_object, lo_borders TYPE ole2_object. * Select the Range of Cells: CALL METHOD OF go_worksheet 'Cells' = lo_cellstart EXPORTING #1 = p_rowini #2 = p_colini. CALL METHOD OF go_worksheet 'Cells' = lo_cellend EXPORTING #1 = p_rowend #2 = p_colend. CALL METHOD OF go_worksheet 'Range' = lo_range EXPORTING #1 = lo_cellstart #2 = lo_cellend. CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '7'. "xlEdgeLeft SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '8'. "xlEdgeTop SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '9'. "xlEdgeBottom SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '10'. "xlEdgeRight SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '11'. "xlInsideVertical SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous CALL METHOD OF lo_range 'Borders' = lo_borders EXPORTING #1 = '12'. "xlInsideHorizontal SET PROPERTY OF lo_borders 'LineStyle' = '1'. "xlContinuous ENDFORM. "Add Border
参考范例：https://blog.csdn.net/weixin_30323631/article/details/98966315
http://www.voidcn.com/article/p-tsyhyxty-nk.html
