Solved

Export queries as excel files

Posted on 1998-02-18
13
234 Views
Last Modified: 2010-05-18
Hi,

I'm generating dynamic html sites which represent content of a specific database. The next step is to perform a possibility to download such a report as excel (5.0) format.
Does anyone knows whether there exists already such a filter in any java class and where to get it? I would also be grateful for the accurate description of the composition of an excel spreadsheet file (including header, ...).
Thanks in advance
0
Comment
Question by:trouvain
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 32

Expert Comment

by:jhance
ID: 1233125
I'm not aware of any Java class that write directly to the Excel 5.0 file format.  I'd recommend a different approach, however.  The problem with using a direct to Excel approach is that you are then dependent upon the version of Excel that is installed on the computer and you never know if/when MS will change the file format.  They don't guarantee that it will remain constant.  The .csv format, howver, it widely used and is very easy to both create from another program and use in another non-Excel program.  You might want to consider just dumping your data to a csv file which can be opened by Excel without any special processing.  In other words, Excel know how to deal with a csv file by default.
0
 

Author Comment

by:trouvain
ID: 1233126
This answer was very poor indeed. Firstly I asked for the excel save format for my reasons. At the other hand I didn't found any description of the .csv format.
0
 
LVL 5

Expert Comment

by:fontaine
ID: 1233127
The best option is to output your data as columns, tab or comma separated. This is easy Java programming and such files are exportable to Excell (all versions!), MS Access (all versions!), and all similar spreadsheet or database, even not from Microsoft!

Example:

Save this in test.txt:

Column1,Column2
Data1,Data2

Open Excell, go to "File|Open...", select test.txt. Excell will prompt a wizard for exporting the file to the spreadsheet!
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:trouvain
ID: 1233128
Sorry,
but this is also too poor for 200 points. I have to provide a more convenient way for my clients to load spreadsheet. I can't tell them to handle import wizards and so on.
0
 
LVL 5

Expert Comment

by:fontaine
ID: 1233129
If you use tabs, it is only click "Finish" on the wizard panel automatically poped up when trying to open the file. Difficult, indeed!
0
 
LVL 6

Expert Comment

by:jpk041897
ID: 1233130
The only alternative I can think of, would be to use a JDBC-ODBC bridge on the server side.

In this fashion, you could populate an Excel file via ODBC calls, you would then need a signed JNI class to actualy write (or copy) the contents to your clients box. (Or FTP as an alternative).
0
 
LVL 5

Expert Comment

by:fontaine
ID: 1233131
trouvain, in my previous answer, simply turn test.txt into test.csv and this time no wizard is needed to export the data to the spreadsheet.
0
 

Author Comment

by:trouvain
ID: 1233132
I don't see any progress in these answers. But I have one remark for fontaine:
Perhaps you should have mentioned the quoting. I've found it somewhere else.
0
 
LVL 5

Accepted Solution

by:
fontaine earned 200 total points
ID: 1233133
trouvain, you have not tried what I proposed, otherwise you would have accepted the answer!

You rejected my previous answer because of the wizard that was popping up. I then solved this problem. The file to download had to be a file with a ".csv" extension and the syntax I described: columns separated by commas. I *ALWAYS* test my answers. Please do the same!


0
 
LVL 5

Expert Comment

by:fontaine
ID: 1233134
And in case one item should include a comma, simply double quote it.

Example:

aaa,bbb,"ccc,ddd" -> 3 colums in Excell, the last being ccc,ddd
0
 
LVL 5

Expert Comment

by:fontaine
ID: 1233135
If the item contrains a double quote, double it:

Example:

aaa,bbb,"ccc,d""dd" -> 3 colums in Excell, the last being ccc,d"dd
0
 

Author Comment

by:trouvain
ID: 1233136
Do you wanna kidding me? I said I've found the solution for the quoting problem. But it doesn't matter if you test your answers. It seems to me that nobody is able to answer the core question. Since I found no F-Button I'm going to make you a gift with 200 points in it!
0
 
LVL 5

Expert Comment

by:fontaine
ID: 1233137
Trouvain, if you had taken a look at the section about questions waiting to be graded, you would have seen that I am currently answering to 1 question on 4 on this site! Experts have no money for answering the questions, only the pleasure to help someone else. If you have no respect for this, don't be astonish if you have no more help!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Running JavaFX on JDeveloper 12C 1 116
ejb entity bean example 2 57
Glassfish admin console not working 1 88
Why is enum singleton a better approach than static factory 3 66
An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Suggested Courses

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question