ABAP-OLE-EXCEL

本文主要对 通过EXCEL输出数据进行总结

---

---

不传模板示例

DATA: V_EXCEL TYPE OLE2_OBJECT,
      V_BOOK TYPE OLE2_OBJECT,
      V_CELL TYPE OLE2_OBJECT,
      V_RANGE TYPE OLE2_OBJECT,
      V_FONT TYPE OLE2_OBJECT,
      V_COLOR TYPE OLE2_OBJECT,
      V_COLUMN TYPE OLE2_OBJECT.

DATA: BEGIN OF LT_STOCK OCCURS 0,           "数据处理
  WERKS TYPE WERKS_D,
  NAME1 TYPE NAME1,
  LGORT TYPE LGORT_D,
  MATNR TYPE MATNR,
  MAKTX TYPE MAKTX,
  LABST TYPE LABST,
  END OF LT_STOCK.

DATA FULLPATH TYPE STRING.


PARAMETERS P_WERKS LIKE MARD-WERKS DEFAULT '1010'.          "界面

TYPE-POOLS OLE2.            "引用OLE类型池

FORM GET_DATA.                      "数据处理
  SELECT A~WERKS
         B~NAME1
         A~LGORT
         A~MATNR
         C~MAKTX
         A~LABST
    INTO TABLE LT_STOCK
    FROM MARD AS A
    INNER JOIN T001W AS B ON A~WERKS = B~WERKS
    INNER JOIN MAKT AS C ON A~MATNR = C~MATNR AND C~SPRAS = SY-LANGU
    WHERE A~WERKS = P_WERKS.
ENDFORM.                    " GET_DATA

FORM EXCEL.             "OLE控制EXCEL

    DEFINE FILL_CELL.
    CALL METHOD OF V_EXCEL 'CELLS' = V_CELL      "单元格位置
      EXPORTING
        #1 = &1
        #2 = &2.
    SET PROPERTY OF V_CELL 'value' = &3.        "单元格内容

    CALL METHOD OF V_CELL 'FONT' = V_FONT.
    SET PROPERTY OF V_FONT 'Bold' = &4.         "设置是否为粗体
    SET PROPERTY OF V_FONT 'size' = &5.         "设置字体大小
  END-OF-DEFINITION.
*创建EXCEL对象
  CREATE OBJECT V_EXCEL 'Excel.Application'.
  CALL METHOD OF V_EXCEL 'Workbooks' = V_BOOK.
  CALL METHOD OF V_BOOK 'ADD'.                    "新建一个EXCEL对象

  FILL_CELL 1 1 '当前商品可用库存状态' 1 18.        "填写首行标题
  SET PROPERTY OF V_CELL 'HorizontalAlignment' = -4108.         "设置标题文本居中
  SET PROPERTY OF V_FONT 'UNDERLINE' = 2.           "标题加下划线

*设置表头，表头统一为10号字体，加粗
  FILL_CELL 2 1 '地点' 1 10.
  FILL_CELL 2 2 '地点名称' 1 10.
  FILL_CELL 2 3 '库位' 1 10.
  FILL_CELL 2 4 '物料' 1 10.
  FILL_CELL 2 5 '物料名称' 1 10.
  FILL_CELL 2 6 '当前库存' 1 10.

*选中标题所在的单元格并合并
  CALL METHOD OF V_EXCEL 'Range' = V_RANGE
    EXPORTING
      #1 = 'A1'
      #2 = 'F1'.
  CALL METHOD OF V_RANGE 'select '.                 "RANGE 也为已经定义好的OLE2_OBJECT.
  SET PROPERTY OF V_RANGE 'MergeCells' = 1.         "合并单元格


  FIELD-SYMBOLS: <WA> TYPE ANY,
                 <FIELD> TYPE ANY.
  DATA LV_TXT(50) TYPE C.

  DATA: ROW_I TYPE I,
        COL_I TYPE I.

*从内表循环数据，按顺序填到单元格之中
  LOOP AT LT_STOCK ASSIGNING <WA>.
    ROW_I = SY-TABIX + 2.
    DO 6 TIMES.
      COL_I = SY-INDEX.
      ASSIGN COMPONENT SY-INDEX OF STRUCTURE <WA> TO <FIELD>.
      LV_TXT = <FIELD>.
      FILL_CELL ROW_I COL_I LV_TXT 0 10.

      IF COL_I = 6 AND LV_TXT <= 0.

* 当可用库存为0时，在EXCEL中将该单元格标识为黄色
        CALL METHOD OF V_CELL 'INTERIOR' = V_COLOR.
        SET PROPERTY OF V_COLOR 'ColorIndex' = 6.
      ENDIF.
    ENDDO.
  ENDLOOP.

*将EXCEL单元格宽度按实际文本长度来设置
  CALL METHOD OF V_EXCEL 'Columns' = V_COLUMN.
  CALL METHOD OF V_COLUMN 'Autofit'.

*设置EXCEL为可见
  SET PROPERTY OF V_EXCEL 'Visible' = 1.
ENDFORM.

传模板示例

过程：通过SMW0上传模板 -> 代码下载模板 -> OLE控制数据填充 -> 保存EXCEL

