SAP ABAP OLE的基本合集

最新推荐文章于 2024-11-04 14:52:45 发布

ABAP 成

最新推荐文章于 2024-11-04 14:52:45 发布

阅读量2.2k

点赞数 3

分类专栏： ABAP 文章标签： excel

本文链接：https://blog.csdn.net/qq_48677456/article/details/129259939

版权

ABAP 专栏收录该内容

31 篇文章

订阅专栏

文章描述了在SAP系统中使用OLE技术进行Office集成，特别是与Excel交互时遇到的两个主要问题：一是VBA语法在ABAP中的使用不友好；二是数据写入Excel速度慢。示例代码展示了如何创建并设置Excel工作表的内容，包括合并单元格、设置样式等，但同时也反映了性能瓶颈。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

早期 SAP 用于解决 Office 集成，使用的是 OLE 技术。 OLE 有两个比较大的缺点，一是语法参照 VBA，在 ABAP 中使用各种 VBA 的方法 (method)，语法的友好性差；二是数据写入到 Excel 中，速度特别慢，慢得难以接受。但是面对定制化需求不得不用这个方法

*&---------------------------------------------------------------------*
*& Report ZQC_NEW54
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zqc_new54.


*INCLUDE ole2incl .


DATA: gs_excel          TYPE ole2_object,
      gs_wbooklist      TYPE ole2_object,
      gs_application    TYPE ole2_object,
      gs_wbook          TYPE ole2_object,
      gs_activesheet    TYPE ole2_object,
      gs_sheets         TYPE ole2_object,
      gs_newsheet       TYPE ole2_object,
      gs_cell1          TYPE ole2_object,
      gs_cell2          TYPE ole2_object,
      gs_cells          TYPE ole2_object,
      gs_range          TYPE ole2_object,
      gs_font           TYPE ole2_object,
      gs_interior       TYPE ole2_object,
      gs_columns        TYPE ole2_object,
      gs_charts         TYPE ole2_object,
      gs_chart          TYPE ole2_object,
      gs_charttitle     TYPE ole2_object,
      gs_charttitlechar TYPE ole2_object,
      gs_chartobjects   TYPE ole2_object.


DATA gv_sheet_name(20) TYPE c .
DATA gv_outer_index LIKE sy-index .
DATA gv_intex(2) TYPE c .
DATA gv_line_cntr TYPE i . "line counter
DATA gv_linno TYPE i . "line number
DATA gv_colno TYPE i . "column number
DATA gv_value TYPE i . "data


CONCATENATE '分页签' gv_intex INTO gv_sheet_name .       "分页签
CREATE OBJECT gs_excel 'EXCEL.APPLICATION' .             " 创建 XLSX
SET PROPERTY OF gs_excel 'Visible' = 1 .                 "1前台运行。为0时表示为后台运行。
GET PROPERTY OF gs_excel 'Workbooks' = gs_wbooklist .
GET PROPERTY OF gs_wbooklist 'Application' = gs_application .
SET PROPERTY OF gs_application 'SheetsInNewWorkbook' = 2 ."设置 Microsoft Excel 软件打开时，自动插入到新工作簿中的工作表数目（即初始sheet数目，默认名字依次为 Sheet1、Sheet2.....）
CALL METHOD OF gs_wbooklist 'Add'        = gs_wbook.      "创建 workbook.
GET PROPERTY OF gs_application 'ActiveSheet' = gs_activesheet .
SET PROPERTY OF gs_activesheet 'Name' = gv_sheet_name .

gv_line_cntr = 1 . "line counter

" 以第一行第一列开始 到 第二行第九列合并
CALL METHOD OF
    gs_excel
    'Cells'  = gs_cell1
  EXPORTING
    #1       = 1
    #2       = 1.

CALL METHOD OF
    gs_excel
    'Cells'  = gs_cell2
  EXPORTING
    #1       = 2
    #2       = 9.

CALL METHOD OF
    gs_excel
    'Range'  = gs_cells
  EXPORTING
    #1       = gs_cell1
    #2       = gs_cell2.

CALL METHOD OF gs_cells 'Select' .  "选中
CALL METHOD OF gs_cells 'Merge' .  "合并


CALL METHOD OF gs_excel 'Cells' = gs_cell1        "选中行列
  EXPORTING
  #1 =  gv_line_cntr
  #2 = 1.
SET PROPERTY OF gs_cell1 'Value' = '设置抬头' .   "设置值


GET PROPERTY OF gs_cell1 'Font' = gs_font .               "单元格文字
SET PROPERTY OF gs_font 'Underline' = 2 .                 "字体添加下划线
SET PROPERTY OF gs_font 'Bold' = 1 .                      "字体加粗
SET PROPERTY OF gs_font 'SIZE' = 16.                      "字体大小
SET PROPERTY OF gs_cell1 'HorizontalAlignment' = -4108 .  "字体默认居中
GET PROPERTY OF gs_cell1 'Interior' = gs_interior .       "单元格属性
SET PROPERTY OF gs_interior 'ColorIndex' = 8 .            "单元格底色
*    SET PROPERTY OF GS_INTERIOR 'Pattern' = -4124 .          "单元格斑马网格

