We help IT Professionals succeed at work.

Writing an Excel file from JAVA

smithc
smithc asked
on
Has anyone ever written an Excel file from JAVA? Any suggestions on which JAVA classes to use? Are there any standards I need to follow

Thanks!!
Comment
Watch Question

Jim CakalicSenior Engineer

Commented:
One roundabout way of reading/writing Excel spreadsheets is to use JDBC/ODBC:
    http://www.javaworld.com/javaworld/javaqa/2001-06/04-qa-0629-excel.html
    http://matrix.psc.sc.edu/tutorials/jdbc/
    http://www.planet-source-code.com/xq/ASP/txtCodeId.2180/lngWId.2/qx/vb/scripts/ShowCode.htm

IBM has an alphaworks technology called ExcelAccessor:
    http://www.alphaworks.ibm.com/ab.nsf/bean/ExcelAccessor

Tidestone has a commercial Java product called Formula One that is Excel file-compatible with an API that permits you to automate spreadsheets from your Java application:
    http://www.tidestone.com/home/default.jsp

You could also use a Java-COM bridge like JIntegra:
    http://www.intrinsyc.com/support/jintegrasuite/excel_example.html

Similar technologies include:
    http://danadler.com/jacob/
    http://www.alphaworks.ibm.com/tech/olebridge

Hope one of these might be appropriate for you.
Jim
Commented:
Hi Smith,
   I posted the same question in expert-exchange site...
I got the reply....and works fine for me...

There are 2 ways to do..

1. Simple way(No API):
     U can create a dsn(for the excel sheet) and u can try
     to insert a record into the excel sheet.

2. API
     There is an API for this and u can download and use..It is simple
  check out ibm's bridge2java:
http://www.alphaworks.ibm.com/tech/bridge2java

If any problem send mail to vasan_sr@yahoo.com
GoodLuck
Vasan S

Commented:
All the Microsoft Office native file formats work in the same way - each Office application is a big COM server and each document is the state information for an instance of an object of <document-type>. Loading and saving is, from a programmatic point of view, object (de)serialisation.

With Java, object serialisation of an object that has a reference to another object results in nested serialisation streams. COM behaves in exactly the same way, and this is how a Word document containing an embedded Excel spreadsheet can be saved into one file.

An implication of this is that deserialisation depends on the presence of all the classes for the objects to be deserialised. Because this requirement may not be satisfied (eg you might give said Word document to someone who has Word but not Excel) each of the COM document types writes a graphic image of itself, allowing non-editable display of unrecognised document types.

The upshot of all this is that in order to read and write such files you have to either emulate the relevant COM classes or wrap them.

Emulation is a huge and daunting task. Wrapper classes are more straightforward but introduce platform dependencies.

You would be better advised to use tab separated values - same as CSV but delimited by tabs instead of commas; it's quite difficult to get a tab into a spreadsheet value, obviating a raft of parsing headaches.

Excel will read and write TSV files. Although it is conventional to give these the extension .TXT you will find that if you give them the extension .XLS then Excel will correctly open them. Excel will also happily save as TSV (although as mentioned the default extension is .TXT and it will propose this extension).

Obviously TSV will not store font information and suchlike, but it's simple, reliable, and trivial to read and write from practically any programming language you like.

Commented:
In J++ it is as simple as importing the excel com Object from your project settings tab:

import excel8.*;
import com.ms.com.*;

_Global globXL=null;
_Application appXL=null;
Workbooks books=null;
_Workbook book = null;

try{
   globXL = (_Global)new Global();
   appXL = (_Application)globXL.getApplication();
   appXL.putVisible(0,true);  // in Excel 97 use:
                              // appXL.setVisible(0,true);

   books = (Workbooks)appXL.getWorkbooks();

   Variant vTemp = new Variant();
   vTemp.putString("c:\\book1.xls");

   Variant vOptional = new Variant();
   vOptional.noParam();
   book =
(_Workbook)books.Open("c:\\book1.xls",vOptional,vOptional,vOptional,vOption
al,vOptional,vOptional,vOptional,vOptional,vOptional,vOptional,vOptional,vO
ptional,0);

   }
   catch(ComFailException e)
   {
      System.out.println(e.getMessage());
   }
 

Commented:
...if all you want is for excel to open up your data in neat cells, you can create a .csv file and excel will open you file without a problem.

Commented:
J++ is not Java. It is a Microsoft product that is neither fish nor fowl, having none of the benefits of platform independence and none of the benefits of platform native implementation.

Although it is certainly possible to make the J++ product handle Java code and emit valid class files, you'll have to disallow all of the M$ language pollution which is quite a job (M$ doesn't *want* your code to work away from Windows).

jerelw, refer to my comment and please note that the CSV option is there considered. It is also discarded in favour of tab separated values due to the likelihood that fields contain commas and quotes, making parsing unreliable.

Commented:
...some people are have applications on microsoft platforms.  

A J++ solution would be a valid solution.

...you can always quote your column containing the comma.

...you're assuming that you cannot escape your delimiters.

...you're also assuming that everyone has your distaste for microsoft.

As for a solution for 'writing excel files' a J++ solution is, nevertheless, a solution that works.  

A J++ solution is also a more useful solution than ranting about microsoft...

Commented:
You can run pure Java on Windows.

J++ is valid but why settle for one platform?

If you quote values, what do you do with values that contain quotes, given that there's no standardised notation for escaping quotes? Same problem with escaping delimiters. Fragile.

My distaste is for paying the overheads of platform independence and then throwing it away.

A platform independent solution is even better, and I already offered one.

A rant about Microsoft? Don't be silly. The posting would have run for another 8000 words. But seriously, I'm platform agnostic. For example, I will cheerfully tell you that JBoss (an EJB container I use) handles six times as many users on Win2K than Linux on the same hardware.

But insisting on pure Java means I can also use Solaris on some big iron from Sun, or an IBM 390.

I think you might be surprised to learn how little of the real world runs Windows - PCs are far from being the commonest platform. They're merely the most conspicuous platform. Java is, for example, available for embedded systems, which are ubiquitous but invisible.

Commented:
Simith,
  u can try bridge2java....that will works fine..
Jim CakalicSenior Engineer

Commented:
FYI -- I know an answer has been accepted -- but I thought I would share another technology that one of my tech people just found. Take a look at the POI project on SourceForge (http://poi.sourceforge.net). It is a pure Java API to Microsoft's OLE 2 Compound Document Format, the file format used by Excel and other MS Office tools. The developer that found it was familiar with using Actuate in VB and said this interface is quite similar (by design) and very easy to work with. In a matter of a couple hours he was writing xls files.

Best regards,
Jim Cakalic

Commented:
Use the free POI API from the Jakarta/Apache project available from http://jakarta.apache.org/poi/index.html .

Explore More ContentExplore courses, solutions, and other research materials related to this topic.