一：用于封装数据的类：
package org.sasu.pojo;/** * 部门列表类 */public class Department { private Integer id;//部门id private String name;//部门名称 private String code;//部门编号 private String category;//部门类别 private String attrib;//部门属性 public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getCode() { return code; } public void setCode(String code) { this.code = code; } public String getCategory() { return category; } public void setCategory(String category) { this.category = category; } public String getAttrib() { return attrib; } public void setAttrib(String attrib) { this.attrib = attrib; }}
二：编写Excel工具类，将Excel表的数据存储到集合中
package org.sasu.utils;import jxl.Sheet;import jxl.Workbook;import jxl.read.biff.BiffException;import org.sasu.pojo.Department;import java.io.File;import java.io.IOException;import java.util.ArrayList;import java.util.List;public class ExcelUtil { public static List<Department> getDepartmentData(File excelFile) { List<Department> departments = new ArrayList<>(); try { Workbook workbook = Workbook.getWorkbook(excelFile);// 创建工作簿对象绑定excel文件 Sheet sheet = workbook.getSheet(0);// 提取第一个工作表 int rows = sheet.getRows();// 提取excel文件第一个工作表共有多少行数据 // 判断数据符合要求（仅限第一列表头） if (sheet.getCell(0, 0).getContents().trim().equals(“部门名称”) && sheet.getCell(1, 0).getContents().trim().equals(“部门编码”) && sheet.getCell(2, 0).getContents().trim().equals(“部门类别”) && sheet.getCell(3, 0).getContents().trim().equals(“部门属性”) && sheet.getCell(4, 0).getContents().trim().equals(“序号”)) { for (int i = 1; i < rows; i ) {// 不包括表头 Department department = new Department(); department.setName(sheet.getCell(0, i).getContents().trim());// 几列几行 department.setCode(sheet.getCell(1, i).getContents().trim()); department.setCategory(sheet.getCell(2, i).getContents().trim()); department.setAttrib(sheet.getCell(3, i).getContents().trim()); department.setId(Integer.parseInt(sheet.getCell(4, i).getContents().trim())); departments.add(department);// 将每行的学生对象加入集合 } } } catch (BiffException | IOException e) { e.printStackTrace(); } return departments; }}
三:jsp页面用于选择文件
<%@ page contentType=”text/html;charset=UTF-8″ language=”java” %><html><head> <title>导入部门</title> <link rel=”stylesheet” href=”${pageContext.request.contextPath}/layui/css/layui.css” media=”all”> <script type=”text/javascript” src=”${pageContext.request.contextPath }/layui/layui.js”></script></head><body><table class=”layui-table”> <div class=”layui-card-body”> <form class=”layui-form” action=”import-dep.action” method=”post” enctype=”multipart/form-data”> <div class=”layui-form-item”> <label class=”layui-form-label”>Excel文件：</label> <div class=”layui-input-inline”> <input type=”file” name=”excel” class=”layui-input”><font color=”red”>${msg }</font><br/> </div> </div> <div class=”layui-form-item”> <div class=”layui-input-block”> <input type=”submit” value=”导入” class=”layui-btn”> </div> </div> </form> </div></table></body></html>
四：对数据进行处理
@RequestMapping(“dep-import.action”) public String depImport() { return “department/department-imp”; } @RequestMapping(“import-dep.action”) public String ExecuteImportDepart(MultipartFile excel, Model model, HttpServletRequest req) {// 执行学生信息导入 if (excel == null) { model.addAttribute(“msg”, “没有选择文件”); } else { String originalFileName = excel.getOriginalFilename().toLowerCase(); if (originalFileName.endsWith(“.xls”)) { String serverPath = req.getSession().getServletContext().getRealPath(“/”); System.out.println(serverPath); File rootFolder = new File(serverPath); String fileName = new Date().getTime() “.xls”; File serverFile = new File(serverPath, fileName); try { /* 正常执行过后，文件已经在服务器上了,接下来可以读取Excel中的内容了 */ excel.transferTo(serverFile); List<Department> departments = ExcelUtil.getDepartmentData(serverFile); String msg = “”; for (Department d : departments) { if (departmentService.save(d)) { msg = msg d.getCode() “-” d.getName() “==>导入成功” “<br/>”; } else { msg = msg “<font color=\”red\”>” msg d.getCode() “-” d.getName() “==>导入失败</font><br/>”; } } serverFile.delete(); req.setAttribute(“msg”, msg); return “department/department-imp”; } catch (Exception e) { req.setAttribute(“msg”, “出现异常，请重试！”); } } else { req.setAttribute(“msg”, “要求是Excel文件（扩展名是xls）”); } } return “department/department-imp”; }
