topic Re: Upload from Excel in Application Development and Automation Discussions

Upload from Excel

Former Member — Thu, 11 Dec 2008 07:43:35 GMT

Hi All,

I am uploading data from Excel sheet for that i used ALSM_EXCEL_TO_INTERNAL_TABLE and i have successfully upload the data from Excel if it's data is less then 65536 in one sheet if data is more then one sheet (for more then 90,000 data) and it's not upload the data continue with the next Excel Sheet .

Please Can Anyone help me How to upload more then 90000 record from Excel (Sheet1,sheet2.......sheet3).

Thanks in advance

Regards,

Tarak

Re: Upload from Excel

Former Member — Thu, 11 Dec 2008 08:17:11 GMT

Hi All,

Please help me about above issue

Thanks

Tarak

Re: Upload from Excel

rainer_hbenthal — Thu, 11 Dec 2008 08:22:35 GMT

Theres now way doing that with your function module. Row- and column counter are declared as N4 which limits both values to max. 9999.

Re: Upload from Excel

Former Member — Thu, 11 Dec 2008 08:26:31 GMT

HI,

If you can updload the data from active sheet of Excel then read that data first to internal table and then using the OLE code make other sheet as active and use the FM to read once again..then it will read data from other sheet and so on...

Re: Upload from Excel

tarangini_katta — Thu, 11 Dec 2008 08:28:51 GMT

Hi,

Follow the below link.It suits for you requirement.

http://www.sap-img.com/abap/uploading-multiple-multitab-excel-sheets-or-ranges.htm

Thanks

Re: Upload from Excel

Former Member — Thu, 11 Dec 2008 08:35:08 GMT

Hi,

Uploading multiple multitab Excel sheets or Ranges from Front end to SAP

Please refer to

http://www.sap-img.com/abap/abap-object-oriented-spreadsheet-with-unlimited-power.htm

Visit

http://help.sap.com/saphelp_47x200/helpdata/en/e9/0be775408e11d1893b0000e8323c4f/frameset.htm

and

http://help.sap.com/saphelp_47x200/helpdata/en/e9/0be775408e11d1893b0000e8323c4f/frameset.htm

You need some basic idea of range object in excel.

You need to create XLS with named ranges or create ranges dynamically.

This could be a neat way to upload XLS the OO way!

The function Module zjnc_get_range reads 1 range into any Internal

table.

DATA: BEGIN OF it_test OCCURS 0,

vpd LIKE mseg-menge,

vas LIKE mkpf-budat,

vkm LIKE mseg-matnr,

END OF it_test.

CALL FUNCTION 'ZJNC_GET_RANGE'

EXPORTING

rangename = 'test'

itabname = 'IT_TEST[]'

irecname = 'it_test'

spreadsheetintf = spreadsheetintf.

=Work!$A$14:$C$16 is range "test"

Numbers & Character data are no problem BUT dates are.

In Excel default date is mm/dd/yyyy but is dependent on PC's

international setting which is normally default

To Avoid any 5-March 3-May type mix-up, I have designed the FM so that you need to

enter dates as 'dd.Mon.yyyy i.e. in Characters in "Internet Date Format"

FUNCTION zjnc_get_range.

*"----

""Local interface:

*" IMPORTING

*" REFERENCE(RANGENAME) TYPE C

*" REFERENCE(ITABNAME) TYPE C

*" REFERENCE(IRECNAME) TYPE C

*" REFERENCE(SPREADSHEETINTF) TYPE REF TO I_OI_SPREADSHEET

*"----

*" REFERENCE(SPREADSHEETINTF) TYPE REF TO I_OI_SPREADSHEET

*"----

DATA:

stru_ref TYPE REF TO cl_abap_structdescr,

comp_tab TYPE abap_compdescr_tab,

one_comp TYPE abap_compdescr,

one_name TYPE string,

type_ref TYPE REF TO cl_abap_typedescr,

is_ddic TYPE abap_bool,

lt_ddic TYPE dd_x031l_table,

wa_ddic TYPE x031l.

DATA: zjncranges TYPE soi_range_list,

zjnccontents TYPE soi_generic_table,

zjnconerange TYPE soi_range_item,

zjnconeitem TYPE soi_generic_item,

prevrow(4) TYPE n,

nrow(4) TYPE n,

ncolumn(4) TYPE n,

mystring TYPE string,

mydate LIKE sy-datum.

FIELD-SYMBOLS: <fs_type> TYPE ANY,

<fs_table> TYPE STANDARD TABLE,

<fs_line> TYPE ANY.

CONCATENATE '(' sy-cprog ')' itabname INTO mystring.

ASSIGN (mystring) TO <fs_table>.

CONCATENATE '(' sy-cprog ')' irecname INTO mystring.

ASSIGN (mystring) TO <fs_line>.

stru_ref ?= cl_abap_structdescr=>describe_by_data( <fs_line> ).

comp_tab = stru_ref->components.

REFRESH zjncranges.

MOVE rangename TO zjnconerange-name.

APPEND zjnconerange TO zjncranges.

CALL METHOD spreadsheetintf->get_ranges_data

IMPORTING

contents = zjnccontents

error = zjncerror

retcode = zjncretcode

CHANGING

ranges = zjncranges.

MOVE 0 TO prevrow.

LOOP AT zjnccontents INTO zjnconeitem.

MOVE zjnconeitem-row TO nrow.

IF nrow <> prevrow.

IF prevrow <> 0.

APPEND <fs_line> TO <fs_table>.

ENDIF.

CLEAR <fs_line>.

MOVE nrow TO prevrow.

ENDIF.

MOVE zjnconeitem-column TO ncolumn.

READ TABLE comp_tab INDEX ncolumn INTO one_comp.

CONCATENATE '(' sy-cprog ')' irecname '-' one_comp-name INTO one_name.

ASSIGN (one_name) TO <fs_type>.

IF one_comp-type_kind <> 'D'.

MOVE zjnconeitem-value TO <fs_type>.

ELSE.

TRANSLATE zjnconeitem-value TO UPPER CASE.

CALL FUNCTION 'CONVERSION_EXIT_SDATE_INPUT'

EXPORTING

input = zjnconeitem-value

IMPORTING

output = mydate.

MOVE mydate TO <fs_type>.

ENDIF.

ENDLOOP.

IF prevrow <> 0.

APPEND <fs_line> TO <fs_table>.

ENDIF.

ENDFUNCTION.

Regards,

Flavya

Edited by: Flavya on Dec 11, 2008 9:35 AM

Re: Upload from Excel

Former Member — Thu, 11 Dec 2008 09:36:26 GMT

Hi,

You can use OLE to populate multiple sheet.

Check the following code to understand the logic.

Here I'm using two sheets for explaining purpose

i just populated same internal table in two sheets where you can use split your table and populate.

TYPE-POOLS ole2.
DATA: wf_cell_from TYPE ole2_object,
      wf_cell_from1 TYPE ole2_object,
      wf_cell_to TYPE ole2_object,
      wf_cell_to1 TYPE ole2_object,
      wf_excel TYPE ole2_object,       " Excel object
      wf_mapl TYPE ole2_object,        " list of workbooks
      wf_map TYPE ole2_object,         " workbook
      wf_worksheet TYPE ole2_object,   " Worksheet
      wf_cell TYPE ole2_object,        " Cell Range
      wf_cell1 TYPE ole2_object,
      wf_range TYPE ole2_object,       " Range of cells to be formatted
      wf_range2 TYPE ole2_object,
      wf_column1 TYPE ole2_object.     " Column to be Autofit


DATA: BEGIN OF t_hex,
      l_tab TYPE x,
      END OF t_hex.

DATA: wf_deli(1) TYPE c.            "delimiter



TYPES: t_data1(1500) TYPE c,
       int_ty TYPE TABLE OF t_data1. "line type internal table

*All the data was prepared as line type internal tables for faster
*download

DATA: int_matl TYPE int_ty ,
      int_matl1 TYPE int_ty ,
      wa_matl TYPE t_data1.

TYPES: BEGIN OF ty_mara,
       matnr TYPE matnr,
       mtart TYPE mtart,
       matkl TYPE matkl,
       meins TYPE meins,
       END OF ty_mara.

