📄 tj_type.java
字号:
HttpSession session = request.getSession();
String age2 = (String)session.getAttribute("age2");
String age3 = (String)session.getAttribute("age3");
String nianling = "年龄" + age2 + "岁到" + age3 + "岁";
//添加表头，每一列的开始添加
Label label = new Label(j,0,nianling);
j++;
sheet.addCell(label);
String xingbie = nianling;
String sql2 = "SELECT * FROM employees WHERE to_char(emp_birth,'yyyy') >= (to_char(sysdate,'yyyy')-'"+age3+"') AND to_char(emp_birth,'yyyy') <= (to_char(sysdate,'yyyy')-'" + age2 + "') AND dep_dep_id=" + tjdept;
Statement stmt = conn.createStatement();
ResultSet rs2 = stmt.executeQuery(sql2);
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();
}
}
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();
}
}
}
request.setAttribute("nianling_ok","report data success!");//导出成功设置属性
return mapping.findForward("tj_nianling_list");
}
//------------------------------------------------------------------------
/**
* 学历----生成部门列表
*/
public ActionForward xuelilist(ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) {
try{
DataSource ds = (DataSource)this.getDataSource(request,"oracledb");
TJ_List tl = new TJ_List();
ArrayList dlist = tl.list(ds);
request.setAttribute("deptlist",dlist);
} catch(Exception e1) {
e1.printStackTrace();
}
return mapping.findForward("tj_xueli");
}
/**
* 按学历统计查询
*/
public ActionForward xueli(ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) {
DynaActionForm dyform = (DynaActionForm) form;
String tjdept = (String) dyform.get("tjdept");
Connection conn = null;
Statement stmt = null;
try{
DataSource ds = (DataSource)this.getDataSource(request,"oracledb");
conn = ds.getConnection();
String sql5 = "SELECT max(xue_id) id FROM xuelis";
Statement stmt5 = conn.createStatement();
ResultSet rs5 = stmt5.executeQuery(sql5);
rs5.next();
int arr = rs5.getInt("id");
ArrayList dlist1[] = new ArrayList[arr];
for (int i = 0; i < dlist1.length; i++) {
dlist1[i] = new ArrayList();
}
//查找数据库中全部职称类型的记录
String sql = "SELECT * FROM xuelis";
stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
int k = 0;
int n = 0;
int h = 0;
String n1 = "";
for(int i = 0;rs.next(); i++){
int xue_id = rs.getInt("xue_id");
n = 0;
h = 0;
n1 = "";
//查找数据库中匹配职称类型的记录
String sql2 = "SELECT * FROM employees WHERE xue_xue_id=? AND dep_dep_id=?";
PreparedStatement pstmt = conn.prepareStatement(sql2);
pstmt.setInt(1,xue_id);
pstmt.setString(2,tjdept);
ResultSet rs2 = pstmt.executeQuery();
while(rs2.next()){
UserForm dept1 = new UserForm();
dept1.setDeptno(rs2.getInt("emp_id")+"");
dept1.setDname(rs2.getString("emp_name"));
dlist1[i].add(dept1);
n++;
k++;
}
n1 = n + "";
ArrayList dlist2 = new ArrayList();
UserForm dept3 = new UserForm();
dept3.setDeptno(n1);
dlist2.add(dept3);
request.setAttribute("j" + i,dlist2);
String dept = "dept" + i;
request.setAttribute(dept,dlist1[i]);
//查找全厂该职称总数
String sql4 = "SELECT * FROM employees WHERE xue_xue_id=?";
PreparedStatement pstmt4 = conn.prepareStatement(sql4);
pstmt4.setInt(1,xue_id);
ResultSet rs4 = pstmt4.executeQuery();
while(rs4.next()){
h++;
}
request.setAttribute("count" + i,h + "");
}
//查找当前部门名称
String sql3 = "SELECT * FROM departments WHERE dep_id=?";
PreparedStatement pstmt3 = conn.prepareStatement(sql3);
pstmt3.setString(1,tjdept);
ResultSet rs3 = pstmt3.executeQuery();
rs3.next();
request.setAttribute("dept",rs3.getString("dep_name"));
request.setAttribute("dep_id",rs3.getString("dep_id"));
//某部门总人数
request.setAttribute("dept_count",k + "");
} catch(Exception e1) {
e1.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
return mapping.findForward("tj_xueli_list");
}
/**
* 导出数据--学历
*/
public ActionForward report_date_xueli(ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) {
String tjdepts = request.getParameter("dept");
int tjdept = Integer.parseInt(tjdepts);
Connection conn = null;
WritableWorkbook book = null;
WritableSheet sheet = null;
try{
DataSource ds = (DataSource)this.getDataSource(request,"oracledb");
conn = ds.getConnection();
String sql = "SELECT * FROM xuelis";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
//建立Excel表
book= Workbook.createWorkbook(new File("C:/xueli_" + tjdepts + ".xls"));
sheet=book.createSheet("xueli",0); //建立工作空间
int j = 0;
int k = 0;
while(rs.next()){
//添加表头，每一列的开始添加
Label label = new Label(j,0,rs.getString(2));
j++;
sheet.addCell(label);
int xue_id = rs.getInt("xueli_id");
String sql2 = "SELECT * FROM employees WHERE xue_xue_id=? AND dep_dep_id=?";
PreparedStatement pstmt = conn.prepareStatement(sql2);
pstmt.setInt(1,xue_id);
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();
}
}
}
request.setAttribute("xueli_ok","report data success!");//导出成功设置属性
return mapping.findForward("tj_xueli_list");
}
//------------------------------------------------------------------------
/**
* 职务----生成部门列表
*/
public ActionForward zhiwulist(ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) {
try{
DataSource ds = (DataSource)this.getDataSource(request,"oracledb");
TJ_List tl = new TJ_List();
ArrayList dlist = tl.list(ds);
request.setAttribute("deptlist",dlist);
} catch(Exception e1) {
e1.printStackTrace();
}
return mapping.findForward("tj_zhiwu");
}
/**
* 按职务统计查询
*/
public ActionForward zhiwu(ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) {
ArrayList dlist1[] = new ArrayList[4];
for (int i = 0; i < 4; i++) {
dlist1[i] = new ArrayList();
}
DynaActionForm dyform = (DynaActionForm) form;
String tjdept = (String) dyform.get("tjdept");
Connection conn = null;
Statement stmt = null;
try{
DataSource ds = (DataSource)this.getDataSource(request,"oracledb");
conn = ds.getConnection();
//查找数据库中全部职称类型的记录
String sql = "SELECT * FROM zhiwus";
stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
int k = 0;
int n = 0;
int h = 0;
String n1 = "";
for(int i = 0;rs.next(); i++){
int zhiwu_id = rs.getInt("zhiwu_id");
n = 0;
h = 0;
n1 = "";
//查找数据库中匹配职称类型的记录
String sql2 = "SELECT * FROM employees e,emp_zhiwu ez WHERE e.emp_id=ez.emp_id AND ez.zhiwu_id=? AND e.dep_dep_id=?";
PreparedStatement pstmt = conn.prepareStatement(sql2);
pstmt.setInt(1,zhiwu_id);
pstmt.setString(2,tjdept);
ResultSet rs2 = pstmt.executeQuery();
while(rs2.next()){
UserForm dept1 = new UserForm();
dept1.setDeptno(rs2.getInt("emp_id")+"");
dept1.setDname(rs2.getString("emp_name"));
dlist1[i].add(dept1);
n++;
k++;
}
n1 = n + "";
ArrayList dlist2 = new ArrayList();
UserForm dept3 = new UserForm();
dept3.setDeptno(n1);
dlist2.add(dept3);
request.setAttribute("j" + i,dlist2);
String dept = "dept" + i;
request.setAttribute(dept,dlist1[i]);
//查找全厂该职称总数
String sql4 = "SELECT * FROM employees e,emp_zhiwu ez WHERE e.emp_id=ez.emp_id AND ez.zhiwu_id=?";
PreparedStatement pstmt4 = conn.prepareStatement(sql4);
pstmt4.setInt(1,zhiwu_id);
ResultSet rs4 = pstmt4.executeQuery();
while(rs4.next()){
h++;
}
request.setAttribute("count" + i,h + "");
}
//查找当前部门名称
String sql3 = "SELECT * FROM departments WHERE dep_id=?";
PreparedStatement pstmt3 = conn.prepareStatement(sql3);
pstmt3.setString(1,tjdept);
ResultSet rs3 = pstmt3.executeQuery();
rs3.next();
request.setAttribute("dept",rs3.getString("dep_name"));
request.setAttribute("dep_id",rs3.getString("dep_id"));
//某部门总人数
request.setAttribute("dept_count",k + "");
} catch(Exception e1) {
e1.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
return mapping.findForward("tj_zhiwu_list");
}
/**
* 导出数据--职务
*/
public ActionForward report_date_zhiwu(ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) {
String tjdepts = request.getParameter("dept");
int tjdept = Integer.parseInt(tjdepts);
Connection conn = null;
WritableWorkbook book = null;
WritableSheet sheet = null;
try{
DataSource ds = (DataSource)this.getDataSource(request,"oracledb");
conn = ds.getConnection();
String sql = "SELECT * FROM zhiwus";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
//建立Excel表
book= Workbook.createWorkbook(new File("C:/zhiwu_" + tjdepts + ".xls"));
sheet=book.createSheet("zhiwu",0); //建立工作空间
int j = 0;
int k = 0;
while(rs.next()){
//添加表头，每一列的开始添加
Label label = new Label(j,0,rs.getString(2));
j++;
sheet.addCell(label);
int zhiwu_id = rs.getInt("zhiwu_id");
String sql2 = "SELECT * FROM employees e,emp_zhiwu ez WHERE e.emp_id=ez.emp_id AND ez.zhiwu_id=? AND e.dep_dep_id=?";
PreparedStatement pstmt = conn.prepareStatement(sql2);
pstmt.setInt(1,zhiwu_id);
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();
}
}
}
request.setAttribute("zhiwu_ok","report data success!");//导出成功设置属性
return mapping.findForward("tj_zhiwu_list");
}
}⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -