abap导出excel代码

最新推荐文章于 2024-08-30 11:27:28 发布

smzok

最新推荐文章于 2024-08-30 11:27:28 发布

阅读量1.1k

点赞数

分类专栏： SAP导出excel abap写excel abap导出excel abap专区文章标签： excel object insert session function include

本文链接：https://blog.csdn.net/smzok/article/details/7541010

版权

abap专区同时被 3 个专栏收录

2 篇文章 0 订阅

订阅专栏

SAP导出excel

1 篇文章 0 订阅

订阅专栏

abap写excel

1 篇文章 0 订阅

订阅专栏

子文件 ZYQJH_INCLUDE ：

*INCLUDE OLE2INCL.
INCLUDE OLE2INCL.
DATA: EXCELOBJ TYPE OLE2_OBJECT,       " Excel object
      WBOOKOBJ TYPE OLE2_OBJECT,       " Workbook object
      WSHEETOBJ TYPE OLE2_OBJECT,      " Worksheet object
      RANGEOBJ TYPE OLE2_OBJECT,       " Range object
      RANGEOBJ2 TYPE OLE2_OBJECT,       " Range object
      CELLOBJ TYPE OLE2_OBJECT,        " Cell object
    SRCFILE31(128) TYPE C VALUE 'C:\sapworkdir\111.xls'.   "source excel

DEFINE CREATE_EXCEL.
  CREATE OBJECT EXCELOBJ 'Excel.Application'.  " Create excel object
  IF SY-SUBRC NE 0.
    WRITE: / 'Excel init failed, return code is ', SY-SUBRC.
    EXIT.
  ELSE.
    SET PROPERTY OF EXCELOBJ 'Visible' = 1.   " Set excel visible
    CALL METHOD OF EXCELOBJ              " Create wbook object
    'Workbooks' = WBOOKOBJ.
    CALL METHOD OF WBOOKOBJ 'Open' = WBOOKOBJ
      EXPORTING
        #1 = &1.                                            "srcfile1.
    CALL METHOD OF WBOOKOBJ              " Create wsheet object
         'Worksheets' = WSHEETOBJ EXPORTING #1 = 'sheet1'.
    CALL METHOD OF WSHEETOBJ 'Activate'.
  ENDIF.
END-OF-DEFINITION.

DEFINE CREATE_EXCELA.
  CREATE OBJECT EXCELOBJ 'Excel.Application'.  " Create excel object
  IF SY-SUBRC NE 0.
    WRITE: / 'Excel init failed, return code is ', SY-SUBRC.
    EXIT.
  ELSE.
    SET PROPERTY OF EXCELOBJ 'Visible' = 0.   " Set excel visible
    CALL METHOD OF EXCELOBJ              " Create wbook object
    'Workbooks' = WBOOKOBJ.
    CALL METHOD OF WBOOKOBJ 'Open' = WBOOKOBJ
      EXPORTING
        #1 = &1.                                            "srcfile1.
    CALL METHOD OF WBOOKOBJ              " Create wsheet object
         'Worksheets' = WSHEETOBJ EXPORTING #1 = 'sheet1'.
    CALL METHOD OF WSHEETOBJ 'Activate'.
  ENDIF.
END-OF-DEFINITION.



DEFINE SET_VISIBLE.
  SET PROPERTY OF EXCELOBJ 'Visible' = 1.   " Set excel visible
END-OF-DEFINITION.

*---------------------------------------------------------------------*
*       FORM fill_range                                               *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  VALUE(F_RANGE)                                                *
*  -->  VALUE(F_VALUE)                                                *
*---------------------------------------------------------------------*
FORM FILL_RANGE USING VALUE(F_RANGE)  VALUE(F_VALUE).
  CALL METHOD OF WSHEETOBJ 'Range' = RANGEOBJ
    EXPORTING
      #1 = F_RANGE.
  SET PROPERTY OF RANGEOBJ 'Value' = F_VALUE.
ENDFORM.                    "fill_range

*---------------------------------------------------------------------*
*       FORM fill_cell                                                *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  VALUE(F_RANGE)                                                *
*  -->  VALUE(F_VALUE)                                                *
*---------------------------------------------------------------------*
FORM FILL_CELL USING VALUE(F_RANGE)  VALUE(F_VALUE).
  CALL METHOD OF WSHEETOBJ 'Cells' = CELLOBJ
    EXPORTING
      #1 = F_RANGE.
  SET PROPERTY OF RANGEOBJ 'Value' = F_VALUE.
