SAP R/3 форум ABAP консультантов
Russian ABAP Developer's Club

Home - FAQ - Search - Memberlist - Usergroups - Profile - Log in to check your private messages - Register - Log in - English
Blogs - Weblogs News

Copy a data from Excel using clipboard

Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> OLE2, Excel, WinWord
View previous topic :: View next topic  
Author Message

Joined: 01 Sep 2007
Posts: 1636

PostPosted: Thu May 17, 2012 9:42 am    Post subject: Copy a data from Excel using clipboard Reply with quote

Author: Rich Heilman

report zrich_0001 .

types: begin of tintern,
       row(4) type n,
       col(4) type n,
<b>       value(80) type c,</b>
       end of tintern.

TYPES: ty_d_itabvalue             TYPE <b>tintern-value,</b>
       ty_t_itab                  TYPE <b>tintern</b>   OCCURS 0,
       begin of ty_s_senderline,
         line(4096)               type c,
       end of ty_s_senderline,
       ty_t_sender                type ty_s_senderline  occurs 0.

  constants: gc_hex_tab  type x  value 9,
             gc_esc              value '"'.

data: iexcel type table of tintern with header line.

parameters: p_file type localfile,
            p_bcol type i default 1,
            p_brow type i default 1,
            p_ecol type i default 99 ,
            p_erow type i default 99.

perform upload_excel   tables iexcel
                    using p_file p_bcol p_brow
                                 p_ecol p_erow.

loop at iexcel.
  write:/ iexcel-row, iexcel-col, iexcel-value.


*       FORM upload_excel                                             *
form upload_excel   tables intern
                    using filename i_begin_col i_begin_row
                                  i_end_col i_end_row.

  type-pools: ole2.

  data: excel_tab     type  ty_t_sender.
  data: ld_separator  type  c.
  data: application   type  ole2_object,
        workbook      type  ole2_object,
        range         type  ole2_object,
        worksheet     type  ole2_object.
  data: h_cell        type  ole2_object,
        h_cell1       type  ole2_object.

  field-symbols: <field>.

* Makro für Fehlerbehandlung der Methods
  define m_message.
    case sy-subrc.
      when 0.
      when 1.
        message id sy-msgid type sy-msgty number sy-msgno
                with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
      when others. raise upload_ole.

* check parameters
*if i_begin_row > i_end_row. raise inconsistent_parameters. endif.
*if i_begin_col > i_end_col. raise inconsistent_parameters. endif.

* set seperator. Direct move is not sufficient (cause of hex type)
  assign ld_separator to <field> type 'X'.
  <field> = gc_hex_tab.

* open file in Excel
  if application-header = space or application-handle = -1.
    create object application 'Excel.Application'.
  call method  of application    'Workbooks' = workbook.
  call method  of workbook 'Open'    exporting #1 = filename.
*  set property of application 'Visible' = 1.
*  m_message.
  get property of  application 'ACTIVESHEET' = worksheet.

* mark whole spread sheet
  call method of worksheet 'Cells' = h_cell
      exporting #1 = i_begin_row #2 = i_begin_col.
  call method of worksheet 'Cells' = h_cell1
      exporting #1 = i_end_row #2 = i_end_col.

  call method  of worksheet 'RANGE' = range
                 exporting #1 = h_cell #2 = h_cell1.
  call method of range 'SELECT'.

* copy marked area (whole spread sheet) into Clippboard
  call method of range 'COPY'.

* Without control flush, CLPB_IMPORT does not find any data
  call function 'CONTROL_FLUSH'
            others = 3.

* read clipboard into ABAP
  call function 'CLPB_IMPORT'
            data_tab   = excel_tab
            clpb_error = 1
            others     = 2.
  if sy-subrc <> 0.
    message a037(alsmex).

  perform separated_to_intern_convert
                                      tables excel_tab intern
                                      using  ld_separator.

