如何在ABAP中调用OFFICE .XLS与.TXT格式

ABAP中调用EXCEL，也会有几种形式

（1）新建一张空白的EXCEL的表格：

*INCLUDE OLE2INCL.
*
*tables:
*  kna1.
*
*DATA:
*  APPLICATION TYPE OLE2_OBJECT,
*  WORKBOOK TYPE OLE2_OBJECT,
*  SHEET TYPE OLE2_OBJECT,
*  CELLS TYPE OLE2_OBJECT,
*
*  begin of inner_tab occurs 0,
*    index type i,
*    kunnr like kna1-kunnr,
*  end of inner_tab.
*
*CREATE OBJECT APPLICATION 'Excel.application' .
*SET PROPERTY OF APPLICATION 'Visible' = 1.
** 宣告一EXCEL工作底槁档(WORKBOOK)
*CALL METHOD OF APPLICATION 'Workbooks' = WORKBOOK.
** 增加一新的工作表(SHEET)，编号是 1 号
*CALL METHOD OF WORKBOOK 'Add'.
*CALL METHOD OF APPLICATION 'Worksheets' = SHEET
*  EXPORTING #1 = 1.
** 设定此工作表开启使用
*CALL METHOD OF SHEET 'Activate'.
*PERFORM FILL_SHEET1.
*FREE APPLICATION.
**----------------------------------------------------------------------------------------------------------------
*FORM FILL_SHEET.
*  DATA:
*    ROW_MAX TYPE I VALUE 256,
*    ROWS TYPE I VALUE 1,
*    INDEX TYPE I.
*  FIELD-SYMBOLS: <NAME>.
*  SELECT * FROM KNA1.
*    ROWS = ROWS + 1. "至第ROWS列
*    INDEX = ROW_MAX * ( ROWS - 1 ) + 1.
*    DO 10 TIMES.
*      "如要每一列放入10个存格的数据
*ASSIGN COMPONENT SY-INDEX OF STRUCTURE KNA1
*TO <NAME>.
*      CALL METHOD OF SHEET 'Cells' = CELLS
*        EXPORTING #1 = INDEX.
*      SET PROPERTY OF CELLS 'Value' = <NAME>.
*      ADD 1 TO INDEX.
*    ENDDO.
*  ENDSELECT.
*endform.
*
*FORM FILL_SHEET1.
*  DATA:
*    index TYPE I value 1.
*  SELECT kunnr FROM KNA1
*    into corresponding fields of table inner_tab
*    where kunnr like 'A1%'.
*  loop at inner_tab.
*    inner_tab-index = sy-index.
*    modify inner_tab.
*      CALL METHOD OF SHEET 'Cells' = CELLS
*        EXPORTING #1 = inner_tab-index.
*      SET PROPERTY OF CELLS 'Value' = inner_tab-kunnr.
*      ADD 1 TO index.
*  ENDloop.
*endform.
 

（2）打开本地的一张EXCEL表格，并写数值到这张表格的指定位置。

INCLUDE ole2incl.
DATA: excel TYPE OLE2_OBJECT,
      workbook TYPE ole2_object,    " Workbook object
       book TYPE ole2_object,
      sheet TYPE ole2_object,   " Worksheet object
      range TYPE ole2_object,     " Range object
      cell TYPE ole2_object.      " Cell object

DATA: sheet_name(20) VALUE 'BS'.  "Excel文件里的sheet name
DATA local_file LIKE rcgfiletr-ftfront."下载文件类型
local_file = 'c:/DO_TEST_OLE.xls'.