CALL METHOD OF
    gs_excel
    'Cells'  = gs_cell1
  EXPORTING
    #1       = 3
    #2       = 1.

CALL METHOD OF
    gs_excel
    'Cells'  = gs_cell2
  EXPORTING
    #1       = 3
    #2       = 2.

CALL METHOD OF
    gs_excel
    'Range'  = gs_cells
  EXPORTING
    #1       = gs_cell1
    #2       = gs_cell2.

CALL METHOD OF gs_cells 'Select' .  "选中
CALL METHOD OF gs_cells 'Merge' .  "合并
SET PROPERTY OF gs_cell1 'Value' = '物料凭证' .  "设置值
SET PROPERTY OF gs_cell1 'HorizontalAlignment' = -4108 .  "字体默认居中

CALL METHOD OF gs_excel 'Cells' = gs_cell1   "第3行第2列
  EXPORTING
  #1 = 3
  #2 = 3.
SET PROPERTY OF gs_cell1 'Value' = '凭证年度' .  "设置值
SET PROPERTY OF gs_cell1 'HorizontalAlignment' = -4108 .  "字体默认居中

CALL METHOD OF gs_excel 'Cells' = gs_cell1   "第3行第3列
  EXPORTING
  #1 = 3
  #2 = 4.
SET PROPERTY OF gs_cell1 'Value' = '凭证项目' .  "设置值
SET PROPERTY OF gs_cell1 'HorizontalAlignment' = -4108 .  "字体默认居中



CALL METHOD OF
    gs_excel
    'Cells'  = gs_cell1
  EXPORTING
    #1       = 3
    #2       = 5.

CALL METHOD OF
    gs_excel
    'Cells'  = gs_cell2
  EXPORTING
    #1       = 3
    #2       = 7.

CALL METHOD OF
    gs_excel
    'Range'  = gs_cells
  EXPORTING
    #1       = gs_cell1
    #2       = gs_cell2.

CALL METHOD OF gs_cells 'Select' .  "选中
CALL METHOD OF gs_cells 'Merge' .  "合并
SET PROPERTY OF gs_cell1 'Value' = '物料' .  "设置值
SET PROPERTY OF gs_cell1 'HorizontalAlignment' = -4108 .  "字体默认居中



CALL METHOD OF gs_excel 'Cells' = gs_cell1   "第3行第5列
   EXPORTING
   #1 = 3
   #2 = 8.
SET PROPERTY OF gs_cell1 'Value' = '移动类型' .  "设置值
SET PROPERTY OF gs_cell1 'HorizontalAlignment' = -4108 .  "字体默认居中

CALL METHOD OF gs_excel 'Cells' = gs_cell1   "第3行第6列
   EXPORTING
   #1 = 3
   #2 = 9.
SET PROPERTY OF gs_cell1 'Value' = '工厂' .  "设置值
SET PROPERTY OF gs_cell1 'HorizontalAlignment' = -4108 .  "字体默认居中


CALL METHOD OF                  ""指定操作的单元格范围
    gs_excel
    'Cells'  = gs_cell1
  EXPORTING
    #1       = 3
    #2       = 1.
CALL METHOD OF
    gs_excel
    'Cells'  = gs_cell2
  EXPORTING
    #1       = 3
    #2       = 9.
CALL METHOD OF
    gs_excel
    'Range'  = gs_cells
  EXPORTING
    #1       = gs_cell1
    #2       = gs_cell2.

CALL METHOD OF gs_cells 'Select' .   "选中
GET PROPERTY OF gs_cells 'Font' = gs_font . "设置字体
SET PROPERTY OF gs_font 'Bold' = 1 .  "加粗

CALL METHOD OF
    gs_excel
    'Cells'  = gs_cell1
  EXPORTING
    #1       = 3
    #2       = 1.
CALL METHOD OF
    gs_excel
    'Cells'  = gs_cell2
  EXPORTING
    #1       = 3
    #2       = 9.
CALL METHOD OF
    gs_excel
    'Range'  = gs_cells
  EXPORTING
    #1       = gs_cell1
    #2       = gs_cell2.
CALL METHOD OF
  gs_cells
  'Select'.
CALL METHOD OF gs_cells 'BorderAround' " 边框
  EXPORTING
    #1 = 1  "连续点
    #2 = 2. "边框粗细





SELECT mblnr,mjahr,zeile,bwart,matnr,werks,lgort,charg,insmk
   FROM mseg
   UP TO 20 ROWS
   INTO TABLE @DATA(gt_out)
  WHERE xauto <> 'X'.



