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

Загрузка таблицы в Excel с разбивкой на несколько Sheets



 
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> OLE2, Excel, WinWord
View previous topic :: View next topic  
Author Message
admin
Администратор
Администратор



Joined: 01 Sep 2007
Posts: 1636

PostPosted: Sun Mar 02, 2008 5:12 pm    Post subject: Загрузка таблицы в Excel с разбивкой на несколько Sheets Reply with quote

Code:
*&---------------------------------------------------------------------
*& Report  ZKRIS_EXCELUPLOAD_2SHEETS
*&
*&---------------------------------------------------------------------
*&
*&
*&---------------------------------------------------------------------
report  zkris_excelupload_2sheets.

* value of excel-cell
types: ty_d_itabvalue             type alsmex_tabline-value,

* internal table containing the excel data
       ty_t_itab                  type alsmex_tabline   occurs 0,

* line type of sender table
       begin of ty_s_senderline,
         line(4096)               type c,
       end of ty_s_senderline,
* sender table
       ty_t_sender                type ty_s_senderline  occurs 0.
*
 
constants:  gc_esc              value '"'.
include lalsmexf01.
type-pools ole2.
 
start-of-selection.
 
  parameters: filename like rlgrap-filename.
  parameters: st_rw_s1 type i.
  parameters: st_cl_s1 type i.
  parameters: st_rw_s2 type i.
  parameters: st_cl_s2 type i.
  parameters: ed_rw_s1 type i.
  parameters: ed_cl_s1 type i.
  parameters: ed_rw_s2 type i.
  parameters: ed_cl_s2 type i.
 
  data: it_data1 type ty_t_itab.
  data: it_data2 type ty_t_itab.
 
  data: it_data_wa like line of it_data1.
 

* DATA DECLARATION

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

* MESSAGE DEFINATION

  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.
    endcase.
  end-of-definition.


* PARAMETER CHECK
 
  if st_rw_s1 > ed_rw_s1.
    raise inconsistent_parameters.
  endif.
 
  if st_cl_s1 > ed_cl_s1.
    raise inconsistent_parameters.
  endif.
 
  if st_rw_s2 > ed_rw_s2.
    raise inconsistent_parameters.
  endif.
 
  if st_cl_s2 > ed_cl_s2.
    raise inconsistent_parameters.
  endif.

  class cl_abap_char_utilities definition load.
  ld_separator = cl_abap_char_utilities=>horizontal_tab.
 

* OPENING EXCEL FILE

  if application-header = space or application-handle = -1.
    create object application 'Excel.Application'.
    m_message.
  endif.
  call method of application 'Workbooks' = workbook.
  m_message.
  call method of application 'Workbooks' = workbook.
  m_message.
  call method of workbook 'Open' exporting #1 = filename.
  m_message.
  call method of application 'Worksheets' = sheet exporting #1 = 1.
  m_message.
  call method of application 'Worksheets' = sheet exporting #1 = 1.
  m_message.
  call method of sheet 'Activate'.
  m_message.
  get property of application 'ACTIVESHEET' = sheet.
  m_message.
 

* MARKING OF WHOLE SPREADSHEET

  call method of sheet 'Cells' = h_cell
    exporting #1 = st_rw_s1 #2 = st_cl_s1.
  m_message.
  call method of sheet 'Cells' = h_cell1
    exporting #1 = ed_rw_s1 #2 = ed_cl_s1.
  m_message.
 
  call method of sheet 'RANGE' = range
    exporting #1 = h_cell #2 = h_cell1.
  m_message.
  call method of range 'SELECT'.
  m_message.
 

* Copy marked area (SHEET1) into Clippboard

 
  call method of range 'COPY'.
  m_message.
 

* Read clipboard into ABAP

 
  call method cl_gui_frontend_services=>clipboard_import
  importing
  data = excel_tab
  exceptions
  cntl_error = 1

* ERROR_NO_GUI = 2
* NOT_SUPPORTED_BY_GUI = 3

  others = 4
  .
  if sy-subrc  0.
    message a037(alsmex).
  endif.
 
  perform separated_to_intern_convert tables excel_tab it_data1
  using ld_separator.
 

* Clear the clipboard

 
  refresh excel_tab.
  call method cl_gui_frontend_services=>clipboard_export
  importing
  data = excel_tab
  changing
  rc = ld_rc
  exceptions
  cntl_error = 1

* ERROR_NO_GUI = 2
* NOT_SUPPORTED_BY_GUI = 3

  others = 4
  .
 

* Working in Second Excel Work Sheet

 
  call method of application 'Worksheets' = sheet exporting #1 = 2.
  m_message.
  call method of sheet 'Activate'.
  m_message.
  get property of application 'ACTIVESHEET' = sheet.
  m_message.

* Mark Sheet2

 
  call method of sheet 'Cells' = h_cell
    exporting #1 = st_rw_s2 #2 = st_cl_s2.
  m_message.
  call method of sheet 'Cells' = h_cell1
    exporting #1 = ed_rw_s2 #2 = ed_cl_s2.
  m_message.
 
  call method of sheet 'RANGE' = range
    exporting #1 = h_cell #2 = h_cell1.
  m_message.
  call method of range 'SELECT'.
  m_message.
 

* Copy Marked Area (Sheet2) into Clippboard

 
  call method of range 'COPY'.
  m_message.
 

* Read Clipboard into ABAP

 
  call method cl_gui_frontend_services=>clipboard_import
  importing
  data = excel_tab1
  exceptions
  cntl_error = 1

* ERROR_NO_GUI = 2
* NOT_SUPPORTED_BY_GUI = 3

  others = 4
  .
  if sy-subrc  0.
    message a037(alsmex).
  endif.
 
  perform separated_to_intern_convert tables excel_tab1 it_data2
  using ld_separator.
 

* Clear Clipboard

 
  refresh excel_tab.
  call method cl_gui_frontend_services=>clipboard_export
  importing
  data = excel_tab1
  changing
  rc = ld_rc
  exceptions
  cntl_error = 1

* ERROR_NO_GUI = 2
* NOT_SUPPORTED_BY_GUI = 3

  others = 4

* Leaving Application

  .
  call method of application 'QUIT'.
  m_message.
 
  free object application.
  m_message.

  loop at it_data1 into it_data_wa.
    write:/ it_data_wa.
  endloop.
 
  skip 3.
 
  loop at it_data2 into it_data_wa.
    write:/ it_data_wa.
  endloop.
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.