ABAP操作EXCEL
1、EXCEL导入内表
方式1:
调用function,’F4_FILENAME’获取文件名。通过屏幕元素Prameter的帮助事件，弹出文件选择框，获取选择文件名。
调用function,’TEXT_CONVERT_XLS_TO_SAP’，将选择excel数据放入内表。
示例：
TYPE-POOLS truxs. DATA:it_raw TYPE truxs_t_text_data. "定义一个内表来存储数据，内表的列数和要传得数据的列数要相同，其按照列来匹配传值 DATA: BEGIN OF gt_data OCCURS 0, col1 TYPE char10, col2 TYPE char10, END OF gt_data. PARAMETERS:p_file TYPE rlgrap-filename. "调用F4_FILENAME，点击输入框后小方块弹出文件选择框 AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file. CLEAR p_file. CALL FUNCTION \'F4_FILENAME\' IMPORTING file_name = p_file."返回文件名 *Excel传值 START-OF-SELECTION. PERFORM exceltotab. FORM exceltotab . CALL FUNCTION \'TEXT_CONVERT_XLS_TO_SAP\' EXPORTING * I_FIELD_SEPERATOR = \' \' "分隔符？ i_line_header = \'X\' "是否去掉首行 i_tab_raw_data = it_raw "WORK TABLE i_filename = p_file TABLES i_tab_converted_data = gt_data[] "ACTUAL DATA EXCEPTIONS conversion_failed = 1 OTHERS = 2. IF sy-subrc <> 0. MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. ENDIF. ENDFORM. "exceltotab
方式2：
直接弹出文件选择弹窗，获取文件路径：
data: ts_files type filetable, rc type i. refresh ts_files. DATA:filename(1024) type C. "调用方法，弹出文件选择框，获取选中文件路径 call method cl_gui_frontend_services=>file_open_dialog exporting window_title = \'Select File\' default_extension = \'*.xls\' multiselection = \' \' " Only one file changing file_table = ts_files rc = rc exceptions file_open_dialog_failed = 1 cntl_error = 2 error_no_gui = 3 not_supported_by_gui = 4 others = 5. read table ts_files into filename index 1.
调用function ‘ALSM_EXCEL_TO_INTERNAL_TABLE’,将excel表内容读入ABAP内表。
"将excel读取到内表\'ALSM_EXCEL_TO_INTERNAL_TABLE\' DATA :excelTab like alsmex_tabline OCCURS 10 WITH HEADER LINE. Data :fileTest like rlgrap-filename VALUE \'D:\myexcel.xlsx\'. call function \'ALSM_EXCEL_TO_INTERNAL_TABLE\' exporting filename = fileTest i_begin_col = 1 i_begin_row = 1 i_end_col = 20 i_end_row = 20000 tables intern = excelTab exceptions inconsistent_parameters = 1 upload_ole = 2 others = 3.
自定义function：‘ALSM_EXCEL_TO_INTERNAL_TABLE’
Structure:ALSMEX_TABLINE,其中VALUE字段类型为CHAR50,如果上传字段长度超过CHAR50,那么上传数据就会不全，所以我们需要自定义Function,修改ALSMEX_TABLINE的VALUE字段。
自定义步骤：
1.复制Sturcture:ALSMEX_TABLINE,自定义名字TEST_ALSMEX_TABLINE修改VALUE的数据类型，定义为需求长度；
2.复制function:ALSM_EXCEL_TO_INTERNAL_TABLE,复制之前先创建目标Function group，不然会复制失败；
3.在新建Function Group创建include: LZTEST_EXCELF01,将ALSMEX下，includes:LALSMEXF01内容复制到新建include。
4.将ALSMEX下，includes:LALSMEXTOP内容复制到新建Function Group的includes:LZTEST_EXCELTOP中，然后检查，激活。
示例：
* value of excel-cell TYPES: ty_d_itabvalue TYPE alsmex_tabline-value, * internal table containing the excel data ty_t_itab TYPE alsmex_tabline OCCURS 0,
5.修改TEST_EXCEL_TO_INTERNAL_TABLE，将INTERN参数对应数据类型修改为自定义的TEST_ALSMEX_TABLINE,将LZTEST_EXCELTOP中的ty_d_itabvalue和ty_t_itab对应的数据类型也修改为自定义类型。
6.程序中调用function即可。
示例：
"将excel读取到内表\'ALSM_EXCEL_TO_INTERNAL_TABLE\' DATA :exceltab like alsmex_tabline OCCURS 10 WITH HEADER LINE. Data :filetest like rlgrap-filename VALUE \'D:test.xlsx\'. * CALL FUNCTION \'ALSM_EXCEL_TO_INTERNAL_TABLE\' CALL FUNCTION \'TEST_EXCEL_TO_INTERNAL_TABLE\' EXPORTING filename = fileTest i_begin_col = 1 i_begin_row = 1 i_end_col = 20 i_end_row = 20000 TABLES intern = exceltab EXCEPTIONS inconsistent_parameters = 1 upload_ole = 2 others = 3.
2、OLE2操作EXCEL
使用ABAP程序操作EXCEL，将内表数据写出到EXCEL
OLE2方式操作EXCEL,OLE是将所有EXCEL中组成结构封装成一个ole2的类。通过ole2对象方法来操作Excel。
1、类对象：lo_application:一个Excel；
通过create object语句创建一个excel对象，
CREATE OBJECT lo_application \'Excel.Application\'. "创建excel对象
设置EXCEL的属性，是否可见：
SET PROPERTY OF lo_application \'Visible\' = 1. "设置excel可见 SET PROPERTY OF lo_application \'SheetsInNewWorkbook\' = 1.
2、类对象：lo_workbooks :工作簿集合；
通过创建的excel对象，调用Workbooks方法，创建他的工作薄集，
CALL METHOD OF lo_application \'Workbooks\' = lo_workbooks. "调用lo_application的\'Workbooks\'创建工作簿集
3、类对象：lo_workbook:工作簿；
通过创建的工作薄集对象，调用add方法，创建他的工作薄，
CALL METHOD OF lo_workbooks \'Add\' = lo_workbook. "调用lo_workbooks 的方法\'Add\' ,创建工作薄
4、类对象：lo_worksheets:工作表，sheet集合；
通过EXCEL对象的Sheets方法，创建sheet集合对象
"创建一个sheet集合 CALL METHOD OF lo_application \'Sheets\' = lo_worksheets . 通过sheet集合对象，调用add方法，可以创建多个sheet对象。创建出的sheet对象调用Activate方法，可以激活。 "创建新的work sheet. "默认创建的最新的sheet,处于active状态 CALL METHOD OF lo_worksheets \'Add\' = new_worksheet. *CALL METHOD OF new_worksheet \'Activate\'. "激活当前新的sheet
5、类对象：lo_worksheet:工作表，sheet;
通过EXCEL对象的Activesheet属性，可以获取到当前活动的sheet,这个sheet就是创建初始时，默认存在的sheet。
GET PROPERTY OF lo_application \'ACTIVESHEET\' = lo_worksheet. "获取当前活动sheet,初始创建时,只有一个sheet,且active
设置sheet的名称，设置sheet的属性name：
SET PROPERTY OF lo_worksheet \'Name\' = \'first sheet\'. "设置sheet名
6、类对象：lo_cell:excel单元格；
通过sheet对象，调用方法Cells方法，在当前sheet中创建单元格对象。
"创建单元个 CALL METHOD OF lo_worksheet \'Cells\' = lo_cell EXPORTING # = 1 "Row # = 1. "Column
设置单元格对象属性value，为单元格赋值：
SET PROPERTY OF lo_cell \'value\' = \'天\'. "设置单元个值
设置单元格对象属性NumberFormat，设置单元格数据格式：
"设置单元格值格式,支持excel中的格式 *SET PROPERTY OF lo_cell \'NumberFormat\' = \'0.00\'. SET PROPERTY OF lo_cell \'NumberFormat\' = \'m/d/yy\'.
设置单元格自动换列
"特别长文字，自动换列，看到全部文字 SET PROPERTY OF lo_cell \'WrapText\' = 1.
设置水平对齐方式
"设置水平对齐方式 "center = -4108 "left = -4131 "right = -4152 "top = -4160 SET PROPERTY OF lo_cell \'HorizontalAlignment\' = -4108.
类对象：lo_font:字体设置对象，调用单元格对象的font方法,获取font对象，通过font对象设置单元格字体。
"设置单元格字体 CALL METHOD OF lo_cell \'font\' = lo_font. "EXCEL支持的字体名 *SET PROPERTY OF lo_font \'Name\' = \'Agency FB\'. "字体名 SET PROPERTY OF lo_font \'Name\' = \'微软正黑体\'. "字体名 SET PROPERTY OF lo_font \'Size\' = 15. "大小 "颜色数字规则? SET PROPERTY OF lo_font \'Color\' = -12123441. "颜色 SET PROPERTY OF lo_font \'TintAndShade\' = 0. "明亮度-1~1,1完全透明 SET PROPERTY OF lo_font \'Bold\' = 0. "是否粗体字 0 :false 1:true SET PROPERTY OF lo_font \'Italic\' = 1. "是否斜体 0：false 1:true SET PROPERTY OF lo_font \'Underline\' = 2. "xlUnderlineStyleSingle = 2,下划线
类对象lo_interior,单元格背景;
示例：
通过单元格对象获取lo_interior对象，设置Color或者ColorIndex属性，设置背景颜色，两种方式都可。对应颜色值不清楚？
"设置单元格背景 DATA:lo_interior TYPE ole2_object. CALL METHOD OF lo_cell \'interior\' = lo_interior. SET PROPERTY OF lo_interior \'Color\' = 12123441. SET PROPERTY OF lo_interior \'ColorIndex\' = 43.
8、类对象：lo_range:多个单元格集合；
选择多个单元格集合。
示例：
选择单元格(1,1)到（3，3）这部分区域:
"选择Ranges of cells CALL METHOD OF lo_worksheet \'Cells\' = lo_cellstart EXPORTING # = 1 "row # = 1. "column CALL METHOD OF lo_worksheet \'Cells\' = lo_cellend EXPORTING # = 3 "row # = 3. "column "选中一个区域Cell CALL METHOD OF lo_worksheet \'RANGE\' = lo_range EXPORTING # = lo_cellstart # = lo_cellend.
也可以选中一行或者一列单元格:
类对象：lo_column:excel行
"选择列 CALL METHOD OF lo_worksheet \'Columns\' = lo_column EXPORTING # = 1.
类对象：lo_row:excel列
"选择行 CALL METHOD OF lo_worksheet \'Row\' = lo_row EXPORTING # = 2.
如果要操作选择部分区域需要lo_selection对象
类对象：lo_selection，选中操作对象
"获取选中区域selection CALL METHOD OF lo_row \'Select\'. CALL METHOD OF lo_application \'selection\' = lo_selection.
9、类对象：lo_validation：验证操作validation对象;
示例：测试失败？
"添加验证 "选中(4，4) CALL METHOD OF lo_worksheet \'Cells\' = lo_cell EXPORTING # = 4 # = 4. "获取选中区域selection CALL METHOD OF lo_cell \'Select\'. CALL METHOD OF lo_application \'selection\' = lo_selection. "获取验证对象lo_validation CALL METHOD OF lo_selection \'Validation\' = lo_validation. "添加验证规则 CALL METHOD OF lo_validation \'Add\' EXPORTING #1 = 4 "验证类型Type 4 = xlValidateDate，验证日期 #2 = 1 "验证弹窗类型AlertStype 1 = xlValidAlertStop #3 = 1 "操作类型，Operator 1 = xlBetween，在之间 #4 = \'1/1/2000\' "Formula1 #5 = \'1/1/2010\'. "Formula2 "设置错误信息 SET PROPERTY OF lo_validation \'ErrorMessage\' = \'Enter a valid date\'.
10、使用复制粘贴方式将内表数数据写入EXCEL
示例：
"复制粘贴方式将数据写入EXCEL CALL METHOD OF lo_worksheet \'Activate\'. "激活当前新的sheet DATA:test_spfli LIKE TABLE OF spfli WITH HEADER LINE. TYPES: ty_data(1500) TYPE c. DATA: lt_data TYPE ty_data OCCURS 0 WITH HEADER LINE. DATA:lv_cont TYPE I. FIELD-SYMBOLS: <field> TYPE ANY. SELECT * FROM spfli INTO TABLE test_spfli. * Prepare the data before copy to clipboard; LOOP AT test_spfli. lv_cont = 1. * Write for example 5 columns per row. DO 5 TIMES. ASSIGN COMPONENT lv_cont OF STRUCTURE test_spfli TO <field>. "使用#将数据链接起来，数据格式#xxx#xxxx#xxxx CONCATENATE lt_data <field> INTO lt_data SEPARATED BY cl_abap_char_utilities=>horizontal_tab. ADD 1 TO lv_cont. ENDDO. "去掉开始#号 SHIFT lt_data BY 1 PLACES LEFT. APPEND lt_data. CLEAR lt_data. ENDLOOP. * Copy to clipboard into ABAP CALL FUNCTION \'CONTROL_FLUSH\' EXCEPTIONS OTHERS = 3. CALL FUNCTION \'CLPB_EXPORT\' TABLES data_tab = lt_data EXCEPTIONS clpb_error = 1 OTHERS = 2. * Select the cell A1 CALL METHOD OF lo_worksheet \'Cells\' = lo_cell EXPORTING #1 = 1 "Row #2 = 1. "Column * Paste clipboard from cell A1 CALL METHOD OF lo_cell \'SELECT\'. CALL METHOD OF lo_worksheet \'PASTE\'. "调用lo_workbook \'SaveAs\' 方法保存excel CALL METHOD OF lo_workbook \'SaveAs\' EXPORTING # = lv_complete_path. if sy-subrc = 0. MESSAGE \'File downloaded successfully\' TYPE \'S\'. ELSE. MESSAGE \'Error downloading the file\' TYPE \'E\'. ENDIF.
完整示例：
TYPE-POOLS: soi,ole2. DATA: lo_application TYPE ole2_object, "代表excel对象 lo_workbook TYPE ole2_object, "代表excel工作薄 lo_workbooks TYPE ole2_object, "代表excel工作薄集合 lo_range TYPE ole2_object, "代表多个单元格集合 lo_worksheet TYPE ole2_object, "代表excel工作表sheet lo_worksheets TYPE ole2_object, "代表excel工作表sheet集合 lo_column TYPE ole2_object, "代表excel列 lo_row TYPE ole2_object, "代表excel行 lo_cell TYPE ole2_object, "代表excel单元格 lo_font TYPE ole2_object. "代表字体 DATA: lo_cellstart TYPE ole2_object, lo_cellend TYPE ole2_object, lo_selection TYPE ole2_object, lo_validation TYPE ole2_object. DATA: lv_selected_folder TYPE string, lv_complete_path TYPE char256, lv_titulo TYPE string . "弹窗描述 DATA: new_worksheet TYPE ole2_object. "新sheet "调用文件路径选择框,默认路径C:\ CALL METHOD cl_gui_frontend_services=>directory_browse EXPORTING window_title = lv_titulo "弹窗最上方,可以添加描述信息,不是弹窗标题 initial_folder = \'C:\\' "初始化选择路径 CHANGING selected_folder = lv_selected_folder "返回选择路径 EXCEPTIONS cntl_error = 0 error_no_gui = 1 OTHERS = 2. "检查是否获取到路径 CHECK NOT lv_selected_folder IS INITIAL. DATA:str_len TYPE I. DATA:temp_sign(1) TYPE C. str_len = STRLEN( lv_selected_folder ) - 1. temp_sign = lv_selected_folder+str_len(1). if temp_sign = \'\\'. "确定保存路径 CONCATENATE lv_selected_folder \'Test\' INTO lv_complete_path. else. "确定保存路径 CONCATENATE lv_selected_folder \'\Test\' INTO lv_complete_path. ENDIF. "创建excel对象 CREATE OBJECT lo_application \'Excel.Application\'. "创建excel对象 SET PROPERTY OF lo_application \'Visible\' = 1. "设置excel可见 SET PROPERTY OF lo_application \'SheetsInNewWorkbook\' = 1. "创建workbook CALL METHOD OF lo_application \'Workbooks\' = lo_workbooks. "调用lo_application的\'Workbooks\'创建工作簿集 CALL METHOD OF lo_workbooks \'Add\' = lo_workbook. "调用lo_workbooks 的方法\'Add\' ,创建工作薄 GET PROPERTY OF lo_application \'ACTIVESHEET\' = lo_worksheet. "获取当前活动sheet,初始创建时,只有一个sheet,且active SET PROPERTY OF lo_worksheet \'Name\' = \'first sheet\'. "设置sheet名 "创建一个sheet集合 CALL METHOD OF lo_application \'Sheets\' = lo_worksheets . "创建新的work sheet. "默认创建的最新的sheet,处于active状态 CALL METHOD OF lo_worksheets \'Add\' = new_worksheet. *CALL METHOD OF new_worksheet \'Activate\'. "激活当前新的sheet SET PROPERTY OF new_worksheet \'Name\' = \'second sheet\'. "设置sheet名 "lo_application默认sheet,设置值 "创建单元个 CALL METHOD OF lo_worksheet \'Cells\' = lo_cell EXPORTING # = 1 "Row # = 1. "Column SET PROPERTY OF lo_cell \'value\' = \'天\'. "设置单元个值 "设置单元格字体 CALL METHOD OF lo_cell \'font\' = lo_font. "EXCEL支持的字体名 *SET PROPERTY OF lo_font \'Name\' = \'Agency FB\'. "字体名 SET PROPERTY OF lo_font \'Name\' = \'微软正黑体\'. "字体名 SET PROPERTY OF lo_font \'Size\' = 15. "大小 "创建单元个 CALL METHOD OF lo_worksheet \'Cells\' = lo_cell EXPORTING # = 1 "Row # = 2. "Column SET PROPERTY OF lo_cell \'value\' = \'12/03/2010\'. "设置单元个值 "设置单元格值格式,支持excel中的格式 *SET PROPERTY OF lo_cell \'NumberFormat\' = \'0.00\'. SET PROPERTY OF lo_cell \'NumberFormat\' = \'m/d/yy\'. "设置单元格字体 CALL METHOD OF lo_cell \'font\' = lo_font. "颜色数字规则? SET PROPERTY OF lo_font \'Color\' = -12123441. "颜色 SET PROPERTY OF lo_font \'TintAndShade\' = 0. "明亮度-1~1,1完全透明 SET PROPERTY OF lo_font \'Bold\' = 0. "是否粗体字 0 :false 1:true SET PROPERTY OF lo_font \'Italic\' = 1. "是否斜体 0：false 1:true SET PROPERTY OF lo_font \'Underline\' = 2. "xlUnderlineStyleSingle = 2,下划线 "设置单元格背景 DATA:lo_interior TYPE ole2_object. CALL METHOD OF lo_cell \'interior\' = lo_interior. SET PROPERTY OF lo_interior \'Color\' = 12123441. "设置单元格边框 DATA:lo_borders TYPE ole2_object. "获取边框对象，输入参数‘7’:左边框xlEdgeLeft；‘8’：上边框xlEdgeTop；‘9’：下边框xlEdgeBottom；‘10’：右边框xlEdgeRight CALL METHOD OF lo_cell \'borders\' = lo_borders EXPORTING # = 7. SET PROPERTY OF lo_borders \'LineStyle\' = 1."xlContinuous=\'1\' SET PROPERTY OF lo_borders \'Weight\' = 4."xlThick = 4 "在对应new sheet设置单元格值 "创建单元个 CALL METHOD OF new_worksheet \'Cells\' = lo_cell EXPORTING # = 1 "Row # = 1. "Column SET PROPERTY OF lo_cell \'value\' = \'hello2\'. "设置单元个值 "创建单元个 CALL METHOD OF new_worksheet \'Cells\' = lo_cell EXPORTING # = 1 "Row # = 2. "Column SET PROPERTY OF lo_cell \'value\' = \'welcome2\'. "设置单元个值 *"选择Ranges of cells *CALL METHOD OF lo_worksheet \'Cells\' = lo_cellstart * EXPORTING * # = 1 "row * # = 1. "column * *CALL METHOD OF lo_worksheet \'Cells\' = lo_cellend * EXPORTING * # = 3 "row * # = 3. "column * *"选中一个区域Cell *CALL METHOD OF lo_worksheet \'RANGE\' = lo_range * EXPORTING * # = lo_cellstart * # = lo_cellend. * "选择列 *CALL METHOD OF lo_worksheet \'Columns\' = lo_column * EXPORTING * # = 1. *"选择行 *CALL METHOD OF lo_worksheet \'Row\' = lo_row * EXPORTING * # = 2. * *"获取选中区域selection *CALL METHOD OF lo_row \'Select\'. *CALL METHOD OF lo_application \'selection\' = lo_selection. *"添加验证 *"选中(4，4) *CALL METHOD OF lo_worksheet \'Cells\' = lo_cell * EXPORTING * # = 4 * # = 4. *"获取选中区域selection *CALL METHOD OF lo_cell \'Select\'. *CALL METHOD OF lo_application \'selection\' = lo_selection. *"获取验证对象lo_validation *CALL METHOD OF lo_selection \'Validation\' = lo_validation. *"添加验证规则 *CALL METHOD OF lo_validation \'Add\' * EXPORTING * #1 = 4 "验证类型Type 4 = xlValidateDate，验证日期 * #2 = 1 "验证弹窗类型AlertStype 1 = xlValidAlertStop * #3 = 1 "操作类型，Operator 1 = xlBetween，在之间 * #4 = \'1/1/2000\' "Formula1 * #5 = \'1/1/2010\'. "Formula2 *"设置错误信息 *SET PROPERTY OF lo_validation \'ErrorMessage\' = \'Enter a valid date\'. "调用lo_workbook \'SaveAs\' 方法保存excel CALL METHOD OF lo_workbook \'SaveAs\' EXPORTING # = lv_complete_path. if sy-subrc = 0. MESSAGE \'File downloaded successfully\' TYPE \'S\'. ELSE. MESSAGE \'Error downloading the file\' TYPE \'E\'. ENDIF. CALL METHOD OF lo_application \'QUIT\'. "退出excel "释放资源 FREE OBJECT lo_worksheet. FREE OBJECT new_worksheet. FREE OBJECT lo_workbook. FREE OBJECT lo_workbooks. FREE OBJECT lo_application.
3、DOI操作EXCEL
DOI:Desktop Office Integretion
四个对象：
container: 存放excel电子表格(spreadsheet)的容器。
展示spreadsheet肯定需要一个容器来存放。这个容器一般在dialog screen中定义，也可以直接使用ABAP程序默认的screen（即screen号码为1000的屏幕)。
container control: 容器中用于创建和管理其他Office集成所需要的对象。container control是一个接口，类型是i_oi_container_control。
document proxy: 每一个document proxy的实例代表用office application打开的文档，可以是excel，也可以是word。如果想打开多个文档，需要定义多个实例。document proxy是一个接口，类型为i_oi_document_proxy。
spreadsheet: spreadsheet接口，代表最终要操作的excel文档。spreadhseet的类型是i_oi_spreadsheet
通过Tcode:OAOR,可以将本地文档模板上传到服务器行，通过对应类读取到ABAP程序中进行操作。
如果读取服务器上的文档模板，需要cl_bds_document_set类：
business document set: bds是business document set的缩写。
business document set用于管理后续要操作的文档，可以包含一个或多个文档。
示例:
"容器对象 DATA:gr_container type ref to cl_gui_container. "管理Excel或其他文档对象 DATA:gr_control type ref to i_oi_container_control. "文档对象，打开保存文档操作 DATA:gr_document type ref to i_oi_document_proxy. "sheet操作接口对象 DATA:gr_spreadsheet type ref to i_oi_spreadsheet. "sheet中选择操作区域range DATA:gr_ranges type SOI_RANGE_LIST. DATA:gr_ranges_item TYPE SOI_RANGE_ITEM. "对应range的填充内容 DATA:gr_contents TYPE SOI_GENERIC_TABLE. DATA:gr_contents_item TYPE SOI_GENERIC_ITEM. start-of-selection. perform main. *get container form get_container. "获取容器对象,实例化 gr_container = cl_gui_container=>screen0."screen0 代表当前1000默认屏幕 endform. "get_container * create container control form create_container_control. "调用方法，创建管理对象control CALL METHOD c_oi_container_control_creator=>get_container_control IMPORTING control = gr_control. "初始化control对象 * initialize control CALL METHOD gr_control->init_control EXPORTING inplace_enabled = \'X\' "嵌入屏幕显示‘x’ inplace_scroll_documents = \'X\' "可滚动 register_on_close_event = \'X\' register_on_custom_event = \'X\' r3_application_name = \'DOI demo\' parent = gr_container. endform. "create excel document form create_excel_document. CALL METHOD gr_control->get_document_proxy EXPORTING document_type = \'Excel.Sheet\' no_flush = \'X\' IMPORTING document_proxy = gr_document. "open_inplace参数控制excel文档是独立显示还是在SAP GUI中嵌入显示。 "如果嵌入显示，gr_control的init_control方法中，inplace_enabled参数要设为X CALL METHOD gr_document->create_document EXPORTING document_title = \'DOI test\' no_flush = \'\' open_inplace = \'\'. endform. "create_excel_document "使用spreedsheet填充数据 form fill_data. "sheet的名字 DATA:sheetname(20) TYPE C . DATA:error TYPE REF TO I_OI_ERROR. DATA:retcode TYPE SOI_RET_STRING. "调用document的方法，获取sheet对象 CALL METHOD gr_document->get_spreadsheet_interface EXPORTING no_flush = \'\' IMPORTING sheet_interface = gr_spreadsheet error = error. "spreadsheet可以获取当前活动sheetname CALL METHOD gr_spreadsheet->get_active_sheet EXPORTING no_flush = \'\' IMPORTING sheetname = sheetname error = error retcode = retcode. "获取当前活动sheet指定ranges区域 CALL METHOD gr_spreadsheet->insert_range_dim EXPORTING no_flush = \'\' name = \'range1\' left = 1 top = 1 rows = 1 columns = 2 * updating = 1 * sheetname = \'sheet1\' IMPORTING error = error retcode = retcode. * "修改range的名字，范围 * CALL METHOD gr_spreadsheet->change_range * EXPORTING * rangename = \'range1\' * newname = \'range1\' * rows = 1 * columns = 1 ** updating = 1 "默认-1 * IMPORTING * error = error * retcode = retcode. "设置ranges区域字体 CALL METHOD gr_spreadsheet->set_font EXPORTING rangename = \'range1\' "range的名字 family = \'Times New Roman\' size = 9 bold = 0 "加粗 italic = 0 "斜体 align = 0 "水平居中 IMPORTING error = error retcode = retcode. "设置ranges区域格式 CALL METHOD gr_spreadsheet->set_format EXPORTING rangename = \'range1\' typ = 0 currency = \'RMB\' decimals = 1 IMPORTING error = error retcode = retcode. "设置ranges区域 gr_ranges_item-name = \'range1\'. gr_ranges_item-rows = 1. gr_ranges_item-columns = 2. APPEND gr_ranges_item TO gr_ranges. "设置内容,value 256长度 gr_contents_item-row = 1. gr_contents_item-column = 1. gr_contents_item-value = \'112\'. APPEND gr_contents_item TO gr_contents. gr_contents_item-row = 1. gr_contents_item-column = 2. gr_contents_item-value = \'113\'. APPEND gr_contents_item TO gr_contents. "设置sheet中的内容 CALL METHOD gr_spreadsheet->set_ranges_data EXPORTING no_flush = \'\' ranges = gr_ranges "选中区域 contents = gr_contents "填充内容 * updating = \'\' * rangesdef = \'\' IMPORTING retcode = retcode error = error. * "创建一个新的sheet * call method gr_spreadsheet->add_sheet * exporting * name = \'sheet1\' * no_flush = \'\' * importing * error = error * retcode = retcode. * "删除一个sheet * call method gr_spreadsheet->delete_sheet * exporting * name = \'sheet1\' * no_flush = \'\' * importing * error = error * retcode = retcode. "active指定name的sheet * CALL METHOD gr_spreadsheet->select_sheet * EXPORTING * name = \'sheet1\' * no_flush = \'\' * IMPORTING * error = error * retcode = retcode. * "修改sheet名字 * CALL METHOD gr_spreadsheet->set_sheet_name * EXPORTING * newname = \'sheet1\' * oldname = sheetname * no_flush = \'\' * IMPORTING * error = error * retcode = retcode. endform. "保存文档 form save_doc. DATA:error TYPE REF TO I_OI_ERROR. DATA:retcode TYPE SOI_RET_STRING. "保存的文件路径 DATA:file_name(20) TYPE C VALUE \'D:\test.xls\'. "只传文件名，不设置路径，该方法会自动默认保存到c盘用户文件下的文档文件夹中） CALL METHOD gr_document->save_as EXPORTING file_name = file_name prompt_user = \'\' IMPORTING error = error retcode = retcode. endform. "释放资源 form free_source. "关闭文档 CALL METHOD gr_document->close_document. "释放容器 CALL METHOD gr_container->free. "关闭文档管理 CALL METHOD gr_control->destroy_control. FREE gr_control. LEAVE PROGRAM. endform. form main. * skip 1. perform get_container. perform create_container_control. perform create_excel_document. Perform fill_data. perform save_doc. perform free_source. endform.
带模板的DOI操作：
1、使用Tcode:OAOR，上传指定格式的文档；、
2、通过调用cl_bds_document_set=>get_with_url方法，获取我们上传文档的URL；
3、然后通过gr_document->open_document，将获取的URL传入，打开的就是我们上传的模板文档；
使用OAOR，上传模板。Class name:HRFPM_EXCEL_STANDARD;Class Type:OT;Object Key:可以自己定义，访问时也是通过这个找到我们文件，示例：DOI_EXCEL_TEXT。
可以选择我们要上传的文档类型。这里我们选中excel，双击会弹出一个文件选择框，选择我们要上传的模板文件就可以了。
修改一下描述，或者默认，点击确认就可以了。
我们可以看到已经上传成功。
使用代码访问到我们上传的文档，获取文档的URL
示例：
*business document system data: gr_bds_documents type ref to cl_bds_document_set, g_classname type sbdst_classname, g_classtype type sbdst_classtype, g_objectkey type sbdst_object_key, g_doc_components type sbdst_components, g_doc_signature type sbdst_signature. * template url data: gt_bds_uris type sbdst_uri, gs_bds_url like line of gt_bds_uris, g_template_url(256) type c. g_classname = \'HRFPM_EXCEL_STANDARD\'. g_classtype = \'OT\'. g_objectkey = \'DOI_EXCEL_TEST\'. form get_template_url. CREATE OBJECT gr_bds_documents. CALL METHOD cl_bds_document_set=>get_info EXPORTING classname = g_classname classtype = g_classtype object_key = g_objectkey CHANGING components = g_doc_components signature = g_doc_signature. CALL METHOD cl_bds_document_set=>get_with_url EXPORTING classname = g_classname classtype = g_classtype object_key = g_objectkey CHANGING uris = gt_bds_uris signature = g_doc_signature. free gr_bds_documents. read table gt_bds_uris into gs_bds_url index 1. g_template_url = gs_bds_url-uri. endform. "get_template_url "通过URL获取模板文档 call method gr_document->open_document exporting open_inplace = \'X\' document_url = g_template_url.