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

Downloading data into 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
admin
Администратор
Администратор



Joined: 01 Sep 2007
Posts: 1639

PostPosted: Sat Jan 19, 2008 11:01 pm    Post subject: Downloading data into Excel using Clipboard Reply with quote

Downloading data into Excel with Format Options using Clipboard
Author: Srikanth Lodd
Original: https ://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/3134

The program developed uses OLE (Object link Enabling). The standard ABAP download module does not accommodate the format options to Excel. Also it downloads data into only one worksheet. Unlike the standard module, this function module has two features.

1) If a file already exists, it adds a new Tab(Worksheet) in the Excel.

2) If the file doesn't exist, then it creates an excel sheet and downloads into it.

Also, you can name the worksheet(tabname in excel) created. Further following formatting options can be provided.

1) Giving a colour index to a cell: This is particularly useful while you upload a file, and you would like to show the errors in data of the file by changing the background colour of the cell.

2) Bold

3) Vertical Orientation: This will provide you an option to change the vertical orientation of text in the cell. For example, downloading text vertically(90 degrees). This orientation corresponds to the degree at which you want to rotate the data in the cell.

4) Comments: Adding comments to a cell. This is very useful while you use the colour index to show that it is an error and add a comment to say what exactly the error is.

You need to create one structure(named as ZFORMATOPTIONS in the example) with following fields.

Note: The text is in the following format fieldname(Dataelement)- Description.

ROW(KCD_EX_ROW_N)- Number of the row to be formatted
COL(KCD_EX_COL_N)- Number of the col to be formatted
VERT(NUMC2)- Degree of rotation(0-90)
BOLD(BOOLE_D)- 'X' if you want to make text bold
COLOR(CHAR1)- Values 1 to 9
COMMENTS(CHAR256)- Free text that is added as a comment to the cell

You can also provide headings to the excel sheet through the table parameter 'T_HEADING' to the function module.

The function group's TOP include, function module, Form include(F01) are placed below. Further a test program is also given to show how this function module could be used.


Code:
*********************TOP INCLUDE Starts*********************
FUNCTION-POOL ZTEST_OLE. "MESSAGE-ID ..
TYPE-POOLS: abap.

* EXCEL sheet using OLE automation.
INCLUDE OLE2INCL.

DEFINE ole_error.
  IF NOT &1 IS INITIAL.
    MESSAGE e899(v1) WITH 'OLE Error ='(002) &1
    RAISING ole_error.
  ENDIF.
END-OF-DEFINITION.

TYPES:
BEGIN OF ty_line,
line(4096) TYPE c,
END OF ty_line.

CONSTANTS:
c_tab TYPE x VALUE 9,
c_bgrw TYPE i VALUE 1,
c_bgcl TYPE i VALUE 1.
*For EXCEL operations through ABAP
DATA:
w_excel TYPE ole2_object, "Holds the excel application
w_wbooks TYPE ole2_object, "Holds Work Books
w_wbook TYPE ole2_object, "Holds Work Book
w_cell TYPE ole2_object, "Holds Cell
w_format TYPE ole2_object, "Object for format
w_font TYPE ole2_object,
w_sheets TYPE ole2_object, "Holds Active Sheet
w_range TYPE ole2_object, "To select a range

*For data processing
it_line TYPE STANDARD TABLE OF ty_line,
wa_line TYPE ty_line,
w_field TYPE ty_line-line,
w_tab TYPE c.

FIELD-SYMBOLS:
<fs_field> TYPE ANY,
<fs_hex> TYPE ANY.
********************TOP Include Ends************************

******************Function Module starts********************
FUNCTION ztest_ole_single_table.
*"----------------------------------------------------------*"*"Local
interface:
*" IMPORTING
*" REFERENCE(FILENAME) TYPE RLGRAP-FILENAME
*" REFERENCE(TABNAME) TYPE CHAR16 OPTIONAL
*" TABLES
*" T_DATA
*" T_HEADING STRUCTURE LINE OPTIONAL
*" T_FORMATOPT STRUCTURE ZFORMATOPTIONS OPTIONAL
*" EXCEPTIONS
*" OLE_ERROR
*" DATA_EMPTY
*" CLIPBOARD_EXPORT_ERROR
*"----------------------------------------------------------
DATA:
file_already_exists TYPE c.

  IF t_data[] IS INITIAL.
    MESSAGE e899(v1) WITH 'No Data in the internal table'(001)
    RAISING data_empty.
  ENDIF.

  ASSIGN w_tab TO <fs_hex> TYPE 'X'.
  <fs_hex> = c_tab.

  REFRESH it_line.

  PERFORM prepare_int_tab TABLES t_data
  t_heading.

  PERFORM create_excel_sheet USING filename
  tabname
  t_data
  CHANGING file_already_exists.

  CHECK NOT t_formatopt[] IS INITIAL.

  PERFORM format_cells TABLES t_formatopt
  USING filename
  file_already_exists.

