Retrieval of Information from Excel Sheet using JDBC-ODBC
Hello,
Currently I am writing a program that extracts information fromexcel sheet. This excel sheet have some values in Chinese. I am able to extract English character from the different column in excel, but if some column contain chineese valur then it prints ?. After that I looked on net and found some details in to convert Big5 (Chinese) character into unicode, but this is not working and in that case nothing is displayed. I am attaching the my code. In my code the 'customer' field contain chinese character.
import java.io.ByteArrayInputStream;
import java.io.InputStreamReader;
import java.io.BufferedReader;
import java.io.Reader;
import java.io.IOException;
import java.sql.*;
public class ExcelJDBC
{
Connection con;
Statement stmt;
public ExcelJDBC(String url)
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(java.lang.ClassNotFoundException e)
{
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try
{
con = DriverManager.getConnection(url, "", "");
stmt = con.createStatement();
}
catch(SQLException ex)
{
System.out.println("SQL EXCEPTION OCCURED");
System.err.println("SQLException: " + ex.getMessage());
ex.printStackTrace();
}
}
public void sql(String sql)
{
try
{
stmt.execute(sql);
}
catch(SQLException ex)
{
System.out.println("SQL EXCEPTION OCCURED");
System.err.println("SQLException: " + ex.getMessage());
ex.printStackTrace();
}
}
public void closeAll()
{
try
{
stmt.close();
con.close();
}
catch(SQLException ex)
{
System.out.println("SQL EXCEPTION OCCURED");
System.err.println("SQLException: " + ex.getMessage());
ex.printStackTrace();
}
}
//big5 to unicode conversion
private String b2u(String str2convert) throws IOException
{
System.out.println("The input string is :" + str2convert);
StringBuffer buffer = new StringBuffer();
byte[] targetBytes = str2convert.getBytes();
ByteArrayInputStream stream = new ByteArrayInputStream(targetBytes);
InputStreamReader isr = new InputStreamReader(stream,"Big5");
Reader in = new BufferedReader(isr);
int chInt;
while((chInt = in.read()) < -1)
{
buffer.append((char)chInt);
}
in.close();
System.out.println("The output string is :" + buffer.toString());
return buffer.toString();
}
public void sqlQuery(String sql)
{
try
{
ResultSet rc = stmt.executeQuery(sql);
if(rc != null)
{
int count = 0;
while (rc.next())
{
String value = "";
value = b2u(rc.getString("workorder"));
System.out.println("The workorder is : "+value);
String customer = rc.getString("Customer");
value = b2u(customer);
System.out.println("The Customer is : "+value);
count++;
}
System.out.println("Entering in loop " +count+ " time.\n");
}
}
catch(SQLException ex)
{
System.out.println("SQL EXCEPTION OCCURED");
System.err.println("SQLException: " + ex.getMessage());
ex.printStackTrace();
}
catch(IOException ex)
{
System.out.println("IO EXCEPTION OCCURED");
System.err.println("IOException: " + ex.getMessage());
ex.printStackTrace();
}
}
public static void main(String[] args)
{
String url = "jdbc:odbc:detailsdsn";
try
{
ExcelJDBC ej = new ExcelJDBC(url);
ej.sqlQuery("SELECT \"workorder\", \"Customer\" FROM \"Sheet1$\"");
ej.closeAll();
}
catch(Exception ex)
{
System.out.println("SQL EXCEPTION OCCURED");
System.err.println("SQLException: " + ex.getMessage());
ex.printStackTrace();
}
}
}
I will highly appriciate any answer of my problem.
Regards,
Ruchi
