*&---------------------------------------------------------------------*
*& 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
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了