Creating Excel the Java way

Hi all,

I would like to share with you a some code development .

There is a constant request to generate Excel report from SAP .

As far as I know there is no built in support to do that from ABAP.

There is also the requirement to run the whole show in back ground job .

The regular “solution” is create html files, CSV files etc.

There is ABAP project http://wiki.scn.sap.com/wiki/display/ABAP/abap2xlsx but not every
organization would like to go this way.

Since I use Java as a hobby I thought I will try and use java to do the job .

The code supplied here is supplied as is. Try it and see if it is good for you.

Steps

Create XML using ABAP .
The XML will contain meta data and instructions to the java program.
Write the XML to a file .
Call Java as external command .
The Java program will parse the XML file and generate the Excel file.

Open source projects used

Apache POI – http://poi.apache.org/ – the Java API for Microsoft Documents
This is main work horse… it will be used to generate the Excel files .
At the time of writing I did not utilize the full potential of this project. It is full of goodies that
are worth exploring…(I plan to try and use the “Formula Support” ).

Apache Commons CLI – http://commons.apache.org/proper/commons-cli
parsing command line options .

Apache Commons Lang – http://commons.apache.org/proper/commons-lang/
StopWatch.

Required jars from the projects (Already included in XmlFileToExcel.jar )

commons-cli-1.2.jar
commons-lang3-3.1.jar
dom4j-1.6.1.jar
poi-3.9-20121203.jar
poi-ooxml-3.9-20121203.jar
poi-ooxml-schemas-3.9-20121203.jar
xmlbeans-2.3.0.jar

The environment
Java Editor – Eclipse – http://www.eclipse.org/

The Java code is located here: https://drive.google.com/folderview?id=0B6Cb7sgVnODWNV9DS29kQXdPblE&usp=sharing

The reason for putting the code here is because of the file types involved.

(If someone can point me to a place within scn.sap.com I will be grateful…)

XmlFileToExcel.zip – The whole set of source code is in a this zip file this way the directory structure of the Java project is preserved.

XmlFileToExcel.jar – This is the “.exe” equivalent of Java .
                     This file contain all the required jars from the projects .
                     The jar was created by eclipse using the “Runnable JAR File Exporter”
                     Class main.Main is the start class for this program.
                     This file is actually a zip file with extension of “jar”.

Y_R_EITAN_TEST_40_02.xml – Sample XML created from SAP .

Y_R_EITAN_TEST_40_02.xlsx – Sample Excel generated by the java program .

Java Setup

Create in some shared folder accessible from sap the following:
- A folder with the name “jre” this will contain the “Java Runtime Environment”.
- – Install java jre on your PC .
  http://www.oracle.com/technetwork/java/javase/downloads/jre7-downloads-1880261.html
  – Note the folder where you install the jre (Usually C:\Program Files\Java\jre7 )
  – Copy folder “jre7” to folder “jre” .
- Create a batch file named Y_JAVA_1.bat
  The content of the file:(note the “\bin”)
  path \\<path to jre>\bin
  java.exe -jar %1 %2 %3 %4 %5 %6 %7 %8 %9
- Download XmlFileToExcel.jar and put it next to Y_JAVA_1.bat .

The folder will look like this:

SAP setup (Simple….)

program:( Y_R_EITAN_TEST_40_02 source included)

Upload the source to SAP and activate .

The program use table sbook as input (On our site it contain 100000 records)
The program read the selected records (FORM get_data_1) .
The program generate and write XML file (FORM write_1_to_xml) .
The program Execute an External Command and call java (FORM write_2_to_excel) .

External command

Use Transaction SM69 and add “Y_JAVA_1” as external command.

Y_JAVA_1 will call the batch file Y_JAVA_1.bat with the required parameters.