Hi,
I had a requirement where in user wanted to upload excel in CRM .
I can easily develop an se38 report where we provide the functionality of uploading excel and subsequent activity.
But this is an approach we have actually implemented many times and since CRM is all about web UI, I wanted to accommodate the requirement from Web UI.
Here for fulfilling I did some research and came across following link :
http://wiki.scn.sap.com/wiki/display/CRM/CRM+Web+UI+-+Uploading+data+from+Excel+files
I must appreciate author for this link.
But here whole code is done using Java script and some more concepts.
I personally did not wanted to follow the approach and hence after exploring I came up with new solution.
Please follow below specified procedure and hope your requirement gets covered.
For accommodating requirement I developed Z component.
Step 1: Create Z component ZEXCEL_UPLOAD, with View 1 for upload bar.
For Upload bar we have standard BSP component available GS_FILE_UPLOAD which we can use same.(By component usage functionality) in your Z component.
Or you can create a new view and just copy the .htm , EH_ONUPLOAD code from View GS_FILE_UPLOAD/VFileUpload .
I actually copied the code in Z view so that I can put my text as per requirement without enhancing standard component. (You can follow any approach)
If you are copying the view don’t forget to accommodate the code for Upload button
So now your View 1 is ready.
Step 2: Create view 2 for displaying the data from excel in table view
Here a beautiful approach is followed . Dynamically we are actually generating the attributes in context node of your result list. Because we wanted to make a generic component.
Here suppose you have 5 columns in your excel automatically 5 columns with the same label of your excel would be available as result.
Hence you can use same component at multiple places
Since we want an list of attributes dynamically we won’t add attributes in context node.
But without any attribute in context note your system won’t allow you to create context node and also will throw exception when you will run the component as no config would be available.
Hence just add one attribute which is available by default like sr. no. later on by code we will replace same by our list of attributes.
Step 3: Reading excel data into an internal table.
I personally prefer using Class-method approach for my development.
Hence I developed a Z class ZCL_MK_GLOBAL and a static method with public visibility EXCEL_FILE_UPLOAD with importing
parameters filename and content.
In EH_ONUPLOAD method of view 1 (Copied code from GS_FILE_UPLOAD component )
We have two variable LV_FILE_NAME and LV_FILE_CONTENT_XSTRING.
At end of the code of EH_ONUPLOAD method, Call your method EXCEL_FILE_UPLOAD of class ZCL_MK_GLOBAL and
pass LV_FILE_NAME and LV_FILE_CONTENT_XSTRING in it.
Declare attribute in ZCL_MK_GLOBAL class.(As shown in below image)
Now we will code in EXCEL_FILE_UPLOAD method.
METHOD EXCEL_FILE_UPLOAD.
DATA: LO_DOC_SPREADSHEET TYPE REF TO CL_FDT_XL_SPREADSHEET,
LS_MESSAGE TYPE IF_FDT_TYPES=>S_MESSAGE,
LV_WS_NAME TYPE STRING,
LT_MESSAGE TYPE IF_FDT_TYPES=>T_MESSAGE,
LT_WS_NAME TYPE STANDARD TABLE OF STRING,
ER_DATA TYPE REF TO DATA.
TRY .
CREATE OBJECT LO_DOC_SPREADSHEET
EXPORTING
DOCUMENT_NAME = IV_FILENAME
XDOCUMENT = IV_CONTENT.
CATCH CX_FDT_EXCEL_CORE.
MESSAGE E004(FDT_EXCEL_API) INTO LS_MESSAGE-TEXT.
LS_MESSAGE-MSGTY = 'E'.
"'Unable to parse the excel content.'.
APPEND LS_MESSAGE TO LT_MESSAGE.
RAISE EXCEPTION TYPE CX_FDT_EXCEL
EXPORTING
MT_MESSAGE = LT_MESSAGE.
ENDTRY.
*gets the first worksheet and then getting the data in a internal table
LO_DOC_SPREADSHEET->IF_FDT_DOC_SPREADSHEET~GET_WORKSHEET_NAMES( IMPORTING WORKSHEET_NAMES = LT_WS_NAME ).
* Get the first worksheet
READ TABLE LT_WS_NAME INDEX 1 INTO LV_WS_NAME.
IF LV_WS_NAME IS INITIAL.
MESSAGE E005(FDT_EXCEL_API) INTO LS_MESSAGE-TEXT.
LS_MESSAGE-MSGTY = 'E'.
"'No worksheet exists in the excel content.'.
APPEND LS_MESSAGE TO LT_MESSAGE.
RAISE EXCEPTION TYPE CX_FDT_EXCEL
EXPORTING
MT_MESSAGE = LT_MESSAGE.
ENDIF.
ER_DATA = LO_DOC_SPREADSHEET->IF_FDT_DOC_SPREADSHEET~GET_ITAB_FROM_WORKSHEET( LV_WS_NAME ).
GV_CTXT_DATA = ER_DATA.
ENDMETHOD.
Step 4: We will create attributes (by reading the header of the excel) for the result list in view 2.
Here we know that in GET_TABLE_LINE_SAMPLE method, code for attributes of a context node (Table Columns) is done.
Hence we will code in this method.
If you have noticed in above steps we took the data in GV_CTXT_DATA.
(If above image is not clear please click on same you would able to see in full screen else please find same code below)
METHOD GET_TABLE_LINE_SAMPLE.
TYPES: BEGIN OF LINE.
** TYPES NO TYPE CHAR2. "Added by wizard
TYPES:
SRNO TYPE CHAR2,
END OF LINE.
FIELD-SYMBOLS: <LS_TABLE_LINE> TYPE STANDARD TABLE.
FIELD-SYMBOLS: <FS_IS_FINAL> TYPE ANY."TABLE.
DATA: LS_TABLE_HEADER TYPE REF TO DATA.
FIELD-SYMBOLS: <GS_TABLE_LINE> TYPE STANDARD TABLE.
FIELD-SYMBOLS: <LS_COMP> TYPE ANY.
DATA: IT_FCAT TYPE LVC_T_FCAT.
FIELD-SYMBOLS: <WA_FCAT> TYPE LVC_S_FCAT.
DATA: LV_TABLINE TYPE REF TO DATA.
ASSIGN ZCL_MK_GLOBAL=>GV_CTXT_DATA->* TO <LS_TABLE_LINE> .
CREATE DATA RV_SAMPLE TYPE LINE. " standard
CHECK <LS_TABLE_LINE> IS ASSIGNED.
READ TABLE <LS_TABLE_LINE> ASSIGNING <FS_IS_FINAL> INDEX 1.
CHECK <FS_IS_FINAL> IS ASSIGNED.
CREATE DATA LV_TABLINE LIKE LINE OF <LS_TABLE_LINE>.
DATA : IT_DETAIL TYPE ABAP_COMPDESCR_TAB,
WA_COMP TYPE ABAP_COMPDESCR,
LV_COUNT TYPE CHAR20.
DATA : LR_REF_DESCR TYPE REF TO CL_ABAP_STRUCTDESCR.
DATA :GT_DYNA_TABLE TYPE REF TO DATA.
DATA: LV_INDEX TYPE SY-INDEX.
DATA : LV_SY_SUBRC TYPE SY-SUBRC VALUE 0.
LR_REF_DESCR ?= CL_ABAP_TYPEDESCR=>DESCRIBE_BY_DATA_REF( LV_TABLINE ).
IT_DETAIL = LR_REF_DESCR->COMPONENTS .
IF IT_DETAIL IS NOT INITIAL.
DESCRIBE TABLE IT_DETAIL LINES LV_COUNT.
* LOOP AT IT_DETAIL INTO WA_COMP.
* WRITE:/ WA_COMP-NAME .
* ENDLOOP.
DO LV_COUNT TIMES.
* WHILE LV_SY_SUBRC = 0.
ASSIGN COMPONENT SY-INDEX OF STRUCTURE <FS_IS_FINAL> TO <LS_COMP>.
* IF SY-SUBRC = 0.
APPEND INITIAL LINE TO IT_FCAT ASSIGNING <WA_FCAT>.
LV_INDEX = SY-INDEX.
<WA_FCAT>-COL_POS = LV_INDEX.
<WA_FCAT>-FIELDNAME = <LS_COMP>.
<WA_FCAT>-COLTEXT = <LS_COMP>.
<WA_FCAT>-DATATYPE = 'C'.
<WA_FCAT>-INTTYPE = 'C'.
<WA_FCAT>-INTLEN = '30' .
* LV_SY_SUBRC = 0.
* ELSE.
* LV_SY_SUBRC = 1.
* ENDIF.
* ENDWHILE.
ENDDO.
CALL METHOD CL_ALV_TABLE_CREATE=>CREATE_DYNAMIC_TABLE
EXPORTING
IT_FIELDCATALOG = IT_FCAT
IMPORTING
EP_TABLE = GT_DYNA_TABLE.
ASSIGN GT_DYNA_TABLE->* TO <LS_TABLE_LINE>.
ENDIF.
CHECK <LS_TABLE_LINE> IS ASSIGNED.
CREATE DATA RV_SAMPLE LIKE LINE OF <LS_TABLE_LINE>.
ENDMETHOD.
And congratulation you are done with it Your data is uploaded from excel to grid…Now play with it in whatever way you want
Please take care of following points
· 1) If your excel header line contain special character than this solution wont work
· 2) If length of header is less than 3 character it will not work
· 3) If length of header increases 15 character it wont work.
But being a developer we can definitely handle this by our coding skills or we can ask user to take care on specified points.
Note: CL_FDT_XL_SPREADSHEET specified class is available in CRM 7.0 EHP1 onwards only.
Thanks for sharing 🙂 ..
definitely will help in the future.
Regards,
Harish Kumar
🙂 ur welcome Harish !
How u configure UI for a dynamic table?
You can not configure the same. As the columns are dynamically genrated if you’d know a table then you should not create dynamic table and then you can configure.
Well job done, will help others as well.
Thanx Dhruvin 🙂
Thanks for sharing …helpful doc 🙂 …
🙂
Thanks for a good document. learnt a new approach.
nice document this is very informative and useful to others as well… Thanks for sharing… 🙂
Hi Preeti,
How would it display the data on Web UI?
Here, you are just passing the structure to “rv_sample” which will help in creating the dynamic structure on web UI however, data will not come.
Please explain, if my understanding is incorrect. Thanks!
Regards,
JotSaroop Singh
Dear Jotsaroop , You are right RV_SAMPLE will only help to create dynamic table but data is coming from a Global attribute. In pictures she has described it with arrows you can do the same to get desired results.
Dear JotSaroop,
Sorry for the delay in reply… I was not so active on SDN. Hope Your query is resolved by Dhruvin’s reply 🙂
hmmm I did all according to what is listed but after I click on the button “upload” nothing happens…
Any ideas?
Hi Tom , I have tried and tested it… can u check in debug where exactly u r facing issue?
Then we can also check where the issue is.
Hello Dhruvin. The issue is that there is not data being displayed. I checked in debug and the global parameter gv_ctxt_data is correctly filled so the upload actually worked. The only thing which is not working is getting my data displayed on the screen.
Hello Tom,
I think u should check get table line sample method…
Hi,
I am new to SAP, I followed the above steps but not able to get the output..I debugged it and found that in GET_TABLE_LINE_SAMPLE ,
CALL METHOD CL_ALV_TABLE_CREATE=>CREATE_DYNAMIC_TABLE is not able to get executed and giving exception.
Thnks
What exception is it throwing?
just check the field catalog table before executing