Skip to Content

abap2xlsx – Generate your professional Excel spreadsheet from ABAP

July 12, 2010 | 36,693 Views |
 abapcode exchangecommunity projectsOpen Sourcesap developer networksapmentor
Follow

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

To report this post you need to login first.

231 Comments

You must be Logged on to comment or reply to a post.

  3. Former Member

    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

    (0) 
  4. 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?

    (0) 
    1. 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.

      (0) 
  5. 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

    (0) 
      1. 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

        (0) 
  6. 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

    (0) 
      1. 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

        (0) 
  7. Former Member

    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!

    (0) 
    1. Ivan Femia 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

      (0) 
  8. 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

    (0) 
    1. 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).

      (0) 
      1. Ivan Femia 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.

        (0) 
        1. 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.

          (0) 
          2. 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).

            (0) 
            1. Ivan Femia 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.

              (0) 
  9. 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

    (0) 
  10. Former Member

    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

    (0) 
  11. 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

    (0) 
    1. Ivan Femia Post author

      Hi

      we support images, see for example zdemo_excel16 and zdemo_excel38.

      Excel watermark are not yet implemented.

      Best

      Ivan

      (0) 
      1. 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

        (0) 
  12. 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

    (0) 
    1. 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

      (0) 
      1. 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

        (0) 
  13. 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

    (0) 
  14. 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


    (0) 
    1. 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

      (0) 
  15. 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

    (0) 
    2. 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

      (0) 
      1. 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

        (0) 
        1. 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

          (0) 
          1. 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

            (0) 
            1. 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

              (0) 
              1. 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

                (0) 
                1. 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

                  (0) 
                  1. 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 …

                    (0) 
                    1. 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

                      (0) 
  17. Former Member

    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

    (0) 
  19. 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)?

    (0) 
  20. 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

    (0) 
    1. 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.

      (0) 
  21. Former Member

    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.

    (0) 
  22. 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 ?

    (0) 
  23. 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?

    (0) 
  24. 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”

    (0) 
            1. 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

              (0) 
              1. 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

                (0) 
  25. 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.

    (0) 
  27. 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

    (0) 
    1. 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

      (0) 
  29. 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

    (0) 
    1. 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

      (0) 
  32. 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?

    (0) 
    1. 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

      (0) 
  33. 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

    (0) 
  34. 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.

    (0) 
  36. 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

    (0) 
    1. 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

      (0) 
      1. 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

        (0) 
        1. 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

          (0) 
    2. Ivan Femia 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

      (0) 
      1. 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

        (0) 
  37. 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

    (0) 
    1. Former Member

      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

      (0) 
      1. 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

        (0) 
        1. Former Member

          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_attachment(  i_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

          (0) 
  38. 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

    (0) 
    2. Former Member

      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

      (0) 
  39. 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



    (0) 
  40. 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

    (0) 
  42. 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.

    (0) 
  43. 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.

    (0) 
  44. 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.

    (0) 
    1. 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.

      (0) 
      1. 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

        (0) 
    2. 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.

      (0) 

Leave a Reply