Read / Write Excel with embedded dropdowns in Web Dynpro ABAP

[chintan_virani]

Hello,

Would be great if you guys can help me clear my doubts regarding following requirement within ABAP:-

Requirement - Users need to get a View which has an embedded excel with all types of cell formatting, macros, dropdowns which restrcits entry in that column only to certain values etc. They should be able to copy-paste complete row/column from their existing spreadsheets into this embedded excel application. Once the data is edited as per the rules, it needs to be read and validations needs to be performed for each cell against MDM business rules via the MDM ABAP APIs

My Thoughts - I think OfficeControl should do the job for me but then is it possible to create excel on fly when the application is displayed to user with drop-downs (this is one of critical factors), color formatting etc and then read it cell by cell and make all the business checks in onSave event of Office control?

I do not plan to use the Web Dynpro table because it does not support full row/column copy-paste option from existing worksheet. Does Web Dynpro ALV support direct copy-paste functionality? Or should I use WD Java and use the 3rd party APIs like jExcel / Apache POI to read/write excel data.

Let me know your thoughts / recommendations.

[thomas_jung · Accepted solution]

>Does Web Dynpro ALV support direct copy-paste functionality?

No. The underlying implementation of the ALV is still the Table UI element. So this limitation exists. Actually this is more of a problem with the fact that the table is HTML and there are currently limits on drag and drop from outside the browser security sandbox. Hopefully HTML5 will help this someday.

I would think that the OfficeControl would be the way to go. If you use a recent version of the Excel, you can use the XML format for Excel. It is still complex to parse from ABAP, but not impossible. Using the XML format of Excel you can create just about any feature you coudl manually in Excel. If creating from scratch I generally design what I want in Excel and then do a save as XML and look at the XML file in a text editor.

[ChrisPaine · Accepted solution]

@Thomas - How can you use the Office control to read back the contents that have been entered into it?

I've often thought it would be great if you could do this - but can't see how - the on save event populates a context element - I'm guessing would be the one that you bound the data source to - but the document type is xls is it not?

The [Help for the Office Control UI Element|http://help.sap.com/saphelp_nw70ehp1/helpdata/en/8e/128b41b4b3b55fe10000000a1550b0/frameset.htm] seems to say you only have the choice between:

>ms_excel - Microsoft Word (sic) document with the file extension xls

>ms_word - Microsoft Word document with the file extension doc

(although interestingly the interface IF_WDL_OFFICE_INTEGRATION also has constants for OFFICEDOCTYPE_STAR_CALC and OFFICEDOCTYPE_STAR_WRITER )

If you pass a content of type XML does it save as XML - or even XLSX saved as XLSX, is there any way you can control this behaviour/ check what file type is returned easily?

If so - the new abap2xlsx ( [Wiki page for abap2xlsx|http://wiki.sdn.sap.com/wiki/display/ABAP/abap2xlsx] ) would be very useful for creating the file and then when the reader comes out, even more useful for reading the response.

The idea of applications which use embedded MS Excel to allow users to do data entry is one that would get many of the clients I work for very, very excited. (yes, they need to get out more).

Thanks for your clarifications,

Chris

[thomas_jung · Accepted solution]

>I'm guessing would be the one that you bound the data source to - but the document type is xls is it not?

It sends back an XSTRING with the document content.

>but the document type is xls is it not?

It should be the same document type that you send into the OfficeControl. If you send in an XLSX you should get back the same.

Yes I was thinking abap2xlsx could possibly be helpful.

[chintan_virani · Accepted solution]

It sends back an XSTRING with the document content.

Is it possible to parse this and get each cell value modified/entered by user ?

Also correct me if I am wrong, abap2xlsx is for displaying excel and getting that data back will be done via xlsx2abap which is still under development.

[thomas_jung · Accepted solution]

>Is it possible to parse this and get each cell value modified/entered by user ?

Yes, if you can in fact get the XSLX or XML format back out, then it should be possible to parse. You might very well have to write you own parsing logic (XSLT, iXML class or just find and replace).

[chintan_virani · Accepted solution]

I tried searching here, do you have an example I can refer?

[ChrisPaine · Accepted solution]

>Also correct me if I am wrong, abap2xlsx is for displaying excel and getting that data back will be done via xlsx2abap which is still under development.

abap2xlsx is for creating MS Excel XLSX format files (not just displaying them) - and yes the possibility to read data from the XLSX format is under development - Gregor Wolf is currently working on it - so I'd believe that an initial implementation would be available soon. But perhaps if you're going to develop the logic yourself anyway you might want to join in the community development effort?

There is a wave (yes I know that it is now "dead" technology - perhaps it will churn to StreamWorks in the future!) that discusses the current development effort: - sorry can't give you link as the version I have seems to be formatted through my Google domain - so won't work for anyone else.. But I think if you follow the blog/wiki that you'll be able to find it.

Cheers,

Chris

[_IvanFemia_ · Accepted solution]

Hi Chintan,

today we released the new version of abap2xlsx containing xlsx2abap [xlsx2abap - Read and edit your Excel files from ABAP|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/20791] [original link is broken] [original link is broken] [original link is broken];, check it out.

Regards,

Ivan