springmvc导入导出

【配置准备】

       因为项目采用的是springmvc和ejb结合，采用maven仓库管理项目，前台使用easy-ui框架。①做导入导出就要引入相应的excel的jar包，在三层的pom.xml文件中添加依赖：

<!-- 下边是导入导入的Jar --><dependency><groupId>com.tgb</groupId><artifactId>itoo-excelV2.0-api</artifactId><version>${project.version}</version><scope>provided</scope></dependency><dependency><groupId>com.tgb</groupId><artifactId>itoo-excelV2.0-tool</artifactId><version>${project.version}</version></dependency>

②在springmvc.xml文件中的配置：

<!-- SpringMVC上传文件时，需要配置MultipartResolver处理器 --><bean id="multipartResolver"class="org.springframework.web.multipart.commons.CommonsMultipartResolver"><property name="defaultEncoding" value="UTF-8" /><!-- 指定所上传文件的总大小不能超过10485760000B。注意maxUploadSize属性的限制不是针对单个文件，而是所有文件的容量之和 --><property name="maxUploadSize" value="10485760000"></property><property name="maxInMemorySize" value="40960"></property></bean>

【导入具体实现】

前台jsp：

<div id="studentImport" class="easyui-window" title="批量导入学生"data-options="modal:true,closed:true,"style="width: 650px; height: 200px; padding: 10px;"><form id="Manage" method="post" enctype="multipart/form-data"action="" novalidate><br> <ahref="${pageContext.request.contextPath}/student/leadToExcelTemplet"class="easyui-linkbutton" style="width: 120px">点击下载模板</a> <br><br> <br> <input id="uploadExcel" name="uploadExcel"class="easyui-filebox" style="width: 70%"data-options="prompt:'选择文件...'"> <a href="#"class="easyui-linkbutton" style="width: 10%"onclick="uploadExcel()">导入学生</a></form></div>

前台JS：

//导入excelfunction uploadExcel() {//得到上传文件的全路径var fileName = $('#uploadExcel').filebox('getValue')//进行基本校验if (fileName == "") {$.messager.alert("提示","请选择上传文件！", "info");} else {//对文件格式进行校验var d1 = /\.[^\.]+$/.exec(fileName);if (d1 == ".xls" || d1==".xlsx") {$('#Manage').form('submit',{url : "${pageContext.request.contextPath}/student/importStudent",onSubmit : function() {return $(this).form('validate');},success : function(result) {var result = eval('(' + result+ ')');if (result == "error") {$.messager.alert("警告", "导入失败！","error");$('#studentImport').dialog('close'); $('#dg').datagrid('reload'); } else {$.messager.alert("提示", "导入成功！","info");$('#studentImport').dialog('close'); $('#dg').datagrid('reload'); }}});} else {$.messager.alert("提示","请选择xls格式文件！", "info");$('#uploadExcel').filebox('setValue', '');}}}

controller--导出模板：

