【配置准备】
因为项目采用的是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();
- }
- }