aspose file tools*
The moose likes Struts and the fly likes Resultset to CSV Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Frameworks » Struts
Bookmark "Resultset to CSV" Watch "Resultset to CSV" New topic
Author

Resultset to CSV

Lalit Vora
Ranch Hand

Joined: Jun 22, 2006
Posts: 37
Hi All.
I want to generate reports. I am using struts. I want that I store result in CSV file and return url of csv so that when user clicks on url if he has excel report will open in excel otherwise in notepad. I dont want to generate report in jsp. So when user selects criteria and clicks on submit it will return url of csv and user can click on that url to open report in excel. I have resultset with me.
Can anyone guide me please

Thanks
Lalit
Merrill Higginson
Ranch Hand

Joined: Feb 15, 2005
Posts: 4864
The following should work:

1-find the actual location of your web application's context with the following statement:

String actualPath = getServlet().getServletContext().getRealPath("/");

2-Once you have this, you can write to a file in that directory using the classes in the java.io package.

3-Have your action forward to a JSP that has a link to the file you just created. Make sure it has a .csv extension.

You need to be aware, though, that giving the user the ability to write a file on the server opens up a lot of issues. You now have to worry about cleaning up the files in a timely manner. You are also more vulnerable to denial of service attacks that will keep requesting the report, thereby writing a file to your server over and over again until the disk is full.

You can avoid all this by not writing to the disk at all, but just writing to the HttpServletRequest Object's OutputStream. When you do this, the user's browser acts as though it's downloading a file without you actually having to write a file on your server.
[ July 05, 2006: Message edited by: Merrill Higginson ]

Merrill
Consultant, Sima Solutions
Lalit Vora
Ranch Hand

Joined: Jun 22, 2006
Posts: 37
Which classes will be useful to write to csv file? Do u have simple example so that i can understand in much better way? Please help me with it

Thanks
Merrill Higginson
Ranch Hand

Joined: Feb 15, 2005
Posts: 4864
There's nothing special about a CSV file. It's just a text file containing data separated by commas.

This link shows you how to write a text file. Just turn all your data into Strings, then concatenate them into a long String with commas separating the data elements that represent a row in the database. Terminate each row with a carriage return ("\n").

Here's an example: the following code writes a single row with 3 data elements to a CSV file:


[ July 05, 2006: Message edited by: Merrill Higginson ]
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42286
    
  64
Um, it's not quite as simple as that. CSV has a number of edge cases that need to be handled, like data containing commas, quotes, double quotes and newline characters. Before you have adapted your code to handle all those, it may be easier to use an existing class, like the Ostermiller CSV Utils.


Ping & DNS - my free Android networking tools app
Lalit Vora
Ranch Hand

Joined: Jun 22, 2006
Posts: 37
Hi Merrill,

I think i am able to write resultset to csv file. I have one problem. Below is my code

ResultSet rset = stmt.executeQuery("select * from stock");
ResultSet r1= rset;
try{
BufferedWriter out = new BufferedWriter(new FileWriter("C:\\j2sdk1.4.2\\demo\\applets\\newfile.csv"));
out.write("Name,Sex,value"+"\n");
while (rset.next())
{
System.out.println (r1.getString(1)); // Print col 1

System.out.println ("Entered");

This works //out.write(r1.getString(1));
*************************out.write(r1.getString(1),"M, 100.5"+"\n");
//out.close();
System.out.println ("Exit");
}
out.close();
}
catch (IOException e) { }

stmt.close();

I am getting error in line with * can not resolve symbol in bufferwriterclass out.write(.......)
^
Above that line is working and i can save result in csvfile. So can u guide me with it. Also how to return user url for this file? so that they can cleck and see report in Excel. Please guide me

Thanks
Lalit
Lalit Vora
Ranch Hand

Joined: Jun 22, 2006
Posts: 37
Hi ,
I forgot to ask will there be any issue if i am getting csv in same way and displaying to user
Merrill Higginson
Ranch Hand

Joined: Feb 15, 2005
Posts: 4864
Remeber that the write method takes only one parameter -- a String. By using a comma that is not in quotes, Java thinks you're entering another parameter.

You therefore need to change:

out.write(r1.getString(1),"M, 100.5"+"\n");

to

out.write(r1.getString(1)+", M, 100.5\n"

will there be any issue if i am getting csv in same way and displaying to user


The instructions I gave you in my first post tell you what your options are for displaying the CSV file to the user. You can either write the file in the web application's context area and provide the user with a link to it, or you can write it directly to the HttpServletResponse object's output stream.
[ July 06, 2006: Message edited by: Merrill Higginson ]
Lalit Vora
Ranch Hand

Joined: Jun 22, 2006
Posts: 37
Hi Merrill,

Thanks I will try that for showing to user. Code is running now. Thank you very much for your support.

I also want to know how to creat log and whats use of it.Any link or idea?

Thanks
Lalit
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Resultset to CSV