tags:

views:

401

answers:

5
+1  Q: 

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"&gt;
<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 ซิตี้แบงก์.

A: 

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).

BobMcGee  2010-02-02 04:21:03
iam getting correct result(ซิตี้แบงก์, ถ.รัชดาภิเษก 3 (เทเลคอมทาวเวอร์)) in the grid. but while exporting iam getting a problem.
Ravikumar  2010-02-02 04:32:15
what are you exporting to? If it's not Unicode, you can't represent the character, so it has to encode it specially in that fashion.
BobMcGee  2010-02-02 04:37:35
A: 

iam getting correct result(ซิตี้แบงก์, ถ.รัชดาภิเษก 3 (เทเลคอมทาวเวอร์)) in the grid. but while exporting iam getting a problem. –

Ravikumar  2010-02-02 04:33:28
A: 

  1. 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.

  2. If the problem is that your lib doesn't support I18N, see a workaround It's not pretty but works well.

  3. I notice that you have the page set to ISO-8859-1 character set. Sure that shouldn't be UTF-8?

Larry K  2010-02-02 05:19:04
+1  A: 

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"/>
Blessed Geek  2010-02-02 07:06:28
A: 

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");

Ravikumar  2010-02-04 08:37:41

related questions

Java Time Zone is messed up
Eclipse on win64
Automate builds for Java RCP for deployment with JNLP
Why are professors or schools picking Java over C++ to teach to students?
Is there a real benefit of using J#?
Public/Popular Websites using JavaServer Faces
Why can't I use a try block around my super() call?
Accessing post variables using Java Servlets
Personal Linux web server
Is this really widening vs autoboxing?
How can I Java webstart multiple, dependent, native libraries?
Why can't I call toString() on a Java primitive?
How do I use Java to read from a file that is actively being written?
What code analysis tools do you use for your Java projects?
IllegalArgumentException or NullPointerException for a null parameter?
How do I configure and communicate with a serial port?
What is the best way to parse strings in Java
Getting started with a custom JXTA PeerGroup
Creating a custom button in Java
How to get started "writing" a code coverage tool?
Which Build-/Configuration Management Tool?
What is the difference between an int and an Integer in Java/C#?
What is the meaning of the type safety warning in certain Java generics casts?
How would you access Object properties from within an object method?
Converting CSV File to XML in Java