ENDFORM.                    "fill_cell

*---------------------------------------------------------------------*
*       FORM insert_row                                               *
*---------------------------------------------------------------------*
*       ........                                                      *
*---------------------------------------------------------------------*
*  -->  VALUE(F_RANGE)                                                *
*---------------------------------------------------------------------*
FORM INSERT_ROW USING VALUE(F_RANGE).
  CALL METHOD OF WSHEETOBJ 'range' = RANGEOBJ
    EXPORTING
      #1 = F_RANGE.
  CALL METHOD OF RANGEOBJ 'copy'.
  CALL METHOD OF RANGEOBJ 'select'.
  CALL METHOD OF RANGEOBJ 'insert'.
  CALL METHOD OF RANGEOBJ 'clearcontents'.
ENDFORM.                    "insert_row

*插入后不清除内容，特别实用于题头和表头
FORM INSERT_ROWA USING VALUE(F_RANGE).
  CALL METHOD OF WSHEETOBJ 'range' = RANGEOBJ
    EXPORTING
      #1 = F_RANGE.
  CALL METHOD OF RANGEOBJ 'copy'.
  CALL METHOD OF RANGEOBJ 'select'.
  CALL METHOD OF RANGEOBJ 'insert'.
ENDFORM.                    "insert_rowa

*插入后不清除内容，特别实用于题头和表头
FORM DEL_ROW USING VALUE(F_RANGE1) VALUE(F_RANGE2).
  DATA:S_RANGE(20) TYPE C.
  CONCATENATE F_RANGE1 F_RANGE2 INTO S_RANGE.
  CALL METHOD OF WSHEETOBJ 'range' = RANGEOBJ
    EXPORTING
      #1 = S_RANGE.
  CALL METHOD OF RANGEOBJ 'delete'.
ENDFORM.                    "del_row


DEFINE READ_DATA_FROM_LOCAL.
  CALL FUNCTION 'WS_UPLOAD'
      EXPORTING
*         CODEPAGE            = ' '
            FILENAME            = &1
            FILETYPE            = 'DAT'
*    IMPORTING
*         FILELENGTH          =
       TABLES
            DATA_TAB            = &2
       EXCEPTIONS
            CONVERSION_ERROR    = 1
            FILE_OPEN_ERROR     = 2
            FILE_READ_ERROR     = 3
            INVALID_TABLE_WIDTH = 4
            INVALID_TYPE        = 5
            NO_BATCH            = 6
            UNKNOWN_ERROR       = 7
            OTHERS              = 8.
  IF SY-SUBRC = 2.
    MESSAGE E005(ZYHY).
  ENDIF.
END-OF-DEFINITION.


*传票生成使用
DATA: SESSION,CTU,GROUP(12),CTUMODE,USER(12),CUPDATE,KEEP,
      E_GROUP(12),HOLDDATE LIKE SY-DATUM,NODATA,
      E_USER(12),E_KEEP,E_HDATE LIKE SY-DATUM,NODATE,SMALLLOG.

DATA:   BDCDATA LIKE BDCDATA    OCCURS 0 WITH HEADER LINE.
*       messages of call transaction
DATA:   MESSTAB LIKE BDCMSGCOLL OCCURS 0 WITH HEADER LINE.
*       error session opened (' ' or 'X')
DATA:   E_GROUP_OPENED.
*       message texts
TABLES: T100.



*&---------------------------------------------------------------------*
*&      Form  OPEN_GROUP
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM OPEN_GROUP.
  IF SESSION = 'X'.
    SKIP.
    WRITE: /(20) 'Create group'(I01), GROUP.
    SKIP.
*   open batchinput group
    CALL FUNCTION 'BDC_OPEN_GROUP'
      EXPORTING
        CLIENT   = SY-MANDT
        GROUP    = GROUP
        USER     = USER
        KEEP     = KEEP
        HOLDDATE = HOLDDATE.
    WRITE: /(30) 'BDC_OPEN_GROUP'(I02),
            (12) 'returncode:'(I05),
                 SY-SUBRC.
  ENDIF.
ENDFORM.                    "OPEN_GROUP

*----------------------------------------------------------------------*
*   end batchinput session                                             *
*   (call transaction using...: error session)                         *
*----------------------------------------------------------------------*
FORM CLOSE_GROUP.
  IF SESSION = 'X'.
