Skip to Content
Author's profile photo Ivan Femia

abap2xlsx – Generate your professional Excel spreadsheet from ABAP

image

One of the main request of many customers is to use MS Excel spreadsheet as data output for reports and analysis.

Most of the standard SAP components (such as ABAP ALV, WebDynpro ALV, System menu, etc.) provide the option to export data in MS Excel 2003 or in many other MS Excel compatible file formats (csv, plain text, mhtml and so on).

What are the limits? Most of them depend on SAPGui or Web Dynpro and are not available for example in case you want to send it as eMail attachment.

Community is really active, bloggers tried to create workbooks using the Excel OLE and ABAP – Create fancy reports, this is a great approach but OLE is too much SAPGui dependent:

  • Excel cannot be created in a not dialog process
  • Excel cannot be used as email attachment (without some dirty trick)
  • Is platform dependent

To avoid some limitations and create spreadsheet with a professional look and feel Sergio Ferrari proposed in 2006 a Downloading data into Excel with Format Options (from SAP Web Applications):create an HTML file and open in Excel.

What are the limits again?

  • Only one sheet for each workbook
  • No graphs
  • No conditional formatting
  • HTML extension is not associated by default with MS Excel
  • No excel advanced features

The technology

Talking with Sergio some days ago about MS Excel and ABAP I had an idea MS Excel 2007 xlsx file format!

I studied the structure of new MSOffice 2007 documents some months ago and I noticed the power of these new document types, but I never had so much free time to deeply analyze their code.

Some of you probably know what’snew in MS Office 2007 documents file format (docx, xlsx, pptx); these new file extensions are basically cab files with several xlm files. No binary, no proprietary code only zipped plain xml!

The Office Open XML format is a full fidelity (all features of the product are supported) file format for Excel2007, and it is the default file format that Excel uses to save newfiles.  These files are composed of several XML parts, all bundled within a zip-compressed file for efficient storage. (ref. msdn)

image

The idea

So the idea: An xlsx generator from scratch in ABAP.

What are the business advantages?

image

and the technical ones?

image

what about themain features?

  • Cell formatting (as in HTML solution)
  • Formulas (as in HTML solution)
  • Multi sheets new!
  • Conditional formatting new!
  • Cell data format new!
  • Graphs new!
  • Drawings new!
  • And many others new!

Professional MS Excel workbooks as background process, as email attachment, as http response in a WDA, using an RFC and so many othercontexts.

Can you imagine a REST service like this?

http://www.techedge.it/Rest/CustomerList.xlsx?VKORG=1001

image

Developers can easily produce Excel with few lines of ABAP code; abap2xlsx class wraps all the logic of xlsx generation for professional and advanced workbooks. See Hello World demo code (more demos available on Google Code),only 5 lines of ABAP code and a new MS Excel is here!

Hello world excel demo code:

image

The output will be:

image

Now unchain your fantasy! Professional Excels are real!

image

Code is shared on SAP Code Exchange @ http://www.abap2xlsx.org via SAPLink.

Do you want to collaborate in this project? Join us on Code Exchange.

Comments, real business cases and suggestions are really appreciated!

New features will came in next releases; I created a roadmap on Google Code and on Wave. I have also scheduled an xlsx2abap project but this will be a really nice to have right now.

Thanks to Sergio Ferrari and Ferrari’s Team for their support.

image

SCN references:

External links:

For support follow these best practices:

Supported releases:

As from SAP WebAS 7.0

