最近在写一个从Excle题库抽题组卷在jsp显示的功能，其中用到了一些算法和技术，在此做记录。
那么，正题开始。博主使用的是ssm框架，本文仅供参考。
一、准备工作
Question类（对应Excle表中的每一个记录）
public class Question {
private String number;
private String context;
private String answerA;
private String answerB;
private String answerC;
private String answerD;
private String trueAnswer;
private String complain;
//setter&getter
}
Excle内容：
编写java读取Excle文件内容的工具类：
参见文章开头链接。
二、各部分代码。
1、servlet（action）
package com.moyi.ssm.stu.action;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Random;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import com.moyi.ssm.stu.entity.Excle;
import com.moyi.ssm.stu.entity.Question;
import com.moyi.ssm.stu.tool.ReadExcelUtils;
@Controller
@RequestMapping(value="/show")
public class showQuestion {
@RequestMapping(value="/showQuestion")
public void login(HttpServletResponse response,HttpServletRequest request,HttpSession session) throws IOException {
try {
String filepath = "F:\\Qustiontest.xls";
ReadExcelUtils excelReader = new ReadExcelUtils(filepath);
// 对读取Excel表格标题测试
String[] title = excelReader.readExcelTitle();
System.out.println("获得Excel表格的标题:");
for (String s : title) {
System.out.print(s + " ");
}
System.out.println("");
// 对读取Excel表格内容测试
Map<Integer, Map<Integer,Object>> map = excelReader.readExcelContent();
List<Question> questionList = new ArrayList<Question>();
List<String> answerList = new ArrayList<String>();
System.out.println("获得Excel表格的内容:");
/*全部内容
for (int i = 1; i <= map.size(); i++) {
System.out.println(map.get(i));
Question qs=new Question();
// for (int j = 0; j < map.get(i).size(); j++) {
// System.out.println(map.get(i).get(j));
// }
qs.setNumber(map.get(i).get(0).toString());
qs.setContext(map.get(i).get(1).toString());
qs.setAnswerA(map.get(i).get(2).toString());
qs.setAnswerB(map.get(i).get(3).toString());
qs.setAnswerC(map.get(i).get(4).toString());
qs.setAnswerD(map.get(i).get(5).toString());
qs.setTrueAnswer(map.get(i).get(6).toString());
qs.setComplain(map.get(i).get(7).toString());
questionList.add(qs);
}
*///第一个for
/*
//获取随机序号
for(int i=1;i<6;i++) {
Random ran = new Random(); //创建一个随机产生数类Scanner
int randomN = ran.nextInt(map.size()+1); //随机产生0-size的数
randomN++;
System.out.println(randomN);
Question qs=new Question();
// for (int j = 0; j < map.get(i).size(); j++) {
// System.out.println(map.get(i).get(j));
// }
qs.setNumber(map.get(randomN).get(0).toString());
qs.setContext(map.get(randomN).get(1).toString());
qs.setAnswerA(map.get(randomN).get(2).toString());
qs.setAnswerB(map.get(randomN).get(3).toString());
qs.setAnswerC(map.get(randomN).get(4).toString());
qs.setAnswerD(map.get(randomN).get(5).toString());
qs.setTrueAnswer(map.get(randomN).get(6).toString());
qs.setComplain(map.get(randomN).get(7).toString());
questionList.add(qs);
answerList.add(map.get(randomN).get(6).toString());
}*///第二个for
//随机从map.size()中抽取5个不同的数字组成试题
// 筛选随机数组大小
int k = 5;
// 待筛选数组最大上标
int n = map.size();
int[] numbers = new int[n];
for (int i = 0; i < numbers.length; i++) {
numbers[i] = i + 1;
}
int[] results = new int[k];
for (int i = 0; i < results.length; i++) {
// 取出一个随机数
int r = (int) (Math.random() * n);
results[i] = numbers[r];
// 排除已经取过的值
numbers[r] = numbers[n - 1];
n--;
}
Arrays.sort(results);
for (int i : results) {
System.out.print(i + " ");
Question qs=new Question();
qs.setNumber(map.get(i).get(0).toString());
qs.setContext(map.get(i).get(1).toString());
qs.setAnswerA(map.get(i).get(2).toString());
qs.setAnswerB(map.get(i).get(3).toString());
qs.setAnswerC(map.get(i).get(4).toString());
qs.setAnswerD(map.get(i).get(5).toString());
qs.setTrueAnswer(map.get(i).get(6).toString());
qs.setComplain(map.get(i).get(7).toString());
questionList.add(qs);
answerList.add(map.get(i).get(6).toString());
}
session.setAttribute("answerList", answerList);
session.setAttribute("questionList", questionList);
// for (Excle excle : excleList) {
// System.out.println(excle);
// }
response.sendRedirect("../views/zcs/showQuestion.jsp");
} catch (FileNotFoundException e) {
System.out.println("未找到指定路径的文件!");
e.printStackTrace();
}catch (Exception e) {
e.printStackTrace();
}
}
@RequestMapping(value="/judgeAnswer")
public void judge(HttpServletResponse response,HttpServletRequest request,HttpSession session) {
//获取到界面传过来的答案
String s=request.getParameter("checkProduct");
//System.out.println(s+"=============");
//将答案字符串分解
String []ss=s.split(",");
session.setAttribute("yourAnswer", ss);
// for(String k:ss) {
// System.out.println(k);
// }
//获取正确答案
List<String> answerList = (List<String>)session.getAttribute("answerList");
//System.out.println("@@@@@@@@@@@@"+answerList);
//定义一个判断结果的boolean数组
boolean []judge=new boolean[answerList.size()];
int truenum=0;
for(int i=0;i<answerList.size();i++) {
if(ss[i].equals(answerList.get(i))) {
judge[i]=true;
truenum++;
}else {
judge[i]=false;
}
System.out.println(judge[i]);
}
session.setAttribute("allnum", answerList.size());
session.setAttribute("truenum", truenum);
session.setAttribute("testresult", judge);
try {
response.sendRedirect("../views/zcs/QuestionResult.jsp");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
2、ReadExcelUtil.java
package com.moyi.ssm.stu.tool;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* 读取Excel
*
* @author littlewheat
*/
public class ReadExcelUtils {
private Logger logger = LoggerFactory.getLogger(ReadExcelUtils.class);
private Workbook wb;
private Sheet sheet;
private Row row;
public ReadExcelUtils(String filepath) {
if(filepath==null){
return;
}
String ext = filepath.substring(filepath.lastIndexOf("."));
try {
InputStream is = new FileInputStream(filepath);
if(".xls".equals(ext)){
wb = new HSSFWorkbook(is);
}else if(".xlsx".equals(ext)){
wb = new XSSFWorkbook(is);
}else{
wb=null;
}
} catch (FileNotFoundException e) {
logger.error("FileNotFoundException", e);
} catch (IOException e) {
logger.error("IOException", e);
}
}
/**
* 读取Excel表格表头的内容
*
* @param InputStream
* @return String 表头内容的数组
* @author littlewheat
*/
public String[] readExcelTitle() throws Exception{
if(wb==null){
throw new Exception("Workbook对象为空！");
}
sheet = wb.getSheetAt(0);
row = sheet.getRow(0);
// 标题总列数
int colNum = row.getPhysicalNumberOfCells();
System.out.println("colNum:" + colNum);
String[] title = new String[colNum];
for (int i = 0; i < colNum; i++) {
// title[i] = getStringCellValue(row.getCell((short) i));
title[i] = row.getCell(i).getStringCellValue();
}
return title;
}
/**
* 读取Excel数据内容
*
* @param InputStream
* @return Map 包含单元格数据内容的Map对象
* @author littlewheat
*/
public Map<Integer, Map<Integer,Object>> readExcelContent() throws Exception{
if(wb==null){
throw new Exception("Workbook对象为空！");
}
Map<Integer, Map<Integer,Object>> content = new HashMap<Integer, Map<Integer,Object>>();
sheet = wb.getSheetAt(0);
// 得到总行数
int rowNum = sheet.getLastRowNum();
row = sheet.getRow(0);
int colNum = row.getPhysicalNumberOfCells();
// 正文内容应该从第二行开始,第一行为表头的标题
for (int i = 1; i <= rowNum; i++) {
row = sheet.getRow(i);
int j = 0;
Map<Integer,Object> cellValue = new HashMap<Integer, Object>();
while (j < colNum) {
Object obj = getCellFormatValue(row.getCell(j));
cellValue.put(j, obj);
j++;
}
content.put(i, cellValue);
}
return content;
}
/**
*
* 根据Cell类型设置数据
*
* @param cell
* @return
* @author littlewheat
*/
private Object getCellFormatValue(Cell cell) {
Object cellvalue = "";
if (cell != null) {
// 判断当前Cell的Type
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:// 如果当前Cell的Type为NUMERIC
case Cell.CELL_TYPE_FORMULA: {
// 判断当前的cell是否为Date
if (DateUtil.isCellDateFormatted(cell)) {
// 如果是Date类型则，转化为Data格式
// data格式是带时分秒的：2013-7-10 0:00:00
// cellvalue = cell.getDateCellValue().toLocaleString();
// data格式是不带带时分秒的：2013-7-10
Date date = cell.getDateCellValue();
cellvalue = date;
} else {// 如果是纯数字
//DecimalFormat df = new DecimalFormat("0");
//cellvalue = df.format(cell.getNumericCellValue());
// 取得当前Cell的数值
cell.setCellType(Cell.CELL_TYPE_STRING);
//cellvalue = String.valueOf(cell.getNumericCellValue());
cellvalue = String.valueOf(cell.getStringCellValue());
}
break;
}
case Cell.CELL_TYPE_STRING:// 如果当前Cell的Type为STRING
// 取得当前的Cell字符串
cellvalue = cell.getRichStringCellValue().getString();
break;
default:// 默认的Cell值
cellvalue = "";
}
} else {
cellvalue = "";
}
return cellvalue;
}
public static void main(String[] args) {
try {
String filepath = "F:\\test.xls";
ReadExcelUtils excelReader = new ReadExcelUtils(filepath);
// 对读取Excel表格标题测试
String[] title = excelReader.readExcelTitle();
System.out.println("获得Excel表格的标题:");
for (String s : title) {
System.out.print(s + " ");
}
System.out.println("");
// 对读取Excel表格内容测试
Map<Integer, Map<Integer,Object>> map = excelReader.readExcelContent();
System.out.println("获得Excel表格的内容:");
for (int i = 1; i <= map.size(); i++) {
System.out.println(map.get(i));
for (int j = 0; j < map.get(i).size(); j++) {
System.out.println(map.get(i).get(j));
}
}
} catch (FileNotFoundException e) {
System.out.println("未找到指定路径的文件!");
e.printStackTrace();
}catch (Exception e) {
e.printStackTrace();
}
}
}
3、jsp界面
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<%
String phone = (String)request.getSession().getAttribute("phone");
%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<link rel="icon" href="<%=basePath %>views/gj/img/favicon.png" type="image/x-icon">
<title>非物质文化遗产</title>
<script src="<%=basePath %>views/lcx/js/jquery-3.3.1.min.js" charset="UTF-8"></script>
<script type="text/javascript">
function CheckProduct() {
//获取单选框的选中状态
product = document.getElementsByTagName('input');
checkProduct = [];
var num=0;
for(k in product){
if (product[k].checked){
checkProduct.push(product[k].value);
num++;
}
}
if(num<5){
alert("请填写完整");
}else{
alert("你的答案是"+checkProduct);
//提交
var isDel = confirm("确认提交吗？");
if (isDel){
//将checkProduct数组当做参数传递到servlet
window.location.href = "<%=basePath %>show/judgeAnswer?checkProduct="+checkProduct;
}//if
}//else
}//function
</script>
</head>
<body οnlοad="startTime()">
<form action="" method="get">
<%int i=1; %>
<h3 align="center">模拟考试</h3>
<div style="text-align: left">
<c:forEach items="${questionList}" var="question" >
<p><%=i++ %>.${question.context }</p>
<ul >
<li><label><input type="radio" name="answer_${question.number }" value="A">A.${question.answerA }</label></li>
<li><label><input type="radio" name="answer_${question.number }" value="B">B.${question.answerB }</label></li>
<li><label><input type="radio" name="answer_${question.number }" value="C">C.${question.answerC }</label></li>
<li><label><input type="radio" name="answer_${question.number }" value="D">D.${question.answerD }</label></li>
</ul>
<!-- <p>${question.complain }</p> -->
<p>　</p>
<p>　</p>
<p>　</p>
</c:forEach>
<div style="text-align: center"><button id="tijiao" type="button" οnclick="CheckProduct()" style="text-align: center" >交卷</button></div>
</div>
</form>
</body>
</html>
效果图：
①抽题显示
②评卷解析
总结一下，实现起来比较简单，通过poi读取excle中内容，保存到List中，通过servlet选择性地传递给jsp，在jsp中通过js获取到考生提交的答案，保存在数组中并传递给servlet，在servlet中将正确答案与提交的答案比对，通过session将结果反馈给界面。
完成这个功能前前后后用了好几天（过年了，总是拖沓┭┮﹏┭┮），通过这篇博客记录下来，如果能够帮到读者，万分有幸，欢迎互相交流学习啊！
1497
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
