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)
The idea
So the idea: An xlsx generator from scratch in ABAP.
What are the business advantages?
and the technical ones?
what about themain features?
- Cell formatting (as in HTML solution)
- Formulas (as in HTML solution)
- Multi sheets new!
- Conditional formatting new!
- Cell data format new!
- Graphs new!
- Drawings new!
- And many others new!
Professional MS Excel workbooks as background process, as email attachment, as http response in a WDA, using an RFC and so many othercontexts.
Can you imagine a REST service like this?
http://www.techedge.it/Rest/CustomerList.xlsx?VKORG=1001
Developers can easily produce Excel with few lines of ABAP code; abap2xlsx class wraps all the logic of xlsx generation for professional and advanced workbooks. See Hello World demo code (more demos available on Google Code),only 5 lines of ABAP code and a new MS Excel is here!
Hello world excel demo code:
The output will be:
Now unchain your fantasy! Professional Excels are real!
Code is shared on SAP Code Exchange @ http://www.abap2xlsx.org via SAPLink.
Do you want to collaborate in this project? Join us on Code Exchange.
Comments, real business cases and suggestions are really appreciated!
New features will came in next releases; I created a roadmap on Google Code and on Wave. I have also scheduled an xlsx2abap project but this will be a really nice to have right now.
Thanks to Sergio Ferrari and Ferrari’s Team for their support.
SCN references:
- Spreadsheet integration – which approach for which SAP technology by SergioFerrari.
- Downloading data into Excel with Format Options (from SAP Web Applications) by SergioFerrari.
- Excel OLE and ABAP – Create fancy reports by Alvaro Tejada Galindo
- Using WD ABAP ALV export – the hacked way by Chris Paine
- xlsx2abap – Read and edit your Excel files from ABAP by Ivan Femia
- abap2xlsx – I’m no longer a baby, I grow up and now I’m a fully featured tool by Ivan Femia
- abap2xlsx team has a present for you… by Ivan Femia
External links:
For support follow these best practices:
- For any question related to abap2xlsx demo, use cases, explanation create a new discussion in SCN Open Source
- For reporting a bug please open a issue on GitHub Issues · ivanfemia/abap2xlsx · GitHub
Supported releases:
As from SAP WebAS 7.0
Is there a way to disable the Excel menu (specifically "File") when displaying an Excel workbook on-line via ABAP2XLSX?
-David
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]
Thanks Ivan. I will look into this.
Very Useful..
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
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
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?
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.
But that's not a state-of-the-art upgrade :-(. 0.1.4 is the latest no-beta-version?
SAPlink is provides only as a daily build. Take the latest and greatest that should have the least bugs.
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
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.
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
Hi Bruno,
the reader is also included in the package. Check report ZDEMO_EXCEL15.
Ah great!
Thanks Uwe.
Best!
Bruno
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
Hi Bruno,
this is a known issue with SAPLink. To solve the issue import the abap2xlsx nugget again.
Best
Ivan
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
You're welcome 🙂
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!
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
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
Hello Lars,
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).
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.
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.
Thanks Gregor Wolf for your feedback...
Lars, this is the official statement on the Apache License and Distribution FAQ
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).
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.
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
You can easily change the name of the classes editing the nugg file.
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
Reimport everything. It is a known issue with ZSAPLINK.
Cheers,
Bruno
Hi Bruno,
Thanks! It really works now. 🙂
Best regards,
Terry
Most welcome, I had the same problem 🙂
Cheers,
Bruno
This is an issue with SAPLink.
Please refer to the installation guide
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
Hi
we support images, see for example zdemo_excel16 and zdemo_excel38.
Excel watermark are not yet implemented.
Best
Ivan
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
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
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
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
HI Will,
have you installed abap2xlsx in your system?
If so, the demo reports are already there.
Ivan
HI Ivan,
How to install abap2xlsx , I have not done so can you please guide me.
Thanks
Willi
Please read the Installation Guide linked from the abap2xlsx homepage
I have gone through the Installation guide but as the nugget file is big it is giving me dump while importing the file.
as I sad before: ask your basis admin to fix the limit of split the file into several ones. It is just XML.
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
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( '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
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
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
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
Please check out SAP Note 1459896 - BCS: Support for four-digit file name extensions.
sweet, this trick works perfectly ... many thanks !
🙂
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
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
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
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
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
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
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.
I believe someone will provide some insight.
Thanks
Willi
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 ...
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
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
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
Hello,
The provided codes are completely free? Can I implement it in our customer system?
Thank you in advance,
Tom
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
Hi @tom dupond
Yes, you can implement without any restriction in your customer system 🙂
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)?
Yes, please install it and have a look at the examples.
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
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.
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.
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 ?
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?
Did you activate every object (here interface ZIF_EXCEL_SHEET_PROPERTIES and class ZCL_EXCEL_SHEET_SETUP) ?
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"
Please install the latest version of abap2xlsx using SAPlink. The version you can get by transport is not up-to-date.
I'll update the nugg and transport ASAP... I was in vacation for a while 🙂
I don't have the autorization to instal SAPlink in the customer system 🙁 I can only work with the transports.
Do you have access to LSMW? Or debugging? If you have access to any of these things, you have SAP_ALL 😉
No, I don't have the authorization 😥
The best way is to have a transport up to date but still no response from Ivan.
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
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
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
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.
Dear Ivan,
Do you know (more less) when you can deliver the latest transport version?
Thanks in advance,
Tom
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
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
Dear Team,
Does anybody have an idea about how to add a picture in the header with the code?
Thanks in advance
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
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
😐 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 🙁
Is there another way to display a picture in each page in the excel file?
Hi Tom,
please open a discussion on SCN with the tag abap2xlsx and [abap2xlsx] in the subject.
Thank you
Ivan
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?
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
Thanks, Gregor. I created a post.
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
Hi Bruno,
I will install your solution in my abap system...
It seems interesting.
Thank you
Ivan
That's an honor Ivan 🙂
Best regards,
Bruno
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.
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
As Bruno stated, Excel version does not affect generated xlsx.
Thank you both.
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.
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:
*** Warning: converting to ODS (Open DataSource) format replaces
the SPACE character in sheet names with the UNDERSCORE character.
-David
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
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
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
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
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
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
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
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
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
Tom and Karl: can you compare your Basis Releases? Maybe something changed in one of the last service packs.
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
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
PLEASE,
This will help us to provide you and the community a better support.
Thanks,
Ivan
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
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
Hello everyone,
is it possible to create an excel file with the Method bind-table but don't get any filters already set ???
Johannes please do not cross post, answer on your discussion abap2xlsx -> bind_table without filters
Ivan
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.
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
Thank you Ivan 🙂
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.
Dear Evgeniy,
you're already programming in ABAP so you can create the formula also programmatically.
Best regards
Gregor
Evgeniy,
please open a discussion in order to keep this thread clean...
And your answer is in the demo provided within the nugget
Ivan
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.
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.
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
DOI is based on an OLE client too. Sorry, I don't know for ABAP2XLSX.
Thank you Sandra for your response.
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.
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.
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
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
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
Try SET_AREA method, check the ZDEMO_EXCEL13 example
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
https://github.com/sapmentors/abap2xlsx/blob/a9be231d99291760edf9164aad1be31c4b9f6def/src/demos/zdemo_excel13.prog.abap#L81
Muchas gracias!
pero me imagino que nuestro sistema no esta actualizado porque en nuestra clase no tenemos el método SET_AREA
por eso no lo encontraba, de echo en el programa ZDEMO_EXCEL13 de nuestro sistema no viene ese codigo
Muchas Gracias
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