Downloading data into Excel with Format Options

OLE2, Excel, WinWord

Добавлено: Сб Янв 19, 2008 11:01 pm Заголовок сообщения: Downloading data into Excel with Format Options Posted: Sat Jan 19, 2008 11:01 pm Post subject: Downloading data into Excel with Format Options
Downloading data into Excel with Format Options using Clipboard Downloading data into Excel with Format Options using Clipboard Author: Srikanth Lodd Author: Srikanth Lodd Original: https ://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/3134 Original: https: / / www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/3134 The program developed uses OLE (Object link Enabling). The program developed uses OLE (Object link Enabling). The standard ABAP download module does not accommodate the format options to Excel. The standard ABAP download module does not accommodate the format options to Excel. Also it downloads data into only one worksheet. Also it downloads data into only one worksheet. Unlike the standard module, this function module has two features. 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. 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. 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. Also, you can name the worksheet (tabname in excel) created. Further following formatting options can be provided. 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. 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 2) Bold 3) Vertical Orientation: This will provide you an option to change the vertical orientation of text in the cell. 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). For example, downloading text vertically (90 degrees). This orientation corresponds to the degree at which you want to rotate the data in the cell. This orientation corresponds to the degree at which you want to rotate the data in the cell. 4) Comments: Adding comments to a 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. 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. 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. Note: The text is in the following format fieldname (Dataelement) - Description. ROW(KCD_EX_ROW_N)- Number of the row to be formatted ROW (KCD_EX_ROW_N) - Number of the row to be formatted COL(KCD_EX_COL_N)- Number of the col to be formatted COL (KCD_EX_COL_N) - Number of the col to be formatted VERT(NUMC2)- Degree of rotation(0-90) VERT (NUMC2) - Degree of rotation (0-90) BOLD(BOOLE_D)- 'X' if you want to make text bold BOLD (BOOLE_D) - 'X' if you want to make text bold COLOR(CHAR1)- Values 1 to 9 COLOR (CHAR1) - Values 1 to 9 COMMENTS(CHAR256)- Free text that is added as a comment to the cell 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. 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. 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. Further a test program is also given to show how this function module could be used.  Code: *********************TOP INCLUDE Starts********************* ********************* TOP INCLUDE Starts ********************* FUNCTION-POOL ZTEST_OLE. FUNCTION-POOL ZTEST_OLE. "MESSAGE-ID .. "MESSAGE-ID .. TYPE-POOLS: abap. TYPE-POOLS: abap. * EXCEL sheet using OLE automation. * EXCEL sheet using OLE automation. INCLUDE OLE2INCL. INCLUDE OLE2INCL. DEFINE ole_error. DEFINE ole_error. IF NOT &1 IS INITIAL. IF NOT & 1 IS INITIAL. MESSAGE e899(v1) WITH 'OLE Error ='(002) &1 MESSAGE e899 (v1) WITH 'OLE Error =' (002) & 1 RAISING ole_error. RAISING ole_error. ENDIF. END-OF-DEFINITION. TYPES: BEGIN OF ty_line, BEGIN OF ty_line, line(4096) TYPE c, line (4096) TYPE c, END OF ty_line. END OF ty_line. CONSTANTS: c_tab TYPE x VALUE 9, c_tab TYPE x VALUE 9, c_bgrw TYPE i VALUE 1, c_bgrw TYPE i VALUE 1, c_bgcl TYPE i VALUE 1. c_bgcl TYPE i VALUE 1. *For EXCEL operations through ABAP * For EXCEL operations through ABAP DATA: w_excel TYPE ole2_object, "Holds the excel application w_excel TYPE ole2_object, "Holds the excel application w_wbooks TYPE ole2_object, "Holds Work Books w_wbooks TYPE ole2_object, "Holds Work Books w_wbook TYPE ole2_object, "Holds Work Book w_wbook TYPE ole2_object, "Holds Work Book w_cell TYPE ole2_object, "Holds Cell w_cell TYPE ole2_object, "Holds Cell w_format TYPE ole2_object, "Object for format w_format TYPE ole2_object, "Object for format w_font TYPE ole2_object, w_font TYPE ole2_object, w_sheets TYPE ole2_object, "Holds Active Sheet w_sheets TYPE ole2_object, "Holds Active Sheet w_range TYPE ole2_object, "To select a range w_range TYPE ole2_object, "To select a range *For data processing * For data processing it_line TYPE STANDARD TABLE OF ty_line, it_line TYPE STANDARD TABLE OF ty_line, wa_line TYPE ty_line, wa_line TYPE ty_line, w_field TYPE ty_line-line, w_field TYPE ty_line-line, w_tab TYPE c. w_tab TYPE c. FIELD-SYMBOLS: <fs_field> TYPE ANY, <fs_field> TYPE ANY, <fs_hex> TYPE ANY. <fs_hex> TYPE ANY. ********************TOP Include Ends************************ ******************** TOP Include Ends ************************ ******************Function Module starts******************** ****************** Function Module starts ******************** FUNCTION ztest_ole_single_table. FUNCTION ztest_ole_single_table. *"----------------------------------------------------------*"*"Local *"------------------------------------------------ ----------*"*" Local interface: *" IMPORTING * "IMPORTING *" REFERENCE(FILENAME) TYPE RLGRAP-FILENAME * "REFERENCE (FILENAME) TYPE RLGRAP-FILENAME *" REFERENCE(TABNAME) TYPE CHAR16 OPTIONAL * "REFERENCE (TABNAME) TYPE CHAR16 OPTIONAL *" TABLES * "TABLES *" T_DATA * "T_DATA *" T_HEADING STRUCTURE LINE OPTIONAL * "T_HEADING STRUCTURE LINE OPTIONAL *" T_FORMATOPT STRUCTURE ZFORMATOPTIONS OPTIONAL * "T_FORMATOPT STRUCTURE ZFORMATOPTIONS OPTIONAL *" EXCEPTIONS * "EXCEPTIONS *" OLE_ERROR * "OLE_ERROR *" DATA_EMPTY * "DATA_EMPTY *" CLIPBOARD_EXPORT_ERROR * "CLIPBOARD_EXPORT_ERROR *"---------------------------------------------------------- *"------------------------------------------------ ---------- DATA: file_already_exists TYPE c. file_already_exists TYPE c. IF t_data[] IS INITIAL. IF t_data [] IS INITIAL. MESSAGE e899(v1) WITH 'No Data in the internal table'(001) MESSAGE e899 (v1) WITH 'No Data in the internal table' (001) RAISING data_empty. RAISING data_empty. ENDIF. ASSIGN w_tab TO <fs_hex> TYPE 'X'. ASSIGN w_tab TO <fs_hex> TYPE 'X'. <fs_hex> = c_tab. <fs_hex> = c_tab. REFRESH it_line. REFRESH it_line. PERFORM prepare_int_tab TABLES t_data PERFORM prepare_int_tab TABLES t_data t_heading. PERFORM create_excel_sheet USING filename PERFORM create_excel_sheet USING filename tabname t_data CHANGING file_already_exists. CHANGING file_already_exists. CHECK NOT t_formatopt[] IS INITIAL. CHECK NOT t_formatopt [] IS INITIAL. PERFORM format_cells TABLES t_formatopt PERFORM format_cells TABLES t_formatopt USING filename USING filename file_already_exists. ENDFUNCTION. *****************Function Module Ends*********************** ***************** Function Module Ends *********************** *****************F01(Form Include) starts******************* ***************** F01 (Form Include) starts ******************* *----------------------------------------------------------- *------------------------------------------------- ---------- ***INCLUDE LZTEST_OLEF01 . *** INCLUDE LZTEST_OLEF01. *----------------------------------------------------------- *------------------------------------------------- ---------- *&---------------------------------------------------------- *&------------------------------------------------ ---------- *& Form prepare_int_tab * & Form prepare_int_tab *&---------------------------------------------------------- *&------------------------------------------------ ---------- * text * Text *----------------------------------------------------------- *------------------------------------------------- ---------- * --> p1 text * -> P1 text * <-- p2 text * <- P2 text *----------------------------------------------------------- *------------------------------------------------- ---------- FORM prepare_int_tab TABLES it_data FORM prepare_int_tab TABLES it_data it_heading STRUCTURE line. it_heading STRUCTURE line. CLEAR wa_line. CLEAR wa_line. IF NOT it_heading[] IS INITIAL. IF NOT it_heading [] IS INITIAL. LOOP AT it_heading. LOOP AT it_heading. CONCATENATE wa_line-line CONCATENATE wa_line-line it_heading-line w_tab INTO wa_line-line. INTO wa_line-line. CONDENSE wa_line. CONDENSE wa_line. ENDLOOP. APPEND wa_line TO it_line. APPEND wa_line TO it_line. ENDIF. LOOP AT it_data. LOOP AT it_data. CLEAR wa_line. CLEAR wa_line. DO. ASSIGN COMPONENT sy-index OF STRUCTURE it_data TO <fs_field>. ASSIGN COMPONENT sy-index OF STRUCTURE it_data TO <fs_field>. IF NOT sy-subrc IS INITIAL. IF NOT sy-subrc IS INITIAL. EXIT. ENDIF. w_field = <fs_field>. w_field = <fs_field>. CONDENSE w_field. CONDENSE w_field. CONCATENATE wa_line-line CONCATENATE wa_line-line w_field w_tab INTO wa_line-line. INTO wa_line-line. CONDENSE wa_line. CONDENSE wa_line. ENDDO. APPEND wa_line TO it_line. APPEND wa_line TO it_line. ENDLOOP. ENDFORM. " prepare_int_tab "Prepare_int_tab *&---------------------------------------------------------- *&------------------------------------------------ ---------- *& Form create_excel_sheet * & Form create_excel_sheet *&---------------------------------------------------------- *&------------------------------------------------ ---------- * text * Text *----------------------------------------------------------- *------------------------------------------------- ---------- * --> p1 text * -> P1 text * <-- p2 text * <- P2 text *----------------------------------------------------------- *------------------------------------------------- ---------- FORM create_excel_sheet USING p_filename FORM create_excel_sheet USING p_filename p_tabname w_data CHANGING p_file_already_exists. CHANGING p_file_already_exists. DATA: l_cols TYPE i, l_cols TYPE i, l_rows TYPE i, l_rows TYPE i, l_name TYPE char16, l_name TYPE char16, l_rc TYPE sy-subrc, l_rc TYPE sy-subrc, l_res TYPE abap_bool, l_res TYPE abap_bool, l_type TYPE c, l_type TYPE c, l_file TYPE string, l_file TYPE string, l_from TYPE ole2_object, l_from TYPE ole2_object, l_to TYPE ole2_object, l_to TYPE ole2_object, l_entcol TYPE ole2_object. l_entcol TYPE ole2_object. CREATE OBJECT w_excel 'Excel.Application'. CREATE OBJECT w_excel 'Excel.Application'. ole_error sy-subrc. ole_error sy-subrc. CALL METHOD OF w_excel 'Workbooks' = w_wbooks. CALL METHOD OF w_excel 'Workbooks' = w_wbooks. ole_error sy-subrc. ole_error sy-subrc. * SET PROPERTY OF w_excel 'Visible' = 1. * SET PROPERTY OF w_excel 'Visible' = 1. ole_error sy-subrc. ole_error sy-subrc. l_file = p_filename. l_file = p_filename. CLEAR l_res. CLEAR l_res. CALL METHOD cl_gui_frontend_services=>file_exist CALL METHOD cl_gui_frontend_services => file_exist EXPORTING file            = l_file file = l_file RECEIVING result          = l_res result = l_res EXCEPTIONS cntl_error      = 1 cntl_error = 1 error_no_gui    = 2 error_no_gui = 2 wrong_parameter = 3 wrong_parameter = 3 OTHERS          = 4. OTHERS = 4. IF sy-subrc <> 0. IF sy-subrc <> 0. MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. ENDIF. IF l_res IS INITIAL. IF l_res IS INITIAL. CLEAR p_file_already_exists. CLEAR p_file_already_exists. ELSE. p_file_already_exists = 'X'. p_file_already_exists = 'X'. ENDIF. IF NOT p_file_already_exists IS INITIAL. IF NOT p_file_already_exists IS INITIAL. * Open the existing file in case if it exists * Open the existing file in case if it exists CALL METHOD OF w_wbooks 'Open' CALL METHOD OF w_wbooks' Open ' EXPORTING #1 = p_filename. # 1 = p_filename. ole_error sy-subrc. ole_error sy-subrc. CALL METHOD OF w_excel 'Sheets' = w_sheets. CALL METHOD OF w_excel 'Sheets' = w_sheets. ole_error sy-subrc. ole_error sy-subrc. CALL METHOD OF w_sheets 'Add'. CALL METHOD OF w_sheets' Add '. ole_error sy-subrc. ole_error sy-subrc. GET PROPERTY OF w_excel 'ActiveSheet' = w_wbook. GET PROPERTY OF w_excel 'ActiveSheet' = w_wbook. ole_error sy-subrc. ole_error sy-subrc. ELSE. CALL METHOD OF w_wbooks 'Add'. CALL METHOD OF w_wbooks' Add '. " = w_wbook. "= W_wbook. ole_error sy-subrc. ole_error sy-subrc. GET PROPERTY OF w_excel 'ActiveSheet' = w_wbook. GET PROPERTY OF w_excel 'ActiveSheet' = w_wbook. ole_error sy-subrc. ole_error sy-subrc. ENDIF. IF NOT p_tabname IS INITIAL. IF NOT p_tabname IS INITIAL. SET PROPERTY OF w_wbook 'Name' = p_tabname. SET PROPERTY OF w_wbook 'Name' = p_tabname. ole_error sy-subrc. ole_error sy-subrc. ENDIF. CALL METHOD OF w_wbook 'Cells' = l_from CALL METHOD OF w_wbook 'Cells' = l_from EXPORTING #1 = c_bgrw # 1 = c_bgrw #2 = c_bgcl. # 2 = c_bgcl. ole_error sy-subrc. ole_error sy-subrc. DESCRIBE FIELD w_data TYPE l_type COMPONENTS l_cols. DESCRIBE FIELD w_data TYPE l_type COMPONENTS l_cols. DESCRIBE TABLE it_line LINES l_rows. DESCRIBE TABLE it_line LINES l_rows. CALL METHOD OF w_wbook 'Cells' = l_to CALL METHOD OF w_wbook 'Cells' = l_to EXPORTING #1 = l_rows # 1 = l_rows #2 = l_cols. # 2 = l_cols. ole_error sy-subrc. ole_error sy-subrc. CALL METHOD OF w_wbook 'Range' = w_range CALL METHOD OF w_wbook 'Range' = w_range EXPORTING #1 = l_from # 1 = l_from #2 = l_to. # 2 = l_to. ole_error sy-subrc. ole_error sy-subrc. CALL METHOD cl_gui_frontend_services=>clipboard_export CALL METHOD cl_gui_frontend_services => clipboard_export IMPORTING data         = it_line data = it_line CHANGING rc           = l_rc rc = l_rc EXCEPTIONS cntl_error   = 1 cntl_error = 1 error_no_gui = 2 error_no_gui = 2 OTHERS       = 3. OTHERS = 3. IF sy-subrc <> 0 IF sy-subrc <> 0 OR NOT l_rc IS INITIAL. OR NOT l_rc IS INITIAL. MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 RAISING clipboard_export_error. RAISING clipboard_export_error. ENDIF. CALL METHOD OF w_range 'Select'. CALL METHOD OF w_range 'Select'. ole_error sy-subrc. ole_error sy-subrc. CALL METHOD OF w_wbook 'Paste'. CALL METHOD OF w_wbook 'Paste'. ole_error sy-subrc. ole_error sy-subrc. WHILE l_cols GT 0. WHILE l_cols GT 0. l_rows = 1. l_rows = 1. CALL METHOD OF w_excel 'Columns' = w_cell CALL METHOD OF w_excel 'Columns' = w_cell EXPORTING #1 = l_cols. # 1 = l_cols. ole_error sy-subrc. ole_error sy-subrc. CALL METHOD OF w_cell 'EntireColumn' = l_entcol. CALL METHOD OF w_cell 'EntireColumn' = l_entcol. ole_error sy-subrc. ole_error sy-subrc. l_cols = l_cols - 1. l_cols = l_cols - 1. CALL METHOD OF l_entcol 'Autofit'. CALL METHOD OF l_entcol 'Autofit'. ole_error sy-subrc. ole_error sy-subrc. ENDWHILE. ENDFORM. " create_excel_sheet "Create_excel_sheet *&---------------------------------------------------------- *&------------------------------------------------ ---------- *& Form format_cells * & Form format_cells *&---------------------------------------------------------- *&------------------------------------------------ ---------- * text * Text *----------------------------------------------------------- *------------------------------------------------- ---------- * -->P_FILENAME text * -> P_FILENAME text * -->P_FILE_ALREADY_EXISTS text * -> P_FILE_ALREADY_EXISTS text *----------------------------------------------------------- *------------------------------------------------- ---------- FORM format_cells TABLES it_formatopt STRUCTURE zformatoptions FORM format_cells TABLES it_formatopt STRUCTURE zformatoptions USING p_filename TYPE rlgrap-filename USING p_filename TYPE rlgrap-filename p_file_already_exists TYPE c. p_file_already_exists TYPE c. DATA: l_row TYPE i, l_row TYPE i, l_col TYPE i, l_col TYPE i, l_entcol TYPE ole2_object, l_entcol TYPE ole2_object, l_cols TYPE ole2_object, l_cols TYPE ole2_object, l_comment TYPE ole2_object. l_comment TYPE ole2_object. LOOP AT it_formatopt. LOOP AT it_formatopt. CLEAR: l_row, l_col. CLEAR: l_row, l_col. l_row = it_formatopt-row. l_row = it_formatopt-row. l_col = it_formatopt-col. l_col = it_formatopt-col. CALL METHOD OF w_wbook 'Cells' = w_cell CALL METHOD OF w_wbook 'Cells' = w_cell EXPORTING #1 = l_row # 1 = l_row #2 = l_col. # 2 = l_col. ole_error sy-subrc. ole_error sy-subrc. IF NOT it_formatopt-bold IS INITIAL. IF NOT it_formatopt-bold IS INITIAL. CALL METHOD OF w_cell 'Font' = w_font. CALL METHOD OF w_cell 'Font' = w_font. ole_error sy-subrc. ole_error sy-subrc. SET PROPERTY OF w_font 'Bold' = 1. SET PROPERTY OF w_font 'Bold' = 1. ole_error sy-subrc. ole_error sy-subrc. CALL METHOD OF w_excel 'Columns' = l_cols CALL METHOD OF w_excel 'Columns' = l_cols EXPORTING #1 = l_col. # 1 = l_col. ole_error sy-subrc. ole_error sy-subrc. CALL METHOD OF l_cols 'EntireColumn' = l_entcol. CALL METHOD OF l_cols' EntireColumn '= l_entcol. ole_error sy-subrc. ole_error sy-subrc. CALL METHOD OF l_entcol 'Autofit'. CALL METHOD OF l_entcol 'Autofit'. ole_error sy-subrc. ole_error sy-subrc. ENDIF. IF NOT it_formatopt-color IS INITIAL. IF NOT it_formatopt-color IS INITIAL. CALL METHOD OF w_cell 'Interior' = w_format. CALL METHOD OF w_cell 'Interior' = w_format. ole_error sy-subrc. ole_error sy-subrc. SET PROPERTY OF w_format 'ColorIndex' = it_formatopt-color. SET PROPERTY OF w_format 'ColorIndex' = it_formatopt-color. ole_error sy-subrc. ole_error sy-subrc. CALL METHOD OF w_excel 'Columns' = l_cols CALL METHOD OF w_excel 'Columns' = l_cols EXPORTING #1 = l_col. # 1 = l_col. ole_error sy-subrc. ole_error sy-subrc. CALL METHOD OF l_cols 'EntireColumn' = l_entcol. CALL METHOD OF l_cols' EntireColumn '= l_entcol. ole_error sy-subrc. ole_error sy-subrc. CALL METHOD OF l_entcol 'Autofit'. CALL METHOD OF l_entcol 'Autofit'. ole_error sy-subrc. ole_error sy-subrc. ENDIF. IF NOT it_formatopt-vert IS INITIAL. IF NOT it_formatopt-vert IS INITIAL. SET PROPERTY OF w_cell 'Orientation' = it_formatopt-vert. SET PROPERTY OF w_cell 'Orientation' = it_formatopt-vert. ole_error sy-subrc. ole_error sy-subrc. CALL METHOD OF w_excel 'Columns' = l_cols CALL METHOD OF w_excel 'Columns' = l_cols EXPORT