How to export Non-English Data from jsp file to excel file

[Neha Deshmukh]

Hi,
I am facing problem while exporting non-english data from jsp file to microsoft excel file.

Code is given below.

Problem: suppose request data is - L�histoire the output in exel file is:
Lhistoire.

( � has value \u2019)


Code:

String csvData=request.getParameter("csv");

response.setContentType("application/x-msexcel");
response.setHeader("Content-disposition", "attachment; filename=" + "searchresults.csv" );

out.println(request.getParameter("csv"));

So can you explain why this is happening and what will be the possible solutions?

[Ulf Dittmer]

Which character encoding are you using for the response - UTF-8?

[Neha Deshmukh]

Hi,
I am using UTF-8

[Neha Deshmukh]

I am facing similar problem for some chinese characters as well.
Is there alternative/solution to solve this problem?

[Ulf Dittmer]

I would create an Excel sheet that includes those characters, and then export it to CSV. That way you know what Excel can deal with. Then create the CSV to match what Excel creates itself.

[Neha Deshmukh]

Hi,

I am able to solve this problem by using:

response.setContentType("application/x-msexcel;charset=Unicode");

previously it was response.setContentType("application/x-msexcel); with UTF-8 Encoding.

Thnax all of you for your information.

But still I have one doubt can anybody tell me why it was not working for UTF-8 and working perfectly fine for Unicode encoding?

[Neha Deshmukh]

Previously when I used UTF-8 encoding,each column from jsp is exported to each cell in excel.But whne i am using Unicode encoding I am getting all data in one cell only seperated with comma which doesn't give table like look which was previously present(with UTF-8)
I opend both file (with UTF-8 and Unicode) in binary format.
Observation: For Unicode encoded binary file it is showing ex:
41 00 63 00 74 00 69 00

where as for UTF-8 it is showing
41 63 74 69

so can anybody help me how to eport data in excel cellwise?
plese reply as early as possible.Its very urgent

[Ulf Dittmer]

If you have trouble getting the commas to be recognized as delimiters, try double-quoting the content, like this:

"content cell 1","content cell 2","content cell 3",...

[Neha Deshmukh]

Hi,
The whole Data i got in JSP is String .I mean the data I got in JSP file is like:
1000,No Subject[#1000],ct1,4/11/2007 2:15:24 PM 1st Row
1010,No Subject[#1000],ct1,4/11/2007 3:21:52 PM 2nd Row

but when I export to Excel its format becomes
"1000","No Subject[#1000]","ct1","4/11/2007 2:15:24 PM"
"1010","No Subject[#1000]","ct1","4/11/2007 3:21:52 PM"

[Ulf Dittmer]

but when I export to Excel its format becomes
"1000","No Subject[#1000]","ct1","4/11/2007 2:15:24 PM"
"1010","No Subject[#1000]","ct1","4/11/2007 3:21:52 PM"

If you save that as a CSV file, Excel should have no problems importing it (and discarding the double quotes along the way).

[Neha Deshmukh]

I am exporting as .csv file only.But it is not working properly.And this problem is because of Unicode encoding only.Because it I use UTF-8 then it is properly exporting to excel.
So is ther any solution for this?

[Ulf Dittmer]

Well, there is no encoding called "Unicode", there's only "UTF-8" and "UTF-16" (and some other less common ones). If you create an Excel file containing Unicode characters, and then export it as CSV, does the file differ from the CSV file the web app creates? If so, how?

[Neha Deshmukh]

Ok I will send you code.
See I got data is jsp file Ex:
String csv= User name,Last name,
abc,abc,
�_L�histoire,L�histoire
提供新闻，杂志，论坛，导航信息。,杂志，论坛，
now the code in jsp is:
String csv=request.getParameter("csv");
response.setContentType("application/vnd.ms-excel;charset=Unicode");
response.setHeader("Content-disposition", "attachment; filename=" + "searchresults.csv" );
out.println(csv);

If I open .csv file with UTF-8 then french and chinese characters are showing garbage value.So I use charset=Unicode.This solves my problem of garbage values.But when I open .csv file then all data is placed in one cell only instead of mapping of column to cell.
I hope you will give me solution to this.

[Ulf Dittmer]

I've suggested twice to create an Excel sheet and export it to CSV, so that you know what Excel can deal with; you really should do this.

The code sample you showed looks a bit strange - you're taking the value of a request parameter, and then write it back to client in the response? Which transfer encodings are you using in that acse?

[Neha Deshmukh]

Hi,
Can you please tell me , programaticaaly how to crate excel sheet and exporting it to CSV?
Also what is tranfering encoding?
what transfering encoding should I use in this case?

[Ulf Dittmer]

Can you please tell me , programaticaaly how to crate excel sheet and exporting it to CSV?

You're missing the point. What I'm suggesting is that you create an XLS file manually -in Excel- that contains Unicode characters, and export that to CSV. If you can import that correctly back into Excel know at least know what Excel can deal with, so you know what the web app needs to generate.

Also what is tranfering encoding? what transfering encoding should I use in this case?

Well, in your example you are retrieving the CSV you're writing to the file from an HTTP request. So if you don't get proper results, you should investigate if that HTTP request is properly encoded (it should be encoded in "UTF-8").
[ May 02, 2007: Message edited by: Ulf Dittmer ]

[Neha Deshmukh]

Hi,
For the first point you mentioned-
I have tried exporting unicode charaters from Excel to CSV.
Unicode characters are properly seen in csv file.
For the second point-
I am using UTF-8 encoding only.
Still facing the problem mentioned above.

[Ulf Dittmer]

So how does the CSV file generated by Excel differ from the one created by the web app? Be specific, and tell us the binary values you're seeing (in short, meaningful, excerpts, of course).

[Neha Deshmukh]

u want binary file for the output?