DATA: int_mara TYPE STANDARD TABLE OF ty_mara,
      wa_mara TYPE ty_mara.

FIELD-SYMBOLS: <fs> .

DATA: wc_sheets LIKE sy-index.  "no.of sheets
DATA: it_tabemp TYPE filetable,
       gd_subrcemp TYPE i.

CONSTANTS wl_c09(2) TYPE n VALUE 09.

CLEAR wc_sheets.


DEFINE ole_check_error.
  if &1 ne 0.
    message e001(zz) with &1.
    exit.
  endif.
END-OF-DEFINITION.

SELECTION-SCREEN BEGIN OF BLOCK block1 WITH FRAME TITLE text-001.
PARAMETERS: p_emp   LIKE rlgrap-filename.
SELECTION-SCREEN END OF BLOCK block1.


AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_emp.

  REFRESH: it_tabemp.
  CALL METHOD cl_gui_frontend_services=>file_open_dialog
    EXPORTING
      window_title      = 'Select File'
      default_filename  = '*.xls'
      initial_directory = 'C:\'
      multiselection    = ' '  "No multiple selection
    CHANGING
      file_table        = it_tabemp
      rc                = gd_subrcemp.
  LOOP AT it_tabemp INTO p_emp.
  ENDLOOP.
* START THE EXCEL APPLICATION
  CREATE OBJECT wf_excel 'EXCEL.APPLICATION'.
  PERFORM err_hdl.

* PUT EXCEL IN FRONT
  SET PROPERTY OF wf_excel  'VISIBLE' = 1.
  PERFORM err_hdl.

* CREATE AN EXCEL WORKBOOK OBJECT
  CALL METHOD OF wf_excel 'WORKBOOKS' = wf_mapl.
  PERFORM err_hdl.

  "C:\Documents and Settings\manoj_palanivelu\Desktop\abc
  SET PROPERTY OF wf_excel 'SheetsInNewWorkbook' = 3. "no of sheets
  PERFORM err_hdl.

  CALL METHOD OF wf_mapl 'ADD' = wf_map.
  PERFORM err_hdl.


*Assign the Delimiter to field  symbol.
  ASSIGN wf_deli TO <fs> TYPE 'X'.
  t_hex-l_tab = wl_c09.
  <fs> = t_hex-l_tab.

  CLEAR int_matl.
  REFRESH int_matl.
  SELECT matnr
       mtart
       matkl
       meins
      FROM mara
      INTO CORRESPONDING FIELDS OF TABLE int_mara.
*first the headings will be displayed  in the excel sheet
  CONCATENATE 'Material Number'
  'Material type'
  'Material Group'
  'Base Unit of Measure'
  INTO wa_matl
  SEPARATED BY wf_deli.
  APPEND wa_matl TO int_matl.


  LOOP AT int_mara INTO wa_mara.
    CONCATENATE wa_mara-matnr
                wa_mara-mtart
                wa_mara-matkl
                wa_mara-meins
                INTO wa_matl
                SEPARATED BY wf_deli.
    APPEND wa_matl TO int_matl.
    CLEAR wa_matl.
  ENDLOOP.

*Copyng thae same contents to another table to display in
*new sheet
  MOVE int_matl TO int_matl1.
  PERFORM f_material_details
  TABLES int_matl
  USING 'Material Details_sheet1'
        1.

  PERFORM f_material_details
  TABLES int_matl
  USING 'Material Details_sheet2'
        2.


  GET PROPERTY OF wf_excel 'ActiveSheet' = wf_map.
  GET PROPERTY OF wf_excel 'ActiveWorkbook' = wf_mapl.

  CALL FUNCTION 'FLUSH'
    EXCEPTIONS
      cntl_system_error = 1
      cntl_error        = 2
      OTHERS            = 3.
  IF sy-subrc = 0.

    CALL METHOD OF wf_map 'SAVEAS'
      EXPORTING #1 = p_emp.
  ENDIF.

  CALL METHOD OF wf_mapl 'CLOSE'.
  CALL METHOD OF wf_excel 'QUIT'.

  FREE OBJECT wf_mapl.
  FREE OBJECT wf_map.
  FREE OBJECT wf_excel.
  MESSAGE s001(zbhi) WITH 'Complete downloading'.


