How to generate an excel file or csv file

Hi, thanks so m uch for your reply.

I would go .csv file first.

>>However, because it's a .jsp file, you might want to have the .csv file be an attachment (you get that "Save or Open" dialog box from the browser), in which case you have to make sure that you set content  disposition before sending the file to your output:

well, i don't quite understand the meaning. i can use a .jsp or a servlet. is there any difference between these two files?

What I meant by the "Save or Open" dialog box is when you click on a link, and instead of opening the requested page in the browser, you get a popup window that asks if you want to open the file with the correct application (Excel in this case) or save it to disk.  In order to get that behavior, its content disposition has to be attachment.

You're right that there's no difference between .jsp files and servlets  in what you want for the response content header.  However, there's a difference in what .jsp files and servlets put in the response header.  There are always 1 to 4 blank lines at the top of a .jsp-generated .html file, which is sometimes a problem for Excel.  Maybe only older versions of Excel.  And with a .jsp file, you'll want to be very careful about what you write to the output -- accidental writing of characters is less likely to happen from a servlet.

hi, thanks so much for your relpy.
well, i'm going to generate an excel file instead of a .csv.

response.setContentType("application/vnd.ms-excel");

then print out each line.  the .xsl file is generated ok except there are some lines are not lined up very well, namely, if some column has long text, it's extended to other line. any suggestion about the excel format? thanks very much for your reply.

also should the order of the code posted be:

response.setHeader( "Pragma", "public" );
response.setContentType("application/csv");
// other possibilities for contentType: application/vnd.ms-excel
String fileName = "myfile.csv";
response.setHeader ("Content-Disposition", "attachment; filename=\"" + fileName + "\"");

String csvFile = "";
while( rs.next() ) {
  String field1 = rs.getString("Field1");
  String field2 = rs.getString("Field2");
  csvFile += "\"" + field1 + "\",\"" + field2 + "\"";
}
out.println(csvFile);

---------------------------------------------------------------------
q1. should the content type be set first?

q2: amy you explain a little in detail about response.setHeader( "Pragma", "public" ); and response.setHeader ("Content-Disposition", "attachment; filename=\"" + fileName + "\"");

it seems the header was set twice here? thanks vey much.

Hi, thanks so much for your reply.

I have tried the csv file out. The current question is: when i open the .csv file in excel, some recods has long text, so the text doesn't look very well.
for example: column 5 has a long text, it seems its content is extended to column 1 of enxt line.
 or i'm not familar the format of excel ?

Did you put quotes around all of your values?

A .csv file should look like this:

"col1","col2","col3"
"val11","val12","val13"
"val21","val22","val23"

If you don't want to put long text into your .csv file, then truncate it.

If there are quotes in your long text, then you have to escape them:

"val""11"""

yeah, i have put all the quotes around all fields
may you explain in detail on how to excape the double quotes?

If there are quotes in your long text, then you have to escape them:

"val""11"""

hi, thanks for your reply.

if a field is:

this is a "double quotes"

so first i need to check if there is a " in the string, then i need to replace " with "" for each "
lastly i need put double quotes surround this field ?

so it would be
"this is a ""double quotes"""

is the above statement right?

That is exactly what I put in the previous answers, yes.

If you test it yourself, you would probably understand it more clearly.  Make a text file with the correct characteristics of your .csv file, then open it in Excel.

well, you didn't use the escape sign \ in your last reply

i'm just wondering how to put these together?

could you post an answer if a field look like the following text:
this is a "double quotes", and there is an embeded comma.  

--------------------------------------------------------------------------------
String csvFile = "";
while( rs.next() ) {
  String field1 = rs.getString("Field1");
  String field2 = rs.getString("Field2");
  csvFile += "\"" + field1 + "\",\"" + field2 + "\"";
}

-------------------------------------------------------------------
how to format the csvFile variable?

The text example for the field:
"val""11"""

was an example that you needed to write the code to produce.  As I showed you earlier, and you probably know if you've been programming in Java, the double quote must be escaped to be written to a String.  So yes, if you want more double quotes in your code, you will only get them if you escape them, too.

Have you tried to run the example code above?  This is a lot harder to do if you never test the code.  The errors you get will help you figure out how you really want to do it.

So try running this code, and look at the output.  You can print it to the screen or save it to a file.

>how to format the csvFile variable?
The csvFile variable is a String, so I'm not sure what your question is here.  That's its format:  String.

You might want to have good tutorials for Java and JSP as you work your way through this code, if it's new to you.

Sun has good tutorials for both:
Java:
http://java.sun.com/docs/books/tutorial/index.html
JSP:
http://java.sun.com/j2ee/tutorial/1_3-fcs/doc/JSPIntro.html

This is a nice simple intro to JSP:
http://www.apl.jhu.edu/~hall/java/Servlet-Tutorial/Servlet-Tutorial-JSP.html

This is a nicely organized Java FAQ:
http://burks.brighton.ac.uk/burks/language/java/jprogfaq/intro.htm

if a field is:
this is a "double quotes", and there is an embedded comma

how the csvFile +=?

------------------------------
well, i testing out the csv already, but some record does not look quiet right.

>if a field is:
this is a "double quotes", and there is an embedded comma

how the csvFile +=?

Okay, do you mean you want to know how to put a comma in a String?

This is a String with a comma:

String aVar = "abc,def";

