smithc
asked on
Writing an Excel file from JAVA
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!!
Thanks!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.getAp
appXL.putVisible(0,true); // in Excel 97 use:
// appXL.setVisible(0,true);
books = (Workbooks)appXL.getWorkbo
Variant vTemp = new Variant();
vTemp.putString("c:\\book1
Variant vOptional = new Variant();
vOptional.noParam();
book =
(_Workbook)books.Open("c:\
al,vOptional,vOptional,vOp
ptional,0);
}
catch(ComFailException e)
{
System.out.println(e.getMe
}
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.getAp
appXL.putVisible(0,true); // in Excel 97 use:
// appXL.setVisible(0,true);
books = (Workbooks)appXL.getWorkbo
Variant vTemp = new Variant();
vTemp.putString("c:\\book1
Variant vOptional = new Variant();
vOptional.noParam();
book =
(_Workbook)books.Open("c:\
al,vOptional,vOptional,vOp
ptional,0);
}
catch(ComFailException e)
{
System.out.println(e.getMe
}
...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.
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.
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.
...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...
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...
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.
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.
Simith,
u can try bridge2java....that will works fine..
u can try bridge2java....that will works fine..
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
Best regards,
Jim Cakalic
Use the free POI API from the Jakarta/Apache project available from http://jakarta.apache.org/poi/index.html .
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