一：前端
<t:dgToolBar title="Excel题库导入" icon="icon-search" onclick="questionImportListImportXls()"></t:dgToolBar>
<script type="text/javascript" charset="utf-8">
function questionImportListImportXls() {
openuploadwin('Excel题库导入', 'xueBaQuestionController.do?upload', "questionImportList");
}
</script>
二：openuploadwin
/**
* 创建上传页面窗口
*
* @param title
* @param addurl
* @param saveurl
*/
function openuploadwin(title, url,name,width, height) {
gridname=name;
$.dialog({
content: 'url:'+url,
cache:false,
button: [
{
name: '开始上传',
callback: function(){
iframe = this.iframe.contentWindow;
iframe.upload();
return false;
},
focus: true
},
{
name: '取消上传',
callback: function(){
iframe = this.iframe.contentWindow;
iframe.cancel();
}
}
]
}).zindex();
}
三：上传页面
<%@ page language="java" import="java.util.*" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@include file="/context/mytags.jsp"%>
<!DOCTYPE html>
<html>
<head>
<title>Excel题库导入</title>
<t:base type="jquery,easyui,tools"></t:base>
</head>
<body style="overflow-y: hidden" scroll="no">
<t:formvalid formid="formobj" layout="div" dialog="true" beforeSubmit="upload">
<fieldset class="step">
<div class="form"><t:upload name="fiels" buttonText="选择要导入的文件" uploader="xueBaQuestionController.do?importExcel" extend="*.xls;*.xlsx" id="file_upload" formData="documentTitle"></t:upload></div>
<div class="form" id="filediv" style="height: 50px"></div>
</fieldset>
</t:formvalid>
</body>
</html>
四：xueBaQuestionController处理导入题库
@RequestMapping(params = "upload")
public ModelAndView upload(HttpServletRequest req) {
return new ModelAndView("weixin/shyd/happycampus/xueba/questionUpload");
}
@RequestMapping(params = "importExcel", method = RequestMethod.POST)
@ResponseBody
public AjaxJson importExcel(HttpServletRequest request, HttpServletResponse response) {
AjaxJson j = new AjaxJson();
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
MultipartFile file = entity.getValue();// 获取上传文件对象
ImportParams params = new ImportParams();
params.setTitleRows(0);
params.setSecondTitleRows(1);
params.setNeedSave(false);
try {
List<XueBaQuestionEntity> questionList = GetAllImportQuestion(file.getInputStream());
for (XueBaQuestionEntity question : questionList) {
if(question.getContent()!=null){
xueBaQuestionService.saveQuestion(question);
}
}
j.setMsg("文件导入成功！");
} catch (Exception e) {
j.setMsg("文件导入失败！");
logger.error(ExceptionUtil.getExceptionMessage(e));
}finally{
try {
file.getInputStream().close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return j;
}
private List<XueBaQuestionEntity> GetAllImportQuestion(InputStream inputstream) {
POIFSFileSystem fs;
HSSFWorkbook wb;
HSSFSheet sheet;
HSSFRow row;
List<XueBaQuestionEntity> questionList = new ArrayList<XueBaQuestionEntity>();
List<XueBaOptionEntity> optionList;
XueBaQuestionEntity question = null;
try{
fs = new POIFSFileSystem(inputstream);
wb = new HSSFWorkbook(fs);
/** 遍历sheet **/
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
/** 获得当前sheet **/
sheet = wb.getSheetAt(i);
int num = 1;
for (int j = 1; j < sheet.getPhysicalNumberOfRows() ; j++) {
num++;
try{
question = new XueBaQuestionEntity();
optionList = new ArrayList<XueBaOptionEntity>();
/** 获得当前行情 **/
row = sheet.getRow(j);
if(row != null){
String qContent = getCellFormatValue(row.getCell(0)).trim();
String aOption = getCellFormatValue(row.getCell(1)).trim();
//题目或A选项为空就不保存
if(StringUtil.isEmpty(qContent) || StringUtil.isEmpty(aOption)){
logger.info("题库第" + num + "行题库或A选项为空，未保存");
continue;
}
String bOption = getCellFormatValue(row.getCell(2)).trim();
String cOption = getCellFormatValue(row.getCell(3)).trim();
String dOption = getCellFormatValue(row.getCell(4)).trim();
String eOption = getCellFormatValue(row.getCell(5)).trim();
String answer = getCellFormatValue(row.getCell(6)).trim();
System.out.println("qcontent:"+qContent);
/* 赋值问题实体 */
question.setContent(qContent);
if(answer.indexOf("，")>0){
question.setType(1);
}else{
question.setType(0);
}
question.setAnswer(answer);
//赋值选项实体
if (StringUtil.isNotEmpty(aOption)) {
XueBaOptionEntity aOptionEntity = new XueBaOptionEntity();
aOptionEntity = DealOption(aOptionEntity,aOption);
optionList.add(aOptionEntity);
}
if (StringUtil.isNotEmpty(bOption)) {
XueBaOptionEntity bOptionEntity = new XueBaOptionEntity();
bOptionEntity = DealOption(bOptionEntity,bOption);
optionList.add(bOptionEntity);
}
if (StringUtil.isNotEmpty(cOption)) {
XueBaOptionEntity cOptionEntity = new XueBaOptionEntity();
cOptionEntity = DealOption(cOptionEntity,cOption);
optionList.add(cOptionEntity);
}
if (StringUtil.isNotEmpty(dOption)) {
XueBaOptionEntity dOptionEntity = new XueBaOptionEntity();
dOptionEntity = DealOption(dOptionEntity,dOption);
optionList.add(dOptionEntity);
}
if (StringUtil.isNotEmpty(eOption)) {
XueBaOptionEntity eOptionEntity = new XueBaOptionEntity();
eOptionEntity = DealOption(eOptionEntity,eOption);
optionList.add(eOptionEntity);
}
question.setXueBaOptionList(optionList);
questionList.add(question);
}
}catch (Exception e) {
e.printStackTrace();
logger.info("题库第" + num + "行解析异常");
}
}
}
}catch (Exception e) {
e.printStackTrace();
}
return questionList;
}
private XueBaOptionEntity DealOption(XueBaOptionEntity optionEntity,
String option) {
int start = option.indexOf("、");
// System.out.println("option:"+option+" start:"+start);
String optionTitle = option.substring(0, start);
String optionContent = option.substring(start+1);
optionEntity.setTitle(optionTitle);
optionEntity.setContent(optionContent);
return optionEntity;
}
private String getCellFormatValue(HSSFCell cell) {
String cellvalue = "";
if (cell != null) {
// 判断当前Cell的Type
switch (cell.getCellType()) {
// 如果当前Cell的Type为NUMERIC
case HSSFCell.CELL_TYPE_NUMERIC:
case HSSFCell.CELL_TYPE_FORMULA: {
// 判断当前的cell是否为Date
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 如果是Date类型则，转化为Data格式
// 方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
// cellvalue = cell.getDateCellValue().toLocaleString();
// 方法2：这样子的data格式是不带带时分秒的：2011-10-12
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellvalue = sdf.format(date);
}
// 如果是纯数字
else {
// 取得当前Cell的数值
cellvalue = String.valueOf(cell.getNumericCellValue());
}
break;
}
// 如果当前Cell的Type为STRIN
case HSSFCell.CELL_TYPE_STRING:
// 取得当前的Cell字符串
cellvalue = cell.getRichStringCellValue().getString();
break;
// 默认的Cell值
default:
cellvalue = " ";
}
} else {
cellvalue = "";
}
return cellvalue;
}