jxl 中文 亂碼 解決

使用 jxl 操作 excel , 當 java code 中的字串值是中文時,遭遇到亂碼狀況,從資料庫取出並寫到Excel 中的資料值是正常中文顯示, 從網路上的解決方式測試也無法解決.
----
WorkbookSettings wbsettings = new WorkbookSettings();
wbsettings.setEncoding("ISO-8859-1");

jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os,wbsettings); //建立Excel

----

Solution:
Compile java code use "javac -encoding BIG5 xxx.java" , 可解決.

JSP and MySQL

所需的JDBC Driver下載處
MySQL: 請按此
將jdbc Driver放至適當目錄位置(一般為{站台/WEB-INF/lib}或{tomcat}/common/lib)

----------------- index.jsp ----------------------
＜%@ page contentType="text/html; charset=Big5"
import="java.sql.*"%＞
＜HTML＞
＜HEAD＞
＜TITLE＞ResultSet變數的建立與記錄的取得＜/TITLE＞
＜/HEAD＞
＜BODY＞
＜CENTER＞
＜FONT SIZE = 5 COLOR = blue＞ResultSet變數的建立與記錄的取得＜/FONT＞
＜/CENTER＞
＜HR＞
＜CENTER＞
＜%
/*以下的程式請將要使用的驅動程式及建立連線語法的註解符號去除
* 建立資料庫連線,其中
* localhost代表本機(或以IP表示,如192.168.1.1)
* 3306代表MySQL所使用的網路port,不同的資料庫用不同的port,請依事實設定
* pubs3代表所連結的資料庫名稱
* useUnicode=true&characterEncoding=big5是設定傳值時所用的編碼系統為"big5"
* root為資料庫的使用者,其他資料庫使用者為sa
* 123456為密碼
*/
//載入JDBC驅動程式類別

//第一個 利用JDBC for MySQL 連結MySQL資料庫(複製mysql-connector-java-3.x.x-bin.jar,其中x代表版本)
//Class.forName("com.mysql.jdbc.Driver");
//Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/pubs3?useUnicode=true&characterEncoding=big5","root", "123456");

//第二個 利用Sybase jconnect 6.0 連結Sybase資料庫,(複製jconnect6/jconn3.jar)
//Class.forName("com.sybase.jdbcx.SybDriver");
//Connection conn = DriverManager.getConnection("jdbc:sybase:Tds:192.168.1.1:5000/pubs3?CHARSET=big5", "sa", "123456");

//第二個 利用Sybase jconnect 5.5 連結Sybase資料庫,(複製jconnect5_5/jconn2.jar)
//Class.forName("com.sybase.jdbc2.jdbc.SybDriver");
//Connection conn = DriverManager.getConnection("jdbc:sybase:Tds:192.168.1.1:5000/pubs3?CHARSET=big5", "sa", "123456");

//第三個 利用sourceforge提供的jtds.jdbc連結Sybase資料庫
// 亦可用此jdbc driver連結 MS-SQL資料庫,因為兩者核心是相同的(複製jtds-x.x.jar,其中x是代表版本)
//Class.forName("net.sourceforge.jtds.jdbc.Driver");
//以下這行是連到MS-SQL
//Connection conn = DriverManager.getConnection("jdbc:jtds:sqlserver://192.168.1.1:1433/pubs","sa","123456");
//以下這行是連到SYBASE
//Connection conn = DriverManager.getConnection("jdbc:jtds:sybase://192.168.1.1:5000/pubs3;CHARSET=big5","sa","123456");

//以下的程式碼都相同
//建立Statement物件
Statement stmt = conn.createStatement();

//建立ResultSet(結果集)物件
ResultSet rs;

//執行SQL敘述
rs = stmt.executeQuery("SELECT * FROM titles");

%＞
＜!-- 將搜尋出來的資料以表格的方式呈現 (本行是HTML的註解)--＞
＜TABLE bgcolor=DodgerBlue＞
＜TR bgcolor=SkyBlue＞
＜TD＞＜B＞title_id＜/B＞＜/TD＞
＜TD＞＜B＞title＜/B＞＜/TD＞
＜TD＞＜B＞type＜/B＞＜/TD＞
＜/TR＞
＜%
//利用while迴圈將資料表中的記錄列出
while (rs.next())
{
%＞
＜TR bgcolor=LightGoldenrodYellow＞
＜TD＞＜B＞＜%= rs.getString("title_id") %＞＜/B＞＜/TD＞
＜TD＞＜B＞＜%= rs.getString("title") %＞＜/B＞＜/TD＞
＜TD＞＜B＞＜%= rs.getString("type") %＞＜/B＞＜/TD＞
＜/TR＞
＜%
}
rs.close(); //關閉ResultSet物件
stmt.close(); //關閉Statement物件
conn.close(); //關閉Connection物件
%＞
＜/TABLE＞
＜/CENTER＞
＜/BODY＞
＜/HTML＞
------------- End index.htm ---------------------

遭遇 MySQL 回傳 error 時
java.sql.SQLException: null, message from server: "Host '192.168.x.x' is not allowed to connect to this MySQL server"


--- Solution
1)By default mysql does not support remote access...

2) you will have to specify this in the server first, (of course you will not be able to do this from a remote machine!!! unless you have remote desktop access)

3)To enable access from a client machine called nile, do the following after starting mysqld (or mysqld_safe) on the server, named delaware:

% mysql -u root -p
mysql＞ use mysql;
mysql＞ grant all privileges on *.* to usrname1@'%' identified by 'test' with grant option;
mysql＞ exit

The user name, usrname1, and the password, test, should be replaced by the real value.
One can then run mysql on nile as follows:

% mysql --host=delaware --user=usrname1 -ptest

To restrict access to a user from a specific host, change '%' in the grant command to a valid hostname or ip address.

java & Excel

0. 下載 jxl
http://www.andykhan.com/jexcelapi/download.html
1. exl.java

package num;
import java.io.*;
import jxl.*;
import jxl.write.*;
import jxl.format.*;
import java.util.*;
import java.awt.Color;
import java.sql.*;
public class exl{
public static void writeExcel(OutputStream os) throws Exception {
Connection cn = null;
Statement stmt = null;
Connection cn1 = null;
Statement stmt1 = null;
ResultSet rs= null;
ResultSet rsemp= null;
int totalRecord = 0;
int rowCount = 0;
Class.forName("oracle.jdbc.driver.OracleDriver");
cn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.1:1521:REP","a","pwd");
stmt = cn.createStatement();
...
rs = stmt.executeQuery(strqSql);
rs.next();
totalRecord=(new Integer(rs.getString("total_dept"))).intValue(); // 轉為數字
rs = null;
...
rs = stmt.executeQuery(strqSql);
rs.next();
totalRecord=totalRecord + (new Integer(rs.getString("total_emp"))).intValue();

rowCount=totalRecord/5;

jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os); //建立Excel
jxl.write.WritableSheet ws = wwb.createSheet("資料表",0); //建立 Sheet
GregorianCalendar gc = new GregorianCalendar(); // 建立日期
jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL,20, WritableFont.BOLD, false,UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
// 設定字型與格式
jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
wcfFC.setAlignment(jxl.format.Alignment.CENTRE); //設定欄位資料置中
wcfFC.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); //設定欄位邊框格式
// wcfFC.setVerticalAlignment(CENTRE);
jxl.write.Label labelC = new jxl.write.Label(0, 0, "天天 :" + (gc.get(GregorianCalendar.MONTH)+1) + " 明細表",wcfFC);
//設定第幾個欄位與資料內容
ws.mergeCells(0, 0, 4, 0); //合併儲存格
ws.addCell(labelC);
...
rs = stmt.executeQuery(strqSql);
int i=0;
int j=1;
while(rs.next())
{
wfc = new jxl.write.WritableFont(WritableFont.ARIAL,14, WritableFont.BOLD, false,UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
wcfFC = new jxl.write.WritableCellFormat(wfc);
wcfFC.setBackground(jxl.format.Colour.YELLOW);
wcfFC.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
labelC = new jxl.write.Label(i, j, rs.getString("CODE") + " " + rs.getString("DEPT"),wcfFC);
ws.setColumnView(i,32); //設定欄寬
ws.addCell(labelC);
j=j+1;
if(j%rowCount==0)
{
i=i+1;
j=1;
}
...
rsemp = stmt1.executeQuery(strqSql);
while(rsemp.next())
{
String Ename=rsemp.getString("ENAME");
if (Ename.indexOf("_") != -1) // 若英文名含有 "_"
Ename=Ename.substring(0,Ename.indexOf("_")); //取出 "_" 前的英文字
wfc = new jxl.write.WritableFont(WritableFont.ARIAL,14, WritableFont.NO_BOLD, false,UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
wcfFC = new jxl.write.WritableCellFormat(wfc);
if (!rsemp.getString("manager").equals("N") && !rsemp.getString("manager").equals("J")) // 某條件以上,將背景設定為粉紅色
wcfFC.setBackground(jxl.format.Colour.PINK); // 設定欄位背景顏色
wcfFC.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
labelC = new jxl.write.Label(i, j, rsemp.getString("EXT") + " " + rsemp.getString("CNAME") + " " +Ename,wcfFC);
ws.setRowView(j,385,false); // 設定列高
ws.addCell(labelC);
j=j+1;
if(j%rowCount==0)
{
i=i+1;
j=1;
}
}
}
SheetSettings settings = ws.getSettings();
settings.setFitHeight(1); // 設定預覽列印與列印成為一頁, 寬度
settings.setFitWidth(1); // 設定預覽列印與列印成為一頁, 高度
settings.setFitToPages(true);

//寫入Exel工作表
wwb.write();
//關閉Excel工作薄對像
wwb.close();
cn=null;
cn1=null;
}


//寫一個這樣的main方法測試一下。
public static void main(String[] args)throws Exception{
File f=new File("kk.xls");
f.createNewFile();
writeExcel(new FileOutputStream(f));
}
}



