The Geek House

abap2xlsx – Generate your professional Excel spreadsheet from ABAP

by
12 Comments

Posted in Abap, Programming

abap2xlsx

Read also on SDN @ http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/20046

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

Most of the standard SAPcomponents (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 fileformats (csv, plain text, mhtml and so on).

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

Community is really active, bloggers tried to create workbooks using the OLE Object technology, 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 andcreate spreadsheet with a professional look and feel SergioFerrari proposed in 2006 a great trick: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 agoabout MS Excel and ABAP I had an idea MSExcel 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 fileextensions are basically cab files with several xlm files. No binary, no proprietary code only zipped plain xml!

The Office Open XML format is afull 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 bundledwithin 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 @ https://cw.sdn.sap.com/cw/groups/abap2xlsx via SAPLink.

Do you want to collaborate in this project? Join us on SAP 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 abap2xlsx project but this will be a really nice to have right now.

Thanks to SergioFerrari and Ferrari Team for their support.

image

SCN references:

External links:

Supported releases:

As from SAP WebAS 7.0

12 Responses

  1. Pingback: Tweets that mention abap2xlsx – Generate your professional Excel spreadsheet from ABAP | Plinky Blog -- Topsy.com

  2. Awesome!!!
    Thanks guys.

  3. romi says:

    How to download code for class abap2xlsx

  4. Ivan Femia says:

    @romi
    http://code.google.com/p/abap2xlsx
    Import on your system using SAPLink.

  5. Florian says:

    Great work!

    I’am looking forward to the graphs feature…

  6. Marc Hoffmann says:

    Do I have to install ALL plugins to make this work in a 4.7 environment? Thanks
    Marc

  7. Ivan Femia says:

    Hi Marc,

    as I wrote
    Supported releases: As from SAP WebAS 7.0
    It should not work on a 4.7

    Cheers,
    Ivan

  8. Dev says:

    Hi Ivan

    when the multiple worksheets sent via email, getting the format error.Please let us
    know we need to use any specfics sending email?

    Thanks& regards
    Dev

  9. Billy says:

    How do I paste a return variable to a cell in an existing excel file on the presentation server?

    for instance, my code is already posting a Journal Voucher from an excel 2003 file but I need to return the FI Document number created in a cell of the same excel file that I used to upload the data.

  10. Jigar says:

    Are pivot tables in multiple sheets possible with this?

Leave a Reply

%d bloggers like this: