Create and Email a Decorated Excel using HTML
Excel Creation Methods :
Today there are a number of methods to create a formated Excel file like Standard FMs, IXML Interface, OLE, Using XML syntax , Using HTML syntax and few more Interfaces ( available over Code Share Pages )..!!
Flexibility and Ease of Use :
Well, taking about Ease of use, I would always prefer Standard FMs.
But the problem with Mr. FM is, there stiffness. I mean they are not really flexible enough to fulfill my business needs. 🙁 My Business Team always thinks, creating an Excel thru SAP is equivalent to creating an Excel thru MS Office..!! ;( 🙁
Basically FMs creates Excel, which are more sort of fixed format file..!!
Next comes IXML interfaces, they are obviously flexible but I find them a bit bulky interms of Memory ComPlexity..!! Probalby, because you need to instnciate a Class and then set its attribute before using just a simPle Style, font or a new color../!! My business asks for a highly decorated Excel..!! Seems tough this way..!!
OLE ,, really tough stuff to deal with, once OLE starts to create Excel,, I usually move out of my desk, m sure, it will take a handsome amount of time..!!
Yes, Mr. OLE is really laZy, just like me. Takes too much of time for Excel creation..!!
So, I will oPt for XML or HTML Syntax out of all the available candidates..!!
Hmmm, I didnt talked about the other interfaces, actually I could not dare to PeeP inside,, My SAP has emPowered me with some beautiful tools.
No need to look at something else..!!
The HTML Way :
Create all you want using HTML Tags and Syntax, download it in a XLS file format and Now its ready to use..!! 😛 😉
As simPle, as a two minute noodle,, well.. Yes it is..!! lol 😉
You can create a Table, Paint it easily, with the color, your near and dear wants..!!
<TABLE>
<td colspan =4>
<div><font size =5>Test Excel</font></div>
</TD>
</TR>
<tr>
<td colspan =9>
<font size =5>Excel Details</font>
</TD>
</TR>
HTML Tutorial will tell in detail about the HTML Syntax..
Create the Excel using your tags, keep them in an internal table or better in a String..!!
Use ,,
cl_bcs_convert=>string_to_solix with code page 4103..!!
Create the Doc Object using
cl_document_bcs=>create_document with tye ‘RAW’..
Add the Excel attachment using document->add_attachment, with tye ‘xls’..
Now, set_document,, add recipient and then send document
For Mail sending area, Object report BCS_EXAMPLE_7 is also helpful..!!
Comments and Rectification :
Kindly put your comments, which can help me to further add values to blogs..!!
My mentors and guides over SDN, kindly rectify me if I am wrong somewhere..!!
Thanking You All..!!
Why not use something really powerful like abap2xlsx? abap2xlsx - ABAP Development - SCN Wiki
Hi Matthew Billingham,
Yes we can use them, but I will not prefer it, if I am not going to use it fully...!!
1. I will hav to import the complete object set...
2. For importin external object, I will hav to go to a complex workflow of approvals.
These are major bottlenecks..
Thanking You All..!!
In answer to both of those - if you invest the effort, then you/your client ends up with a very powerful useful tool. I've managed to get it implemented in multinationals with very strong change control.
Hi Matthew,
Thanks alot for the guidance.. wil certainly try it, if my customer approves the external object..!!
Thanking You All..!!
As it is open soruce, a scan for any select/insert/delete/update rfc calls is possible, and nothing of them can be found in the source,.
And the next improvement will come! *tatatata*
some advertisment 😉
individual coloring of barcharts/pie charts and line charts,
No no no. Let's keep redeveloping the wheel (or in this case, a nicely painted wheel instead of the complete Porsche 911, at an incredibly reasonable prices of £0).
If the business become aware they have the whole kit and kaboodle of Excel in their ABAP generated reports, they'll DEMAND that abap2xlsx be implemented.
Well, it was my demand 😆 and i did it, Ivan just needs to make a new packjage...
Rainer Hübenthal do you mean the latest Codata and Cofile?
I'll do as soon as I can... 🙂
Good approach of explanation...
Hi Ankit,
I am glad, you liked it..!!
Thanking You All..!!
Great Job Ankit. Thanks a lot for your elaborate explanation with pros and cons of different approaches. Kudos for your intelligence.
Ankit, Nice Stuff. You are right it is not easy to convince business to make use of open source solution. Though its great. Could you please share your code I mean just the HTML data in string and output screenshot. I also get lot of request from users please email us the formatted excel file.
Hi Jason,
Sorry for late reply..!!
Because of the Security policy of my Organization, I could not share the complete code, but I will certainly help you to achieve the desired result..!!
1. Pls Copy the Standard Report BCS_EXAMPLE_7..!!
2. clear the variable
lv_string at line no. 142
and
concatenate the following content
<TABLE>
<td colspan =3><div><font size =5>Decorated Excel using HTML</font></div></TD></TR>
<TD align=left><div><font size =3><u>Name</u></font></div></TD>
<TD align=left><div><font size =3><u>Email</u></font></div></TD>
<TR bgColor=lightyellow>
<TD align=left><div><font size =1> Jason Bourn</font></div></TD>
<TD align=left><div><font size =1> Private</font></div></TD>
<TABLE>
<TD align=left> </TD>
<div><b><font size =2> Enjoy SAP</font></div></TR>
Save, Activate and Execute the Z Program..!!
You can see the email with attachment in SOST..!!
Hope this helps, Pls revert if any additional effort is required from mine end.
Thanking You All..!!
Unfortunately this will work only when I have to email an XLS document. We cannot download it to your desktop or Application Server. May be that's when we need abap2xlsx
Hi Yuvaraj,
No, we can easily download the Excel file to our desktop.
I have even tried it.
Thanking You All..!!
Hi Ankit,
I'd appreciate it if you explain me how do download a document created in binary using cl_bcs_convert=>string_to_solix( ) into desktop as an XLS document.
Using GUI_UPLOAD and 'BIN' as file type?
Thanks,
Yuvaraj S
Hi Yuvaraj,
If you have referred the Report BCS_EXAMPLE_7,
here
cl_bcs_convert=>string_to_solix
returns us a table binary_content.
Convert this Binary content into Hexadecimal X.
Use FM GUI_DOWNLOAD with file type BIN and pass the Hexadecimal Table to this FM.
We can download the Excel, over our Desktop..!!
Pls. post if there are any issues.
Thanking You All..!!
Is there a way to use this method for also XLSX-Files?
(Can't use ABAP2XLSX at work...)
I would like to clarify, that this will just create some sort of (invalid) HTML file with .XLS extension, which MS Excel luckily parse and open.
Changing extension will not change format of file!
Yes, that was my experience as well. I managed to create an MHTML-File via Transformation. You can open the xls-file using MS-Excel 2013, but it always gives a warning that has to be ignored. I had hoped there was a better way to do this.
If you can not use abap2xlsx, then you can try "cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform" method (there are some tutorials for it).
I will try this.
Is it possible to create a workbook with multiple sheets that way? Else it's not sufficent for my needs. I need to build an excel where each sheet consists of a different message-list.
If this won't work I see myself copying parts of ABAP2XLSX ... I hope I can avoid that.
I am afraid that "cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform" method is creating only single sheet table 🙁
That's what I feared... Still I can use this method for standard-Excel-Output.
Any other alternative - I mean before I start rewriting some of ABAP2XLSX-code?
One of our task is to teach customer and IT departments about new solutions and advantages.
I would suggest to talk with your customer and explain the advantages of abap2xlsx. Maybe for your project abap2xlsx is to much, but it could open new possibilities/scenarios in the future.
Installing a mini version (home made) of abap2xlsx it will not bring any strategic advantage to the company.
I would suggest to also present this presentation (even if it is dated) to your manager and explain your suggestion.
Ivan
Thanks - that would be propably the best. Yet I will have to do some re-writing as they have some very strict naming-conventions. But I guess that I can handle this. (I also had to hide the ZSAPLINK-Installation, as this is regarded as "non-secure" by the bosses)
Difficult situation at this company, but I will cope somehow.
Have to agree with Matthew that abap2xlsx would probably make more sense at this point. At minimum it would be helpful to include "the business case" - why there is a need for such spreadsheets? What do the users do with them? Our users love to export SAP reports into Excel but we encourage the use of templates and macros in Excel instead of formatting from SAP.
And I have to ask - what's the deal with punctuation in this blog? I'm sorry but it looks rather unprofessional and is difficult to read due to such odd use of punctuation marks and capitalization all over the place.
Thanks - the point is simple, that the customer has a working solution for old .xls files. There he has a list in form of an excel-workbook with each sheet listing the different error-cases. It's the result of a program that automatically creates the billing for all due clients. These billing-data is on the first sheet (the positive-list). On the following sheets you find all cases that would be due, but couldn't be billed because of: 1) wrong adresses, 2) missing data, 3) wrong data, and so on. Each case on a different sheet. They keep this excel-files, because their customers phone and ask them why they got no bill, and so they can tell them - "We got a wrong adress from you" or "We miss some data" and so on. (This billings mean money for the customers else they wouldn't call)
With new Excel 2013 it's still working, but when opening the xls-file you always get a message that the format doesn't fit and the file is propably damaged. If you ignore the message the file opens without any problem and works fine.
Now you might say it's just a minor fault of Office 2013, but it can get annoying at a time, so they asked me to change it to proper xlsx, so it will all work like it did before the MS-Office-Upgrade. Yet with all the restrictions and impacts I wonder if the effort is really justified...
Hello guys,
Have any of u done excel with multiple sheets using html in sap, can u help me on it.