ENDFUNCTION.
*****************Function Module Ends***********************

*****************F01(Form Include) starts*******************
*-----------------------------------------------------------
***INCLUDE LZTEST_OLEF01 .
*-----------------------------------------------------------
*&----------------------------------------------------------
*& Form prepare_int_tab
*&----------------------------------------------------------
* text
*-----------------------------------------------------------
* --> p1 text
* <-- p2 text
*-----------------------------------------------------------
FORM prepare_int_tab TABLES it_data
it_heading STRUCTURE line.
  CLEAR wa_line.
  IF NOT it_heading[] IS INITIAL.
    LOOP AT it_heading.
      CONCATENATE wa_line-line
      it_heading-line
      w_tab
      INTO wa_line-line.
      CONDENSE wa_line.
    ENDLOOP.
    APPEND wa_line TO it_line.
  ENDIF.
  LOOP AT it_data.
    CLEAR wa_line.
    DO.
      ASSIGN COMPONENT sy-index OF STRUCTURE it_data TO <fs_field>.
      IF NOT sy-subrc IS INITIAL.
        EXIT.
      ENDIF.
      w_field = <fs_field>.
      CONDENSE w_field.
      CONCATENATE wa_line-line
      w_field
      w_tab
      INTO wa_line-line.
      CONDENSE wa_line.
    ENDDO.
    APPEND wa_line TO it_line.
  ENDLOOP.

ENDFORM. " prepare_int_tab
*&----------------------------------------------------------
*& Form create_excel_sheet
*&----------------------------------------------------------
* text
*-----------------------------------------------------------
* --> p1 text
* <-- p2 text
*-----------------------------------------------------------
FORM create_excel_sheet USING p_filename
p_tabname
w_data
CHANGING p_file_already_exists.
  DATA:
  l_cols TYPE i,
  l_rows TYPE i,
  l_name TYPE char16,
  l_rc TYPE sy-subrc,
  l_res TYPE abap_bool,
  l_type TYPE c,
  l_file TYPE string,
  l_from TYPE ole2_object,
  l_to TYPE ole2_object,
  l_entcol TYPE ole2_object.


  CREATE OBJECT w_excel 'Excel.Application'.
  ole_error sy-subrc.

  CALL METHOD OF w_excel 'Workbooks' = w_wbooks.
  ole_error sy-subrc.
* SET PROPERTY OF w_excel 'Visible' = 1.
  ole_error sy-subrc.
  l_file = p_filename.
  CLEAR l_res.
  CALL METHOD cl_gui_frontend_services=>file_exist
    EXPORTING
      file            = l_file
    RECEIVING
      result          = l_res
    EXCEPTIONS
      cntl_error      = 1
      error_no_gui    = 2
      wrong_parameter = 3
      OTHERS          = 4.
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
    WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.
  IF l_res IS INITIAL.
    CLEAR p_file_already_exists.
  ELSE.
    p_file_already_exists = 'X'.
  ENDIF.
  IF NOT p_file_already_exists IS INITIAL.

