Formatted Excel Process
The dictionary defines automation as “the technique of making an apparatus, a process, or a system operates automatically.”
We define automation as “the creation and application of technology to monitor and control the
production and delivery of products and services.”
The automation profession includes “everyone involved in the creation and application of technology to monitor and control the production and delivery of products and services”; and the automation
professional is “any individual involved in the creation and application of technology to monitor and
control the production and delivery of products and services.”
My new development or tool is a process which tries to automate the data download option in such a
way that would reduce the post download work.
A custom program used to develop in system with the logic provided by the functional people. After the development is moved to production system, final user either executes the same in foreground to get the data or if it takes more time, then based on the input they execute the same in background.
Later they download the data in excel file and do different kind of color formatting before sharing the
same with higher managements based on different conditions.
This new tool imbibed with the auto color formatting process which minimizes the earlier manual effort in excel file.
Means, user now doesn’t need to download the as reported data as excel file in their local desktop /laptop to do color formatting.
Process Output and Information:
By using this custom tool in custom reports, a new option “XL Option’s” will display in tool bar.
Which contains below functionality
1. Condition Color
4. Save Variant
5. Apply Variant
Except above options, there are three more options added in context menu.
By using any of these options, a color can be add in selected row, column or cell.
The added color can be changed to another color or can be removed from respective selected cell, row or column.
Color option selection
First time when color selection pop-up window will open, then one will have option to choose either
from excel or ALV.
SAP ALV only supports 7 different colors to apply in output cells.
Here this new tool provides an option to user to select whether use given 7 colors to apply in output ALV for further process or default a color will apply in cells, but internally the selected color will update which will apply only when download or send the output as file.
This is one-time selection, once any of the respective option selected, that will applicable for all color selection processes.
For example, if 7 Colors of ALV option is selected for further process.
After selection of the color, system will update selected color in ALV output.
And if XL Color option is selected,
Then after selection of any color, system will update a default color in ALV
Note: The default color can be changed with the help of developer while programming of the respective method from class ZCL_PRV_OLE_PRO.
By using this option user can set colors on particular cells or rows based on the conditions. It’s same as query option on displaying output.
The below pop-up window will open once this option is selected.
Above window contains output field information, respective values with F4 help and applying color
condition on cell or rows.
For example, select “Max. Data Length” column from fields drop down
And select greater then and less then information from Condition button.
In value field, by using F4 help request, system will display a pop-up screen, which will only contain the values from current output based on selected condition field.
The final query screen is displayed below,
By using below option, user needs to select a color option to apply color on build query.
On Selection of OK, build query will execute and apply color on respective rows or cells.
By using this option, displayed output will get downloaded in local system with formatted excel file
based on applied color.
This option provides an instant mail to single or multiple mail ids which can also includes condition
information into mail body.
Above pop-up window will open to provide additional information with mails ids to send current output as attachment in mail.
Here mail Subject and attachment name is mandatory fields.
By selection of “Send Color Condition” option, received mail will contain condition information in mail
body to provide more details on applied color on attached excel file.
Output from SOST t-code.
By using this option, user can save applied condition with selected color information as variant for future use for respective program output.
Below pop-up window will open, which contains the applied condition information on current output.
A valid variant name has to be enter to save the applied condition for future use.
By using this option, already saved variant can be applied on current output based on executed program.
Displayed above pop-up screen will open for selection of any of the existing variant.
Note: Some reports may have different output based on conditions, in such scenarios, if selected variant information is different than current output. Then an error message will display for selecting valid variant based on current output.
Color would be applied based on selected variant, means if saved variant color used by 7 Colors of ALV, then while applying variant condition, system will update the colors from cells or rows based on 7 colors of ALV and the output will be same as while saving the variant.
But if saved variant is based on another color option (Excel), then system will display the default color in ALV output.
Configuration for Background Jobs and mail
Here some more options has been provided to use formatted excel file for background process.
Developed a program to display executed background report in ALV report, user can use T-Code
To Display background information user needs to enter date and user id.
Program and job name is optional input. On execution of the process below output will display.
On selection of any of the finished background job the outcome will display as output in ALV format.
Excel option tool will be available for instant use.
Map Background Excel Process
By using program ZPRV_BG_MAPPING (Create T-Code as per your choice), user can map the background job information with saved variant and other information like map content and mail ids.
To add or update mail content information, select option, a new pop-up window will open to insert or update existing mail content information.
Added information will be as mail body while sending output of mapped background job with selected
By selection of “Send Condition” option, mail body will display information with color code based on
applied conditions on fields.
To add new or modify existing mail id, use option. A new pop-up window will open to add or modify existing mail id. The formatted excel file, will send based on maintained mail id for the respective mapped information.
Activate / De-Activate Background Excel Process
By activating the process, a program will set in background process based on the entered time on a daily basis.
It will check the Mapping information for background process, program and other information. If any of the background job information is found for the execution date, then it will fetch the report data, apply the selected variant and send to maintained mail id’s with related information.
Follow the below link to download nugget file of this development.
Link contains 2 files:
- Formatted Excel Development Steps
Formatted Excel Development Steps document file contains below information.
Function Group: ZPRV_OLE_PRO
- ZPRV_DISPLAY_ALV_COLOR (Display 7 colors of ALV grid)
- ZPRV_OLE_CONDITION_COLOR (Fill Color Based on condition)
- ZPRV_OLE_GET_COLORS Get (Color Window to select color)
- ZPRV_OLE_GET_XL_COLOR (Get Color Selection from Excel)
- ZPRV_OLE_SAVE_VARIANT (Save variant based on made condition)
- ZPRV_OLE_SELECT_VARIANT (Display Variant to Select)
- ZPRV_OLE_SEND_MAIL (Send mail)
- ZPRV_SELECT_COLOR_OPTION (Select Color Selection option)
Design and Developed by
Praveer Kumar Sen
Are you using ABAP2XLSX.to download and/or email the data in XLSX format?
The reason I ask is that I saw the term OLE mentioned in the name of own of your Z classes above. That could of course just the name you choose and it does not refer to the OLE technology that can be used to download SAP data to Excel.
OLE is really slow compared to ABAP2XLSX. Anyway, ABAP2XLSX does all the things you talk about above i.e. conditional formatting, emailing in the background.
If that is the technology you are using, all well and good, forget i said anything. It is just that every three or four months I see a blog on SCN where someone has invented a new way to download SAP data to Excel, unaware there has been a really good way to do this, open source, for many years now.
This totally ties in with how you start your blog - users often use the standard SAP way to download data into Excel and then spend ages formatting it, adding graphs, even little things like changing the layout to landscape.If the SAP program can do this for them then it is a real time saver - you can even have the ABAP2XLSX generate pretty graphs and pie charts dynamically, based on the data in the spreadsheet.
Yes, I have used ABAP2XLSX functionality, but still there is OLE method exist (to understand OLE concept) on developed ZCL_PRV_OLE_PRO class, which can be used to download small amount of table data through OLE method.
Nice presentation - gray screenshots are a little bit too dark in my opinion. Is your tool downloadable somewhere? Thanks.
Yes, where is the tool itself? I'm confused...
Sorry for delay response, link to download the developed tool has been updated in blog.