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’
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.
4、常见文档MIME Type
{".3gp", "video/3gpp"},
{".apk", "application/vnd.android.package-archive"},
{".asf", "video/x-ms-asf"},
{".avi", "video/x-msvideo"},
{".bin", "application/octet-stream"},
{".bmp", "image/bmp"},
{".c", "text/plain"},
{".class", "application/octet-stream"},
{".conf", "text/plain"},
{".cpp", "text/plain"},
{".doc", "application/msword"},
{".docx", "application/vnd.openxmlformats-officedocument.wordprocessingml.document"},
{".xls", "application/vnd.ms-excel"},
{".xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"},
{".exe", "application/octet-stream"},
{".gif", "image/gif"},
{".gtar", "application/x-gtar"},
{".gz", "application/x-gzip"},
{".h", "text/plain"},
{".htm", "text/html"},
{".html", "text/html"},
{".jar", "application/java-archive"},
{".java", "text/plain"},
{".jpeg", "image/jpeg"},
{".jpg", "image/jpeg"},
{".js", "application/x-javascript"},
{".log", "text/plain"},
{".m3u", "audio/x-mpegurl"},
{".m4a", "audio/mp4a-latm"},
{".m4b", "audio/mp4a-latm"},
{".m4p", "audio/mp4a-latm"},
{".m4u", "video/vnd.mpegurl"},
{".m4v", "video/x-m4v"},
{".mov", "video/quicktime"},
{".mp2", "audio/x-mpeg"},
{".mp3", "audio/x-mpeg"},
{".mp4", "video/mp4"},
{".mpc", "application/vnd.mpohun.certificate"},
{".mpe", "video/mpeg"},
{".mpeg", "video/mpeg"},
{".mpg", "video/mpeg"},
{".mpg4", "video/mp4"},
{".mpga", "audio/mpeg"},
{".msg", "application/vnd.ms-outlook"},
{".ogg", "audio/ogg"},
{".pdf", "application/pdf"},
{".png", "image/png"},
{".pps", "application/vnd.ms-powerpoint"},
{".ppt", "application/vnd.ms-powerpoint"},
{".pptx", "application/vnd.openxmlformats-officedocument.presentationml.presentation"},
{".prop", "text/plain"},
{".rc", "text/plain"},
{".rmvb", "audio/x-pn-realaudio"},
{".rtf", "application/rtf"},
{".sh", "text/plain"},
{".tar", "application/x-tar"},
{".tgz", "application/x-compressed"},
{".txt", "text/plain"},
{".wav", "audio/x-wav"},
{".wma", "audio/x-ms-wma"},
{".wmv", "audio/x-ms-wmv"},
{".wps", "application/vnd.ms-works"},
{".xml", "text/plain"},
{".z", "application/x-compress"},
{".zip", "application/x-zip-compressed"},
{"", "*/*"}