Blogs

Ivan Femia

abap2xlsx - Generate your professional Excel spreadsheet from ABAP
Ivan Femia Active Contributor Platinum: 2,500+ points SAP Mentor
Business Card
Company: Techedge
Posted on Jul. 12, 2010 08:36 AM in ABAP, Code Exchange, Community Projects, Open Source, SAP Developer Network

URL: http://www.plinky.it/blog/2010/07/12/abap2xlsx-–-generate-your-professional-excel-spreadsheet-from-abap
Subscribe.Subscribe
Print. Print
Permalink Permalink
Share
Share

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 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 and create spreadsheet with a professional look and feel Sergio Ferrari 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 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 @ https://cw.sdn.sap.com/cw/groups/abap2xlsx 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:

 

Supported releases:

As from SAP WebAS 7.0

Ivan Femia  Active Contributor Platinum: 2,500+ points SAP Mentor SAP Mentor & SAP NetWeaver Consultant at Techedge follow me on Twitter @IvanFemia

Comment on this article
Comment on this weblog
Showing messages 1 through 69 of 69.
Titles Only Main Topics Oldest First

  • Error with import
    2012-01-06 11:18:36 Sajjad Ahmad Business Card [Reply]

    Hi,


    I tried to import but I got error message. I did import plugins but did not workout.
    I got following message
    There is no installed SAPlink plugin for object type DOMA
    There is no installed SAPlink plugin for object type DTEL
    There is no installed SAPlink plugin for object type TABL
    There is no installed SAPlink plugin for object type TTYP


    Thanks,


    Sajjad

  • Great code!
    2011-08-05 08:04:01 Gemini Twin Business Card [Reply]

    I downloaded abap2xlsx and I can see why everyone likes it so much. Great work!
    I have 2 questions. 1, is it possible to zoom out by 1 iteration (I cannot see a method for this so I assume not at this moment)? Second, is there example code of outputting a bespoke itab to excel? There is an example, demo number 3 I think, but this binds to itab creation of a sap std table. I am trying to code this but struggling. :(
    Thank you.
    Warren.
    • Great code!
      2011-08-05 08:11:14 Gregor Wolf Business Card [Reply]

      Hi Warren,


      1) Please check the CodeExchange Issue List at https://code.sdn.sap.com/spaces/abap2xlsx/tickets/custom_report/1 if this feature has already be requested. If not provide a detailed description of what is needed. Also attach a sample XLSX file for reference.


      2) The internal table can also be a custom table. The used structure has to exist in the data dictionary.


      Best regards
      Gregor

  • saving to an existing 2003 xls file
    2011-08-02 08:49:18 Belizaire Vital Business Card [Reply]

    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.


    Please help

    • saving to an existing 2003 xls file
      2011-08-02 13:22:06 Gregor Wolf Business Card [Reply]

      Hi Belizaire,


      abap2xlsx does not only have a class where you can write .xlsx files. It does also provide a reader class which creates a abap2xlsx object. This can then be used for the input data and you also can add additional data to it.


      Best regards
      Gregor

  • Background processing with errors
    2010-09-23 05:38:23 Arthur Viool Business Card [Reply]

    Hi Ivan,


    Thanks for sharing this really great solution with us. It is just what I was looking for.


    I have one question;


    You state that background processing is possible.


    I assume that this works by using open dataset, transfer and close dataset.
    I tested this by using “open dataset XXXX for output in binary mode”.
    When trying to open the resulted xlsx file, I get the message that the content is unreadable, with the question whether or not I want to restore the file. When I confirm this the xlsx is opened and after a second popup is clicked away the file is usable.
    Do you know a method to download the xlsx files in the background in a way which results in files without errors?


    Thanks again,


    Arthur

  • Demo Reports
    2010-09-22 02:18:28 Stefan Riedel-Seifert Business Card [Reply]

    Hi,


    please provide in future not fixed output folders in your demos, because for example in our company there are no write capabilities in the C-Partition, and also not in Root on D.


    When opening some generated file in Excel Xp i will get an error:


    This file was created in a newer version of Microsoft Excel .... OLE (?) ...


    I have no found any comments of (in-)compatibility with excel versions. Please provide informations.


    Regards,
    Stefan


    • Demo Reports
      2010-09-22 03:45:58 Ivan Femia Business Card [Reply]

      Hi Stefan,


      we discussed about the fixed path some days ago, we will provide a better solution in the next daily build.


      Regarding the incompatibility issue, I also use XP without any problem, could you please explain better.


      Regards,
      Ivan

  • Runtime Errors
    2010-09-22 01:42:12 Stefan Riedel-Seifert Business Card [Reply]

    I'am wondering a little bit, that the code is running. On our (7.01 Basis-)System after importing the transport files (main + patch): none of the demos are running:


    ...
    It was tried to call a not-implemented method
    "ZIF_EXCEL_SHEET_PROTECTION~INITIALIZE" in the class "ZCL_EXCEL_WORKSHEET".
    ...


    Why?


    Regrads, Stefan

    • Runtime Errors
      2010-09-22 02:58:47 Gregor Wolf Business Card [Reply]

      Hi Stefan,


      when you have imported the release using SAPlink please get the SVN Version for the SAPLink Class PlugIn from http://saplink.googlecode.com/svn/trunk/core/CLAS_ZSAPLINK_CLASS.slnk it fixes that problem. After activating the plugin you have to do the import again.


      Best regards
      Gregor

  • e-mail file generated
    2010-09-14 08:11:35 Stephen Herlick Business Card [Reply]

    Hi Ivan;


    Really get tool and appreciate the work you and others have done to enhance the tool.


    I have generated a file and downloaded to my PC directly. When I open the file in Excel 2007 everything is good. However, when I e-mail the same file via ABAP, when I open it in Excel 2007, I get a minor formatting error. Not sure why this is happening as I am using the same code I used to e-mail CSV files and all works ok.


    Have you tried e-mailing the file?


    Any thoughts you have would be appreciated.


    Thanks


    Stephen

  • No more Excelophobia !!!
    2010-08-04 11:58:08 Suhas Saha Business Card [Reply]

    Whenever i read "The report output can be downloaded as an Excel format" in the spec, the first thing that crossed my mind was "No can do. Have to convince the functional guy to give up his fetish for excel". The fear of using OLE gave me goosebumps.


    Thanks to you, Ivan, petty developers like me can turn into XLSX magicians :-)


    All in all very nice work & looking forward to xlsx2abap project !!!


    Cheers,
    Suhas


  • Good Job!
    2010-08-04 04:17:45 Leonardo Araujo Business Card [Reply]

    Good job man!
    I had built a report class(another community project - ABAP Report Wizard) in the past that encapsulated the report formatting in ALV. That was great cause all the reports using it were sharing the same look/feel and functionality.
    I will take a look at implementing a custom ALV function (new button) that will implement your export to XLSX functionality.
    I will keep you posted.
    Great job.


    Leo

  • Very nice work
    2010-08-02 09:54:08 Thomas Jung SAP Employee Business Card [Reply]

    I realize I'm a little late to the game here. I've just had the chance to download and try out this framework. Excellent work. I was really taken back by the high quality of the work. This isn't just a prototype or code sample - but a really well designed and assembled framework. To hide the level of complexity that is going on here with a simple interface is no small feat.


    Even better is the fact that you are sharing this with everyone. I hope you guys submitted this for Demo Jam. The framework alone would go over well - but then combining it with (as suggested in the forums) some simple REST based service for Excel output would really blow people away.

    • Very nice work
      2010-08-04 09:36:16 Ivan Femia Business Card [Reply]

      Hi Thomas,


      thanks for your comment.
      Unfortunately I'm in late for Demo Jam as I know the submission deadline was July, 31.
      Btw I will carry on with framework developments also with SDN contribution.


      Regards,
      Ivan

  • Formating of Numbers
    2010-07-31 19:37:55 Stephen Herlick Business Card [Reply]

    Hi Ivan;


    Really cool utility. I have been playing with it a bit and find it really useful.


    One question, I tried changing the number format in ZDEMO_EXCEL6 from c_format_number to c_format_number_00. It didn't seem to have any affect.


    Any idea?


    Thanks


    Stephen

  • Backport to 6,20
    2010-07-29 05:15:55 Ludek Vondruska Business Card [Reply]

    I tried this project too, but I had a problem. We are running 6.20 and we don´t have CL_OBJECT_COLLECTION and CL_OBJECT_COLLECTION_ITERATOR classes in our system.


    I had to copy these classes from release 7.01 and adjust source code to use the copies named ZCL_OBJECT_COLLECTION a ZCL_OBJECT_COLLECTION_ITERATOR.


    Now I have backport of abap2xlsx on 6.20 and it seems, it is working.

    • Backport to 6,20
      2010-07-29 09:36:51 Ivan Femia Business Card [Reply]

      Sounds good, i prefer to maintain 'public' version compliant to sap standard classes, if you could test it for me in 6.20 we could insert this trick into documentation.


      Thanks
      Ivan

      • Backport to 6,20
        2010-11-15 14:20:57 Bruce Tjosvold Business Card [Reply]

        Ivan,


        We are on Basis 620, patch level 0065.


        I am running into a problem with several ZDEMO* programs that use the following line:


        column_dimension->set_width( 13 ). "ZDEMO_EXCEL10


        causes this error:


        "You can only omit the parameter name if the method has a single parameter. This must be an importing parameter."


        Any thoughts on the cause, or a correction?


        Thanks
        Bruce

        • Backport to 6,20
          2010-11-16 13:56:05 Gregor Wolf Business Card [Reply]

          Hi Bruce,


          I've fixed some of the missing parameter problems in report ZDEMO_EXCEL10 and ZDEMO_EXCEL11 in the SVN version of A2X. Perhaps you try it.


          Best regards
          Gregor

          • Backport to 6,20
            2010-11-17 15:30:45 Bruce Tjosvold Business Card [Reply]

            Gregor,


            We are on SAP_ABA 620 SAPKA62065.


            I installed those two programs using the slnk files. They now compile.


            However, all ZDEMO programs that use the following code abend.


            CREATE OBJECT lo_excel.


            giving the following message:



            ZCL_EXCEL=====================CP :
            "Incorrect logical expression: Instead of "LO_ITERATOR->HAS_NEXT(" a fi"
            Error in ABAP application program.


            With a break-point set at "create object lo_excel.", the program abbends as soon as I hit F5.


            My guess is that the folowing method of class ZCL_EXCEL is causing the problem.


            method get_next_table_id .
            data: lo_worksheet type ref to zcl_excel_worksheet,
            lo_iterator type ref to zcl_object_collection_iterator,
            lv_tables_count type i.


            lo_iterator = me->get_worksheets_iterator( ).
            while lo_iterator->has_next( ) eq abap_true.
            lo_worksheet ?= lo_iterator->get_next( ).


            lv_tables_count = lo_worksheet->get_tables_size( ).
            add lv_tables_count to ep_id.


            endwhile.


            add 1 to ep_id.


            endmethod.


            Class ZCL_EXCEL activated successfully.



            Thanks
            Bruce

  • Formula.xlsx
    2010-07-25 20:58:36 Steve Sinclair Business Card [Reply]

    I'm running Excel 2007 on XP/SP3.


    For some reason the formula demo isn't working. ZDEMO_EXCEL6 code has the value of the sum() in it but if I set that to zero, run it then open formula.xlsx it shows the sum(c4:c8) as zero instead of 1760. Doing a manual calculation using F9 doesn't recalculate the value.


    Any ideas?


    Thanks
    Steve

    • Formula.xlsx
      2010-07-26 09:58:24 Ivan Femia Business Card [Reply]

      Hi Steve,


      this is how Excel works, you need to calculate the sum or change a value into the range from excel in order to force the calculus.
      I didn't find a solution right now and I don't now if any exists.


      Thanks
      Ivan

  • Great blog Ivan!
    2010-07-21 09:03:01 Alvaro Tejada Galindo SAP Employee Business Card [Reply]

    Mr. Sergio Ferrari himself made note this blog. Great work Ivan, really cool and thanks for the link to my humble blog -:)
    Keep on doing such a great job and I hope I could find some time to maybe contribute -;)


    Greetings,
    Blag.

  • abap2xlsx - Generate your professional Excel spreadsheet from ABAP
    2010-07-21 00:06:19 Anitha Babu Business Card [Reply]

    If there is something which I have been craving for, this is one such blog on EXCEL.Out of my recent experience with a client , I delivered an EXCEL download as they wanted it and could not understand why the GUI_DOWNLOAD cant download REAL EXCEL.


    So I wrote a blog to share my experience as to what REAL EXCEL means the approach I adopted was OLE, much traditional way of doing it since there were hundreds of blogs and fixes on what i was looking for.


    This is simply great!! Thanks very much for sharing this wonderful blog. Thanks Again.


    Regards,
    Anitha

  • Breaking rulz in ABAP2Excel
    2010-07-15 10:58:51 Raul Miguel Romero Galindo Business Card [Reply]

    Until this moment, this is the ABAP technical post of the year!!!
    Excellent work, really. Just as a suggestion: in your demo programs you could put a path parameter in order to export the Excel content in any directory that anyone wants. Just as a suggestion :D.


    And Ok, I join to the vuvuzelas's party with Ivan!!!


    Thanks in advance.
    R. Romero

  • Great work!
    2010-07-14 00:09:24 Andrea Olivieri Business Card [Reply]

    A chorus of vuvuzelas to consecrate the best ABAP of the year from my friend (and colleague) Ivan!
    • Great work!
      2010-07-14 00:20:32 Ivan Femia Business Card [Reply]

      Thanks Andrea,


      let's do the Vuvuzela Dance


      http://www.vuvuzela-time.co.uk/weblogs.sdn.sap.com/pub/wlg/20046


      Ivan

  • Totally cool
    2010-07-13 04:02:05 Chris Paine Business Card [Reply]

    If you actually succeed in being able to parse XLSX files back into an readable format - you will change the interface of SAP - the accountants will _LOVE_ you!


    Great work guys!


    One minor point:
    >Can you imagine a REST service like this?
    >http://www.techedge.it/Rest/CustomerList.xlsx?VKORG=1001
    doesn't work for me...


    Cheers,


    Chris

    • Totally cool
      2010-07-13 04:28:50 Ivan Femia Business Card [Reply]

      Hi Chris,


      Thanks for your feedback, I really appreciate.


      >One minor point:
      >>Can you imagine a REST service like this?
      >>http://www.techedge.it/Rest/CustomerList.xlsx?VKORG=1001
      >doesn't work for me...


      It's an idea, not yet a real implemented project.


      Regards,
      Ivan


      • Totally cool
        2010-07-13 05:54:21 Chris Paine Business Card [Reply]

        Ivan, Doh! - my bad! But great idea. I've posted the link to the blog to all my collegues - this project is quite exceptional in the potential it has to change how we do things.
        Thanks!
  • Being a bit fair to OLE :)
    2010-07-13 02:21:04 Serdar Simsekler Business Card [Reply]

    Hi Ivan


    Definitely a brilliant idea and a good blog. Just a couple of points.


    - Well, like everything else this one seems to be buried in the pages of history but it may be worth mentioning the tutorial "An Easy Reference for OLE Automation" at http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/204d1bb8-489d-2910-d0b5-cdddb3227820. (That's not only because I am the author :))


    - Mentioning the tutorial, we can say "OLE Excel" has some capability for "Advanced Formatting" as OLE calls makes most formatting options available.


    All in all, still your new approach is superior thanks to the already mentioned features. And I think being platform independent and being usable in background processing are obviously great enhancements.


    Cheers...


    Serdar

  • Very good work...
    2010-07-12 16:26:29 Renato Pereira de Souza Business Card [Reply]

    I was trying to do the same (not so good indeed) :):)
    I´ve installed on our system.. works very well...
    congratulations...
  • Reading XLSX Files
    2010-07-12 09:40:09 Kevin Barter Business Card [Reply]

    Have you tried to read the files back into SAP? I have done a little bit, and can get it to work, but I am not an XSLT expert, and am sure it could be done better.
    • Reading XLSX Files
      2010-07-12 09:51:04 Ivan Femia Business Card [Reply]

      Hi Kevin,


      I have planned the xlsx Reader as you can see in the Roadmap on Google Code or on Google Wave.


      As I wrote on blog "I have also scheduled an xlsx2abap project but this will be a really nice to have right now."


      Regards,
      Ivan

      • Reading XLSX Files
        2010-07-12 13:41:20 Gregor Wolf Business Card [Reply]

        Hi Kevin, Hi Ivan,


        first of all thanks to Ivan that you started this great project!


        It is an excellent timing as I need an ABAP based replacement for PHPExcel? It is a XLSX creator based on PHP. I've used it in one project to get a nicely formated XSLX file which can be used to do a download/upload from CRM. Unfortunately it is very memory consuming and so I'm only able to create files with less the 1500 lines. But what we could take from the PHPExcel project could be the class design and the method names so it would be easy to move from one lib to the other.


        @Ivan: Do you know my ZAKE_SVN Project which would allow you to checkin / checkout code into the Google Code Subversion repository? Would love to see if you could use it.


        Best regards
        Gregor

        • Reading XLSX Files
          2010-07-12 14:02:20 Ivan Femia Business Card [Reply]

          Hi Gregor,


          I know PHPExcel and some ideas are coming from it.
          Your help is really appreciated!


          Tomorrow I will try ZAKE_SVN.


          Thanks,
          Ivan

  • Source Code (not .NUGG)
    2010-07-12 09:39:11 Kevin Barter Business Card [Reply]

    Any chance of seeing the source code directly (not in a SAPLink file)?


    The client I am at will not allow the code to be installed.

    • Source Code (not .NUGG)
      2010-07-12 09:48:43 Ivan Femia Business Card [Reply]

      Hi Kevin,


      library consist in more than 100 objects (class + DDIC), it is really impossible to share the code in an other way.


      Ivan

      • Source Code (not .NUGG)
        2010-07-20 07:23:43 Saquib Khan Business Card [Reply]

        Great Job ! Is there way to build up excel columns dynamically based on ABAP report output.


        Example, I have written a utility , which convert your ABAP report dynamically based on variant and send out an email as PDF attachment in background/foreground.


        I tried to do this dynamically for the excel as well, every time column headings and data got messed up. Is there way we can do it.


        BTW I saw some of your code, pretty impressive.


        Keep up the good work.


        ~ Saquib

Showing messages 1 through 69 of 69.