• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Which character encoding are you using for the response - UTF-8?
 
Neha Deshmukh
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,
I am using UTF-8
 
Neha Deshmukh
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am facing similar problem for some chinese characters as well.
Is there alternative/solution to solve this problem?
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 30
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 30
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If you have trouble getting the commas to be recognized as delimiters, try double-quoting the content, like this:

 
Neha Deshmukh
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 30
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 30
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 30
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 30
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 30
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
u want binary file for the output?
 
reply
    Bookmark Topic Watch Topic
  • New Topic