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: 

Download ALV to EXcel- Urgent pls help me

Former Member
0 Kudos

Please help me in this...

I have an ALV report around 120 column. The report is displayed perfectly on screen. But when I use the Export option to download as an excel file, some data is missing in SAP No field

For example

My report output is like this

sap No Name Jobdes. Dept

00021 AAA clerk1 FI

00022 BBB clerk1 FI

00023 CCC clerk1 FI

00024 DDD clerk1 FI

00025 EEE clerk1 FI

00026 FFF clerk1 FI

Excel output is

sap No Name Jobdes. Dept

00021 AAA clerk1 FI

Blank BBB clerk1 FI

Blank CCC clerk1 FI

Blank DDD clerk1 FI

00025 EEE clerk1 FI

Blank FFF clerk1 FI

Pls help me

1 ACCEPTED SOLUTION

Former Member
0 Kudos

Hi Kumar,

Pls try the below Function Modules to download the data to excel.

ALSM_EXCEL_TO_INTERNAL_TABLE

KCD_EXCEL_OLE_TO_INT_CONVERT – Uploads data directly from Excel sheet

RH_START_EXCEL_WITH_DATA – Starts Excel with contents of an internal table

or the below links also might help you.

Check the following link:

http://www.sap-img.com/abap/upload-direct-excel.htm

or even the below code might be helpful.

Multiple excel sheets generation in a workbook

CREATE OBJECT EXCEL 'EXCEL.SHEET'.

GET PROPERTY OF EXCEL 'Application' = APPLICATION.

SET PROPERTY OF APPLICATION 'Visible' = 1.

CALL METHOD OF APPLICATION 'Workbooks' = BOOKS.

CALL METHOD OF BOOKS 'Add' = BOOK.

CALL METHOD OF BOOK 'WORKSHEETS' = SHEET.

CALL METHOD OF SHEET 'ADD'.

Fill all the sheets with relavant data

PERFORM SHEET1 TABLES ITAB1.

PERFORM SHEET2 TABLES ITAB2.

PERFORM SHEET3 TABLES ITAB3.

PERFORM SHEET4 TABLES ITAB4.

Quit the excel after use

CALL METHOD OF EXCEL 'QUIT'.

FREE OBJECT: COLUMN,SHEET,BOOK,BOOKS,APPLICATION,EXCEL. "NO FLUSH.

CLEAR V_SHEET.

FORM FILL_CELL USING ROW COL VAL.

CALL METHOD OF SHEET 'cells' = CELL NO FLUSH

EXPORTING #1 = ROW #2 = COL.

SET PROPERTY OF CELL 'value' = VAL.

FREE OBJECT CELL NO FLUSH.

ENDFORM. " FILL_CELL

FORM SHEET1 TABLES ITAB1 STRUCTURE ITAB1.

V_SHEET = Sheet Name.

V_NO = V_NO + 1.

CALL METHOD OF BOOK 'worksheets' = SHEET NO FLUSH EXPORTING #1 = V_NO.

SET PROPERTY OF SHEET 'Name' = V_SHEET NO FLUSH.

PERFORM FILL_SHEET1 TABLES ITAB1 USING V_NO V_SHEET.

CALL METHOD OF SHEET 'Columns' = COLUMN.

FREE OBJECT SHEET.

CALL METHOD OF COLUMN 'Autofit'.

FREE OBJECT COLUMN.

ENDFORM.

Repeat above procedure for all sheets you want to add

FORM FILL_SHEET1

TABLES ITAB1 STRUCTURE ITAB1

USING V_NO V_SHEET.

ROW = 1.

PERFORM FILL_CELL USING ROW 1 'Column1 Name'.

PERFORM FILL_CELL USING ROW 2 'Column2 Name'.

PERFORM FILL_CELL USING ROW 3 'Column3 Name'.

ROW = ROW + 1.

LOOP AT ITAB1.

PERFORM FILL_CELL USING ROW 1 ITAB1-Column1.

PERFORM FILL_CELL USING ROW 2 ITAB1-Column2.

PERFORM FILL_CELL USING ROW 3 ITAB1-Column3.

ROW = ROW + 1.

ENDLOOP.

ENDFORM.

Repeat above procedure for all sheets you want to add

Try this also

TYPE-POOLS: truxs.

DATA: it_raw TYPE truxs_t_text_data.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

field_name = 'P_FILE'

IMPORTING

file_name = p_file.

Upload Excel file

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR =

i_line_header = 'X'

i_tab_raw_data = it_raw

i_filename = p_file

TABLES

i_tab_converted_data = i_XCEL[]

