excel download..

[Former Member]

i have around 60k records of data and i want to download it to an excel sheet. but it is not fitting in one sheet. so how can i download it to multiple sheets with out using OLE concept. can i download with GUI_DOWNLOAD functionmodule using any counter option and tehn repeating that FM.

please do reply.

[Former Member]

Hi

function module WS_DOWNLOAD though this function module downloads the contents onto the Excel sheet, there cannot be any column headings or we cannot differentiate the primary keys just by seeing the Excel sheet.

For this purpose, we can use the function module XXL_FULL_API. The Excel sheet which is generated by this function module contains the column headings and the key columns are highlighted with a different color. Other options that are available with this function module are we can swap two columns or supress a field from displaying on the Excel sheet.

Check this sample report

 TABLES:

  sflight.

* header data................................
DATA :
  header1 LIKE gxxlt_p-text VALUE 'Pavan',
  header2 LIKE gxxlt_p-text VALUE 'Excel sheet'.

* Internal table for holding the SFLIGHT data
DATA BEGIN OF t_sflight OCCURS 0.
        INCLUDE STRUCTURE sflight.
DATA END   OF t_sflight.

* Internal table for holding the horizontal key.
DATA BEGIN OF  t_hkey OCCURS 0.
        INCLUDE STRUCTURE gxxlt_h.
DATA END   OF t_hkey .

* Internal table for holding the vertical key.
DATA BEGIN OF t_vkey OCCURS 0.
        INCLUDE STRUCTURE gxxlt_v.
DATA END   OF t_vkey .

* Internal table for holding the online text....
DATA BEGIN OF t_online OCCURS 0.
        INCLUDE STRUCTURE gxxlt_o.
DATA END   OF t_online.

* Internal table to hold print text.............
DATA BEGIN OF t_print OCCURS 0.
        INCLUDE STRUCTURE gxxlt_p.
DATA END   OF t_print.

* Internal table to hold SEMA data..............
DATA BEGIN OF t_sema OCCURS 0.
        INCLUDE STRUCTURE gxxlt_s.
DATA END   OF t_sema.

* Retreiving data from sflight.
SELECT * FROM sflight
         INTO TABLE t_sflight.

* Text which will be displayed online is declared here....
t_online-line_no    = '1'.
t_online-info_name  = 'Created by'.
t_online-info_value = 'PAVAN PRAVEEN'.
APPEND t_online.

* Text which will be printed out..........................
t_print-hf     = 'H'.
t_print-lcr    = 'L'.
t_print-line_no = '1'.
t_print-text   = 'This is the header'.
APPEND t_print.

t_print-hf     = 'F'.
t_print-lcr    = 'C'.
t_print-line_no = '1'.
t_print-text   = 'This is the footer'.
APPEND t_print.

* Defining the vertical key columns.......
t_vkey-col_no   = '1'.
t_vkey-col_name = 'MANDT'.
APPEND t_vkey.

t_vkey-col_no   = '2'.
t_vkey-col_name = 'CARRID'.
APPEND t_vkey.

t_vkey-col_no   = '3'.
t_vkey-col_name = 'CONNID'.
APPEND t_vkey.

t_vkey-col_no   = '4'.
t_vkey-col_name = 'FLDATE'.
APPEND t_vkey.

* Header text for the data columns................

t_hkey-row_no = '1'.
t_hkey-col_no = 1.
t_hkey-col_name = 'PRICE'.
APPEND t_hkey.

t_hkey-col_no = 2.
t_hkey-col_name = 'CURRENCY'.
APPEND t_hkey.

t_hkey-col_no = 3.
t_hkey-col_name = 'PLANETYPE'.
APPEND t_hkey.

t_hkey-col_no = 4.
t_hkey-col_name = 'SEATSMAX'.
APPEND t_hkey.

t_hkey-col_no = 5.
t_hkey-col_name = 'SEATSOCC'.
APPEND t_hkey.

t_hkey-col_no = 6.
t_hkey-col_name = 'PAYMENTSUM'.
APPEND t_hkey.

* populating the SEMA data..........................
t_sema-col_no  = 1.
t_sema-col_typ = 'STR'.
t_sema-col_ops = 'DFT'.
APPEND t_sema.

t_sema-col_no = 2.
APPEND t_sema.

t_sema-col_no = 3.
APPEND t_sema.

t_sema-col_no = 4.
APPEND t_sema.

t_sema-col_no = 5.
APPEND t_sema.

t_sema-col_no = 6.
APPEND t_sema.

t_sema-col_no = 7.
APPEND t_sema.

t_sema-col_no = 8.
APPEND t_sema.

t_sema-col_no = 9.
APPEND t_sema.

t_sema-col_no = 10.
t_sema-col_typ = 'NUM'.
t_sema-col_ops = 'ADD'.
APPEND t_sema.

CALL FUNCTION 'XXL_FULL_API'
  EXPORTING
*   DATA_ENDING_AT          = 54
*   DATA_STARTING_AT        = 5
   filename                = 'TESTFILE'
   header_1                = header1
   header_2                = header2
   no_dialog               = 'X'
   no_start                = ' '
    n_att_cols              = 6
    n_hrz_keys              = 1
    n_vrt_keys              = 4
   sema_type               = 'X'
*   SO_TITLE                = ' '
  TABLES
    data                    = t_sflight
    hkey                    = t_hkey
    online_text             = t_online
    print_text              = t_print
    sema                    = t_sema
    vkey                    = t_vkey
 EXCEPTIONS
   cancelled_by_user       = 1
   data_too_big            = 2
   dim_mismatch_data       = 3
   dim_mismatch_sema       = 4
   dim_mismatch_vkey       = 5
   error_in_hkey           = 6
   error_in_sema           = 7
   file_open_error         = 8
   file_write_error        = 9
   inv_data_range          = 10
   inv_winsys              = 11
   inv_xxl                 = 12
   OTHERS                  = 13
          .
IF sy-subrc <> 0.
  MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
          WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
 

Regards

Pavan

[Former Member]

hi ,

try ti use this FM SAP_CONVERT_TO_XLS_FORMAT.

this may help you.

[pradeep_nellore]

Hi Pavan,

I have developed a small algorithm for your requirement....

See if it helps.

1.If number of records >65000

Count number of records---Count

2.Divide Count by 65000--N

3.Rounf off N to next highest Intetger--P

P will be our number of files that we can download.

4.Do P times.

--loop at itab where SY-DBCNT=no_of_records (Initialize no_of_records to Zero)

--move 65000 records to TEMP_ITAB.(Declare TEMP_ITAB with similar structure to itab).

--Generate a name for file to download using string operations.

--Call GUI_DOWNLOAD.

--Delete Entries in TEMP_ITAB.

--no_of_records = SY-DBCNT.

--Endloop.

--Enddo.

Thanks

--Pradeep

[Former Member]

HI,

we can download nearly 63000 records to excel by using ws_download.

u can use the logic like this, use describe statement to know no of records in final internal table, use condition if records are more tthan < 60000 use fm ws_download, if more than 60000, move the records to another internal table and use another ws_download.

regards

siva