Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

The sap abap creating of excel sheet using internal table and formatting using OLE.

kunnath_rahul
Explorer
0 Kudos
595
The sap program which I created is not working and downloading the excel sheet with formatted allignment like centering the data in cells. I dont understand what the issue.

I have used vbak and vbap tables and then joined them and added into internal table lt_sales_data.

I want to use the internal table data and convert into excel using OLE to format the allignment and need downloading it. Can anybody tell that what is wrong with code.

Code:

REPORT zole_display.<br>INCLUDE ole2incl.<br>TYPES: BEGIN OF ty_sales_data,<br> salesdocument TYPE vbak-vbeln,<br> item TYPE vbap-posnr,<br> material TYPE vbap-matnr,<br> quantity TYPE vbap-kwmeng,<br> END OF ty_sales_data.<br>DATA: lt_sales_data TYPE TABLE OF ty_sales_data,<br> lo_excel TYPE ole2_object,<br> lo_workbook TYPE ole2_object,<br> lo_worksheet TYPE ole2_object,<br> lo_cells TYPE ole2_object,<br> lo_range TYPE ole2_object,<br> lv_row TYPE i,<br> lv_col TYPE i,<br> lv_filepath TYPE string,<br> lv_filename TYPE string.<br>SELECT vbak~vbeln<br> vbap~posnr<br> vbap~matnr<br> vbap~kwmeng<br> INTO TABLE lt_sales_data<br> FROM vbak<br> INNER JOIN vbap ON vbak~vbeln = vbap~vbeln<br> WHERE vbak~vdatu BETWEEN '19000101' AND '20231231'.
CREATE OBJECT lo_excel 'Excel.Application'.<br>SET PROPERTY OF lo_excel 'Visible' = 1.<br>CALL METHOD OF lo_excel 'Workbooks' = lo_workbook<br> EXPORTING<br> #1 = 1.

CALL METHOD OF lo_workbook 'Worksheets' = lo_worksheet<br> EXPORTING<br> #1 = 1.

CALL METHOD OF lo_worksheet 'Cells' = lo_cells.<br>lv_col = 1.<br>CALL METHOD OF lo_cells 'Item' = lo_range EXPORTING #1 = 1 #2 = lv_col.<br>SET PROPERTY OF lo_range 'Value' = 'Sales Document'.<br>SET PROPERTY OF lo_range 'Font.Bold' = 1.<br>SET PROPERTY OF lo_range 'HorizontalAlignment' = 3.

ADD 1 TO lv_col.<br>CALL METHOD OF lo_cells 'Item' = lo_range EXPORTING #1 = 1 #2 = lv_col.<br>SET PROPERTY OF lo_range 'Value' = 'Item'.<br>SET PROPERTY OF lo_range 'Font.Bold' = 1.<br>SET PROPERTY OF lo_range 'HorizontalAlignment' = 3.

ADD 1 TO lv_col.<br>CALL METHOD OF lo_cells 'Item' = lo_range EXPORTING #1 = 1 #2 = lv_col.<br>SET PROPERTY OF lo_range 'Value' = 'Material'.<br>SET PROPERTY OF lo_range 'Font.Bold' = 1.<br>SET PROPERTY OF lo_range 'HorizontalAlignment' = 3.

ADD 1 TO lv_col.<br>CALL METHOD OF lo_cells 'Item' = lo_range EXPORTING #1 = 1 #2 = lv_col.<br>SET PROPERTY OF lo_range 'Value' = 'Quantity'.<br>SET PROPERTY OF lo_range 'Font.Bold' = 1.<br>SET PROPERTY OF lo_range 'HorizontalAlignment' = 3.

lv_row = 2.<br>LOOP AT lt_sales_data INTO DATA(ls_sales_data).<br> lv_col = 1.

 CALL METHOD OF lo_cells 'Item' = lo_range EXPORTING #1 = lv_row #2 = lv_col.<br> SET PROPERTY OF lo_range 'Value' = ls_sales_data-salesdocument.<br> SET PROPERTY OF lo_range 'HorizontalAlignment' = 3.

 ADD 1 TO lv_col.<br> CALL METHOD OF lo_cells 'Item' = lo_range EXPORTING #1 = lv_row #2 = lv_col.<br> SET PROPERTY OF lo_range 'Value' = ls_sales_data-item.<br> SET PROPERTY OF lo_range 'HorizontalAlignment' = 3.

 ADD 1 TO lv_col.<br> CALL METHOD OF lo_cells 'Item' = lo_range EXPORTING #1 = lv_row #2 = lv_col.<br> SET PROPERTY OF lo_range 'Value' = ls_sales_data-material.<br> SET PROPERTY OF lo_range 'HorizontalAlignment' = 3.

 ADD 1 TO lv_col.<br> CALL METHOD OF lo_cells 'Item' = lo_range EXPORTING #1 = lv_row #2 = lv_col.<br> SET PROPERTY OF lo_range 'Value' = ls_sales_data-quantity.<br> SET PROPERTY OF lo_range 'HorizontalAlignment' = 3.

 ADD 1 TO lv_row.<br>ENDLOOP.<br>CALL METHOD OF lo_worksheet 'Columns' = lo_range EXPORTING #1 = 1.<br>CALL METHOD OF lo_range 'AutoFit'.<br>lv_filename = 'C:\Users\LENOVO\Downloads\Book5.xls'.<br>CALL METHOD OF lo_workbook 'SaveAs' EXPORTING #1 = lv_filename.<br>CALL METHOD OF lo_excel 'Quit'.<br>FREE OBJECT lo_excel.
3 REPLIES 3

Sandra_Rossi
Active Contributor
0 Kudos
495

Please edit your question (Actions>Edit), select your code and press the button [CODE], which makes the code appear colored/indented, it'll be easier for people to look at it. Thanks!

Sandra_Rossi
Active Contributor
0 Kudos
495

Please check the format of your post after posting.

Ctrl+Shift+V (unformatted paste) may help you.

Sandra_Rossi
Active Contributor
0 Kudos
495

Still can't read your question.