Creating Excel the Java way

[rosenberg_eitan]

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.

When you run the program you will be prompted for "My folder" this also needs to be a shared folder accessible from SAP .

All the paths needs to to be in \\host\directoryname structure (Universal Naming Convention) .

Thats all for now. have fun....