如何解决EXCEL上传函数ALSM_EXCEL_TO_INTERNAL_TABLE的重新封装问题
如何解决EXCEL上传函数ALSM_EXCEL_TO_INTERNAL_TABLE的重新封装问题
这篇文章主要介绍了如何解决EXCEL上传函数ALSM_EXCEL_TO_INTERNAL_TABLE的重新封装问题，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。
ALSM_EXCEL_TO_INTERNAL_TABLE有两个限制：
1、 每个CELL只能导入前50个字符；
2、 如果超过9999行，行号会初始化为从零开始
解决办法：
1、 COPY ALSM_EXCEL_TO_INTERNAL_TABLE 为 ZALSM_EXCEL_TO_INTERNAL_TABLE ，并做少许改动即可
2、 定义结构新的返回ZSALSMEX_TABLINE
3、函数输入输出
代码如下（定义与ZEXCEL自定义函数组下，便于代码管理）：
function zalsm_excel_to_internal_table.
*"———————————————————————-
*"*"Local interface:
*" IMPORTING
*" VALUE(FILENAME) LIKE RLGRAP-FILENAME
*" VALUE(I_BEGIN_COL) TYPE INT4
*" VALUE(I_BEGIN_ROW) TYPE INT4
*" VALUE(I_END_COL) TYPE INT4
*" VALUE(I_END_ROW) TYPE INT4
*" VALUE(I_SHEET_NO) TYPE I OPTIONAL
*" TABLES
*" INTERN STRUCTURE ZSALSMEX_TABLINE
*"———————————————————————-
data: excel_tab type ty_t_sender.
data: ld_separator type c.
data: application type ole2_object,
workbook type ole2_object,
range type ole2_object,
worksheet type ole2_object.
data: h_cell type ole2_object,
h_cell1 type ole2_object.
data:
ld_rc type i.
* Rückgabewert der Methode "clipboard_export "
* Makro für Fehlerbehandlung der Methods
define m_message.
case sy-subrc.
when 0.
when 1.
message id sy-msgid type sy-msgty number sy-msgno
with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
when others. raise upload_ole.
endcase.
end-of-definition.
* check parameters
if i_begin_row > i_end_row. raise inconsistent_parameters. endif.
if i_begin_col > i_end_col. raise inconsistent_parameters. endif.
* Get TAB-sign for separation of fields
class cl_abap_char_utilities definition load.
ld_separator = cl_abap_char_utilities=>horizontal_tab.
* open file in Excel
if application-header = space or application-handle = -1.
create object application 'Excel.Application'.
m_message.
endif.
call method of application 'Workbooks' = workbook.
m_message.
call method of workbook 'Open'
exporting
#1 = filename.
m_message.
* set property of application 'Visible' = 1.
* m_message.
if i_sheet_no = space."用默认模式
get property of application 'ACTIVESHEET' = worksheet.
m_message.
else.
*–>可以实现读取多个sheet
call method of application 'WORKSHEETS' = worksheet
exporting
#1 = i_sheet_no.
call method of worksheet 'Activate'.
m_message.
endif.
* mark whole spread sheet
call method of worksheet 'Cells' = h_cell
exporting
#1 = i_begin_row
#2 = i_begin_col.
m_message.
call method of worksheet 'Cells' = h_cell1
exporting
#1 = i_end_row
#2 = i_end_col.
m_message.
call method of worksheet 'RANGE' = range
exporting
#1 = h_cell
#2 = h_cell1.
m_message.
call method of range 'SELECT'.
m_message.
* copy marked area (whole spread sheet) into Clippboard
call method of range 'COPY'.
m_message.
* read clipboard into ABAP
call method cl_gui_frontend_services=>clipboard_import
importing
data = excel_tab
exceptions
cntl_error = 1
* ERROR_NO_GUI = 2
* NOT_SUPPORTED_BY_GUI = 3
others = 4.
if sy-subrc <> 0.
message a037(alsmex).
endif.
perform separated_to_intern_convert tables excel_tab intern
using ld_separator.
* clear clipboard
refresh excel_tab.
call method cl_gui_frontend_services=>clipboard_export
importing
data = excel_tab
changing
rc = ld_rc
exceptions
cntl_error = 1
* ERROR_NO_GUI = 2
* NOT_SUPPORTED_BY_GUI = 3
others = 4.
* quit Excel and free ABAP Object – unfortunately, this does not kill
* the Excel process
call method of application 'QUIT'.
m_message.
* >>>>> Begin of change note 575877
* to kill the Excel process it's necessary to free all used objects
free object h_cell. m_message.
free object h_cell1. m_message.
free object range. m_message.
free object worksheet. m_message.
free object workbook. m_message.
free object application. m_message.
* <<<<< End of change note 575877
endfunction.
全变变量：LZEXCELTOP
FUNCTION-POOL ZEXCEL. "MESSAGE-ID ..
***下载EXCEL
include ole2incl.
data: gv_excel type ole2_object,
gv_workbook type ole2_object,
gv__map type ole2_object,
gv_sheet type ole2_object,
gv_newsheet type ole2_object,
gs_cell1 type ole2_object,
gs_cell2 type ole2_object,
gs_range type ole2_object.
type-pools kcdu.
****上传EXCLE
*DATA: dy_line TYPE REF TO data.
*FIELD-SYMBOLS: <dyn_wa>,
* <dyn_value> TYPE any.
********************************************
type-pools: ole2.
* value of excel-cell
types: ty_d_itabvalue type zsalsmex_tabline-value,
* internal table containing the excel data
ty_t_itab type zsalsmex_tabline occurs 0,
* line type of sender table
begin of ty_s_senderline,
line(4096) type c,
end of ty_s_senderline,
* sender table
ty_t_sender type ty_s_senderline occurs 0.
*
constants: gc_esc value '"'.
types:
begin of ty_kcdu_srec,
srec type c length 2048,
end of ty_kcdu_srec.
types ty_t_kcdu_srec type table of ty_kcdu_srec.
types ty_ztsalsmex_tab_6 type table of zsbc_csv_tab.
constants: c_comma value ',',
c_point value '.',
c_esc value '"'.
constants: c_separator type char1 value ',',
c_quo type char1 value '"'.
全局form：LZEXCELF01
*———————————————————————-*
***INCLUDE LZEXCELF01 .
*———————————————————————-*
*&———————————————————————*
*& Form separated_to_intern_convert
*&———————————————————————*
* text
*———————————————————————-*
* –>P_EXCEL_TAB text
* –>P_INTERN text
* –>P_LD_SEPARATOR text
*———————————————————————-*
form separated_to_intern_convert tables i_tab type ty_t_sender
i_intern type ty_t_itab
using i_separator type c.
data: l_sic_tabix like sy-tabix,
l_sic_col type kcd_ex_col.
data: l_fdpos like sy-fdpos.
refresh i_intern.
loop at i_tab.
l_sic_tabix = sy-tabix.
l_sic_col = 0.
while i_tab ca i_separator.
l_fdpos = sy-fdpos.
l_sic_col = l_sic_col + 1.
perform line_to_cell_separat tables i_intern
using i_tab l_sic_tabix l_sic_col
i_separator l_fdpos.
endwhile.
if i_tab <> space.
clear i_intern.
i_intern-row = l_sic_tabix.
i_intern-col = l_sic_col + 1.
i_intern-value = i_tab.
append i_intern.
endif.
endloop.
endform. " SEPARATED_TO_INTERN_CONVERT
*———————————————————————*
form line_to_cell_separat tables i_intern type ty_t_itab
using i_line
i_row like sy-tabix
ch_cell_col type kcd_ex_col
i_separator type c
i_fdpos like sy-fdpos.
data: l_string type ty_s_senderline.
data l_sic_int type i.
clear i_intern.
l_sic_int = i_fdpos.
i_intern-row = i_row.
l_string = i_line.
i_intern-col = ch_cell_col.
* csv Dateien mit separator in Zelle: –> ;"abc;cd";
if ( i_separator = ';' or i_separator = ',' ) and
l_string(1) = gc_esc.
perform line_to_cell_esc_sep using l_string
l_sic_int
i_separator
i_intern-value.
else.
if l_sic_int > 0.
i_intern-value = i_line(l_sic_int).
endif.
endif.
if l_sic_int > 0.
append i_intern.
endif.
l_sic_int = l_sic_int + 1.
i_line = i_line+l_sic_int.
endform. "line_to_cell_separat
*———————————————————————*
form line_to_cell_esc_sep using i_string
i_sic_int type i
i_separator type c
i_intern_value type ty_d_itabvalue.
data: l_int type i,
l_cell_end(2).
field-symbols: <l_cell>.
l_cell_end = gc_esc.
l_cell_end+1 = i_separator .
if i_string cs gc_esc.
i_string = i_string+1.
if i_string cs l_cell_end.
l_int = sy-fdpos.
assign i_string(l_int) to <l_cell>.
i_intern_value = <l_cell>.
l_int = l_int + 2.
i_sic_int = l_int.
i_string = i_string+l_int.
elseif i_string cs gc_esc.
* letzte Celle
l_int = sy-fdpos.
assign i_string(l_int) to <l_cell>.
i_intern_value = <l_cell>.
l_int = l_int + 1.
i_sic_int = l_int.
i_string = i_string+l_int.
l_int = strlen( i_string ).
if l_int > 0 . message x001(kx) . endif.
else.
message x001(kx) . "was ist mit csv-Format
endif.
endif.
endform. "line_to_cell_esc_sep
感谢你能够认真阅读完这篇文章，希望小编分享的“如何解决EXCEL上传函数ALSM_EXCEL_TO_INTERNAL_TABLE的重新封装问题”这篇文章对大家有帮助，同时也希望大家多多支持高防服务器网，关注高防服务器网行业资讯频道，更多相关知识等着你来学习!
[微信提示：高防服务器能助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。
[图文来源于网络，不代表本站立场，如有侵权，请联系高防服务器网删除]
[