首页 > 代码库 > Excel导入功能
Excel导入功能
一：前端
<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 = http://www.mamicode.com/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 =http://www.mamicode.com/ String.valueOf(cell.getNumericCellValue()); } break; } // 如果当前Cell的Type为STRIN case HSSFCell.CELL_TYPE_STRING: // 取得当前的Cell字符串 cellvalue =http://www.mamicode.com/ cell.getRichStringCellValue().getString(); break; // 默认的Cell值 default: cellvalue = " "; } } else { cellvalue = ""; } return cellvalue; }
Excel导入功能
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。