Skip to Content
Author's profile photo Former Member

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..!!

Assigned Tags

      28 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Matthew Billingham
      Matthew Billingham

      Why not use something really powerful like abap2xlsx? abap2xlsx - ABAP Development - SCN Wiki

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      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..!!

      Author's profile photo Matthew Billingham
      Matthew Billingham

      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.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Matthew,


      Thanks alot for the guidance.. wil certainly try it, if my customer approves the external object..!!


      Thanking You All..!!


      Author's profile photo Rainer Hübenthal
      Rainer Hübenthal

      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,

      Author's profile photo Matthew Billingham
      Matthew Billingham

      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.

      Author's profile photo Rainer Hübenthal
      Rainer Hübenthal

      Well, it was my demand 😆 and i did it, Ivan just needs to make a new packjage...

      Author's profile photo Ivan Femia
      Ivan Femia

      Rainer Hübenthal do you mean the latest Codata and Cofile?

      I'll do as soon as I can... 🙂

      Author's profile photo Former Member
      Former Member

      Good approach of explanation...

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Ankit,

      I am glad, you liked it..!!

      Thanking You All..!!

      Author's profile photo Prathap Telukutla
      Prathap Telukutla

      Great Job Ankit. Thanks a lot for your elaborate explanation with pros and cons of different approaches. Kudos for your intelligence.

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      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..!!

      Author's profile photo Yuvaraj S
      Yuvaraj S

      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

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Yuvaraj,

      No, we can easily download the Excel file to our desktop.

      I have even tried it.

      Thanking You All..!!

      Author's profile photo Yuvaraj S
      Yuvaraj S

      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

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      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..!!

      Author's profile photo Michael Calekta
      Michael Calekta

      Is there a way to use this method for also XLSX-Files?
      (Can't use ABAP2XLSX at work...)

      Author's profile photo Tomas Buryanek
      Tomas Buryanek

      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!

      Author's profile photo Michael Calekta
      Michael Calekta

      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.

      Author's profile photo Tomas Buryanek
      Tomas Buryanek

      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).

      Author's profile photo Michael Calekta
      Michael Calekta

      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.

      Author's profile photo Tomas Buryanek
      Tomas Buryanek

      I am afraid that "cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform" method is creating only single sheet table 🙁

      Author's profile photo Michael Calekta
      Michael Calekta

      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?

      Author's profile photo Ivan Femia
      Ivan Femia

      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

      Author's profile photo Michael Calekta
      Michael Calekta

      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.

      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      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.

      Author's profile photo Michael Calekta
      Michael Calekta

      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...