wood burning stoves 2.0*
The moose likes Java in General and the fly likes Exporting some data to excel Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Java » Java in General
Bookmark "Exporting some data to excel" Watch "Exporting some data to excel" New topic
Author

Exporting some data to excel

Anthony D'Souz
Ranch Hand

Joined: Oct 13, 2011
Posts: 63
Hi all,

Please provide me the possible ways of exporting some data into an excel sheet with their corresponding pros and cons. I want to start playing with the different ways of doing the same. I dont have any idea about how to do the same/different or best way to do the same.

Please help.

Thanks,
Butny
Steve Luke
Bartender

Joined: Jan 28, 2003
Posts: 4167
    
  21

The ways I know:

1) Write data to a text file, either in CSV format, or tab deliminated
- Pros: Easy to do, not limited by OS, version of Excel, or spreadsheet application compatibility.
- Cons: Not really writing to Excel. CSV seems simple, but is more complicated than it sounds, so whereas an external API is not required it sure does help to use one that has been pre-made.

2) Use an application API which acts as a bridge between Excel and Java. Such as Apache POI or JExcel
- Pros: Actually exporting to Excel. Hard word is done for you. With proper tool, you get support
- Cons: Rely on an external API as a dependency. Worry about Excel version compatibility. Worry about non-Excel spreadsheet compatibility.

3) Use some third hand protocol to communicate between Excel and your application, such as DDE or COM
- Pros: none that I can think of...
- Cons: Typically rely on external APIs, OS-dependent features, version compatibility, tools present in Excel to receive data...


Steve
Anthony D'Souz
Ranch Hand

Joined: Oct 13, 2011
Posts: 63
Hi,

Thanks for the reply Steve. For exporting the data to excel, is nt there any other way rather than depending on some API? May be some helpful jars(just a guess) for easing the work?

Since I don't want to rely on any external API hence i will try out the first option you posted. Can you please provide some sample code for the same which i can play around with? Also inform the jars i need to use for doing the same.

Thanks,
Butny
Tim Moores
Rancher

Joined: Sep 21, 2011
Posts: 2408
You don't need to "export" a CSV file to Excel - Excel can import those just fine. But Steve's spot on - CSV is more complicated than it appears at first; instead of coding that yourself you should use one of the numerous freely available libraries.
Anthony D'Souz
Ranch Hand

Joined: Oct 13, 2011
Posts: 63
Hi Tim,

Thanks for the reply. Can you please suggest some libraries for doing the same? Since i don't have any idea about how to do the same, a sample code will also be very helpful for me so that i can play around with it.

Thanks,
Butny
Randall Twede
Ranch Hand

Joined: Oct 21, 2000
Posts: 4340
    
    2

just a comment. i had to do that once in a VB program. it was very simple. just save the textArea as a .txt file and excel can open it just fine. the columns might need adjusting is all. i adjusted them in my program so the user didn't have to do it each time.


SCJP
Visit my download page
Anthony D'Souz
Ranch Hand

Joined: Oct 13, 2011
Posts: 63
Hi,

Thanks for your reply Randall. can you please provide some sample code and the jars required for doing the same in java?

Thanks,
Butny
Tim Moores
Rancher

Joined: Sep 21, 2011
Posts: 2408
This page lists several CSV libraries: http://www.coderanch.com/how-to/java/AccessingFileFormats
Randall Twede
Ranch Hand

Joined: Oct 21, 2000
Posts: 4340
    
    2

here is an example i found on the web
example
Anthony D'Souz
Ranch Hand

Joined: Oct 13, 2011
Posts: 63
Hi,

Thanks for the link. I am able to write some static data into an excel sheet now by using the Apache POI. the code i used is:



but when i am using this code section under the button click event handler:



then the program hangs. i believe that i am not able to understand how to track the open and save button of the pop-up which is displayed when this line is executed:



Please help.

Thanks,
Butny
Tim Moores
Rancher

Joined: Sep 21, 2011
Posts: 2408
"E:\\Book1.xls" is a file path on the server; it probably doesn't exist, and isn't what you want anyway.

What you actually need to do in order to stream the data to the browser is to use the ServletResponse's output stream instead of some file stream.
Anthony D'Souz
Ranch Hand

Joined: Oct 13, 2011
Posts: 63
Hi Tim,

