abap2xlsx – Generate your professional Excel spreadsheet from ABAP [PDF]

Home / Community / Blogs

abap2xlsx – Generate your professional Excel spreadsheet from ABAP July 12, 2010

| 20,866 Views | Ivan Femia more by this author

ABAP Development abap | code exchange | community projects | Open Source | sap developer network | sapmentor

share

share



tweet



share





Follow

One of the main request of many customers is to use MS Excel spreadsheet as height="350"]https://www.youtube.com/embed/jg7MRm2uK3I[/embed]

David Eizenga January 21, 2014 at 2:04 pm

Thanks Ivan. I will look into this.

Durga Prakash Jasti January 15, 2014 at 9:53 pm

Very Useful..

Edward Pelyavskyy January 29, 2014 at 7:16 pm

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

Ivan Femia Post author January 29, 2014 at 8:39 pm

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

Stefan Riedel-Seifert January 30, 2014 at 12:55 pm

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?

Gregor Wolf January 30, 2014 at 1:08 pm

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.

Stefan Riedel-Seifert January 30, 2014 at 1:42 pm

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

Gregor Wolf January 30, 2014 at 1:53 pm

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

Bruno Esperança February 5, 2014 at 11:11 am

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

Manish Kumar February 5, 2014 at 11:44 am

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.

Bruno Esperança February 5, 2014 at 1:39 pm

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

Uwe Fetzer February 5, 2014 at 1:59 pm

Hi Bruno, the reader is also included in the package. Check report ZDEMO_EXCEL15.

Bruno Esperança February 5, 2014 at 2:02 pm

Ah great! Thanks Uwe. Best! Bruno

Bruno Esperança February 5, 2014 at 4:30 pm

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

Ivan Femia Post author February 5, 2014 at 4:32 pm

Hi Bruno, this is a known issue with SAPLink. To solve the issue import the abap2xlsx nugget again. Best Ivan

Bruno Esperança February 5, 2014 at 5:08 pm

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

Ivan Femia Post author February 5, 2014 at 5:14 pm

You’re welcome

Karl Pelzer February 26, 2014 at 8:19 am

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!

Ivan Femia Post author February 26, 2014 at 2:34 pm

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

Lars Palitzsch March 13, 2014 at 9:07 am

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

Uwe Fetzer March 13, 2014 at 9:35 am

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

Ivan Femia Post author March 13, 2014 at 8:35 pm

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.

Gregor Wolf March 13, 2014 at 10:46 pm

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.

Ivan Femia Post author March 13, 2014 at 11:05 pm

Thanks Gregor Wolf for your feedback… Lars, this is the official statement on the Apache License and Distribution FAQ

Uwe Fetzer March 14, 2014 at 10:42 am

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

Ivan Femia Post author March 14, 2014 at 3:49 pm

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.

Lars Palitzsch March 14, 2014 at 7:09 am

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

Ivan Femia Post author March 14, 2014 at 3:46 pm

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

Terry Huang April 17, 2014 at 7:13 am

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:

Best regards, Terry

Bruno Esperança April 17, 2014 at 7:27 am

Reimport everything. It is a known issue with ZSAPLINK. Cheers, Bruno

Terry Huang April 17, 2014 at 7:47 am

Hi Bruno, Thanks! It really works now. Best regards, Terry

Bruno Esperança April 17, 2014 at 7:55 am

Most welcome, I had the same problem Cheers, Bruno

Ivan Femia Post author April 17, 2014 at 2:45 pm

This is an issue with SAPLink. Please refer to the installation guide

Paramesh Puranik April 29, 2014 at 8:31 pm

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.

Thank you!! PP.SAP

Ivan Femia Post author April 29, 2014 at 9:15 pm

Hi we support images, see for example zdemo_excel16 and zdemo_excel38. Excel watermark are not yet implemented. Best Ivan

Paramesh Puranik April 30, 2014 at 2:01 pm

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

Willi Robert July 15, 2014 at 2:17 pm

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

Gregor Wolf July 15, 2014 at 2:21 pm

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

Willi Robert July 17, 2014 at 2:36 pm

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

Ivan Femia Post author July 17, 2014 at 5:36 pm

