ABAP EXCEL上载 / 下载(2)

*&---------------------------------------------------------------------*
*& Report  ZFIU04
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT ZFIU04.

TABLES:RBKP,LFA1,BKPF,BSAK,BSEG.

INCLUDE OLE2INCL.    "FOR OLE
DATA: EXCEL    TYPE OLE2_OBJECT,
      BOOKS    TYPE OLE2_OBJECT,
      SHEET    TYPE OLE2_OBJECT,
      CELL     TYPE OLE2_OBJECT,
      RANGE    TYPE OLE2_OBJECT,
      WORKBOOK TYPE OLE2_OBJECT.

TYPES: BEGIN OF GS_TAB,
         BELNR     TYPE BSAK-BELNR,
         GJAHR     TYPE BSEG-GJAHR,
         BUKRS     TYPE BSEG-BUKRS,
         LIFNR     TYPE BSEG-LIFNR,
         BKTXT     TYPE BKPF-BKTXT,
         BUDAT     TYPE BKPF-BUDAT,
         DMBTR1    TYPE BSAK-DMBTR,
         NAME1     TYPE LFA1-NAME1,
         STRAS     TYPE LFA1-STRAS,
         MCOD3     TYPE LFA1-MCOD3,
**         BELNR1    TYPE BKPF-BELNR,
         AUGBL     TYPE BSAK-AUGBL,
         ZUONR     TYPE BSEG-ZUONR,
         DMBTR     TYPE BSEG-DMBTR,
         GBELNR    TYPE STRING,
         NUM_ZUONR TYPE BSEG-ZUONR,
       END OF GS_TAB.

DATA:GT_TAB  TYPE STANDARD TABLE OF GS_TAB WITH HEADER LINE,
     GT_BESG TYPE STANDARD TABLE OF GS_TAB WITH HEADER LINE.
DATA:GT_TAB1 TYPE STANDARD TABLE OF GS_TAB WITH HEADER LINE.
DATA: COUNT       TYPE I,
      COUNT1      TYPE I,
      N           TYPE P,
      AMOUNT      TYPE RBKP-RMWWR,
      EN_AMOUNT   TYPE STRING,
      ZSPELL      LIKE  SPELL,
      JSHJ        TYPE STRING,
      JSHJ1       TYPE STRING,
      JSHJ2       TYPE STRING,
      HJTXT(255),
      HJTXT1(255).
DATA: DATE  TYPE STRING,
      DATE1 TYPE STRING,
      DATE2 TYPE STRING,
      DATE3 TYPE STRING.




DATA: L_EXIST.
PARAMETERS:P_ZUOBR LIKE BSEG-ZUONR.
PARAMETERS:P_GJAHR LIKE RBKP-GJAHR,
           P_BUKRS LIKE RBKP-BUKRS,
**           P_LIFNR LIKE RBKP-LIFNR,
           P_DATE  LIKE SY-DATUM.

PARAMETERS: P_FNAME(60) DEFAULT 'D:\MODEL\ZFIU04.xls ' MODIF ID TY1.

AT SELECTION-SCREEN OUTPUT.
  P_DATE = SY-DATUM.
  DATE = P_DATE.

AT SELECTION-SCREEN.

START-OF-SELECTION .

  CALL FUNCTION 'TMP_GUI_GET_FILE_EXIST'
    EXPORTING
      FNAME          = P_FNAME
    IMPORTING
      EXIST          = L_EXIST
**     ISDIR          =
**     FILESIZE       =
    EXCEPTIONS
      FILEINFO_ERROR = 1
      OTHERS         = 2.

  IF SY-SUBRC <> 0 OR L_EXIST <> 'X'.
    MESSAGE I398(00) WITH 'Open model file' P_FNAME'err！！Please check exist'.
    EXIT.
  ENDIF.
  PERFORM GET_DATA.
  PERFORM EXPORT_EXCEL.

FORM GET_DATA.

  SELECT DISTINCT A~ZUONR,A~BELNR AS BELNR1,A~LIFNR,A~BUKRS,A~GJAHR, B~AUGBL
  FROM BSEG AS A INNER JOIN BSAK AS B
  ON A~BELNR = B~BELNR
  AND A~GJAHR = B~GJAHR
  AND A~BUKRS = B~BUKRS
  INTO CORRESPONDING FIELDS OF TABLE @GT_TAB1
  WHERE A~ZUONR EQ @P_ZUOBR
