• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Exporting some data to excel

 
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Bartender
Posts: 4179
22
IntelliJ IDE Python Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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...
 
Anthony D'Souz
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Saloon Keeper
Posts: 7585
176
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 65
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 4716
9
Scala Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Anthony D'Souz
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Saloon Keeper
Posts: 7585
176
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This page lists several CSV libraries: https://coderanch.com/how-to/java/AccessingFileFormats
 
Randall Twede
Ranch Hand
Posts: 4716
9
Scala Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
here is an example i found on the web
example
 
Anthony D'Souz
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Saloon Keeper
Posts: 7585
176
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
"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
Posts: 65
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 65
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 4716
9
Scala Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
i would try to help with that if i wasn't half drunk try the swing forum
 
Anthony D'Souz
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.

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

Thanks,
Butny

 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic