ABAP-EXCEL 上传下载
作者：桥下本有油菜花
- 2022 年 7 月 19 日
本文字数：15186 字
阅读完需：约 50 分钟
OLE
早期 SAP 解决 与 Office 集成的技术。
OLE 语法参照 VBA，在 ABAP 中调用方法来操作 Excel。
语法的友好性差；另外将数据从 ABAP 内表写入到 Excel ，速度特别慢，性能难以接受。
方法:
创建实例
CREATE OBJECT name_obj 'app'.复制代码
设置属性
SET PROPERTY OF name_obj 'XXX' = f .复制代码
读取属性
GET PROPERTY OF name_obj ‘XXX' = f .复制代码
调用实例方法
CALL METHOD OF name_obj 'XXX' = f "由f来接收返回值 EXPORTING #1 = f1. "调用name_obj的方法xxx 传入参数f1…fn
复制代码
释放实例
FREE OBJECT name_obj . "释放.
复制代码
对象
DATA: GO_EXCEL TYPE OLE2_OBJECT, "Excel GO_BOOKS TYPE OLE2_OBJECT, "工作簿 GO_BOOK TYPE OLE2_OBJECT, "工作表 GO_CELL TYPE OLE2_OBJECT. "单元格
复制代码
OLE-获取模板
SMW0 上传的模板，直接下载 MIME 数据到本地
DATA: lv_fname TYPE rlgrap-filename, lv_key TYPE wwwdatatab, lv_rc TYPE i, lv_objid TYPE wwwdata-objid VALUE 'Z9050'.
SELECT SINGLE relid FROM wwwdata INTO @DATA(lv_relid) WHERE relid = @lc_mi AND objid = @lv_objid AND srtf2 = 0.IF sy-subrc <> 0. MESSAGE i000 WITH text-e03 DISPLAY LIKE zp3ps_e." Check template EXIT.ENDIF.
lv_fname = pv_fullpath.CONCATENATE lc_mi lv_objid INTO lv_key.
CALL FUNCTION 'DOWNLOAD_WEB_OBJECT' EXPORTING key = lv_key destination = lv_fname IMPORTING rc = lv_rc.
复制代码
OLE-打开 Excel 并激活工作表
"打开ExcelCREATE OBJECT GO_EXCEL ’Excel.Application’."设置Excel可见SET PROPERTY OF GO_EXCEL ’Visible’ = 1."创建空白工作簿CALL METHOD OF GO_EXCEL 'Workbooks' = GO_BOOKS."创建工作表CALL METHOD OF GO_BOOKS 'Add' = GO_BOOK."选择工作表CALL METHOD OF GO_EXCEL 'Worksheets' = GO_BOOK EXPORTING #1 = 1."设置工作表名SET PROPERTY OF GO_BOOK 'Name' = 'Sheet1'."激活工作表CALL METHOD OF GO_BOOK 'Activate'. 复制代码
OLE-选择单元格赋值
通过将数据复制到粘贴板进行数据插入
"清空单元格内容CALL METHOD OF GO_EXCEL 'Cells' = LO_CELL1 EXPORTING #1 = 1 #2 = 1. GET PROPERTY OF GO_EXCEL 'ActiveCell' = LO_CELL2.
CALL METHOD OF LO_CELL2 'SpecialCells' = LO_CELL3 EXPORTING #1 = '11'. "'xlLastCell'. CALL METHOD OF GO_EXCEL 'Range' = GO_RANGE EXPORTING #1 = LO_CELL1 #2 = LO_CELL3.
CALL METHOD OF GO_RANGE 'ClearContents'. "将要下载的表格复制到剪贴板CL_GUI_FRONTEND_SERVICES=>CLIPBOARD_EXPORT( IMPORTING DATA = LT_CLIPTAB[] CHANGING RC = LV_RD EXCEPTIONS CNTL_ERROR = 1 ERROR_NO_GUI = 2 NOT_SUPPORTED_BY_GUI = 3 OTHERS = 4 )."粘贴数据CALL METHOD OF GO_EXCEL 'Cells' = LO_CELL1 EXPORTING #1 = 1 #2 = 1. CALL METHOD OF GO_EXCEL 'Cells' = LO_CELL2 EXPORTING #1 = LV_ROW #2 = LV_COL. CALL METHOD OF GO_EXCEL 'Range' = GO_RANGE EXPORTING #1 = LO_CELL1 #2 = LO_CELL2. CALL METHOD OF GO_RANGE 'Select'.CALL METHOD OF GO_SHEET 'Paste'. 复制代码
OLE-设置单元格格式
单元格内设置公式，例如，合计数据
行合计：PV_VAL = ‘=SUM(RC[1]:RC[7]) ’ 合计第一列到第七列数据
列合计：PV_VAL = ‘=SUM(R[1]C:R[7]C) ’ 合计第一行到第七行数据
设置单个单元格数据
CALL METHOD OF GO_EXCEL 'Cells' = LO_CELL EXPORTING #1 = PV_ROW #2 = PV_COL.
SET PROPERTY OF LO_CELL 'Value' = PV_VAL.FREE OBJECT LO_CELL. "数据类型 '@'：常规；'#,##0' ：金额SET PROPERTY OF GO_RANGE 'NumberFormat' = '@' . "对齐方式 2：左对齐 3：居中 4:右对齐SET PROPERTY OF GO_RANGE 'HorizontalAlignment' = 2."颜色 Eg：15 浅灰 16深灰 6 黄色CALL METHOD OF GO_RANGE 'Interior' = GO_CELLINT. SET PROPERTY OF GO_CELLINT 'Colorindex' = PV_COLOR. "边框CALL METHOD OF GO_RANGE 'Borders' = GO_BORDER.SET PROPERTY OF GO_BORDER 'Weight' = 2.SET PROPERTY OF GO_BORDER 'Linestyle' = 1. "自适应宽度CALL METHOD OF GO_EXCEL 'Columns' = GO_COLUMNS.CALL METHOD OF GO_COLUMNS 'AutoFit'."执行宏CALL METHOD OF GO_EXCEL 'RUN' EXPORTING #1 = 'ZMAC'.复制代码
OLE-保存文件
"保存文件CALL METHOD OF GO_BOOKS 'Save'."另存为CALL METHOD OF GO_BOOKS 'SaveAs' EXPORTING #1 = 'c:\test.xls' #2 = 1."关闭工作区CALL METHOD OF GO_BOOKS 'Close'."退出ExcelCALL METHOD OF GO_EXCEL 'Quit'.复制代码
DOI
DOI 是 Desktop Office Integration 的缩写，是 SAP 提供的解决与 Office 集成的技术方案。
DOI 可以看作 OLE 的替代品，用面向对象的方式实现。
很好的解决了 OLE 的两个问题。
为了操作 Excel 文档，至少需要以下四个对象：
Container
存放 Excel 电子表格 (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 文档。
类型是 i_oi_spreadsheet
business document set
如果读取服务器上的文档模板，用 cl_bds_document_set 类，将 business document set 缩写为 bds，bds 用于管理要操作的文档，可以包含一个或多个文档。
DOI-打开 Excel
实例化 container control、container 和 Document Proxy
DATA: GO_CONTROL TYPE REF TO I_OI_CONTAINER_CONTROL, GO_DOCU_PROXY TYPE REF TO I_OI_DOCUMENT_PROXY, GO_SHEET TYPE REF TO I_OI_SPREADSHEET, GO_ERROR TYPE REF TO I_OI_ERROR. "实例化container control、container和Document Proxyc_oi_container_control_creator=>get_container_control( IMPORTING control = go_control error = go_error ). go_error->raise_message( EXPORTING type = zp3ps_e ).
go_control->init_control( EXPORTING r3_application_name = 'EXCEL CONTAINER' inplace_enabled = space inplace_resize_documents = zp3ps_x inplace_scroll_documents = zp3ps_x inplace_show_toolbars = zp3ps_x parent = cl_gui_container=>screen0 register_on_close_event = space register_on_custom_event = space IMPORTING error = go_error ). go_error->raise_message( EXPORTING type = zp3ps_e ).
DATA lv_doc_type(80) VALUE soi_doctype_excel_sheet . " Excel.Sheet go_control->get_document_proxy( EXPORTING document_type = lv_doc_type IMPORTING document_proxy = go_docu_proxy error = go_error ). go_error->raise_message( EXPORTING type = zp3ps_e ).
复制代码
DOI-新建空白工作簿
对于有模板的，可以通过打开模板进行后续操作；没有模板的，需要创建一个空白工作簿，如下
DATA:lv_temp TYPE i. IF pv_create IS NOT INITIAL. go_docu_proxy->create_document( EXPORTING document_title = 'excel' open_inplace = zp3ps_x no_flush = zp3ps_x IMPORTING error = go_error ). go_error->raise_message( EXPORTING type = zp3ps_e ). ENDIF.复制代码
打开工作簿后，需要实例化工作表对象
*Indicates Whether Interface Is Supported For This Doc. Type go_docu_proxy->has_spreadsheet_interface( IMPORTING is_available = lv_temp ).
CHECK lv_temp = 1.*Returns an Interface Reference go_docu_proxy->get_spreadsheet_interface( IMPORTING error = go_error sheet_interface = go_sheet ). IF go_error->has_failed = zp3ps_x. go_error->raise_message( EXPORTING type = zp3ps_e ). ENDIF.
复制代码
DOI-获取模板
方法一：SMW0 上传的模板，获取模板数据到内表进行打开
DATA: lt_doc_table LIKE TABLE OF w3mime, lv_doc_size TYPE i, lv_doc_type(80) VALUE soi_doctype_excel_sheet, lv_doc_format(80) TYPE c, lv_retcode TYPE soi_ret_string, lv_objid TYPE wwwdata-objid VALUE 'Z9050'.
CALL FUNCTION 'SAP_OI_LOAD_MIME_DATA' EXPORTING object_id = lv_objid IMPORTING data_size = lv_doc_size document_format = lv_doc_format document_type = lv_doc_type TABLES data_table = lt_doc_table EXCEPTIONS object_not_found = 1 internal_error = 2 OTHERS = 3.
go_docu_proxy->open_document_from_table( EXPORTING document_table = lt_doc_table[] document_size = lv_doc_size no_flush = zp3ps_x IMPORTING retcode = lv_retcode ).
IF lv_retcode <> c_oi_errors=>ret_ok. MESSAGE i000 WITH text-e02 DISPLAY LIKE zp3ps_e. EXIT. ENDIF.复制代码
方法二：OAOR 上传的模板，获取模板到 URL 进行打开
DATA: lo_bds_documents TYPE REF TO cl_bds_document_set,
lv_classname TYPE sbdst_classname VALUE 'HRFPM_EXCEL_STANDARD',
lv_classtype TYPE sbdst_classtype VALUE 'OT',
lv_objectkey TYPE sbdst_object_key VALUE 'DOITEST',
lv_doc_components TYPE sbdst_components, lv_doc_signature TYPE sbdst_signature.
DATA: lt_bds_uris TYPE sbdst_uri, ls_bds_url LIKE LINE OF lt_bds_uris, lv_url(256) TYPE c.
CREATE OBJECT lo_bds_documents.
cl_bds_document_set=>get_info( EXPORTING classname = lv_classname classtype = lv_classtype object_key = lv_objectkey CHANGING components = lv_doc_components signature = lv_doc_signature ). cl_bds_document_set=>get_with_url( EXPORTING classname = lv_classname classtype = lv_classtype object_key = lv_objectkey CHANGING uris = lt_bds_uris signature = lv_doc_signature ).
FREE lo_bds_documents.
READ TABLE lt_bds_uris INTO ls_bds_url INDEX 1.lv_url = ls_bds_url-uri.
go_docu_proxy->open_document( EXPORTING document_title = 'excel' document_url = lv_url open_inplace = space IMPORTING error = go_error ).IF go_error->has_failed = zp3ps_x go_error->raise_message( EXPORTING type = zp3ps_e ).ENDIF.
复制代码
DOI-激活工作表
获取所有工作表名
DATA: lt_sheets TYPE soi_sheets_table. go_sheet->get_sheets( IMPORTING sheets = lt_sheets error = go_error ).复制代码
修改工作表名
"修改Sheet1为Headergo_sheet->set_sheet_name( EXPORTING newname = 'Header' oldname = 'Sheet1' IMPORTING error = go_error ).
复制代码
删除工作表
"删除Sheet2go_sheet->delete_sheet( EXPORTING name = 'Sheet2' IMPORTING error = go_error ).
复制代码
新增工作表
"新增的sheet会放在当前激活sheet前go_sheet->add_sheet( EXPORTING name = 'Item' IMPORTING error = go_error ).
复制代码
选择工作表
"选择Header进行后续操作go_sheet->select_sheet( EXPORTING name = 'Header' no_flush = 'X' IMPORTING error = go_error ).
复制代码
DOI-选择单元格赋值
方法 1：通过函数获取内表字段类型，然后将整个表格插入到 Excel 中
DATA:lv_line TYPE i, lv_column TYPE i, lt_fields LIKE TABLE OF rfc_fields.
DESCRIBE TABLE gt_main LINES lv_line. DESCRIBE TABLE gt_fieldcat LINES lv_column.
* Insert space range go_sheet->insert_range_dim( EXPORTING name = 'DATA' top = 2 left = 1 rows = lv_line " total row columns = lv_column " total column updating = 0 IMPORTING error = go_error ).* Get fileds CALL FUNCTION 'DP_GET_FIELDS_FROM_TABLE' TABLES data = gt_main fields = lt_fields EXCEPTIONS dp_invalid_table = 1 OTHERS = 2.
* Insert a Table go_sheet->insert_one_table( EXPORTING data_table = gt_main[] fields_table = lt_fields rangename = 'DATA' wholetable = 'X' updating = 1 ).
复制代码
方法 2：通过设置行，列，值的方式，将数据处理到结果表中,可以用来处理数据量较少或未处理数据，例如字段标题
DATA:lv_column TYPE i, lt_ranges TYPE soi_range_list, ls_ranges TYPE soi_range_item, lt_title TYPE soi_generic_table, ls_title TYPE soi_generic_item.
DESCRIBE TABLE gt_fieldcat LINES lv_column.
* Select rangeREFRESH: lt_ranges,lt_title.CLEAR ls_ranges.ls_ranges-name = 'TITLE'.ls_ranges-columns = lv_column.ls_ranges-rows = 1.ls_ranges-code = 1.APPEND ls_ranges TO lt_ranges. LOOP AT gt_fieldcat INTO gs_fieldcat. CLEAR ls_title. ls_title-row = 1. ls_title-column = sy-tabix. ls_title-value = gs_fieldcat-scrtext_l. APPEND ls_title TO lt_title.ENDLOOP.
CALL METHOD go_sheet->set_ranges_data EXPORTING ranges = lt_ranges contents = lt_title IMPORTING error = go_error.复制代码
DOI-设置单元格格式
单元格分区
DATA:lt_ranges TYPE soi_full_range_table.lt_ranges[] = VALUE #(( name = 'TITL' top = 1 left = 1 rows = 1 columns = 9 )( name = 'BODY' top = 2 left = 1 rows = lv_rows columns = 9 )( name = 'KEY' top = 2 left = 1 rows = lv_rows columns = 2 ) ).go_sheet->insert_ranges( EXPORTING ranges = lt_ranges IMPORTING error = go_error )."工作表分为3块："A1:I1：TITL"A2:I?： BODY"A2:B?： KEY
复制代码
字体设置
go_sheet->set_font( EXPORTING rangename = 'BODY' family = 'Arial' size = 20 bold = 1 italic = 0 align = 2 IMPORTING error = go_error )."字体：Arial"字体大小：20"字体加粗：0 不加粗； 1 加粗"斜体：0 非斜体； 1 斜体"对齐方式：0 左对齐；1 居中；" 2 右对齐
复制代码
单元格设置
DATA:lt_format TYPE soi_format_table.lt_format[] = VALUE #( ( name = 'KEY' front = 3 back = 6 bold = 1 frametyp = 127 framecolor = 4 ) ).go_sheet->set_ranges_format( EXPORTING formattable = lt_format IMPORTING error = go_error ). "设置KEY 区域"字体颜色为红色，"背景颜色为黄色，"字体加粗，"并添加绿色边框复制代码
宽度自适应
go_sheet->fit_widest( EXPORTING name = 'TITL' IMPORTING error = go_error ).复制代码
单元格边框
go_sheet->set_frame( EXPORTING rangename = 'TITL' typ = '127' color = '1' IMPORTING error = go_error ).复制代码
执行宏
go_docu_proxy->execute_macro( EXPORTING macro_string = 'MACRO1' ).复制代码
DOI-保存文件
保存文档到本地
IF go_docu_proxy IS NOT INITIAL. go_docu_proxy->save_as( EXPORTING file_name = pv_filename IMPORTING error = go_error ).ENDIF.
复制代码
保存文档到 url
go_docu_proxy->save_document_to_url( EXPORTING url = lv_url IMPORTING error = go_error CHANGING document_size = lv_size ).
复制代码
保存文档到内表
go_docu_proxy->save_document_to_table( IMPORTING error = go_error CHANGING document_size = lv_size document_table = lt_table ).
复制代码
退出文档，销毁对象
IF go_docu_proxy NOT IS INITIAL. go_docu_proxy->close_document( ). FREE go_docu_proxy.ENDIF.
IF go_control NOT IS INITIAL. go_control->destroy_control( ). FREE go_control.ENDIF.
复制代码
DOI-ALV Tree 下载
对于 Tree 的下载，可以使用 DOI 的 SET_HIERARCHY_TABLE 方法设置 Excel 的层级结构
DATA: gt_hiera TYPE soi_hierarchy_table. CALL METHOD go_sheet->set_hierarchy_table EXPORTING table = gt_hiera."传入参数GT_HIERA的结构如下：TYPES: BEGIN OF SOI_HIERARCHY_ITEM, FIRSTLINE TYPE I, LENGTH TYPE I, SHEET(128) TYPE C, MODE TYPE I, END OF SOI_HIERARCHY_ITEM. "需要设置层级开始行，层级包含的行数，和层级结构使用的sheet名"[注] 多个层级时需要添加空行，否则连续层级会并到一起"传入参数的处理逻辑可以参考程序YHY_BCALV_TREE中的FORM GET_DOWNLOAD_DATA .
复制代码
XML
用 Open Office 技术，实现与 ALV 自带下载功能一致的下载效果
使用方法简单，适合于下载 ALV 报表，可以直接使用 ALV 设置的 FIELDCATALOG, LAYOUT, SORT 等属性对下载数据设置格式，无需其他数据转换
选择下载格式
DATA: ls_xml_choice TYPE if_salv_bs_xml=>s_type_xml_choice, lt_xml_choice TYPE if_salv_bs_xml=>t_type_xml_choice, lv_xml TYPE xstring, r_result_data TYPE REF TO cl_salv_ex_result_data_table, lo_dref TYPE REF TO data, lv_flavour TYPE string, lv_version TYPE string.
lt_xml_choice = cl_salv_export_xml_dialog=>get_gui_spreadsheet_formats( ).SORT lt_xml_choice BY xml_type.READ TABLE lt_xml_choice INTO ls_xml_choice WITH KEY xml_type = '10' BINARY SEARCH. 复制代码
根据 ALV 相关设置,format 数据
GET REFERENCE OF gt_main[] INTO lo_dref.r_result_data = cl_salv_ex_util=>factory_result_data_table( r_data = lo_dref s_layout = gs_layout t_fieldcatalog = gt_fieldcat t_sort = gt_sort ).
复制代码
转换数据为 XML 格式
CASE cl_salv_bs_a_xml_base=>get_version( ). WHEN if_salv_bs_xml=>version_25. lv_version = if_salv_bs_xml=>version_25. WHEN if_salv_bs_xml=>version_26. lv_version = if_salv_bs_xml=>version_26.ENDCASE.
lv_flavour = if_salv_bs_c_tt=>c_tt_xml_flavour_export.CALL METHOD cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform EXPORTING xml_type = ls_xml_choice-xml_type xml_version = lv_version r_result_data = r_result_data xml_flavour = if_salv_bs_c_tt=>c_tt_xml_flavour_export IMPORTING xml = lv_xml.复制代码
下载数据并打开
GET REFERENCE OF gt_main[] INTO lo_dref.r_result_data = cl_salv_ex_util=>factory_result_data_table( r_data = lo_dref t_fieldcatalog = gt_fieldcat s_layout = gs_layout ).复制代码
通过 XML 方式下载的数据，只需要定义好 FIELDCATALOG 等属性就可以适应字段转换，但是其他方式下载，需要进行一定转换
"有单位字段,例如金额，参照货币转换为外部格式和千分位，并将负号提前WRITE gt_main-price TO lv_price CURRENCY gt_main-currency.CONDENSE: lv_price."数值字段 以文本格式存储的数值，需要将负号提前，必要时增加千分位"负号提前：使用函数CLOI_PUT_SIGN_IN_FRONT"千分位：WRITE lv_number TO lv_char."日期格式 格式适应外部格式"1.使用函数转换日期格式：CONVERT_DATE_TO_EXTERNAL将日期格式转换为User Profile设置的日期格式"2.使用掩码：WRITE lv_time TO lv_char USING EDIT MASK '__:__:__'."含内外码"根据字段的convexit属性转换成外码，例如N类型前导0，项目号等"去掉前导0：SHIFT lv_char LEFT DELETING LEADING zp3ps_num_0."其他含转换的字段：WRITE lv_proj USING EDIT MASK '==ABPSN'.
复制代码
文件路径获取
获取文件保存路径
FORM get_download_file USING pv_fullpath TYPE string. DATA:lv_fname TYPE string, lv_path TYPE string, lv_def_path TYPE string.
CALL METHOD cl_gui_frontend_services=>directory_get_current “获取当前默认路径 CHANGING current_directory = lv_def_path.
CALL METHOD cl_gui_frontend_services=>file_save_dialog “弹框选择保存路径 EXPORTING window_title = 'Excel Download' default_extension = 'XLSX' file_filter = cl_gui_frontend_services=>filetype_excel initial_directory = lv_def_path CHANGING filename = lv_fname path = lv_path fullpath = pv_fullpath EXCEPTIONS cntl_error = 1 error_no_gui = 2 not_supported_by_gui = 3 invalid_default_file_name = 4 OTHERS = 5.复制代码
WINDOW_TITLE: 设置弹出屏幕标题
DEFAULT_FILE_NAME：默认文件名
WITH_ENCODING：'X'显示 Encoding 选择框
FILE_FILTER：限制文件类型
判断文件路径可用性
DATA: lv_rc TYPE i.CALL METHOD cl_gui_frontend_services=>file_exist EXPORTING file = pv_fullpath RECEIVING result = DATA(lv_res) EXCEPTIONS cntl_error = 1 error_no_gui = 2 wrong_parameter = 3 not_supported_by_gui = 4 OTHERS = 5.
IF lv_res EQ zp3ps_x. " If the file already exists... cl_gui_frontend_services=>file_delete( EXPORTING filename = pv_fullpath CHANGING rc = lv_rc ). IF sy-subrc NE 0. MESSAGE i000 WITH text-e01 DISPLAY LIKE 'E'. " File already open. check please. RETURN. ENDIF.ENDIF.
复制代码
Excel 上传
GUI
通过函数 GUI_UPLOAD 或者 cl_gui_frontend_services 下的 gui_download 方法。
该方法需要设置 codepage 来适应语言，容易出现乱码，更适合 TXT,CSV 等其他文件的上传
通过 GUI_UPLOAD 上传 TXT 文档，输出表字段类型应为字符型
根据结果表创建一个字段类型为字符型的内表
DATA:lo_table TYPE REF TO cl_abap_tabledescr, lo_struc TYPE REF TO cl_abap_structdescr, lt_data TYPE REF TO data, lv_length TYPE i.FIELD-SYMBOLS: <ft_tab> TYPE STANDARD TABLE.
lo_table ?= cl_abap_tabledescr=>describe_by_data( gt_main[] ).lo_struc ?= lo_table->get_table_line_type( ).DATA(lt_components) = lo_struc->components.
DATA(lt_fieldcat) = VALUE lvc_t_fcat( FOR ls_components IN lt_components( fieldname = ls_components-name inttype = 'C' intlen = '1024' ) ).
cl_alv_table_create=>create_dynamic_table( EXPORTING it_fieldcatalog = lt_fieldcat IMPORTING ep_table = lt_data ).ASSIGN lt_data->* TO <ft_tab>.
cl_gui_frontend_services=>gui_upload( EXPORTING filename = CONV #( pv_fname ) filetype = 'ASC' has_field_separator = 'X' codepage = '8500' IMPORTING filelength = lv_length CHANGING data_tab = <ft_tab> ).复制代码
DOI
通过函数 TEXT_CONVERT_XLS_TO_SAP 上传数据
该函数内部是通过 DOI 的方式获取数据的
获取数据可以直接存到最终需要表，但是不建议直接存，防止非法数据导致错误，例如日期时间格式等，比较适合纯文本的数据
通过 TEXT_CONVERT_XLS_TO_SAP 上传 Excel 文档
如果直接输出到日期时间字段，日期格式需满足‘YYYYMMDD’时间格式需满足‘HHMMSS’，输出到数值字段，不能有负数和千分为符
FORM upload_data_doi USING pv_fname TYPE rlgrap-filename. DATA: lt_data LIKE TABLE OF gt_main, lt_raw_data TYPE truxs_t_text_data.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP' EXPORTING i_line_header = 'X' i_tab_raw_data = lt_raw_data i_filename = pv_fname TABLES i_tab_converted_data = lt_data EXCEPTIONS conversion_failed = 1 OTHERS = 2.ENDFORM.复制代码
OLE
通过函数 ALSM_EXCEL_TO_INTERNAL_TABLE 上传数据
该函数内部是通过 OLE 的方式获取数据的
获取数据结果为行，列，值的形式，需要自行转换到内表中，且值限制为 50 位
同类扩展函数有 ZP3CMZ_ALSM_EXCEL_TO_ITAB，可以选择多个 Sheet
通过 ALSM_EXCEL_TO_INTERNAL_TABLE 上传 Excel 文档
需要指定开始和结束行列,值限制 50 位，扩展函数 ZP3CMZ_ALSM_EXCEL_TO_ITAB 放大字符限制为 200 位
DATA: lt_data LIKE TABLE OF alsmex_tabline.CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE' EXPORTING filename = pv_fname i_begin_col = '1' i_begin_row = '2' i_end_col = '9' i_end_row = '5000' TABLES intern = lt_data EXCEPTIONS inconsistent_parameters = 1 upload_ole = 2 OTHERS = 3.
复制代码
Excel 上传-文件路径获取
弹出文件选择窗口获取文件路径
FORM upload_filename_get CHANGING pv_fname TYPE rlgrap-filename pv_flag TYPE c. DATA: lv_def_path TYPE string, lt_file_table TYPE filetable, lv_rc TYPE i, lv_action TYPE i.
cl_gui_frontend_services=>directory_get_current( CHANGING current_directory = lv_def_path ).
CALL METHOD cl_gui_frontend_services=>file_open_dialog EXPORTING window_title = 'Upload' default_extension = 'XLS' default_filename = 'Export' file_filter = cl_gui_frontend_services=>filetype_excel initial_directory = lv_def_path CHANGING file_table = lt_file_table rc = lv_rc user_action = lv_action EXCEPTIONS file_open_dialog_failed = 1 cntl_error = 2 error_no_gui = 3 not_supported_by_gui = 4 OTHERS = 5. IF sy-subrc <> 0. MESSAGE s005(sbcs_send) DISPLAY LIKE zp3ps_e." Error when uploading the document pv_flag = zp3ps_x. RETURN. ENDIF. IF lv_action = cl_gui_frontend_services=>action_cancel. “用户取消上传操作 pv_flag = zp3ps_x. RETURN. ENDIF.
CHECK lt_file_table IS NOT INITIAL. READ TABLE lt_file_table INTO DATA(ls_file) INDEX 1. CHECK sy-subrc EQ 0. IF strlen( ls_file ) GE 128. “文件路径长度不可大于128 MESSAGE s000 WITH text-e02 DISPLAY LIKE zp3ps_e. RETURN. " The length of file path should be less than 128 ELSE. pv_fname = ls_file. TRANSLATE pv_fname TO UPPER CASE. ENDIF.ENDFORM.复制代码
文档选择的 Search Help
PARAMETERS: p_file LIKE rlgrap-filename DEFAULT 'C:\'.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file. CALL FUNCTION 'F4_FILENAME' EXPORTING field_name = 'C:\Actual M/M Approval.xlsx' IMPORTING file_name = p_file.
复制代码
Excel 上传数据检查
以 OLE 方式上传的数据为例，进行数据有效性检查和转化
"获取字段属性用于有效性校验FORM processing_data TABLES pt_data STRUCTURE alsmex_tabline. DATA:BEGIN OF lt_refname OCCURS 0, ref_fieldname LIKE dd03p-fieldname, ref_tabname LIKE dd03p-tabname, END OF lt_refname, lt_fieldcat TYPE slis_t_fieldcat_alv. CALL FUNCTION 'REUSE_ALV_FIELDCATALOG_MERGE' EXPORTING i_program_name = sy-repid i_internal_tabname = 'GT_MAIN' i_client_never_display = zp3ps_x i_inclname = sy-repid i_bypassing_buffer = zp3ps_x i_buffer_active = zp3ps_x CHANGING ct_fieldcat = lt_fieldcat EXCEPTIONS inconsistent_interface = 1 program_error = 2 OTHERS = 3.
MOVE-CORRESPONDING lt_fieldcat TO lt_refname[]. SORT lt_refname BY ref_tabname ref_fieldname. DELETE ADJACENT DUPLICATES FROM lt_refname COMPARING ref_tabname ref_fieldname. IF lt_refname[] IS NOT INITIAL. SELECT * FROM dd03n INTO TABLE @DATA(lt_dd03n) FOR ALL ENTRIES IN @lt_refname WHERE tabname = @lt_refname-ref_tabname AND fieldname = @lt_refname-ref_fieldname. ENDIF. "根据字段属性转化为内部格式 DATA:lt_main LIKE TABLE OF gt_main, ls_main LIKE LINE OF lt_main, lv_function TYPE rs38l_fnam. FIELD-SYMBOLS: <fv_field>.
SORT: pt_data BY row col, lt_fieldcat BY col_pos, lt_dd03n BY tabname fieldname. LOOP AT pt_data INTO DATA(ls_data). ASSIGN COMPONENT ls_data-col OF STRUCTURE ls_main TO <fv_field>. CHECK sy-subrc = 0. READ TABLE lt_fieldcat WITH KEY col_pos = ls_data-col INTO DATA(ls_fieldcat) BINARY SEARCH. CHECK sy-subrc = 0.
PERFORM check_data_type USING ls_fieldcat ls_data CHANGING ls_main.
READ TABLE lt_dd03n WITH KEY tabname = ls_fieldcat-ref_tabname fieldname = ls_fieldcat-ref_fieldname INTO DATA(ls_dd03n) BINARY SEARCH. IF ls_dd03n-convexit IS NOT INITIAL. CONDENSE ls_dd03n-convexit. CONCATENATE 'CONVERSION_EXIT_' ls_dd03n-convexit '_INPUT' INTO lv_function. CALL FUNCTION lv_function EXPORTING input = <fv_field> IMPORTING output = <fv_field>. ENDIF. AT END OF row. PERFORM convert_curr_to_internal USING lt_fieldcat CHANGING ls_main. APPEND ls_main TO lt_main. CLEAR ls_main. ENDAT. ENDLOOP. ENDFORM.
"检查数据类型是否符合(日期类型字段)FORM check_data_type USING ls_fieldcat TYPE slis_fieldcat_alv ls_data TYPE alsmex_tabline CHANGING ls_main LIKE gt_main. DATA: lv_year TYPE n LENGTH 4, lv_month TYPE n LENGTH 2, lv_day TYPE n LENGTH 3, lv_time TYPE n LENGTH 6. FIELD-SYMBOLS: <fv_field>. ASSIGN COMPONENT ls_fieldcat-fieldname OF STRUCTURE ls_main TO <fv_field>. CHECK sy-subrc = 0.
CASE ls_fieldcat-inttype. WHEN zp3ps_d. CLEAR: lv_year, lv_month, lv_day. SPLIT ls_data-value AT zgfi7_demarcation INTO lv_year lv_month lv_day. IF strlen( lv_year ) <> 4. MESSAGE s000 WITH 'date format must YYYY/MM/DD' DISPLAY LIKE zp3ps_e. RETURN. ENDIF.
<fv_field> = lv_year && lv_month && lv_day. CALL FUNCTION 'DATE_CHECK_PLAUSIBILITY' EXPORTING date = <fv_field> EXCEPTIONS plausibility_check_failed = 1 OTHERS = 2. IF sy-subrc <> 0. MESSAGE s000 WITH 'Invalid Date' DISPLAY LIKE zp3ps_e. RETURN. ENDIF. WHEN zp3ps_t. TRANSLATE ls_data-value USING ': '. CONDENSE ls_data-value NO-GAPS. lv_time = ls_data-value.
<fv_field> = lv_time. CALL FUNCTION 'TIME_CHECK_PLAUSIBILITY' EXPORTING time = <fv_field> EXCEPTIONS plausibility_check_failed = 1 OTHERS = 2. IF sy-subrc <> 0. MESSAGE s000 WITH 'Invalid Time' DISPLAY LIKE zp3ps_e. RETURN. ENDIF. WHEN zp3ps_p. REPLACE ALL OCCURRENCES OF ',' IN ls_data-value WITH ''. IF cl_abap_matcher=>matches( pattern = '^(-?([1-9]\d*|0)(\.\d*)?)$' text = ls_data-value ) = abap_false. MESSAGE s000 WITH 'Invalid data type.' DISPLAY LIKE zp3ps_e. RETURN. ENDIF.
<fv_field> = ls_data-value. WHEN OTHERS. <fv_field> = ls_data-value. ENDCASE.ENDFORM. "检查是否有金额字段，转化金额为内部格式FORM convert_curr_to_internal USING pt_fieldcat TYPE slis_t_fieldcat_alv CHANGING ps_main LIKE gt_main. DATA:lv_amount TYPE bapicurr-bapicurr, lv_waers TYPE tcurc-waers, lv_length TYPE i. FIELD-SYMBOLS: <fv_amount> TYPE any, <fv_waers> TYPE any.
READ TABLE pt_fieldcat WITH KEY datatype = 'CURR' TRANSPORTING NO FIELDS. IF sy-subrc = 0. LOOP AT pt_fieldcat INTO DATA(ls_fieldcat) WHERE datatype = 'CURR'. ASSIGN COMPONENT ls_fieldcat-fieldname OF STRUCTURE ps_main TO <fv_amount>. ASSIGN COMPONENT ls_fieldcat-cfieldname OF STRUCTURE ps_main TO <fv_waers>. lv_length = ls_fieldcat-intlen. IF <fv_waers> IS NOT INITIAL AND <fv_amount> IS NOT INITIAL. lv_amount = <fv_amount>. lv_waers = <fv_waers>.
CALL FUNCTION 'BAPI_CURRENCY_CONV_TO_INTERNAL' EXPORTING currency = lv_waers amount_external = lv_amount max_number_of_digits = lv_length IMPORTING amount_internal = <fv_amount>. ENDIF. ENDLOOP. ENDIF.ENDFORM. " CONVERT_CURR_TO_INTERNAL复制代码
划线
评论
复制
发布于: 刚刚阅读数: 3
桥下本有油菜花
关注
还未添加个人签名 2022.06.08 加入
还未添加个人简介
评论