Actually using tabs is like running from the problem,
it the data contains a tab, then it would mess things
up again...you'll need to do a litle encoding for
this...
if the data is like..
Col1 Col2 col3
data1 data"2 data,3
data"4 data,5 data6
data,7 data8 data"9
then you output as
"col1","col2","col3"
"data1","data""2","data,3"
"data""4","data,5","data6"
"data,7","data8","data""9"
note here that these should be no space between the
deliminator comma and the enclosing double-quote. If
there is one, excel will presume the data not encoded
and treat the enclosing double quotes as part of the
element breaking on the first comma it hits...this is
one reason i donot use tabs, you could as well switch
to tabs instead of commas as long as you use the
double quotes, but tabs make it visually harder to
notice if spaces were pumped after the deliminator and
before the enclosing dbl-quotes... i use the following
code to accomplish this...
public class CSVEncoder{
private static final char DOUBLE_QUOTE = '"';
private static final String COMMA_DOUBLE_QUOTE =
",\"";
/**
* This method encodes a String array into a row for
a CSV file
* ex:
* if you were to pass in the following array
* String[] values = {"data1", "data\"2", "data3"};
*
* using a syntax like..
* String encodedValus = CSVEncoder.encode(values);
*
* you'd get...
* "\"data1\",\"data\"\"2\",\"data,3\""
*
* appearing as...
* "data1","data""2","data,3"
* in the data stream
**/
public static String encode(String[] columns){
if(columns.length < 1)
return null;
StringBuffer encoded = new StringBuffer();
// encode the firstElement
encoded.append(DOUBLE_QUOTE);
encoded.append(encode(columns[0]));
encoded.append(DOUBLE_QUOTE);
//encode the rest of'em
int maxLoop = columns.length;
for (int loop = 1; loop < maxLoop; loop++){
encoded.append(COMMA_DOUBLE_QUOTE);
encoded.append(encode(columns[loop]));
encoded.append(DOUBLE_QUOTE);
}
return encoded.toString();
}
/**
* encodes a given string into a CSV safe format
* ex:
* if you were to pass a String...
* String value = "6\"3\', 190.5 cm"; //6"3', 190.5
cm
*
* using a syntax like...
* String encodedValue = CSVEncoder.encode(value);
*
* you'd get...
* "6""3', 190.5 cm"
*
* which would look like...
* 6""3', 190.5 cm
* in the data stream
**/
public static String encode(String s){
StringBuffer encoded = new StringBuffer();
int maxLoop = s.length();
for(int loop = 0; loop < maxLoop; loop++){
char theChar = encoded.charAt(loop);
//if the current char is a dbl-quote, stuff another
dbl-quote into the buffer
if(theChar == DOUBLE_QUOTE)
encoded.append(DOUBLE_QUOTE);
encoded.append(theChar);
}
return encoded.toString();
}
}
--- Erin Lester <[EMAIL PROTECTED]>
wrote:
> Actually that's how it was formatted. I tried what
> someone else
> suggested, using tabs instead of commas, and that
> worked.
>
> Thanks,
> Erin
>
> On Thu, 19 Jul 2001, Paul Kofon wrote:
>
> ><>Hi,
> ><>I've never done what you'd like to do. But if I
> remember correctly, the CSV
> ><>text below will display correctly in Excel:
> ><>
> ><>Item,Price
> ><>Doll,30
> ><>GameBoy,200
> ><>
> ><>You'd have two rows and two columns (with headers
> Item and Price) of data.
> ><>If the CSV data you're feeding Excel is not
> formatted like this, then
> ><>chances are that you'd get wrong results - that
> explains why your single row
> ><>of data isn't showing up correctly.
> ><>
> ><>Regards,
> ><>
> ><>Paul
> ><>
> ><>>From: William Kaufman <[EMAIL PROTECTED]>
> ><>>Reply-To: [EMAIL PROTECTED]
> ><>>To: "'[EMAIL PROTECTED]'"
> <[EMAIL PROTECTED]>
> ><>>Subject: RE: Generate Excel File
> ><>>Date: Wed, 18 Jul 2001 17:58:12 -0700
> ><>>
> ><>>We've got it working exactly as you say, but
> only if the data is delimited
> ><>>with _tabs_, not _commas_.
> ><>>
> ><>> -- Bill
> K.
> ><>>
> ><>> > -----Original Message-----
> ><>> > From: Erin Lester
> [mailto:[EMAIL PROTECTED]]
> ><>> > Sent: Wednesday, July 18, 2001 2:21 PM
> ><>> > To: [EMAIL PROTECTED]
> ><>> > Subject: Generate Excel File
> ><>> >
> ><>> >
> ><>> > Hi, I'm trying to generate an excel file as
> the response
> ><>> > returned from a
> ><>> > servlet. I have set the response's content
> type to
> ><>> > "application/vnd.ms-excel" and this seems to
> cause excel to open. The
> ><>> > data I return is comma separated values.
> ><>> >
> ><>> > Once upon a time I read on a newsgroup (don't
> think it was
> ><>> > this one) that
> ><>> > someone had done the same thing and excel
> interpreted the
> ><>> > data as a csv
> ><>> > file and it displayed nicely in excel.
> Unfortunately excel
> ><>> > seems to think
> ><>> > that the entire row of comma separated values
> that I'm
> ><>> > sending it is one
> ><>> > single cell (not a row of cells which are the
> values between
> ><>> > the commas).
> ><>> >
> ><>> > Can anyone tell me where I'm going wrong or
> of another way to
> ><>> > generate an
> ><>> > excel spreadsheet using JSP?
> ><>> >
> ><>> > Thanks!
> ><>> > Erin
> ><>> >
> ><>
> ><>
>
><>_________________________________________________________________
> ><>Get your FREE download of MSN Explorer at
> http://explorer.msn.com/intl.asp
> ><>
>
__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/