HI Will, have you installed abap2xlsx in your system? If so, the demo reports are already there. Ivan

Willi Robert July 18, 2014 at 4:40 am

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

Gregor Wolf July 18, 2014 at 5:17 am

Please read the Installation Guide linked from the abap2xlsx homepage

Willi Robert July 18, 2014 at 6:25 pm

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

Gregor Wolf July 18, 2014 at 8:38 pm

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

Ian MacLaren July 17, 2014 at 12:38 pm

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

Willi Robert July 21, 2014 at 9:23 pm

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

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( ‘[email protected]‘ ). 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

Gregor Wolf July 22, 2014 at 6:09 am

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

Willi Robert July 22, 2014 at 9:02 pm

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

Ian MacLaren July 22, 2014 at 6:12 pm

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 not has anyone any suggestions? Repair SAP to CHAR(4) ? regards Ian

Gregor Wolf July 22, 2014 at 8:45 pm

Please check out SAP Note 1459896 – BCS: Support for four-digit file name extensions.

Ian MacLaren July 23, 2014 at 8:23 am

sweet, this trick works perfectly … many thanks !

Willi Robert July 22, 2014 at 9:05 pm

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

Ian MacLaren July 23, 2014 at 8:26 am

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

Willi Robert July 23, 2014 at 2:07 pm

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

Ian MacLaren July 23, 2014 at 3:08 pm

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

Willi Robert July 23, 2014 at 3:57 pm

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

Ian MacLaren July 23, 2014 at 4:14 pm

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

Willi Robert July 24, 2014 at 4:15 pm

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

Ian MacLaren July 25, 2014 at 8:25 am

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 …

Willi Robert July 25, 2014 at 2:15 pm

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

Gregor Wolf October 21, 2014 at 6:41 am

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

Markus Feifel October 21, 2014 at 7:54 am

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

tom dupond October 29, 2014 at 8:15 am

Hello, The provided codes are completely free? Can I implement it in our customer system? Thank you in advance, Tom

Gregor Wolf October 29, 2014 at 8:47 am

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

Simone Milesi October 29, 2014 at 8:47 am

Hi @tom dupond Yes, you can implement without any restriction in your customer system

tom dupond October 29, 2014 at 8:55 am

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)?

Gregor Wolf October 29, 2014 at 9:02 am

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

tom dupond October 29, 2014 at 10:49 am

I imported the transport and did not found an example for my requirement (open an xml file and change…) My template:

Logo in the Header and page number in the footer

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

Gregor Wolf October 29, 2014 at 11:53 am

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.

Olivier Muff October 29, 2014 at 12:11 pm

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.

tom dupond October 29, 2014 at 12:22 pm

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 ?

tom dupond October 29, 2014 at 3:02 pm

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?

Raymond Giuseppi October 29, 2014 at 3:08 pm

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

tom dupond October 29, 2014 at 3:17 pm

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”

Gregor Wolf October 29, 2014 at 3:45 pm

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

Ivan Femia Post author October 30, 2014 at 5:25 am

I’ll update the nugg and transport ASAP… I was in vacation for a while

tom dupond October 31, 2014 at 9:52 am

I don’t have the autorization to instal SAPlink in the customer system

I can only work with the

transports.

Bruno Esperança October 31, 2014 at 11:51 am

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

tom dupond October 31, 2014 at 12:51 pm

No, I don’t have the authorization The best way is to have a transport up to date but still no response from Ivan.

Bruno Esperança October 31, 2014 at 2:46 pm

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

tom dupond October 31, 2014 at 3:40 pm

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

Bruno Esperança October 31, 2014 at 3:49 pm

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

tom dupond October 30, 2014 at 7:50 am

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

I hope that the new transport will fix these issues. Thank you very much for you help.

tom dupond October 30, 2014 at 12:24 pm

Dear Ivan, Do you know (more less) when you can deliver the latest transport version? Thanks in advance, Tom

tom dupond November 3, 2014 at 1:20 pm

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

Bruno Esperança November 3, 2014 at 4:59 pm

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

tom dupond November 3, 2014 at 3:19 pm

Dear Team, Does anybody have an idea about how to add a picture in the header with the code? Thanks in advance

tom dupond November 4, 2014 at 8:11 am

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

