22
\$\begingroup\$

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.
\$\endgroup\$
2
\$\begingroup\$

I would recommend using CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

I do not think there is an option to use field symbols though.

If a field symbol contains internal table data you can use ASSIGN COMPONENT sy-index. The values are retrieved one by one as well.

This is one example to convert the data to internal table from excel sheet.Using 'ALSM_EXCEL_TO_INTERNAL_TABLE' function module internal table contains the data like ROW, COL,VALUE. So now we have to convert it to the actual internal table structure.

Below is the internal table structure.

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.

Below is how to convert the excel sheet data to internal table. You need to take the entire ROW with all the columns one by one and move it to one variable . Here I took LW_LINE . LW_LINE contains the values of the one ROW of actual internal table structure .After getting all the ROW values append it to the actual internal table.

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

Examples came from Rajitha

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.