*&---------------------------------------------------------------------* *& Report ZFIR002 *&---------------------------------------------------------------------* *& *&---------------------------------------------------------------------* REPORT zfir002. *----------------------------------------------------------------------* * TABLES *----------------------------------------------------------------------* TABLES: bseg,bkpf,faglflext,glt0,setheadert,zqtfi002. *----------------------------------------------------------------------* * TYPE-POOLS *----------------------------------------------------------------------* TYPE-POOLS: slis. TYPE-POOLS: esp1. " 增加弹出消息框 TYPE-POOLS ole2 . DATA: it_i_message_tab TYPE esp1_message_tab_type WITH HEADER LINE. TYPES: BEGIN OF typ_glt0,"会计凭证总账内表 ryear TYPE glt0-ryear, drcrk TYPE glt0-drcrk, "借方/贷方标识 rpmax TYPE glt0-rpmax, "期间 hslvt TYPE glt0-hslvt, hsl01 TYPE glt0-hsl01, hsl02 TYPE glt0-hsl02, hsl03 TYPE glt0-hsl02, hsl04 TYPE glt0-hsl02, hsl05 TYPE glt0-hsl02, hsl06 TYPE glt0-hsl02, hsl07 TYPE glt0-hsl02, hsl08 TYPE glt0-hsl02, hsl09 TYPE glt0-hsl02, hsl10 TYPE glt0-hsl02, hsl11 TYPE glt0-hsl02, hsl12 TYPE glt0-hsl02, hsl13 TYPE glt0-hsl13, hsl14 TYPE glt0-hsl14, hsl15 TYPE glt0-hsl15, hsl16 TYPE glt0-hsl16, racct TYPE glt0-racct, "科目 END OF typ_glt0. DATA: gt_fag TYPE TABLE OF faglflext WITH HEADER LINE . TYPES: BEGIN OF typ_tab1," box_name TYPE c, "选择框 t_edit TYPE lvc_t_styl, zxiangm(30),"项目 zitem TYPE i, "行次 zbysjs TYPE glt0-hsl01, "本月实际数 zbnljs TYPE glt0-hsl01, "本年累计数 zsnljs TYPE glt0-hsl01, "上年累计数 zzflag TYPE c, zbnsyljs TYPE glt0-hsl01, "本年上月累计数 END OF typ_tab1. DATA: t_out TYPE TABLE OF typ_tab1 WITH HEADER LINE . DATA: lt_otab TYPE TABLE OF typ_tab1 WITH HEADER LINE . DATA: lt_tabh TYPE TABLE OF typ_tab1 WITH HEADER LINE . RANGES: rg_budat FOR bkpf-budat. DATA: g_time TYPE bkpf-monat. DATA: p_roald LIKE rlgrap-filename. *************excel define *定义OLE变量 "INCLUDE zqficor002_ole2incl. *INCLUDE OLE2INCL. DATA: excel TYPE ole2_object, sheet TYPE ole2_object, cell TYPE ole2_object, workbook TYPE ole2_object. DATA: BEGIN OF gt_type OCCURS 0, zitem TYPE zel_zitem, c100(50) TYPE c, "第三列 d100(50) TYPE c, "第四列 e100(50) TYPE c, "第五列 h100(50) TYPE c, "本年上月累计数 END OF gt_type. * 会计凭证信息 DATA:BEGIN OF it_acdoca OCCURS 0. DATA: rbukrs LIKE acdoca-rbukrs, "公司代码 belnr LIKE acdoca-belnr, "会计凭证 gjahr LIKE acdoca-ryear, "年度 docln LIKE acdoca-docln, "行项目 drcrk LIKE acdoca-drcrk, "借贷标识 budat LIKE acdoca-budat, "过账日期 hsl LIKE acdoca-hsl, "金额 racct LIKE acdoca-racct, "科目号 rstgr LIKE bseg-rstgr, "原因代码 buzei LIKE bseg-buzei, "BSEG中的行项目 txt20 LIKE skat-txt20, "科目描述 sn_gjahr TYPE bseg-gjahr, "上年年度 zbz(1). INCLUDE STRUCTURE zqsficor002. "INCLUDE 结构 DATA:END OF it_acdoca. DATA:it_acdoca_mx LIKE TABLE OF it_acdoca WITH HEADER LINE. DATA:wa_type LIKE LINE OF gt_type. DATA: gt_tpkm TYPE TABLE OF zqtfi002. "利润表科目及格式维护 DATA: wa_tpkm TYPE zqtfi002. DATA: g_text1(40), g_text2(50), g_text3(40). DATA g_year_sy TYPE bseg-gjahr. DATA g_month_sy TYPE bkpf-monat. DATA: it_event TYPE slis_t_event, "不能有表头,否则会RUNTIME ERROR it_listheader TYPE slis_t_listheader. DATA str_butxt TYPE string. DATA:gt_alv1 TYPE STANDARD TABLE OF zqsficor002, wa_alv1 TYPE zqsficor002. ** 加入 SELECT 的條件 DATA: gt_vimsellist LIKE vimsellist OCCURS 1 WITH HEADER LINE. INCLUDE z_rpt_template. "报表模板 *----------------------------------------------------------------------* * SELECTION-SCREEN *----------------------------------------------------------------------* SELECTION-SCREEN FUNCTION KEY 1. SELECTION-SCREEN: BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001. PARAMETERS:p_bukrs TYPE bseg-bukrs OBLIGATORY, p_ryear TYPE bseg-gjahr OBLIGATORY, p_rpmax TYPE bkpf-monat OBLIGATORY. PARAMETERS:s1 AS CHECKBOX DEFAULT 'X', s2 AS CHECKBOX. PARAMETERS:p1 RADIOBUTTON GROUP g1, p2 RADIOBUTTON GROUP g1. SELECTION-SCREEN: END OF BLOCK b1. *----------------------------------------------------------------------* * INITIALIZATION. *----------------------------------------------------------------------* INITIALIZATION. sscrfields-functxt_01 = '利润表科目维护'. p_ryear = sy-datum(4). p_rpmax = sy-datum+4(2). PERFORM frm_initial. *----------------------------------------------------------------------* * AT SELECTION-SCREEN *----------------------------------------------------------------------* AT SELECTION-SCREEN. CASE sscrfields-ucomm . WHEN 'FC01'. "管理报表格式维护'. * REFRESH GT_VIMSELLIST. * GT_VIMSELLIST-VIEWFIELD = 'BUKRS'. * GT_VIMSELLIST-OPERATOR = 'EQ'. * GT_VIMSELLIST-VALUE = P_BUKRS. * GT_VIMSELLIST-AND_OR = 'AND'. * APPEND GT_VIMSELLIST. CALL FUNCTION 'VIEW_MAINTENANCE_CALL' EXPORTING action = 'S' view_name = 'ZQTFI002' TABLES dba_sellist = gt_vimsellist EXCEPTIONS client_reference = 1 foreign_lock = 2 invalid_action = 3 no_clientindependent_auth = 4 no_database_function = 5 no_editor_function = 6 no_show_auth = 7 no_tvdir_entry = 8 no_upd_auth = 9 only_show_allowed = 10 system_failure = 11 unknown_field_in_dba_sellist = 12 view_not_found = 13 maintenance_prohibited = 14 OTHERS = 15. IF sy-subrc <> 0. MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. ENDIF. ENDCASE. ** 权限检查 PERFORM frm_sub_auth_check. *----------------------------------------------------------------------* * INCLUDE *----------------------------------------------------------------------* INCLUDE zqficor002_f01. * INCLUDE ZQFIR002_F01. * INCLUDE ZFIR_LDV_PROFIT_F01. *----------------------------------------------------------------------* * START-OF-SELECTION *----------------------------------------------------------------------* START-OF-SELECTION. ** 取数据 PERFORM frm_sub_read_table. *----------------------------------------------------------------------* * END-OF-SELECTION *----------------------------------------------------------------------* END-OF-SELECTION. ** Excel输出 " PERFORM FRM_SUB_EXCEL_OUTPUT.
*&---------------------------------------------------------------------* *& 包含 ZQFIR002_F01 *&---------------------------------------------------------------------* *&---------------------------------------------------------------------* *& Form SUB_AUTH_CHECK *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM frm_sub_auth_check . AUTHORITY-CHECK OBJECT 'F_BKPF_BUK' ID 'BUKRS' FIELD p_bukrs ID 'ACTVT' FIELD '03'. IF sy-subrc <> 0. * MESSAGE E007 WITH P_BUKRS. EXIT. ENDIF. ENDFORM. " FRM_SUB_AUTH_CHECK *&---------------------------------------------------------------------* *& Form FRM_SUB_READ_TABLE *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM frm_sub_read_table . DATA:last_year TYPE gjahr. RANGES:rr_year FOR bseg-gjahr . g_time = p_rpmax. IF p_rpmax = '01'. g_year_sy = p_ryear - 1. g_month_sy = 16. ELSE. g_year_sy = p_ryear. g_month_sy = p_rpmax - 1. ENDIF. last_year = p_ryear - 1. rr_year-sign = 'I'. rr_year-option = 'BT'. rr_year-low = p_ryear. rr_year-high = p_ryear. APPEND rr_year. *********************根据公司代码、年份查找总帐: 总计表 SELECT racct ryear drcrk"借贷标识 rpmax hslvt hsl01 hsl02 hsl03 hsl04 hsl05 hsl06 hsl07 hsl08 hsl09 hsl10 hsl11 hsl12 hsl13 hsl14 hsl15 hsl16 INTO CORRESPONDING FIELDS OF TABLE gt_fag FROM faglflext WHERE ryear IN rr_year AND rbukrs = p_bukrs AND prctr IN s_prctr. IF sy-subrc <> 0. MESSAGE '无数据！' TYPE 'S' DISPLAY LIKE 'E'. LEAVE LIST-PROCESSING. ENDIF. ***************损益表会计科目及格式维护 SELECT * INTO TABLE gt_tpkm FROM zqtfi002a. DATA: ls_dex TYPE i, ls_dex2 TYPE i. SELECT MAX( zitem ) INTO ls_dex2 FROM zqtfi002a. DO ls_dex2 TIMES. ls_dex = ls_dex + 1. t_out-zitem = ls_dex. APPEND t_out. ENDDO. RANGES: rg_hkont FOR bseg-hkont. FIELD-SYMBOLS <fs_h1> TYPE any. DATA: l_num LIKE bkpf-monat, l_hslvt LIKE glt0-hslvt, l_charh1(13). DATA: ls_zbysjs TYPE glt0-hsl01, "本月实际数 ls_zbnljs TYPE glt0-hsl01, "本年累计数 ls_zsnljs TYPE glt0-hsl01, "shang年累计数 ls_zbnsyljs TYPE glt0-hsl01. "本年上月累计数 DATA: ls_zzflag1 TYPE c. LOOP AT t_out. READ TABLE gt_tpkm INTO wa_tpkm WITH KEY zitem = t_out-zitem. IF sy-subrc = 0. t_out-zxiangm = wa_tpkm-zitemt."行项目名称 CLEAR wa_tpkm. LOOP AT gt_tpkm INTO wa_tpkm WHERE zitem = t_out-zitem AND zsaknr1 IS NOT INITIAL. CLEAR: ls_zbysjs,ls_zbnljs,ls_zsnljs,ls_zbnsyljs. "本年累计 LOOP AT gt_fag WHERE racct >= wa_tpkm-zsaknr1 AND racct <= wa_tpkm-zsaknr2. IF wa_tpkm-rfarea IS NOT INITIAL. CHECK wa_tpkm-rfarea = gt_fag-rfarea. ENDIF. IF gt_fag-ryear = p_ryear. l_hslvt = gt_fag-hslvt . CLEAR l_num. DO g_time TIMES. l_num = l_num + 1. CONCATENATE 'GT_FAG-HSL' l_num INTO l_charh1."总账表所需要计算的月份 ASSIGN (l_charh1) TO <fs_h1>. ls_zbnljs = ls_zbnljs + <fs_h1>."本年累计数 IF p_rpmax = l_num. ls_zbysjs = ls_zbysjs + <fs_h1>."本月实际数 ENDIF. CLEAR l_charh1. ENDDO. ls_zbnljs = ls_zbnljs + l_hslvt." "本年累计数 * ELSEIF gt_fag-ryear = last_year. * l_hslvt = gt_fag-hslvt . * CLEAR l_num. * DO g_time TIMES. * l_num = l_num + 1. * CONCATENATE 'GT_FAG-HSL' l_num INTO l_charh1."总账表所需要计算的月份 * ASSIGN (l_charh1) TO <fs_h1>. * ls_zsnljs = ls_zsnljs + <fs_h1>."上年累计数 * CLEAR l_charh1. * ENDDO. * ls_zsnljs = ls_zsnljs + l_hslvt." "上年累计数 ENDIF. ********************************************************************** 本年上月累计数 * IF gt_fag-ryear = g_year_sy. * l_hslvt = gt_fag-hslvt . * CLEAR l_num. * DO g_month_sy TIMES. * l_num = l_num + 1. * CONCATENATE 'GT_FAG-HSL' l_num INTO l_charh1."总账表所需要计算的月份 * ASSIGN (l_charh1) TO <fs_h1>. * ls_zbnsyljs = ls_zbnsyljs + <fs_h1>."上年累计数 * CLEAR l_charh1. * ENDDO. * ls_zbnsyljs = ls_zbnsyljs + l_hslvt." "上年累计数 * ENDIF. ********************************************************************** "本年上月累计数 ENDLOOP. IF wa_tpkm-zjia01 = '-'. "- ls_zsnljs = 0 - ls_zsnljs. ls_zbysjs = 0 - ls_zbysjs. ls_zbnljs = 0 - ls_zbnljs. ls_zbnsyljs = 0 - ls_zbnsyljs." "本年上月累计数 ENDIF. t_out-zbysjs = t_out-zbysjs + ls_zbysjs." "本月实际数 t_out-zbnljs = t_out-zbnljs + ls_zbnljs." "本年累计数 t_out-zsnljs = t_out-zsnljs + ls_zsnljs." "上年累计数 t_out-zbnsyljs = t_out-zbnsyljs + ls_zbnsyljs." "本年上月累计数 ENDLOOP. * IF s1 <> 'X'. * IF wa_tpkm-zamtdisp = 'Y'. "反向显示 * t_out-zsnljs = 0 - t_out-zsnljs. * t_out-zbysjs = 0 - t_out-zbysjs." "本月实际数 * t_out-zbnljs = 0 - t_out-zbnljs." "本年累计数 * t_out-zbnsyljs = 0 - t_out-zbnsyljs." "本年上月累计数 * ENDIF. * ENDIF . MODIFY t_out. ENDIF. ENDLOOP. LOOP AT t_out.""""""""""""将内表数据赋值给准备EXCEL输出的内表中。 wa_type-zitem = t_out-zitem. wa_type-c100 = t_out-zbysjs."本月实际数 wa_type-d100 = t_out-zbnljs."本年累计数 wa_type-e100 = t_out-zsnljs. wa_type-h100 = t_out-zbnsyljs."本年上月累计数 IF t_out-zbysjs < 0. CALL FUNCTION 'CLOI_PUT_SIGN_IN_FRONT' CHANGING value = wa_type-c100. ENDIF. IF t_out-zbnljs < 0. CALL FUNCTION 'CLOI_PUT_SIGN_IN_FRONT' CHANGING value = wa_type-d100. ENDIF. IF t_out-zsnljs < 0. CALL FUNCTION 'CLOI_PUT_SIGN_IN_FRONT' CHANGING value = wa_type-e100. ENDIF. IF t_out-zbnsyljs < 0."本年上月累计数 CALL FUNCTION 'CLOI_PUT_SIGN_IN_FRONT' CHANGING value = wa_type-h100. ENDIF. APPEND wa_type TO gt_type. ENDLOOP. CLEAR: gt_alv1. LOOP AT gt_type INTO wa_type. MOVE-CORRESPONDING wa_type TO wa_alv1. READ TABLE gt_tpkm INTO wa_tpkm WITH KEY zitem = wa_type-zitem. IF sy-subrc EQ 0. wa_alv1-zitemt = wa_tpkm-zitemt. ENDIF. wa_alv1-zbnsyljs = wa_type-h100."本年上月累计 wa_alv1-bukrs = p_bukrs. wa_alv1-zbysjs = wa_type-c100."本月实际 wa_alv1-zsnljs = wa_type-e100."上年累计 wa_alv1-zbnljs = wa_type-d100."本年累计 APPEND wa_alv1 TO gt_alv1. CLEAR:wa_alv1. ENDLOOP. """""""""添加公式 DATA:lv_index TYPE i, lv_index1 TYPE i. DATA:lv_shuzi TYPE char3, lv_char TYPE char1. DATA:lv_fh TYPE char1 . DATA:lv_dmbtr TYPE dmbtr, lv_dmbtr1 TYPE dmbtr. SORT gt_tpkm BY zitem . DATA:lv_rate TYPE string. IF p_waers EQ 'USD'. CLEAR lv_rate. " "如果是美元货币 CALL FUNCTION 'READ_EXCHANGE_RATE' EXPORTING date = sy-datum foreign_currency = 'USD' local_currency = 'MXN' IMPORTING exchange_rate = lv_rate. ENDIF. LOOP AT gt_tpkm INTO DATA(gs_tbkm) WHERE zgs IS NOT INITIAL . lv_index = strlen( gs_tbkm-zgs ). CLEAR lv_index1 . CLEAR:lv_dmbtr , lv_dmbtr1 . CLEAR:lv_shuzi . lv_fh = '+'. WHILE lv_index <> lv_index1. lv_char = gs_tbkm-zgs+lv_index1(1). IF lv_char = '+' OR lv_char = '-'. READ TABLE gt_alv1 INTO wa_alv1 WITH KEY zitem = lv_shuzi . IF sy-subrc = 0 . IF lv_fh = '+'. lv_dmbtr = lv_dmbtr + wa_alv1-zbysjs . lv_dmbtr1 = lv_dmbtr1 + wa_alv1-zbnljs . ELSEIF lv_fh = '-'. lv_dmbtr = lv_dmbtr - wa_alv1-zbysjs . lv_dmbtr1 = lv_dmbtr1 - wa_alv1-zbnljs . ENDIF. lv_fh = lv_char . ENDIF. CLEAR lv_shuzi . ELSE. lv_shuzi = lv_shuzi && lv_char . CONDENSE lv_shuzi NO-GAPS . ENDIF. lv_index1 = lv_index1 + 1 . IF lv_index1 = lv_index . READ TABLE gt_alv1 INTO wa_alv1 WITH KEY zitem = lv_shuzi . IF sy-subrc = 0 . IF lv_fh = '+'. lv_dmbtr = lv_dmbtr + wa_alv1-zbysjs . lv_dmbtr1 = lv_dmbtr1 + wa_alv1-zbnljs . ELSEIF lv_fh = '-'. lv_dmbtr = lv_dmbtr - wa_alv1-zbysjs . lv_dmbtr1 = lv_dmbtr1 - wa_alv1-zbnljs . ENDIF. lv_fh = lv_char . ENDIF. ENDIF. ENDWHILE. wa_alv1-zbysjs = lv_dmbtr . wa_alv1-zbnljs = lv_dmbtr1 . "汇率转换 IF p_waers EQ 'USD'. IF lv_rate <> 0. wa_alv1-zbnljs = wa_alv1-zbnljs / lv_rate. ENDIF. ENDIF. MODIFY gt_alv1 FROM wa_alv1 TRANSPORTING zbysjs zbnljs WHERE zitem = gs_tbkm-zitem . ENDLOOP. ENDFORM. " FRM_SUB_READ_TABLE *&---------------------------------------------------------------------* *& Form FRM_SUB_EXCEL_OUTPUT *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM frm_sub_excel_output . DATA: l_text TYPE string, l_objnm LIKE wwwdatatab-objid VALUE 'ZQFIR002', l_objdt LIKE wwwdatatab, l_rc TYPE sy-subrc. ** CLEAR: g_text1,g_text2,g_text3. SELECT SINGLE butxt INTO g_text1 FROM t001 WHERE bukrs = p_bukrs. CONCATENATE '编制单位：' g_text1 INTO g_text1 . CONCATENATE p_ryear '年' p_rpmax '月' INTO g_text2. CONCATENATE p_ryear '年' p_rpmax '月' TEXT-002 INTO g_text3. SELECT SINGLE relid objid INTO CORRESPONDING FIELDS OF l_objdt FROM wwwdata WHERE objid = l_objnm. IF sy-subrc <> 0. CONCATENATE '模板:' l_objnm '不存在，请使用事务码SMW0上传' INTO l_text. MESSAGE l_text TYPE 'S'. CLEAR l_text. LEAVE LIST-PROCESSING. ELSE. CALL FUNCTION 'TMP_GUI_BROWSE_FOR_FOLDER' EXPORTING window_title = '选择损益表保存文件夹' initial_folder = '' IMPORTING selected_folder = p_roald EXCEPTIONS cntl_error = 1 OTHERS = 2. CONCATENATE p_roald '\' g_text2 '_' '损益表.XLSX' INTO p_roald. CALL FUNCTION 'DOWNLOAD_WEB_OBJECT' EXPORTING key = l_objdt destination = p_roald IMPORTING rc = l_rc. IF l_rc <> 0. CLEAR l_text. CONCATENATE '模板:' l_objnm '下载失败，请与开发人员联系' INTO l_text. MESSAGE l_text TYPE 'S'. LEAVE LIST-PROCESSING. ENDIF. ENDIF. * 正在处理提示 PERFORM frm_process_prompt. * 打开EXCEL PERFORM frm_excel_open USING p_roald. * EXCEL赋值 PERFORM frm_excel_value. * 释放对象 PERFORM frm_excel_free. ENDFORM. " FRM_SUB_EXCEL_OUTPUT *&---------------------------------------------------------------------* *& Form FRM_PROCESS_PROMPT *&---------------------------------------------------------------------* * 正在处理提示 *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM frm_process_prompt . DATA: l_dis(150) TYPE c. CONCATENATE '正在处理文件:' p_roald INTO l_dis. CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' EXPORTING text = l_dis. ENDFORM. " FRM_PROCESS_PROMPT *&---------------------------------------------------------------------* *& Form FRM_EXCEL_OPEN *&---------------------------------------------------------------------* * 打开EXCEL *----------------------------------------------------------------------* * -->P_L_FILE text *----------------------------------------------------------------------* FORM frm_excel_open USING p_l_file. CREATE OBJECT excel 'EXCEL.APPLICATION'. IF sy-subrc <> 0. MESSAGE 'Excel开打失败!.' TYPE 'S'. STOP. ELSE. SET PROPERTY OF excel 'VISIBLE' = 1. CALL METHOD OF excel 'WORKBOOKS' = workbook. "新产生一个Excel CALL METHOD OF workbook 'OPEN' EXPORTING #1 = p_l_file. "要打开的Excel的路径 GET PROPERTY OF excel 'ACTIVECELL' = sheet. ENDIF. CALL METHOD OF excel 'WORKSHEETS' = sheet EXPORTING #1 = 'SHEET1'. CALL METHOD OF sheet 'ACTIVATE'. ENDFORM. " FRM_EXCEL_OPEN *&---------------------------------------------------------------------* *& Form FRM_EXCEL_VALUE *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM frm_excel_value . DATA: l_zitem TYPE zqtfi002-zitem, l_zcol TYPE zqtfi002-zcol. *打开第一个Sheet CALL METHOD OF excel 'WORKSHEETS' = sheet EXPORTING #1 = 1. CALL METHOD OF sheet 'ACTIVATE'. SELECT SINGLE butxt INTO @DATA(lv_butxt) FROM t001 WHERE bukrs = @p_bukrs. g_text2 = '编制单位:' && lv_butxt && ' /' && g_text2 . REPLACE '/' IN g_text2 WITH space . ** "PERFORM frm_cell_set USING 2 1 g_text3. "PERFORM frm_cell_set USING 3 2 g_text3. "年月 "PERFORM frm_cell_set USING 3 1 lv_butxt. "公司名称 "PERFORM frm_cell_set USING 4 1 g_text1. PERFORM frm_cell_set USING 3 1 g_text2. * LOOP AT gt_type. * l_zitem = gt_type-zitem. * READ TABLE gt_tpkm INTO wa_tpkm WITH KEY * zitem = l_zitem. * CHECK sy-subrc = 0 AND * wa_tpkm-zrow IS NOT INITIAL AND * wa_tpkm-zcol IS NOT INITIAL. * * IF gt_type-c100 NE 0. * PERFORM frm_cell_set USING wa_tpkm-zrow wa_tpkm-zcol gt_type-c100 . * ENDIF. * IF gt_type-d100 NE 0. * l_zcol = wa_tpkm-zcol + 1. * PERFORM frm_cell_set USING wa_tpkm-zrow l_zcol gt_type-d100 . * ENDIF. * ENDLOOP. DATA: lv_c100 TYPE c LENGTH 50, lv_d100 TYPE c LENGTH 50. LOOP AT gt_alv1 INTO DATA(ls_alv1). l_zitem = ls_alv1-zitem. lv_c100 = ls_alv1-zbysjs. lv_d100 = ls_alv1-zbnljs. CONDENSE lv_c100 NO-GAPS. CONDENSE lv_d100 NO-GAPS. IF ls_alv1-zbysjs < 0. CALL FUNCTION 'CLOI_PUT_SIGN_IN_FRONT' CHANGING value = lv_c100. ENDIF. IF ls_alv1-zbnljs < 0. CALL FUNCTION 'CLOI_PUT_SIGN_IN_FRONT' CHANGING value = lv_d100. ENDIF. READ TABLE gt_tpkm INTO wa_tpkm WITH KEY zitem = l_zitem. CHECK sy-subrc = 0 AND wa_tpkm-zrow IS NOT INITIAL AND wa_tpkm-zcol IS NOT INITIAL. IF lv_c100 NE 0. PERFORM frm_cell_set USING wa_tpkm-zrow wa_tpkm-zcol lv_c100 . ENDIF. IF lv_d100 NE 0. l_zcol = wa_tpkm-zcol + 1. PERFORM frm_cell_set USING wa_tpkm-zrow l_zcol lv_d100 . ENDIF. CLEAR: lv_c100, lv_d100. ENDLOOP. ***************** GET PROPERTY OF excel 'ACTIVESHEET' = sheet. GET PROPERTY OF excel 'ACTIVEWORKBOOK' = workbook. CALL METHOD OF workbook 'SAVE'. ENDFORM. " FRM_EXCEL_VALUE *&---------------------------------------------------------------------* *& Form FRM_EXCEL_FREE *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM frm_excel_free . FREE: excel,workbook,sheet,cell. ENDFORM. " FRM_EXCEL_FREE *&---------------------------------------------------------------------* *& Form FRM_CELL_SET *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * -->P_ROW 第几行 * -->P_COL 第几列 * -->P_D_BUTXT 要填的值 *----------------------------------------------------------------------* FORM frm_cell_set USING p_row p_col p_d_butxt. CALL METHOD OF sheet 'CELLS' = cell EXPORTING #1 = p_row #2 = p_col. SET PROPERTY OF cell 'VALUE' = p_d_butxt. ENDFORM. " FRM_CELL_SET *&---------------------------------------------------------------------* *& Form reda_excel_data *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * -->U_FROW text * -->U_FCOL text *----------------------------------------------------------------------* FORM reda_excel_data USING u_frow u_fcol. * U_TROW * U_TCOL. DATA: lo_cell TYPE ole2_object, lo_cell1 TYPE ole2_object, lo_range TYPE ole2_object. CALL METHOD OF sheet 'Cells' = lo_cell EXPORTING #1 = u_frow #2 = u_fcol. CALL METHOD OF sheet 'Cells' = lo_cell1 EXPORTING #1 = u_frow #2 = u_fcol. CALL METHOD OF sheet 'RANGE' = lo_range EXPORTING #1 = lo_cell #2 = lo_cell1. CALL METHOD OF lo_range 'SELECT'. CALL METHOD OF sheet 'PASTE'. FREE OBJECT lo_cell. FREE OBJECT lo_cell1. FREE OBJECT lo_range. ENDFORM. " REDA_EXCEL_DATA *&---------------------------------------------------------------------* *& FORM FRM_INITIAL *&---------------------------------------------------------------------* *& 报表初始化 *&---------------------------------------------------------------------* FORM frm_initial . * 指派输出表 PERFORM frm_assign_table TABLES gt_alv1[]. * 设置表格字段文本元素前缀字符 PERFORM frm_set_grid_label USING 'C'. * 设置初始化GUI状态的子过程 PERFORM frm_set_gui_status_from USING c_form_pf_status. * 捕捉“全选”，“全不选”命令 PERFORM frm_catch_command USING: '&ALL', '&SAL'. * 设置接受用户命令的子过程 PERFORM frm_set_command_form USING c_form_command. gs_layout-stylefname = 'STYLE'. ENDFORM. "FRM_INITIAL *&---------------------------------------------------------------------* *& FORM FRM_TOP_OF_PAGE *&---------------------------------------------------------------------* * 表头 *----------------------------------------------------------------------* * --> P1 TEXT * <-- P2 TEXT *----------------------------------------------------------------------* FORM frm_top_of_page. "建立输出抬头 PERFORM frm_comment_build. "执行显示抬头标题 CALL FUNCTION 'REUSE_ALV_COMMENTARY_WRITE' EXPORTING it_list_commentary = it_listheader. ENDFORM. "FRM_TOP_OF_PAGE *&---------------------------------------------------------------------* *& FORM FRM_COMMENT_BUILD *&---------------------------------------------------------------------* * TEXT *----------------------------------------------------------------------* * --> P1 TEXT * <-- P2 TEXT *----------------------------------------------------------------------* FORM frm_comment_build . DATA lw_listheader TYPE slis_listheader. CLEAR: it_listheader, lw_listheader. lw_listheader-typ = 'H'. lw_listheader-info = 'Estado de resultados'. APPEND lw_listheader TO it_listheader. * CLEAR lw_listheader. * lw_listheader-typ = 'S'. * lw_listheader-key = '会企03表'. * APPEND lw_listheader TO it_listheader. lw_listheader-typ = 'S'. * LW_LISTHEADER-INFO = '制表单位：'. lw_listheader-key = str_butxt.. APPEND lw_listheader TO it_listheader. CLEAR lw_listheader. lw_listheader-typ = 'S'. lw_listheader-key = p_rpmax. APPEND lw_listheader TO it_listheader. CLEAR lw_listheader. lw_listheader-typ = 'S'. lw_listheader-key = 'Unidades:' && p_waers. APPEND lw_listheader TO it_listheader. ENDFORM. " FRM_COMMENT_BUILD *&---------------------------------------------------------------------* *& FORM FRM_PF_STATUS *&---------------------------------------------------------------------* * 设置状态栏 *----------------------------------------------------------------------* FORM frm_pf_status USING pt_extab TYPE slis_t_extab. SET PF-STATUS 'STANDARD' EXCLUDING pt_extab. ENDFORM. "FRM_PF_STATUS FORM frm_user_command USING pv_ucomm LIKE sy-ucomm ps_selfield TYPE slis_selfield. FIELD-SYMBOLS <fs_out> TYPE any. CASE pv_ucomm. WHEN '&IC1'."点击ALV单元格，实现显示明细 CHECK NOT ps_selfield-value IS INITIAL. READ TABLE <fs_outtab> ASSIGNING <fs_out> INDEX ps_selfield-tabindex. CHECK sy-subrc EQ 0. PERFORM frm_get_document USING <fs_out>. MOVE-CORRESPONDING <fs_out> TO it_acdoca_mx. PERFORM frm_show_detail USING it_acdoca_mx ps_selfield-fieldname. PERFORM frm_refresh_display USING ps_selfield. WHEN '&EXPORT'."导出EXCEL "PERFORM frm_save_data_excel. PERFORM frm_sub_excel_output. ENDCASE. ENDFORM. "FRM_USER_COMMAND *---------------------------------------------------------------------* * FORM FRM_EDIT_FCAT *---------------------------------------------------------------------* * 编辑表头字段 *---------------------------------------------------------------------* FORM frm_edit_fcat. ** 0不显示 * $FIELD NO_ZERO: 'BNHSL','SNHSL'. * 隐藏字段 $field no_out: 'ZSAKNR1', 'ZSAKNR2', 'ZSNLJS','ZBNSYLJS'. "by wangwei 20200720隐藏上年累计和本年上月累计两个字段 "注册事件 PERFORM frm_register_events. ENDFORM. "FRM_EDIT_FCAT *&---------------------------------------------------------------------* *& FORM FRM_REGISTER_EVENTS *&---------------------------------------------------------------------* *& 注册事件 *&---------------------------------------------------------------------* FORM frm_register_events . DATA: formname_top_of_page TYPE slis_formname VALUE 'FRM_TOP_OF_PAGE'. DATA l_events TYPE slis_alv_event. CALL FUNCTION 'REUSE_ALV_EVENTS_GET' EXPORTING i_list_type = 0 IMPORTING et_events = gt_event EXCEPTIONS list_type_wrong = 1 OTHERS = 2. 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 l_events. READ TABLE gt_event WITH KEY name = slis_ev_top_of_page INTO l_events. IF sy-subrc = 0. MOVE formname_top_of_page TO l_events-form. MODIFY gt_event FROM l_events INDEX sy-tabix. ELSE. l_events-form = formname_top_of_page. l_events-name = formname_top_of_page. APPEND l_events TO gt_local_evt. ENDIF. ENDFORM. *&---------------------------------------------------------------------* *& Form FRM_GET_DOCUMENT *&---------------------------------------------------------------------* *& text *&---------------------------------------------------------------------* *& --> <FS_OUT> *&---------------------------------------------------------------------* FORM frm_get_document USING ps_out TYPE zqsficor002. DATA:rt_hkont TYPE RANGE OF skat-saknr, rs_hkont LIKE LINE OF rt_hkont. REFRESH:it_acdoca_mx. LOOP AT gt_tpkm INTO wa_tpkm WHERE zitem = ps_out-zitem. IF wa_tpkm-zsaknr1 IS NOT INITIAL AND wa_tpkm-zsaknr2 IS NOT INITIAL. rs_hkont-sign = 'I'. rs_hkont-option = 'BT'. rs_hkont-low = wa_tpkm-zsaknr1. rs_hkont-high = wa_tpkm-zsaknr2. APPEND rs_hkont TO rt_hkont. CLEAR: rs_hkont. ELSEIF wa_tpkm-zsaknr1 IS NOT INITIAL. rs_hkont-sign = 'I'. rs_hkont-option = 'EQ'. rs_hkont-low = wa_tpkm-zsaknr1. APPEND rs_hkont TO rt_hkont. CLEAR: rs_hkont. ENDIF. ENDLOOP. *****查询会计凭证信息 IF rt_hkont IS NOT INITIAL. SELECT acdoca~rbukrs "公司代码 acdoca~belnr "会计凭证 acdoca~ryear "年度 acdoca~docln "行项目 acdoca~drcrk "借贷标识 acdoca~budat "过账日期 acdoca~hsl "金额 acdoca~racct "科目号 skat~txt20 INTO CORRESPONDING FIELDS OF TABLE it_acdoca_mx FROM acdoca INNER JOIN skat ON acdoca~racct = skat~saknr WHERE acdoca~racct IN rt_hkont AND acdoca~rbukrs EQ p_bukrs AND acdoca~gjahr EQ p_ryear AND acdoca~poper EQ p_rpmax AND skat~spras EQ sy-langu AND skat~ktopl EQ 'HD01'. ENDIF. ENDFORM. *&---------------------------------------------------------------------* *& FORM FRM_SHOW_DETAIL *&---------------------------------------------------------------------* *& 显示明细数据 *&---------------------------------------------------------------------* FORM frm_show_detail USING ps_data LIKE it_acdoca_mx pv_field TYPE fieldname. DATA: pt_fieldcat TYPE slis_t_fieldcat_alv. DATA: ls_layout TYPE slis_layout_alv, ls_fieldcat TYPE slis_fieldcat_alv, lt_fieldcat TYPE slis_t_fieldcat_alv. * BUILD THE FIELD CATALOG DEFINE m_fieldcat1. ADD 1 TO ls_fieldcat-col_pos. ls_fieldcat-fieldname = &1. ls_fieldcat-seltext_l = &2. ls_fieldcat-outputlen = &3. ls_fieldcat-emphasize = &4. ls_fieldcat-key = &5. ls_fieldcat-do_sum = &6. APPEND ls_fieldcat TO lt_fieldcat. END-OF-DEFINITION. " m_fieldcat1 'ZNO' '报表项' '8' '' '' ''. m_fieldcat1 'RBUKRS' '公司代码' '8' '' 'X' ''. m_fieldcat1 'BUDAT' '过账日期' '10' '' '' ''. m_fieldcat1 'GJAHR' '年度' '6' '' 'X' ''. m_fieldcat1 'BELNR' '会计凭证' '10' '' 'X' ''. m_fieldcat1 'DOCLN' '行项目' '6' '' 'X' ''. m_fieldcat1 'DRCRK' '借贷标识' '8' '' 'X' ''. m_fieldcat1 'HSL' '金额' '16' '' '' 'X'. m_fieldcat1 'RACCT' '科目号' '10' '' '' ''. m_fieldcat1 'TXT20' '科目描述' '20' '' '' ''. * m_fieldcat1 'RSTGR' '原因代码' '8' '' '' ''. * m_fieldcat1 'ZTXT' '编号文本' '20' '' '' ''. * m_fieldcat1 'ZTYPE' '类型' '4' '' '' ''. * m_fieldcat1 'OPERATOR' '运算符' '6' '' '' ''. SORT it_acdoca_mx BY budat gjahr belnr belnr. * 显示明细数据 ls_layout-colwidth_optimize = 'X'. CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY' EXPORTING i_callback_program = sy-repid * I_CALLBACK_TOP_OF_PAGE = 'FRM_TOP_OF_PAGE' it_fieldcat = lt_fieldcat is_layout = ls_layout * I_CALLBACK_PF_STATUS_SET = 'STANDARD1' i_callback_user_command = 'FRM_USER_COMMAND1' i_default = 'X' i_save = 'A' * IT_FILTER = PT_FILTER * IT_SORT = PT_SORT TABLES t_outtab = it_acdoca_mx EXCEPTIONS program_error = 1 OTHERS = 2. ENDFORM. "FRM_SHOW_DETAIL FORM frm_user_command1 USING pv_ucomm LIKE sy-ucomm ps_selfield TYPE slis_selfield. DATA : lv_grid TYPE REF TO cl_gui_alv_grid. CALL FUNCTION 'GET_GLOBALS_FROM_SLVC_FULLSCR' IMPORTING e_grid = lv_grid. CALL METHOD lv_grid->check_changed_data. CASE pv_ucomm. WHEN '&IC1'. CLEAR it_acdoca_mx. READ TABLE it_acdoca_mx INDEX ps_selfield-tabindex. IF sy-subrc = 0. CASE ps_selfield-fieldname. WHEN 'GJAHR' OR 'BELNR' OR 'DOCLN'. SET PARAMETER ID 'BLN' FIELD it_acdoca_mx-belnr. SET PARAMETER ID 'GJR' FIELD it_acdoca_mx-gjahr. SET PARAMETER ID 'BUK' FIELD it_acdoca_mx-rbukrs. CALL TRANSACTION 'FB03' AND SKIP FIRST SCREEN . ENDCASE. ENDIF. ENDCASE. ENDFORM. "FRM_USER_COMMAND1