Assigned Tags

      254 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Eugen Littau
      Eugen Littau

      Does abap2xlsx allow to set a MIP (Microsoft Information Protection) label for the file?

      Author's profile photo Gregor Wolf
      Gregor Wolf

      Dear Eugen,

      please search at abap2xlsx/issues for existing issues on this topic. If you can't find one please create an issue and provide information to the specification. We also are happy if you solve this requirement by sending us a pull request.

      CU
      Gregor

      Author's profile photo Mohamed Yosufdeen JMH
      Mohamed Yosufdeen JMH

      Dear Eugen Littau,

      Yes, it is possible to automatically add MIP (Microsoft Information Protection) label for the downloaded file from abap2xlsx.
      HALOCORE is a data security software for SAP | SECUDE
      We are happy to help you, please reach out to Contact@secude.com to see it in action.

      Kind regards
      Yosuf

      Author's profile photo Wolfgang Braun
      Wolfgang Braun

      Hello,
      is there any way that I can use ABAP2XLSX to transfer all cell settings (styles, colors, font sizes, fonts, connection of multiple cells etc) for a defined row and column from an uploaded excel/sheet to a newly created excel/sheet.
      Unfortunately I have not found anything general about this.
      Thanks a lot

      Author's profile photo Sandra Rossi
      Sandra Rossi

      There is currently the Pull Request #1015 (CLONE_WORKSHEET) that I'm currently validating (it takes a little time). You could try it and tell us whether it works for you.

      Author's profile photo Sandra Rossi
      Sandra Rossi

      If you don't want to create a new sheet every time, just use one as template, you could use the template feature -> ZDEMO_EXCEL_FILL_TEMPLATE.

      Author's profile photo Wolfgang Braun
      Wolfgang Braun

      Hi Sandra,

      I have a question about the "Excel Template"?

      The above mentioned program is working if I use the template provided by the ZDEMO_EXCEL_FILL_TEMPLATE.

      When I use my Excel, were I have to fill my different sheets, it is not working.

      Do I have to set the ranges in the Excel, where the data should be filled?
      How I have to define these ranges in every sheet?
      Can you give me a hint?

      Thanks Wolfgang

       

       

      Author's profile photo Sandra Rossi
      Sandra Rossi

      The concept of template in abap2xlsx is to mark regions of your Excel file by using range names.  You then use the program ZDEMO_EXCEL_GET_TYPES to obtain ABAP code to define the ABAP code of a complex structure corresponding to these range names. Then, in ABAP, you inject the values in this complex structure and you execute the method FILL_TEMPLATE. The author has described it in this blog post, and later merged it into abap2xlsx. If you need further information, please open an issue at abap2xlsx.

      Author's profile photo Wolfgang Braun
      Wolfgang Braun

      Hi Sandra,

      I now recognized how to work with the Excel Templates.
      But I found a bug in method validate_range:
      if sheet name contains a space like "Master Data" Excel creates the ranges of the names with Apostroph (') like 'Master Data'.
      Subsequently the validate_range method fills the field sheet in the mt_range-table with 'Master Data'.

      Later on, when the mt_range table is processed in the fill_range method the lookup in the mt_range is done with the sheet name without Apostroph (Master Data) in my case and therefor there is no match.

      Wolfgang

      Author's profile photo Gregor Wolf
      Gregor Wolf

      Would be great if you could contribute a correction via the GitHub repository at abap2xlsx

      Author's profile photo Wolfgang Braun
      Wolfgang Braun

      Hi Gregor,

      I adjusted the code and it works now proberly for my case.
      When I finished my testing, I will provide the correction.

      Wolfgang

      Author's profile photo Wolfgang Braun
      Wolfgang Braun

      Hi,

      I have to further questions:

      1. ist there a chance to read a .xml file with abap2xlsx.I believe the extension .xml is Excel 2003.
        When I try, there is an exception due to missing some files which are expected e.g '_rels/.rels' ?
      2. Is there a possibility to convert the result (xstring) from lr_writer->write_file( lr_excel ) to a xml-string and download as xml-file?

      Thanks
      Wolfgang

      Author's profile photo Gregor Wolf
      Gregor Wolf

      Hi Wolfgang,

      I would suggest you create an issue at https://github.com/abap2xlsx/abap2xlsx/issues and provide more details about the usecase and also an example of the file.

      Best Regards
      Gregor