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

OLE, Excel



 
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> Function Modules | Функциональные модули
View previous topic :: View next topic  
Author Message
admin
Администратор
Администратор



Joined: 01 Sep 2007
Posts: 1639

PostPosted: Fri Sep 14, 2007 5:42 pm    Post subject: OLE, Excel Reply with quote

EXCEL_OLE_STANDARD_DAT - Just calls MS_EXCEL_OLE_STANDARD_DAT

MS_EXCEL_OLE_STANDARD_DAT - Downloads internal table and opens it in MS Excel.
Code:

report z_it2excel.

data: it_t001 type t001 occurs 0,
it_fields type dfies occurs 0 with header line,

begin of it_fnames occurs 0,
  reptext like dfies-reptext,
end of it_fnames.
* The structure definition required for this internal table.
* The following definition is not working:
* it_fnames like dfies-reptext occurs 0 with header line.
* because Function Module will use the following:
* ASSIGN COMPONENT 1 OF STRUCTURE FIELDNAMES TO .

select * from t001 into table it_t001.

CALL FUNCTION 'GET_FIELDTAB'
  EXPORTING
* LANGU = SY-LANGU
* ONLY = ' '
    TABNAME = 'T001'
* WITHTEXT = 'X'
* IMPORTING
* HEADER =
* RC =
  TABLES
    FIELDTAB = it_fields
    EXCEPTIONS
    INTERNAL_ERROR = 1
    NO_TEXTS_FOUND = 2
    TABLE_HAS_NO_FIELDS = 3
    TABLE_NOT_ACTIV = 4
    OTHERS = 5
    .
IF SY-SUBRC <> 0.
    MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
          WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.

loop at it_fields.
  it_fnames-reptext = it_fields-reptext.
  append it_fnames.
endloop.

CALL FUNCTION 'EXCEL_OLE_STANDARD_DAT'
  EXPORTING
    FILE_NAME = 'C:\T001'
* CREATE_PIVOT = 0
    DATA_SHEET_NAME = 'Companies'
* PIVOT_SHEET_NAME = ' '
* PASSWORD = ' '
* PASSWORD_OPTION = 0
  TABLES
* PIVOT_FIELD_TAB =
    DATA_TAB = it_t001
    FIELDNAMES = it_fnames
  EXCEPTIONS
    FILE_NOT_EXIST = 1
    FILENAME_EXPECTED = 2
    COMMUNICATION_ERROR = 3
    OLE_OBJECT_METHOD_ERROR = 4
    OLE_OBJECT_PROPERTY_ERROR = 5
    INVALID_FILENAME = 6
    INVALID_PIVOT_FIELDS = 7
    DOWNLOAD_PROBLEM = 8
    OTHERS = 9
    .
IF SY-SUBRC <> 0.
  MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
  WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.

CALL FUNCTION 'MS_EXCEL_OLE_STANDARD_DAT'
  EXPORTING
    FILE_NAME = 'C:\T001_2'
* CREATE_PIVOT = 0
    DATA_SHEET_NAME = 'Companies 2'
* PIVOT_SHEET_NAME = ' '
* PASSWORD = ' '
* PASSWORD_OPTION = 0
  TABLES
* PIVOT_FIELD_TAB =
    DATA_TAB = it_t001
    FIELDNAMES = it_fnames
  EXCEPTIONS
    FILE_NOT_EXIST = 1
    FILENAME_EXPECTED = 2
    COMMUNICATION_ERROR = 3
    OLE_OBJECT_METHOD_ERROR = 4
    OLE_OBJECT_PROPERTY_ERROR = 5
    INVALID_FILENAME = 6
    INVALID_PIVOT_FIELDS = 7
    DOWNLOAD_PROBLEM = 8
    OTHERS = 9
    .
IF SY-SUBRC <> 0.
  MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
        WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.


KCD_EXCEL_OLE_TO_INT_CONVERT - Uploads an *.xls file to internal table (max cell length = 32).
This function uses a range selection and copy-paste technique, therefore it quite fast.
Code:

report z.

parameter p_file like rlgrap-filename.
data: w_text type natxt, "message
* this internal table should have the same structure
* as *.xls file to be uploaded. E.g.:
begin of it_data occurs 0,
s1(10), "text field 1
s2(20), "text field 2
n1 type i, "integer number field
end of it_data.

start-of-selection.
perform f_excel_upload tables it_data
using p_file
changing w_text.
write: / 'Result =', w_text.

end-of-selection.
loop at it_data.
write: / it_data-s1, it_data-s2, it_data-n1.
endloop.

