Problem when down loading a simple report to Excel sheet

[Former Member]

HI Frenz,

When I download a Simple report to excel , The Excel shows a blank column in between two column, which should not be the case.

Kindly let me know ASAP

Regards

Irfan

[Former Member]

hi this may help you,

REPORT ZTESTPROG002.

tables: mara.

PARAMETERS: p_file LIKE rlgrap-filename default 'C:\Documents and Settings\venkatapp\Desktop\venkat.xls'.

data:begin of it_mara occurs 0,

matnr like mara-matnr,

meins like mara-meins,

mtart like mara-mtart,

mbrsh like mara-mbrsh,

end of it_mara.

SELECT matnr

meins

mtart

mbrsh

FROM mara

INTO corresponding fields of TABLE it_mara.

CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'

EXPORTING

i_filename = p_file

TABLES

i_tab_sap_data = it_mara.

reward points if useful,

venkat.

[Former Member]

Thnks Venkat,

But I am displaying the classical report,

Only if the user wants then he can export it to excel ..

by going to List -> Save - > File -> Spreadsheet.

In this Excel sheet the problem exists.

[Former Member]

Hi,

Check out the below samle code

Download a report to excel with format (border, color cell, etc)

REPORT ZSIRI NO STANDARD PAGE HEADING. 
* this report demonstrates how to send some ABAP data to an 
* EXCEL sheet using OLE automation. 
INCLUDE OLE2INCL. 
* handles for OLE objects 
DATA: H_EXCEL TYPE OLE2_OBJECT,        " Excel object 
      H_MAPL TYPE OLE2_OBJECT,         " list of workbooks 
      H_MAP TYPE OLE2_OBJECT,          " workbook 
      H_ZL TYPE OLE2_OBJECT,           " cell 
      H_F TYPE OLE2_OBJECT.            " font 
TABLES: SPFLI. 
DATA  H TYPE I. 
* table of flights 
DATA: IT_SPFLI LIKE SPFLI OCCURS 10 WITH HEADER LINE. 




*&---------------------------------------------------------------------* 
*&   Event START-OF-SELECTION 
*&---------------------------------------------------------------------* 
START-OF-SELECTION. 
* read flights 
  SELECT * FROM SPFLI INTO TABLE IT_SPFLI UP TO 10 ROWS. 
* display header 
  ULINE (61). 
  WRITE: /     SY-VLINE NO-GAP, 
          (3)  'Flg'(001) COLOR COL_HEADING NO-GAP, SY-VLINE NO-GAP, 
          (4)  'Nr'(002) COLOR COL_HEADING NO-GAP, SY-VLINE NO-GAP, 
          (20) 'Von'(003) COLOR COL_HEADING NO-GAP, SY-VLINE NO-GAP, 
          (20) 'Nach'(004) COLOR COL_HEADING NO-GAP, SY-VLINE NO-GAP, 
          (8)  'Zeit'(005) COLOR COL_HEADING NO-GAP, SY-VLINE NO-GAP. 
  ULINE /(61). 
* display flights 
  LOOP AT IT_SPFLI. 
  WRITE: / SY-VLINE NO-GAP, 
           IT_SPFLI-CARRID COLOR COL_KEY NO-GAP, SY-VLINE NO-GAP, 
           IT_SPFLI-CONNID COLOR COL_NORMAL NO-GAP, SY-VLINE NO-GAP, 
           IT_SPFLI-CITYFROM COLOR COL_NORMAL NO-GAP, SY-VLINE NO-GAP, 
           IT_SPFLI-CITYTO COLOR COL_NORMAL NO-GAP, SY-VLINE NO-GAP, 
           IT_SPFLI-DEPTIME COLOR COL_NORMAL NO-GAP, SY-VLINE NO-GAP. 
  ENDLOOP. 
  ULINE /(61). 
* tell user what is going on 
  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' 
     EXPORTING 
*           PERCENTAGE = 0 
           TEXT       = TEXT-007 
       EXCEPTIONS 
            OTHERS     = 1. 
* start Excel 
  CREATE OBJECT H_EXCEL 'EXCEL.APPLICATION'. 
*  PERFORM ERR_HDL. 

  SET PROPERTY OF H_EXCEL  'Visible' = 1. 
*  CALL METHOD OF H_EXCEL 'FILESAVEAS' EXPORTING #1 = 'c:\kis_excel.xls' 
. 

*  PERFORM ERR_HDL. 
* tell user what is going on 
  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' 
     EXPORTING 
*           PERCENTAGE = 0 
           TEXT       = TEXT-008 
       EXCEPTIONS 
            OTHERS     = 1. 
* get list of workbooks, initially empty 
  CALL METHOD OF H_EXCEL 'Workbooks' = H_MAPL. 
  PERFORM ERR_HDL. 