EXCEPTIONS

conversion_failed = 1

OTHERS = 2.

IF sy-subrc 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

Pls reward if useful.

THanks,

Sirisha

8 REPLIES 8

Former Member
0 Kudos

Hi Kumar,

Pls try the below Function Modules to download the data to excel.

ALSM_EXCEL_TO_INTERNAL_TABLE

KCD_EXCEL_OLE_TO_INT_CONVERT – Uploads data directly from Excel sheet

RH_START_EXCEL_WITH_DATA – Starts Excel with contents of an internal table

or the below links also might help you.

Check the following link:

http://www.sap-img.com/abap/upload-direct-excel.htm

or even the below code might be helpful.

Multiple excel sheets generation in a workbook

CREATE OBJECT EXCEL 'EXCEL.SHEET'.

GET PROPERTY OF EXCEL 'Application' = APPLICATION.

SET PROPERTY OF APPLICATION 'Visible' = 1.

CALL METHOD OF APPLICATION 'Workbooks' = BOOKS.

CALL METHOD OF BOOKS 'Add' = BOOK.

CALL METHOD OF BOOK 'WORKSHEETS' = SHEET.

CALL METHOD OF SHEET 'ADD'.

Fill all the sheets with relavant data

PERFORM SHEET1 TABLES ITAB1.

PERFORM SHEET2 TABLES ITAB2.

PERFORM SHEET3 TABLES ITAB3.

PERFORM SHEET4 TABLES ITAB4.

Quit the excel after use

CALL METHOD OF EXCEL 'QUIT'.

FREE OBJECT: COLUMN,SHEET,BOOK,BOOKS,APPLICATION,EXCEL. "NO FLUSH.

CLEAR V_SHEET.

FORM FILL_CELL USING ROW COL VAL.

CALL METHOD OF SHEET 'cells' = CELL NO FLUSH

EXPORTING #1 = ROW #2 = COL.

SET PROPERTY OF CELL 'value' = VAL.

FREE OBJECT CELL NO FLUSH.

ENDFORM. " FILL_CELL

FORM SHEET1 TABLES ITAB1 STRUCTURE ITAB1.

V_SHEET = Sheet Name.

V_NO = V_NO + 1.

CALL METHOD OF BOOK 'worksheets' = SHEET NO FLUSH EXPORTING #1 = V_NO.

SET PROPERTY OF SHEET 'Name' = V_SHEET NO FLUSH.

PERFORM FILL_SHEET1 TABLES ITAB1 USING V_NO V_SHEET.

CALL METHOD OF SHEET 'Columns' = COLUMN.

FREE OBJECT SHEET.

CALL METHOD OF COLUMN 'Autofit'.

FREE OBJECT COLUMN.

ENDFORM.

Repeat above procedure for all sheets you want to add

FORM FILL_SHEET1

TABLES ITAB1 STRUCTURE ITAB1

USING V_NO V_SHEET.

ROW = 1.

PERFORM FILL_CELL USING ROW 1 'Column1 Name'.

PERFORM FILL_CELL USING ROW 2 'Column2 Name'.

PERFORM FILL_CELL USING ROW 3 'Column3 Name'.

ROW = ROW + 1.

LOOP AT ITAB1.

PERFORM FILL_CELL USING ROW 1 ITAB1-Column1.

PERFORM FILL_CELL USING ROW 2 ITAB1-Column2.

PERFORM FILL_CELL USING ROW 3 ITAB1-Column3.

ROW = ROW + 1.

ENDLOOP.

ENDFORM.

Repeat above procedure for all sheets you want to add

Try this also

TYPE-POOLS: truxs.

DATA: it_raw TYPE truxs_t_text_data.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

field_name = 'P_FILE'

IMPORTING

file_name = p_file.

Upload Excel file

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR =

i_line_header = 'X'

i_tab_raw_data = it_raw

i_filename = p_file

TABLES

i_tab_converted_data = i_XCEL[]

EXCEPTIONS

conversion_failed = 1

OTHERS = 2.

IF sy-subrc 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

Pls reward if useful.

THanks,

Sirisha

Former Member
0 Kudos

Hi Kumar,

Pls try the below Function Modules to download the data to excel.

ALSM_EXCEL_TO_INTERNAL_TABLE

KCD_EXCEL_OLE_TO_INT_CONVERT – Uploads data directly from Excel sheet

RH_START_EXCEL_WITH_DATA – Starts Excel with contents of an internal table

or the below links also might help you.

Check the following link:

http://www.sap-img.com/abap/upload-direct-excel.htm

or even the below code might be helpful.

