Skip to Content
avatar image
Former Member

i_oi_spreadsheet number of rows in Excel sheet.

Hello.

I am using i_oi_spreadsheet interface to read content of Excel files. I have decided to use this interface, because I need to have access to several sheets defined in Excel file. Unfortunately method get_ranges_data which I used, requires to define range of data (cells) which will be read.

I can predict how many columns will be in each sheet, but I can not predict how many rows will be in each sheet.

Is there any way to read number of rows in each sheet in Excel file? Or any way to select all data in sheet?

Below I have add part of my code for reading content of sheets to show the way how I have done it for now.

DATA:  l_rows  TYPE i VALUE 30,

       l_cols  TYPE i VALUE 500.

CALL METHOD iref_spreadsheet->set_selection
         EXPORTING
           top     = 1
           left    = 1
           rows    = l_rows
           columns = l_cols.

CALL METHOD iref_spreadsheet->insert_range
         EXPORTING
           name     = 'Test'
           rows     = l_rows
           columns  = l_cols
           no_flush = ''
         IMPORTING
           error    = iref_error.
       IF iref_error->has_failed = 'X'.
         EXIT.
       ENDIF.


CALL METHOD iref_spreadsheet->get_ranges_data
         EXPORTING
           all      = 'X'
         IMPORTING
           contents = lt_data       " in this table will be data from cells 30 x 500
           error    = iref_error
         CHANGING
           ranges   = i_ranges.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Apr 17, 2013 at 03:17 PM

    Hi Adam,

    When I have used this in the past it has been on a spread sheet where the extent of the cells to process is already defined in a range.

    If you're not familiar with the functionality in excel you highlight the cell and overtype the cell number with the range name (see screenshot, the populated cells are the ones highlighted)

    So instead of setting the range in your program you pass just the range name to the method get_ranges_data.  The size of the range is defined in the spread sheet and all cells in this range are read.

    This all of course depends on you being able to influence the format of the spread sheet you're uploading.

    Regards,

    Nick

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hello Nick,

      Thank you for your advice. I've spend a few days on this problem and finally I figured out how solve this problem.

      I didn't find any SAP method in SOFFICEINTEGRATION package which allows to count rows with data in your Excel sheet,

      but I wrote some part of code which sequentially reads few rows from sheet and then check is that any empty rows.

      If read data has empty rows, reading process is stopped in otherwise read data has been stored and next part of data

      is reading from sheet.

      Here's small example:

      CONSTANTS:

         lc_rows_step TYPE i VALUE 50.    " of course we can change this value

      l_is_done = ' '.

      l_top = 1.

      l_rows = lc_rows_step.

      WHILE l_is_done = 'X'.

         ls_range-name = 'TmpRangeName'.

         ls_range-rows = l_rows.

         ls_range-columns = 10.

         APPEND ls_range TO lt_range.

      * It's quite important to set selection before inserting range,

      *   if you skip this step you can not control which cells of sheet

      *   will be selected to new range.

         CALL METHOD iref_spreadsheet->set_selection

            EXPORTING

               left    = 1

               top     = l_top

               rows    = l_rows

               columns = l_cols.

         CALL METHOD iref_spreadsheet->insert_range

            EXPORTING

               columns = l_cols

               rows = l_rows

               name = 'TmpRangeName'.

      * Do not set flag all, if any filters will be defined in Excel sheet,

      *   you accidentally read some extra data (in lt_content).        

         CALL METHOD iref_spreadsheet->get_ranges_data

      *     EXPORTING

      *        all = ''

            IMPORTING

               contents = lt_content

            CHANGING

               ranges = lt_ranges.

      * Now delete current range, in next step of WHILE loop,

      *   new range will be define.

         CALL METHOD iref_spreadsheet->delete_ranges

            EXPORTING

               ranges = lt_ranges.

      * And now we should check that is there any empty rows in lt_content

         DO lc_ TIMES.

            l_is_empty = 'X'.

            LOOP AT lt_content TRANSPORTING NO FIELDS WHERE row = lc_rows_step AND value <> ''.

               l_is_empty = ' '.

               EXIT.

            ENDLOOP.

            IF l_is_empty = 'X'.

               l_is_done = 'X'.

            ENDIF.

         ENDDO.

         ADD lc_rows_step TO l_top.    " update l_top to select next part of cells.

      ******

      * And in this place, we ought to copy content of lt_content to another internal table

      *   with new row numbers.

      *****

      ENDWHILE.

      End this is all. Please note that we do not need to change parameters of insert_range method,

      we need to change only selection of cells in our sheet.

      I hope it helps.

      Nick, once more thanks for help,

      Best regards,

      Adam