*   close batchinput group
    CALL FUNCTION 'BDC_CLOSE_GROUP'.
    WRITE: /(30) 'BDC_CLOSE_GROUP'(I04),
            (12) 'returncode:'(I05),
                 SY-SUBRC.
  ELSE.
    IF E_GROUP_OPENED = 'X'.
      CALL FUNCTION 'BDC_CLOSE_GROUP'.
      WRITE: /.
      WRITE: /(30) 'Fehlermappe wurde erzeugt'(I06).
    ENDIF.
  ENDIF.
ENDFORM.                    "CLOSE_GROUP

*----------------------------------------------------------------------*
*        Start new transaction according to parameters                 *
*----------------------------------------------------------------------*
FORM BDC_TRANSACTION USING TCODE.
  DATA: L_MSTRING(480).
  DATA: L_SUBRC LIKE SY-SUBRC.
  CTUMODE = 'E' .
* batch input session
  IF SESSION = 'X'.
    CALL FUNCTION 'BDC_INSERT'
      EXPORTING
        TCODE     = TCODE
      TABLES
        DYNPROTAB = BDCDATA.
    IF SMALLLOG <> 'X'.
      WRITE: / 'BDC_INSERT'(I03),
               TCODE,
               'returncode:'(I05),
               SY-SUBRC,
               'RECORD:',
               SY-INDEX.
    ENDIF.
* call transaction using
  ELSE.
    REFRESH MESSTAB.
    CALL TRANSACTION TCODE USING BDCDATA
                     MODE   CTUMODE
                     UPDATE CUPDATE
                     MESSAGES INTO MESSTAB.
    L_SUBRC = SY-SUBRC.
    IF SMALLLOG <> 'X'.
      WRITE: / 'CALL_TRANSACTION',
               TCODE,
               'returncode:'(I05),
               L_SUBRC,
               'RECORD:',
               SY-INDEX.
      LOOP AT MESSTAB.
        SELECT SINGLE * FROM T100 WHERE SPRSL = MESSTAB-MSGSPRA
                                  AND   ARBGB = MESSTAB-MSGID
                                  AND   MSGNR = MESSTAB-MSGNR.
        IF SY-SUBRC = 0.
          L_MSTRING = T100-TEXT.
          IF L_MSTRING CS '&1'.
            REPLACE '&1' WITH MESSTAB-MSGV1 INTO L_MSTRING.
            REPLACE '&2' WITH MESSTAB-MSGV2 INTO L_MSTRING.
            REPLACE '&3' WITH MESSTAB-MSGV3 INTO L_MSTRING.
            REPLACE '&4' WITH MESSTAB-MSGV4 INTO L_MSTRING.
          ELSE.
            REPLACE '&' WITH MESSTAB-MSGV1 INTO L_MSTRING.
            REPLACE '&' WITH MESSTAB-MSGV2 INTO L_MSTRING.
            REPLACE '&' WITH MESSTAB-MSGV3 INTO L_MSTRING.
            REPLACE '&' WITH MESSTAB-MSGV4 INTO L_MSTRING.
          ENDIF.
          CONDENSE L_MSTRING.
          WRITE: / MESSTAB-MSGTYP, L_MSTRING(250).
        ELSE.
          WRITE: / MESSTAB.
        ENDIF.
      ENDLOOP.
      SKIP.
    ENDIF.
** Erzeugen fehlermappe ************************************************
    IF L_SUBRC <> 0 AND E_GROUP <> SPACE.
      IF E_GROUP_OPENED = ' '.
        CALL FUNCTION 'BDC_OPEN_GROUP'
          EXPORTING
            CLIENT   = SY-MANDT
            GROUP    = E_GROUP
            USER     = E_USER
            KEEP     = E_KEEP
            HOLDDATE = E_HDATE.
        E_GROUP_OPENED = 'X'.
      ENDIF.
      CALL FUNCTION 'BDC_INSERT'
        EXPORTING
          TCODE     = TCODE
        TABLES
          DYNPROTAB = BDCDATA.
    ENDIF.
  ENDIF.
  REFRESH BDCDATA.
ENDFORM.                    "BDC_TRANSACTION