The problem is solved to some extent with this code:



The save button click of the pop-up is working fine. But on clicking the open, another pop-up opens asking me for open/save/close and then if i click open, then the excel is opened along with the contents.

Where is the mistake i did in the above code?

Thanks,
Butny
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Steve Luke wrote:
3) Use some third hand protocol to communicate between Excel and your application, such as DDE or COM
- Pros: none that I can think of...
- Cons: Typically rely on external APIs, OS-dependent features, version compatibility, tools present in Excel to receive data...

I've been through this. We're using the Jacob library to call Excel's COM model, but it is poorly documented and generally painful experience. Good knowledge of Excel's object model and VBA is required. The pro in this case is that this setup allows the user to mark an area in his currently opened workbook from/to which he wants to import/export some data into/out of our application. We were actually porting an old VB app to Java and couldn't afford not to implement this functionality in new version.

I can also think of 4) Use system clipboard. Caveats do exist also here though. I can elaborate if someone wants, incidentally I've been through this too...
Anthony D'Souz
Ranch Hand

Joined: Oct 13, 2011
Posts: 63
Hi Martin,

Thanks for your reply. From your prospective, which is the best way for exporting/importing data to/from excel in Java?. I am using the Apache POI.

I will like to know the explanation of the fourth possible way you suggested i.e. Use system clipboard and the jars dependency for the same as well.

btw, can you please provide the solution for the problem i faced in my previous post of this thread? why is the pop-up opening the second time when i click the open button of the pop-up?

Thanks,
Butny
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Anthony D'Souz wrote:Hi Martin,

Thanks for your reply. From your prospective, which is the best way for exporting/importing data to/from excel in Java?. I am using the Apache POI.

I will like to know the explanation of the fourth possible way you suggested i.e. Use system clipboard and the jars dependency for the same as well.

It seems that you're developing a thin client, therefore probably only first two options are actually accessible to you (you need to create a file which a user can download onto his computer). The third option requires the Java code to run on the user's machine.

The clipboard communication I've suggested is not something fully automated - you'll just let your application put the data onto a clipboard and let the user paste the data wherever he wants. A plain, tab delimited text is sufficient to have Excel put the data into different cells. The problems here are mainly the decimal separator if transferring decimal numbers, and date/time formats. In Excel both of these follow settings in the Regional settings control panel, and this is not always the same as separators and formats obtained from Java locale.

For thin client, a slight modification would be needed, but it could still be interesting to you - Excel can accept HTML from clipboard and it will generally use formatting present in the data. You might generate a page containing just the data to pass to Excel and let the user press Ctrl-A, Ctrl-C and Ctrl-V to copy/paste it with reasonable little effort. Admittedly, not very user friendly, but if your users need to get the data from you into a existing workbook (with formulas for example), it might in the end suit them better than generating an Excel or CVS file for them which has to be downloaded, opened, copied from and finally deleted. But if your aim is to generate some kind of reports, this is most probably not something to consider. In any case it looks rather rough, but there are still IT segments out there where rough solution is perfectly adequate.


btw, can you please provide the solution for the problem i faced in my previous post of this thread? why is the pop-up opening the second time when i click the open button of the pop-up?

I'm sorry, I don't have any experience in this area.
Randall Twede
Ranch Hand

Joined: Oct 21, 2000
Posts: 4340
    
    2

i would try to help with that if i wasn't half drunk try the swing forum
Anthony D'Souz
Ranch Hand

Joined: Oct 13, 2011
Posts: 63
Hi martin,

Thanks for the explanation. It sounds interesting.

[quote=Martin Vajsar]But if your aim is to generate some kind of reports, this is most probably not something to consider.[/quote]

Yes, my aim is to try different options for creating an excel report and then implementing the best one in my application, hence i am not considering the clipboard option as you suggested for the time being.

Can you please have a look at this thread and provide some ideas of doing the same in different ways so that i can give it a try.

http://www.coderanch.com/t/560632/java/java/Sending-some-text-image-printer

Thanks,
Butny

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Exporting some data to excel
 
Similar Threads
Excel-format report
Alignment issue while Export data to Excel file from a jsp
Oracle 9i to Excel Sheet
how to apped standard Font to HSSFWorkbook
Oracle 9i to Excel Sheet