Posted: Sat Jan 19, 2008 11:01 pm Post subject: Downloading data into Excel using Clipboard
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.
*****************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.
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.
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***********************
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.