* Open the existing file in case if it exists
    CALL METHOD OF w_wbooks 'Open'
      EXPORTING
      #1 = p_filename.
    ole_error sy-subrc.

    CALL METHOD OF w_excel 'Sheets' = w_sheets.
    ole_error sy-subrc.

    CALL METHOD OF w_sheets 'Add'.
    ole_error sy-subrc.

    GET PROPERTY OF w_excel 'ActiveSheet' = w_wbook.
    ole_error sy-subrc.


  ELSE.
    CALL METHOD OF w_wbooks 'Add'. " = w_wbook.
    ole_error sy-subrc.

    GET PROPERTY OF w_excel 'ActiveSheet' = w_wbook.
    ole_error sy-subrc.

  ENDIF.

  IF NOT p_tabname IS INITIAL.

    SET PROPERTY OF w_wbook 'Name' = p_tabname.
    ole_error sy-subrc.

  ENDIF.

  CALL METHOD OF w_wbook 'Cells' = l_from
    EXPORTING
    #1 = c_bgrw
    #2 = c_bgcl.
  ole_error sy-subrc.

  DESCRIBE FIELD w_data TYPE l_type COMPONENTS l_cols.
  DESCRIBE TABLE it_line LINES l_rows.

  CALL METHOD OF w_wbook 'Cells' = l_to
    EXPORTING
    #1 = l_rows
    #2 = l_cols.
  ole_error sy-subrc.

  CALL METHOD OF w_wbook 'Range' = w_range
    EXPORTING
    #1 = l_from
    #2 = l_to.
  ole_error sy-subrc.

  CALL METHOD cl_gui_frontend_services=>clipboard_export
    IMPORTING
      data         = it_line
    CHANGING
      rc           = l_rc
    EXCEPTIONS
      cntl_error   = 1
      error_no_gui = 2
      OTHERS       = 3.
  IF sy-subrc <> 0
  OR NOT l_rc IS INITIAL.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
    WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4
    RAISING clipboard_export_error.
  ENDIF.

  CALL METHOD OF w_range 'Select'.
  ole_error sy-subrc.

  CALL METHOD OF w_wbook 'Paste'.
  ole_error sy-subrc.

  WHILE l_cols GT 0.
    l_rows = 1.
    CALL METHOD OF w_excel 'Columns' = w_cell
      EXPORTING
      #1 = l_cols.
    ole_error sy-subrc.

    CALL METHOD OF w_cell 'EntireColumn' = l_entcol.
    ole_error sy-subrc.

    l_cols = l_cols - 1.

    CALL METHOD OF l_entcol 'Autofit'.
    ole_error sy-subrc.

  ENDWHILE.

ENDFORM. " create_excel_sheet
*&----------------------------------------------------------
*& Form format_cells
*&----------------------------------------------------------
* text
*-----------------------------------------------------------
* -->P_FILENAME text
* -->P_FILE_ALREADY_EXISTS text
*-----------------------------------------------------------
FORM format_cells TABLES it_formatopt STRUCTURE zformatoptions
USING p_filename TYPE rlgrap-filename
p_file_already_exists TYPE c.

  DATA:
  l_row TYPE i,
  l_col TYPE i,
  l_entcol TYPE ole2_object,
  l_cols TYPE ole2_object,
  l_comment TYPE ole2_object.

  LOOP AT it_formatopt.
    CLEAR: l_row, l_col.
    l_row = it_formatopt-row.
    l_col = it_formatopt-col.

    CALL METHOD OF w_wbook 'Cells' = w_cell
      EXPORTING
      #1 = l_row
      #2 = l_col.
    ole_error sy-subrc.

    IF NOT it_formatopt-bold IS INITIAL.
      CALL METHOD OF w_cell 'Font' = w_font.
      ole_error sy-subrc.
      SET PROPERTY OF w_font 'Bold' = 1.
      ole_error sy-subrc.
      CALL METHOD OF w_excel 'Columns' = l_cols
        EXPORTING
        #1 = l_col.
      ole_error sy-subrc.
      CALL METHOD OF l_cols 'EntireColumn' = l_entcol.
      ole_error sy-subrc.
      CALL METHOD OF l_entcol 'Autofit'.
      ole_error sy-subrc.
    ENDIF.

    IF NOT it_formatopt-color IS INITIAL.
      CALL METHOD OF w_cell 'Interior' = w_format.
      ole_error sy-subrc.
      SET PROPERTY OF w_format 'ColorIndex' = it_formatopt-color.
      ole_error sy-subrc.
      CALL METHOD OF w_excel 'Columns' = l_cols
        EXPORTING
        #1 = l_col.
      ole_error sy-subrc.
      CALL METHOD OF l_cols 'EntireColumn' = l_entcol.
      ole_error sy-subrc.
      CALL METHOD OF l_entcol 'Autofit'.
      ole_error sy-subrc.
    ENDIF.

    IF NOT it_formatopt-vert IS INITIAL.
      SET PROPERTY OF w_cell 'Orientation' = it_formatopt-vert.
      ole_error sy-subrc.
      CALL METHOD OF w_excel 'Columns' = l_cols
        EXPORTING
        #1 = l_col.
      ole_error sy-subrc.
      CALL METHOD OF l_cols 'EntireColumn' = l_entcol.
      ole_error sy-subrc.
      CALL METHOD OF l_entcol 'Autofit'.
      ole_error sy-subrc.
    ENDIF.


    IF NOT it_formatopt-comments IS INITIAL.
