How to convert the SAP data into multiple excel sh...

former_member207480 · ‎02-12-2015

Hi,

I am facing issue in downloading SAP data into multiple excel sheet.

I have the smartform data in sap. I want to convert as it is in excel with table and border and content.

Also, Is there any other way to design the excel layout in abap editor?

Please suggest the same.

- Amol Khochare

Message was edited by: Thea Hillenbrand: I deselected the category ABAP Trials and Developer Editions. This category should be used for ABAP systems in the Cloud Applicance Library related contributions. Kind regards, Thea

former_member191253 · ‎02-12-2015

Hi Amol,

I think this link will help you.

Download Data into Multiple Sheet Excel Document with Non Editable Columns (Password protected) Usin...

former_member207480 · ‎02-13-2015

Hi Swaroop,

I have check the given document and it is very useful.

But, I have different scenarios like in excel there is tables, borders and contents.

Please check below screen shots.

mani_sekar · ‎02-13-2015

Hi Amol,

Below link will use to download the report in excel with formatted cell

http://www.erpgreat.com/abap/download-to-excel-with-format-border-color-cell-etc.htm

Regards,

Manikandan S

former_member207480 · ‎02-13-2015

Hi Swaroop,

I understook what you are saying. But, I do not want like ALV report.

The file should be generated like the same as balance sheet format or P&L format.

Regards

Amol Khochare

former_member191253 · ‎02-16-2015

For that type of layout you need to write so many OLE codes for every cell and you have to use concept of Cell Merging column merging.

Regards,

Swaroop.

SimoneMilesi · ‎02-13-2015

Did you checked the class abap2xlsx - ABAP Development - SCN Wiki

Former Member · ‎02-18-2015

Hello Amol,

Please check below code.

DECLARE INTO TOP

-----------------

TYPE-POOLS: ole2.

* Ole data Declarations

DATA: gs_excel TYPE ole2_object, " Excel object

gs_wbooks TYPE ole2_object, " List of Workbooks

gs_wbook TYPE ole2_object, " Workbook

gs_cells TYPE ole2_object,

gs_cell1 TYPE ole2_object,

gs_cell2 TYPE ole2_object,

gs_font TYPE ole2_object,

gs_inth TYPE ole2_object,

gs_intf TYPE ole2_object,

gs_auto TYPE ole2_object,

gs_wind TYPE ole2_object,

gs_sele TYPE ole2_object,

gs_prot TYPE ole2_object,

gs_range TYPE ole2_object,

gs_rows TYPE ole2_object,

gs_cols TYPE ole2_object,

gs_border TYPE ole2_object,

gs_logo TYPE ole2_object,

gs_pgsetup TYPE ole2_object,

gs_shapes TYPE ole2_object,

gs_valid TYPE ole2_object,

gs_select TYPE ole2_object,

gs_appli TYPE ole2_object,

gs_sheet TYPE ole2_object.

DATA: verexcel TYPE string,

moff TYPE string,

mlen TYPE i,

numexcel TYPE i,

intexcel TYPE i,

mydocuments TYPE string,

myfile(255) TYPE c,

mcfile(255) TYPE c,

mysave(255) TYPE c,

wstrlen TYPE i,

index TYPE i,

numrows TYPE i,

numrows1 TYPE i.

DATA: gs_row TYPE i VALUE 1,

gs_col TYPE i VALUE 1,

gs_ridx TYPE i,

gs_cidx TYPE i.

FIELD-SYMBOLS: <fs>.

DATA: asondt(20) TYPE c,

rundate(20) TYPE c,

monthtext TYPE t247-ltx,

wtitle(200) TYPE c,

cyear(50) TYPE c,

pyear(50) TYPE c,

wsname TYPE t001s-sname.

DATA: ld_filename TYPE string,

ld_path TYPE string,

ld_fullpath TYPE string,

ld_result TYPE i.

-----------------------------------------------------------------------

PERFORM f_create_excel.

*&---------------------------------------------------------------------*

*& Form F_CREATE_EXCEL

*&---------------------------------------------------------------------*

FORM f_create_excel.

" Start Excel

IF gs_excel-header = space OR gs_excel-handle = -1.

CREATE OBJECT gs_excel 'EXCEL.APPLICATION'.

IF sy-subrc NE 0.

MESSAGE i398(00) WITH 'Problem to Creating Excel File'.

ENDIF.

" Get list of workbooks, initially empty

SET PROPERTY OF gs_excel 'DisplayAlerts' = 0.

CALL METHOD OF gs_excel 'Workbooks' = gs_wbooks.

