Problem in uploading the excel file

[Former Member]

Hi,

I checked with FM KCD_EXCEL_OLE_TO_INT_CONVERT and ALSM_EXCEL_TO_INTERNAL_TABLE to upload the excel sheet data into internal table, but both displays a pop-up when execute the report with message like (<b>Do you want to save the changes made to excel file</b>)

Can any one know how to avoid this pop-up? or is there any other way to upload the excel file.

Regards,

Kumar

[Former Member]

Hi,

I checked with FM KCD_EXCEL_OLE_TO_INT_CONVERT and ALSM_EXCEL_TO_INTERNAL_TABLE to upload the excel sheet data into internal table, but both displays a pop-up when execute the report with message like (<b>Do you want to save the changes made to excel file</b>)

Can any one know how to avoid this pop-up? or is there any other way to upload the excel file.

Regards,

Kumar

[rahulkavuri]

check this code, i found this most performance oriented, reward points if found helpfu;

REPORT  Y_EXCEL.

*Data Declaration
*----------------
DATA: ITAB LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.
* Has the following format:
*             Row number   | Colum Number   |   Value
*             ---------------------------------------
*      i.e.     1                 1             Name1
*               2                 1             Joe

TYPES: BEGIN OF T_RECORD,
    NAME1 LIKE ITAB-VALUE,
    NAME2 LIKE ITAB-VALUE,
*    age   like itab-value,
    END OF T_RECORD.
DATA: IT_RECORD TYPE STANDARD TABLE OF T_RECORD INITIAL SIZE 0,
      WA_RECORD TYPE T_RECORD.
DATA: GD_CURRENTROW TYPE I.

*Selection Screen Declaration
*----------------------------
PARAMETER P_INFILE LIKE RLGRAP-FILENAME.

**********************AT SELECTION-SCREEN ON VALUE-REQUEST

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_INFILE.

  CALL FUNCTION 'F4_FILENAME'
    EXPORTING
      PROGRAM_NAME  = SYST-CPROG
      DYNPRO_NUMBER = SYST-DYNNR
      FIELD_NAME    = 'P_INFILE '
    IMPORTING
      FILE_NAME     = P_INFILE.


************************************************************************
START-OF-SELECTION.

  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      FILENAME                = P_INFILE
      I_BEGIN_COL             = '1'
      I_BEGIN_ROW             = '1'  "Do not require headings
      I_END_COL               = '2'
      I_END_ROW               = '33'
    TABLES
      INTERN                  = ITAB
    EXCEPTIONS
      INCONSISTENT_PARAMETERS = 1
      UPLOAD_OLE              = 2
      OTHERS                  = 3.
  IF SY-SUBRC <> 0.
    MESSAGE E010(ZZ) WITH TEXT-001. "Problem uploading Excel Spreadsheet
  ENDIF.

* Sort table by rows and colums
  SORT ITAB BY ROW COL.

* Get first row retrieved
  READ TABLE ITAB INDEX 1.

* Set first row retrieved to current row
  GD_CURRENTROW = ITAB-ROW.

  LOOP AT ITAB.
*   Reset values for next row
    IF ITAB-ROW NE GD_CURRENTROW.
      APPEND WA_RECORD TO IT_RECORD.
      CLEAR WA_RECORD.
      GD_CURRENTROW = ITAB-ROW.
    ENDIF.

    CASE ITAB-COL.
      WHEN '0001'.                              "First name
        WA_RECORD-NAME1 = ITAB-VALUE.
      WHEN '0002'.                              "Surname
        WA_RECORD-NAME2 = ITAB-VALUE.
*      when '0003'.                              "Age
*        wa_record-age   = itab-value.
    ENDCASE.
  ENDLOOP.
  APPEND WA_RECORD TO IT_RECORD.
*!! Excel data is now contained within the internal table IT_RECORD

* Display report data for illustration purposes
  LOOP AT IT_RECORD INTO WA_RECORD.
    WRITE:/     SY-VLINE,
           (10) WA_RECORD-NAME1, SY-VLINE,
           (10) WA_RECORD-NAME2, SY-VLINE.
*           (10) wa_record-age, sy-vline.
  ENDLOOP.

[Former Member]

Hi Rahul,

I have done the same coding, i hope you didn't understood my issue. The problem is when you execute the report with excel file as input, then a pop-up window appears. how to avoid that pop-up? because when a user runs the report he should be wordering why this pop-up appeared.

Regards,

Kumar

[Former Member]

Hello Friends,

I have similar problem like.

I have done the same coding, i hope you didn't understood my issue. The problem is when you execute the report with excel file as input, then a pop-up window appears. how to avoid that pop-up? because when a user runs the report he should be wordering why this pop-up appeared.

please let me know if you have something for me.

[Former Member]

Hi Kumar,

I used a method in order to upload and Excel file, you just need to indicate the path of the file and the initial row, initial column, ending row and ending column.

This method doesn't show a popup.

Here's the part of the code that upload the file.

CLASS lcl_bi DEFINITION.
  PUBLIC SECTION.
    CLASS-METHODS main.

  PRIVATE SECTION.
    CLASS-METHODS: 
                 subir_archivo
                    IMPORTING
                       file          TYPE rlgrap-filename
                       row_ini    TYPE i
                       col_ini     TYPE i
                       row_end  TYPE i
                       col_end   TYPE i
                    CHANGING
                       table       TYPE it_table,
                 delimitar_columnas
                    CHANGING
                       excel_line TYPE it_excel_line
                       table         TYPE it_table.