*模板下载
DATA:  lo_objdata LIKE wwwdatatab,
         lo_mime LIKE w3mime,
         lc_filename  TYPE string VALUE '哈哈哈哈哈' ,       "名称
         lc_fullpath  TYPE string   ,
         lc_path      TYPE  string  ,
         ls_destination LIKE rlgrap-filename,
         ls_objnam TYPE string,
         li_rc LIKE sy-subrc,
         ls_errtxt TYPE string.
  DATA:p_objid TYPE wwwdatatab-objid,
       p_dest LIKE sapb-sappfad.
  p_objid = 'ZTESTT'.         "模板名称
  CALL METHOD cl_gui_frontend_services=>file_save_dialog
    EXPORTING
      default_extension    = 'XLS'
      default_file_name    = lc_filename
    CHANGING
      filename             = lc_filename
      path                 = lc_path
      fullpath             = lc_fullpath
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4.
  IF lc_fullpath = ''.
    MESSAGE  '不能打开excel' TYPE 'E'.
  ENDIF.
  IF sy-subrc = 0.
    p_dest = lc_fullpath.
    concatenate p_objid '.XLS' into ls_objnam.
    CONDENSE ls_objnam NO-GAPS.
    SELECT SINGLE relid objid FROM wwwdata INTO CORRESPONDING FIELDS OF lo_objdata
           WHERE srtf2 = 0 AND relid = 'MI' AND objid = p_objid.

*检查表wwwdata中是否存在所指定的模板文件
    IF sy-subrc NE 0 OR lo_objdata-objid EQ space.
      CONCATENATE '模板文件' ls_objnam '不存在' INTO ls_errtxt.
      MESSAGE ls_errtxt TYPE 'I'.
    ENDIF.
    ls_destination = p_dest.

*如果存在，调用DOWNLOAD_WEB_OBJECT 函数下载模板到路径下
    CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'
      EXPORTING
        key         = lo_objdata
        destination = ls_destination
      IMPORTING
        rc          = li_rc.
    IF li_rc NE 0.
      CONCATENATE '模板文件：' ls_objnam '下载失败' INTO ls_errtxt.
      MESSAGE ls_errtxt TYPE 'E'.
    ENDIF.
    fname = ls_destination.
  ENDIF.

*对EXCEL进行数据处理
CREATE OBJECT excel 'EXCEL.APPLICATION'.  "Create EXCEL OBJECT
  IF sy-subrc NE 0.
    EXIT.
  ENDIF.
*  SET PROPERTY OF excel 'Visible' = 1.  "1/0 是否显示EXCEL

  CALL METHOD OF
      excel
      'Workbooks' = workbook.

  CALL METHOD OF
      workbook
      'Open'

    EXPORTING
      #1       = fname."打开上面下载路径下的excel文件

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

  CALL METHOD OF
      sheet
      'Select'.

  CALL METHOD OF sheet 'ACTIVATE'. "sheet 激活

  SET PROPERTY OF sheet 'NAME' = ''. "设定sheet名称

*此处假设内表itab 中已经存在需要写入excel中的数据
    
   data: row TYPE i,
          col TYPE i.
  LOOP AT it_alv INTO wa_alv.
      CALL METHOD OF                "将数据插入EXCEL对应的行 列
          excel
          'CELLS' = cell
        EXPORTING
          #1      = row
          #2      = col.
      SET PROPERTY OF cell 'VALUE' = wa_alv-zbnfpo.
  ENDLOOP.

*保存EXCEL数据
GET PROPERTY OF excel 'ActiveSheet' = sheet.
  FREE OBJECT sheet.
  FREE OBJECT workbook.

  GET PROPERTY OF excel 'ActiveWorkbook' = workbook.
  CALL METHOD OF
      workbook
      'SAVE'.

  SET PROPERTY OF excel 'Visible' = 0.  "是否显示EXCEL 此处显示不退出

* SET PROPERTY OF excel 'Visible' = 1.

  CALL METHOD OF workbook 'CLOSE'.
  CALL METHOD OF excel 'QUIT'. "注释部分为不显示直接退出

  FREE OBJECT sheet.
  FREE OBJECT workbook.
  FREE OBJECT excel.

相关类型池/对象/函数/数据库表

• 类型池：OLE2
• 对象：OLE2_OBJECT
• 对象：WWWDATATAB
• 对象：W3MIME
• 对象：SAPD-SAPPFAD "存档链接文件路径
• 函数：GUI_FILE_SAVE_DIALOG -> 对话框维护文件路径
• 函数：GUI_DOWNLOAD -> 将内表下载到PC端
• 函数：DOWNLOAD_WEB_OBJECT -> 下载一个WEB模板
• 数据库表：OLELOAD -> OLE类型信息加载
• 数据库表：WWWDATA -> 存储WWW对象的INDX类型表

或可直接使用函数读取

TEXT_CONVERT_XLS_TO_SAP 此函数弊端为只可识别Office的Excel
ALSM_EXCEL_TO_INTERNAL_TABLE 该函数支持WPS的识别，但每个单元格最大限长为50字符，行数最多为9999，如有特殊需要可自行修改该函数