Accessing Excel from Java using EZ JCom

However, the Excel API is huge. Therefore it has been pre-built and packaged with some sample programs. Those interested in calling Excel from Java should download this pre-packaged version instead of using the tool directly.

Sample Programs

Note that these samples are for "demonstration" purposes. In typical usage as a calculation engine, the Excel application will not be made visible and the "Quit" method would be a part of the finalizations. (However, the Excel application would need to be made visible if its UI elements are to be used by the calling Java program.)

• Excel Sample 1. This sample starts up a copy of Excel, makes it visible, puts the value "33" in cell A1, and leaves Excel running for the user.
• Excel Sample 2. This sample starts up a copy of Excel, DOES NOT make it visible, puts the value "33" in cell A1, saves to file C:\JavaExcelSample.xls and exits the running copy of Excel.
• Excel Sample 3. This sample starts up a copy of Excel, makes it visible, opens the file C:\JavaExcelSample.xls, puts a formula in B1 and one in B2, and copies the formulae to C1:F1 and B2:D5. Leaves Excel running for the user.
• Excel Sample 4. This sample starts up a copy of Excel, populates in with data, and creates a chart from cells B1:D5. Leaves Excel running for the user.
• Excel Sample 5. Various simple calculations using Excel.

Steps in programming the Excel COM interface

• Some methods in Excel returns a variant. A variant is a generic container that can contain objects of various classes, e.g. integers, floats, or more complicated objects. In EZ JCom, the Java class ezjcom.JComVariant corresponds to the COM variant concept. A useful technique to find out what is actually being returned in the variant, is to call the "getObject()" method and print out the name of the object's class, e.g.

  JComVariant var = SomeExcelMethod...
  Object obj = var.getObject();
  Class objClass = obj.getClass();
  System.out.println( objClass );
  

  Once you know what is in the variant, you can then proceed with developing your program further.

• To work in a new instance of Excel, Call the "Add()" method of the "Workbooks" interface to add a new Workbook. Or call the "Open( filename )" method to open an existing .xls file.
• From the "_Worksheet" interface, "Range" interfaces can be retrieved. Use the Range interfaces to retrieve or modify the values in the spreadsheet. A single cell is also considered a range, e.g. the cell A1 is referred as the Range "A1:A1".
• The "_Worksheet" interface can also be used to "Save" or "SaveAs" to a file.
• The "_Application" interface has a "Quit" method for stopping the Excel program.
• Before exiting the program, the method

  ezjcom.JComObject.JComShutdown();

  must be called to close down COM connections and clean up all resources. This will allow the Excel server to exit, otherwise instances of Excel.exe may be left running in the memory. If you want the Excel to stay active for further interactions, do not call "Quit". But you must still make sure the Excel objects are released by calling the JComShutdown method.

  A simple way to ensure this method is called is by placing it in the "finally" clause, like the samples.