ENDCLASS.

  METHOD main.
    ctumode = p_ctu.

*   Subimos el archivo de carga
    lcl_bi=>subir_archivo( EXPORTING
                                   file    = p_file
                                   row_ini = p_row1
                                   col_ini = p_col1
                                   row_end = p_row2
                                   col_end = p_col2
                                 CHANGING
                                   table = it_archivo ).
  ENDMETHOD.                    "main

  METHOD subir_archivo.
    DATA: app       TYPE obj_record,
          workbook  TYPE obj_record,
          worksheet TYPE obj_record,
          cell_1    TYPE obj_record,
          cell_2    TYPE obj_record,
          range     TYPE obj_record.

    DATA it_excel_line TYPE TABLE OF t_excel_line.

    CREATE OBJECT app 'EXCEL.APPLICATION'.
    IF sy-subrc NE 0.
      " error.
      EXIT.
    ENDIF.

    CALL METHOD OF app 'WORKBOOKS' = workbook.
    CALL METHOD OF workbook 'OPEN'
      EXPORTING
      #1 = file.
    IF sy-subrc NE 0.
      WRITE:/ 'Error en excel'.
    ENDIF.

    GET PROPERTY OF app 'ACTIVESHEET' = worksheet.

    CALL METHOD OF worksheet 'Cells' = cell_1
      EXPORTING
      #1 = row_ini
      #2 = col_ini.
    CALL METHOD OF worksheet 'Cells' = cell_2
      EXPORTING
      #1 = row_end
      #2 = col_end.

    CALL METHOD OF worksheet 'RANGE' = range
      EXPORTING
      #1 = cell_1
      #2 = cell_2.

    CALL METHOD OF range 'SELECT'.
    CALL METHOD OF range 'COPY'.

    CALL FUNCTION 'CLPB_IMPORT'
      TABLES
        data_tab   = it_excel_line
      EXCEPTIONS
        clpb_error = 1
        OTHERS     = 2.

    lcl_bi=>delimitar_columnas( CHANGING
                                        excel_line = it_excel_line
                                        table      = it_archivo ).

    SET PROPERTY OF app 'CutCopyMode' = 0.
    CALL METHOD OF app 'QUIT'.
    FREE OBJECT app.
  ENDMETHOD.                    "subir_archivo

  METHOD delimitar_columnas.
    FIELD-SYMBOLS: <pointer> TYPE ANY,
                   <wa_line> TYPE t_archivo.
    TYPES: BEGIN OF t_xls,
               value(1024),
           END OF t_xls.

    DATA: it_xls        TYPE TABLE OF t_xls,
          wa_xls        TYPE t_xls,
          w_len         TYPE i,
          wa_excel_line TYPE t_excel_line,
          new_line_dyn  TYPE REF TO data,
          w_indice      TYPE i,
          w_var         TYPE c.

    LOOP AT excel_line INTO wa_excel_line.
      w_len = STRLEN( wa_excel_line ).
      MOVE 1 TO w_indice.
      DO w_len TIMES.

        w_var = wa_excel_line+w_indice(1) .

        IF w_var CA '0123456789ABCDEFGHIJKLMNÑOPQRSTUVWXYZabcdefghijklmnñopqrstuvwxyz:;.,-'.

          ADD 1 TO w_indice.

        ELSE.
          IF w_len GT w_indice.
*           Reemplazamos # por /
            wa_excel_line+w_indice(1) = '/'.

            ADD 1 TO w_indice.
          ENDIF.
        ENDIF.
      ENDDO.

      SPLIT wa_excel_line AT '/' INTO TABLE it_xls.

      LOOP AT it_xls INTO wa_xls.
        ASSIGN COMPONENT sy-tabix OF STRUCTURE <wa_line> TO <pointer>.
        <pointer> = wa_xls-value.
      ENDLOOP.
      APPEND <wa_line> TO table.
      CLEAR: wa_xls.
      REFRESH it_xls.
    ENDLOOP.
  ENDMETHOD.                    "delimitar_columnas

Message was edited by:

Eric Hernandez Pardo

Message was edited by:

Eric Hernandez Pardo

[faisalatsap]

Hi, Eric and Rahul

Please Have a look at this =>

Hope you will get idea about the display problem.

Regards,

Faisal

[Former Member]

Please note the original date of the thread. It was well before the 2,500 character limit.

Rob

[faisalatsap]

Sorry, Sir

I didn't check the Post Date, will keep in mind next time

Thanks and Regards,

Faisal

[Former Member]

It happens all the time - I was just warning others who might want to jump in.

Rob

[Former Member]

Hi Kumar,

Use the function module SAP_CONVERT_TO_XLS_FORMAT to download the internal table to an excel file.

PARAMETERS: p_file LIKE rlgrap-filename DEFAULT 'c:tmptest.xls'.
DATA: t100_Lines TYPE STANDARD TABLE OF t001 WITH DEFAULT KEY.

SELECT * FROM t001 INTO TABLE t100_Lines.

CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'
  EXPORTING
    i_filename     = p_file
  TABLES
    i_tab_sap_data = t100_Lines.

Reward points if found helpful

Revert back for more help

Regards

Naresh

[Former Member]

Hi,

Use FM FAA_FILE_UPLOAD_EXCEL. It will upload an excel file into a table of type string. Then, use SPLIT to get it into an internal table. It has a limit of 5000 records. So if you have more than that to be uploaded, then create a Z copy of above FM & change the value of parameter ld_max_rows to whatever you want.

Reward points if the answer is helpful.

Regards,

Mukul