ABAP 下载EXCEL DOI使用OLE功能（DOI获取OLE对象）

狗尾巴草_lan

已于 2023-07-24 09:46:55 修改

阅读量628

点赞数

分类专栏： ABAP 开发模块文章标签： sap abap excel Powered by 金山文档

于 2023-02-17 10:02:25 首次发布

本文链接：https://blog.csdn.net/royhao/article/details/129042340

版权

ABAP 开发模块专栏收录该内容

8 篇文章 2 订阅

订阅专栏

SAP下载Excel可通过OLE技术实现，但由于OLE的下载速度较慢，对于数据量大的时候影响用户体验，所以最新使用DOI技术。

DOI主要使用了接口I_OI_DOCUMENT_PROXY和I_OI_SPREADSHEET，但由于SAP封装的类方法有限，有些特殊要求无法满足，好在DOI底层其实也是使用了OLE对象，所以我们可以获取OLE对象实现SAP未封装的功能，譬如复制sheet、设置列宽等等。

DOI获取OLE对象可调用接口I_OI_DOCUMENT_PROXY的方法GET_DOCUMENT_HANDLE得到HANDLE-OBJ。然后获取HANDLE-OBJ的Application属性即为Office应用对象，获取Office应用对象的ActiveWorkbook属性即为Excel对象。

此方法修改Excel对象之后保存文件到本地电脑，虽然调用了I_OI_DOCUMENT_PROXY的方法CLOSE_DOCUMENT和 RELEASE_DOCUMENT，但是文件依然会是打开状态。对于此问题我找到一个解决办法是先将文件数据保存至内表然后再重新打开，如有其它方法欢迎评论区留言指教。

获取Excel对象核心代码如下：

  CALL METHOD gr_document->get_document_handle
    IMPORTING
      error  = lr_error
      handle = lr_handle.
  CALL METHOD lr_error->raise_message
    EXPORTING
      type = 'E'.

  GET PROPERTY OF lr_handle-obj 'Application' = lr_excel.
  GET PROPERTY OF lr_excel 'ActiveWorkbook' = lr_workbook.

重新打开文件核心代码如下：

* 重新打开文件防止保存后还有缓存文件
  CALL METHOD gr_document->save_document_to_table
    EXPORTING
      no_flush       = 'X'
    IMPORTING
      error          = lr_error
    CHANGING
      document_size  = lv_size
      document_table = lt_table.
  CALL METHOD lr_error->raise_message
    EXPORTING
      type = 'E'.

  CALL METHOD gr_document->close_document
    IMPORTING
      error = lr_error.
  CALL METHOD lr_error->raise_message
    EXPORTING
      type = 'E'.

  CALL METHOD gr_document->release_document
    IMPORTING
      error = lr_error.
  CALL METHOD lr_error->raise_message
    EXPORTING
      type = 'E'.

  CALL METHOD gr_document->open_document_from_table
    EXPORTING
      document_size  = lv_size
      document_table = lt_table
      open_inplace   = 'X'
    IMPORTING
      error          = lr_error.
  CALL METHOD lr_error->raise_message
    EXPORTING
      type = 'E'.

  CALL METHOD gr_document->get_spreadsheet_interface
    EXPORTING
      no_flush        = ''
    IMPORTING
      sheet_interface = gr_spreadsheet
      error           = lr_error.
  CALL METHOD lr_error->raise_message
    EXPORTING
      type = 'E'.

示例功能：复制模板Sheet并根据需求复制对应列

*&---------------------------------------------------------------------*
*& Form frm_add_sheet
*&---------------------------------------------------------------------*
*& 增加Sheet
*&---------------------------------------------------------------------*
*& --> IV_KTEXT_COL          科室列数
*& <-- CV_NAME               Sheet名
*&---------------------------------------------------------------------*
FORM frm_add_sheet USING iv_ktext_col TYPE i
                CHANGING cv_name      TYPE char50.
  DATA: lr_error    TYPE REF TO i_oi_error,
        lr_handle   TYPE cntl_handle,
        lr_excel    TYPE ole2_object,
        lr_workbook TYPE ole2_object,
        lr_sheet    TYPE ole2_object,
        lr_range    TYPE ole2_object，
        lt_table    TYPE sbdst_content,
        lv_size     TYPE i.

  CALL METHOD gr_document->get_document_handle
    IMPORTING
      error  = lr_error
      handle = lr_handle.
  CALL METHOD lr_error->raise_message
    EXPORTING
      type = 'E'.

  GET PROPERTY OF lr_handle-obj 'Application' = lr_excel.
  GET PROPERTY OF lr_excel 'ActiveWorkbook' = lr_workbook.

  IF cv_name = TEXT-t02.
*   重命名汇总Sheet
    IF g_first_day = g_last_day.
      cv_name = g_first_day.
    ELSE.
      cv_name = g_first_day && '-' && g_last_day.
    ENDIF.

    CALL METHOD OF lr_workbook 'Sheets' = lr_sheet
      EXPORTING
        #1 = 1.
    SET PROPERTY OF lr_sheet 'Name' = cv_name.
  ELSE.
*   复制模板Sheet
    CALL METHOD OF lr_workbook 'Sheets' = lr_sheet
      EXPORTING
        #1 = 1.
    CALL METHOD OF lr_sheet 'Copy'
      EXPORTING
        #1 = lr_sheet.
    SET PROPERTY OF lr_sheet 'Name' = cv_name.
  ENDIF.
  CALL METHOD OF lr_sheet 'Select'.

* 科室分列导出
  IF gv_expand IS NOT INITIAL.
*   新增科室列
    DO iv_ktext_col - 1 TIMES.
*     复制科室列
      CALL METHOD OF lr_excel 'Columns' = lr_range
        EXPORTING
          #1 = 6.
      CALL METHOD OF lr_range 'Select'.
      CALL METHOD OF lr_range 'Copy'.
      CALL METHOD OF lr_range 'Insert'.
    ENDDO.
  ENDIF.

* 重新打开文件防止保存后还有缓存文件
  CALL METHOD gr_document->save_document_to_table
    EXPORTING
      no_flush       = 'X'
    IMPORTING
      error          = lr_error
    CHANGING
      document_size  = lv_size
      document_table = lt_table.
  CALL METHOD lr_error->raise_message
    EXPORTING
      type = 'E'.

  CALL METHOD gr_document->close_document
    IMPORTING
      error = lr_error.
  CALL METHOD lr_error->raise_message
    EXPORTING
      type = 'E'.

  CALL METHOD gr_document->release_document
    IMPORTING
      error = lr_error.
  CALL METHOD lr_error->raise_message
    EXPORTING
      type = 'E'.

  CALL METHOD gr_document->open_document_from_table
    EXPORTING
      document_size  = lv_size
      document_table = lt_table
      open_inplace   = 'X'
    IMPORTING
      error          = lr_error.
  CALL METHOD lr_error->raise_message
    EXPORTING
      type = 'E'.

  CALL METHOD gr_document->get_spreadsheet_interface
    EXPORTING
      no_flush        = ''
    IMPORTING
      sheet_interface = gr_spreadsheet
      error           = lr_error.
  CALL METHOD lr_error->raise_message
    EXPORTING
      type = 'E'.

  FREE lr_handle.
  FREE lr_excel.
  FREE lr_workbook.

ENDFORM.