report_data_state.java

1.员工添加 2.人事调动 3.在职人员管理 4.部门（分厂）管理

package pmsys.wyj;

import javax.servlet.http.HttpSession;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import java.io.*;
import jxl.*; 
import jxl.write.*; 

import pmsys.*;
import pmsys.wyj.*;

public class Report_data_state {
	public String data_state(DataSource ds,HttpServletRequest request,String sql,String tjdepts,String states) {
		int tjdept = Integer.parseInt(tjdepts);
		Connection conn = null; 
		WritableWorkbook book = null;
		WritableSheet sheet = null;	
		try{		
			conn = ds.getConnection();
			//建立Excel表
			book= Workbook.createWorkbook(new File("C:/states_" + tjdepts + ".xls"));
			sheet=book.createSheet("states",0);	//建立工作空间
			int j = 0;
			int k = 0;		
			String state = states;
			String sql1 = "SELECT * FROM states WHERE states_id=" + state;
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql1);
			rs.next();
				//添加表头，每一列的开始添加
				Label label = new Label(j,0,rs.getString("states_name"));
				j++;
				sheet.addCell(label);
				//String xingbie = states;		
				String sql2 = sql;
			
				PreparedStatement pstmt = conn.prepareStatement(sql2);
				pstmt.setString(1,state);
				pstmt.setInt(2,tjdept);
				ResultSet rs2 = pstmt.executeQuery();
				
				int i = 1;
				try {
					//找出存在的内容添加进Excel表工作区中    
					while(rs2.next()){
						Label label2 = new Label(k,i,rs2.getString("emp_name"));
						sheet.addCell(label2);
						i++;
					}
				} 
				catch(Exception e) {
					e.printStackTrace();
				}
				k++;
			}
			
        catch(Exception e) {
			e.printStackTrace();
        } finally {
        
	        		try {
	        			book.write();	
	        			book.close();
	        		}
					catch(Exception e) {
						e.printStackTrace();
					}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
		return "report data success!";
	}
}