In July 2010, SAP Mentor Ivan Femia released the first version of the open source project abap2xlsx that allowed ABAP developers to generate professional spreadsheets from scratch with all the advanced features that stand alone Excel editors provide like: style and cell data formats, drawings support, conditional formatting, formulas, and so on.
This project has improved the user experience compared to excel generated using SAP standard components (such as ABAP ALV, WebDynpro ALV, System menu, etc.) and it has made ABAP developers more freedom adding, for example, the ability to generate an excel from non dialog processes and to include the generated excel as email attachment. At last but not least, this framework allows the use of spreadsheets in an interactive way, making possible to read them via ABAP for further processing.
Time after time, more and more SDN community members joined into the project and improved its functionalities. The abap2xlsx project reaches a large number of contributors, so today it is almost time for the new release that will include many new features such as the use of template files for charts, very hidden sheet, many bug fixes and so on. In this blog we will focus on one of the most important news: the ability to create custom charts from scratch!
Yes, that’s right: with just a few simple ABAP code lines, you can create business graphics within the excel file and send it, for example, to many iPhone/iPad via a push notification or via an html email (as weekly summary for example).
Another interesting way to use abapxlsx charts is to generate them using abap and then catch them as an image in order to show the chart in a WebDynpro or to send it directly to a mobile device! You believe that this is not possible? It is not easy, but you can do it following these steps:
Generate the excel file with the chart as you wish via ABAP using abap2xlsx
Add a simple macro to the excel via ABAP using abap2xlsx in order to enable the excel to export the chart in an image file like this:
Write a simple vbs file via ABAP in order to run the image generation like this:
Save the excel file and the vbs on the SAP NetWeaver Application Server filesystem via ABAP
* Opening the File
OPEN DATASET lv_filename FOR OUTPUT IN TEXT MODE
IF SY-SUBRC NE 0.
WRITE: ‘File cannot be opened. Reason:’, lv_msgtxt.
ENDIF.* Transferring Data
LOOP AT lt_table.
TRANSFER lt_table-line TO FILENAME.
ENDLOOP.* Closing the File
CLOSE DATASET lv_filename.
Run the saved VBScript file via ABAP
v_command = ‘cscript.exe test.vbs’.
CALL ‘SYSTEM’ ID ‘COMMAND’ FIELD v_command.
The chart image has been generated! Get it from the Application Server filesystem via ABAP and display or send it!
Today we would like to present you yet another Excel add-in that we created for one our client. This is another implementation of the tool to build and transfer Excel charts into PowerPoint presentations.
The tool is quite universal so you could easily adapt yourself to use it.
The solution is quite simple and it uses at maximum the natural capabilities of Excel.
So, imagine you have a huge set of data which you have to analyse somehow, create charts on separate subsets of data and transfer them into PowerPoint presentations to then submit them to your clients. With this add-in you are actually supposed to have
– a table with data
– an area where you can insert row numbers
– an area that is filled dynamically with data by means of formulas and that takes a row number as an input
– sheet charts that have as data source the before mentioned area
Today we would like to present you another solution that we started to use in our company. This is a tool for automatically building ARROW CHARTS!
After spending some time searching for ideas or similar tools in the Internet we found nothing that would perfectly fit our needs, thus, we created our own solution. 🙂
To make the story short, this is how it works:
The tool works with a 4-column table of data where each row starts with a name of the series. An XY chart will be created (or you may use any already created XY chart). Only four columns after the title column are considered. The tool will use the first 2 data columns to put the point and the arrow from the second point will point to the first one. 🙂 (I hope I made myself clear…)
The current version does not work correctly in Excel 2007. In fact, it works perfectly well in Excel 2003. So, make sure you updated your MS Office software. Since this is our tool for internal use, we did not test it on Office 2002 and Office 2000.
Soon we will introduce the support for Office 2007.
This tool can become very useful for the companies which conduct marketing research and need to build dozens of arrow charts. Since Excel does not provide tools for doing that automatically the work is typically done manually. From now on we make Excel do it for us!!! 🙂
Feel free to contact us with any questions, suggestions and ideas about this topic.
Traditionally, you can find this solution on our web site www.yRiver.it
(ArrowCharts.exee [the file you download is a self-extracting archive; change its extension from .exee to .exe])
where in the category OUR PROJECTS
you can also find other projects, solutions, ideas and concepts. 🙂
I would like to present you a project of mine that shows how it’s possible to load data into excel from an external data file and create dynamic charts.
Very interesting is the possibility to upload data with a simple browsing window. (only 1 code line)
Dim sFileName As Variant
sFileName = Application.GetOpenFilename(“Text Files (*.txt), *.txt”) ‘can add parameters. See help for details.
If sFileName = False Then
MsgBox “No data file selected.”
Else …File parsing logic…
Ask me for any futher information about this tool.
today we will present you one more small solution to simplify your life in working with Excel. This is an Excel add-in that will enable you to build charts based on one selected.
Let me explain its use on an example. Assume that you have a huge table of data (say, some 50 000 rows). When you decide to build a chart based on this table of data you will probably want to build one chart per one row since it is the only feasible way of presenting data graphically in such a case. If you decide to do that manually (even if you totally need “only”, say, 100 charts) it will take A LOT of time!
So, this add-in will help you create charts based on the selected range of rows. You have to define the template chart.
The current implementation allows you to build charts with only one series. Though, the number of series could be expanded on demand.
Below you can see the video demonstration of how it actually works: