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> <a
				href="${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%"
				οnclick="uploadExcel()">导入学生</a>
		</form>
	</div>

前台JS：

	//导入excel
		function 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表格的表头，map
			LinkedHashMap<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 是否有文件上传,即多部分请求importDailyResult
		if (multipartResolver.isMultipart(request)) {
			// 转换成多部分request
			MultipartHttpServletRequest 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();

			}
			// 把刚获取的列存入list
			list.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中--start
					if (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
				}
			}
			// 将学生信息导入到数据库-start
			if (studentList.size() > 0) {
				boolean flag = studentBean.saveEntitys(studentList);
				if (flag == true) {
					result = "success";
				}
			}
			// 将学生信息导入到数据库-end
		}
		jacksonJsonUntil.beanToJson(response, result);
	}

【导出具体实现】

前台JS：

		//导出Excel
		function 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();
		}
	}