Igor Borodin

April 22, 2014 2 minute read

XLSX Workbench for SAP(ABAP) – tool for exporting data to Excel

PROJECT LINK:

XLSX Workbench for SAP

YOUTUBE CHANNEL:

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

85 Comments

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

Former Member

April 23, 2014 at 6:20 am

Nice stuff......

Igor Borodin

Blog Post Author

April 23, 2014 at 7:09 am

Thank you for your interest in my work. Did you try to use it?

Former Member

April 23, 2014 at 9:48 am

I will try it and update you....

Former Member

May 12, 2014 at 9:26 am

I was looking a long time for a tool like that... i will try it.

Igor Borodin

Blog Post Author

May 12, 2014 at 12:01 pm

write your opinion after you have tried, please!

Former Member

May 13, 2014 at 2:03 pm

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

Igor Borodin

Blog Post Author

May 13, 2014 at 2:38 pm

Hello, Wolfgang.

XLSX Workbench provides graphical UI and uses visual approach (like SMARTFORMS, for example).

Best regards, Igor.

Ivan Femia

May 13, 2014 at 3:39 pm

Does it support reading functionality to upload and edit excel files?

Igor Borodin

Blog Post Author

May 14, 2014 at 6:25 am

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.

Ivan Femia

May 14, 2014 at 12:59 pm

I'm curious to install your tool it seems interesting...

Thank for sharing!

Ivan

Former Member

May 13, 2014 at 3:52 pm

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?

Igor Borodin

Blog Post Author

May 14, 2014 at 10:18 am

Jozef, thank you for what you notice! I updated the nugget. Check now, please!

Former Member

May 14, 2014 at 10:54 am

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.

Igor Borodin

Blog Post Author

May 14, 2014 at 11:36 am

About SAPLINK - I agree with you. And I have corrected installation instruction, thank you!

Ivan Femia

May 14, 2014 at 1:13 pm

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 www.saplink.org

Ivan

Igor Borodin

Blog Post Author

May 14, 2014 at 1:30 pm

Ivan, thank you for links! I will update the nugget using the latest version of SAPLINK. Igor.

Alexander Likavcan

June 3, 2014 at 3:24 pm

Hi Igor,

really nice work, just started to test and I'm really amazed.

Thanks for sharing.

Igor Borodin

Blog Post Author

June 4, 2014 at 6:41 am

Hi, Alexander, thanks for feedback.

Igor Borodin

Blog Post Author

June 30, 2014 at 12:58 pm

Hi all !

New version is available. Incompatibility issue with Excel 2013 was fixed.

Former Member

July 16, 2014 at 3:00 pm

what an amazing work! is this free to use 🙂

Igor Borodin

Blog Post Author

September 15, 2014 at 7:56 am

yes of course it's free

Igor Borodin

Blog Post Author

September 1, 2014 at 1:50 pm

New version is available.

Merging cells dynamically is supported now. Here is a new component description. Here and Here are examples of use.
In addition, some other improvements.

Bruno Esperança

September 1, 2014 at 2:14 pm

This looks nice, thanks for sharing.

I'll try to test this when I have some spare time!

Best,

Bruno

Igor Borodin

Blog Post Author

September 2, 2014 at 6:33 am

Hi Bruno, thank you! I would like to have your feedback after test!

Igor Borodin

Blog Post Author

October 14, 2014 at 10:45 am

Hi all, another update.

Export ALV-Tree to Excel with outline hierarchy. Lots of layout and formatting options.

Former Member

November 2, 2014 at 5:19 pm

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

Igor Borodin

Blog Post Author

November 7, 2014 at 3:42 pm

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.

Former Member

November 9, 2014 at 2:48 pm

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

Igor Borodin

Blog Post Author

November 11, 2014 at 2:40 pm

Hi Laurent, I have released a new (2.2) version.

Thank you, but unfortunately class cl_openxml_helper isn't available in my system...

I agree with you, that hard coded hex lines seen as a blackbox and it's not better solution. I have used it only for minimizing number of objects that have to be installed.
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& .
If you want to upload XLSX-template from frontend, see here. But I don't recomend to do it. Because the uploaded file may have invalid format or it may contain unwanted embedded objects, external links etc, that can cause an error while compiling the resultant file .

Igor.

Former Member

November 15, 2014 at 7:13 pm

Hi Igor,

Thanks for your changes in 2.02 version. It works fine.

Former Member

November 25, 2014 at 10:54 pm

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

Igor Borodin

Blog Post Author

November 26, 2014 at 9:24 am

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.

Former Member

December 24, 2014 at 5:45 pm

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

Igor Borodin

Blog Post Author

December 25, 2014 at 12:16 pm

Laurent, I have answered you an email.

Igor.

Former Member

February 22, 2015 at 3:22 pm

Hi Igor,

Huge improvement of memory consumption for large file with Version 2.07.

Thanks a lot

Laurent

T Fdo

February 24, 2015 at 4:45 pm

really cool stuff..Thank you Igor...I've downloaded and its working fine

Former Member

May 6, 2015 at 1:22 pm

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

Igor Borodin

Blog Post Author

May 7, 2015 at 7:43 am

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

