利润表。。。。

 

*&---------------------------------------------------------------------*
*& 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