unable to export thai character into excel
Using this code
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@page import="java.io.*"%>
<%@page import="com.db.action.SearchFormDBImage"%>
<%@ page import=" java.util.*"%>
<%@page import ="org.apache.poi.hssf.usermodel.HSSFSheet"%>
<%@page import ="org.apache.poi.hssf.usermodel.HSSFWorkbook"%>
<%@page import ="org.apache.poi.hssf.usermodel.HSSFRow"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<meta http-equiv="refresh" content="600;URL=Logout"/>
<title>ExportToExcel</title>
</head>
<body >
<form name="" method="post" id="exportForm" >
<%! ServletOutputStream stream = null;
FileOutputStream fout = null;
RequestDispatcher dispatcher=null;
String userID="";
String CountryCode="";
%>
<%
userID=(String)session.getAttribute("userID");
CountryCode=(String)session.getAttribute("CountryCode");
if(userID==null || CountryCode==null ){
response.sendRedirect("SelectCountry.jsp");
}
else{
%>
<%
try
{
response.setHeader("Pragma", "");
response.setHeader("Expires","0");
response.setHeader("Cache-Control", "");
String filename = "SearchResults.xls";
response.setHeader("content-disposition","attachment; filename="+filename);
response.setContentType("application/excel");
OutputStream stream = null;
HSSFWorkbook wb=null;
HSSFRow row=null;
ArrayList alSearch;
List list_records=(List)session.getAttribute("alSearch");
if (list_records != null && !list_records.isEmpty())
{
int size =list_records.size();
wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
row = sheet.createRow((short)0);
row.createCell((short)(0)).setCellValue("Business Date");
row.createCell((short)(1)).setCellValue("Work Source ID");
row.createCell((short)(2)).setCellValue("Batch Number");
row.createCell((short)(3)).setCellValue("Trans ID");
row.createCell((short)(4)).setCellValue("Item Type");
row.createCell((short)(5)).setCellValue("Amount");
row.createCell((short)(6)).setCellValue("Stub Account Number");
row.createCell((short)(7)).setCellValue("Invoice No");
row.createCell((short)(8)).setCellValue("Cheque Acct Number");
row.createCell((short)(9)).setCellValue("Cheque Number");
row.createCell((short)(10)).setCellValue("Bank Branch Code");
row.createCell((short)(11)).setCellValue("Credit Note Indicator");
row.createCell((short)(12)).setCellValue("Unique Identifier");
row.createCell((short)(13)).setCellValue("Hope");
int rowIncrement=1;
int index=0;
for(index = 0; rowIncrement<=size; index++)
{
com.db.bean.Bean obj=(com.db.bean.Bean)list_records.get(index);
row = sheet.createRow((short)rowIncrement);
row.createCell((short)(0)).setCellValue((String)obj.getBusDate());
row.createCell((short)(1)).setCellValue((String)obj.getWSIDDesc());
row.createCell((short)(2)).setCellValue((String)obj.getBatchNum());
row.createCell((short)(3)).setCellValue((String)obj.getTransID());
row.createCell((short)(4)).setCellValue((String)obj.getItemType());
String AmountPaid="";
AmountPaid=(String)obj.getAmtPaid();
if(AmountPaid.length()>2 ){
AmountPaid=AmountPaid.substring(0,AmountPaid.length()-2)+"."+AmountPaid.substring(AmountPaid.length()-2,AmountPaid.length());
}
if(AmountPaid.length()==1){
AmountPaid="0.0"+AmountPaid;
}
if(AmountPaid.length()==2){
AmountPaid="0."+AmountPaid;
}
row.createCell((short)(5)).setCellValue(AmountPaid);
row.createCell((short)(6)).开发者_如何学运维setCellValue((String)obj.getStubAccNum());
row.createCell((short)(7)).setCellValue((String)obj.getInvoiceNo());
row.createCell((short)(8)).setCellValue((String)obj.getChequeAccNum());
row.createCell((short)(9)).setCellValue((String)obj.getChequeNum());
row.createCell((short)(10)).setCellValue((String)obj.getBankBranchCode());
row.createCell((short)(11)).setCellValue((String)obj.getCni());
row.createCell((short)(12)).setCellValue((String)obj.getUI());
row.createCell((short)(13)).setCellValue((String)obj.getEField2Value());
rowIncrement++;
}
stream = response.getOutputStream();
wb.write(stream);
stream.flush();
}
else
{
response.sendRedirect("SelectCountry.jsp");
}
}
catch ( Exception ex )
{
ex.printStackTrace();
System.out.println("Exception caught while generating the excel report: "+ex);
response.sendRedirect("ExcelNotFound.jsp");
}
finally
{
try{
stream.close();
session.removeAttribute("alSearch");
}
catch ( Exception ex )
{
ex.printStackTrace();
System.out.println("Exception caught while closing the the excel stream: "+ex);
}
}
%>
<%}%>
</body>
</html>
I am getting thai characters as 45IA, when it should be something like ซิตี้แบงก์.
Hey, I had the same problem.
I had set my system to accept Right-to-left languages and yet my Hebrew characters were not showing up but as a bunch of hex. I had set the eclipse project definition to default to utf-8 but still a bunch of hex showed up on the browser.
Then voila! I set the jsps/servlets to accept utf-8 too.
Either have this (for general output),
<%@ page pageEncoding="utf-8"%>
or this (for html output),
<%@ page contentType="text/html; charset=UTF-8" %>
or (for excel spreadsheet streams),
<%@ page contentType="application/vnd.ms-excel; charset=UTF-8" %>
in your jsp.
There are equivalent settings for servlets at the http response objects. For example,
setContentType( "text/html; charset=UTF-8" );
You see, ISO-8859-1 is the default enconding and I found out that it is pretty useless for internationalisation and why the net would still keep using it as the default when web pages are part of, if not the prime-mover of, the globalisation movement.
Perhaps, you could also do the following config at tomcat because http request params are part of the url in a get method. If you don't, you have to allow only post method in your web app if you have html form or http request parameters that are in Thai.
At $CATALINA_HOME/conf/server.xml, add the following line, where port# is the port of your http service :
<Connector port="port#" URIEncoding="UTF-8"/>
You need to have the Asian languages pack installed in Windows, I believe. The weird letters and numbers generally mean you lack a font containing that character (most fonts don't support Hindi/Chinese/Thai characters).
Are you sure that the HSSFWorkbook lib supports international characters? There are a lot of Excel libs out there that do not. One that does is the current version of the perl Excel lib. It writes I18N characters just fine. By the way, the default character set for Excel handles Chinese and lots of other languages just fine in Win XP SP 2 and Excel 2003. MS Arial covers an enormous amount of the Unicode space just to eliminate these sorts of character set issues.
If the problem is that your lib doesn't support I18N, see a workaround It's not pretty but works well.
I notice that you have the page set to ISO-8859-1 character set. Sure that shouldn't be UTF-8?
Thanks for giving solutions. I tried with the following code. it is working fine for me.
POI supports Thailand (and any Unicode characters) for writing Excel sheets fine. Just set the cell encoding to UTF-16 (by default it's ISO-8859-1 which does NOT support Chinese), and use a font that actually has the characters you want to display (e.g. Arial Unicode MS). For example:
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("New sheet");
HSSFFont font = wb.createFont();
font.setFontName("Arial Unicode MS");
HSSFCellStyle style = wb.createCellStyle();
style.setFont(font);
HSSFRow row = sheet.createRow((short) 0);
HSSFCell cell = row.createCell((short) 0);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(style);
cell.setCellValue("\u53f8");
加载中,请稍侯......
精彩评论