Former Member

May 7, 2015 at 8:08 am

Hi Igor,

I followed example 3.2.10 and it seems to be working.

Thank you very much for your support.

Regards,

Raja

David Planansky

June 2, 2015 at 11:13 am

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

Igor Borodin

Blog Post Author

June 2, 2015 at 11:30 am

Hi, David, see parameter IV_PROTECT in the FM 'ZXLWB_CALLFORM'

Regards,

Igor

David Planansky

June 2, 2015 at 12:42 pm

Hi,

thank you very much. I didn't notice this parameter. It is working like a charm now.

Regards,

David

Alejandro Bindi

November 24, 2015 at 7:25 pm

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!

Igor Borodin

Blog Post Author

November 25, 2015 at 8:35 am

Thank you Alejandro.

sap manie

February 29, 2016 at 2:55 pm

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

Igor Borodin

Blog Post Author

February 29, 2016 at 3:42 pm

Hi, try to set the format of the cell to 'text' in the Excel template.

Regards,

Igor

sap manie

February 29, 2016 at 3:52 pm

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

Igor Borodin

Blog Post Author

March 1, 2016 at 9:15 am

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

sap manie

March 2, 2016 at 4:45 pm

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

sap manie

March 29, 2016 at 2:55 pm

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.

Igor Borodin

Blog Post Author

March 29, 2016 at 3:16 pm

Hi, try to restart SAP GUI. It's cl_gui_gos_container layout bug of SAP GUI, not program error.

Regards, Igor.

sap manie

March 31, 2016 at 4:03 pm

Hi,

Thanks, it reappeared!

I also had to change the Theme to the "SAP Signature."

Former Member

March 29, 2016 at 11:25 am

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,

Igor Borodin

Blog Post Author

March 29, 2016 at 11:53 am

Hi Charles! Just save, close and open the form again!

Regards, Igor.

Former Member

March 29, 2016 at 1:55 pm

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!

Former Member

June 30, 2016 at 3:14 pm

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.

Igor Borodin

Blog Post Author

June 30, 2016 at 3:53 pm

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.

Former Member

June 30, 2016 at 4:34 pm

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.

Igor Borodin

Blog Post Author

June 30, 2016 at 5:18 pm

No, this tool is not for converting forms, it is for creating forms.

Regards, Igor.

Igor Borodin

Blog Post Author

July 11, 2016 at 11:39 am

Hi, Kiran! Did you check this example ? It works well for me.

Regards, Igor.

Simone Cattozzi

December 23, 2016 at 8:22 am

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

Igor Borodin

Blog Post Author

May 30, 2017 at 10:15 am

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

Bohdan Petrushchak

February 23, 2017 at 1:21 pm

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.

Igor Borodin

Blog Post Author

May 30, 2017 at 10:17 am

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

Klaus Spassin

April 25, 2017 at 1:37 pm

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.

Igor Borodin

Blog Post Author

May 30, 2017 at 10:18 am

Thank You, Klaus.

Best regards, Igor

Igor Borodin

Blog Post Author

May 30, 2017 at 10:26 am

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

Former Member

November 1, 2017 at 11:50 am

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.

Igor Borodin

Blog Post Author

November 7, 2017 at 1:40 pm

Hi Rohan. For whole Workbook protection You only able to use VBA or VBScript or DOI (see example with VBA/ DOI).

Best regards, Igor.

Changxing Bu

December 12, 2017 at 6:31 am

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

Igor Borodin

Blog Post Author

December 28, 2017 at 8:01 am

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.

文远叶

September 17, 2019 at 12:55 am

Nice work , Thank you for sharing .

And is it can print data to the excel which has more than one sheet?

Best regards, jasen.

Igor Borodin

Blog Post Author

September 19, 2019 at 9:07 am

Hi Jasen,

Yes, it allows you output data to several sheets (see this example).

Best regards, Igor.

YI SHEN

February 14, 2020 at 8:16 am

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.

Igor Borodin

Blog Post Author

September 3, 2020 at 8:47 am

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

Victor Gonzalez Mereulo

October 20, 2020 at 7:25 am

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.

Igor Borodin

Blog Post Author

January 19, 2021 at 2:55 pm

Hi Victor,

Unfortunately, there is no option to specify password via context because of no library for converting password to hash.

Best regards, Igor

Jixin Wang

January 19, 2021 at 6:54 am

Hi,Igor,

It's an amazing tool comparing with ole/doi/xslt transformation, then I am confused about the filename when to be generated, when i click saveas button,it popup a window with filename just like ~SAP{67EF95E3-E2F7-4EF3-B4A8-9E7C84E06365}.xlsx, I spent a lot of time on finding how the filename is be generated. I need your help. thank you.

Igor Borodin

Blog Post Author

January 19, 2021 at 3:07 pm

Hi Jixin,

This is initial file name generated by SAP GUI. If You need another default name when saving, just use User-enhanceability of Viewer .

You can redefine SaveAs function using callback program, something like:

