Comments
Multiple sheet Excel Document from SAP using OLE
The following ABAP code builds on that learnt from Creating a basic excel document using OLE. The code below demonstrates how to download an SAP table into an excel spreadsheet and then do some basic formatting to it, Including the following:
• Making a range of cells bold (i.e. header line)
• Assigning specific font style and size to range of cells
• Making the columns auto fit to the size of the text
• Assigning a specific shading colour to a cell or range of cells
• Assigning a border to a range of cells (including different types and weights)
*&------------------------------------------------------* *& Report ZFORMATEXCEL * *& * *&------------------------------------------------------* *& Author: SAP ABAP Development Website www.sapdev.co.uk* *& * *&------------------------------------------------------* REPORT ZFORMATEXCEL . INCLUDE ole2incl. DATA: application TYPE ole2_object, workbook TYPE ole2_object, sheet TYPE ole2_object, cells TYPE ole2_object, cell1 TYPE ole2_object, cell2 TYPE ole2_object, range TYPE ole2_object, font TYPE ole2_object, column TYPE ole2_object, shading TYPE ole2_object, border TYPE ole2_object. CONSTANTS: row_max TYPE i VALUE 256. DATA index TYPE i. Data: ld_colindx type I, "column index ld_rowindx type i. "row index types: begin of t_data, field1 type string, field2 type string, field3 type string, field4 type string, field5 type string, field6 type string, field7 type string, field8 type string, field9 type string, field10 type string, field11 type string, field12 type string, field13 type string, field14 type string, field15 type string, field16 type string, end of t_data. data: it_header type STANDARD TABLE OF t_data, wa_header like LINE OF it_header, it_data type STANDARD TABLE OF t_data, wa_data like LINE OF it_data. *field symbol to hold values field-symbols: <fs>. DATA: BEGIN OF itab1 OCCURS 0, first_name(10), END OF itab1. DATA: BEGIN OF itab2 OCCURS 0, last_name(10), END OF itab2. DATA: BEGIN OF itab3 OCCURS 0, formula(50), END OF itab3. ************************************************************************ *START-OF-SELECTION START-OF-SELECTION. wa_header-field1 = 'Header1'. wa_header-field2 = 'Header2'. wa_header-field3 = 'Header3'. wa_header-field4 = 'Header4'. wa_header-field5 = 'Header5'. wa_header-field6 = 'Header6'. wa_header-field7 = 'Header7'. wa_header-field8 = 'Header8'. wa_header-field9 = 'Header9'. wa_header-field10 = 'Longer Header10'. wa_header-field11 = 'Header11'. wa_header-field12 = 'Header12'. wa_header-field13 = 'Header13'. wa_header-field14 = 'Header14'. wa_header-field15 = 'Header15'. wa_header-field16 = 'Header16'. APPEND wa_header to it_header. wa_data-field1 = 'Clumn1 data'. wa_data-field2 = 'Column1 data'. wa_data-field3 = 'Column1 data'. wa_data-field4 = 'Column1 data'. wa_data-field5 = 'Column1 data'. wa_data-field6 = 'Column1 data'. wa_data-field7 = 'Column1 data'. wa_data-field8 = 'Column1 data'. wa_data-field9 = 'Column1 data'. wa_data-field10 = 'Column1 data'. wa_data-field11 = 'Column1 data'. wa_data-field12 = 'Column1 data'. wa_data-field13 = 'Column1 data'. wa_data-field14 = 'Column1 data'. wa_data-field15 = 'Column1 data'. wa_data-field16 = 'Column1 data'. APPEND wa_DATA to it_DATA. APPEND wa_DATA to it_DATA. CREATE OBJECT application 'excel.application'. SET PROPERTY OF application 'visible' = 1. CALL METHOD OF application 'Workbooks' = workbook. * Create new worksheet set property of application 'SheetsInNewWorkbook' = 1. CALL METHOD OF workbook 'Add'. * Create first Excel Sheet CALL METHOD OF application 'Worksheets' = sheet EXPORTING #1 = 1. CALL METHOD OF sheet 'Activate'. SET PROPERTY OF sheet 'Name' = 'Sheet1'. *********************************************** * Download header data to excel spreadsheet * *********************************************** ld_rowindx = 1. "start at row 1 for headings LOOP AT it_header INTO wa_header. *Use sy-tabix for row index ld_rowindx = sy-tabix. * Fill columns for current row Clear ld_colindx. Do. * Assign <fs> to table columns Assign component sy-index of structure wa_header to <fs>. If sy-subrc ne 0. Exit. Endif. ld_Colindx = sy-index. CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = ld_rowindx #2 = ld_colindx. SET PROPERTY OF cells 'Value' = <fs>. Enddo. ENDLOOP. *********************************************** * Download data to excel spreadsheet * *********************************************** CALL METHOD OF application 'Worksheets' = sheet EXPORTING #1 = 2. clear: ld_rowindx, ld_colindx. LOOP AT it_data into wa_data. ld_rowindx = sy-tabix + 1. "start at row 2 (leave 1st for for headings * Fill columns for current row Clear ld_colindx. Do. Assign component sy-index of structure wa_data to <fs>. If sy-subrc ne 0. Exit. Endif. ld_colindx = sy-index. CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = ld_rowindx #2 = ld_colindx. SET PROPERTY OF cells 'Value' = <fs>. Enddo. ENDLOOP. *********************************************** * Capture range of cells to be modified * *********************************************** *Start of range Cell CALL METHOD of application 'Cells' = cell1 EXPORTING #1 = 1 "down #2 = 1. "across *End of range Cell CALL METHOD of application 'Cells' = cell2 EXPORTING #1 = 1 "down #2 = 16. "across CALL METHOD of application 'Range' = range EXPORTING #1 = cell1 #2 = cell2. *********************************************** * Modify properties of cell range * *********************************************** * SET FONT DETAILS of range get PROPERTY OF range 'Font' = font. set PROPERTY OF font 'Bold' = 1. set property of font 'Size' = 10. * SET CELL SHADING PROPERTIES of range call method of range 'INTERIOR' = shading. set property of shading 'ColorIndex' = 15. "colour - change number for diff colours set property of shading 'Pattern' = 1. "pattern - solid, striped etc free object shading. * CHANGE RANGE VALUES - include all lines (1,2 and 3) free range. CALL METHOD of application 'Cells' = cell1 "start cell EXPORTING #1 = 1 "down #2 = 1. "across CALL METHOD of application 'Cells' = cell2 "end cell EXPORTING #1 = 3 "down #2 = 16. "across CALL METHOD of application 'Range' = range EXPORTING #1 = cell1 #2 = cell2. * SET BORDER PROPERTIES of range call method of range 'BORDERS' = BORDER exporting #1 = '1'. "left set property of border 'LineStyle' = '1'. "line style solid, dashed... set property of border 'WEIGHT' = 2. "max = 4 free object border. call method of range 'BORDERS' = BORDER exporting #1 = '2'. "right set property of border 'LineStyle' = '1'. set property of border 'WEIGHT' = 2. "max = 4 free object border. call method of range 'BORDERS' = BORDER exporting #1 = '3'. "top set property of border 'LineStyle' = '1'. set property of border 'WEIGHT' = 2. "max = 4 free object border. call method of range 'BORDERS' = BORDER exporting #1 = '4'. "bottom set property of border 'LineStyle' = '1'. set property of border 'WEIGHT' = 2. "max = 4 free object border. * Overwites all cell values in range to equal 'test' * SET PROPERTY OF range 'VALUE' = 'test'. *********************************************** * Set Columns to auto fit to width of text * *********************************************** CALL METHOD OF application 'Columns' = COLUMN. CALL METHOD OF COLUMN 'Autofit'. free object COLUMN. *********************************************** * Save excel speadsheet to particular filename* *********************************************** CALL METHOD OF sheet 'SaveAs' EXPORTING #1 = 'c:\temp\exceldoc2.xls' "filename #2 = 1. "fileFormat free object sheet. free object workbook. free object application. * Closes excel window, data is lost if not saved * SET PROPERTY OF application 'visible' = 0. * call method of workbook 'CLOSE'. * call method of excel 'QUIT'.