finish.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 Finish {
	
	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 Finish(String classno) throws ClassNotFoundException, SQLException, IOException, RowsExceededException, WriteException
	{
		int zhong = 0,i=2;
		String bclass,bno,bname,bcount,jiage;
		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;
		WritableWorkbook wwb = Workbook.createWorkbook(new File("d:/"+classno+"班取书单.xls"));
        WritableSheet ws = wwb.createSheet("Sheet1",0);
		
		Class.forName(DBDriver);	 //加载驱动器
		con=DriverManager.getConnection(connectionStr,"",""); //连接数据库	
		stmt=con.createStatement();   //创建Statement对象
		
		rs=stmt.executeQuery("Select * From Temp,书_inf Where Temp.书号=书_inf.书号");
		
		ExistSell 	ex	= new ExistSell();
		
		
		
        ws.addCell(new Label(2,0,"取书单"));
		ws.addCell(new Label(0,1,"书号"));
        ws.addCell(new Label(1,1,"书名"));
        ws.addCell(new Label(2,1,"价格"));
        ws.addCell(new Label(3,1,"数量"));
			
		while(rs.next())
		{
			bclass = rs.getString("班号");
			bno    = rs.getString("书号");
			bcount = rs.getString("数量");
			jiage  = rs.getString("价格"); 
			bname  = rs.getString("书名");
			zhong  = zhong + Integer.parseInt(bcount)*Integer.parseInt(jiage);
			
			ws.addCell(new Label(0,i,bno));
			ws.addCell(new Label(1,i,bname));
			ws.addCell(new Label(2,i,jiage));
			ws.addCell(new Label(3,i,bcount));
			i++;
			if(ex.YOrN(bno,bcount).equals("YY"))
			{
				YY yy = new YY(bclass,bno,bcount);	
			}
			if(ex.YOrN(bno,bcount).equals("YN"))
			{
				YN yn = new YN(bclass,bno,bcount);
			}
		}
        ws.addCell(new Label(3,i,"共"+zhong+"元"));
        wwb.write();
        wwb.close();
        Runtime   runtime =   Runtime.getRuntime();   
        runtime.exec("cmd /c start d:/"+classno+"班取书单.xls");   
        
		System.out.println(zhong);
		stmt.executeUpdate("delete from Temp");
		stmt.close();    //关闭语句
		con.close();   //关闭连接	  
	}
}