SET PROPERTY OF gs_excel: 'Visible' = 0,

'SheetsInNewWorkbook' = 1.

CALL METHOD OF gs_wbooks 'Add' = gs_wbook.

* IF LINES( gt_final ) GT 0.

PERFORM f_add_sheet USING <your table name> 'Balance Sheet' gs_sheet. "ENDIF.

" AutoFit

CALL METHOD OF gs_excel 'Columns' = gs_cols.

CALL METHOD OF gs_cols 'Autofit'.

" Autofilter

* SET PROPERTY OF gs_cells 'AutoFilter' = 1.

" FreezePanes

CALL METHOD OF gs_excel 'Range' = gs_range EXPORTING #1 = 'A6'.

CALL METHOD OF gs_range 'Select'.

GET PROPERTY OF gs_excel 'ActiveWindow' = gs_wind.

SET PROPERTY OF gs_wind 'FreezePanes' = 1.

" Border Properties

* CALL METHOD OF gs_excel 'Cells' = gs_cell1

* EXPORTING #1 = 5 #2 = 1.

* CALL METHOD OF gs_excel 'Cells' = gs_cell2

* EXPORTING #1 = gs_row #2 = 8.

* CALL METHOD OF gs_excel 'Range' = gs_range

* EXPORTING #1 = gs_cell1 #2 = gs_cell2.

* +----------------------+

* + LINESTYLE +

* + 1 = Continuous +

* + 4 = DashDot +

* + 5 = DashDotDot +

* + -4115 = Dash +

* + -4119 = Double +

* +----------------------+

* + WEIGHT +

* + 1 = Hairline +

* + 2 = Thin +

* + 4 = Thick +

* +----------------------+

CALL METHOD OF gs_range 'BORDERS' = gs_border

EXPORTING #1 = '1'. " Left

SET PROPERTY OF gs_border: 'LineStyle' = 1,

'Weight' = 2.

CALL METHOD OF gs_range 'BORDERS' = gs_border

EXPORTING #1 = '2'. " Right

SET PROPERTY OF gs_border: 'LineStyle' = 1,

'Weight' = 2.

CALL METHOD OF gs_range 'BORDERS' = gs_border

EXPORTING #1 = '3'. " Top

SET PROPERTY OF gs_border: 'LineStyle' = 1,

'Weight' = 2.

CALL METHOD OF gs_range 'BORDERS' = gs_border

EXPORTING #1 = '4'. " Bottom

SET PROPERTY OF gs_border: 'LineStyle' = 1,

'Weight' = 2.

" Page Setup

GET PROPERTY OF gs_excel 'ActiveSheet' = gs_sheet.

GET PROPERTY OF gs_sheet 'PageSetup' = gs_pgsetup.

" Page

SET PROPERTY OF gs_pgsetup: 'Orientation' = 2, " 1 = Portrait | 2 = Lanscape

* 'Zoom' = 75, " (in Percentage)

'FitToPagesWide' = 1,

'FitToPagesTall' = 1,

'PaperSize' = 9. " 1 = Letter | 3 = Tabloid | 5 = Legal | 8 = A3 | 9 = A4 | 11 = A5

" Margins ( sizes are in points )

SET PROPERTY OF gs_pgsetup: 'LeftMargin' = '18', " '0.25"'.

'RightMargin' = '18', " '0.25"'.

'TopMargin' = '54', " '0.75"'.

'BottomMargin' = '54', " '0.75"'.

'CenterHorizontally' = 1.

* +--------------------------+

* + HEADER/FOOTER +

* +--------------------------+

* + &P = Page Number +

* + &N = Number of Pages +

* + &B = Bold +

* + &I = Italic +

* + &U = Single Underline +

* + &E = Double Underline +

* + &S = Strikethrough +

* + &X = Superscript +

* + &Y = Subscript +

* + &G = Picture +

* + &A = Sheet Name +

* + &D = Date +

* + &T = Time +

* + &F = File Name +

* + &"FontName" = Font Name +

* + &nn(&12) = Font Size +

* +--------------------------+

" Header/Footer

SET PROPERTY OF gs_pgsetup: 'CenterFooter' = '&"Cambria"' & '&B' & '&12' & 'Page &P of &N'.

* 'LeftFooter' = '&G'.

* SET PROPERTY OF gs_pgsetup 'RightFooter' = '&F'.

" Sheet

SET PROPERTY OF gs_pgsetup: 'PrintTitleRows' = '$1:$5'. " Rows to repeat at top

* 'PrintGridlines' = 1. " 1 = Checked

