How to solve the problem of re-encapsulation of EXCEL upload function ALSM_EXCEL_TO_INTERNAL_TABLE
This article mainly introduces how to solve the EXCEL upload function ALSM_EXCEL_TO_INTERNAL_TABLE re-encapsulation problem, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.
ALSM_EXCEL_TO_INTERNAL_TABLE has two restrictions:
1. Each CELL can only import the first 50 characters.
2. If there are more than 9999 lines, the line number will be initialized to start from zero
Solution:
1. COPY ALSM_EXCEL_TO_INTERNAL_TABLE is ZALSM_EXCEL_TO_INTERNAL_TABLE, and you can make some changes
2. Define the structure and return ZSALSMEX_TABLINE.
3. Function input and output
The code is as follows (defined with ZEXCEL custom function group to facilitate code management):
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. "use default mode
Get property of application 'ACTIVESHEET' = worksheet.
M_message.
Else.
*-- > you can read multiple 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.
*
