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

      241 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

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

      -David

      Author's profile photo Ivan Femia
      Ivan Femia
      Blog Post Author

      Is this possible without a Macro?

      Using a Macro you should follow the example in this video [embed width="425" height="350"]https://www.youtube.com/embed/jg7MRm2uK3I[/embed]

      Author's profile photo Former Member
      Former Member

      Thanks Ivan. I will look into this.

      Author's profile photo Durga Prakash Jasti
      Durga Prakash Jasti

      Very Useful..

      Author's profile photo Edward Pelyavskyy
      Edward Pelyavskyy

      Hi Ivan,

      I have a test program that shows an issue I'm facing. I think it is a bug in abap2xlsx but may be I'm not using it right.

      Where can I submit my test program? Can I open a ticket somewhere?

      Regards,

      Edward

      Author's profile photo Ivan Femia
      Ivan Femia
      Blog Post Author

      EDward,

      you can create a new ticket on GIThub if you think is a bug.

      For discussion about the usage I would suggest to open a discussion in SCN

      Best

      Ivan

      Author's profile photo Stefan Riedel-Seifert
      Stefan Riedel-Seifert

      I am not able, to update the framework: i have installed an older version (?) and want's to overwrite with the newest nugg: ABAP2XLSX_V_7_0.nugg. I choose the option: overwrite originals. i get the message: Start import .... ouch, your pants are on fire..better go get help.

      What's going wrong. I use SAPlink 0.1.4.

      Any suggestions?

      Author's profile photo Gregor Wolf
      Gregor Wolf

      I would suggest that you delete all objects existing and related to ABAP2XLSX and try again. That can normally be done easily in SE80 when you have everything in one package. First delete all classes and reports and then the dictionary objects.

      What you should check alsi is that you have the latest SAPlink version from the build directory in the SVN repo at http://www.saplink.org.

      Author's profile photo Stefan Riedel-Seifert
      Stefan Riedel-Seifert

      But that's not a state-of-the-art upgrade :-(. 0.1.4 is the latest no-beta-version?

      Author's profile photo Gregor Wolf
      Gregor Wolf

      SAPlink is provides only as a daily build. Take the latest and greatest that should have the least bugs.

      Author's profile photo Bruno Esperança
      Bruno Esperança

      Hi Ivan,

      Thank you so much for this.

      However, I am trying hard to find where to get xlsx2abap from without success 🙁

      Could someone help me please?

      Thank you!

      Best,

      Bruno

      Author's profile photo Former Member
      Former Member

      Based on google search, project home is:

      http://ivanfemia.github.io/abap2xlsx/

      On right side you can see download zip option.

      Download and extract that zip file, and locate ABAP2XLSX_V_7_0.nugg file.

      This is the nugget file that you need to install, using latest SAPLink version.

      For installing SAPLink, similar search can be done.

      Author's profile photo Bruno Esperança
      Bruno Esperança

      Hi Manish,

      Thanks for replying. I did find that, but I am looking for the opposite, xlsx2abap, and not abap2xlsx, or is it included in the same package?

      Thank you!

      Best,

      Bruno

      Author's profile photo Uwe Fetzer
      Uwe Fetzer

      Hi Bruno,

      the reader is also included in the package. Check report ZDEMO_EXCEL15.

      Author's profile photo Bruno Esperança
      Bruno Esperança

      Ah great!

      Thanks Uwe.

      Best!

      Bruno

      Author's profile photo Bruno Esperança
      Bruno Esperança

      Hi all,

      I'm trying to run the angry birds example. I've had some trouble with methods ZIF_EXCEL_BOOK_PROTECTION~INITIALIZE not being implemented, but I implemented them empty as I saw someone saying this would fix it, but then I ran into the method ZIF_EXCEL_WRITER~WRITE_FILE not being implemented in class ZCL_EXCEL_WRITER_2007, and implementing this empty "fixes it", but nothing comes from the ZANGRY_BIRDS program.

      Can anyone help me with this?

      Thank you!

      Best,

      Bruno

      Author's profile photo Ivan Femia
      Ivan Femia
      Blog Post Author

      Hi Bruno,

      this is a known issue with SAPLink. To solve the issue import the abap2xlsx nugget again.

      Best

      Ivan

      Author's profile photo Bruno Esperança
      Bruno Esperança

      Perfect, it's working now.

      Thanks so much Ivan, and I'm sorry if this question had been asked, I swear to God I searched before I asked but maybe I didn't look deep enough in the replies... and I'm also guessing that the methods that don't get implemented are random, so that doesn't help 🙁

      Thanks again!

      Best,

      Bruno

      Author's profile photo Ivan Femia
      Ivan Femia
      Blog Post Author

      You're welcome 🙂

      Author's profile photo Karl Pelzer
      Karl Pelzer

      Wow! I just stumbled across this project as it was mentioned on the famous german 'Tricktresor' website.

      It took quite a while to get it up and running but the results are really worth it.

      Although there are plenty of demo programs included, I just wanted to know if there is a plain documentation of all methods and attributes.

      Many thanks to all the contributors!

      Author's profile photo Ivan Femia
      Ivan Femia
      Blog Post Author

      Thank you Karl.

      Unfortunately, there isn't an API doc... There is a plan to add comment in the code in order to use abap2doc project, but it is not yet started.

      So far we tried to create a demo report for most of the features of abap2xlsx in order to give you the ability to "copy" the standard way.

      Any suggestion is welcomed

      Ivan

      Author's profile photo Lars Palitzsch
      Lars Palitzsch

      Hi Ivan,

      When looking for a way to create Excel from SAP (in background) for a customer request I got aware of abap2xls. I was positively surprised about the feature set and how well the samples work. Excellent work.

      Because we consider to use this for a customer project I have two questions:

      It is under Apache License so we could use, distribute and even sell it as it is or modified free of charge? When doing this we have to add copyright  and NOTICE information.

      When installing this at customer systems there could be conflicts between names of objects used in this projects and objects which already exist in the customer system. I was thinking of creating a copy in our namespace to avoid these conflicts. Doing this makes it obviously harder to apply changes in your source and to transfer any changes made in our copy back. Do you have any recommendations or thoughts on this?

      Regards,
      Lars

      Author's profile photo Uwe Fetzer
      Uwe Fetzer

      Hello Lars,

      so we could use, distribute and even sell it as it is or modified free of charge?

      Yes, but the unmodified part of the package must still be under Apache License. The modified part can be under a license of your choice and must be stated in the NOTICE file.

      Regarding the namespace:

      The ABAP2XLSX package contains many (really many!) objects. Renaming these objects would be a huge work and error-prone. I think the better choice would be a test whether there is a conflict (you can list the objects with SAPLINK).

      Author's profile photo Ivan Femia
      Ivan Femia
      Blog Post Author

      Uwe gave you the right explanation on the licence.

      I'm planning to move it to the /CEX/ namespace, but it needs time.

      You can try to see the object in the nugg with SAPlink and also try to import the nugg without the overwrite option in SAPlink.

      So far in several installations that I did no conflicts were raised.

      Author's profile photo Gregor Wolf
      Gregor Wolf

      Unfortunately there are customers that have issues installing a Namespace. so we have to carefully think avout if we want to move. For our Server side component of SAPlink for ABAP in Eclipse I think about removing the /CEX/ Namespace.

      Author's profile photo Ivan Femia
      Ivan Femia
      Blog Post Author

      Thanks Gregor Wolf for your feedback...

      Lars, this is the official statement on the Apache License and Distribution FAQ

      Author's profile photo Uwe Fetzer
      Uwe Fetzer

      Hello Gregor + Ivan,

      it's not the problem with the installation of the namespace, but the maintanance of the objects. If you install the namespace in a customers system, you only have the "Repair" license and not the "Developer" license.

      So: please don't change ABAP2XLS to /CEX/ (and yes Gergor, it would be great, if we can get rid of /CEX/ in SAPlink for ADT).

      Author's profile photo Ivan Femia
      Ivan Femia
      Blog Post Author

      Uwe,

      there are pro and contra on both the side.

      So far I never had the need of /CEX/ namespace or renaming of the objects, but there should be a better way to organize the import of an external project into your ABAP system.

      Author's profile photo Lars Palitzsch
      Lars Palitzsch

      Thanks for the fast reply.

      Regarding the namespace:

      I agree, that renaming is not a good option. I was thinking of modifing SAPLINK to add a prefix to the objects and modify the code accordingly, but this isn't fully thought through.

      Using a namespace for ABAP development is something we do for years without problems (we deliver our stuff with transports). So I would vote for /CEX/.

      Many thanks,

      Lars

      Author's profile photo Ivan Femia
      Ivan Femia
      Blog Post Author

      You can easily change the name of the classes editing the nugg file.

      Author's profile photo Terry Huang
      Terry Huang

      Hi Ivan,

      I have import all the objects into my system by using of ABAP2XLSX_V_7_0.nugg. When I try to run program ZDEMO_EXCEL. The system run a dump. And I run ST22,

      It tells me:

      "An attempt was made in class "ZCL_EXCEL" to call non-implemented method

      "ZIF_EXCEL_BOOK_PROTECTION~INITIALIZE"."

      And there are a lot of same problems.

      And then I click into those methods and reactive this class. And It's ok then. But the size of all generated file is 0.

      I think it could be related with the reimplement of the method are all null. So how can I deal with it?

      eg:

      QQ截图20140417151841.jpgQQ截图20140417151816.jpg

      Best regards,

      Terry

      Author's profile photo Bruno Esperança
      Bruno Esperança

      Reimport everything. It is a known issue with ZSAPLINK.

      Cheers,

      Bruno

      Author's profile photo Terry Huang
      Terry Huang

      Hi Bruno,

      Thanks!  It really works now. 🙂

      Best regards,

      Terry

      Author's profile photo Bruno Esperança
      Bruno Esperança

      Most welcome, I had the same problem 🙂

      Cheers,

      Bruno

      Author's profile photo Ivan Femia
      Ivan Femia
      Blog Post Author

      This is an issue with SAPLink.

      Please refer to the installation guide

      Author's profile photo Former Member
      Former Member

      Great Job Ivan Femia!!

      I'm trying to create a Excel with Background Image on the Excel Sheet. Is there a way to set that? Any input on this is highly appreciated:

      I was looking for a method to set Background Image, but couldnt find. So, tried with a Template file containing Background Image. But, output file didnt contain Background Image.

      I tried following -

         CREATE OBJECT lo_excel.
        CREATE OBJECT lo_excel_reader TYPE ZCL_EXCEL_READER_2007.
        lo_excel = lo_excel_reader->LOAD_FILE( I_FILENAME = 'C:\Users\PPSAP\Desktop\ExcelDraftTemplate.xlsx'
                                                                I_FROM_APPLSERVER = ' ' ).

        CREATE OBJECT lo_excel_writer TYPE zcl_excel_writer_2007.
        lv_file = lo_excel_writer->write_file( lo_excel ).

        lt_file_tab = cl_bcs_convert=>xstring_to_solix( iv_xstring  = lv_file ).
        lv_bytecount = xstrlen( lv_file ).

        cl_gui_frontend_services=>gui_download( EXPORTING bin_filesize = lv_bytecount
                                                          filename     = lv_full_path
                                                          filetype     = 'BIN'
                                                 CHANGING data_tab     = lt_file_tab ).

      " Newly created file does not contain Background Image and below is what ExcelDraftTemplate.xlsx conbtains.
      TemplateFilewithBackgroundImage.jpg

      Thank you!!

      PP.SAP

      Author's profile photo Ivan Femia
      Ivan Femia
      Blog Post Author

      Hi

      we support images, see for example zdemo_excel16 and zdemo_excel38.

      Excel watermark are not yet implemented.

      Best

      Ivan

      Author's profile photo Former Member
      Former Member

      Hi Ivan

      Thank you so much for the quick response.

      Tool is absolutely great! It has majority of the functionalities that we normally use.

      Looking forward to additions of such features!

      thank you

      PP

      Author's profile photo Former Member
      Former Member

      I was trying to get it from .nug file, I am not able to do so as my system is not able to handle such a big internal table and giving short dump.

      Can any one please provide me the code.

      Also if any one is having Idea if we can send the .XLSX attahment in the mail as written in note 1459896 but that is also not working.

      Please can anyone tell me.

      Thanks

      Willi

      Author's profile photo Gregor Wolf
      Gregor Wolf

      Dear Willi,

      if you encounter a SAPlink error then please file a detailed Bug description at SAPlink Project. But please try to talk before with your basis admin if some parameters can be adjusted.

      For the other problem I think you should check out the sample reports coming with abap2xlsx. They also have the send as attachment functionality.

      Best regards

      Gregor

      Author's profile photo Former Member
      Former Member

      Hi Gregor,

      Is it possible for someone to just post the code for sending mail as XLSX, I am not able to run nugg file as it is too big and my system is giving dump.

      Please if it is possible for someone please do I need it ASAP wery much required.

      Thanks

      Author's profile photo Ivan Femia
      Ivan Femia
      Blog Post Author

      HI Will,

      have you installed abap2xlsx in your system?

      If so, the demo reports are already there.

      Ivan

      Author's profile photo Former Member
      Former Member

      HI Ivan,

      How to install abap2xlsx , I have not done so can you please guide me.

      Thanks

      Willi

      Author's profile photo Gregor Wolf
      Gregor Wolf

      Please  read the Installation Guide linked from the abap2xlsx homepage

      Author's profile photo Former Member
      Former Member

      I have gone through the Installation guide but as the nugget file is big it is giving me dump while importing the file.

      Author's profile photo Gregor Wolf
      Gregor Wolf

      as I sad before: ask your basis admin to fix the limit of split the file into several ones. It is just XML.

      Author's profile photo Ian MacLaren
      Ian MacLaren

      Hi Ivan

      just to add to the comments already made that this is superb stuff ... really easy to use, lots of functions and feature.  Just what I needed and I have that warm "I've re-used code" glow  🙂

      nice one and also thanks to the SAPLink team

      regards

      Ian

      Author's profile photo Former Member
      Former Member

      I am not able to open the file in SOST it shows below error.


      error while opening.png


      try.

          lo_send_request = cl_bcs=>create_persistent( ).

      * -------- create and set document with attachment --------------- *

      * create document from internal table with text

        append 'Hello world!' to lt_doc_text.

            CONCATENATE text-002    lc_tab

                        text-003    lc_tab

                        text-004    lc_crlf

                       INTO l_string .

                 TRY.

                     cl_bcs_convert=>string_to_soli(

                       EXPORTING

                         iv_string   = l_string

                       RECEIVING

                         et_soli  = lit_soli_tab

                          ).

                       CALL METHOD cl_bcs_convert=>string_to_xstring

                         EXPORTING

                           iv_string     = l_string

                           iv_codepage   = '4103'

                           iv_add_bom    = 'X'

                         receiving

                           ev_xstring    = lv_string.

                   CATCH cx_bcs.

                     MESSAGE e445(so).

                 ENDTRY.

         lo_document = cl_document_bcs=>create_document( i_type = 'RAW'

                                                         i_text = lt_doc_text

      *                                                  i_length = '12'

                                                         i_subject = 'Main Document' ).

      append 'This is an attachment' to lt_att_text.

      * four character file extension '.text' is set

        lv_filename = 'AttachmentFilename.xlsx'.

        concatenate '&SO_FILENAME=' lv_filename into lv_text_line.

        append lv_text_line to lt_att_head.

      CALL METHOD cl_bcs_convert=>xstring_to_solix

         EXPORTING

           iv_xstring = lv_string

         receiving

           et_solix   = binary_content

           .

          l_legth = xstrlen( lv_string ).

          lv_size = l_legth.

      lo_document->add_attachment( exporting i_attachment_type = 'XLS'

                                        i_attachment_subject = lv_filename

                                        i_attachment_size = lv_size

                                        i_att_content_hex    = binary_content

                                        i_attachment_header = lt_att_head ).

      *     add document object to send request

               lo_send_request->set_document( lo_document ).

      *     create recipient object

                 g_recipient = cl_cam_address_bcs=>create_internet_address( 'test@test.com' ).

                 TRY.

                     CALL METHOD lo_send_request->add_recipient

                       EXPORTING

                         i_recipient = g_recipient

                         i_express   = 'X'.

                   CATCH cx_send_req_bcs .

                 ENDTRY.

      *     ---------- send document ---------------------------------------

               g_sent_to_all = lo_send_request->send( i_with_error_screen = 'X' ).

               COMMIT WORK.


      I also have posted it as question.


      Sending .xlsx file from ABAP


      I believe expert would help me.


      Thanks

      Willi


      Author's profile photo Gregor Wolf
      Gregor Wolf

      Dear Willi,

      as said before: Install abap2xlsx and with all the examples you will have a working example how to correctly add an .xlsx file to an email.

      Best regards

      Gregor

      Author's profile photo Former Member
      Former Member

      Hi Gregor,

      I am not able to activate all the object at once it showing some type of error or other, Can you help.

      Thanks

      WIlli

      Author's profile photo Ian MacLaren
      Ian MacLaren

      Hi All

      Perhaps not the correct place to ask this but I am using ABAP2XLSX to create XLSX files to send via email using BCS and this works perfectly

      However the attachment type parameter in cl_document_bcs->add_attachment which feeds through to FM SO_ATTACHMENT_INSERT_API1 allows only a 3 char field with domain SO_OBJ_TP so the attachment arrives as <myfile.xls> not <myfile.xlsx> 😥

      has anyone any suggestions?  Repair SAP to CHAR(4) ?

      regards

      Ian

      Author's profile photo Gregor Wolf
      Gregor Wolf
      Author's profile photo Ian MacLaren
      Ian MacLaren

      sweet, this trick works perfectly ... many thanks !

      🙂

      Author's profile photo Former Member
      Former Member

      Hi Ian,

      I agree with you, I went through the same case and used note 1459896 as suggested by Gregor again I am not able to open it in SOST.

      Please can you try what is written in this note and see if it works for you.

      Thanks

      Willi

      Author's profile photo Ian MacLaren
      Ian MacLaren

      Hi Willi

      this works for me.  NB in SOST I can see the attachment just as a name "ATT" w/o extension and a type of XLS but the email shows up correctly in my inbox as "ATT.XLSX" and Excel sees this correctly as an XML CAB file. 

      Seriously cool software ...

      regards

      Ian

      Author's profile photo Former Member
      Former Member

      Hi Ian,

      Gr8 that this trick is working for someone, can you please post your code as I used the same trick but I am not able to open it in SOST and my BASIS doesn't allow me to send mails to external system from development environment.

      You help is highly appreciated.

      Thanks

      Willi

      Author's profile photo Ian MacLaren
      Ian MacLaren

      Hi Willi, this my code, its not complicated ... the form generate_xlsx fills in cells with format/style for my application in the returned class instance lcl_excel.  The key bit the note details is to fill in the filename REQUEST.XLSX in class parameter i_attachment_header - its hardcoded at present as we are still prototyping ... hope this helps

      /wp-content/uploads/2014/07/code_504439.png

      Author's profile photo Former Member
      Former Member

      may I ask one more question wree you able to open it in SOST.

      Also one more question I am not able to actiavate all the objects imported from .nugg file can you please help with some trick or if I am missing something.

      Thanks

      Author's profile photo Ian MacLaren
      Ian MacLaren

      yes, I can open in SOST.

      The NUGG file objects have some errors initially just because the objects are a bit "chicken" and "egg", referring to objects not yet activated.  This is to be expected, you get this in any development of course.

      activate all objects ignoring errors and all will be well

      Author's profile photo Former Member
      Former Member

      Yes its gr8 it works .

      I have a question what exactly is the key to send in XLSX format, for sure it is not just changing the header table like

      lv_filename = 'AttachmentFilename.xlsx'.

      CONCATENATE '&SO_FILENAME=' lv_filename INTO lv_text_line.

      APPEND lv_text_line TO lt_att_head.


      1. is it changing the content into XML format
      2. can without using so many classes we can convert an internal table to XML and then calculate its value and send.

      I believe someone will provide some insight.

      Thanks

      Willi

      Author's profile photo Ian MacLaren
      Ian MacLaren

      no, this has no effect on the actual file contents ... if you want a true XLSX then use Ivan's class library.  this code fragment means that the file attachment gets sent with the correct name.  without this, the file is a cab zip of xml but labelled myfile.xls and Excel warns you the file extension does not match the file and may be corrupt.  still works but not great user experience ...

      Author's profile photo Former Member
      Former Member

      Hi Ian,

      I understand that  below code segment is for sending the xlsx attachment type but still

      if  I do not use the library provided by Ivan then again it doesnot open that means creating a CAB XML file from internal table is also the key when you want to send XLSX file.

      correct me if I am wrong.

      lv_filename = 'AttachmentFilename.xlsx'.

      CONCATENATE '&SO_FILENAME=' lv_filename INTO lv_text_line.

      APPEND lv_text_line TO lt_att_head.

      Thanks

      Willi

      Author's profile photo Gregor Wolf
      Gregor Wolf

      Dear Markus,

      please don't crosspost issues. You've raised the issue already at INSTALLING: Could not create WB object from OCN (type),  (name), ZCL_EXCEL (encl.) · Issue #335 · ivanfemia/abap2xlsx · …

      Best regards

      Gregor

      Author's profile photo Markus Feifel
      Markus Feifel

      Hi Gregor Wolf,

      sorry for the crosspost. I deleted this post here.

      Do you have any suggestions to solve this problem or where this problem comes from?

      Best regards,

      Markus

      Author's profile photo Former Member
      Former Member

      Hello,

      The provided codes are completely free? Can I implement it in our customer system?

      Thank you in advance,

      Tom

      Author's profile photo Gregor Wolf
      Gregor Wolf

      Dear Tom,

      the provided code is licensed under Apache 2.0 (https://github.com/ivanfemia/abap2xlsx/blob/master/LICENSE). So if your company allows the use of this license, then nothing should hinder you to use abap2xlsx in your customer system.

      Best regards

      Gregor

      Author's profile photo Simone Milesi
      Simone Milesi

      Hi @tom dupond

      Yes, you can implement without any restriction in your customer system 🙂

      Author's profile photo Former Member
      Former Member

      Ok thank you.

      With this tool, is it possible to open an xlsx file (template) and change it with data in the system (replace variable by values: single variable and table content)?

      Author's profile photo Gregor Wolf
      Gregor Wolf

      Yes, please install it and have a look at the examples.

      Author's profile photo Former Member
      Former Member

      I imported the transport and did not found an example for my requirement (open an xml file and change...)

      My template:

      /wp-content/uploads/2014/10/2014_10_29_120320_573436.jpg

      Logo in the Header and page number in the footer

      /wp-content/uploads/2014/10/2014_10_29_120635_573452.jpg

      I want to read this template and change the data in red (customer...) and also add lines with data (DE, FR lines..)

      Is it possible with your tool. If yes which demo programme can I take?

      Thank you in advance

      Author's profile photo Gregor Wolf
      Gregor Wolf

      Please search the Reports ZDEMO_EXCEL* for read. But I think for this simple form you should generate it directly in ABAP2XLSX. Images are supported.

      Author's profile photo Olivier Muff
      Olivier Muff

      Yes, I did this recently and find that generating a new xlsx with the data in it is the most strait forward.  If you really need to fill fields in a template, you either have to know the cell locations that you will fill, or you have to loop through all the cells looking for a particular pattern.  Use worksheet->get_cell method and if it contains the pattern then you can use worksheet->set_cell method to fill it.

      Author's profile photo Former Member
      Former Member

      I agree with you to generate the document with ABAP2XLSX but I need a header with a logo and a footer with an incremented page number(will be in all pages).

      Is this possible with the code to set the header or the footer? With worksheet->set_cell ?

      Author's profile photo Former Member
      Former Member

      I found ZDEMO_EXCEL4 which contain code for header and footer bur I got an error 🙁

      Line 3: "Field "C_SELECTED" is unknown. It is neither in one of the specified"

      Line 65 "Method "SET_HEADER_FOOTER" is unknown or PROTECTED or PRIVATE."

      ...

      Why these objects does not exist? Do I install additional transport?

      Author's profile photo Raymond Giuseppi
      Raymond Giuseppi

      Did you activate every object (here interface ZIF_EXCEL_SHEET_PROPERTIES and class ZCL_EXCEL_SHEET_SETUP) ?

      Author's profile photo Former Member
      Former Member

      Yes there are active. And I imported the objects by transport, that means all objects were already active...

      Code in report ZDEMO_EXCEL4:

      lo_worksheet->sheet_setup->set_header_footer( ip_odd_header  = ls_header

                                                      ip_odd_footer  = ls_footer ). 

      I did not found "sheet_setup"

      Author's profile photo Gregor Wolf
      Gregor Wolf

      Please install the latest version of abap2xlsx using SAPlink. The version you can get by transport is not up-to-date.

      Author's profile photo Ivan Femia
      Ivan Femia
      Blog Post Author

      I'll update the nugg and transport ASAP... I was in vacation for a while 🙂

      Author's profile photo Former Member
      Former Member

      I don't have the autorization to instal SAPlink in the customer system 🙁 I can only work with the transports.

      Author's profile photo Bruno Esperança
      Bruno Esperança

      Do you have access to LSMW? Or debugging? If you have access to any of these things, you have SAP_ALL 😉

      Author's profile photo Former Member
      Former Member

      No, I don't have the authorization  😥

      The best way is to have a transport up to date but still no response from Ivan.

      Author's profile photo Bruno Esperança
      Bruno Esperança

      I'm not sure if I understand Tom...

      You must have authorization somewhere! "Install" SAPLink where you have authorization, "install" ABAP2XLSX, there, put it into a transport, no?

      Regards,

      Bruno

      Author's profile photo Former Member
      Former Member

      Hello Bruno,

      I have the debugger authorization, maybe with this one I can skip the auth. check...

      How can I install SAPlink? I found only the .nugg files... What is the exact procedure?

      Best Regards,

      Tom

      Author's profile photo Bruno Esperança
      Bruno Esperança

      Hi Tom,

      Google is your friend!

      I would say you need to download these files:

      https://www.assembla.com/code/saplink/subversion/nodes/389/trunk/build

      This one for the plugins:

      build | SVN | Assembla

      And follow the instructions here:

      SAPlink User Documentation - ABAP Development - SCN Wiki

      The only way I can help you more, is if I do it for you 🙂

      Regards,

      Bruno

      Author's profile photo Former Member
      Former Member

      Dear Ivan,

      Please let me know when the transport is up to date. The transport will update the old objects? Because I saw that another guy installed your package in the past and when I tried to install the new one there were some error 🙁

      /wp-content/uploads/2014/10/2014_10_30_085016_574223.jpg

      I hope that the new transport will fix these issues.

      Thank you very much for you help.

      Author's profile photo Former Member
      Former Member

      Dear Ivan,

      Do you know (more less) when you can deliver the latest transport version?

      Thanks in advance,

      Tom

      Author's profile photo Former Member
      Former Member

      Hi Bruno,

      Thank you very much. It works now 🙂 All objects have been updated with SAPLink.

      One last question, I tested ZDEMO_EXCEL4 in order to add a header and a footer with page number and this work perfectly but I need also to add a picture in the right part of the header. And I did not found a solution to do this 😐 Do you have an idea?

      Best Regards,

      Tom

      Author's profile photo Bruno Esperança
      Bruno Esperança

      Hi again Tom,

      I'm glad it worked for you now.

      I haven't used this much. I don't know how to add images to excel. Have you looked well enough in the examples? My guess is that... if it isn't in an example, it is not possible.

      But hopefully someone else will be able to answer that for ya.

      Cheers,

      Bruno

      Author's profile photo Former Member
      Former Member

      Dear Team,

      Does anybody have an idea about how to add a picture in the header with the code?

      Thanks in advance

      Author's profile photo Former Member
      Former Member

      Hi Bruno,

      Yes, I checked the examples and I know how to add a picture in the excel but there is no way to add it in the header 🙁

      I also create an excel template with a header and footer and used the report to read the excel and it does not read the header with the picture...

      Now, I'm not sure whether there is a solution.

      Best Regards,

      Tom

      Author's profile photo Gregor Wolf
      Gregor Wolf

      Dear Tom,

      so if it's possible to have an image maintained in the header directly in excel, then it seems to be a missing functionality of abap2xlsx right now. You can either start creating this functionality yourself or hire one of the contributors to implement it for you.

      Best regards

      Gregor

      Author's profile photo Former Member
      Former Member

      😐 This is realy a bad news because as far as I know we have to generate a vml file and this is something very complicate 🙁

      Author's profile photo Former Member
      Former Member

      Is there another way to display a picture in each page in the excel file?

      Author's profile photo Ivan Femia
      Ivan Femia
      Blog Post Author

      Hi Tom,

      please open a discussion on SCN with the tag abap2xlsx and [abap2xlsx] in the subject.

      Thank you

      Ivan

      Author's profile photo Kenneth Moore
      Kenneth Moore

      Hello Ivan. I love your product!  But I ran into the issue of when I published a transaction as an internet service (SICF - ITS), the option to 'Direct Display' the spreadsheet does not work.  Excel never starts.  Any ideas?

      Author's profile photo Gregor Wolf
      Gregor Wolf

      Dear Kenneth,

      I wouldn't think that this is a abap2xlsx problem. But please post in SCN with the tag abap2xlsx and [abap2xlsx] in the subject.

      Best regards

      Gregor

      Author's profile photo Kenneth Moore
      Kenneth Moore

      Thanks, Gregor.  I created a post.

      Author's profile photo Bruno Esperança
      Bruno Esperança

      Hi guys,

      I've created a fancy little file reader that uses your xlsx reader here

      Basically, I use your capabilities of reading the xlsx file into a char like structure, and then the reader will automatically take care of the conversion from external format to the internal SAP format.

      Feel free to take a look, any comments or suggestions are welcome.

      Best,

      Bruno

      Author's profile photo Ivan Femia
      Ivan Femia
      Blog Post Author

      Hi Bruno,

      I will install your solution in my abap system...

      It seems interesting.

      Thank you

      Ivan

      Author's profile photo Bruno Esperança
      Bruno Esperança

      That's an honor Ivan 🙂

      Best regards,

      Bruno

      Author's profile photo Former Member
      Former Member

      Hi Ivan,

      I am new to ABAP2XLSX. Just want to check if this can support both 32bit and 64bit MS Excel?

      (Sorry, if this is wrong place to post the question.)

      Thank you.

      Author's profile photo Bruno Esperança
      Bruno Esperança

      Hi Suneel,

      As far as I know, it doesn't make a difference, the XLSX file does not depend on the Office version.

      Regards,

      Bruno

      Author's profile photo Ivan Femia
      Ivan Femia
      Blog Post Author

      As Bruno stated, Excel version does not affect generated xlsx.

      Author's profile photo Former Member
      Former Member

      Thank you both.

      Author's profile photo Ian MacLaren
      Ian MacLaren

      Hi Ivan

      I use your class to create XLSX for outbound email and to unpack XLSX from inbound email and it works beautifully

      I also store the XLSX raw data in a table and the user has a transaction to display the XLSX in place within SAPGUI.  This works and the spreadsheet displays the correct data with all the expected formats etc. ... all good.

      The only problem is the user gets an annoying warning from Excel that the workbook is corrupt - this is not true and he/she needs to just ignore this.

      Any thoughts?  Currently the users ignore this and its no big deal but my system is not perfect 🙂

      regards

      Ian

      This is the error message ...

      /wp-content/uploads/2015/02/pic1_639216.png

      If he/she proceeds, Excel reports an error with the spreadsheet /wp-content/uploads/2015/02/pic2_639217.png

      the log of the error from Excel is not useful  - there is no error

      /wp-content/uploads/2015/02/pic3_639230.png

      my code is as follows where LT_RAWDATA holds the raw XLSX info.

      /wp-content/uploads/2015/02/pic4_639231.png

      Author's profile photo Former Member
      Former Member

      Ian,

      It could be graphic characters or unusual formatting.

      I am storing the XLSX template in an SAP table in Xstring
      format. I had the same “unreadable content” message.

      I had to remove all range names, Freeze pane settings, and
      especially external document links.

      If this does not fix the issue, try this:

      1. Open the XLSX template
      2. In Excel, File -> Save As, Pick ODS format
      3. Close the XLSX file
      4. Open the ODS file
      5. In Excel, File -> Save As, Pick XLSX format
      6. Fix sheet names (see below comment)
      7. Load the revised XLSX file into the SAP table

        

      *** Warning: converting to ODS (Open DataSource) format replaces
      the SPACE character in sheet names with the UNDERSCORE character.

      -David

      Author's profile photo Ian MacLaren
      Ian MacLaren

      Hi David

      possibly but the XLSX is generated in SAP, emailed to the user who fills in some text and sends this back to SAP where the entries are processed.  Some of the cells are formatted in terms of font size, basic formula for syntax check ( i.e. ISNUMBER ), colour and protection but otherwise I can't think of anything "toxic" in the spreadsheet.  Excel complains but it does display and all the attributes and content are displayed correctly.

      regards

      Ian

      Author's profile photo Former Member
      Former Member

      Ian,

      Are you using set_cell or set_cell_style? I had the "unreadable content" message once when I placed text in a cell that was formatted as a number.  I changed all cell formats to text and the unreadable content message no longer displayed.

      -David

      Author's profile photo Ivan Femia
      Ivan Femia
      Blog Post Author

      Ian,

      I would suggest to open an issue on GitHub and provide the details above and an example XLSX file.

      Usually this error comes from a wrong xml file in the structure.

      Provide as many datails as possible in order to analyze the issue.

      Ivan

      Author's profile photo Ian MacLaren
      Ian MacLaren

      Hi Ivan

      I'll do as you suggest ... apart from this the system is working flawlessly i.e. the outbound XLSX works correctly when emailed from SAP and when the form is filled in, I can process the data entries made by the users

      regards

      Ian

      Author's profile photo Former Member
      Former Member

      Dear Team,

      I used the email send option with the demo reports...I checked in SOST and the mail and attachement are ok, I download or opened the attachement and evething is ok, the attachement can be opened as an 'xlsx" file.

      But when I process the line is SOST and receive the main in my Gmail inbox, the attachement has not the correct extention, normally the extention must be "filename.xlsx" but in the mail it is "filename.xlsx.XLS".

      Ans when I open this file there is a warning....

      Do you have a solution to avoid this issue?

      Thank you in advance,

      Tom

      Author's profile photo Karl Pelzer
      Karl Pelzer

      Hi Tom,

      it works for me. In the method 'send_email' you'll find the following comments:

      * Add attachment to document
      * since the new excelfiles have an 4-character extension .xlsx but the attachment-type only holds 3 charactes .xls,
      * we have to specify the real filename via attachment header
      * Use attachment_type xls to have SAP display attachment with the excel-icon

      So I defined a constant in the DATA-Section :

      gc_save_file_name type string value 'ABRV_Kandis.XLSX'.

      That's it. Hope it works for you too.

      Regards

      Karl

      Author's profile photo Former Member
      Former Member

      Hello Karl,

      Yes, I know and I have the same code => I used the demo report program.

      CONSTANTS: gc_save_file_name TYPE string VALUE '03_iTab.xlsx'.


               attachment_subject  = gc_save_file_name.
               CONCATENATE '&SO_FILENAME=' attachment_subject INTO wa_attachment_header.
               APPEND wa_attachment_header TO t_attachment_header.


      But in the inbox of Latus or Gmail, the attached file name is "03_iTab.xlsx.XLS"

      😕


      FYI: I processed manually the line in SOST


      Best Regards,

      Tom

      Author's profile photo Karl Pelzer
      Karl Pelzer

      Hi Tom,

      that's strange. I am not using the demo report but it's just copy and paste of the routines. So far, my code looks the same.

      I also use manual processing in SOST. It's connected to an excahnge server and I receive the mail by Outlook. The filename is exactly 'ABRV_Kandis.XLSX' as defined.

      The next code lines in my program are:

      sood_bytecount = bytecount" next method expects sood_bytecount instead of any positive integer *sigh*
      cl_document->add_attachmenti_attachment_type    = 'XLS' "#EC NOTEXT
                                                               i_attachment_subject = attachment_subject

                                                               i_attachment_size    = sood_bytecount
                                                              i_att_content_hex    = t_rawdata
                                                              i_attachment_header  = t_attachment_header ).

      Regards

      Karl

      Author's profile photo Former Member
      Former Member

      Hi Karl,

      I have the same code. Is there any configuration to do in order to avoid this additional extention (.XLS)?

      I tried to create the attachement with "i_attachment_type    = ' ' "

      and the file was created with "03_iTab.xlsx.EXT"

      Very strange !


      Best Regards,

      Tom

      Author's profile photo Uwe Fetzer
      Uwe Fetzer

      Tom and Karl: can you compare your Basis Releases? Maybe something changed in one of the last service packs.

      Author's profile photo Karl Pelzer
      Karl Pelzer

      Hi Tom,

      as you can see in my example above, I am using i_attachment_type ='XLS'

      Please note it ist not '.XLS' with a dot.

      Maybe you can go into transaction SOST, mark the line with your mail and then press the button with the glasses. What is the attachment named like?

      @ Uwe

      We are using component SAP_BASIS on release 702 with SP-Level 14.

      Greets

      Karl

      Author's profile photo Former Member
      Former Member

      Hi Karl,

      SAP_BASIS on release 732 with SP4.

      I use i_attachment_type ='XLS' and not with a dot.

      In SOST evething is ok. When I download via SOST the file is xlsx but the only issue is when I receive via mail in the Lotus or Gmail inbox it is xlsx.XLS

      Please see screenshot from SOST and mail inbox

      /wp-content/uploads/2015/02/2015_02_12_163947_644126.jpg/wp-content/uploads/2015/02/2015_02_12_143309_644034.jpg

      Best Regards

      Tom



      Author's profile photo Ivan Femia
      Ivan Femia
      Blog Post Author

      PLEASE,

      This will help us to provide you and the community a better support.

      Thanks,

      Ivan

      Author's profile photo Former Member
      Former Member

      Hello!

      First of all I want to thank you for this great project, well done!

      I have question about Charts - I was studying program ZDEMO_EXCEL39, i know how to make Excel file with BarChart, PieChart, LineChart, but I haven't figurued yet how to make Chart like this:

      /wp-content/uploads/2015/04/chart_683827.png

      Thank in advance for any help/guide,

      Jakub

      Author's profile photo Ivan Femia
      Ivan Femia
      Blog Post Author

      Hi Jakub,

      thank you for your feedback.

      Please open a new discussion in SCN space Open Source, someone of our team will help you.

      Ivan

      Author's profile photo Former Member
      Former Member

      Hello everyone,

      is it possible to create an excel file with the Method bind-table but don't get any filters already set ???

      Author's profile photo Ivan Femia
      Ivan Femia
      Blog Post Author

      Johannes please do not cross post, answer on your discussion abap2xlsx -> bind_table without filters

      Ivan

      Author's profile photo Former Member
      Former Member

      HI Ivan,

      i have a requirment like to write data to XLSX but unfortunatly when i tried to see the code link which you have mentioned its saying "UNAUTHORIZED" even though i am logged in.

      Could please help on this , i need this as reference.

      Thank you.

      Author's profile photo Ivan Femia
      Ivan Femia

      Code is shared on GitHub and it is open source.

      http://www.abap2xlsx.org

      You should not have any authorization issue on SCN, the post is in the open source section.

      Please check with SCN team for SCN auth issue.

      Ivan

      Author's profile photo Former Member
      Former Member

      Thank you Ivan 🙂

      Author's profile photo Evgeniy Astafev
      Evgeniy Astafev

      Hi Ivan.

      Can you give any hint or solution about inserting rows into worksheet like in excel? I'm trying to implement it but I found that I must rebuild all formulas under the inserted row.

      Sample worksheet:

      A1 = 5, A2 = 8, A3 = SUMM(A1:A2).

      I want to insert new row between A1 and A2 and get A1 = 5, A2 = empty, A3 = 8, A4 = SUM(A1:A3).

      The worst thing for me right now is the formula.

      Author's profile photo Gregor Wolf
      Gregor Wolf

      Dear Evgeniy,

      you're already programming in ABAP so you can create the formula also programmatically.

      Best regards

      Gregor

      Author's profile photo Ivan Femia
      Ivan Femia

      Evgeniy,

      please open a discussion in order to keep this thread clean...

      And your answer is in the demo provided within the nugget

      Ivan

      Author's profile photo Ramesh Mahankali
      Ramesh Mahankali

      Hi Ivan,

      I have a scenario where, I need to populate data into pre formatted excel template in background mode. Do you know if OLE2 supports excel sheet generation in background mode?

      Also the excel template has macro's build into it and has an file extension of .XLSM.

      Thanks in Advance,

      Ramesh

      PS: I tried to post this question in SAP open source, but could not find an option to open a new discussion.

      Author's profile photo Sandra Rossi
      Sandra Rossi

      OLE requires a connection from ABAP server to a server with OLE application installed (note that all Microsoft Office applications (Excel, Word...) are OLE compatible). In dialog, all OLE applications from the frontend computer can be accessed. In background, the frontend computers cannot be accessed, or it's difficult/rather unusual to do it. So we always prefer using abap2xlsx to generate directly the Excel file, and it's much faster too.

      Author's profile photo Ramesh Mahankali
      Ramesh Mahankali

      Hi Sandra,

      Thank you for your response. I would like to specifically know the ABAP2XLSX capabilities around reading a template(with macros) and populating data with in multiple sheets. I tried using class I_OI_SPREADSHEET, but it works only in foreground.

      Appreciate your response.

      Ramesh

      Author's profile photo Sandra Rossi
      Sandra Rossi

      DOI is based on an OLE client too. Sorry, I don't know for ABAP2XLSX.

      Author's profile photo Ramesh Mahankali
      Ramesh Mahankali

      Thank you Sandra for your response.

      Author's profile photo Sandra Rossi
      Sandra Rossi

      The abap2xlsx tool is supplied with ZDEMO_EXCEL29 which opens any existing XLSM template, and saves it into another one, and the macros are kept successfully.

      Author's profile photo Liang WS
      Liang WS

      Anybody tried to hide a sheet before? Assuming you have 3 sheets, but 3rd sheet is to be hidden. I don't seem to find a functionality to hide a sheet.

      Kindly share if you know the way to do it. Thanks in advance.

      Author's profile photo Mathias Silver
      Mathias Silver

      Hello everybody,

      currently I am trying to process an xlsm template from the SAP Directory.
      My problem is that the commandbuttons (Active-X form controls) from the xlsm-templage will no longer appear in the exported file. I am using the generel functions from the report ZDEMO_EXCEL29.

      Is there a functionality for copying all buttons from the template?

      Thanks in advance,

      Mathias

       

       

      Author's profile photo Rajesh Rajgor
      Rajesh Rajgor

      Hi ,

      Activate the following things after installation,

      ZEXCEL_S_DRAWINGS structure

      ZEXCEL_S_WORKSHEET_HEAD_FOOT structure

      ZEXCEL_T_DRAWINGS TableType

      ZCL_EXCEL_DRAWINGS class

      Author's profile photo Md RP
      Md RP

      Hola, tengo un problema al exportar a excel.

      utilizo  CALL METHOD cl_gui_frontend_services=>gui_download

      pero cuando me abre el excel, quiero que el tipo del formato sea tipo rango.

      Sin que el usuario tenga que pulsar:  Diseño-> convertir a rango.

       

      Esto es posible?

      muchas gracias

      Author's profile photo Pavel Astashonok
      Pavel Astashonok

      Try SET_AREA method, check the ZDEMO_EXCEL13 example

      Author's profile photo Md RP
      Md RP

      Hola... gracias por tu rápida respuesta.

      pero estoy mirando de nuevo el ZDEMO_EXCEL13 y no veo ningún method SET_AREA

      muchas gracias

      Author's profile photo Pavel Astashonok
      Pavel Astashonok

      https://github.com/sapmentors/abap2xlsx/blob/a9be231d99291760edf9164aad1be31c4b9f6def/src/demos/zdemo_excel13.prog.abap#L81

      Author's profile photo Md RP
      Md RP

      Muchas gracias!

      pero me imagino que nuestro sistema no esta actualizado porque en nuestra clase no tenemos el método SET_AREA

      clase%20ZCL_EXCEL_WORKSHEET

      por eso no lo encontraba, de echo en el programa ZDEMO_EXCEL13 de nuestro sistema no viene ese codigo

       

      Muchas Gracias

      Author's profile photo Adrian Polvo
      Adrian Polvo

      Hi.

      I am trying to install it from abapGit repository in ECC but I got below errors, it seems that the structures are not being imported, all domains and data elements have been imported but the structures do not, therefore, type tables are not being installed as well.

      Has anyone has this error before?

      Thanks in advance.

      Errors

      Errors

      Author's profile photo Laura Quinteros
      Laura Quinteros

      I get the same errors as the previous poster, Adrian Polvo, when trying to install it in an ECC system.

      Does anyone know how to solve this issue?