@RequestMapping("/student/leadToExcelTemplet")public void leadToExcel(HttpServletRequest request,HttpServletResponse response) {excelUtil = new ExcelUtil();try {// excel表格的表头，mapLinkedHashMap<String, String> fieldMap = new LinkedHashMap<String, String>();fieldMap.put("code", "学号");fieldMap.put("name", "姓名");fieldMap.put("sex", "性别");fieldMap.put("classes.className", "所属班级");fieldMap.put("entranceDate", "入学日期");fieldMap.put("identityCardID", "身份证号");fieldMap.put("nativePlaceNativePlace", "籍贯");fieldMap.put("nation", "民族");fieldMap.put("politicalStatus", "政治面貌");fieldMap.put("accountAddress", "户口所在地");fieldMap.put("originalPlace", "生源地");fieldMap.put("graduatedSchool", "毕业学校");fieldMap.put("email", "电子邮箱");fieldMap.put("telNum", "手机");String sheetName = "学生";// 导出模板excelUtil.leadToExcel(fieldMap, sheetName, response);} catch (Exception e) {e.printStackTrace();}}

controller--导入：

@RequestMapping(value = "/student/importStudent", method = RequestMethod.POST)public void importStudent(HttpServletResponse response,HttpServletRequest request) throws Exception {// 创建一个通用的多部分解析器CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(request.getSession().getServletContext());InputStream inExcelFile = null;// 判断 request 是否有文件上传,即多部分请求importDailyResultif (multipartResolver.isMultipart(request)) {// 转换成多部分requestMultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;// 取得request中的所有文件名Iterator<String> iter = multiRequest.getFileNames();while (iter.hasNext()) {// 记录上传过程起始时的时间，用来计算上传时间int pre = (int) System.currentTimeMillis();// 取得上传文件MultipartFile file = multiRequest.getFile(iter.next());try {inExcelFile = file.getInputStream();} catch (IOException e) {e.printStackTrace();}}}// 创建一个list 用来存储读取的内容List list = new ArrayList();Workbook rwb = null;Cell cell = null;String result = "error";// 获取Excel文件对象try {rwb = Workbook.getWorkbook(inExcelFile);} catch (BiffException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}// 获取文件的指定工作表 默认的第一个Sheet sheet = rwb.getSheet(0);// 行数(表头的目录不需要，从1开始)for (int i = 0; i < sheet.getRows(); i++) {// 创建一个数组 用来存储每一列的值String[] str = new String[sheet.getColumns()];// 列数for (int j = 0; j < sheet.getColumns(); j++) {// 获取第i行，第j列的值cell = sheet.getCell(j, i);str[j] = cell.getContents();}// 把刚获取的列存入listlist.add(str);}List<Student> studentList = new ArrayList<Student>();if (list.size() > 0) {for (int i = 1; i < list.size(); i++) {Student student = new Student();Object[] object = (Object[]) list.get(i);String code = object[0].toString();// 学号String name = object[1].toString();// 姓名String sex = object[2].toString();// 性别String className = object[3].toString();// 班级String classesId = null;// 1.判断学生表中是否存在，根据学生code查询String studentCodeResult = "error";studentCodeResult = studentBean.queryByCode(code, dataBaseName);if (studentCodeResult == "error") {// 2.根据班级名称查 询班级id，如果不存在，需要打印日志，日志表明第几条数据，哪个字段有问题。List<Classes> classesList = studentBean.queryClassesByClassName(className, dataBaseName);if (classesList != null && classesList.size() > 0) {classesId = classesList.get(0).getId();} else {logger.info("第" + (i + 1) + "行的班级名称不规范");}// 将学生信息添加进studentList中--startif (studentCodeResult == "error" && classesId != null) {String id = CreateUUID22.getUUID22();student.setId(id);student.setCode(code);student.setName(name);student.setSex(sex);student.setClassesId(classesId);student.setDataBaseName(dataBaseName);studentList.add(student);}// 将学生信息添加进studentList中--end}}// 将学生信息导入到数据库-startif (studentList.size() > 0) {boolean flag = studentBean.saveEntitys(studentList);if (flag == true) {result = "success";}}// 将学生信息导入到数据库-end}jacksonJsonUntil.beanToJson(response, result);}

【导出具体实现】

前台JS：

//导出Excelfunction exportExcel() {document.getElementById("exportExcel").href = "${pageContext.request.contextPath}/student/exportStudent";}

controller：

@RequestMapping("/student/exportStudent")public void exportStudent(HttpServletRequest request,HttpServletResponse response) {String jobTitleName = null;// 1.获取要导出的教师集合（可以在页面选择也可以使所有教师，暂定++为所有教师）List<Student> studentList = studentBean.queryStudentAll(dataBaseName);// 2.创建Excel表头excelUtil = new ExcelUtil();LinkedHashMap<String, String> fieldMap = new LinkedHashMap<String, String>();fieldMap.put("code", "学号");fieldMap.put("name", "姓名");fieldMap.put("sex", "性别");String sheetName = "学生";try {// 导出模板ExcelUtil.listToExcel(studentList, fieldMap, sheetName, response);System.out.println("导出成功");} catch (Exception e) {e.printStackTrace();}}