db_excel.java

教材订购系统

package program;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class Db_excel {

	String DBDriver="sun.jdbc.odbc.JdbcOdbcDriver";
	String connectionStr="jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)}; DBQ=D:/Java_Work/BookManager/program/db/bookmanage.mdb";
	Connection con = null;
	Statement stmt = null;
	ResultSet rs = null;
 
	public Db_excel(String tname) throws RowsExceededException, WriteException, ClassNotFoundException, SQLException, IOException
	{
		work(tname);
	}
	
	public void work(String tname) throws ClassNotFoundException, SQLException, IOException, RowsExceededException, WriteException
	{
		if(tname.equals("书_inf")) 
			tname = "一览表";
		
		
		Class.forName(DBDriver);	 //加载驱动器
		con=DriverManager.getConnection(connectionStr,"",""); //连接数据库
	    stmt=con.createStatement();   //创建Statement对象
	    if(tname.equals("一览表"))
	    {
	    	tname = "书_inf";
	    	rs=stmt.executeQuery("Select * From 书_inf Order By 书号");
	    }
	    else if(tname.equals("购书"))
	    {
	    	tname = "缺书";
	    	rs=stmt.executeQuery("Select 书_inf.书号,书_inf.书名,书_inf.价格,"+tname+".数量,书_inf.出版社 From "+tname+",书_inf Where "+tname+".书号=书_inf.书号  Order By 书_inf.出版社");  //查询表
	    }	
	    else
	    	rs=stmt.executeQuery("Select 书_inf.书号,书_inf.书名,书_inf.价格,"+tname+".数量,书_inf.出版社 From "+tname+",书_inf Where "+tname+".书号=书_inf.书号 Order By 书_inf.书号");  //查询表
	   
	    WritableWorkbook wwb = Workbook.createWorkbook(new File("d:/"+tname+"表.xls"));
        WritableSheet ws = wwb.createSheet("Sheet1",0);
        
       
        ws.addCell(new Label(0,0,"书号"));
        ws.addCell(new Label(1,0,"书名"));
        ws.addCell(new Label(2,0,"价格"));
        if(tname.equals("库存"))
        {
        	ws.addCell(new Label(3,0,"数量"));
        	ws.addCell(new Label(4,0,"出版社"));
        }
        ws.addCell(new Label(3,0,"出版社"));
        int i=1;
        while(rs.next()) 
        {
        	ws.addCell(new Label(0,i,rs.getString("书号")));
            ws.addCell(new Label(1,i,rs.getString("书名")));
            ws.addCell(new Label(2,i,rs.getString("价格")));
            if(tname.equals("库存"))
            {
            	ws.addCell(new Label(3,i,rs.getString("数量")));
            	ws.addCell(new Label(4,i,rs.getString("出版社")));
            }
            else
            	ws.addCell(new Label(3,i,rs.getString("出版社")));
            i++;
        	
        }      
       
        
        wwb.write();
        wwb.close();
        stmt.close();    //关闭语句
		con.close();   //关闭连接	  
		Runtime   runtime =   Runtime.getRuntime();     
        runtime.exec("cmd /c start d:/"+tname+"表.xls");   

	}
	
}