ABAP Code to Internal Table as Excel File on SAP Application Server
I recently got a requirement to write a file on application server in excel format. This post explains how I reached to a solution and how to code the solution.
At first, I thought its very simple. The first thing I did – created a tab separated file with ‘.xlsx’ extension.
I could write the file. There is no issue there. The problem is, Excel does not open this file and simply says, it is not Excel file.
Then, I started experimenting or simply put Trial and Error method.
- Changed extension to ‘.xls’
- Toggled between writing modes TEXT and BINARY in OPEN DATASET
- Used SMART LINEFEED in OPEN DATASET
- Toggled between downloading options – ASC and BIN formats (Using CG3Y)
- Tried accessing file at Unix Level and downloaded the file using WINSCP
- Used Tab, Comma, Semi-Colon to separate file
None of these worked. I also searched over SAP blogs for some help. I did find below which says you can do this with using tabs.
Writing Excel file into Application Server using horizontal tab | SAP Blogs
But this method did not work for me. It continued to give the same error.
I did find few more discussions like below, but none with a satisfactory outcome and also some of them were very old.
Create XLS file on Application Server (no tab-delimited format) | SAP Community
Create excel file on application server | SAP Community
At this point, I was convinced that I should now try to convince the user who requested this to use .csv file. As .csv can be opened in Excel. I did try that but that did not work either.
Then I took a step back, started looking at it from different point of view and it struct me. Creating excel attachment works. Why not application server file?
Yes, I was providing the extension as ‘.xlsx’, but what I was writing was not Excel. The missing piece was that the contents were not transformed to Excel content. So, there it was – the solution.
So, I created below method for the transformation.
CLASS zcl_itab_to_excel DEFINITION PUBLIC FINAL. PUBLIC SECTION. METHODS: itab_to_xstring IMPORTING ir_data_ref TYPE REF TO data RETURNING VALUE(rv_xstring) TYPE xstring. ENDCLASS. CLASS zcl_itab_to_excel IMPLEMENTATION. METHOD itab_to_xstring. FIELD-SYMBOLS: <fs_data> TYPE ANY TABLE. CLEAR rv_xstring. ASSIGN ir_data_ref->* TO <fs_data>. TRY. cl_salv_table=>factory( IMPORTING r_salv_table = DATA(lo_table) CHANGING t_table = <fs_data> ). DATA(lt_fcat) = cl_salv_controller_metadata=>get_lvc_fieldcatalog( r_columns = lo_table->get_columns( ) r_aggregations = lo_table->get_aggregations( ) ). DATA(lo_result) = cl_salv_ex_util=>factory_result_data_table( r_data = ir_data_ref t_fieldcatalog = lt_fcat ). cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform( EXPORTING xml_type = if_salv_bs_xml=>c_type_xlsx xml_version = cl_salv_bs_a_xml_base=>get_version( ) r_result_data = lo_result xml_flavour = if_salv_bs_c_tt=>c_tt_xml_flavour_export gui_type = if_salv_bs_xml=>c_gui_type_gui IMPORTING xml = rv_xstring ). CATCH cx_root. CLEAR rv_xstring. ENDTRY. ENDMETHOD. ENDCLASS.
And it worked very well. You simply need to pass your internal table reference to the method, get the excel content in xstring and transfer it to application server.
GET REFERENCE OF it_out_rec INTO DATA(lo_data_ref). DATA(lv_xstring) = NEW zcl_itab_to_excel( )->itab_to_xstring( lo_data_ref ). OPEN DATASET lv_xls_file FOR OUTPUT IN BINARY MODE. IF sy-subrc EQ 0. TRANSFER lv_xstring TO lv_xls_file. CLOSE DATASET. ENDIF.
- It is easy to create excel file on application server with help of the SALV and related classes
- This method also works well if xlsx attachment is required to be sent in an email
- This method also helps formatting the excel with aggregations and layout settings. Explore class cl_salv_controller_metadata for more details.
Your comments and suggestions are welcome to further improve this code. Please use the comment section below or you can also post your questions here.
– Jagdish Patil
Uhm, have you tried abap2xlsx ?
Yes, I checked it, but I had to do this in a day and did not have time to try it out. And from the experience - we wont be able to use this on client's system.
It is however very interesting and I am currently trying to understand it.
If I may ask, what was the issue with the client system, too old perhaps?
I've had a colleague extend an existing custom report to use abap2xlsx in about half a day but I had already done the installation long before that, to try it out.
It may come down to restrictions as far as "just" grabbing source code (and potentially many other workbench objects) from "somewhere on the internet" and putting that into the productive system landscape. Not all companies will allow that.
That's really true!
I managed to get it into a validated system of a regulated industry with very tight processes.
I asked the person responsible for the development platforms, and they wrote an exception to usual naming standards. Far too useful not to use abap2xlsx - saves an absolute fortune in programming and maintenance as well...
I must admit when it came to trying to get it done another time, I just used the technique described here. But from one of these blogs...
Yes, I had used this technique to attach xlsx for an email. Tried the same thing and it worked.
The only reason I posted it here that I thought it will help someone some day.
I've experienced once system owners who were against the use of open source solutions in ABAP. For example no abapGit. Crazy but true: the note of a lawyer. Liability for the use of open source must first be clarified. Technically it was all ok, but neither organizationally nor legally. What a day 😉
Ok, then I got lucky: customer's IT was very interested in this library! 🙂
And also my colleague liked it, so it's already 2 installations 😀
Not allowed to use source code which is open source.
I guess they're in for a rude awakening with the newer SAP offerings 😀
Thanks for the explanation and for the code, as well.
Nice, you can also achieve the same result by simply calling this code instead of cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform
This is helpful. I will try this out. This looks so much simpler.
to_xml internally calls IF_SALV_BS_TT_UTIL~TRANSFORM so in the end it should be the same but somehow there are subtle differences: the direct call favours short (CURRENCY) / medium length (SEATSOCC) labels, the more compact, indirect call the long ones.
well, what do you know, they're really different: on a 7.31 system the direct call to "transform" produces a file that Excel wants to repair. Using "to_xml" produces a valid file. After struggling to understand what was wrong with the original Z report, we switched to the basic SFLIGHT examples and discovered this difference in file generation.
Can you upload any video or blog about how to check https://github.com/sapmentors/abap2xlsx data shared over git? As there are many doc and not sure from where to start.
I'm not sure I understand your question: do you want to learn about the internals of abapGit?
If the question is only related to abap2xlsx documentation, what we have at the moment is the demos repository with lots of code samples.
The Excel message is clear. "Excel cannot open the file 'Outtabxlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file." means that your file has the extension xlsx but the format inside it is not Office Open XML for Excel (what xlsx means), it's a text with tab-delimited values. If you have a text file with tab-delimited values, you should use the extension txt.
There are other blog posts or answers about the same kind of logic, but you sum up very shortly. NB: 2 little remarks about your code, zcl_itab_to_excel=>convert_to_excel doesn't exist; you may also avoid the reference parameter ir_data_ref because anyway it can still fail if the internal table is read-only -you may instead duplicate the memory (unfortunately) by defining the internal table as a pass-by-value parameter-.
It's also a nice solution for people who can't install abap2xlsx because of company policy.
So, thanks for that!
Thanks for pointing out the method name. I missed to change it where it is called. Updated now.
On the parameter, it did not feel right to create a copy as the I was working with huge number of records, but can you explain a bit on the potential failure? In that case I would move the code where I already have the internal table instead of calling the method.
I would measure memory aspect. ABAP has optimised stuff in this area and I believe it uses a Copy-On-Write approach, meaning internally only one dataset exists until you change it. SALV doesn't change data so you should be OK there.
What Sandra referred to was that SALV will fail on a readonly table. So if a method calls your method with its own importing parameter (these are always readonly), the ref will point to a readonly variable.
But anyhow my other reason for writing here was that I too started thinking "not another Excel export blog", but yours is actually a neat solution for simple 1:1 downloads. Sure abap2xlsx can do a lot more and is just as simple, but SALV is already available in every system and I learnt something new that it can do, so thanks 🙂
Mike Pokraka I forgot that "Copy-On-Write approach", thanks for correcting me 😉
This helps a lot.
2 other syntax errors found by using your code (missing parenthesis + it's an instance method):
Concerning my comment about "can still fail", here is the case to make the internal table read-only, it will fail when it tries to execute cl_salv_table=>factory (can be seen in debug, xstring is returned empty):
Thanks Sandra. So much clear now.
And thanks for correcting the syntax errors.
Another month, another ABAP to XLSX solution..
Not sure I get what you meant.
As I mentioned in the post itself, I did search for this. All I could find was excel download to front end and that works fine. I did not find a single one for writing one to application server.
If you do have it - please let me know. I will be happy to put that in the post so that we will have multiple solutions linked together when next time someone searches for this.
abap2xlsx definitely works for backend and has done since at least 2016 - it's fairly obvious that it does really, as its main output is an xstring which you can write anywhere. You could even send it out in a restful web service.
Nothing personally about your own blog post,
just mentioned the fact that every month a new post regarding export to an XLSX file is being published (and without SAP releases an official solution nor adopting the great ABAP2XLSX project).
I tried the code, I was able to change Xstring data further to binary using SCMS_XSTRING_TO_BINARY and then download to the application layer. But when I am using open data set to write to AL11 in binary mode, excel is getting generated in AL11 and opening .xlsx file it shows :-
When I download it as 'Download file as text' and saving in .xlsx format it saved again in gibberish way. I am stuck. Please help.
Apologies for responding so late, I believe by now you must have figured this out.
On AL11 the file will appear like junk characters as you have shown, but if you FTP the file or download to local using tools like winscp, you can open the file properly. You can take help from a Basis Consultant as well to get the file in case you do not have any such tool.
As per my experience, CG3Y also does not work in this case.
My requirement is to send payment data in .xls format to application server
from there the user downloads and upload in bank server
i tried with horizontal tab and we are getting file - but when opening the file there is a popup coming as warning " excel cannot open the file, because file may be corrupted"
I tried the steps mentioned by you, and in my file all fields are the coming in first column
Could you please help here
steps i followed
we are sending the file to application server and user wants to open the .xls file without any popup warning
I got the same issue as you. have you fixed it yet?
I was able to create .xlsx file without problem. However, user wants to delete the first row with no data. How to remove the blank row? Please advise.
hello Thanx for sharing and it's work perfectly but how do you manage an internal table with millions of records ( for the fiscal administation) and i have the dump on the
with a SYSTEM_NO_ROLL.
It is possible do the transformation by package?
Hi Jagdish , please help me .. I am getting syntax error .
below is the error that i am getting
Statement concluding with "...DATASET" ended unexpectedly.
Hi Jagdish Sir,
I am facing same issue for generate excel (.xlsx) file on FTP server.
I want to transfer itab data on FTP serve in Excel (.xlsx) format.
Please provide sample code.
inside the method
and finaly we have in r_xstring ( xlsx file in xtring format ) and we can use the logic Jagdish to export data on SAP Application Server
VINO YAD for transfer data to FTP server you can take a look https://heikoevermann.com/sap-ftp-connections-in-abap/
i try your method to send an itab as attachment but to open it with Excel.
I pass a XLS type in the attachment parameter but when i open the attachment i see this :
My code if someone can look and see what i do wrong.
CREATE OBJECT GET_XLS.
LV_XSTRING = GET_XLS->RUN( IT_OUT_REC = LT_SEND ).
EXCEL_AS_SOLIX_STACK = CL_BCS_CONVERT=>XSTRING_TO_SOLIX( IV_XSTRING = LV_XSTRING ).
DOCUMENT = CL_DOCUMENT_BCS=>CREATE_DOCUMENT(
I_TYPE = 'RAW'
I_SUBJECT = MAIL_TITLE ).
I_ATTACHMENT_TYPE = 'XLS'
I_ATTACHMENT_SUBJECT = MAIL_ATTACHMENT_SUBJECT
I_ATT_CONTENT_HEX = EXCEL_AS_SOLIX_STACK ).
SEND_REQUEST->SET_DOCUMENT( DOCUMENT ).
* Envoie de l'email
CALL METHOD SEND_REQUEST->SEND(
RESULT = SENT_TO_ALL ).
With : DATA: GET_XLS TYPE REF TO ZCL_ITAB_TO_EXCEL.
DATA LO_DATA_REF TYPE REF TO DATA.
GET REFERENCE OF IT_OUT_REC INTO LO_DATA_REF.
RV_XSTRING = NEW ZCL_ITAB_TO_EXCEL( )->ITAB_TO_XSTRING( LO_DATA_REF ).
FIELD-SYMBOLS: <FS_DATA> TYPE ANY TABLE.
DATA: ANY_BCS_EXCEPTION TYPE REF TO CX_ROOT.
DATA: DIAGNOSTIC TYPE STRING.
ASSIGN IR_DATA_REF->* TO <FS_DATA>.
IMPORTING R_SALV_TABLE = DATA(LO_TABLE)
CHANGING T_TABLE = <FS_DATA> ).
R_COLUMNS = LO_TABLE->GET_COLUMNS( )
R_AGGREGATIONS = LO_TABLE->GET_AGGREGATIONS( ) ).
R_DATA = IR_DATA_REF
T_FIELDCATALOG = LT_FCAT ).
XML_TYPE = IF_SALV_BS_XML=>C_TYPE_XLSX
XML_VERSION = CL_SALV_BS_A_XML_BASE=>GET_VERSION( )
R_RESULT_DATA = LO_RESULT
XML_FLAVOUR = IF_SALV_BS_C_TT=>C_TT_XML_FLAVOUR_EXPORT
GUI_TYPE = IF_SALV_BS_XML=>C_GUI_TYPE_GUI
XML = RV_XSTRING ).
CATCH CX_ROOT INTO ANY_BCS_EXCEPTION.
DIAGNOSTIC = ANY_BCS_EXCEPTION->IF_MESSAGE~GET_TEXT( ).
Thks if someone can see the problem and help. I still look at many blog and exemple but it don't help me to solve.