* clear clipboard
  refresh excel_tab.
  call function 'CLPB_EXPORT'
            data_tab   = excel_tab
            clpb_error = 1
            others     = 2.

* quit Excel and free ABAP Object - unfortunately, this does not kill
* the Excel process
  call method of application 'QUIT'.

  free   object application.


*       FORM separated_to_intern_convert                              *
*       ........                                                      *
*  -->  I_TAB                                                         *
*  -->  I_INTERN                                                      *
*  -->  I_SEPARATOR                                                   *
form separated_to_intern_convert tables i_tab       type ty_t_sender
                                        i_intern    type ty_t_itab
                                 using  i_separator type c.
  data: l_sic_tabix like sy-tabix,
        l_sic_col   type kcd_ex_col.
  data: l_fdpos     like sy-fdpos.

  refresh i_intern.

  loop at i_tab.
    l_sic_tabix = sy-tabix.
    l_sic_col = 0.
    while i_tab ca i_separator.
      l_fdpos = sy-fdpos.
      l_sic_col = l_sic_col + 1.
      perform line_to_cell_separat tables i_intern
                                   using  i_tab l_sic_tabix l_sic_col
                                          i_separator l_fdpos.
    if i_tab <> space.
      clear i_intern.
      i_intern-row = l_sic_tabix.
      i_intern-col = l_sic_col + 1.
      i_intern-value = i_tab.
      append i_intern.
endform.                    " SEPARATED_TO_INTERN_CONVERT

FORM line_to_cell_separat TABLES i_intern    type ty_t_itab
                          USING  i_line
                                 i_row       LIKE sy-tabix
                                 ch_cell_col TYPE kcd_ex_col
                                 i_separator TYPE c
                                 i_fdpos     LIKE sy-fdpos.
  DATA: l_string   TYPE ty_s_senderline.
  DATA  l_sic_int  TYPE i.

  CLEAR i_intern.
  l_sic_int = i_fdpos.
  i_intern-row = i_row.
  l_string = i_line.
  i_intern-col = ch_cell_col.
* csv Dateien mit separator in Zelle: --> ;"abc;cd";
  IF ( i_separator = ';' OR  i_separator = ',' ) AND
       l_string(1) = gc_esc.
      PERFORM line_to_cell_esc_sep USING l_string
    IF l_sic_int > 0.
      i_intern-value = i_line(l_sic_int).
  IF l_sic_int > 0.
    APPEND i_intern.
  l_sic_int = l_sic_int + 1.
  i_line = i_line+l_sic_int.

FORM line_to_cell_esc_sep USING i_string
                                i_sic_int      TYPE i
                                i_separator    TYPE c
                                i_intern_value type ty_d_itabvalue   .
  DATA: l_int TYPE i,
  FIELD-SYMBOLS: <l_cell>.
  l_cell_end = gc_esc.
  l_cell_end+1 = i_separator .

  IF i_string CS gc_esc.
    i_string = i_string+1.
    IF i_string CS l_cell_end.
      l_int = sy-fdpos.
      ASSIGN i_string(l_int) TO <l_cell>.
      i_intern_value = <l_cell>.
      l_int = l_int + 2.
      i_sic_int = l_int.
      i_string = i_string+l_int.
    ELSEIF i_string CS gc_esc.
*     letzte Celle
      l_int = sy-fdpos.
      ASSIGN i_string(l_int) TO <l_cell>.
      i_intern_value = <l_cell>.
      l_int = l_int + 1.
      i_sic_int = l_int.
      i_string = i_string+l_int.
      l_int = strlen( i_string ).
      IF l_int > 0 . MESSAGE x001(kx) . ENDIF.
      MESSAGE x001(kx) . "was ist mit csv-Format

Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> OLE2, Excel, WinWord All times are GMT + 4 Hours
Page 1 of 1

Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You can download files in this forum

All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG.
SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG.
Every effort is made to ensure content integrity. Use information on this site at your own risk.