Upload XLSX file in WebDynpro ABAP and view its content

[ashish_shah]

Upload XLSX file in WebDynpro ABAP and view its content

You can see many posts in WebDynpro ABAP Form about reading an Excel file and showing its content in a WebDynpro ABAP Table, however many of them would be closed unanswered since it was not directly possible.

Now it is possible with this interesting and collaborative project ABAP2XLSX!!! Way to go guys for innovative thinking by starting this project!!!

Prerequisites:

1. Install SAPLINK 1.4 or higher version http://code.google.com/p/saplink/downloads/list
2. Import and activate needed plug-ins for DDIC , Function Group, Dev Class, Interfaces , WebDynpro ABAP http://code.google.com/p/saplink-plugins/downloads/list or https://cw.sdn.sap.com/cw/docs/DOC-145448
3. Import most important Plug-in ABAP2XLSX  https://cw.sdn.sap.com/cw/releases/86?container=1016 Installation guide https://cw.sdn.sap.com/cw/docs/DOC-137513
4. An XLSX excel file with some data.
5. Join ABAP2XLSX and SAPLINKGroup for more updates on these awsome discoveries https://cw.sdn.sap.com/cw/groups/saplink and https://cw.sdn.sap.com/cw/groups/abap2xlsx

Create a WD Component with File upload UI Element:

1. Create a WD Component in SE80 with a window and a view
2. In View controller, create a node” upload_content” and an attribute “file_content” to store Uploaded Excel file data in XSTRING format.
3. Create a File Upload UI Element and bind its datasource property to XSTRING attribute.
4. Create a Button and assign an action upload to it, when an excel file will be uploaded
5. And here comes the magical code to read the excel 2007 (.xlsx) file.

Method OnActionUpload.

METHOD onactionupload . DATA lo_nd_upload_content    TYPE REF TO if_wd_context_node. DATA lo_el_upload_content    TYPE REF TO if_wd_context_element. DATA ls_upload_content       TYPE wd_this->element_upload_content. DATA: lo_componentcontroller  TYPE REF TO ig_componentcontroller, lo_current_controller   TYPE REF TO if_wd_controller, lo_view_controller      TYPE REF TO if_wd_view_controller, lo_message_manager      TYPE REF TO if_wd_message_manager . DATA: excel                   TYPE REF TO zcl_excel, lo_excel_writer         TYPE REF TO zif_excel_writer,   reader                  TYPE REF TO zif_excel_reader. DATA: worksheet               TYPE REF TO zcl_excel_worksheet, highest_column          TYPE zexcel_cell_column, highest_row             TYPE int4, column                  TYPE zexcel_cell_column VALUE 1, col_str                 TYPE zexcel_cell_column_alpha, row                     TYPE int4               VALUE 1, value                   TYPE zexcel_cell_value. DATA: ex                      TYPE REF TO zcx_excel, msg                     TYPE string, lv_highest_row          TYPE string, lv_highest_column       TYPE string, lv_rowdata              TYPE string, lv_rownumber            TYPE string. lo_current_controller ?= wd_this->wd_get_api( ). lo_message_manager = lo_current_controller->get_message_manager( ). lo_view_controller = wd_this->wd_get_api( ). *   navigate from <CONTEXT> to <UPLOAD_CONTENT> via lead selection lo_nd_upload_content = wd_context->get_child_node( name = wd_this->wdctx_upload_content ). *   get element via lead selection lo_el_upload_content = lo_nd_upload_content->get_element( ). *   get all declared attributes   lo_el_upload_content->get_static_attributes( IMPORTING static_attributes = ls_upload_content ). TRY. CREATE OBJECT reader TYPE zcl_excel_reader_2007. excel = reader->load( ls_upload_content-file_content ). worksheet = excel->get_active_worksheet( ). highest_column = worksheet->get_highest_column( ). MOVE highest_column TO lv_highest_column. highest_row    = worksheet->get_highest_row( ). MOVE highest_row TO lv_highest_row. CONCATENATE 'Highest column: ' lv_highest_column  'Highest row: '  lv_highest_row INTO msg. CALL METHOD lo_message_manager->report_message EXPORTING message_text = msg. CLEAR lv_rowdata. WHILE row <= highest_row. WHILE column <= highest_column. col_str = zcl_excel_common=>convert_column2alpha( column ). worksheet->get_cell( EXPORTING ip_column = col_str ip_row    = row IMPORTING ep_value = value ). CONCATENATE lv_rowdata value INTO lv_rowdata SEPARATED BY space. column       = column + 1. ENDWHILE. CLEAR msg. MOVE row TO lv_rownumber. CONCATENATE 'Row# ' lv_rownumber ' Data = ' lv_rowdata INTO msg. CLEAR lv_rowdata. CALL METHOD lo_message_manager->report_message EXPORTING message_text = msg. column = 1. row = row + 1. ENDWHILE. CATCHzcx_excel INTO ex.    " Exceptions for ABAP2XLSX CLEAR msg. msg = ex->error. *  report message CALL METHOD lo_message_manager->report_message EXPORTING message_text = msg. ENDTRY. ENDMETHOD.