2 . 在 JSP 中
response.reset();
response.setContentType("application/vnd.ms-excel");
ExpTelExl.writeExcel(response.getOutputStream());
即可下載或打開 Excel.

JDBC connect string

1. Oracle
Class.forName( "oracle.jdbc.driver.OracleDriver" );
cn = DriverManager.getConnection( "jdbc:oracle:thin:@DbComputerNameOrIP:1521:SID", sUsr, sPwd );

#將 Oracle Client jdbc\lib\ 底下的 classes12.jar , 複製到 c:\classes12.jar
#Windows 環境變數 CLASSPATH 加入 c:\classes12.jar

2. SQL Server
Class.forName( "com.microsoft.jdbc.sqlserver.SQLServerDriver" );
cn = DriverManager.getConnection( "jdbc:microsoft:sqlserver://DbComputerNameOrIP:1433;databaseName=master", sUsr, sPwd );

#從 這裡 Down SQL Server JDBC Driver , 將解開後的 mssqlserver.jar,msbase.jar,msutil.jar 複製到 c:\
#Windows 環境變數 CLASSPATH 加入 c:\mssqlserver.jar,c:\msbase.jar,c:\msutil.jar

3.ODBC
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
Connection cn = DriverManager.getConnection( "jdbc:odbc:" + sDsn, sUsr, sPwd );

Get Microsoft Active Directory CN Attribute

This summary is not available. Please click here to view the post.

Tomcat context 設定

於 Tomcat 中建立 Context 方式
1.


2.
path="/eTEL" 代表網域名稱，就是http://localhost/eTEL。docBase="eTEL"代表網站的目錄位置，C:\Tomcat 5.0\webapps\eTEL



3.
debug則是設定除錯level，0表示最少的資訊，9代表最多的資訊。Reloadable則表示Tomcat在執行時，當class、web.xml被更新時，都會自動重新載入，不需要再啟動Tomcat.

Java JDBC connect to Oracle database

#將 Oracle Client jdbc\lib\ 底下的 classes12.jar , 複製到 c:\classes12.jar
#Windows 環境變數 CLASSPATH 加入 c:\classes12.jar

import java.sql.*;
public class connDB2 {
public static void main(String[] args)
{
Connection cn = null;
Statement stmt = null;
ResultSet rs= null;
try {
// Load the JDBC driver
String driverName = "oracle.jdbc.driver.OracleDriver";
Class.forName(driverName);
// Create a connection to the database
String serverName = "192.168.X.X";
String portNumber = "1521";
String sid = "ERP";
String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
String username = "system";
String password = "xxxx";
cn = DriverManager.getConnection(url, username, password);
if (cn == null) System.out.println("connection is null");
stmt = cn.createStatement();
if (cn == stmt) System.out.println("stmt is null");
// query sql statement
rs = stmt.executeQuery("select a from a where rownum <10");
System.out.println("測試資料");
while(rs.next())
{
System.out.println(rs.getString("a"));
}
// delete sql statement
String strSql = "delete from a";
stmt.execute(strSql);
// insert sql statement
strSql = "insert into a values('aaa')";
stmt.execute(strSql);

} catch (ClassNotFoundException cfe) {
// Could not find the database driver
cfe.printStackTrace();
} catch (SQLException sqle) {
sqle.printStackTrace();
// Could not connect to the database
}
catch (Exception e){
e.printStackTrace();
}
finally {
try
{
// System.out.println("out");
stmt.close();
cn.close();
} catch (Exception exception)
{
//exception.printStackTrace();
//System.exit(1);
}
}

}
}