abap2xlsx - Generate your professional Excel sprea... - Page 2

_IvanFemia_ · ‎2010 Jul 12

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

The idea

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

What are the business advantages?

and the technical ones?

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

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:

The output will be:

Now unchain your fantasy! Professional Excels are real!

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.ferrari2 and Ferrari's Team for their support.

SCN references:

External links:

http://blogs.msdn.com/b/excel/archive/2006/07/20/671995.aspx

For support follow these best practices:

For any question related to abap2xlsx demo, use cases, explanation create a new discussion in SCN Open Source
For reporting a bug please open a issue on GitHub Issues · ivanfemia/abap2xlsx · GitHub

Supported releases:

As from SAP WebAS 7.0

_IvanFemia_ · ‎2010 Sep 23

Hi Arthur,

maybe something wrong in the open dataset-close dataset.

Is the xlsx valid if you run abap2xlsx in foreground?

You can open an issue on project site with a simple report example to verify the issue.

Regards,
Ivan

arthur_viool · ‎2010 Sep 23

Ivan,

The xlsx is valid in the foreground. I have posted the details of the issue on the project site, issue #31.

Regards,

Arthur

gregorw · ‎2010 Sep 23

Hi Arthur,

as you might already noticed I've provided a solution for the issue #31 at Google Code.

Regards
Gregor

_IvanFemia_ · ‎2010 Sep 24

Demo reports have no longer fixed path.
Checkout last daily build.

Ivan

gregorw · ‎2010 Sep 27

You have to update your Kernel when you run into the error described in SAP Note 1385713 - SUBMIT: Allowing parameter of type STRING.

_IvanFemia_ · ‎2010 Oct 10

Hi all,
we moved on SAP Code Exchange
https://cw.sdn.sap.com/cw/groups/abap2xlsx

Former Member · ‎2010 Nov 15

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

_IvanFemia_ · ‎2010 Nov 15

Hi Bruce,

you have to specify the name of the parameter, this will solve your issue 😄

Cheers,
Ivan

gregorw · ‎2010 Nov 16

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

Former Member · ‎2010 Nov 17

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

Former Member · ‎2011 Aug 02

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

gregorw · ‎2011 Aug 02

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

former_member201275 · ‎2011 Aug 05

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.

gregorw · ‎2011 Aug 05

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

Sajjad_Ahmad · ‎2012 Jan 06

Hi,

How to download / install ABAP2XLSX_DAILY.NUGG file?
Any help about it?

Thanks,

Sajjad

gregorw · ‎2012 Jan 06

Hi Sajjad,

go to the abap2xlsx Code Exchange project homepage and check out the installation guide.

Best regards
Gregor

Sajjad_Ahmad · ‎2012 Jan 06

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

gregorw · ‎2012 Jan 06

Hi Sajjad,

you need to install the DDIC plugin which you can find in the SAPlink Plugin List. Don't forget also to activate the classes.

Best regards
Gregor

_IvanFemia_ · ‎2012 Jan 06

Hi,

did you install and activate SapLink plungins for DDic, Class and Interface?

Regards,
Ivan

franois_henrotte · ‎2012 Mar 23

I don't understand why the solution needs 100+ objects. We do it with just one function and some routines (ok we don't generate graphics but we could).

The principle is to generate a file in xml format (respecting the DOM of Excel) and save it with XLS extension. When you open it, Excel will do the conversion automatically.

Really, nothing very complex...

_IvanFemia_ · ‎2012 Mar 23

Hi François,

abap2xlsx gives you a fully featured framework to create Excel.

It gives you the possibility to generate Excel in background task, send it via email, customize graphics and layout using most of the OXML features.

I suggest you to read the several blogs in SCN and CAde Exchange.

Regards,

Ivan

gregorw · ‎2012 Mar 23

Dear François,

especially you should check all the samples coming with it. And in addition to create an Excel File you can also read .XLSX files into ABAP and do further processing.

Best regards
Gregor

Former Member · ‎2012 Mar 27

Magnificent work!!

Former Member · ‎2012 Apr 25

<irony>Why do you need a 250 MB programme to create or edit excel sheets? Why do you need a $500 pc to run it on if it's so simple? Why do you need excel at all if you can maintain your table on a sheet of paper, which is also not very complex.</irony>

_IvanFemia_ · ‎2012 Apr 25

Hi Jan,

I don't understand you reply.

abap2xlsx is only 1MB nugget file and we do not ask anyone any $, it is absolutely open source.

Regards,

Ivan

Former Member · ‎2012 Apr 25

Hi Ivan,

I was referring to Excel itself, and in response to François' "simplification" of the work.

Best wishes,