*&---------------------------------------------------------------------*
*&      Form  ERR_HDL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM err_hdl.

  IF sy-subrc <> 0.
    WRITE: / 'OLE ERROR: RETURN CODE ='(i10), sy-subrc.
    STOP.
  ENDIF.

ENDFORM.                    "ERR_HDL

*-- End of Program
*&---------------------------------------------------------------------*
*&      Form  f_material_details
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM f_material_details
   TABLES lint_matl
  USING l_sheet_name TYPE string
        l_sheet_no TYPE i.

  DATA: lv_lines TYPE i.

  wc_sheets = l_sheet_no.

*-- activating the worksheet and giving a  name to it
  CALL METHOD OF wf_excel 'WORKSHEETS' = wf_worksheet
    EXPORTING
    #1 = wc_sheets.
  CALL METHOD OF wf_worksheet 'ACTIVATE'.
  SET PROPERTY OF wf_worksheet 'NAME' = l_sheet_name.

*--formatting the cells
  CALL METHOD OF wf_excel 'Cells' = wf_cell_from
    EXPORTING
    #1 = 1
    #2 = 1.
  DESCRIBE TABLE lint_matl LINES lv_lines.
  CALL METHOD OF wf_excel 'Cells' = wf_cell_to
    EXPORTING
    #1 = lv_lines
    #2 = 4.
*--range of cells to be formatted (in this case 1 to 4)
  CALL METHOD OF wf_excel 'Range' = wf_cell
    EXPORTING
    #1 = wf_cell_from
    #2 = wf_cell_to.

*--formatting the cells
  CALL METHOD OF wf_excel 'Cells' = wf_cell_from1
    EXPORTING
    #1 = 1
    #2 = 1.
  DESCRIBE TABLE lint_matl LINES lv_lines.
  CALL METHOD OF wf_excel 'Cells' = wf_cell_to1
    EXPORTING
    #1 = lv_lines
    #2 = 1.
  CALL METHOD OF wf_excel 'Range' = wf_cell1  " Cell range for first                                                                                
" column(Material)
    EXPORTING
    #1 = wf_cell_from1
    #2 = wf_cell_to1.

  SET PROPERTY OF wf_cell1 'NumberFormat' = '@' . "To disply zeros 
                                                  "in Material number


  DATA l_rc TYPE i.
*DATA download into excel first sheet
  CALL METHOD cl_gui_frontend_services=>clipboard_export
    IMPORTING
      data                 = lint_matl[]
    CHANGING
      rc                   = l_rc
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4.
  CALL METHOD OF wf_worksheet 'Paste'.

  CALL METHOD OF wf_excel 'Columns' = wf_column1.
  CALL METHOD OF wf_column1 'Autofit'.
  FREE OBJECT wf_column1.

ENDFORM.                    " f_material_details

Hope this will help you

Regards,

Manoj Kumar P

Re: Upload from Excel

Former Member — Thu, 11 Dec 2008 10:16:24 GMT

Hi Manoj Kumar ,

In your program in line 213 it's give an error call ' L_SHEET_NAME can not be a table , a reference a string or contain any of there object' .

Plz help me

Thanks a lot

Tarak

Re: Upload from Excel

Former Member — Thu, 11 Dec 2008 10:45:17 GMT

Hi Tarak,

I'm using ECC5.0 . There it's not giving me any error.

Now I've made some changes so that it will work for you.

Check it out.

TYPE-POOLS ole2.
DATA: wf_cell_from TYPE ole2_object,
      wf_cell_from1 TYPE ole2_object,
      wf_cell_to TYPE ole2_object,
      wf_cell_to1 TYPE ole2_object,
      wf_excel TYPE ole2_object,       " Excel object
      wf_mapl TYPE ole2_object,        " list of workbooks
      wf_map TYPE ole2_object,         " workbook
      wf_worksheet TYPE ole2_object,   " Worksheet
      wf_cell TYPE ole2_object,        " Cell Range
      wf_cell1 TYPE ole2_object,
      wf_range TYPE ole2_object,       " Range of cells to be formatted
      wf_range2 TYPE ole2_object,
      wf_column1 TYPE ole2_object.     " Column to be Autofit


DATA: BEGIN OF t_hex,
      l_tab TYPE x,
      END OF t_hex.

DATA: wf_deli(1) TYPE c.            "delimiter



TYPES: t_data1(1500) TYPE c,
       int_ty TYPE TABLE OF t_data1. "line type internal table

*All the data was prepared as line type internal tables for faster
*download

DATA: int_matl TYPE int_ty ,
      int_matl1 TYPE int_ty ,
      wa_matl TYPE t_data1.

TYPES: BEGIN OF ty_mara,
       matnr TYPE matnr,
       mtart TYPE mtart,
       matkl TYPE matkl,
       meins TYPE meins,
       END OF ty_mara.

DATA: int_mara TYPE STANDARD TABLE OF ty_mara,
      wa_mara TYPE ty_mara.

FIELD-SYMBOLS: <fs> .

DATA: wc_sheets LIKE sy-index.  "no.of sheets
DATA: it_tabemp TYPE filetable,
       gd_subrcemp TYPE i.

CONSTANTS wl_c09(2) TYPE n VALUE 09.

CLEAR wc_sheets.


DEFINE ole_check_error.
  if &1 ne 0.
    message e001(zz) with &1.
    exit.
  endif.
END-OF-DEFINITION.

SELECTION-SCREEN BEGIN OF BLOCK block1 WITH FRAME TITLE text-001.
PARAMETERS: p_emp   LIKE rlgrap-filename.
SELECTION-SCREEN END OF BLOCK block1.


AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_emp.

  REFRESH: it_tabemp.
  CALL METHOD cl_gui_frontend_services=>file_open_dialog
    EXPORTING
      window_title      = 'Select File'
      default_filename  = '*.xls'
      initial_directory = 'C:\'
      multiselection    = ' '  "No multiple selection
    CHANGING
      file_table        = it_tabemp
      rc                = gd_subrcemp.
  LOOP AT it_tabemp INTO p_emp.
  ENDLOOP.
* START THE EXCEL APPLICATION
  CREATE OBJECT wf_excel 'EXCEL.APPLICATION'.
  PERFORM err_hdl.

* PUT EXCEL IN FRONT
  SET PROPERTY OF wf_excel  'VISIBLE' = 1.
  PERFORM err_hdl.

* CREATE AN EXCEL WORKBOOK OBJECT
  CALL METHOD OF wf_excel 'WORKBOOKS' = wf_mapl.
  PERFORM err_hdl.

  SET PROPERTY OF wf_excel 'SheetsInNewWorkbook' = 3. "no of sheets
  PERFORM err_hdl.

  CALL METHOD OF wf_mapl 'ADD' = wf_map.
  PERFORM err_hdl.


*Assign the Delimiter to field  symbol.
  ASSIGN wf_deli TO <fs> TYPE 'X'.
  t_hex-l_tab = wl_c09.
  <fs> = t_hex-l_tab.

  CLEAR int_matl.
  REFRESH int_matl.
  SELECT matnr
       mtart
       matkl
       meins
      FROM mara
      INTO CORRESPONDING FIELDS OF TABLE int_mara.
*first the headings will be displayed  in the excel sheet
  CONCATENATE 'Material Number'
  'Material type'
  'Material Group'
  'Base Unit of Measure'
  INTO wa_matl
  SEPARATED BY wf_deli.
  APPEND wa_matl TO int_matl.


  LOOP AT int_mara INTO wa_mara.
    CONCATENATE wa_mara-matnr
                wa_mara-mtart
                wa_mara-matkl
                wa_mara-meins
                INTO wa_matl
                SEPARATED BY wf_deli.
    APPEND wa_matl TO int_matl.
    CLEAR wa_matl.
  ENDLOOP.