Multiple excel sheets generation in a workbook

CREATE OBJECT EXCEL 'EXCEL.SHEET'.

GET PROPERTY OF EXCEL 'Application' = APPLICATION.

SET PROPERTY OF APPLICATION 'Visible' = 1.

CALL METHOD OF APPLICATION 'Workbooks' = BOOKS.

CALL METHOD OF BOOKS 'Add' = BOOK.

CALL METHOD OF BOOK 'WORKSHEETS' = SHEET.

CALL METHOD OF SHEET 'ADD'.

Fill all the sheets with relavant data

PERFORM SHEET1 TABLES ITAB1.

PERFORM SHEET2 TABLES ITAB2.

PERFORM SHEET3 TABLES ITAB3.

PERFORM SHEET4 TABLES ITAB4.

Quit the excel after use

CALL METHOD OF EXCEL 'QUIT'.

FREE OBJECT: COLUMN,SHEET,BOOK,BOOKS,APPLICATION,EXCEL. "NO FLUSH.

CLEAR V_SHEET.

FORM FILL_CELL USING ROW COL VAL.

CALL METHOD OF SHEET 'cells' = CELL NO FLUSH

EXPORTING #1 = ROW #2 = COL.

SET PROPERTY OF CELL 'value' = VAL.

FREE OBJECT CELL NO FLUSH.

ENDFORM. " FILL_CELL

FORM SHEET1 TABLES ITAB1 STRUCTURE ITAB1.

V_SHEET = Sheet Name.

V_NO = V_NO + 1.

CALL METHOD OF BOOK 'worksheets' = SHEET NO FLUSH EXPORTING #1 = V_NO.

SET PROPERTY OF SHEET 'Name' = V_SHEET NO FLUSH.

PERFORM FILL_SHEET1 TABLES ITAB1 USING V_NO V_SHEET.

CALL METHOD OF SHEET 'Columns' = COLUMN.

FREE OBJECT SHEET.

CALL METHOD OF COLUMN 'Autofit'.

FREE OBJECT COLUMN.

ENDFORM.

Repeat above procedure for all sheets you want to add

FORM FILL_SHEET1

TABLES ITAB1 STRUCTURE ITAB1

USING V_NO V_SHEET.

ROW = 1.

PERFORM FILL_CELL USING ROW 1 'Column1 Name'.

PERFORM FILL_CELL USING ROW 2 'Column2 Name'.

PERFORM FILL_CELL USING ROW 3 'Column3 Name'.

ROW = ROW + 1.

LOOP AT ITAB1.

PERFORM FILL_CELL USING ROW 1 ITAB1-Column1.

PERFORM FILL_CELL USING ROW 2 ITAB1-Column2.

PERFORM FILL_CELL USING ROW 3 ITAB1-Column3.

ROW = ROW + 1.

ENDLOOP.

ENDFORM.

Repeat above procedure for all sheets you want to add

Try this also

TYPE-POOLS: truxs.

DATA: it_raw TYPE truxs_t_text_data.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

field_name = 'P_FILE'

IMPORTING

file_name = p_file.

Upload Excel file

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'

EXPORTING

I_FIELD_SEPERATOR =

i_line_header = 'X'

i_tab_raw_data = it_raw

i_filename = p_file

TABLES

i_tab_converted_data = i_XCEL[]

EXCEPTIONS

conversion_failed = 1

OTHERS = 2.

IF sy-subrc 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

Pls reward if useful.

THanks,

Sirisha

0 Kudos

Any one pls help me. Its very urgent

0 Kudos

Hi Kumar,

Have you set sort criteria on you ALV.

If so remove it and try downloading the report again.

Thanks,

Arun

0 Kudos

Hi Arun

I fixed the problem. I given full mark to u

Former Member
0 Kudos

hi...

1) Once you have alv report displayed in the screen.

2) Click button 'View' ( next to print button) on application toolbar

3) Select Excel in Place

4) This will download the same format as of Report

I hope your ALV have all the Standard functions in Toolbar. If not copy the status from and get the function as mentioned above.

go through...

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/11099070-0b01-0010-fd9d-af9dc930...

or

GO THROUGH THIS LINK

http://www.cdg-inc.com/news/WhitePaper_ALV_Reporting_Power.htm

If you only want to transfer the data to Excel like when you transfer the data from

ALV to Excel simply use the Function Modules:

XXL_SIMPLE_API

If you want more modifications when you transfer it to Excel use:

XXL_FULL_API

regards.

Sudarsan

0 Kudos

Hi Sudarsan

i am using the same method. if i download using local file, no problem

Former Member
0 Kudos