JSP/Servlet to Excel Example

You may read this example simply to get a feel for how easy it is to use J-Integra® to access existing applications, however we urge you to try out this example. Even if you are not familiar with Java, give it a go!

If you do wish to try out this example, you should first download and install J-Integra® and the Java 2 SDK, Enterprise Edition. Note also that a large quantity of Java files are automatically generated from Excel in this example; compiling them takes quite a long time, and should probably only be attempted on a machine with more than 128MB of memory.

This example will require access to a machine that is running Windows NT or Windows 95/98, and has Excel installed on it, in order to generate the pure Java proxies which can then be used to access Excel from any machine that supports a standard Java Virtual Machine (JVM). This proxy generation step need only be done once.

The steps involved

1. Configure your environment

2. Generate the Java proxies for Excel

3. Compile the example servlet

4. Create the example JSP

5. Deploy and run the example

6. Remote Access

Configure your environment

This example assumes that you have installed

• The JDK under D:\JDK1.3.1

• The J2EE under D:\J2SDKEE1.3

• Excel under D:\Program Files\Microsoft Office,

• J-Integra® under c:\jintegra.

• Ensure that you have installed the J-Integra® license.

We will be performing this example under D:\pure. Create that directory, and an excel directory under it. Update your path environment variable to include the JDK and J-Integra® bin directories, and update your CLASSPATH, and J2EE_CLASSPATH environment variable to include the JDK and J-Integra® class libraries:

SET PATH=%PATH%;D:\jdk1.3.1\bin;D:\j2sdkee1.3\bin;c:\jintegra\bin;
SET CLASSPATH=%CLASSPATH%;D:\j2sdkee1.3\lib\j2ee.jar;
SET CLASSPATH=%CLASSPATH%;D:\jdk1.3.1\lib\tools.jar;
SET CLASSPATH=%CLASSPATH%;c:\jintegra\lib\jintegra.jar;
SET J2EE_CLASSPATH=%CLASSPATH%

Generate the Java proxies for Excel

Type Libraries contain information about COM components. We will be using the information contained in Excel's type library to generate pure Java proxies which can be used to access Excel from a standard JVM.

1. Start the com2java tool:

   

   If you get an error about the command not being found, check that you set your path environment variable correctly above.

2. Click the "..." button next to the typelib field to select Excel's Type Library, which is located in

   D:\Program Files\Microsoft Office\Office\EXCEL8.OLB

   (You may have a different version of this COM component on your system).

   

3. Enter D:\pure into the Output Dir field

4. Enter excel in the Java package field

5. Click the button

6. When you click OK, the proxy files will be generated:

   The files generated include:

• A Java interface and proxy class for each COM interface.
• A Java class for each COM class.
• A Java interface for each ENUM (a set of constant definitions).



Take a look at some of the generated files to get a feel for what is being generated.

You will notice that comments are included in the generated files in order to make them easier to understand. You may wish to use the javadoc documentation generation tool on the files (the comments are compatible with that tool).

