请查看性能和可维护性方面的问题。
从面向对象的角度讲，一个小注释，类级别的属性几乎是全局级别的，因为每个方法都可以访问，因此这些前缀带有
G_ class ZCL_RM_EXCEL_DATA definition
public
final
create public .
public section.
*"* public components of class ZCL_RM_EXCEL_DATA
*"* do not include other source files here!!!
methods CONSTRUCTOR
importing
!P_DATA type ZRMTT_ALSMEX_TABLINE .
methods RANGE_TO_LIST
importing
!P_RANGE type STRING
!P_VECTOR type I default 1
preferred parameter P_RANGE
returning
value(P_OUT) type STRINGTAB .
class-methods DERIVE_LOCATION
importing
value(P_CELL) type STRING
exporting
!P_COL type I
!P_ROW type I .
class-methods COLUMN_TO_INT
importing
!P_COL type STRING
returning
value(P_OUT) type I .
class-methods INT_TO_COLUMN
importing
!P_COL type I
returning
value(P_OUT) type STRING .
methods GET_BOUNDARIES
exporting
!P_TOP_ROW type I
!P_BOTTOM_ROW type I
!P_LEFT_MOST_COL type I
!P_RIGHT_MOST_COL type I .
methods RANGE
importing
!P_FROM_ROW type I
!P_TO_ROW type I
!P_FROM_COL type I
!P_TO_COL type I
returning
value(P_RANGE) type STRING .
methods GET_CELL_VALUE
importing
!P_CELL type STRING
returning
value(P_VALUE) type STRING .
protected section.
*"* protected components of class ZCL_RM_EXCEL_DATA
*"* do not include other source files here!!!
private section.
*"* private components of class ZCL_RM_EXCEL_DATA
*"* do not include other source files here!!!
data G_DATA type ZRMTT_ALSMEX_TABLINE .
data G_TOP_ROW type I .
data G_BOTTOM_ROW type I .
data G_LEFT_MOST_COL type I .
data G_RIGHT_MOST_COL type I .
ENDCLASS.
CLASS ZCL_RM_EXCEL_DATA IMPLEMENTATION.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method ZCL_RM_EXCEL_DATA=>COLUMN_TO_INT
* +-------------------------------------------------------------------------------------------------+
* | [--->] P_COL TYPE STRING
* | [<-()] P_OUT TYPE I
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD column_to_int.
*****************************************************************************
* Description : Convert a column ( D ) into '4' *
*****************************************************************************
DATA: l_col_char(3) TYPE c.
DATA: l_length TYPE i.
DATA: l_char TYPE c.
DATA: l_index TYPE i.
"This magic is required to achieve what ASC & CHR would do in VBa
FIELD-SYMBOLS: <dummy> TYPE x.
"Cast from string to char so that we can use positional offsets
l_col_char = p_col.
TRANSLATE l_col_char TO UPPER CASE.
l_length = strlen( l_col_char ).
"We require to do this l_length times to support columns like 'AZ'
DO l_length TIMES.
l_index = sy-index - 1.
l_char = l_col_char+l_index(1).
ASSIGN l_char TO <dummy> CASTING TYPE x.
"26 because there are 26 letters in the alphabet
"256 because we need to shift the 2byte value to the right
"64 because 65 -> A in ASCII, and 65-64 = 1, is column 1
p_out = p_out * 26 + ( ( <dummy> / 256 ) - 64 ).
ENDDO.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_RM_EXCEL_DATA->CONSTRUCTOR
* +-------------------------------------------------------------------------------------------------+
* | [--->] P_DATA TYPE ZRMTT_ALSMEX_TABLINE
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD constructor.
*****************************************************************************
* Description : This class provides helper methods to analyze data coming *
* from an excel sheet that was copied into the buffer and then*
* read from the buffer into an internal table of type *
* alsmex_tabline. *
* *
* The standard SAP does not promise to give the data sorted in*
* any way, hence the silly looping over all the data to find *
* the bottom row, top row, left most and right most column *
* *
*****************************************************************************
DATA: wa_data TYPE alsmex_tabline. "ROW,COL,VALUE
"Take over the data
g_data = p_data.
"Sane defaults for cell boundaries
g_bottom_row = g_right_most_col = 0.
g_top_row = g_left_most_col = 2147483647. "MAXINT
"We will loop over this once
LOOP AT g_data INTO wa_data.
IF wa_data-row GT g_bottom_row.
g_bottom_row = wa_data-row.
ENDIF.
IF wa_data-col GT g_right_most_col.
g_right_most_col = wa_data-col.
ENDIF.
IF wa_data-row LT g_top_row.
g_top_row = wa_data-row.
ENDIF.
IF wa_data-col LT g_left_most_col.
g_left_most_col = wa_data-col.
ENDIF.
ENDLOOP.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method ZCL_RM_EXCEL_DATA=>DERIVE_LOCATION
* +-------------------------------------------------------------------------------------------------+
* | [--->] P_CELL TYPE STRING
* | [<---] P_COL TYPE I
* | [<---] P_ROW TYPE I
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD derive_location.
*****************************************************************************
* Description : This method will derive from a string a column and a row *
* This string can contain several formats: *
* '123' -> Row 123 , Column -1 *
* 'AB' -> Column 27 , Row -1 *
* 'AB123' -> Row 123 , Column 25 *
*****************************************************************************
DATA: l_len TYPE i.
DATA: l_row TYPE string.
DATA: l_col TYPE string.
TRANSLATE p_cell TO UPPER CASE.
"We are having a row indicator
IF p_cell CO '0123456789'.
p_row = p_cell.
p_col = -1.
RETURN.
ENDIF.
"We are having a col indicator
IF p_cell NA '0123456789'.
p_row = -1.
p_col = column_to_int( p_cell ).
RETURN.
ENDIF.
"We having a colum/row cell, the hardest case
IF p_cell CA '123456789'. "This is just to get the row into SY-FDPOS
p_row = p_cell+sy-fdpos.
l_col = p_cell(sy-fdpos).
p_col = column_to_int( l_col ).
ENDIF.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_RM_EXCEL_DATA->GET_BOUNDARIES
* +-------------------------------------------------------------------------------------------------+
* | [<---] P_TOP_ROW TYPE I
* | [<---] P_BOTTOM_ROW TYPE I
* | [<---] P_LEFT_MOST_COL TYPE I
* | [<---] P_RIGHT_MOST_COL TYPE I
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD get_boundaries.
*****************************************************************************
* Description : This method simply exposes private properties to the caller *
*****************************************************************************
"Just pass it!
p_top_row = g_top_row.
p_bottom_row = g_bottom_row.
p_left_most_col = g_left_most_col.
p_right_most_col = g_right_most_col.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_RM_EXCEL_DATA->GET_CELL_VALUE
* +-------------------------------------------------------------------------------------------------+
* | [--->] P_CELL TYPE STRING
* | [<-()] P_VALUE TYPE STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD get_cell_value.
*****************************************************************************
* Description : This method will parse the string-location into row, column,*
* read the table entry and pass the value back *
* As in a real excel file, cells that are not filled in, are *
* considered/returned as blank *
*****************************************************************************
DATA: wa_data TYPE alsmex_tabline. "ROW,COL,VALUE
DATA: l_row TYPE i.
DATA: l_col TYPE i.
derive_location( EXPORTING p_cell = p_cell IMPORTING p_col = l_col p_row = l_row ).
READ TABLE g_data INTO wa_data WITH KEY row = l_row
col = l_col.
p_value = wa_data-value.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method ZCL_RM_EXCEL_DATA=>INT_TO_COLUMN
* +-------------------------------------------------------------------------------------------------+
* | [--->] P_COL TYPE I
* | [<-()] P_OUT TYPE STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD int_to_column.
*****************************************************************************
* Description : This method is the opposite of COLUMN_TO_INT and will *
* convert an integer into a character. 2 -> B *
*****************************************************************************
"CHR , ASC functionality must pass through hex magic
FIELD-SYMBOLS: <dummy> TYPE c.
DATA: i(4) TYPE x.
DATA: l_target TYPE i.
DATA: l_rest TYPE string.
"We are doing this in a recursive manner,
"to understand recursive coding, one must start by understanding recursive coding
IF p_col LT 27.
" 64 because A starts at 65 and is value 1, 256 because now we need to shift to the left
i = ( 64 + p_col ) * 256.
ASSIGN i TO <dummy> CASTING TYPE c.
p_out = <dummy>+1.
ELSE.
i = ( 64 + p_col MOD 26 ) * 256.
ASSIGN i TO <dummy> CASTING TYPE c.
p_out = <dummy>+1.
"26 because there are 26 characters in the alphabet
l_target = ( p_col - p_col MOD 26 ) / 26.
l_rest = int_to_column( l_target ).
CONCATENATE l_rest p_out INTO p_out.
ENDIF.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_RM_EXCEL_DATA->RANGE
* +-------------------------------------------------------------------------------------------------+
* | [--->] P_FROM_ROW TYPE I
* | [--->] P_TO_ROW TYPE I
* | [--->] P_FROM_COL TYPE I
* | [--->] P_TO_COL TYPE I
* | [<-()] P_RANGE TYPE STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD range.
*****************************************************************************
* Description : This method will convert any a topleft and bottomright *
* coordinate into the correct Excel range notations to *
* Note the assumptions in the code, the caller has some *
* responsability in calling this correctly. *
*****************************************************************************
DATA: l_left TYPE string.
DATA: l_right TYPE string.
DATA: l_max_left TYPE string.
DATA: l_max_right TYPE string.
DATA: l_top_row TYPE string.
DATA: l_bottom_row TYPE string.
DATA: l_from_row TYPE string.
DATA: l_to_row TYPE string.
l_left = int_to_column( p_from_col ).
l_right = int_to_column( p_to_col ).
l_max_left = int_to_column( g_left_most_col ).
l_max_right = int_to_column( g_left_most_col ).
l_top_row = g_top_row.
l_bottom_row = g_bottom_row.
l_from_row = p_from_row.
l_to_row = p_to_row.
CONDENSE: l_bottom_row , l_top_row , l_from_row , l_to_row .
"4 cases, everything, row to row, col to col, row/col to row/col
"Case 1, everything
IF p_from_col EQ -1 AND p_from_row EQ -1 AND p_to_col EQ -1 AND p_to_row EQ -1.
CONCATENATE l_max_left l_top_row ':' l_max_right l_bottom_row INTO p_range.
RETURN.
ENDIF.
"Case 2, row to row, ASSUMING that from is smaller than to
IF p_from_col EQ -1 AND p_to_col EQ -1 AND p_from_row NE -1 AND p_to_row NE -1.
CONCATENATE l_from_row ':' l_to_row INTO p_range.
RETURN.
ENDIF.
"Case 3, col to col, ASSUMING that from is smaller than to
IF p_from_col NE -1 AND p_to_col NE -1 AND p_from_row EQ -1 AND p_to_row EQ -1.
CONCATENATE l_left ':' l_right INTO p_range.
RETURN.
ENDIF.
"Case 4, cell to cell, ASSUMING that from is smaller than to
IF p_from_col NE -1 AND p_to_col NE -1 AND p_from_row NE -1 AND p_to_row NE -1.
CONCATENATE l_left l_from_row ':' l_right l_to_row INTO p_range.
ENDIF.
ENDMETHOD.
* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Instance Public Method ZCL_RM_EXCEL_DATA->RANGE_TO_LIST
* +-------------------------------------------------------------------------------------------------+
* | [--->] P_RANGE TYPE STRING
* | [--->] P_VECTOR TYPE I (default =1)
* | [<-()] P_OUT TYPE STRINGTAB
* +--------------------------------------------------------------------------------------</SIGNATURE>
METHOD range_to_list.
*****************************************************************************
* Description : This method will collect all cells in a given range *
*****************************************************************************
DATA: l_from TYPE string,
l_to TYPE string.
DATA: l_from_row TYPE i,
l_from_col TYPE i,
l_to_row TYPE i,
l_to_col TYPE i.
DATA: wa_data TYPE alsmex_tabline. "ROW,COL,VALUE
SPLIT p_range AT ':' INTO l_from l_to.
IF sy-subrc NE 0.
RETURN.
ENDIF.
derive_location( EXPORTING p_cell = l_from IMPORTING p_col = l_from_col p_row = l_from_row ).
derive_location( EXPORTING p_cell = l_to IMPORTING p_col = l_to_col p_row = l_to_row ).
"4 cases, everything, row to row, col to col, row/col to row/col
"Case 1, everything
IF l_from_col EQ -1 AND l_from_row EQ -1 AND l_to_col EQ -1 AND l_to_row EQ -1.
LOOP AT g_data INTO wa_data.
APPEND wa_data-value TO p_out.
ENDLOOP.
RETURN.
ENDIF.
"Case 2, row to row, ASSUMING that from is smaller than to
IF l_from_col EQ -1 AND l_to_col EQ -1 AND l_from_row NE -1 AND l_to_row NE -1.
LOOP AT g_data INTO wa_data WHERE row GE l_from_row AND row LE l_to_row.
APPEND wa_data-value TO p_out.
ENDLOOP.
RETURN.
ENDIF.
"Case 3, col to col, ASSUMING that from is smaller than to
IF l_from_col NE -1 AND l_to_col NE -1 AND l_from_row EQ -1 AND l_to_row EQ -1.
LOOP AT g_data INTO wa_data WHERE col GE l_from_col AND col LE l_to_col.
APPEND wa_data-value TO p_out.
ENDLOOP.
RETURN.
ENDIF.
"Case 4, cell to cell, ASSUMING that from is smaller than to
IF l_from_col NE -1 AND l_to_col NE -1 AND l_from_row NE -1 AND l_to_row NE -1.
LOOP AT g_data INTO wa_data WHERE col GE l_from_col AND col LE l_to_col
AND row GE l_from_row AND row LE l_to_row.
APPEND wa_data-value TO p_out.
ENDLOOP.
RETURN.
ENDIF.
ENDMETHOD.
ENDCLASS.
#1 楼
我建议使用CALL FUNCTION'ALSM_EXCEL_TO_INTERNAL_TABLE'我不认为可以使用字段符号。
如果字段符号包含内部表数据，则可以使用分配组件sy-index。值也一一检索。
这是一个将数据从excel表转换为内部表的示例。使用'ALSM_EXCEL_TO_INTERNAL_TABLE'功能模块内部表包含ROW，COL，VALUE等数据。因此现在我们必须将其转换为实际的内部表结构。
下面是内部表结构。
BEGIN OF WA_BKPF,
BUKRS LIKE BKPF-BUKRS, " company Code
BELNR LIKE BKPF-BELNR, " Accounting Document Number
GJAHR LIKE BKPF-GJAHR, " Fiscal Year
BLART LIKE BKPF-BLART, " Document type
BLDAT LIKE BKPF-BLDAT, " Document Date in Document
BUDAT LIKE BKPF-BUDAT, " Posting Date in the Document
MONAT LIKE BKPF-MONAT, " Fiscal Period
CPUDT LIKE BKPF-CPUDT, " Accounting Document Entry Date
END OF WA_BKPF.
下面是如何将Excel工作表数据转换为内部表。您需要一张一张地接所有列的整个ROW，并将其移到一个变量中。在这里我拍了LW_LINE。 LW_LINE包含实际内部表结构的一个ROW的值。获取所有ROW值后，将其附加到实际内部表中。
FORM UPLOAD .
DATA:
LW_ROW TYPE I, " Index to read t_value table
LW_LINE(100) TYPE C, " Holds VALUE from t_rowcol
LW_COMPONENTS TYPE I, " Nimber of fields in t_bkpf
LW_TYPE TYPE C, " Holds Field type of fs_bkpf
LW_TIMES TYPE I. " No of rows in internal table
* finding no.of fields in fs_bkpf structure
DESCRIBE FIELD FS_BKPF TYPE LW_TYPE COMPONENTS LW_COMPONENTS.
*describe table t_bkpf.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
FILENAME = W_FNAME1
I_BEGIN_COL = 1
I_BEGIN_ROW = 1
I_END_COL = LW_COMPONENTS
I_END_ROW = 50
TABLES
INTERN = T_ROWCOL
EXCEPTIONS
INCONSISTENT_PARAMETERS = 1
UPLOAD_OLE = 2
OTHERS = 3.
IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF. " IF SY-SUBRC <> 0.
* Finding no.of rows in T_ROWCOL internal table
DESCRIBE TABLE T_ROWCOL.
LW_TIMES = SY-TFILL DIV LW_COMPONENTS.
LW_ROW = 0.
DO LW_TIMES TIMES.
LW_ROW = LW_ROW + 1.
LOOP AT T_ROWCOL WHERE ROW = LW_ROW .
CONCATENATE LW_LINE
T_ROWCOL-VALUE
INTO LW_LINE
SEPARATED BY SPACE.
ENDLOOP. " LOOP AT T_ROWCOL...
SHIFT LW_LINE LEFT.
* Separating w_line into fields of T_BKPF
SPLIT LW_LINE AT SPACE INTO FS_BKPF-BUKRS
FS_BKPF-BELNR
FS_BKPF-GJAHR
FS_BKPF-BLART
FS_BKPF-BLDAT
FS_BKPF-BUDAT
FS_BKPF-MONAT
FS_BKPF-CPUDT.
* Appending each row value from excel file to t_bkpf
APPEND FS_BKPF TO T_BKPF.
CLEAR : LW_LINE,
FS_BKPF.
ENDDO. " DO LW_TIMES TIMES.
* Displaying data from internal table
LOOP AT T_BKPF INTO FS_BKPF.
WRITE :/
FS_BKPF-BUKRS UNDER TEXT-001,
FS_BKPF-BELNR UNDER TEXT-002,
FS_BKPF-GJAHR UNDER TEXT-003,
FS_BKPF-BLART UNDER TEXT-004,
FS_BKPF-BLDAT UNDER TEXT-005,
FS_BKPF-BUDAT UNDER TEXT-006,
FS_BKPF-MONAT UNDER TEXT-007,
FS_BKPF-CPUDT UNDER TEXT-008.
ENDLOOP. " LOOP AT T_BKPF INTO FS_BKPF.
ENDFORM. " UPLOAD
示例来自Rajitha
评论
\ $ \ begingroup \ $
ALSM_EXCEL_TO_INTERNAL_TABLE与问题有何关系？
\ $ \ endgroup \ $
–桑德拉·罗西（Sandra Rossi）
19年8月24日在6:17