最近用了一下java对excel进行操作,然后改了一下用到jsp里,不知道合不合适,能用而已,漏洞百出
说明:写的是一个成绩查询
文件包:java操作excel的包(略)
环境:win7/MyEclipse8.6/Tomcat6.x
首先写了个登陆框
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ page import= "org.apache.poi.hssf.usermodel.HSSFCell"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFRow"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFSheet"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFWorkbook"%>
<%@ page import="java.io.FileInputStream" %>
<%@ page import="java.io.FileNotFoundException" %>
<%@ page import="java.io.IOException" %>
<%@ page import="java.io.InputStream" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'index.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>
<form name="login" method="POST" action="check.jsp">
<center>
<p>查询条件选择</p><br/>
输入年级编号:<input type="text" name="grade"/><br/>
输入考试编号:<input type="text" name="testtime"/><br/>
输入学号或班级:<input type="text" name="checkNumber" /><br/>
按
<input type="radio" name="R" value="1"/>班级
<input type="radio" name="R" value="2"/>学号<br/>
<input type="submit" name="check" width="95px" height="32px" value="查询"/><br/>
说明:①输入前面不要留空格!
<br/>
②圆点选项必须对应填写得内容!<br/>
③考试编号可以为空!
</center>
</form>
<br>
</body>
</html>
接着是主题
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFCell"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFRow"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFSheet"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFWorkbook"%>
<%@ page import="java.io.FileInputStream" %>
<%@ page import="java.io.FileNotFoundException" %>
<%@ page import="java.io.IOException" %>
<%@ page import="java.io.InputStream" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'check.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>
<%
String radio=request.getParameter("R");
String grade=request.getParameter("grade");//获取年级
if(radio.isEmpty())
{
out.println("<br>请选择班级或学号进行查询");
return;
}
if(grade.isEmpty())
{
out.print("<br>查询年级为空!");
return;
}
int a=Integer.parseInt(radio);
HSSFWorkbook wb=null;
InputStream myxls = null;
try {
myxls = new FileInputStream("H:\\YLZX\\JWC\\200811.xls");
wb = new HSSFWorkbook(myxls);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
HSSFSheet sheet=wb.getSheet(grade);//输入单表的名称
HSSFRow row=null;
HSSFCell cell;
int i;
short y=0;
%>
<table border="2" color="green">
<%
// out.println("表格列数"+sheet.getLastRowNum());
/**
**输出表头
*/
row=sheet.getRow(0);
for (y=0;y<row.getLastCellNum();y++)
{
cell=row.getCell(y);
out.print("<td width=auto >");
//判断储存格的格式
switch ( cell.getCellType() )
{
case HSSFCell.CELL_TYPE_NUMERIC:
out.print(cell.getNumericCellValue());
//getNumericCellValue()会回传double值,若不希望出现小数点,请自行转型为int
break;
case HSSFCell.CELL_TYPE_STRING:
out.print( cell.getStringCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
out.print(cell.getNumericCellValue());
//读出公式储存格计算後的值
//若要读出公式内容,可用cell.getCellFormula()
break;
default:
out.print( "不明的格式");
break;
}//switch
out.println("</td>");
} //for y
out.println("</tr>");
%>
<%
// out.println("++++"+a+"++++");
int flag=0;
switch(a)
{
case 1: //输出班级查询,班级必须为文本类型
String num=request.getParameter("checkNumber");//获得查询班级编号
if(num.isEmpty())
{
out.println("<br>查询班级为空!");
return;
}
String testtime=request.getParameter("testtime");//获得考试编号
int leg=testtime.length();
// out.print("考试编号的长度是:"+leg);
if(leg==0)//没有输入考试编号的查询
{
for (i=1;i<=sheet.getLastRowNum();i++)
{
row=sheet.getRow(i);
//判断班级为关键字查询
cell=row.getCell((short)2);
String b=cell.getStringCellValue();
if(num.equals(b))
{
// out.println("equal");
out.println("<tr>");
for (y=0;y<row.getLastCellNum();y++)
{
cell=row.getCell(y);
out.print("<td width=auto>");
//判断储存格的格式
switch ( cell.getCellType() )
{
case HSSFCell.CELL_TYPE_NUMERIC:
out.print(cell.getNumericCellValue());
//getNumericCellValue()会回传double值,若不希望出现小数点,请自行转型为int
break;
case HSSFCell.CELL_TYPE_STRING:
out.print( cell.getStringCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
out.print(cell.getNumericCellValue());
//读出公式储存格计算後的值
//若要读出公式内容,可用cell.getCellFormula()
break;
default:
out.print( "不明的格式");
break;
}//switch
out.println("</td>");
} //for y
out.println("</tr>");
}//if
else{//无此班级信息
flag=1;
}
}//for i
}//if//没有输入考试编号的查询
else{//输入了考试编号的查询
for (i=1;i<=sheet.getLastRowNum();i++)
{
row=sheet.getRow(i);
//判断班级为关键字查询
cell=row.getCell((short)2);
String b=cell.getStringCellValue();//获取班级编号
cell=row.getCell((short)5);//获取考试编号
String testN=cell.getStringCellValue();
if(num.equals(b)&&testtime.equals(testN))//对班级和考试编号进行匹配 成功则查询
{
//out.println("equal");
flag=0;
out.println("<tr>");
for (y=0;y<row.getLastCellNum();y++)
{
cell=row.getCell(y);
out.print("<td width=auto >");
//判断储存格的格式
switch ( cell.getCellType() )
{
case HSSFCell.CELL_TYPE_NUMERIC:
out.print(cell.getNumericCellValue());
//getNumericCellValue()会回传double值,若不希望出现小数点,请自行转型为int
break;
case HSSFCell.CELL_TYPE_STRING:
out.print( cell.getStringCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
out.print(cell.getNumericCellValue());
//读出公式储存格计算後的值
//若要读出公式内容,可用cell.getCellFormula()
break;
default:
out.print( "不明的格式");
break;
}//switch
out.println("</td>");
} //for y
out.println("</tr>");
}//if
else{//无此班级和考试场次信息
flag=1;
}
}//for i
}//else输入了考试编号的查询
// out.println("末尾"+a);
break;
case 2://输入学号查询,学号必须为文本类型
String stdnum=request.getParameter("checkNumber");//获得查询的学号
String testN=request.getParameter("testtime");//获得考试编号
if(stdnum.isEmpty())
{
out.println("<br>查询学号为空!");
return;
}
// out.println("<br>"+stdnum+"hello World!");
//double num=Double.parseDouble(ChNum);
if(testN.isEmpty()){//不要求考试编号查询
for (i=1;i<=sheet.getLastRowNum();i++)
{
row=sheet.getRow(i);
//判断班级为关键字查询
cell=row.getCell((short)0);
String b=cell.getStringCellValue();
// out.println("++++++"+b);
if(stdnum.equals(b))
{
// out.println("equal");
flag=0;
out.println("<tr>");
for (y=0;y<row.getLastCellNum();y++)
{
cell=row.getCell(y);
out.print("<td width=auto >");
//判断储存格的格式
switch ( cell.getCellType() )
{
case HSSFCell.CELL_TYPE_NUMERIC:
out.print(cell.getNumericCellValue());
//getNumericCellValue()会回传double值,若不希望出现小数点,请自行转型为int
break;
case HSSFCell.CELL_TYPE_STRING:
out.print( cell.getStringCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
out.print(cell.getNumericCellValue());
//读出公式储存格计算後的值
//若要读出公式内容,可用cell.getCellFormula()
break;
default:
out.print( "不明的格式");
break;
}//switch
out.println("</td>");
} //for y
out.println("</tr>");
}//if
}//for i
}//if不要求考试编号查询
else{//要求对考试编号限制
for (i=1;i<=sheet.getLastRowNum();i++)
{
row=sheet.getRow(i);
//判断班级为关键字查询
cell=row.getCell((short)0);
String b=cell.getStringCellValue();
cell=row.getCell((short)5);
String TestNum=cell.getStringCellValue();
// out.println("++++++"+b);
if(stdnum.equals(b)&&testN.equals(TestNum))
{
// out.println("equal");
out.println("<tr>");
for (y=0;y<row.getLastCellNum();y++)
{
cell=row.getCell(y);
out.print("<td width=auto >");
//判断储存格的格式
switch ( cell.getCellType() )
{
case HSSFCell.CELL_TYPE_NUMERIC:
out.print(cell.getNumericCellValue());
//getNumericCellValue()会回传double值,若不希望出现小数点,请自行转型为int
break;
case HSSFCell.CELL_TYPE_STRING:
out.print( cell.getStringCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
out.print(cell.getNumericCellValue());
//读出公式储存格计算後的值
//若要读出公式内容,可用cell.getCellFormula()
break;
default:
out.print( "不明的格式");
break;
}//switch
out.println("</td>");
} //for y
out.println("</tr>");
}//if
}//for i
}//else要求要对考试编号 查询
// out.println("末尾"+a);
break;
case 3:
out.println(a);
break;
default:
out.print( "不明的查询");
break;
}
try {
myxls.close();//必须关闭输入流
} catch (IOException e) {
e.printStackTrace();
}
%>
</table>
</body>
</html>