* add a new workbook 
  CALL METHOD OF H_MAPL 'Add' = H_MAP. 
  PERFORM ERR_HDL. 
* tell user what is going on 
  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' 
     EXPORTING 
*           PERCENTAGE = 0 
           TEXT       = TEXT-009 
       EXCEPTIONS 
            OTHERS     = 1. 
* output column headings to active Excel sheet 
  PERFORM FILL_CELL USING 1 1 1 'Flug'(001). 
  PERFORM FILL_CELL USING 1 2 0 'Nr'(002). 
  PERFORM FILL_CELL USING 1 3 1 'Von'(003). 
  PERFORM FILL_CELL USING 1 4 1 'Nach'(004). 
  PERFORM FILL_CELL USING 1 5 1 'Zeit'(005). 
  LOOP AT IT_SPFLI. 
* copy flights to active EXCEL sheet 
    H = SY-TABIX + 1. 
    PERFORM FILL_CELL USING H 1 0 IT_SPFLI-CARRID. 
    PERFORM FILL_CELL USING H 2 0 IT_SPFLI-CONNID. 
    PERFORM FILL_CELL USING H 3 0 IT_SPFLI-CITYFROM. 
    PERFORM FILL_CELL USING H 4 0 IT_SPFLI-CITYTO. 
    PERFORM FILL_CELL USING H 5 0 IT_SPFLI-DEPTIME. 
  ENDLOOP. 

* changes by Kishore  - start 
*  CALL METHOD OF H_EXCEL 'Workbooks' = H_MAPL. 
  CALL METHOD OF H_EXCEL 'Worksheets' = H_MAPL." EXPORTING #1 = 2. 

  PERFORM ERR_HDL. 
* add a new workbook 
  CALL METHOD OF H_MAPL 'Add' = H_MAP  EXPORTING #1 = 2. 
  PERFORM ERR_HDL. 
* tell user what is going on 
  SET PROPERTY OF H_MAP 'NAME' = 'COPY'. 
  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' 
     EXPORTING 
*           PERCENTAGE = 0 
           TEXT       = TEXT-009 
       EXCEPTIONS 
            OTHERS     = 1. 
* output column headings to active Excel sheet 
  PERFORM FILL_CELL USING 1 1 1 'Flug'(001). 
  PERFORM FILL_CELL USING 1 2 0 'Nr'(002). 
  PERFORM FILL_CELL USING 1 3 1 'Von'(003). 
  PERFORM FILL_CELL USING 1 4 1 'Nach'(004). 
  PERFORM FILL_CELL USING 1 5 1 'Zeit'(005). 
  LOOP AT IT_SPFLI. 
* copy flights to active EXCEL sheet 
    H = SY-TABIX + 1. 
    PERFORM FILL_CELL USING H 1 0 IT_SPFLI-CARRID. 
    PERFORM FILL_CELL USING H 2 0 IT_SPFLI-CONNID. 
    PERFORM FILL_CELL USING H 3 0 IT_SPFLI-CITYFROM. 
    PERFORM FILL_CELL USING H 4 0 IT_SPFLI-CITYTO. 
    PERFORM FILL_CELL USING H 5 0 IT_SPFLI-DEPTIME. 
  ENDLOOP. 
* changes by Kishore  - end 
* disconnect from Excel 
*      CALL METHOD OF H_EXCEL 'FILESAVEAS' EXPORTING  #1 = 'C:\SKV.XLS'. 

  FREE OBJECT H_EXCEL. 
  PERFORM ERR_HDL. 
*---------------------------------------------------------------------* 
*       FORM FILL_CELL                                                * 
*---------------------------------------------------------------------* 
*       sets cell at coordinates i,j to value val boldtype bold       * 
*---------------------------------------------------------------------* 
FORM FILL_CELL USING I J BOLD VAL. 
  CALL METHOD OF H_EXCEL 'Cells' = H_ZL EXPORTING #1 = I #2 = J. 
  PERFORM ERR_HDL. 
  SET PROPERTY OF H_ZL 'Value' = VAL . 
  PERFORM ERR_HDL. 
  GET PROPERTY OF H_ZL 'Font' = H_F. 
  PERFORM ERR_HDL. 
  SET PROPERTY OF H_F 'Bold' = BOLD . 
  PERFORM ERR_HDL. 
ENDFORM. 
*&---------------------------------------------------------------------* 
*&      Form  ERR_HDL 
*&---------------------------------------------------------------------* 
*       outputs OLE error if any                                       * 
*----------------------------------------------------------------------* 
*  -->  p1        text 
*  <--  p2        text 
*----------------------------------------------------------------------* 
FORM ERR_HDL. 
IF SY-SUBRC <> 0. 
  WRITE: / 'Fehler bei OLE-Automation:'(010), SY-SUBRC. 
  STOP. 
ENDIF. 
ENDFORM.                    " ERR_HDL 

Cheers,

Chandru