*----------------------------------------------------------------------*
*        Start new screen                                              *
*----------------------------------------------------------------------*
FORM BDC_DYNPRO USING PROGRAM DYNPRO.
  CLEAR BDCDATA.
  BDCDATA-PROGRAM  = PROGRAM.
  BDCDATA-DYNPRO   = DYNPRO.
  BDCDATA-DYNBEGIN = 'X'.
  APPEND BDCDATA.
ENDFORM.                    "BDC_DYNPRO

*----------------------------------------------------------------------*
*        Insert field                                                  *
*----------------------------------------------------------------------*
FORM BDC_FIELD USING FNAM FVAL.
  IF FVAL <> NODATA.
    CLEAR BDCDATA.
    BDCDATA-FNAM = FNAM.
    BDCDATA-FVAL = FVAL.
    APPEND BDCDATA.
  ENDIF.
ENDFORM.                    "BDC_FIELD

在程序里引用此文件：

*-----------------------------------------------------------*
*导入包含文件                                                *
*-----------------------------------------------------------*
INCLUDE ZYQJH_INCLUDE."插入EXECL的

需要输出时调用代码：LINE1是自动生成序号，LINE2是输出每一行的定位。

  CREATE_EXCEL 'C:\ZCOE010.xls'.
  SORT ITAB BY BANFN BNFPO.
  DATA: LINE1 TYPE I ,
        LINE2 TYPE I ,
        STR1(10) TYPE C,
        STR2(10) TYPE C,
        STR3(10) TYPE C,
        STR4(10) TYPE C,
        STR5(10) TYPE C,
        STR6(10) TYPE C,
        STR7(10) TYPE C,
        STR8(10) TYPE C,
        STR9(10) TYPE C,
        STR10(10) TYPE C.


  LINE2 = 4.
  LINE1 = 1.

  LOOP AT ITAB.
    ITAB-ZLINE = LINE1.

    WRITE LINE2 TO STR1.
    CONDENSE STR1 NO-GAPS.
    CONCATENATE 'A' STR1 INTO STR1.
    WRITE LINE2 TO STR2 NO-GAP.
    CONDENSE STR2 NO-GAPS.
    CONCATENATE 'B' STR2 INTO STR2.
    WRITE LINE2 TO STR3 NO-GAP.
    CONDENSE STR3 NO-GAPS.
    CONCATENATE 'C' STR3 INTO STR3.
    WRITE LINE2 TO STR4 NO-GAP.
    CONDENSE STR4 NO-GAPS.
    CONCATENATE 'D' STR4 INTO STR4.
    WRITE LINE2 TO STR5 NO-GAP.
    CONDENSE STR5 NO-GAPS.
    CONCATENATE 'E' STR5 INTO STR5.
    WRITE LINE2 TO STR6 NO-GAP.
    CONDENSE STR6 NO-GAPS.
    CONCATENATE 'F' STR6 INTO STR6.
    WRITE LINE2 TO STR7 NO-GAP.
    CONDENSE STR7 NO-GAPS.
    CONCATENATE 'G' STR7 INTO STR7.
    WRITE LINE2 TO STR8 NO-GAP.
    CONDENSE STR8 NO-GAPS.
    CONCATENATE 'H' STR8 INTO STR8.
    WRITE LINE2 TO STR9 NO-GAP.
    CONDENSE STR9 NO-GAPS.
    CONCATENATE 'I' STR9 INTO STR9.
    WRITE LINE2 TO STR10 NO-GAP.
    CONDENSE STR10 NO-GAPS.
    CONCATENATE 'K' STR10 INTO STR10.

    PERFORM FILL_RANGE USING STR1
            ITAB-ZLINE.
    PERFORM FILL_RANGE USING STR2
            ITAB-DSNAM.
    PERFORM FILL_RANGE USING STR3
            ITAB-BANFN.
    PERFORM FILL_RANGE USING STR4
            ITAB-BNFPO.
    PERFORM FILL_RANGE USING STR5
            ITAB-TXZ01.
    PERFORM FILL_RANGE USING STR6
            ''.
    PERFORM FILL_RANGE USING STR7
            ITAB-MSEHL.
    PERFORM FILL_RANGE USING STR8
            ITAB-MENGE.
    PERFORM FILL_RANGE USING STR9
            ITAB-BADAT.
    PERFORM FILL_RANGE USING STR10
            ITAB-AFNAM.

    PERFORM INSERT_ROW USING 'AA'.

    LINE1 = LINE1 + 1.
    LINE2 = LINE2 + 1.
  ENDLOOP.