form f_excel_upload tables p_download
using value(p_filename) like rlgrap-filename
changing p_text type natxt.
data: it_intern type kcde_cells occurs 0 with header line,
w_index type i,
w_start_col type i value '1',
w_start_row type i value '1',
w_end_col type i value '256',
w_end_row type i value '65536',
w_rows like sy-fdpos.
field-symbols: .
call function 'KCD_EXCEL_OLE_TO_INT_CONVERT'
EXPORTING
filename = p_filename
i_begin_col = w_start_col
i_begin_row = w_start_row
i_end_col = w_end_col
i_end_row = w_end_row
TABLES
intern = it_intern
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
others = 3.
if sy-subrc <> 0.
p_text = 'Upload Error:'.
write sy-subrc to p_text+14 left-justified.
else.
if it_intern[] is initial.
p_text = 'No Data Uploaded.'.
else.
sort it_intern by row col.
loop at it_intern.
move : it_intern-col to w_index.
assign component w_index of structure p_download to .
if sy-subrc = 0.
move : it_intern-value to .
unassign .
endif.
at end of row.
append p_download.
clear p_download.
endat.
endloop.
describe table p_download lines w_rows.
p_text = 'Uploaded Rows:'.
write w_rows to p_text+15 left-justified.
endif.
endif.
endform.
* end of source code *


There is another Example that reads data from Excel file cell by cell.
Code:

report z_read_excel.

parameter p_file like rlgrap-filename.
data: w_text type natxt, "message
* this internal table should have the same structure
* as *.xls file to be uploaded. E.g.:
begin of it_data occurs 0,
s1(10), "text field 1
s2(20), "text field 2
n1 type i, "integer number field
end of it_data.

start-of-selection.
  perform f_excel_upload tables it_data
    using p_file
    changing w_text.
write: / 'Result =', w_text.

end-of-selection.
 loop at it_data.
   write: / it_data-s1, it_data-s2, it_data-n1.
  endloop.

form f_excel_upload tables p_download
  using value(p_filename) like rlgrap-filename
  changing p_text type natxt.
data: w_start_col type i value '1',
      w_start_row type i value '1',
      w_end_col type i value '3',
      w_end_row type i value '5',
      w_rows like sy-fdpos,
      w_cols like sy-fdpos,
      w_curr_row like sy-fdpos,
      w_curr_col like sy-fdpos,
      w_index type i,
      w_value(99) type c.

data: h_appl like obj_record,
      h_work like obj_record,
      h_cell like obj_record.
     
field-symbols: .

* start excel
if h_appl-header = space or h_appl-handle = -1.
  create object h_appl 'excel.application'.
  if sy-subrc <> 0.
    p_text = 'Start Excel Error:'.
    write sy-subrc to p_text+19 left-justified.
    message i002(sy) with sy-msgli.
  endif.
  set property of h_appl 'visible' = 0.
endif.

* open file
call method of h_appl 'workbooks' = h_work.
call method of h_work 'open' exporting #1 = p_filename.

* get data cell by cell
w_rows = w_end_row - w_start_row + 1.
w_cols = w_end_col - w_start_col + 1.
w_curr_row = w_start_row.
do w_rows times.
 w_curr_col = w_start_col.
 w_index = 1.

 do w_cols times.
  call method of h_appl 'cells' = h_cell
    exporting #1 = w_curr_row
              #2 = w_curr_col.
  get property of h_cell 'value' = w_value.
  assign component w_index of structure p_download to .
  if sy-subrc = 0.
    move : w_value to .
    unassign .
  endif.
  add 1 to: w_curr_col, w_index.
 enddo.
 add 1 to w_curr_row.
 append p_download.
 clear p_download.
enddo.

* release excel
call method of h_appl 'quit'.
free object h_appl.
h_appl-handle = -1.


if p_download[] is initial.
  p_text = 'No Data Uploaded.'.
else.
  describe table p_download lines w_rows.
  p_text = 'Uploaded Rows:'.
  write w_rows to p_text+15 left-justified.
endif.
endform.
* end of source code *

ALSM_EXCEL_TO_INTERNAL_TABLE - the same as KCD_EXCEL_OLE_TO_INT_CONVERT but max cell length = 50

FTBU_START_EXCEL - just [download internal table to file and] start Excel (w/o OLE).

XXL_FULL_API - Output Excel list viewer list

- XXLFTEST (XXL Test Environment / XXL_FULL_API Example)
- XXLTTEST (XXL Interface: Test Program for Formats and Types (also XXL_FULL_API))
- XXLSTEST (XXL Test Environment / XXL_SIMPLE_API Example)

XXL_SIMPLE_API - Output list viewer list with reduced functions

XXL_CHECK_API

Examble of use in customer reports:
if question = 'STARTABLE'
and return_code <> 0
then "Download to Excel" button has to be excluded from a pf-status

In fact, the return_code is always set to 0 in the FM source code.

The modules of the TXXL function group uses the DLLs and other files installed with SAPGUI in directory:
...\Program Files\SAP\FrontEnd\SAPgui\xxl\
Can work both with Excel and Lotus 1-2-3:

SAP_CONVERT_TO_XLS_FORMAT - Convert data to Microsoft Excel format.

MS_EXCEL_OLE_STANDARD_OLE - will build a file, and automatically start Excel

RH_START_EXCEL_WITH_DATA - starts Excel with the contents of an internal table. This function finds Excel in the desktop registry. It also uses a local PC working directory to save the file (that's what the 'W' value for data path flag does). Very transparent to user!
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 -> Function Modules | Функциональные модули 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.