导航：首页 > 互联网科技 >

如何解决EXCEL上传函数ALSM_EXCEL_TO_INTERNAL_TABLE的重新封装问题

发表于：2026-11-02 作者：千家信息网编辑

千家信息网最后更新 2026年11月02日，这篇文章主要介绍了如何解决EXCEL上传函数ALSM_EXCEL_TO_INTERNAL_TABLE的重新封装问题，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让

千家信息网最后更新 2026年11月02日如何解决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: ,
* 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_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 .
i_intern_value = .
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 .
i_intern_value = .
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