Creation of Pivot table in the Excel

[mohanakrushna]

Hello Experts,

We have a requirement as to a create a pivot table in the excel.

The Program run on the background only due to more no of records(approximately 5,00,000 weekly).

by using the FM MS_EXCEL_OLE_STANDARD_DAT not able to create the Pivot table.

I am able to create the Pivot table in the excel but not able to save that excel file.

The pivot table creation in the excel using the reference code of the Function module "XXL_SIMPLE_API".

The sample code follows

CALL METHOD cl_gui_frontend_services=>registry_get_value

EXPORTING root = cl_gui_frontend_services=>hkey_classes_root

key = 'ExcelWorksheet\protocol\StdFileEditing\server'

value = '' "

IMPORTING reg_value = excel_file.

excel_file_and_path = excel_file.

IF excel_file_and_path NE space. " Excel is installed

DATA registry_lookup2 TYPE string.

CALL METHOD cl_gui_frontend_services=>registry_get_value

EXPORTING root = cl_gui_frontend_services=>hkey_classes_root

key = 'Excel.Application\CurVer'

value = ''"

IMPORTING reg_value = registry_lookup2.

ENDIF.

IF registry_lookup2 NE space.

IF registry_lookup2 EQ 'Excel.Application.5'

OR registry_lookup2 EQ 'Excel.Application.6'

OR registry_lookup2 EQ 'Excel.Application.7'.

backup = stat. stat = 'C'.

max_row_xxl_limit = 16384.

ELSE.

IF registry_lookup2 EQ 'Excel.Application.12' OR

registry_lookup2 EQ 'Excel.Application.14'

OR registry_lookup2 EQ 'Excel.Application.15'

OR registry_lookup2 EQ 'Excel.Application.16'.

max_row_xxl_limit = 1048576.

ELSE. max_row_xxl_limit = 65536.

ENDIF.

ENDIF.

ENDIF.

IF ( max_col_xxl GT 256 ) OR ( max_row_xxl GT max_row_xxl_limit ).

** MESSAGE E020 RAISING DATA_TOO_BIG.

ENDIF.

CALL FUNCTION 'RSAU_WRITE_DOWNLOAD_AUDIT_LOG'

EXPORTING

filename = 'Excel file'

filetype = 'XLS'

filelength = max_row_xxl mode = 'S'

EXCEPTIONS

parameter_error = 1

OTHERS = 99.

CALL METHOD cl_gui_frontend_services=>registry_get_value

EXPORTING

root = cl_gui_frontend_services=>hkey_classes_root

key = 'Excel.Application\CurVer'

value = ''"

IMPORTING reg_value = registry_lookup.

IF registry_lookup NE space.

kalkul-varoption = 'Microsoft Excel'.

APPEND kalkul. excel = 'X'.

ENDIF.

CALL METHOD cl_gui_frontend_services=>registry_get_value

EXPORTING

root = cl_gui_frontend_services=>hkey_classes_root

key = 'Lotus123.Workbook\CLSID'

value = ''

IMPORTING reg_value = registry_lookup.

CALL METHOD c_oi_container_control_creator=>get_container_control

IMPORTING control = control

error = error.

CREATE OBJECT container

EXPORTING

container_name = 'CONTAINER'.

CALL METHOD control->init_control

EXPORTING

r3_application_name = 'Basis' "#EC NOTEXT inplace_

enabled = ''

inplace_scroll_documents = 'X'

parent = container

register_on_close_event = 'X'

register_on_custom_event = 'X'

no_flush = 'X'

IMPORTING

error = error.

CALL METHOD control->get_document_proxy

EXPORTING

document_type = 'Excel.Sheet'

IMPORTING

document_proxy = document

error = error.

IF NOT document IS INITIAL.

CALL METHOD document->create_document

EXPORTING

open_inplace = ''.

CALL METHOD document->get_pivot_interface

IMPORTING

pivot_interface = pivot.

ENDIF.

CALL METHOD pivot->insert_full

EXPORTING

n_vrt_keys = v

n_hrz_keys = h

n_att_cols = a

sema = xmplt_s[]

hkey = local_hkey[]

vkey = y_it_col_text[]

online_text = y_it_online_text[]

data = lt_final[]

EXCEPTIONS

dim_mismatch_data = 1

dim_mismatch_sema = 2

dim_mismatch_vkey = 3

error_in_hkey = 4

error_in_sema = 5

inv_data_range = 6

error_in_vkey = 7.