*Copyng thae same contents to another table to display in
*new sheet
  MOVE int_matl TO int_matl1.
  PERFORM f_material_details
  TABLES int_matl
  USING  1.

  PERFORM f_material_details
  TABLES int_matl
  USING  2.


  GET PROPERTY OF wf_excel 'ActiveSheet' = wf_map.
  GET PROPERTY OF wf_excel 'ActiveWorkbook' = wf_mapl.

  CALL FUNCTION 'FLUSH'
    EXCEPTIONS
      cntl_system_error = 1
      cntl_error        = 2
      OTHERS            = 3.
  IF sy-subrc = 0.

    CALL METHOD OF wf_map 'SAVEAS'
      EXPORTING #1 = p_emp.
  ENDIF.

  CALL METHOD OF wf_mapl 'CLOSE'.
  CALL METHOD OF wf_excel 'QUIT'.

  FREE OBJECT wf_mapl.
  FREE OBJECT wf_map.
  FREE OBJECT wf_excel.
  MESSAGE s001(zbhi) WITH 'Complete downloading'.


*&---------------------------------------------------------------------*
*&      Form  ERR_HDL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM err_hdl.

  IF sy-subrc <> 0.
    WRITE: / 'OLE ERROR: RETURN CODE ='(i10), sy-subrc.
    STOP.
  ENDIF.

ENDFORM.                    "ERR_HDL

*-- End of Program
*&---------------------------------------------------------------------*
*&      Form  f_material_details
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM f_material_details
   TABLES lint_matl
  USING l_sheet_no TYPE i.

  DATA: lv_lines TYPE i,
        lv_sheet_name(50) TYPE c.

  wc_sheets = l_sheet_no.
  CASE l_sheet_no.
    WHEN 1.
      lv_sheet_name = 'Material_sheet1'.
    WHEN 2.
      lv_sheet_name = 'Material_sheet2'.
  ENDCASE.


*-- activating the worksheet and giving a  name to it
  CALL METHOD OF wf_excel 'WORKSHEETS' = wf_worksheet
    EXPORTING
    #1 = wc_sheets.
  CALL METHOD OF wf_worksheet 'ACTIVATE'.



  SET PROPERTY OF wf_worksheet 'NAME' = lv_sheet_name.

*--formatting the cells
  CALL METHOD OF wf_excel 'Cells' = wf_cell_from
    EXPORTING
    #1 = 1
    #2 = 1.
  DESCRIBE TABLE lint_matl LINES lv_lines.
  CALL METHOD OF wf_excel 'Cells' = wf_cell_to
    EXPORTING
    #1 = lv_lines
    #2 = 4.
*--range of cells to be formatted (in this case 1 to 4)
  CALL METHOD OF wf_excel 'Range' = wf_cell
    EXPORTING
    #1 = wf_cell_from
    #2 = wf_cell_to.

*--formatting the cells
  CALL METHOD OF wf_excel 'Cells' = wf_cell_from1
    EXPORTING
    #1 = 1
    #2 = 1.
  DESCRIBE TABLE lint_matl LINES lv_lines.
  CALL METHOD OF wf_excel 'Cells' = wf_cell_to1
    EXPORTING
    #1 = lv_lines
    #2 = 1.
  CALL METHOD OF wf_excel 'Range' = wf_cell1  " Cell range for first

                                              " column(Material)
    EXPORTING
    #1 = wf_cell_from1
    #2 = wf_cell_to1.

  SET PROPERTY OF wf_cell1 'NumberFormat' = '@' . "To disply zeros
  "in Material number


  DATA l_rc TYPE i.
*DATA download into excel first sheet
  CALL METHOD cl_gui_frontend_services=>clipboard_export
    IMPORTING
      data         = lint_matl[]
    CHANGING
      rc           = l_rc
    EXCEPTIONS
      cntl_error   = 1
      error_no_gui = 2
      OTHERS       = 4.
  CALL METHOD OF wf_worksheet 'Paste'.

  CALL METHOD OF wf_excel 'Columns' = wf_column1.
  CALL METHOD OF wf_column1 'Autofit'.
  FREE OBJECT wf_column1.

ENDFORM.                    " f_material_details

Regards,

Manoj Kumar P