Tag Archives: Java

Reading Excel Spreadsheets from MCP

I am currently working on an EAE project where we have some data in Excel spreadsheets that need to go into a DMSII database. In this case the spreadsheet data are only read once and after that maintained in DMSII. Here it is easy enough to save the spreadsheet as a CSV file, copy the file to the MCP platform and read the file with a one time EAE report. But what if you have data coming in on Excel sheets on a regular basis? Do you still want to go through the hassle of opening the file and saving it as a CSV? No need, the Apache Software Foundation (who else?) has a Java API that knows how to read Excel spreadsheets.

The Apache POI project provides a Java API that can read, and write, XLS files (in the HSSF/Horrible Spread Sheet Format as they call it) and XLSX files. To read a spreadsheet from ALGOL, all I needed to do was wrap a little Java code around the POI interface to make it easily accessible from an ALGOL program.

The code presented here was just an excercise for me, to see how complicated it is to read Excel. I can imagine the ALGOL program to be replaced by a library, which can be called from an EAE report. You can copy the Excel file to the MCP platform using FTP or Windows copy. Alternatively, users could be allowed to upload Excel data using their web browser. In that case the Tomcat web server running on your JProcessor can implement a servlet that uses the Apache (again) Commons FileUpload package to accept Excel file uploads from authorized clients. The uploaded files can be written to an MCP pack for batch processing, or they can be processed right away using, for example, JDBC.

You’ll need to download the Apache POI API from http://poi.apache.org/download.html to compile the ExcelReaderImpl class. Documentation of the POI API and examples can also be found on the Apache site.

The method openSpreadsheet() is not very complicated. It instantiates some POI objects and if an exception is thrown, it returns a non zero result.

The getRow() method is a bit more involved because the type of cell contents dictates the method with which to retrieve the cell data. I find the handling of cell of type CELL_TYPE_FORMULA a bit clumsy. First, I used a FormulaEvaluator class to calculate the result of the formala in a cell. However, since the FormulaEvaluator runs on Java, there is no guarantee that the result is exactly the same as the result Excel originally produced. That is why I commented out that bit and opted for the approach where I try and catch until a valid result is returned.

SpreadsheetReader.java This defines a generic interface to read spreadsheets.
ExcelReaderImpl.java This class implements an Excel xls-type file reader
ExcelReaderLibrary.java This class contains static methods to make calls from ALGOL easy

The ALGOL include file was, as usual, generated using the JifGen utility. But, since I’m running Java 7 on my PC and the Java version of my MCP is still version 6, some more path info was needed in the JifGen arguments:

\"program files"\java\jdk1.6.0_25\jre\bin\java -cp /libs/mcp/mcp.jar;/libs/mcp/mcptools.jar;/"program files"/java/jdk1.6.0_25/lib/tools.jar com.unisys.mcp.tools.JifGen -algol -classpath dist/excel2mcp.jar -d mcp/algol  -output EXCEL_ -verbose com.mcpjava.mcp.spreadsheet.ExcelReaderLibrary

The generated ALGOL file and the test driver are nothing special:

EXCEL_INCL.ALG_M
READEXCEL.alg_m

Java as a library, part 2

In the previuos blog I’ve made a Java class Grep that exports a method named doGrep(). I also supplied a Java test driver to test the Grep class. In this blog I will write the ALGOL program that takes the search text and directory as a parameter and calls the Java doGrep() method to do the actual work.

The utility JifGen has been run to generate the ALGOL include file that holds the definitions of the Java methods (GREP_INCL.ALG_M). I copied this ALGOL symbol to my MCP system as
GREP/SRC/INCLUDE/GREP_INCL.

My ALGOL program looks like this:

NEXT+   ....*....1....*....2....*....3....*....4....*....5....*....6....*....7..
00001000%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
00002000% grep                                                                  
00003000% Parameters:                                                           
00004000%   text:    String parameter of the text to search for.                
00005000%   path:    String parameter of file or directory to search.           
00006000%   recurse: Boolean, if TRUE all subdirectories will also be           
00007000%            searched.                                                  
00008000%                                                                       
00009000% Note: The Apache Software Licence applies to this code.               
00010000% (http://www.apache.org/licenses/LICENSE-2.0.html)                     
00011000%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
00012000$ tads                                                                  
00013000$$ set level 2                                                          
00014000procedure grep(arguments);                                              
00015000array arguments[*];                                                     
00016000begin                                                                   
00017000$$  INCLUDE JNI="*DIR/JRE6/JDK/INCLUDE/JNILIB/ALGOL ON DISK"            
00018000                                                                        
00019000    REAL ARRAY envEtc[0:JNI_MIN_ENV_ARRAY_SIZE+JNI_MIN_JVM_ARRAY_SIZE ];
00020000    ARRAY JNIEnv[0] = envEtc;                                           
00021000    ARRAY JavaVM[-JNI_MIN_ENV_ARRAY_SIZE] = envEtc;                     
00022000    ebcdic value array jvmOptions ("-cp ./grep.jar " 48"00");           
00023000    ebcdic value array mcpOptions (48"00");                             
00024000    integer jvmRslt;                                                    
00025000    double returnArray;                                                 
00026000    ebcdic array param[0:299];                                          
00027000    ebcdic array ax[0:119];                                             
00028000    integer errorCode;                                                  
00029000    pointer pparam;                                                     
00029200    file lp(kind = printer);                                            
00029400    ebcdic array line[0:132];                                           
00030000                                                                        
00031000$$  INCLUDE GREPJNI="GREP/SRC/INCLUDE/GREP_INCL"                        
00032000                                                                        
00033000    procedure listHits(returnArray);                                    
00034000    value returnArray;                                                  
00035000    double returnArray;                                                 
00036000    begin                                                               
00038000        ebcdic array found[0:300];                                      
00039000        integer foundLen;                                               
00040000        double arrayElement;                                            
00041000        integer returnLength;                                           
00042000        integer i;                                                      
00043000                                                                        
00044000        returnLength := JNI_GET_ARRAY_LENGTH(JNIEnv, returnArray);      
00045000                                                                        
00046000        for i := 0 step 1 until (returnLength - 1) do                   
00047000        begin                                                           
00048000            replace found by " " for 300;                               
00049000            foundLen := 300;                                            
00050000            arrayElement := JNI_GET_OBJECT_ARRAY_ELEMENT                
00051000                (JNIEnv, returnArray, i);                               
00052000            JNI_GET_STRING_EBCDIC_CHARS                                 
00053000                (JNIEnv, arrayElement, found, 0, foundLen);             
00054000            if foundLen > 0 then                                        
00055000            begin                                                       
00056000                write(lp, *, found);                                    
00057000            end;                                                        
00058000        end;                                                            
00059000    end of listHits;                                                    
00060000                                                                        
00061000    if (size(arguments) * 6) > 300 then                                 
00062000    begin                                                               
00063000        resize(param, size(arguments) * 6, DISCARD);                    
00064000    end;                                                                
00065000    replace pparam:param[0] by pointer(arguments)                       
00066000        for size(arguments) * 6 until = 48"00";                         
00067000                                                                        
00068000    % Initialize the Java Virtual Machine                               
00069000    jvmRslt := JNI_CREATE_JAVA_VM_STAR(JavaVM, JNIEnv,                  
00070000                                       JNI_VERSION_1_6,                 
00071000                                       jvmOptions,                      
00072000                                       mcpOptions);                     
00072100    if jvmRslt < 0 then                                                 
00072200    begin                                                               
00072300        replace line by " " for 132;                                    
00072400        replace line by "Result from JNI_CREATE_JAVA_VM_STAR: ",        
00072500            jvmRslt for * digits;                                       
00072600        write(lp, 132, line);                                           
00072700    end                                                                 
00072800    else                                                                
00072900    begin                                                               
00073000        INITIALIZE_GREP_;                                               
00074000        returnArray := GREP__GREP_DO_GREP(param,                        
00075000            0, offset(pparam));                                         
00076000        if JNI_Check_Exception(JNIEnv) then                             
00077000        begin                                                           
00078000            errorCode := JNI_GET_LAST_ERROR(JNIEnv);                    
00079000            replace ax by "JNI Exception: Error code = ",               
00080000                errorCode for * digits, 48"00";                         
00081000            display(ax);                                                
00082000        end;                                                            
00083000        listHits(returnArray);                                          
00083500        JNI_DESTROY_JAVA_VM(JavaVM);                                    
00084000    end;                                                                
00085000end of grep.                                                            

Line 17000 includes the variables and procedure declarations that are necessary to communicate via JNI. It needs to be included before the include of the generated GREP/SRC/INCLUDE/GREP_INCL. The lines 19000 – 21000 declare some arrays that the generated ALGOL file needs. The comments at the top of the generated include file remind you of this.

Lines 22000 and 23000 define parameter strings that need to be supplied to the JNI_CREATE_JAVA_VM_STAR procedure (line 69000). This procedure will start a Java Virtual Machine on the JProcessor. The array jvmOptions sets the CLASSPATH of the JVM. In this program I have hardcoded the CLASSPATH relative to the CURRENTDIRECTORY attribute of the program. I a production system you would probably get these parameters from a configuration file.

The INIALIZE_GREP_ in line 73000 results in a call (via GREP/SRC/INCLUDE/GREP_INCL) of the JAVAJNILIB library procdeure JNI_FIND_CLASS_E. This procedure returns a handle to the Grep class.

Since I've made the doGrep() method a static method, I don't need to create an instance of the Grep class. I find this makes the ALGOL code less complicated. In a production system that I built, I defined a single class to interface between Java and ALGOL. A call to a static method in this class creates a single instance of a Manager class, the Manager class in turn, instantiates other objects as needed. This scheme can work because a JVM is dedicated to one program stack.

In line 74000, the actual doGrep method is called. The parameter param is an EBCDIC array that contains the arguments for doGrep. The next parameter, 0, is the offset into the param array where the actual arguments start. The last parameter gives the length, in characters, of the arguments string. The GREP__GREP_DO_GREP procedure in the generated include file wraps the actual, more complex, JNI call.

I started a test using the WFL below:

NEXT+   ....*....1....*....2....*....3....*....4....*....5....*....6....*....7..
00000100BEGIN JOB WFL/GREP;                                                     
00000200                                                                        
00000300RUN GREP/GREPDRIVER(                                                    
00000350        "-r grep  /-/user/usercode/ruud/grep/src");                     
00000400    LIBRARY JAVAVM(LIBACCESS = BYTITLE,                                 
00000500        TITLE=*DIR/JRE6/BIN/JAVAPROXYLIB ON DISK);                      
00000600    CURRENTDIRECTORY="/-/USER/USERCODE/RUUD/GREP";                      
00000700                                                                        
00000800END JOB 

The CURRENTDIRECTORY equation is, apparently, just for the GREP/GREPDRIVER program, it is not propagated to the Java Virtual Machine. That means that the path to directory to be searched by Grep must be an absolute path, it cannot be relative to the CURRENTDIRECTORY.

I also got a message “JAVAHOMEPATH not provided; JRE location extracted from proxy name (/-/DISK/DIR/JRE6).”. This message comes from the program *SYSTEM/JAVAFILE, which is used by the JVM to access the MCP file system. Adding a file equation FILE JAVAHOMEPATH(PATHNAME="/-/DISK/DIR/JRE6"); did not get rid of the message. But using the path from the PROXYLIB is just fine.

When I ran my test WFL, much to my surprise, I didn't get any results back. I figured the JNI_CREATE_JAVA_VM_STAR failed, that's why I added the code in lines 72300 – 72600. But there was no problem there, nor was there a problem in the GREP__GREP_DO_GREP call. Wouldn't it be nice if I could debug my Java code now. Well, I could, the way to do that was obvious: Just insert the debug arguments
-Xdebug -Xrunjdwp:transport=dt_socket,address=9000,server=y,suspend=y
at the beginning of the jvmOptions array and I could connect my Netbeans to the JVM under test. Stepping through the doGrep() Java code, the problem jumped right at me. The param.split(“ “) method splits the parameter string using a single space as a token separator, but my test job has two spaces between “grep” and “/-/user/....”. I have now changed the regular expression in the param.split to “ +”, telling the split to use one or more spaces to separate the string.