**    AND B~BLART EQ 'KZ'
  AND   A~GJAHR EQ @P_GJAHR
  AND   A~BUKRS EQ @P_BUKRS.
  
  SELECT A~BELNR,A~AUGBL,A~DMBTR AS DMBTR1,A~BUKRS,A~GJAHR, B~DMBTR,B~LIFNR,
  C~NAME1,C~STRAS,C~MCOD3,D~BKTXT,D~BUDAT
  
  FROM BSAK AS A
  INNER JOIN BSEG AS B
  ON A~BELNR = B~BELNR
  AND A~GJAHR = B~GJAHR
  AND A~BUKRS = B~BUKRS
  INNER JOIN BKPF AS D
  ON D~BELNR = B~BELNR
  AND D~GJAHR = B~GJAHR
  AND D~BUKRS = B~BUKRS
  INNER JOIN LFA1 AS C
  ON C~LIFNR = B~LIFNR
  INTO CORRESPONDING FIELDS OF TABLE @GT_TAB
  FOR ALL ENTRIES IN @GT_TAB1
  WHERE A~AUGBL = @GT_TAB1-AUGBL
  AND A~BUKRS = @GT_TAB1-BUKRS
  AND A~GJAHR = @GT_TAB1-GJAHR
  AND A~BLART NE 'KZ'
  AND B~KOART EQ 'K'.

  SELECT A~AUGBL, A~BELNR,B~DMBTR
  FROM BSAK AS A
  INNER JOIN BSEG AS B
  ON A~BELNR = B~BELNR
  AND A~GJAHR = B~GJAHR
  AND A~BUKRS = B~BUKRS
  INTO CORRESPONDING FIELDS OF TABLE @GT_BESG
FOR ALL ENTRIES IN @GT_TAB
WHERE A~AUGBL = @GT_TAB-AUGBL
AND A~BUKRS = @GT_TAB-BUKRS
AND A~GJAHR = @GT_TAB-GJAHR
AND A~BLART EQ 'KZ'
AND B~ZUONR EQ @P_ZUOBR.
  .
  SORT GT_TAB DESCENDING BY AUGBL.
  SORT GT_BESG DESCENDING BY AUGBL.

  SELECT A~AUGBL, A~BELNR,B~DMBTR
  FROM BSAK AS A
  INNER JOIN BSEG AS B
  ON A~BELNR = B~BELNR
  AND A~GJAHR = B~GJAHR
  AND A~BUKRS = B~BUKRS
  INTO TABLE @DATA(GT_BESG1)
  FOR ALL ENTRIES IN @GT_TAB
  WHERE A~AUGBL = @GT_TAB-AUGBL
  AND A~BUKRS = @GT_TAB-BUKRS
  AND A~GJAHR = @GT_TAB-GJAHR
  AND A~BLART EQ 'KZ'.

  N = LINES( GT_BESG1 )."行数
  IF N NE 1.
    LOOP AT GT_TAB.
      READ TABLE GT_BESG WITH KEY AUGBL = GT_TAB-AUGBL BINARY SEARCH.
      IF SY-SUBRC EQ 0.
        GT_TAB-AUGBL = GT_BESG-AUGBL .
*        GT_TAB-BELNR = GT_BESG-BELNR .
        GT_TAB-DMBTR1 = GT_BESG-DMBTR .
      ENDIF.

      MODIFY GT_TAB.
    ENDLOOP.
  ENDIF.
  DATE1 = DATE+0(4).
  DATE2 = DATE+4(2).
  DATE3 = DATE+6(2).
  DATE = |{ DATE1 }/{ DATE2 }/{ DATE3 }|.
  CLEAR:AMOUNT.
  LOOP AT GT_TAB.

    GT_TAB-NUM_ZUONR = P_ZUOBR.
    CALL FUNCTION 'PREPARE_STRING' "只取数字格式
      EXPORTING
        I_VALID_CHARS  = ' 1234567890.,'
        I_XVALID_CHECK = 'X'
        I_XCHAR_REPL   = 'X'
        I_XTOUPPER     = 'X'
      CHANGING
        C_STRING       = GT_TAB-NUM_ZUONR.
    CONDENSE GT_TAB-NUM_ZUONR NO-GAPS.

    AMOUNT = AMOUNT + GT_TAB-DMBTR1.
    MODIFY GT_TAB.
  ENDLOOP.

  JSHJ = AMOUNT .
  SPLIT JSHJ AT '.' INTO   JSHJ1 JSHJ2.
  CONDENSE JSHJ1 NO-GAPS.
  CONCATENATE JSHJ1 '.00' INTO JSHJ1.

  CONDENSE JSHJ2 NO-GAPS.
  CALL FUNCTION 'SPELL_AMOUNT'
    EXPORTING
      AMOUNT   = JSHJ1
      CURRENCY = 'USD'
*     FILLER   = ' '
      LANGUAGE = 'E'
    IMPORTING
      IN_WORDS = ZSPELL.

  IF SY-SUBRC =  0.
    HJTXT = ZSPELL-WORD.
  ENDIF.
  CONDENSE JSHJ2 NO-GAPS.
  IF JSHJ2 <> '00'.
    CONDENSE JSHJ2 NO-GAPS.
    CONCATENATE JSHJ2 '.00' INTO JSHJ2.
    CALL FUNCTION 'SPELL_AMOUNT'
      EXPORTING
        AMOUNT   = JSHJ2
        CURRENCY = 'USD'