Sample Excel file Content

Sample WebDynpro ABAP Application accepting xlsx file:

Output :

Happy programming!!!!

Feel free to share your experience with Excel upload in WDA.

[Former Member]

Thanks a lot for sharing this as we were able to implement them.

Actually above code always reads the active work sheet 3 and we had to set them to read sheet 1 and read the active work sheet and it worked for us.

EXCEL->SET_ACTIVE_SHEET_INDEX( '1').

once again Thank you,

Yamuna

[ashish_shah]

Glad you were able to use this blog.

Thanks for sharing information about reading the specific worksheet, I will mention this in my blog as well.

[Former Member]

Dear Ashish

Thanks a lot for your valueable blots which you have share with us. I have also facing same problem when I am uploading a excel file of format .xlsx.So I have used your blog also. But I am getting some error like "zcl_excel is unknown".   an u kindly tell me what are these : zcl_excel,zif_excel_writer,zif_excel_reader,zcl_excel_worksheet, zexcel_cell_column,

zexcel_cell_column_alpha,zexcel_cell_value, zcx_excel_reader_2007

Regards

Rashmita Parida

[ashish_shah]

Hi Rashmita,

in the prerequisites section , i have mentioned that you need ABAP2XLSX plugins: For more information , go through following links:

Import most important Plug-in ABAP2XLSX  https://cw.sdn.sap.com/cw/releases/86?container=1016 Installation guide https://cw.sdn.sap.com/cw/docs/DOC-137513

Regards,

Ashish Shah

[Former Member]

Hi Ashish,

I have a question on the installation of Plug-in ABAP2XLSX, actually we installed it in our development box and it has by default went in to $TMP, how are we suppose to implement them in other box ie., testing and production, is that individually we have to install them else move to a package and move them via TR's if so is that only moving  zcl_excel class would be enough!!

Thanks,

Yamuna

[ashish_shah]

Hi Yamuna,

As mentioned in installation guide : https://cw.sdn.sap.com/cw/docs/DOC-137513

"

you can perform this activity either using  STMS or SAPLink using nugg file.

Using Change Request

Queue into your STMS abap2xlsx codata and cofile and import the CR to your system."

If you need to send these class files to your QA and production environment, you can use STMS transport requests.

Regards,

Ashish Shah

[Former Member]

Hi ashish,

can you please explain in details how to import the  Plug-in ABAP2XLSX .

because i have successfully uploaded the all the above plugings but when i hit the above

Plug-in ABAP2XLSX .

I COULD NOT FIND THE LINK TO DOWNLOAD THE  nugg file.

can you please guide to import the Plug-in ABAP2XLSX .

THANKS IN ADVANCE,

HARISH SINGH

[Former Member]

Hi,

Now i have done successfully with the prerequisites section.

and place the code but i ma getting erro like

"

• Call of a method or kernel Method that has not been implemented "

