related page 1
related page 2
related page 3
NOTE: Before beginning, the XLSX Workbench functionality must be available in the your system.
Suppose we need to generate Excel-file in the background mode.
For ease of example, lets create form, that contains only the classical phrase “Hello world !” nested in the rectangte area. The resultant Excel-file we will send via SAP-mail (in this case – to themselves).
1 PREPARE A PRINTING PROGRAM.
As you can see, most of the code takes the mailing (does not apply to the form creation) :
REPORT z_hello_world .
* declare and fill context
DATA gs_context TYPE lvc_s_tabl .
DATA gv_document_rawdata TYPE mime_data .
gs_context-value = ‘Hello world!’ .
* call the form
CALL FUNCTION ‘ZXLWB_CALLFORM’
EXPORTING
iv_formname = ‘HELLO_WORLD’
iv_context_ref = gs_context
iv_viewer_suppress = ‘X’
IMPORTING
ev_document_rawdata = gv_document_rawdata
EXCEPTIONS
OTHERS = 2 .
IF sy-subrc NE 0 .
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 .
ENDIF .
* mailing
PERFORM send_mail USING gv_document_rawdata .
*&———————————————————————*
*& Form send_mail
*&———————————————————————*
FORM send_mail USING pv_document_rawdata TYPE mime_data .
DATA:
lv_attachment_size TYPE sood-objlen ,
lv_subject TYPE so_obj_des ,
lv_document_size TYPE i ,
lt_document_table TYPE solix_tab .
DATA:
lr_send_request TYPE REF TO cl_bcs ,
lr_mail_message TYPE REF TO cl_document_bcs ,
lr_recipient TYPE REF TO if_recipient_bcs ,
lr_error TYPE REF TO i_oi_error ,
ls_retcode TYPE soi_ret_string ,
lv_attachment_type TYPE soodk-objtp VALUE ‘XLS’ .
CALL FUNCTION ‘SCMS_XSTRING_TO_BINARY’
EXPORTING
buffer = pv_document_rawdata
IMPORTING
output_length = lv_document_size
TABLES
binary_tab = lt_document_table.
lr_send_request = cl_bcs=>create_persistent( ) .
lv_subject = ‘test mail’ .
lr_mail_message = cl_document_bcs=>create_document(
i_type = ‘RAW’
i_subject = lv_subject ) .
lv_attachment_size = lv_document_size .
TRY .
lr_mail_message->add_attachment(
i_attachment_type = lv_attachment_type
i_attachment_subject = space
i_attachment_size = lv_attachment_size
i_att_content_hex = lt_document_table ) .
CATCH cx_document_bcs .
ENDTRY .
lr_send_request->set_document( lr_mail_message ) .
lr_recipient = cl_sapuser_bcs=>create( sy-uname ).
lr_send_request->set_send_immediately( abap_on ) .
lr_send_request->add_recipient(
i_recipient = lr_recipient
i_express = abap_on ) .
lr_send_request->send( i_with_error_screen = abap_on ) .
COMMIT WORK .
ENDFORM . “send_mail
2 PREPARE A FORM.
2.1 Launch XLSX Workbench, and in the popup window specify a form name HELLO_WORLD , and then press the button «Process»:
Empty form will be displayed:
2.2 Push button
to save the form.
2.3 Assign context LVC_S_TABL to the form:
Herewith, you will be prompted to create a form’s structure automatically (based on context):
Let’s press the button: 
.
As result, «Pattern» (
) and «Value» (
) will be added under the «Sheet» in the form structure tree :
Added components will already have a binding with context. For this components, only template binding is required.
We’ll do it later, but first we perform markup of template.
2.4 Make markup in the Excel template:
2.5 Template binding:
- Pose cursor on the node in the form’s structure tree;
- Select a cell range [A1 : C3] in the Excel-template;
- Press a button
located in the item «Area in the template» of the Properties tab:
- Pose cursor on the node in the form’s structure tree;
- Select a cell range [B2] in the Excel-template;
- Press a button located in the item «Area in the template» of the Properties tab:
Scheme of bindings:
2.6 Activate form by pressing button
.
3 EXECUTION.
Launch SE38 and run your report Z_HELLO_WORLD in background mode :
What was wrong with ABAP2XLSX that a new framework needed to be created to achieve the exact same thing?
Two different tools for the same issue? Is that a bad thing?
Isn’t it called SAP? 😉
No, SAP would have fifty three different tools for the same issue!
Here’s some differences I see (sorry If I’m saying the obvius)
– Not all the customers accept the import of community software
– This framework is supported by SAP, the other by the community, I couldn’t say which support wil be better, one enthusiastic community vs a “concerned” software vendor.
Second point first:-
– are you saying the XLSX Workbench is an SAP delivered, SAP supported solution, part of the standard SAP framework? Are you sure? Can you point me to an official SAP site, outside of the SCN where it is mentioned? Or is it actually just another community project? It looks to me like the code lives on a Google project site.
– I am aware that a lot of companies refuse to allow open source software such as SAPLINK / ABAP2XLSX / MOCKA and so forth. As I said before, I would like a list of such companies so I can buy shares in their competitors. The difference is, if an SAP supported framework has bugs or lacks functionality I want, I have to put in a support request which will be ignored, and in an open source framework I can either sk for help with a far better chance of a response or even better I can enhance / fix it myself the same day and then publish my change so everyone else can benefit from it.
The open source model is more and more common these days and even SAP is waking up to it hence “Open UI5”.
Mind you, the actual point I am making is that a lot of people are unaware of ABAP2XLSX and thus waste their time inventing something that does more or less the exact same thing. You tend to see at least one post a month on SCN from someone who has discovered how to download an excel file in the background from ABAP.
If the XLSX workbench has a different function than ABAP2XLSX then fine, that answers the actual question I asked in the first place, but the title of this blog was about downloading an excel sheet in the background, which was not a new thing.
Cheersy Cheers
Paul
You just killed me here… You are absolutely right, is another community project, my bad. 🙁
I agree with the rest of your comments, so nothing to say, but
That’s quite complex, The customers should pay twice? You will develop the changes/fixes on your spare time or in a project?
Hi Luis !
First I want clarify that the XLSX Workbench is my personal project (it is not supported by SAP).
Also, Paul Hardy was right XLSX Workbench is the one of the many solutions to generate Excel forms. However, my solution differs from the others in that it has a visual interface (this is the main difference). I think, visual approach has profits, as for development and for support.
Regards, Igor.
The screenshots looked so good that I got confused…so congrats! 😀
Hi Igor,
First, I would like to say that I really appreciate your effort and willing to share your project.
Second, Have you thought on using ABAP2XLSX for the internal generation of XLSX instead of your own code?
Shai, now i have no time for so deep reworking of my solution.
I am afraid that both solutions implement quite different basic principles of generating resultant file. For example, I do not create the resultant file “from scratch”; instead of this, I take Template (with its cell styles, settings, etc.) and enrich it according to form structure and context data.
Hi,
The template file is also XLSX, isn’t it?
I just wondered if there was/is any special reason not to use ABAP2XLSX for it.
I agree that refactoring it now will require some time, though.
Well, as far as I can tell, it it isn’t the same.
XLSX Workbench offers a design-time environment (similar to Smartforms) for generation of XLSX files.
However, I agree that it should have been implemented as another layer on top of ABAP2XLSX and make use of it internally.
I reached Igor Borodin some months ago to try to work on this way, but unfortunately working is limiting our time to talk and collaborate.
I like the UI of the XLSX Workbench and I agree that the engine should be the abap2xlsx that is now a mature product with an incredible list of features.
If anyone whats to join into this discussion is really welcomed and Community can improve and merge two ideas into a new powerful open source tool.
Ivan
We should ask Igor Borodin‘s permission first, of course.
Hi,
I would like to raise a question.
Why can’t we use OLE to create Excel in background Job ?
Is that because we do not have Microsoft office installed on the application server ?
Can we use OLE if we did ?
The reason I am asking this is that I know for a fact that my colleagues are generating and serving Excel files using OLE as part of a web server agents (the server is a domino server from IBM) . they have on the server a copy of office and that’s it….
regards.
AFAIK you can’t use OLE2, it works only in foreground from SAPGui.
Maybe your colleagues are using some tricks with other middle-ware (JSP, PHP or ..) can do the job, but it is a huge complication, for something that can be achieved easily from abap using abap2xlsx.
Hi,
Object Linking and Embedding (OLE) is part of the windows operating system.
(See http://en.wikipedia.org/wiki/Object_Linking_and_Embedding) .
No tricks are involve.
My colleagues are using lotus script and this language have a built in support to use ole .
it looks like this:
Dim msExcel As Variant
Set msExcel = GetObject(“Excel.Application”)
Call msExcel.Workbooks.Add
And this is VBScript:
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = False
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = “Test value”
objExcel.ActiveWorkbook.SaveAs (“C:\excelvbscript.xls”)
objExcel.Quit
This look very similar to the ABAP code don’t you think ?
You might be right if SAP is limiting ABAP when using OLE .
I wonder if anyone tries to use OLE in back ground job ?
regards.
Hi Aitan,
AFAIK it often raises problems if you depend on external things like OLE.
Sometimes the Windows machine or the installed office gets an update or something is reconfigured for other purposes and your XLS-Solution does not work anymore.
Ivans abap2xlsx is a pure ABAP solution and it does not depend on other system components and I can tell you from my experience, that it is very stable.
I did not yet try it, but I think Igors XLSX Workbench is also solution which only depends on ABAP and therefore it also has a big advantage compared to any OLE solution.
Best regards,
Dominik
Hi,
Have you seen what SAP function are using to create Excel ?
The risk of using OLE for me is less then using abap2xlsx .
I prefer to create native excel files using Java libraries .
Like this Creating Excel the Java way
Regards.
POI (which I use in a product I’m developing – SAP related, but PC based) doesn’t use OLE. So it’s as safe as abap2xlsx.
So you prefer java libraries instead of ABAP library. What is the advantage?
ABAP is creating an XML file, call external java for processing the XML and then creates a XLSX. Does it seem to complex for just a Excel?
abap2xlsx creates file directly from ABAP (no external calls needed) accordingly to OOXML standard (ECMA International as ECMA-376) that is used in excel, freeoffice, openoffice and many others So what is the risk?
Well, it’s a question of who you trust. You (Ivan) and Igor, or Microsoft. 😏
Looking at Creating Excel the Java way
Step 1. Sample XML created from SAP
Who trust the guy that generates the XML?
Step 2. Sample Excel generated by the java program .
Who trust the guy that wrote the java program?
Microsoft has some API (OLE) to execute actions in excel. That could be an advantage, but what is the risk of having a java code running on the server bash?
The advantage of an open source: having a community of expert developers that close a gap that two companies don’t want to cover… And the code can be verified by everyone.
my 2 cents
When I was saying trick I mean another tool, language, middle ware.
In your case a Lotus Script. But you can also create with other scripting languages.
abap2xlsx is just ABAP base, no other scriptings are required. So having this in mind you can understand that the complexity of the entire flow is lower.
Even more having the file generated directly from ABAP, the access to SAP data to be exported is faster and also more secure.
OLE definitly needs SAPgui. See http://help.sap.com/abapdocu_740/en/abenole2.htm:
“The statements of the automation command set are transferred from the ABAP runtime environment to the SAP GUI on the current presentation server which is responsible for the actual communication with the automation server.”
Hi,
Thank you very very much.
The question now is why this limitation ?
Regards.
Isn’t it obvious? For the same reason that you can’t download to your PC file system in background.
Hi,
Sorry but I disagree .
If you make a folder on your local PC accessible from your SAP .
Using UNC naming you can write directly to your desktop .
Of course you have to have the computer on…..
No dialog will be shown to the user .
Regards.
UNC is just a way of identifying files across heterogenous systems. For it to work, you have to correctly configure both windows and linux. This will require admin rights and some linux jiggery-pokery. So it’s a little more complex than “just give the UNC name”. It doesn’t just work out of the box and isn’t provided by SAP. Similarly for OLE you’ll need transport layer software – why should SAP provide this? What’s in it for them? If there was a real customer need being unmet that they could make money out of, I’m sure they’d do it.
Why do you want to use OLE anyway? It’s a pain to work with – both in ABAP and in Java. You seem to think the open source POI which natively generates Excel from Java is ok, so why is abap2xlsx a problem?
I’m sorry, but you really seem to be grasping at straws here.
Hi,
1 – To have OLE in the back ground open up new options currently not available .
2 – Regarding office integration. There is a package in SAP called SOFFICEINTEGRATION ,
I would like to use it in back ground mode, Can I ?
Because if we could no body would bother to create Java code , abap2xlsx or the recent XLSX Workbench .
3- If there is a real customer need ?
I do not know if this reflect “customer need” but I can see that there is a a lot of interested people here .
Regards.
I looked into this twelve years ago. At the time there was software available for Unix, which could communicate with a windows server running MS Word, Excel etc.
Since it would take quite some time to implement, it was decided that DOI and running in foreground was sufficient for the business requirement.
Hi,
Based on the questions about the subject in the forum I am afraid that
users wants to have this facility.
SAP simply do not want to invest….
Regards.
They don’t need to invest. There’s abap2xlsx and XLSX workbench. I really don’t understand your complaint.
Hi,
I simply see the deliberate inhibit of OLE as unfair .
Regards.
What deliberate inhibition???
See the response of Frank Thielemann
OLE definitly needs SAPgui. See http://help.sap.com/abapdocu_740/en/abenole2.htm:
“The statements of the automation command set are transferred from the ABAP runtime environment to the SAP GUI on the current presentation server which is responsible for the actual communication with the automation server.”
😥 😥 😥
Of course it needs SAPGui. SAPGui is the OLE client. Which is why you’ve only got OLE in foreground.
Hi,
My application server is windows server.
OLE is part of the operating system .
OLE as came out from Microsoft do not need any interactive session .
I want to use OLE as part of a back ground job .
The only way to use it based on what you are saying is to use external command like cscript.exe which is a shame….
Regards.
Most appservers run on Unix, which has no OLE. In all the many sites I’ve worked on in the past 18 years, I don’t recall one that used Windows for the appservers – so the demand I think is quite low. Further, any OLE at appserver level would only work with a Windows server, which kind of goes against the whole OS agnostic paradigm.
The man doth protest too much, methinks.
Hi,
Now that you mention it it makes a lot of sense.
Thank you .
Regards .
In regard to the OLE vs ABAP2XLSX debate. Here are some reasons I think ABAP2XLSX comes out on top:-
ℹ Most ABAP programmers do not have the skills to go mucking around in the OS level, as that is dangerous if you do not know what you are doing. With ABAP2XLSX everything is done at the application server layer i.e. in ABAP.
(2) OLE takes forever to download data – the cells get filled out one by one and you can watch the spreadsheet filling out in slow motion.
(3) OLE is an officially supported SAP mechanism which means if something is wrong, you log an OSS call, wait fifteen weeks, and then get told it is a consulting issue. With an open source project you can track the progess of issue resoution, I have found things get fixed really quickly, and the best bit is you can fix it yourself. Often I know what SAP needs to do to fix their standard code, and tell them, but they won’t believe me as I am a “customer” as opposed to someone pulled out of a bus queue and thrust into first level support.
(4) Most importantly, no bull fighter ever shouts “ABAP2XLSX! ABAP2XLSX!” whilst waving a red cape in front of a bull.
I think SAP Press are releasing a book on the 3rd of April 2015 about all the latest ABAP features, which has a chapter all about ABAP2XLSX but I cannot for the life of me remember who the author is.
Cheersy Cheers
Paul
The author is you 🙂
5) No-one asks for Café abap2xlsx in France. Though on second thoughts this is an argument in favour of OLÉ
Hi,
Say what is your time ?
I have 7:55
Regards.
CET – so at the time you were writing it was 6:55, and I’d been up and about over an hour.
I am in Sydney, Australia the time with me is currently 7:09 AM on the 23rd of February.
In the UK, it is 8:09 PM on the 22nd of Februray.
in Boston, USA, it is 3:09 PM on the 22nd of February.
Happy to clear this up.
Cheersy Cheers
Paul
I think I already read that chapter, but I want to read the entire book.
3rd of April 2015 is on my calendar!
Ivan
Nice one.. looks like someone loves Smartforms and made an upgrade (or enhancement 🙂 )
I like it and will give it a try.
Thank you for sharing it.
~Florian
Could you describe how to send this xlsx document to SAP printer?
Instead attach it to email like in example above.
Hi, Vladimir. You can print the form using OLE or DOI approach ( see here ). If you mean sending xlsx file to spool – no way, I think.
Regards, Igor.
Hi.
Thanks Igor, but this way is unsuitable in my case. We need print excel sheet in the background or through http request.
hai mr.Igor Borodin,
I really want to appreciate For your great work , and i done that and it worked for me as expected i attached and send mail also.
but i have a one question though i passed ‘ iv_protect = ‘X’ ‘ this way still i can open the attached or saved document without any password to enable password do we need to do any more for that .
Regards,
kiran rachamalla.
Hi, Kiran! This option protects file from changes by users (makes the workbook read only). But it doesn’t protect file from opening.
Regards, Igor.
Ohh Yeh then its working Fine for me
Any way do we have that option to protect the generated file with Password .
And one more i am Getting Dump When i put a table type in the structure like fields
At this Point of Code
LOOP AT ls_buffer_rs–rowstructure ASSIGNING <rowstructure> .
IF sy–tabix GT 1 .
CONCATENATE lv_comp_fullpath ‘-LINE-‘ INTO lv_comp_fullpath .
lv_offset = strlen( lv_comp_fullpath ) .
<rowstructure>–loop_relpath = <rowstructure>–loop_fullpath+lv_offset .
ENDIF .
In class ‘ lcl_formruntime’ Method ‘ compose_process_grid’ .
Kiran, it does not support the password to open the file.
Concerning the dump: What version of workbench do you use? Can you explain ZT_B0DATA_REF structure?
Regards, Igor
Kiran, perhaps the dump appears because you have placed two tables on the same context level. The Grid component allows only one table per context level.
Regards, Igor
Ohh yes i placed 2 tables at the same level ,
Do you have any idea regarding how to place a password protection while opening a file (as u might have checked may references) .
and regarding dump can we avoid the dump (at least can we avoid dump ) i mean need to change code ? but for that need understanding of that code i think .
any way i am using the new version only.
Regards,
kiran rachamalla.
Regarding to password. Maybe I will add this option in future versions, but now this is not supported. You can use callback form and set password via OLE.
Regarding to dump. You are using a Grid component, am I right? The one table per one context level – this is base principle of the Grid (it does not apply to the whole context, but only apply to the subtable which is assigned to Grid). Here are two ways to avoid the dump. First way is changing context. Second way is using Loop/Pattern/Value construction instead the Grid component.
Regards, Igor
yes Using Grid only , Yeh i will try to use the Loop/ Pattern . Thank you for the great response and if done password protection using callback will that work ?
Kiran, see case 4: save with protecting worksheet .
Regards, Igor .
Hai Igor Borodin,
I want to change some details on them as of my requirement but i am not knowing how it is handled , i want to see the [content…].xml file so where can i see that file , i want to see how the content is there is it willingly hidden if not may i know where can i see that xml file .
Regards,
kiran rachamalla,
Hi, Kiran . Just open XLSX-file as ZIP-archive (you can change file extension from .XLSX to .ZIP and open). There are several XML-files for describing content of spreadsheet.
Regards, Igor
hai igor borodin,
Yeh that was great and thank you.
regards,
kiran rachamalla.
Hi Igor,
thanks for amazing tool. I implemented and works very well with Microsoft Office.
Unfortunately I have Open Office 4.1.2 and does not work.
Any suggestions?
Thanks.
Kind Regards,
Domenico.
Hi Domenico, does Open Office not open resultant file? or you can’t create form (in design time) ?
Regards, Igor
Hello Igor, I implemented a form with your tools, and Microsoft Office. If I open the file on a PC with Microsoft Office Excel works fine but if I open it with a PC with Open Office then I just get a blank screen.
Hi Domenico, try so:
* save file to desktop
CALL FUNCTION ‘ZXLWB_CALLFORM‘
EXPORTING
iv_formname = ‘HELLO_WORLD’
iv_context_ref = gs_context
iv_viewer_suppress = ‘X’
iv_save_as = ‘C:\Users\Igor\Desktop\QQQ.XLSX’
EXCEPTIONS
OTHERS = 2.
IF sy-subrc NE 0 .
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 .
ENDIF .
*open file in OpenOffice
cl_gui_frontend_services=>execute(
EXPORTING
synchronous = ‘X’
application = ‘SOFFICE.EXE’
parameter = ‘C:\Users\Igor\Desktop\QQQ.XLSX’
operation = ‘OPEN’
maximized = ‘X’
EXCEPTIONS
OTHERS = 10 ).