import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import jxl.write.*;
import jxl.write.biff.RowsExceededException;
import java.util.List;
import java.util.ArrayList;
import java.util.Iterator;
import java.io.File;
import java.io.IOException;
public class WriteExecl {
private String location;
private DataSource ds;
public WriteExecl(String filename){
ds=new DataSource();
location=filename;
}
public List<Score> chaxun(){
List<Score> list=new ArrayList<Score>();
Connection con=ds.open();
Statement st=null;
ResultSet rs=null;
String sql="select * from execl ";
try{
st=con.createStatement();
rs=st.executeQuery(sql);
while(rs.next()){
Score score=new Score();
score.setSid(rs.getString("sid"));
score.setName(rs.getString("name"));
score.setHb(rs.getString("hb"));
score.setDpj(rs.getString("dpj"));
score.setZw(rs.getString("zw"));
score.setDmt(rs.getString("dmt"));
score.setJava(rs.getString("java"));
score.setRjgc(rs.getString("rjgc"));
score.setZjwh(rs.getString("zjwh"));
score.setWxjs(rs.getString("wxjs"));
list.add(score);
}
}catch(java.sql.SQLException e){
System.out.println("取出数据异常");
e.printStackTrace();
}finally{
if(rs!=null){
try{rs.close();}catch(java.sql.SQLException e1){
System.out.println("rs关闭异常");
rs=null;
}
if(st!=null){
try{
st.close();
}catch(java.sql.SQLException e2){
System.out.println("st关闭异常");
st=null;
}
if(con!=null){
try{
con.close();
}catch(java.sql.SQLException e3){
con=null;
}
}
}
}
}
return list;
}
public int WriteExecl(){
int i=1;
List<Score> list=chaxun();
//System.out.println("list的长度"+list.size());
try {
jxl.write.WritableWorkbook wb=jxl.Workbook.createWorkbook(new File(location));
jxl.write.WritableSheet sheet=wb.createSheet("网络工程",0);
jxl.write.WritableSheet sheet2=wb.createSheet("电子商务",1);
jxl.write.WritableSheet sheet3=wb.createSheet("英语",2);
jxl.write.WritableSheet sheet4=wb.createSheet("工业设计",3);
jxl.write.Label labe0=new jxl.write.Label(0,0,"学号");
jxl.write.Label labe11=new jxl.write.Label(1,0,"学生姓名");
jxl.write.Label labe1=new jxl.write.Label(2,0,"汇编");
jxl.write.Label labe2=new jxl.write.Label(3,0,"单片机");
jxl.write.Label labe3=new jxl.write.Label(4,0,"组网");
jxl.write.Label labe4=new jxl.write.Label(5,0,"多媒体技术");
jxl.write.Label labe5=new jxl.write.Label(6,0,"java");
jxl.write.Label labe6=new jxl.write.Label(7,0,"软件工程");
jxl.write.Label labe7=new jxl.write.Label(8,0,"计算机安装维护");
jxl.write.Label labe8=new jxl.write.Label(9,0,"文件检索");
sheet.addCell(labe0);
sheet.addCell(labe1);
sheet.addCell(labe2);
sheet.addCell(labe3);
sheet.addCell(labe4);
sheet.addCell(labe5);
sheet.addCell(labe6);
sheet.addCell(labe7);
sheet.addCell(labe8);
sheet.addCell(labe11);
Iterator it=list.iterator();
String content=null;
while(it.hasNext()){
Score score=(Score)it.next();
for(int j=0;j<10;j++){
switch(j){
case 0:content=score.getSid();
break;
case 1:content=score.getName();
break;
case 2:content=score.getHb();
break;
case 3:content=score.getDpj();
break;
case 4:content=score.getZw();
break;
case 5:content=score.getDmt();
break;
case 6:content=score.getJava();
break;
case 7:content=score.getRjgc();
break;
case 8:content=score.getZjwh();
break;
case 9:content=score.getWxjs();
break;
}
jxl.write.Label label=new jxl.write.Label(j,i,content);
try {
sheet.addCell(label);
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
i++;
}
wb.write();
wb.close();
} catch (IOException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
return i;
}
public static void main(String[] args) {
int j=new WriteExecl("test.xls").WriteExecl();
System.out.println("完成了"+(j-1)+"名同学的execl文档生成");
System.out.println("生成的execl文档名字为:test.xls");
System.out.println("请注意查看");
}
}