for the code excel = reader->load( ls_upload_content-file_content ).

what code i have to place in that load method .

thanks ,

[ashish_shah]

Hi Harish,

It seems you have some object from ABAP2XLSX plugins which are not active.

can you look at your inactive objects list and activate all the inactive objects.

If still the problem persists then i suggest you reimport the ABAP2XLSX plugin and reactivate all the objects.

Regards,

Ashish

[Former Member]

Hi Ashish,

Thanks for the response.

In the above discussed Prerequisites: in the second point you said to activate the plugins dev class and interfaces from the provided link i could found DDIC , Function Group,

webDynpro ABAP links but for INTERFACES AND DEV CLASS CAN YOU PLEASE GUIDE ME WHICH ONE I HAVE TO DOWNLOAD IN THE BELOW LINK because we have 11 items in the below link which one i have to download for the interfaces and dev class plugin i could found ddic,function group,webdynpro abap links please guide me

http://code.google.com/p/saplink-plugins/downloads/list

[ashish_shah]

Hi,

Refer this link for the list of plugins.

https://cw.sdn.sap.com/cw/docs/DOC-145448

And also join the group https://cw.sdn.sap.com/cw/groups/saplink for more updates on SAPLINK.

Regards,

Ashish

[Former Member]

Thanks Ashish,

I will check with this and i will post a message if i face problem..

Thanks for the reply it really helps me.

Thanks,

Harish Singh

[vijay_ramarao]

Hi Ashish,

System is able to read only XLSX files, but not XLS files. Our requirement is both XLS and XLSX. Do you have any idea how to make it work for XLS also. Please help. Thanks.

Regards,

Vijay.

[Former Member]

Hi asah,

You done Excellent job. I have small issue in the method load

excel = reader->load( ls_upload_content-file_content ).

return parameter is still initial. please suggest as soon as possible.

[Former Member]

Hello All,

For this you need to install additional z pluggins through SAPLINK. Instead you can use cl_fdt_xl_spreadsheet standard class for .xlsx file upload. You can refer to my content for the same through the given link.

http://scn.sap.com/docs/DOC-45719

Please award points if useful.

Thanks and Regards,

Devesh Singh

[Former Member]

Hello All,

For uploading .xlsx file in SAP Webdynpro refer following link:-

http://scn.sap.com/docs/DOC-45719

For downloading .xlsx file in Sap Webdynpro refer following link:-

http://scn.sap.com/docs/DOC-46001

Award point if useful.

Thanks and Regards,

Devesh Singh

[Former Member]

Hi Ashish,

Nice article.

I used it and was able to upload xlsx file. However, the date field in the excel sheet returned  me "41500".

How can I solve this issue?

Regards,

Prashant

[former_member218500]

Hi,

   I tried the above method to upload excel file in webdynpro. In the given above code, would like to know what is zcl_excel_worksheet and zcl_excel  defined as?? I am getting as unknown.

Your response is much appreciated.

[Former Member]

Hello Rita,

ZCL_EXCEL_WORKSHEET and ZCL_EXCEL would get installed/loaded into your SAP after you install the plugins from SAPLINK. As Ashish mentioned, follow below steps before writing the logic for upload.

1. Install SAPLINK 1.4 or higher version http://code.google.com/p/saplink/downloads/list
2. Import and activate needed plug-ins for DDIC , Function Group, Dev Class, Interfaces , WebDynpro ABAP http://code.google.com/p/saplink-plugins/downloads/list or https://cw.sdn.sap.com/cw/docs/DOC-145448
3. Import most important Plug-in ABAP2XLSX  https://cw.sdn.sap.com/cw/releases/86?container=1016 Installation guide https://cw.sdn.sap.com/cw/docs/DOC-137513
4. An XLSX excel file with some data.
5. Join ABAP2XLSX and SAPLINKGroup for more updates on these awsome discoveries https://cw.sdn.sap.com/cw/groups/saplink and https://cw.sdn.sap.com/cw/groups/abap2xlsx

Thanks and Regards,

Devesh Singh