Gregor Wolf November 4, 2014 at 10:10 am

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

tom dupond November 4, 2014 at 2:26 pm

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

tom dupond November 5, 2014 at 10:00 am

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

Ivan Femia Post author November 7, 2014 at 3:03 pm

Hi Tom, please open a discussion on SCN with the tag abap2xlsx and [abap2xlsx] in the subject. Thank you Ivan

Kenneth Moore November 14, 2014 at 2:20 pm

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?

Gregor Wolf November 14, 2014 at 2:25 pm

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

Kenneth Moore November 14, 2014 at 4:20 pm

Thanks, Gregor. I created a post.

Bruno Esperança January 12, 2015 at 3:30 pm

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

Ivan Femia Post author January 12, 2015 at 5:59 pm

Hi Bruno, I will install your solution in my abap system… It seems interesting. Thank you Ivan

Bruno Esperança January 12, 2015 at 11:23 pm

That’s an honor Ivan Best regards, Bruno

SUNEEL CHILLIMUNTA January 14, 2015 at 3:15 am

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.

Bruno Esperança January 14, 2015 at 9:29 am

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

Ivan Femia Post author January 14, 2015 at 3:14 pm

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

SUNEEL CHILLIMUNTA January 15, 2015 at 2:44 am

Thank you both.

Ian MacLaren February 5, 2015 at 4:24 pm

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 …

If he/she proceeds, Excel reports an error with the spreadsheet

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

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

David Eizenga February 5, 2015 at 5:24 pm

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

Ian MacLaren February 6, 2015 at 10:07 am

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

David Eizenga February 12, 2015 at 1:22 pm

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

Ivan Femia Post author February 5, 2015 at 5:40 pm

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

Ian MacLaren February 6, 2015 at 10:30 am

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

tom dupond February 12, 2015 at 8:38 am

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

Karl Pelzer February 12, 2015 at 10:21 am

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

tom dupond February 12, 2015 at 11:03 am

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

Karl Pelzer February 12, 2015 at 11:25 am

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

tom dupond February 12, 2015 at 12:41 pm

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

Uwe Fetzer February 12, 2015 at 12:56 pm

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

Karl Pelzer February 12, 2015 at 1:05 pm

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

tom dupond February 12, 2015 at 1:36 pm

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

Best Regards Tom

Ivan Femia Post author February 13, 2015 at 3:21 pm

PLEASE, for any question related to abap2xlsx demo, use cases, explanation use this space on SCN Open Source For reporting a bug please open a issue on GitHub Issues · ivanfemia/abap2xlsx · GitHub

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

Jakub Misiarz April 14, 2015 at 7:02 am

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:

Thank in advance for any help/guide, Jakub

Ivan Femia Post author April 14, 2015 at 2:55 pm

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

Johannes Kretschmer June 22, 2015 at 11:21 am

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

Ivan Femia Post author June 22, 2015 at 6:04 pm

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

SRIHARSHA kasukurthi September 28, 2015 at 2:18 pm

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.

Ivan Femia September 28, 2015 at 3:43 pm

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

SRIHARSHA kasukurthi September 30, 2015 at 7:15 am

Thank you Ivan

Evgeniy Astafev November 13, 2015 at 1:20 pm

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.

Gregor Wolf November 14, 2015 at 10:53 pm

Dear Evgeniy, you’re already programming in ABAP so you can create the formula also programmatically. Best regards Gregor

Ivan Femia November 14, 2015 at 11:58 pm

Evgeniy, please open a discussion in order to keep this thread clean… And your answer is in the demo provided within the nugget Ivan

Ramesh Mahankali July 2, 2017 at 2:38 am

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.

Sandra Rossi July 2, 2017 at 7:08 am

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.

Ramesh Mahankali July 3, 2017 at 11:47 am

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

Sandra Rossi July 3, 2017 at 3:23 pm

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

Ramesh Mahankali July 3, 2017 at 3:31 pm

Thank you Sandra for your response.

Sandra Rossi July 3, 2017 at 7:13 pm

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.

Previous

Share & Follow















1

2

Privacy

Terms of Use

Legal Disclosure

Copyright

Trademark

Sitemap

Newsletter