" Sheet Protect

* GET PROPERTY OF gs_excel 'ActiveSheet' = gs_sheet.

* GET PROPERTY OF gs_sheet 'Protection' = gs_prot.

* CALL METHOD OF gs_sheet 'PROTECT' EXPORTING #1 = 'pass@786' #2 = 0.

* Display save dialog window

CONCATENATE 'Balance Sheet - ' p_ryear '.xls' INTO ld_filename SEPARATED BY space.

CONDENSE ld_filename.

CALL FUNCTION 'GUI_FILE_SAVE_DIALOG'

EXPORTING

window_title = 'Balance Sheet'

default_extension = 'xls'

default_file_name = ld_filename

IMPORTING

fullpath = ld_fullpath.

" Save File

CALL METHOD OF gs_wbook 'SaveAs' EXPORTING #1 = ld_fullpath #2 = 1.

CALL METHOD OF gs_excel 'QUIT'.

CALL METHOD cl_gui_frontend_services=>execute

EXPORTING

document = ld_fullpath

* minimized = 'X'

operation = 'OPEN'.

ENDFORM. "F_CREATE_EXCEL

*&---------------------------------------------------------------------*

*& Form F_ADD_SHEET

*&---------------------------------------------------------------------*

FORM f_add_sheet USING p_tab p_sheetname p_sheet.

GET PROPERTY OF gs_excel 'ActiveSheet' = p_sheet.

CALL METHOD OF p_sheet 'Activate'.

CALL METHOD OF p_sheet 'Add' = gs_wbook.

SET PROPERTY OF p_sheet 'Name' = p_sheetname.

MOVE '<Company Name>' TO wtitle.

PERFORM f_merge_cell USING 1 wtitle p_sheetname 28 35.

CONCATENATE 'Balance Sheet as at 31st March, ' p_ryear INTO wtitle SEPARATED BY space.

PERFORM f_merge_cell USING 2 wtitle p_sheetname 14 18.

PERFORM: f_fill_header,

f_fill_detail.

ENDFORM. "F_ADD_SHEET

*&---------------------------------------------------------------------*

*& Form F_MERGE_CELL

*&---------------------------------------------------------------------*

FORM f_merge_cell USING p_row wtitle p_sheetname p_size p_height.

CALL METHOD OF gs_excel 'Cells' = gs_cell1

EXPORTING #1 = p_row #2 = 1.

CALL METHOD OF gs_excel 'Cells' = gs_cell2

EXPORTING #1 = p_row #2 = 8.

CALL METHOD OF gs_excel 'Range' = gs_range

EXPORTING #1 = gs_cell1 #2 = gs_cell2.

CALL METHOD OF gs_range 'Select'.

CALL METHOD OF gs_range 'Merge'.

SET PROPERTY OF gs_cell1 'Value' = wtitle.

" For Alignment

SET PROPERTY OF gs_cell1 'HorizontalAlignment' = 3. " 1 = Default | 2 = Left | 3 = Center | 4 = Right | 5 = Justify

SET PROPERTY OF gs_cell1 'VerticalAlignment' = 2. " 1 = Top | 2 = Center | 3 = Bottom | 4 = Justify

" For WrapText

SET PROPERTY OF gs_cell1 'WrapText' = 1.

" For RowHeight

CALL METHOD OF gs_excel 'Rows' = gs_rows

EXPORTING #1 = p_row.

SET PROPERTY OF gs_rows 'RowHeight' = p_height.

" For Font

GET PROPERTY OF gs_cell1 'Font' = gs_font.

SET PROPERTY OF gs_font: 'Name' = 'Calibri',

'Bold' = 1,

'Size' = p_size, "12,

'ColorIndex' = 21. " Foreground Colour ( Brown )

ENDFORM. "F_MERGE_CELL

*&---------------------------------------------------------------------*

*& Form F_FILL_HEADER

*&---------------------------------------------------------------------*

FORM f_fill_header.

CLEAR: gs_row, gs_col.

gs_row = 4. gs_col = 1.

CONCATENATE '31st March,' p_ryear INTO cyear.

CONCATENATE '31st March,' p_ryear1 INTO pyear.

PERFORM f_ins_head USING gs_row gs_col 'Particular'. gs_col = gs_col + 1.

DO 3 TIMES.

PERFORM f_ins_head USING gs_row gs_col ' '. gs_col = gs_col + 1.

ENDDO.

PERFORM f_ins_head USING gs_row gs_col cyear. gs_col = gs_col + 1.

PERFORM f_ins_head USING gs_row gs_col ' '. gs_col = gs_col + 1.

