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
View Code
方式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.
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.
View Code
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.
View Code
带模板的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.