//读取和写入Excel的类
import java.io.*;
import jxl.*;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import java.sql.*;
import java.util.ArrayList;
public class ReadXLS {
public ArrayList initconn() {
String url ="jdbc:oracle:thin:@192.168.8.212:1521:gt)";
ArrayList arr = new ArrayList();
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(url,"crm","crm");
PreparedStatement pst = conn.prepareStatement("select cust_no ,name from cust");
ResultSet rs = pst.executeQuery();
while (rs.next()){
Cust cust = new Cust();
cust.setCust_no( rs.getString("cust_no"));
cust.setCust_name(rs.getString("cust_name"));
arr.add(cust);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return arr;
}
public void writeExc(){
ArrayList arr = initconn();
try {
// 打开文件
WritableWorkbook book=Workbook.createWorkbook(new File("测试.xls"));
// 生成名为“第一页”的工作表,参数0表示这是第一页
WritableSheet sheet=book.createSheet("第一页",0);
// 在Label对象的构造子中指名单元格位置是第一列第一行(0,0)
// 以及单元格内容为test
for (int i = 0; i < arr.size(); i++) {
Cust cust = (Cust) arr.get(i);
Label label=new Label(0,i,cust.getCust_no());
Label label2=new Label(1,i,cust.getCust_name());
sheet.addCell(label);
sheet.addCell(label2);
}
// Label label=new Label(0,0,"test");
// 将定义好的单元格添加到工作表中
// sheet.addCell(label);
/**
* 生成一个保存数字的单元格 *必须使用Number的完整包路径,否则有语法歧义 *单元格位置是第二列,第一行,值为789.123
*/
// jxl.write.Number number = new jxl.write.Number(1,0,789.123);
// sheet.addCell(number);
// //
// 写入数据并关闭文件
book.write();
book.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void readExc(){
try {
Workbook book = Workbook.getWorkbook(new File("测试.xls"));
//获得第一个工作表对象
Sheet sheet = book.getSheet(0);
//得到第一列第一行的单元格
Cell cell1 = sheet.getCell(0, 0);
String result = cell1.getContents();
System.out.println(result);
book.close();
} catch (Exception e) {
System.out.println(e);
}
}
public static void main(String args[]) {
ReadXLS rxls = new ReadXLS();
rxls.writeExc();
}
}
public class Cust {
private String cust_name;
private String cust_no ;
public String getCust_name() {
return cust_name;
}
public void setCust_name(String cust_name) {
this.cust_name = cust_name;
}
public String getCust_no() {
return cust_no;
}
public void setCust_no(String cust_no) {
this.cust_no = cust_no;
}
}