PERFORM f_ins_head USING gs_row gs_col pyear. gs_col = gs_col + 1.

PERFORM f_ins_head USING gs_row gs_col ' '.

CLEAR: gs_col.

gs_row = gs_row + 1. gs_col = 1.

DO 4 TIMES.

PERFORM f_ins_head USING gs_row gs_col ' '. gs_col = gs_col + 1.

ENDDO.

PERFORM f_ins_head USING gs_row gs_col 'Rs.'. gs_col = gs_col + 1.

PERFORM f_ins_head USING gs_row gs_col 'Rs.'.

ENDFORM. "F_FILL_HEADER

*&---------------------------------------------------------------------*

*& Form F_INS_HEAD

*&---------------------------------------------------------------------*

FORM f_ins_head USING p_row p_col p_value.

CALL METHOD OF gs_excel 'Cells' = gs_cells EXPORTING #1 = p_row #2 = p_col.

SET PROPERTY OF gs_cells 'Value' = p_value.

" For Alignment

IF p_col > 5.

SET PROPERTY OF gs_cells 'HorizontalAlignment' = 4. " 1 = Default | 2 = Left | 3 = Center | 4 = Right | 5 = Justify

ENDIF.

SET PROPERTY OF gs_cells 'VerticalAlignment' = 2. " 1 = Top | 2 = Center | 3 = Bottom | 4 = Justify

" For Bold Font

GET PROPERTY OF gs_cells 'Font' = gs_font.

SET PROPERTY OF gs_font: 'Name' = 'Calibri',

'Bold' = 1,

'Size' = 11,

'ColorIndex' = 27. " Foreground Colour ( Yellow )

GET PROPERTY OF gs_cells 'Interior' = gs_inth.

SET PROPERTY OF gs_inth 'ColorIndex' = 45. " Background Colour ( Orange )

ENDFORM. "F_INSERT_HEAD

*&---------------------------------------------------------------------*

*& Form F_FILL_DETAIL

*&---------------------------------------------------------------------*

FORM f_fill_detail.

CLEAR: gs_row, gs_col.

gs_row = 7. gs_col = 1.

PERFORM f_ins_dtl USING gs_row gs_col 1 'SOURCES OF FUNDS :'.

gs_row = gs_row + 1.

PERFORM f_ins_dtl USING gs_row gs_col 1 'SHAREHOLDERS FUND'.

gs_row = gs_row + 1. gs_col = 2.

PERFORM f_ins_dtl USING gs_row gs_col 0 'Share Capital'. gs_col = gs_col + 3.

PERFORM f_ins_dtl USING gs_row gs_col 0 gs_final-g_shrd_caps. gs_col = gs_col + 2.

PERFORM f_ins_dtl USING gs_row gs_col 0 gs_final-g_shrd_caps1.

gs_row = gs_row + 1. gs_col = 2.

PERFORM f_ins_dtl USING gs_row gs_col 0 'Reserves and Surplus'. gs_col = gs_col + 3.

PERFORM f_ins_dtl USING gs_row gs_col 0 gs_final-g_resv_surp. gs_col = gs_col + 1.

PERFORM f_ins_dtl USING gs_row gs_col 0 gs_final-g_shr_hold. gs_col = gs_col + 1.

PERFORM f_ins_dtl USING gs_row gs_col 0 gs_final-g_resv_surp1. gs_col = gs_col + 1.

PERFORM f_ins_dtl USING gs_row gs_col 0 gs_final-g_shr_hold1.

ENDFORM. "F_FILL_DETAIL

*&---------------------------------------------------------------------*

*& Form F_INS_DTL

*&---------------------------------------------------------------------*

FORM f_ins_dtl USING p_row p_col p_bold p_value.

CALL METHOD OF gs_excel 'Cells' = gs_cells EXPORTING #1 = p_row #2 = p_col.

SET PROPERTY OF gs_cells 'Value' = p_value.

IF p_col > 5.

SET PROPERTY OF gs_cells 'NumberFormat' = '#,##0.00;-#,##0.00;'.

ENDIF.

" For Bold Font

GET PROPERTY OF gs_cells 'Font' = gs_font.

SET PROPERTY OF gs_font: 'Name' = 'Calibri',

'Bold' = p_bold,

'Size' = 11,

'ColorIndex' = 41. " Foreground Colour ( Cyan )

ENDFORM. "F_INS_DTL

former_member207480 · ‎02-18-2015

Hi,

It is really helpful. Thanks.

How to convert the SAP data into multiple excel sheet in the same excel file