Shulou(Shulou.com)06/01 Report--

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.

*

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.