* CALL METHOD OF w_excel 'Range' = w_range
* EXPORTING
* #1 = l_row
* #2 = l_col.
* ole_error sy-subrc.
* CALL METHOD OF w_range 'Select'.
* ole_error sy-subrc.
      CALL METHOD OF w_cell 'AddComment' = l_comment.
      ole_error sy-subrc.
      CALL METHOD OF l_comment 'Text'
        EXPORTING
        #1 = it_formatopt-comments.
      ole_error sy-subrc.
    ENDIF.
  ENDLOOP.
  PERFORM save_and_close USING p_filename
  p_file_already_exists.

ENDFORM. " format_cells
*&----------------------------------------------------------
*& Form save_and_close
*&----------------------------------------------------------
* text
*-----------------------------------------------------------
* -->P_P_FILENAME text
* -->P_P_FILE_ALREADY_EXISTS text
*-----------------------------------------------------------
FORM save_and_close USING p_filename
p_file_already_exists.
  IF p_file_already_exists IS INITIAL.
    CALL METHOD OF w_wbook 'Saveas'
      EXPORTING
      #1 = p_filename.
    ole_error sy-subrc.
  ELSE.
    CALL METHOD OF w_excel 'ActiveWorkbook' = w_wbooks.
    ole_error sy-subrc.
    CALL METHOD OF w_wbooks 'Save'.
    ole_error sy-subrc.
  ENDIF.
  CALL METHOD OF w_wbooks 'Close'.
  ole_error sy-subrc.

ENDFORM. " save_and_close
***************Form Include Ends****************************
**********************Test Progam***************************
REPORT ztest_ole.
TABLES mara.

SELECT-OPTIONS:
s_matnr FOR mara-matnr.

PARAMETERS:
p_file TYPE rlgrap-filename,
p_tabnm TYPE char16.


DATA:
BEGIN OF it_mara OCCURS 1,
matnr TYPE mara-matnr, "Material No.
mtart TYPE mara-mtart, "Material Type
matkl TYPE mara-matkl, "Material Group
groes TYPE mara-groes, "Size/Dimension
END OF it_mara,
it_heading TYPE STANDARD TABLE OF line,
wa_heading TYPE line,
it_formatopt TYPE STANDARD TABLE OF zformatoptions,
wa_format TYPE zformatoptions,
l_col TYPE zformatoptions-col.


START-OF-SELECTION.
  SELECT matnr
  mtart
  matkl
  groes
  FROM mara
  UP TO 100 ROWS
  INTO TABLE it_mara.

  wa_heading-line = 'Material No.'. APPEND wa_heading TO it_heading.
  wa_heading-line = 'Material Type'. APPEND wa_heading TO it_heading.
  wa_heading-line = 'Material Group'. APPEND wa_heading TO it_heading.
  wa_heading-line = 'Size/Dimension'. APPEND wa_heading TO it_heading.

  DO 4 TIMES.
    CLEAR wa_format.
    wa_format-row = 1.
    wa_format-col = l_col + 1.
    l_col = l_col + 1.
    wa_format-bold = 'X'.
    wa_format-color = '6'.
    wa_format-vert = 45.
    wa_format-comments = 'This is a heading'.
    APPEND wa_format TO it_formatopt.
  ENDDO.

  CALL FUNCTION 'ZTEST_OLE_SINGLE_TABLE'
    EXPORTING
      filename               = p_file
      tabname                = p_tabnm
    TABLES
      t_data                 = it_mara
      t_heading              = it_heading
      t_formatopt            = it_formatopt
    EXCEPTIONS
      ole_error              = 1
      data_empty             = 2
      clipboard_export_error = 3
      OTHERS                 = 4.
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
    WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  PERFORM get_file CHANGING p_file.
*&----------------------------------------------------------
*& Form get_file
*&----------------------------------------------------------
* text
*-----------------------------------------------------------
* <--P_P_FILE text
*-----------------------------------------------------------
FORM get_file CHANGING p_file.
  DATA:
  l_file TYPE string,
  l_path TYPE string,
  l_fpath TYPE string.

  CALL METHOD cl_gui_frontend_services=>file_save_dialog
    CHANGING
      filename     = l_file
      path         = l_path
      fullpath     = l_fpath
    EXCEPTIONS
      cntl_error   = 1
      error_no_gui = 2
      OTHERS       = 3.
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
    WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.
  p_file = l_fpath.
ENDFORM. " get_file
********************Test Program Ends***********************
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.