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.