In the CSV file, everything within the double quotes is within double quotes, so does not have to be escaped.  Except double quote itself, of course, and we've already talked about that.

>well, i testing out the csv already, but some record does not look quiet right.

Excellent.  Post the lines that you think are a problem, and the code you used to produce them, and we can help with that.

the question is:

if  a field has several lines , or has an empty line, then  this field in excel file is not displayed in one cell. actually it occupy several cells of rows in excel.

i'm not sure how to fix this?

for example a pragraph looks like:
-----------------------------------------------------------------------------------------------
A neglected area of research in the literature on leadership generally is the role of a leader who must dissolve an organization.  

This is in his opinion a topic that could emerge in an age of increasing complexity, downsizings, bankruptcy, and the consolidation of historic institutions (such as mainstream church denominations) -- to understand better the role of leadership during these upheavals.    
------------------------------------------------------------------------

the above text has an empty line , and also two paragraphs. when i copy and paste the text froma jsp application, it will be sent to the database.

when i display the above content, this filed is not displyed in a single cell in .csv file. actually it occupied several cells. not sure how to fix this?

Please post your code that you use to put the above sample text into a field in your .csv file.  Then post the line from the .csv file that is broken.  We'll be able to help you more from there.

hi lilyyan

make use of the component CSVWriter available at

http://ostermiller.org/utils/CSV.html

hope this'll helps.. it helped me !!

The Ostermiller CSVWriter is a useful utility.  Like most of the available CSV readers and writers, it tends not to handle unusual data well.  So make sure you take out newlines, 2-byte unicode characters, etc.  from your column text before handing it to the CSVWriter methods.

The same applies, only more so, to reading a .csv file with CSVWriter.  If any of the data is hand-entered by users, and if therefore there is fairly random text (special characters) in the fields, the read methods will throw an exception because of the data from the file.

hi, thanks for all replies.
mrcoffee365: sorry, i just got a chance to reply this question.  the following is a sample data.

the currecnt  issue, if there is a newline or empty in one field, this filed will span to other column when i open the csv file in excel.  

------------------------------------------------------------------------------------
<p class=MsoNormal><span style='color:black'><![if !supportEmptyParas]>&nbsp;<![endif]><o:p></o:p></span></p>

 <p class=MsoNormal><span style='color:black'>Research projects encompass a  variety of disciplines, including insect endocrinology, isoprenoid metabolism,  protein biochemistry, and synthetic organic chemistry. <span  style="mso-spacerun: yes">&nbsp;</span>Several enzymes involved in JH  biosynthesis are currently under investigation in our laboratory.<span  style="mso-spacerun: yes">&nbsp; </span>These include farnesyl diphosphate  synthase (FPPS), which catalyzes isoprenoid coupling to produce the carbon  backbone of JH, and farnesol oxidase (FO), which catalyzes the conversion of  farnesol to farnesal (i.e., alcohol to aldehyde oxidation), and isopentenyl  diphosphate isomerase (IPPI), which catalyzes the double bond migration of of  C5 isoprenoid diphosphates.<span style="mso-spacerun: yes">&nbsp; </span>Using  the tobacco hornworm, the spruce budworm, the cockroach (<i>Diploptera punctata</i></span><span
style='color:black'>), and fruit fly, as our insect models, we perform  biochemical studies, including <i>in vitro</i></span><span style='color:black'>  experiments using synthesized substrate analogs and inhibitors.<span  style="mso-spacerun: yes">&nbsp; </span><o:p></o:p></span></p>

the current is if the filed contain a new line or empty line, when i open the csv file in excel, the line with a new line or empty line between paragraph will start a new comlumn while diplayed in excel .

should the new line or empty line be removed? if so, how. thanks very much for your reply.

As I mentioned previously (in parallel thread Q_22821250), I'm using Excel 2007. Embedded newlines in a quote delimited field are not interpreted as field breaks. So if I have a record like:

    1022,"Field with newlines<CR><NL><CR><NL>",,"Embedded ,, commas"

Excel interprets this a four fields and displays it in four columns of a single row. If I change the CSV slightly and add extra spaces after the first comma like so:

    1022,  "Field with newlines<CR><NL><CR><NL>",,"Embedded ,, commas"

then Excel does not correctly interpret the delimiters and displays the record across multiple rows and columns as you say. Remove any extra spaces around comma field separators and see if that helps.

Regards,
Jim

hi, thanks a bunch for all replies.

well, i guess currently, i 'm knnd of messed up. i can try mrcoffee365's answer, or Jim's answer. also i almost want to try jakarta's poi option at this point.

maybe mrcoffee365's answer is the most simple way so far.

to Jim:

q1. you used : if (delim) twice in writeField() method. may you explain a little bit

q2. how do you use boolean nlines variable in your code. it seems only declared once and not used.

thanks very much for your reply.

HI, EVERYONE. FINALLY THE CSV FILE CAN BE DISPLAYED CORRECTLY BY USING MRCOFFEE365'S ANSWER !!

THANKS SO MUCH! I WILL CLEAN UP SOME CODE AND COME BACK SOON.: )))

Congrats!  Glad it worked for you.

Hi Page administrator,

Could you please change the accepted answer for this question to
mrcoffee365's reply which is answered at 09.13.2007 at 09:08AM PDT, ID: 19884875.

I'm not familiar with on how to assign only one accepted solution to multiple answers in the new EE system. Would please do me a favior? Thanks so much!