I wrote this code many years ago to analyze Excel data coming in from the clipboard.
Please review for performance and maintainability concerns.
One minor note, from an OO perspective, a class level property is pretty much global level as every method has access, so these are prefixed with 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.