7. Go to D:\Pure and enter the following command to compile the generated proxy files:
   javac -J-mx128m -J-ms128m excel/*.java

   The -J parameters make more memory available to the compiler than it would normally have, since there are a lot of generated files in this example.

8. Because we are going to deploy the generated classes in a servlet/JSP, it would be much easier if we create a jar file to put all the generated files into just one file. In order to package them into a jar file, while you are in D:\Pure sub-directory, issue this command

   

9. The resultant file is excel.jar. You need to update your CLASSPATH, and J2EE_CLASSPATH environment variables to include this jar file.

   SET CLASSPATH=%CLASSPATH%;D:\Pure\excel.jar;.;

   SET J2EE_CLASSPATH=%CLASSPATH%

Create and compile the example Java servlet

This document is based on Microsoft Excel 2000 API. If you are using Microsoft Excel 97, or Microsoft Excel XP, please refer to API Differences Between Excel 97, 2000 & XP to learn about the API differences between these APIs.

1. Create the file ExcelCalculatorExample.java, by cutting and pasting from your Web Browser. This file is included in your jintegra\examples\servlet-com\ServletToExcelCalculator directory when you download J-Integra®.

   Here is the file.

   import java.io.*; import excel.*; import javax.servlet.*; import javax.servlet.http.*; import com.linar.jintegra.AuthInfo; import com.linar.jintegra.Cleaner; public class ExcelCalculatorExample extends HttpServlet { public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException { res.setContentType("text/html"); ServletOutputStream out = res.getOutputStream(); out.println("<html><head><title>Three tier, client-server, web-enabled, calculator</title></head>"); // Any parameters passed to us? String p1 = req.getParameter("v1"); String p2 = req.getParameter("v2"); Double v1 = null; Double v2 = null; boolean badValues = false; try { v1 = p1 == null ? null : new Double(p1); v2 = p2 == null ? null : new Double(p2); } catch (NumberFormatException nfe) { badValues = true; } if (p1 == null || p2 == null || badValues) { // Build a form asking for two values to add together out.println("<BODY><form>Enter two values to add together, and then click submit"); out.println("<br><input NAME=v1 SIZE=5><br><input NAME=v2 SIZE=5>" + "<br><input TYPE=SUBMIT VALUE=Submit></form></body></html>"); return; } try { Cleaner.trackObjectsInCurrentThread(); // May want to consider using setThreadDefault(...) // AuthInfo.setDefault("Domain", "username", "password"); // Create an instance of Excel.Application on the local host excel.Application app = new excel.Application(); // Use Excel objects to get at a range in the displayed Worksheet excel.Workbooks workbooks = app.getWorkbooks(); excel.Workbook workbook = workbooks.add(null); excel.Sheets worksheets = workbook.getWorksheets(); excel.Worksheet sheet = new excel.Worksheet(worksheets.add(null, null, null, null)); excel.Range range = sheet.getRange("A1:B1", null); Object[] values = {v1, v2 }; // For Excel XP, use: range.setValue2(values); // For Excel 2000, use: range.setValue(values); //Use setValue2 for Excel XP/2003 range = sheet.getRange("C1", null); // For Excel XP/2003, use: range.setValue2("=SUM(A1:B1)"); // For Excel 2000, use: range.setValue("=SUM(A1:B1)"); // For Excel XP/2003, use: ... + range.getValue2() // For Excel 2000, use: out.println("<body>The Excel calculator results are " + p1 + " + " + p2 + "=" + range.getValue()); //Use getValue2 for Excel XP/2003 // Release the objects -- Garbage collection would also automatically release them eventually workbook.close(new Boolean(false), null, null); // False == don't prompt to save changes app.quit(); out.println("</body></html>"); } catch (Exception e) { PrintWriter pw = new PrintWriter(out); e.printStackTrace(pw); pw.flush(); } finally { //Removes the COM object com.linar.jintegra.Cleaner.releaseAllInCurrentThread(); } } public String getServletInfo() { return "Access MS Excel from your browser"; } }

   If you are running under Windows 98 or Windows 95, then you will need to modify the example slightly. Replace the initial section with the following. We start up a Worksheet rather than the Application. (We get the Application property using late binding since a Worksheet started this way only seems to support IDispatch):

   Worksheet tmpSheet = new Worksheet(); Application app = new Application(tmpSheet.getPropertyByName("Application")); app.setVisible(true); // Nice to see what is happening // Use Excel objects to get at a range in the displayed Worksheet ...

2. Compile the example using the javac ExcelCalculatorExample.java command.

   

   If you get a The name specified is not recognized as an internal or external command error, then it is likely that you did not set you path environment variable correctly (see the first section of this example)

   If you get a ExcelCalculatorExample.java:1: Package excel not found in import... error, then your CLASSPATH environment variable does not correctly include excel.jar

   If you get a .\excel\Application.java:3: Package com.linar.jintegra not found in import... error, then your CLASSPATH environment variable does not correctly include jintegra.jar

   If you get an error like error: File .\excel\_Application.java does not contain type excel._Application as expected. Please adjust the class path so that the file does not appear in the package excel., then it is likely that you did not specify the package excel when generating the proxy files from the type library.

Create the example JSP

This document is based on Microsoft Excel 2000 API. If you are using Microsoft Excel 97, or Microsoft Excel XP, please refer to API Differences Between Excel 97, 2000 & XP to learn about the API differences between these APIs.

1. Create the file ExcelCalculatorExample.jsp, by cutting and pasting from your Web browser. This file is included in your jintegra/examples/jsp-servlet-com/ServletToExcelCalculator directory when you download J-Integra®.

   Here is the file.

   <HEAD> <SCRIPT language="JavaScript"> <%@ page import="java.io.*,excel.*,com.linar.jintegra.AuthInfo,com.linar.jintegra.Cleaner" %> <%! String m_strResult = ""; %> </SCRIPT> </HEAD> <TITLE>Excel Calculator Example(JSP)</TITLE> <BODY> <SCRIPT language="JavaScript"> <% String p1 = (String) request.getParameter("v1"); String p2 = (String) request.getParameter("v2"); Double v1 = null; Double v2 = null; boolean badValues = false; try { v1 = p1 == null ? null : new Double(p1); v2 = p2 == null ? null : new Double(p2); } catch(NumberFormatException nfe) { badValues = true; } if ((null != p1) && (null != p2) && (false == badValues)) { try { Cleaner.trackObjectsInCurrentThread(); // May want to consider using setThreadDefault(...) // AuthInfo.setDefault("Domain", "username", "password"); // Create an instance of Excel.Application on the local host excel.Application app = new excel.Application(); // Use Excel objects to get at a range in the displayed Worksheet excel.Workbooks workbooks = app.getWorkbooks(); excel.Workbook workbook = workbooks.add(null); excel.Sheets worksheets = workbook.getWorksheets(); excel.Worksheet sheet = new excel.Worksheet(worksheets.add(null, null, null, null)); excel.Range range = sheet.getRange("A1:B1", null); Object[] values = {v1, v2 }; // For Excel XP/2003, use: range.setValue2(values); // For Excel 2000, use: range.setValue(values); // Update the spreadsheet range = sheet.getRange("C1", null); // For Excel XP/2003, use: range.setValue2("=SUM(A1:B1)"); // For Excel 2000, use: range.setValue("=SUM(A1:B1)"); // For Excel XP/2003, use: ... + range.getValue2() // For Excel 2000, use: m_strResult = "The Excel calculator results are " + p1 + " + " + p2 + "=" + range.getValue(); //Use getValue2 for Excel XP/2003 // Release the objects -- Garbage collection would also automatically release them eventually workbook.close(new Boolean(false), null, null); // False == don't prompt to save changes app.quit(); } catch(Exception ex) { m_strResult = "Exception:" + ex.toString(); } finally { com.linar.jintegra.Cleaner.releaseAllInCurrentThread(); } } else { m_strResult = "<FORM Action=\"ExcelCalculatorExample.jsp\" ACTION=\"POST\">"; m_strResult += "Enter two values to add together, and then click submit"; m_strResult += "<br><INPUT NAME=v1 SIZE=5><br><INPUT NAME=v2 SIZE=5>"; m_strResult += "<br><INPUT TYPE=SUBMIT VALUE=Submit>"; m_strResult += "</FORM>"; } %> document.write('<%=m_strResult%>'); </SCRIPT> </BODY> </HTML>

Deploy and run the Example

For information on deploying and running your Servlet or JSP onto your application server, see the following procedures.

Remote Access

When running under Windows, J-Integra® can use native code to pick up your current login identity. If you would rather that J-Integra® did not do this, or if you are not logged in to an NT domain (for example if your Java code is on a UNIX box), then you must configure DCOM access to Excel, and you must specify the NT domain, user and password to be used by the J-Integra® runtime.

Proceed to Configuring DCOM for Remote Access

Conclusion

Using J-Integra® you have enabled a JSP/Java Servlet to create and interact with Excel and worksheets. The example has used Excel to perform a simple addition of two numbers and retrieved the result from Excel.

Although this is a very trivial example, it demonstrates the power of J-Integra®. Remember that you can access Excel on your local or remote Windows computer or access Excel remotely from a non-Windows computer.