DATA:lv_index TYPE i VALUE '3'.
DATA:lv_sum TYPE i .
LOOP AT gt_out INTO DATA(gs_out).
  lv_sum = lv_index + sy-tabix.

  CALL METHOD OF
    gs_excel
    'Cells'  = gs_cell1
  EXPORTING
    #1       = lv_sum
    #2       = 1.

  CALL METHOD OF
      gs_excel
      'Cells'  = gs_cell2
    EXPORTING
      #1       = lv_sum
      #2       = 2.

  CALL METHOD OF
      gs_excel
      'Range'  = gs_cells
    EXPORTING
      #1       = gs_cell1
      #2       = gs_cell2.

  CALL METHOD OF gs_cells 'Select' .  "选中
  CALL METHOD OF gs_cells 'Merge' .  "合并
  SET PROPERTY OF gs_cell1 'Value' = gs_out-mblnr .  "设置值
  SET PROPERTY OF gs_cell1 'HorizontalAlignment' = -4108 .  "字体默认居中


  CALL METHOD OF gs_excel 'Cells' = gs_cell1   "第3行第2列
    EXPORTING
    #1 = lv_sum
    #2 = 3.
  SET PROPERTY OF gs_cell1 'Value' = gs_out-mjahr .  "设置值
  SET PROPERTY OF gs_cell1 'HorizontalAlignment' = -4108 .  "字体默认居中


  CALL METHOD OF gs_excel 'Cells' = gs_cell1   "第3行第3列
    EXPORTING
    #1 = lv_sum
    #2 = 4.
  SET PROPERTY OF gs_cell1 'Value' = gs_out-zeile .  "设置值
  SET PROPERTY OF gs_cell1 'HorizontalAlignment' = -4108 .  "字体默认居中



  CALL METHOD OF
      gs_excel
      'Cells'  = gs_cell1
    EXPORTING
      #1       = lv_sum
      #2       = 5.

  CALL METHOD OF
      gs_excel
      'Cells'  = gs_cell2
    EXPORTING
      #1       = lv_sum
      #2       = 7.

  CALL METHOD OF
      gs_excel
      'Range'  = gs_cells
    EXPORTING
      #1       = gs_cell1
      #2       = gs_cell2.

  CALL METHOD OF gs_cells 'Select' .  "选中
  CALL METHOD OF gs_cells 'Merge' .  "合并
  SET PROPERTY OF gs_cell1 'Value' = gs_out-matnr .  "设置值
  SET PROPERTY OF gs_cell1 'HorizontalAlignment' = -4108 .  "字体默认居中



  CALL METHOD OF gs_excel 'Cells' = gs_cell1   "第3行第5列
     EXPORTING
     #1 = lv_sum
     #2 = 8.
  SET PROPERTY OF gs_cell1 'Value' = gs_out-werks .  "设置值
  SET PROPERTY OF gs_cell1 'HorizontalAlignment' = -4108 .  "字体默认居中

  CALL METHOD OF gs_excel 'Cells' = gs_cell1   "第3行第6列
     EXPORTING
     #1 = lv_sum
     #2 = 9.
  SET PROPERTY OF gs_cell1 'Value' = gs_out-lgort .  "设置值
  SET PROPERTY OF gs_cell1 'HorizontalAlignment' = -4108 .  "字体默认居中
ENDLOOP.

CALL METHOD  OF  gs_excel  'Worksheets' = gs_cells EXPORTING #1 = 'sheet2'. "跳转二页更改页签名字
CALL METHOD  OF  gs_cells  'Activate'.
SET PROPERTY OF  gs_cells 'Name' = '分页签二' .

CALL METHOD OF gs_application 'Worksheets' = gs_sheets
    EXPORTING
      #1 = '分页签二'.  "这里sheet2为要操作的sheet的名字。

  CALL METHOD OF gs_excel 'Cells' = gs_cell1
    EXPORTING
    #1 = 1
    #2 = 1.
  SET PROPERTY OF gs_cell1 'Value' = '111111111111111' .  "设置值
  SET PROPERTY OF gs_cell1 'HorizontalAlignment' = -4108 .  "字体默认居中
  CALL METHOD OF gs_excel 'COLUMNS' = gs_columns
  EXPORTING
  #1 = 1. "the column number
  "自定义18位数字后的后三位变成了零我也很蒙     "防止变成科学计数法
  SET PROPERTY OF gs_columns 'ColumnWidth' = 50.    "宽度
  SET PROPERTY OF gs_columns 'NumberFormat' = '000000000000000'.

" 释放资源
FREE: gs_excel, gs_wbooklist, gs_application, gs_wbook,
gs_activesheet,gs_sheets, gs_newsheet, gs_cell1,
gs_cell2, gs_cells, gs_range, gs_font, gs_interior,
gs_columns, gs_charts, gs_chart, gs_charttitle,
gs_charttitlechar, gs_chartobjects.