*       FILLER   = ' '
        LANGUAGE = 'E'
      IMPORTING
        IN_WORDS = ZSPELL.
    IF SY-SUBRC =  0.
      HJTXT1 = ZSPELL-WORD.
    ENDIF.
    .
    CONCATENATE HJTXT 'AND CENTS'  HJTXT1 'ONLY' INTO  EN_AMOUNT.
  ELSE.
    EN_AMOUNT = HJTXT .
  ENDIF.
*BREAK-POINT.
ENDFORM.

FORM EXPORT_EXCEL.
  DATA: L_SEQ TYPE I.

  CREATE OBJECT EXCEL 'EXCEL.APPLICATION'.
  CALL METHOD OF EXCEL 'WORKBOOKS' = BOOKS.
  CALL METHOD OF BOOKS 'OPEN'
    EXPORTING
      #1 = P_FNAME.
  CALL METHOD OF EXCEL 'WORKSHEETS' = SHEET
  EXPORTING #1 = 1.
  CALL METHOD OF SHEET 'ACTIVATE'.
  CALL METHOD OF EXCEL 'CELLS' = CELL
  EXPORTING #1 = 1 #2 = 1.
  "表头输出
  READ TABLE GT_TAB INDEX 1.
  CHECK  SY-SUBRC = 0.
  COUNT = 6.
  COUNT1 = 44.
  LOOP AT GT_TAB.

    PERFORM FILL_CELL USING COUNT 1  GT_TAB-BELNR.
    PERFORM FILL_CELL USING COUNT 2  GT_TAB-BKTXT.
    PERFORM FILL_CELL USING COUNT 3  GT_TAB-BUDAT.
    PERFORM FILL_CELL USING COUNT 4  GT_TAB-DMBTR.
    PERFORM FILL_CELL USING COUNT 6  GT_TAB-DMBTR1.
    COUNT = COUNT + 1.

    PERFORM FILL_CELL USING COUNT1 1  GT_TAB-BELNR.
    PERFORM FILL_CELL USING COUNT1 2  GT_TAB-BKTXT.
    PERFORM FILL_CELL USING COUNT1 3  GT_TAB-BUDAT.
    PERFORM FILL_CELL USING COUNT1 4  GT_TAB-DMBTR.
    PERFORM FILL_CELL USING COUNT1 6  GT_TAB-DMBTR1.
    COUNT1 = COUNT1 + 1.

  ENDLOOP.

  PERFORM FILL_CELL USING 19 1 DATE .
  PERFORM FILL_CELL USING 19 3 GT_TAB-NAME1.

  PERFORM FILL_CELL USING 30 2 GT_TAB-NAME1 .
  PERFORM FILL_CELL USING 31 2 GT_TAB-STRAS.
  PERFORM FILL_CELL USING 32 2 GT_TAB-MCOD3 .

  PERFORM FILL_CELL USING 24 6 DATE.
  PERFORM FILL_CELL USING 27 6 AMOUNT.
  PERFORM FILL_CELL USING 19 6 AMOUNT.
  PERFORM FILL_CELL USING 57 6 AMOUNT.

  PERFORM FILL_CELL USING 57 1 DATE .
  PERFORM FILL_CELL USING 57 3 GT_TAB-NAME1.

  PERFORM FILL_CELL USING  3 6 GT_TAB-NUM_ZUONR.
  PERFORM FILL_CELL USING 22 6 GT_TAB-NUM_ZUONR.
  PERFORM FILL_CELL USING 41 6 GT_TAB-NUM_ZUONR.
  PERFORM FILL_CELL USING 37 2 GT_TAB-NUM_ZUONR.

  PERFORM FILL_CELL USING 19 2  P_ZUOBR.
  PERFORM FILL_CELL USING 57 2  P_ZUOBR.
  PERFORM FILL_CELL USING 26 4  P_ZUOBR.
*  PERFORM FILL_CELL USING 29 2  EN_AMOUNT.


  SET PROPERTY OF EXCEL 'Visible' = 1."必须

ENDFORM.

FORM FILL_CELL USING    P_I
      P_J
      P_VAL.
  CALL METHOD OF EXCEL 'CELLS' = CELL EXPORTING #1
    = P_I #2 = P_J.
  SET PROPERTY OF CELL 'VALUE' = P_VAL.

ENDFORM.                    " FILL_CELL
*--------------------------------------------------
*       form save_book
*--------------------------------------------------
FORM SAVE_BOOK.
  GET PROPERTY OF EXCEL 'ActiveSheet' = SHEET.
  FREE OBJECT SHEET.
  FREE OBJECT WORKBOOK.

  GET PROPERTY OF EXCEL 'ActiveWorkbook' = WORKBOOK
  .
  CALL METHOD OF WORKBOOK 'SAVEAS'
    EXPORTING
      #1 =
           P_FNAME
      #2 = 1.
  CALL METHOD OF WORKBOOK 'CLOSE'.
  CALL METHOD OF EXCEL 'QUIT'.

  FREE OBJECT SHEET.
  FREE OBJECT WORKBOOK.
  FREE OBJECT EXCEL.
ENDFORM.                    "SAVE_BOOK