START-OF-SELECTION.

  DATA: c1 TYPE i,
          c2 TYPE i,
          c3 TYPE i,
          c4 TYPE i.

  CREATE OBJECT excel 'EXCEL.APPLICATION'.         "CREATE EXCEL OBJECT
  IF sy-subrc NE 0.
    EXIT.
  ENDIF.
  SET PROPERTY OF excel 'Visible' = 1.             "是否显示EXCEL
  CALL METHOD OF excel 'Workbooks' = workbook.
  DATA h TYPE i.

   CALL METHOD OF workbook 'Open'
    EXPORTING
    #1 = local_file.

  CALL METHOD OF excel 'Sheets' = sheet
    EXPORTING
    #1 = 1.

  CALL METHOD OF sheet 'SELECT' .

*填充抬头
  PERFORM fill_range USING 1 3 'F_aaaaaa'.          "第一行第三列
  PERFORM fill_range USING 1 4 'F_cccccccccc'.      "第一行第四列

  GET PROPERTY OF excel 'ActiveWorkbook' = sheet.
*  CALL METHOD OF sheet 'printout'.                  "打印

*  CALL METHOD OF excel 'SAVE'.                      "保存
*  CALL METHOD OF excel 'Quit'.                      "退出

  FREE OBJECT cell.
  FREE OBJECT range.
  FREE OBJECT sheet.
  FREE OBJECT workbook.
  FREE OBJECT excel.

*---------------------------------------------------------------------*
FORM fill_range USING value(f_row)
                      value(f_col)
                      value(f_value).

  CALL METHOD OF excel 'CELLS' = cell
    EXPORTING
    #1 = f_row
    #2 = f_col.
  SET PROPERTY OF cell 'VALUE' = f_value.
ENDFORM.

（3）将TXT格式的文本读取并显示（参考VIVIAN的SAP技术空间）：

t-code: file 逻辑路径，逻辑文件名新建

t-code: al11  SAP directory 看文件内容

function: 'file_get_name'  逻辑文件名转换成物理文件名

 

*写文件

data:  fname type char256.

fname = './2.TXT'.                      "物理文件名

*文本

data: text1 type c value 'X'.

open dataset fname for output in text mode encoding default.     "不说明用那种mode 默认为in binary mode

                                                                                       "不用encoding

  if sy-subrc = 0.                                                                 " if sy-subrc = 8说明文件没有打开

*好像一个transfer在文件里就是一行

*如果要在文件里定义显示格式，感觉还挺麻烦的，一行的文本用concatenate 或者用OFFSET定义

*感觉这些方法挺笨的，不知道有没有好的方法

      transfer text1 into fname.                                                          

  endif.

close dataset fname.

 

*表

types: begin of gtyp_itab,
           v1 type c,
           v2 type c,
        end of gtyp_itab.
data: gt_itab type standard table of gtyp_itab.
data: gw_itab type gtyp_itab.

gw_itab-v1 = 'a'.
gw_itab-v2 = 'b'.
append gw_itab to gt_itab.

clear gw_itab.

 

open dataset fname for output in text mode encoding default.
  if sy-subrc = 0.
    clear gw_itab.
    loop at gt_itab into gw_itab.
        transfer gw_itab to fname.     "文件里显示的是完全连在一起的文本，真难看！！

        clear gw_itab.
    endloop.
  endif.
close dataset fname.

 

*读文件

data: text01 type c.

open dataset fname for input in text mode encoding default.
  if sy-subrc = 0.
      read dataset fname into text1.
      write: text1.         "显示在屏幕上
  endif.

close dataset fname.

 

（4）内表转成TXT文件

在一个internal table中放了几行数据，想把这些数据转变成一个TXT文件，每一条记录占一行。

 

 data: filename(128) value 'd:/sapdownload.txt'."保存文件名
CALL FUNCTION 'WS_DOWNLOAD'
EXPORTING
*   BIN_FILESIZE                  = ' '
   CODEPAGE                      = '8400'
   FILENAME                      = filename
   FILETYPE                      = 'DAT'
  TABLES
    DATA_TAB                     = itab.
if sy-subrc = 0.
  message s010(zbarbug) with filename.
elseif sy-subrc <> 0.
message E000(zbarbug) with filename.
endif.