*&———————————————————————*
*&      Form  CALLBACK_FORM
*&———————————————————————*
FORM callback_form
         USING pv_event     TYPE char50
      CHANGING cv_fcode     TYPE ui_func
               cr_toolbar   TYPE REF TO cl_gui_toolbar
               cv_rawdata   TYPE xstring . ” excel document as hex-string

TYPE-POOLS:
  abap .

CASE pv_event .
*=======================================================================
  WHEN ‘FUNCTION_CODE’ .    ” process function codes
*=======================================================================

    CASE cv_fcode .
      WHEN ‘APPL_SAVEAS’ .  ” redefine SaveAs function
        CLEAR cv_fcode .    ” clear function code to disable predefined logic

*         get DOI object
          DATA:
            lo_container_control TYPE REF TO i_oi_container_control,
            lo_document_proxy    TYPE REF TO i_oi_document_proxy,
            lo_spreadsheet       TYPE REF TO i_oi_spreadsheet.

          PERFORM viewer_get_doi_object
               IN PROGRAM saplzxlwb
                 CHANGING lo_container_control
                          lo_document_proxy
                          lo_spreadsheet .
          CHECK lo_document_proxy IS BOUND .

*         display save dialog window
          DATA:
            lv_filename           TYPE string ,
            lv_path               TYPE string ,
            lv_fullpath           TYPE string ,
            lv_fullpath_char      TYPE c LENGTH 10000 ,
            lv_result             TYPE i .

          cl_gui_frontend_services=>file_save_dialog(
            EXPORTING window_title        = ‘Save file as’
                      file_filter         = ‘XLSX-Workbench Form (*.XLSX) |*.XLSX|’
                      prompt_on_overwrite = abap_off
                      initial_directory   = ‘C:\temp\’
                      default_file_name   = ‘Your default file name’
            CHANGING  fullpath            = lv_fullpath
                      filename            = lv_filename
                      path                = lv_path
                      user_action         = lv_result ) .
          CHECK lv_result EQ 0 .
          CHECK lv_fullpath IS NOT INITIAL .

*         save file
          lv_fullpath_char = lv_fullpath .
          lo_document_proxy->save_copy_as(
            EXPORTING file_name   = lv_fullpath_char
                      prompt_user = abap_off
                      no_flush    = abap_off ) .
      WHEN OTHERS .

    ENDCASE .

ENDCASE .

ENDFORM .                    “CALLBACK_FORM

Jixin Wang

February 1, 2021 at 12:00 pm

I am sorry to ask you again when i want to change the filename at inpalace='' mode(IV_VIEWER_INPLACE = '' and IV_VIEWER_SUPPRESS = '' ), we use xlwb in alv, every row is an employee resume, the key-user double click the row it triggered openning a excel with employee name instead guid.

I also study user-enhance. I debug the source. I don't find the relation between METHOD call_floating_form (Version5.00/line 35667) and PERFORM viewer_get_doi_object
IN PROGRAM saplzxlwb.

I have done it when IV_SAVE_AS = 'C:\xxxx\name.XLSX ',after downloading I use cl_gui_frontend_services=>execute().

Could I implement it using user-enhance. thank you.

Igor Borodin

Blog Post Author

February 3, 2021 at 5:35 am

Hi Jixin,
Last time I understood you need to change default file name when you press 'Save As' Button in viewer. But if You need to directly save file at certain name and then open it, You have done it right way.

Best regards, Igor.

Jixin Wang

March 17, 2021 at 3:56 am

Hi, could you give me some more examples on cells with special format. I have read the example "DYNTABLE_2", and the example is for dynamic excel. after that I used format in workbench.

it shows errors that the format cells must be in the area of pattern(filling data area). I also confused how to use format focus on pattern in a loop. sometimes we need to write data in a loop. but some rows have different format, just like font, left-aligned.etc.thank you...

Anton Gladkiy

March 18, 2021 at 12:52 pm

Hi,
please, clarify message text, that has been shown (I can't identify it).

The idea is:
You can dynamically decide that: has Pattern to be shown or hidden (in runtime) using option below:

Here FORMAT_1 is context field (for example, type char1) placed in row of the contect table.

If You pass 'X' to field FORMAT_1 in the printing program, than the pattern will be shown (but if set space, pattern will be hidden). Thus FORMAT_1 is context field for defining appearance of pattern.

You can create, for example, three patterns (with different format) and associate each of them to corresponding context field for defining appearance (FORMAT_1/ FORMAT_2/ FORMAT_3). After that, You can fill field FORMAT_1 for first row, FORMAT_2 for second row and FORMAT_3 for third row of context table. As result, first 3 row of grid will be output in various format.

Best regard,
Igor

Kiran Kerudi

March 22, 2021 at 4:31 am

Hi Igor,

Thank you for sharing such an amazing tool!

Since SAPLink is succeeded by abapGIT, are there any plans to migrate this to GitHub? If not, could you please let me know how I can take care of future releases/changes?

Thank you again!

Best Regards,
Kiran

Igor Borodin

Blog Post Author

April 7, 2021 at 7:50 am

Hi Kiran,
I will I'll think about abapGIT, thank You.

Best regards, Igor

XLSX Workbench for SAP(ABAP) – tool for exporting data to Excel

RELATED PAGES:

IDEA

Assigned Tags