aspose file tools*
The moose likes Other JSE/JEE APIs and the fly likes How to export Non-English Data from jsp file to excel file Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Java » Other JSE/JEE APIs
Bookmark "How to export Non-English Data from jsp file to excel file" Watch "How to export Non-English Data from jsp file to excel file" New topic
Author

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

Neha Deshmukh
Ranch Hand

Joined: Apr 04, 2007
Posts: 30
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
Marshal

Joined: Mar 22, 2005
Posts: 42918
    
  68
Which character encoding are you using for the response - UTF-8?
Neha Deshmukh
Ranch Hand

Joined: Apr 04, 2007
Posts: 30
Hi,
I am using UTF-8
Neha Deshmukh
Ranch Hand

Joined: Apr 04, 2007
Posts: 30
I am facing similar problem for some chinese characters as well.
Is there alternative/solution to solve this problem?
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42918
    
  68
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
Ranch Hand

Joined: Apr 04, 2007
Posts: 30
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
Ranch Hand

Joined: Apr 04, 2007
Posts: 30
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
Marshal

Joined: Mar 22, 2005
Posts: 42918
    
  68
If you have trouble getting the commas to be recognized as delimiters, try double-quoting the content, like this:

Neha Deshmukh
Ranch Hand

Joined: Apr 04, 2007
Posts: 30
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
Marshal

Joined: Mar 22, 2005
Posts: 42918
    
  68
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
Ranch Hand

Joined: Apr 04, 2007
Posts: 30
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
Marshal

Joined: Mar 22, 2005
Posts: 42918
    
  68
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
Ranch Hand

Joined: Apr 04, 2007
Posts: 30
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
Marshal

Joined: Mar 22, 2005
Posts: 42918
    
  68
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
Ranch Hand

Joined: Apr 04, 2007
Posts: 30
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
Marshal

Joined: Mar 22, 2005
Posts: 42918
    
  68
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
Ranch Hand

Joined: Apr 04, 2007
Posts: 30
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
Marshal

Joined: Mar 22, 2005
Posts: 42918
    
  68
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
Ranch Hand

Joined: Apr 04, 2007
Posts: 30
u want binary file for the output?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to export Non-English Data from jsp file to excel file