PROJECT LINK:
RELATED PAGES:
- Export ALV-Tree to Excel with XLSX Workbench
- Export internal table to Excel with XLSX Workbench
- Create a Formatted Excel in a Background Job
YOUTUBE CHANNEL:
- XLSX Workbench (for SAP) – Simple Excel form from scratch – YouTube
- XLSX Workbench (for SAP) – Export Grid to Excel – YouTube
- XLSX Workbench (for SAP) – Export CL SALV TREE to Excel – YouTube
- XLSX Workbench (for SAP) – Export CL_GUI_ALV_TREE to Excel – YouTube
IDEA
For a long time only OLE/DOI-approach was available for creating spreadsheets. As you know, OLE/DOI-approach is quite flexible, but it has some restrictions. For example, it requires installed MS Excel application (this makes impossible to use background job for generate spreadsheet). Moreover, OLE-approach has low performance (this problem can be partially solved using VBA, but this requires manually changing of security settings MS-Excel application on each front-end computer).
Since, ZIP-folder processing tools became available in the SAP environment, we are able to generate spreadsheets with new Open XML Format (XLSX). This approach does not have lacks of OLE.
But, in my opinion, the flaw of all existing solutions for generate spreadsheets from ABAP- is many lines of ABAP code required directly for creating and formatting spreadsheet layout. As result, the same Excel forms are quite differently implemented by the different developers. That approach heavily complicates a support of these developments.
My idea is creating the design-time environment for visual form editing (like a SMARTFORMS tool, for example).
A unified approach to development is realized because no need to write the code for the Excel form formatting – you only care about source data for the form.
Runtime printing form is being generated with the new Open XML Format (XLSX), without using of Microsoft Excel application (it makes possible to get result in the background jobs SAP).
DESIGN-TIME AND RUNTIME
Where are two modes of XLSX Workbench tool.
- Design-time mode starts with a call transaction ZXLWB_WORKBENCH and provides completely visual interface, which allows you to quickly create or change the form layout even you don’t have ABAP skills:
- Runtime mode occurs each time the printing programm is being called.
The printing program prepares the source data (and fill the context) and finally calls the functional module ‘ZXLWB_CALLFORM’, which compiles and displays the form:
Developer decides how to use the runtime form:- display on the front-end computer via MS Excel application;
- save on the front-end computer as file (.XLSX) with a specified path;
- return into the printing program as rawdata.
WHAT DOES DEVELOPMENT PROCESS LOOK LIKE ?
You need to develop three components:
- CONTEXT. As a Context, you can use any suitable Table type or Structure or Data element from the ABAP-dictionary. Or just create your own Context via transaction SE11.
- FORM. Call transaction ZXLWB_WORKBENCH and build the form structure in the visual editor. Forms are stored in the SAP WEB-repository (transaction SMW0, binary data for WebRFC-applications), and represented as XLSX-files. All data about form’s structure is inside the file.
- PRINTING PROGRAM. It must contain next steps:
-
declare a context (just variable which refers to ABAP-dictionary type);
-
fill context with data;
-
-
call function module ‘ZXLWB_CALLFORM’.
Nice stuff……
Thank you for your interest in my work. Did you try to use it?
I will try it and update you….
I was looking a long time for a tool like that… i will try it.
write your opinion after you have tried, please!
Hello Igor,
i found that two of my clients have installed the abap2xlsx tool ( http://wiki.scn.sap.com/wiki/display/ABAP/abap2xlsx ), what is the difference to your tool?
Regards Wolfgang
Hello, Wolfgang.
XLSX Workbench provides graphical UI and uses visual approach (like SMARTFORMS, for example).
Best regards, Igor.
Does it support reading functionality to upload and edit excel files?
Hello, Ivan.
I didn’t mean that is the only difference, but I had in mind that the visual approach of the form design is the main idea of my work. Both tools are quite different.
P.S. XLSX Workbench does not support reading functionality to upload and edit excel files.
Best regards, Igor.
I’m curious to install your tool it seems interesting…
Thank for sharing!
Ivan
upon trying to install using saplink, I got a dump in SAPLINK’s method GETOBJECTINFOFROMIXMLDOC, no attribute ( AttrNode) found in rootAttr.
Using saplink 0.1.4. Is the problem in saplink or in the nugget?
Jozef, thank you for what you notice! I updated the nugget. Check now, please!
Thanks for the update. I tried now, but unfortunately it seems I’m not able to find a working active FUGR plugin for the SAPLINK. All the resources aim at Downloads – saplink-plugins – SAPlink plugins – Google Project Hosting. And there seems to be only a deprecated version for the FUGR object type. In fact whole this SAPLINK stuff would be a lot nicer, if it had one single place to live.
However, it is working fine. Maybe one more comment – in the installation instructions, I should create the trannsaction for program ZXLWB_WORKBENCH, which does not exist. ZXLWB is ok with this.
About SAPLINK – I agree with you. And I have corrected installation instruction, thank you!
Igor and Josef,
the latest versions of SAPlink and Plugins are located on Assembla repository, Google Code nuggets are very old version. Maybe, Gregor Wolf can update the references in Google Code to forward to Assembla.
SAPlink daily nugget in the folder trunk/build
Plugins daily nugget in the folder build
The official link for SAPlink is http://www.saplink.org
Ivan
Ivan, thank you for links! I will update the nugget using the latest version of SAPLINK. Igor.
Hi Igor,
really nice work, just started to test and I’m really amazed.
Thanks for sharing.
Hi, Alexander, thanks for feedback.
Hi all !
New version is available. Incompatibility issue with Excel 2013 was fixed.
what an amazing work! is this free to use 🙂
yes of course it’s free
New version is available.
This looks nice, thanks for sharing.
I’ll try to test this when I have some spare time!
Best,
Bruno
Hi Bruno, thank you! I would like to have your feedback after test!
Hi all, another update.
Export ALV-Tree to Excel with outline hierarchy. Lots of layout and formatting options.
Hi Igor,
Very impressive work, more than 18000 lines of code in ZXLWB_INCLUDE. Wow.
I was wondering if there is a way to replace the Author and Company properties of the xlsx files actually sets with your name and your site?
Can you please also explain what is the usage of the 77 lines of Hexa data (from line 2725 to 2802) in ZXLWB_INCLUDE ? How can we see the detailed content of these lines ?
Thanks for your reply.
Laurent
Hi Laurent.
Secial for you I have released a new (2.1) version. There new importing parameter IT_DOCPROPERTIES was added to the ZXLWB_CALLFORM function module. How to use it see here.
The lines of Hexa data in the XLWB INCLUDE contains an INITIAL (ie used only when you create NEW form) template of Excel document, that you see at right-hand frame of the workbench window.
Igor.
Hi Igor,
Thanks very much for making possible to update document properties.
About the template loaded from hex lines hard coded in the include, I think it should be better to be able to load a personal template.
As you can imagine, hex data in a program could be seen as a blackbox and could frighten Security guys.
Moreover, template loading will offer flexibility to adapt the template for different situations.
In your constructor method of lcl_excel, this could be something like:
* ‘74796C65732E786D6C504B0102130014000600080000002100DAEB1BFFA0060000861B00001300000000000000000000000000E30B0000786C2F7468656D652F7468656D65312E786D6C504B010214001400000008007A6659445FEBF2DC330100001002’ ,
* ‘00001800000000000000010020000000B4120000786C2F776F726B7368656574732F7368656574312E786D6C504B010214001400000008002D6659445FA65CD1440100007C02000011000000000000000100200000001D140000646F6350726F70732F63’ ,
* ‘6F72652E786D6C504B0102140014000000080019665944DE01D331A80100009A030000100000000000000001002000000090150000646F6350726F70732F6170702E786D6C504B05060000000009000900F3060000661700000000000000000000000000’ .
*
*
*
* CALL FUNCTION ‘SCMS_BINARY_TO_XSTRING’
* EXPORTING
* input_length = lv_docsize
* IMPORTING
* buffer = lv_rawdata
* TABLES
* binary_tab = lt_doctab.
DATA:
lv_filename TYPE string,
lv_retcode TYPE char1 ,
ls_fields TYPE sval ,
lt_fields TYPE STANDARD TABLE OF sval .
ls_fields–tabname = ‘SI_RQ_CONTENT’ .
ls_fields–fieldname = ‘PHYSNAME’ .
ls_fields–fieldtext = ‘Select XLSX Template’ .
APPEND ls_fields TO lt_fields .
CALL FUNCTION ‘POPUP_GET_VALUES_USER_HELP’
EXPORTING
* F1_FORMNAME = ‘ ‘
* F1_PROGRAMNAME = ‘ ‘
f4_formname = ‘POPUP_XLSX_TEMPLATE_F4’
f4_programname = sy–repid
* FORMNAME = ‘ ‘
popup_title = ‘XLSX Template’
* PROGRAMNAME = ‘ ‘
* START_COLUMN = ‘5’
* START_ROW = ‘5’
* NO_CHECK_FOR_FIXED_VALUES = ‘ ‘
IMPORTING
returncode = lv_retcode
TABLES
fields = lt_fields
EXCEPTIONS
error_in_fields = 1
OTHERS = 2.
IF sy–subrc <> 0.
MESSAGE ID sy–msgid TYPE ‘S’ NUMBER sy–msgno
WITH sy–msgv1 sy–msgv2 sy–msgv3 sy–msgv4 .
EXIT .
ENDIF.
IF lv_retcode NE space .
MESSAGE s000(lp) WITH ‘Operation was terminated by the user’.
EXIT .
ENDIF .
READ TABLE lt_fields INTO ls_fields INDEX 1 .
CHECK sy–subrc EQ 0 .
lv_filename = ls_fields–value .
TRY.
lv_rawdata = cl_openxml_helper=>load_local_file( lv_filename ).
CATCH cx_openxml_not_found.
ENDTRY.
rawdata_set( lv_rawdata ) .
and new form for F4 template selection:
*&———————————————————————*
*& Form popup_xlsx_template_f4
*&———————————————————————*
* text
*———————————————————————-*
* –>TABNAME text
* –>FIELDNAME text
* –>DISPLAY text
* –>RETURNCODE text
* –>VALUE text
*———————————————————————-*
FORM popup_xlsx_template_f4 USING tabname fieldname display
CHANGING returncode value.
DATA: lv_filename TYPE string.
lv_filename = cl_openxml_helper=>browse_local_file_open( iv_title = ‘Select XLSX File’
iv_filename = ”
iv_extpattern = ‘All files(*.*)|*.*’ ).
value = lv_filename.
ENDFORM. “popup_xlsx_template_f4
What do you think ?
Laurent
Hi Laurent, I have released a new (2.2) version.
Thank you, but unfortunately class cl_openxml_helper isn’t available in my system…
If security guys are frightened by the hardcoded template, you can just delete method TEMPLATE_GETFROM_HARDCODE from the class LCL_EXCEL. But instead, you have to upload an empty XLSX-file to WEB-repository (SMW0 transaction) with object name ZXLWB_&TEMPLATE& .
Igor.
Hi Igor,
Thanks for your changes in 2.02 version. It works fine.
Hi Igor,
1. I have inserted a pivot table in a XLSX form but when I execute it I get an error when Excel 2013 open the file: “We found an error with some contents in ‘xxxxxxxxxxxxxxxxxxxxxx.xlsx’. Do you want us to try to recover as much as we can ? if you trust the source of this workbook, click yes.”
When I do it with Excel 2010 it works well
2. From the pivot table, I have tried to generate a chart in the form. I have done the Excel part in the template then I have created a new pattern, assigned an area in the template, added a Drawing, used ‘Drawing from template’ as source and bind it with the chart. The form is correctly activated but at execution I have the same error as in paragraph 1.
3.I have also tried to create a form with your 3.2.12 Dynamic merge cells example but unfortunately at execution, at each new level in the context, the next used cell is not on the right of the previous one but starts on a new row. Do you have an idea what I am missing ? Can it be an issue with Excel 2013 or because my SAP version is not at the same level as yours ? Mine is a 7.02.
Thanks for you reply
Laurent
Hi Laurent !
3) Version of SAP or Excel does not matter in this case. I can assume that “Merge cells” option of your Resizable pattern now is switched to “Span columns” mode (instead required “Span rows”). But, to be more specific, I have to see your form’s structure. You can send me screenshot or your downloaded form at email igorborodin.55@gmail.com .
1) and 2) XLSX Workbench is designed just for creation of static printing forms.
Unfortunately, at the moment it does not support any calculations, embedded objects, external data sources etc. including pivot tables. All calculations has to be performed in the printing program. Maybe I will do pivot tables support in the future, but not now.
Component “Drawing” now supports only Pixel image (Excel menu: Insert tab > Illustrations group > Picture), and Vector drawing (Excel menu: Insert tab > Illustrations group > Shapes) and does not support Charts.
Igor.
Hi Igor,
3. I have sent you an email today with my Form details.
2. & 1. Embedding Pivot table and chart would be great. I hope you can do it in a near future.
Thanks
Laurent
Laurent, I have answered you an email.
Igor.
Hi Igor,
Huge improvement of memory consumption for large file with Version 2.07.
Thanks a lot
Laurent
really cool stuff..Thank you Igor…I’ve downloaded and its working fine
Hi Igor,
Thank you very much. This is really a wonderful tool.
I tried few examples, it worked 🙂 .
Can we control the color of any line in excel based on the data from context. Say if I get an error line item, can I make that line red in excel generated. Basically, my doubt is like can we control something based on data we get from context.
Thanks in advance 🙂
Regards,
Raja
Hi, Raja ! Of course you can!
(a) First, you have to add two fields (type char1) into context (table line level). This fields should be named WHITE_ROW and RED_ROW, for example. To show red-color row, pass ‘X’ to field RED_ROW in the printing program. Otherwise, pass ‘X’ to field WHITE_ROW.
(b) Secondly, prepare patterns for white-colored row (already exists in your form) and red-colored row (just copy from white row and modify it). In the item ‘Appearance at runtime’ of Properties tab, assign pattern to appropriate context field from (a).
In addition, see using of ‘Appearance at runtime’ in the examples 3.2.08a and 3.2.10 .
Regards,
Igor
Hi Igor,
I followed example 3.2.10 and it seems to be working.
Thank you very much for your support.
Regards,
Raja
Hi,
very nice work. I start to use it and it is fine. I have a one question to the functionality. Is it possible to have the output excel sheet protected with password? Because when I am trying to lock the sheet in template, the output sheet is not protected. Right now I am running version 2.09.
Regards,
David
Hi, David, see parameter IV_PROTECT in the FM ‘ZXLWB_CALLFORM’
Regards,
Igor
Hi,
thank you very much. I didn’t notice this parameter. It is working like a charm now.
Regards,
David
This is an outstanding piece of work.
Will have to try it when I have some spare time (I hope that moment is in the near future 🙁 ). I don’t know if you created it all by yourself or with a group of people but in any case I suppose it took a lot of time.
Congratulations Igor!
Thank you Alejandro.
Hi Igor,
This is a great development. Well done.
I am displaying an EAN number from MEAN-EAN11 onto the Excel sheet and instead of an 18 digit number like 1234567890000, it is being displayed in scientific format like 1.1223456E+12
I can manually set the format of the cell to ‘number’ which corrects the display, but I’m wondering if this can be done in the tool? I tried setting the format in the XLWB but it didn’t seem to stick.
Thanks,
Sap
Hi, try to set the format of the cell to ‘text’ in the Excel template.
Regards,
Igor
Hi,
You mean change the format in the XLWB? Yes, I changed the format of the cell to Text, saved and activated but it seems to revert back to “general” when the file is opened.
Thanks,
Sap
It is very strange that cell format is different in design-time and runtime… are you sure? what XLWB version are you using? ms office version?
As fast solution, you can insert leading space to the ean number, this should help.
Regards,
Igor
Thanks – the “space” idea seemed to have worked.
I changed the field to ‘text’ in XLWB and activated. When opening the Excel, it’s back to ‘general’. I have Office 2013 and XLWB 3.04
Thanks,
Sap
Hi,
Would there be any reason why the toolbar (containing the Info, Activate buttons, etc.) would suddenly not be visible anymore? I debugged and can see the init_appltoolbar( ) method is being called, with no errors.
The ‘top’ toolbar is now the buttons to create a component etc. so I can’t save/activate etc. a form anymore…
I also tried creating a brand new form, but the buttons still do not appear.
Thks.
Hi, try to restart SAP GUI. It’s cl_gui_gos_container layout bug of SAP GUI, not program error.
Regards, Igor.
Hi,
Thanks, it reappeared!
I also had to change the Theme to the “SAP Signature.”
Hi,
Great development!
I added a new field to the structure that the form is based on, and I can’t seem to display it in XLSX. When I click on’value’ for a field, it shows me the ‘old’ structure – is it possible to ‘refresh’ this so the new structure gets pulled in?
Thks,
CO
Hi Charles! Just save, close and open the form again!
Regards, Igor.
Thanks Igor.
I had to open and close it a few times before the structure became visible for some reason, but it’s working fine now. Thanks!
Hi IGOR,
Nice work. I have a question though. I have a smart form in message control and want to convert that output form into XLS . Is that possible with this tool?
Thanks and Regards
Nimisha Chawak.
Hi Nimisha. What exactly do you mean by ‘smart form in message control’?
You have to build a form using XLSX Workbench. You can use a printing program of Smartform, and, perhaps, interface parameter as context for XLSX Workbench form (if interface parameter is just one).
Regards, Igor.
HI,
I have a shipment and a output type . To the output type they have a smart form attached. In todays worls they justy have a printout from the output type. But now they also want an email and attachment as xls.
In the smart form they have 2 internal tables been printed.
Question here is , is there a way to use this tool to convert the current smart form into excel.
Thanks and Regards
Nimisha.
No, this tool is not for converting forms, it is for creating forms.
Regards, Igor.
Hi, Kiran! Did you check this example ? It works well for me.
Regards, Igor.
Great tool!! But there’s a way to import data from excel to internal table?
Or maybe a suggestion to perform an import like the tool does an export.
Thanks
Simone, unfortunately this tool is only for export data to excel, not for import. But there are many ways for importing excel data, google to the rescue 🙂
Best regards, Igor
Hello Igor,
Thank you for an article! We’ve installed it and will check how it works:)
P.S. By the way, did you consider moving your project to GitHub in order to control versioning / track the issues more easily? In case, you would be interested in that, you might also consider using (and contributing:) to a dedicated tool abapGit, that might be used to integrate development environment in SAP with GitHub.
Hello Bohdan, Thank You. About GitHub: I had thought about it, but I decided that centralized development provides more order and quality documentation, than multi-fork development.
Best regards, Igor
Very awsome job. Thanks Igor, it solve my problem. I had to install it by myself, without saplink and had no issues.
Very professional tool.
Thank You, Klaus.
Best regards, Igor
Hi guys, version 4.04 is ready. Now it supports .XLSM file format and embeded VBA-macro (of course, only in dialogue runtime mode). Read more .
Best regards, Igor
Can I have password protection for excel itself means the data should not be visible until password is entered.
Encrypt with Password option I am not able to use the same.
Hi Rohan. For whole Workbook protection You only able to use VBA or VBScript or DOI (see example with VBA/ DOI).
Best regards, Igor.
Looks very nice. I want to have a try but fail to download the nugg file because of security check of the browser. Where else can I download the installation file? Thank you!
BR, Eric Bu
Hi Eric. The nugg file is stored only on the official site. But You can send me email (see here) and I will reply You with nugg file attachment.
Best regards, Igor.
Nice work , Thank you for sharing .
And is it can print data to the excel which has more than one sheet?
Best regards, jasen.
Hi Jasen,
Yes, it allows you output data to several sheets (see this example).
Best regards, Igor.
Recently I have been studied the user guides for a while.
And I have a question that it seems not support edit the files that import from my local pc?
Best regards, bella.
Hi Bella,
I need more details about it. XLSX Workbench definitely has to support imported files, but only if they are correct (has XLSX or XLSM extension).
Best regards, Igor
Awesome and very useful project! Thank you for your work.
I have a question regarding password protection. In the popup to select the password it talks about a hash function for each option… would it be possible to set a specific password? Ideally a field from context could contain it, but in my case, I would just need a specific fixed option.
Thank you & Best regards.