Jan

gregorw · ‎2012 Apr 25

Dear Jan,

i think you should add an <irony> flag to your comment.

Best regards

Gregor

abhishek_katti · ‎2012 Oct 26

Great development!!

We were struggling for .xlsx in mail attachment and preparation of the file from SAP data, Thanks to abap2xlsx. Now we are able to send the same through background job also.

Hats Off to abap2xlsx & SAPLink team.

Regards,

Abhishek Katti

gregorw · ‎2012 Oct 26

Dear Abhishek,

it would be great if you can share your case at:

Do you have a great DEMO Report for abap2xlsx?

Best regards

Gregor

_IvanFemia_ · ‎2012 Oct 26

Thank Abhishek,

this is a great feedback for all the people that are contributing on these projects.

Hope to see you on www.abap2xlsx.org

Best Regards,

Ivan

kmoore007 · ‎2013 Feb 18

Love it! Nice job!

matt · ‎2013 Feb 28

I installed this at a client site (large multinational), for a high-profile application that scrapes reports from the spool and emails them out as xlsx attachments. The excel conversion was simplicity itself - very nicely written.

The report on the spools is a mixture of sy-vline, +, hyphen - that standard characters - making boxes. I scrape individual boxes, with their columns into cells. Any text not in a box was treated as free text - all in one cell. Where a box has repeated headers the same, it is merged into a single box. Any intervening free text (like page numbers) is ignored.

What was really good was that the ABAP development manager approved the use of the open source code, without hesitation. :smile: I hope she reads this, as I'm very grateful for her pragmatic approach.

Former Member · ‎2013 May 02

Hi Ivan,

great idea, nice job!

Regards

Ivan

jayan_abap · ‎2013 May 07

Looks good.

Great job buddy!!! :smile:

Former Member · ‎2013 May 07

Nice Blog !!

Former Member · ‎2013 May 28

Wow! This is an outstanding project.

Even to think of the idea is brilliant! Thanks.

RieSe · ‎2013 Jul 10

Excel 2010 has an interesting feature: if opened via a web-resource, it tries to save against this web-resource. So i implemented a webdav-handler for provisioning. The user can make changes in the excel. With the reader functionality, it is really very simple to evaluate the changes :cool: .

gregorw · ‎2013 Jul 10

Dear Stefan,

it would be great if you could share this in the project.

Best regards

Gregor

Former Member · ‎2013 Oct 17

Thank you all, Excellent work.

Former Member · ‎2013 Oct 17

Useful Info and new learning...

Former Member · ‎2013 Dec 30

I have got a question. I want to use abap2xlsx for the following scenario:

- A Template Excel should be imported

- Changes should be made

- the excel should be exported again.

During testing I found some minor bugs/issues within abap2xlsx. I had fixed some of them more or less clean but I have also made some quick and dirty stuff.

So I would like to get some of the clean stuff into "standard"

How is that possible? Whom to ask?

Regards

Stefan

_IvanFemia_ · ‎2014 Jan 02

Hi Stefan,

great, I really love to include your enhancements to the public release!

abap2xlsx is now shared on GitHub.

You can create a fork of the project, apply you modifies and submit a pull request.

Let me know if you have any issue!

Ivan

rob_smeets2 · ‎2014 Jan 15

Hi Ivan, I'm facing problems installing Saplink and would like if it's possible (as mentioned but not explained in an installation guide somewhere) to install by STMS instead.

_IvanFemia_ · ‎2014 Jan 15

We decided to no longer provide the Transport Request for abap2xlsx.

What problem are you facing with SAPLink?

Former Member · ‎2014 Jan 15

Is there a way to disable the Excel menu (specifically "File") when displaying an Excel workbook on-line via ABAP2XLSX?

-David

rob_smeets2 · ‎2014 Jan 15

Hi,

I finally managed to get saplink activated after installing the nugg a second time, but I'm discouraged by the problems I have activating abap2xlsx... Is there a specific order in which everything should be activated, or a way to activate everything at once?

It doesn't help that the test system on which I'm installing this is a slow mess :smile:

Thanks for any hints you might have.

_IvanFemia_ · ‎2014 Jan 15

Check the installation guide in the wiki

Home · ivanfemia/abap2xlsx Wiki · GitHub

_IvanFemia_ · ‎2014 Jan 15

Is this possible without a Macro?

Using a Macro you should follow the example in this video

prakashjasti · ‎2014 Jan 15

Very Useful..

Former Member · ‎2014 Jan 21

Thanks Ivan. I will look into this.

abap2xlsx - Generate your professional Excel spreadsheet from ABAP

Do you have a great DEMO Report for abap2xlsx?