JeeSite的Excel导入、导出、支持大数据量，使用annotation最小化配置
介绍：
对Apache POI 3.9的简单封装，实现Excel的导出导入功能。使用Annotation定义导出导入字段。http://jeesite.com
优点：
- 简单易用，支持大数量导出，配置简单，代码量少。
- 支持Excel 2003、2007、2010（xls、xlsx）格式。
- 支持简单格式设置，对齐方式，排序等
- 可导出字典类型数据，自定义数据字段类型（例如：部门关联对象，部门名称与部门编号互转）。
- 无需建立导入模板，系统自动生成。
缺点：
- 格式单一，无法导出格式比较复杂的表格。
- 不能使用模板进行导入，导出。
分析开始 ：
后台框架：SpringMVC+mybatis
1 前台jsp界面：
<%@ page contentType="text/html;charset=UTF-8" %>
<%@ include file="/WEB-INF/views/include/taglib.jsp"%>
<html>
<head>
<title>用户管理</title>
<meta name="decorator" content="default"/>
<script type="text/javascript">
$(document).ready(function() {
$("#btnExport").click(function(){
top.$.jBox.confirm("确认要导出用户数据吗？","系统提示",function(v,h,f){
if(v=="ok"){
$("#searchForm").attr("action","${ctx}/sys/user/export");
$("#searchForm").submit();
}
},{buttonsFocus:1});
top.$('.jbox-body .jbox-icon').css('top','55px');
});
$("#btnImport").click(function(){
$.jBox($("#importBox").html(), {title:"导入数据", buttons:{"关闭":true},
bottomText:"导入文件不能超过5M，仅允许导入“xls”或“xlsx”格式文件！"});
});
});
function page(n,s){
if(n) $("#pageNo").val(n);
if(s) $("#pageSize").val(s);
$("#searchForm").attr("action","${ctx}/sys/user/list");
$("#searchForm").submit();
return false;
}
</script>
</head>
<body>
<div id="importBox" class="hide">
<form id="importForm" action="${ctx}/sys/user/import" method="post" enctype="multipart/form-data"
class="form-search" style="padding-left:20px;text-align:center;" onsubmit="loading('正在导入，请稍等...');"><br/>
<input id="uploadFile" name="file" type="file" style="width:330px"/><br/><br/>
<input id="btnImportSubmit" class="btn btn-primary" type="submit" value=" 导 入 "/>
<a href="${ctx}/sys/user/import/template">下载模板</a>
</form>
</div>
<ul class="nav nav-tabs">
<li class="active"><a href="${ctx}/sys/user/list">用户列表</a></li>
<shiro:hasPermission name="sys:user:edit"><li><a href="${ctx}/sys/user/form">用户添加</a></li></shiro:hasPermission>
</ul>
<form:form id="searchForm" modelAttribute="user" action="${ctx}/sys/user/list" method="post" class="breadcrumb form-search ">
<input id="pageNo" name="pageNo" type="hidden" value="${page.pageNo}"/>
<input id="pageSize" name="pageSize" type="hidden" value="${page.pageSize}"/>
<sys:tableSort id="orderBy" name="orderBy" value="${page.orderBy}" callback="page();"/>
<ul class="ul-form">
<li><label>归属公司：</label><sys:treeselect id="company" name="company.id" value="${user.company.id}" labelName="company.name" labelValue="${user.company.name}"
title="公司" url="/sys/office/treeData?type=1" cssClass="input-small" allowClear="true"/></li>
<li><label>登录名：</label><form:input path="loginName" htmlEscape="false" maxlength="50" class="input-medium"/></li>
<li class="clearfix"></li>
<li><label>归属部门：</label><sys:treeselect id="office" name="office.id" value="${user.office.id}" labelName="office.name" labelValue="${user.office.name}"
title="部门" url="/sys/office/treeData?type=2" cssClass="input-small" allowClear="true" notAllowSelectParent="true"/></li>
<li><label>姓 名：</label><form:input path="name" htmlEscape="false" maxlength="50" class="input-medium"/></li>
<li class="btns"><input id="btnSubmit" class="btn btn-primary" type="submit" value="查询" onclick="return page();"/>
<input id="btnExport" class="btn btn-primary" type="button" value="导出"/>
<input id="btnImport" class="btn btn-primary" type="button" value="导入"/></li>
<li class="clearfix"></li>
</ul>
</form:form>
<sys:message content="${message}"/>
<table id="contentTable" class="table table-striped table-bordered table-condensed">
<thead><tr><th>归属公司</th><th>归属部门</th><th class="sort-column login_name">登录名</th><th class="sort-column name">姓名</th><th>电话</th><th>手机</th><%--<th>角色</th> --%><shiro:hasPermission name="sys:user:edit"><th>操作</th></shiro:hasPermission></tr></thead>
<tbody>
<c:forEach items="${page.list}" var="user">
<tr>
<td>${user.company.name}</td>
<td>${user.office.name}</td>
<td><a href="${ctx}/sys/user/form?id=${user.id}">${user.loginName}</a></td>
<td>${user.name}</td>
<td>${user.phone}</td>
<td>${user.mobile}</td><%--
<td>${user.roleNames}</td> --%>
<shiro:hasPermission name="sys:user:edit"><td>
<a href="${ctx}/sys/user/form?id=${user.id}">修改</a>
<a href="${ctx}/sys/user/delete?id=${user.id}" onclick="return confirmx('确认要删除该用户吗？', this.href)">删除</a>
</td></shiro:hasPermission>
</tr>
</c:forEach>
</tbody>
</table>
<div class="pagination">${page}</div>
</body>
</html>2：提交到controller
/**
* Copyright © 2012-2014 <a href="https://github.com/thinkgem/jeesite">JeeSite</a> All rights reserved.
*/
package com.thinkgem.jeesite.modules.sys.web;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.ConstraintViolationException;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.mvc.support.RedirectAttributes;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.thinkgem.jeesite.common.beanvalidator.BeanValidators;
import com.thinkgem.jeesite.common.config.Global;
import com.thinkgem.jeesite.common.persistence.Page;
import com.thinkgem.jeesite.common.utils.DateUtils;
import com.thinkgem.jeesite.common.utils.StringUtils;
import com.thinkgem.jeesite.common.utils.excel.ExportExcel;
import com.thinkgem.jeesite.common.utils.excel.ImportExcel;
import com.thinkgem.jeesite.common.web.BaseController;
import com.thinkgem.jeesite.modules.sys.entity.Office;
import com.thinkgem.jeesite.modules.sys.entity.Role;
import com.thinkgem.jeesite.modules.sys.entity.User;
import com.thinkgem.jeesite.modules.sys.service.SystemService;
import com.thinkgem.jeesite.modules.sys.utils.UserUtils;
/**
* 用户Controller
* @author ThinkGem
* @version 2013-8-29
*/
@Controller
@RequestMapping(value = "${adminPath}/sys/user")
public class UserController extends BaseController {
@Autowired
private SystemService systemService;
@ModelAttribute
public User get(@RequestParam(required=false) String id) {
if (StringUtils.isNotBlank(id)){
return systemService.getUser(id);
}else{
return new User();
}
}
@RequiresPermissions("sys:user:view")
@RequestMapping(value = {"index"})
public String index(User user, Model model) {
return "modules/sys/userIndex";
}
@RequiresPermissions("sys:user:view")
@RequestMapping(value = {"list", ""})
public String list(User user, HttpServletRequest request, HttpServletResponse response, Model model) {
Page<User> page = systemService.findUser(new Page<User>(request, response), user);
model.addAttribute("page", page);
return "modules/sys/userList";
}
@ResponseBody
@RequiresPermissions("sys:user:view")
@RequestMapping(value = {"listData"})
public Page<User> listData(User user, HttpServletRequest request, HttpServletResponse response, Model model) {
Page<User> page = systemService.findUser(new Page<User>(request, response), user);
return page;
}
@RequiresPermissions("sys:user:view")
@RequestMapping(value = "form")
public String form(User user, Model model) {
if (user.getCompany()==null || user.getCompany().getId()==null){
user.setCompany(UserUtils.getUser().getCompany());
}
if (user.getOffice()==null || user.getOffice().getId()==null){
user.setOffice(UserUtils.getUser().getOffice());
}
model.addAttribute("user", user);
model.addAttribute("allRoles", systemService.findAllRole());
return "modules/sys/userForm";
}
@RequiresPermissions("sys:user:edit")
@RequestMapping(value = "save")
public String save(User user, HttpServletRequest request, Model model, RedirectAttributes redirectAttributes) {
if(Global.isDemoMode()){
addMessage(redirectAttributes, "演示模式，不允许操作！");
return "redirect:" + adminPath + "/sys/user/list?repage";
}
// 修正引用赋值问题，不知道为何，Company和Office引用的一个实例地址，修改了一个，另外一个跟着修改。
user.setCompany(new Office(request.getParameter("company.id")));
user.setOffice(new Office(request.getParameter("office.id")));
// 如果新密码为空，则不更换密码
if (StringUtils.isNotBlank(user.getNewPassword())) {
user.setPassword(SystemService.entryptPassword(user.getNewPassword()));
}
if (!beanValidator(model, user)){
return form(user, model);
}
if (!"true".equals(checkLoginName(user.getOldLoginName(), user.getLoginName()))){
addMessage(model, "保存用户'" + user.getLoginName() + "'失败，登录名已存在");
return form(user, model);
}
// 角色数据有效性验证，过滤不在授权内的角色
List<Role> roleList = Lists.newArrayList();
List<String> roleIdList = user.getRoleIdList();
for (Role r : systemService.findAllRole()){
if (roleIdList.contains(r.getId())){
roleList.add(r);
}
}
user.setRoleList(roleList);
// 保存用户信息
systemService.saveUser(user);
// 清除当前用户缓存
if (user.getLoginName().equals(UserUtils.getUser().getLoginName())){
UserUtils.clearCache();
//UserUtils.getCacheMap().clear();
}
addMessage(redirectAttributes, "保存用户'" + user.getLoginName() + "'成功");
return "redirect:" + adminPath + "/sys/user/list?repage";
}
@RequiresPermissions("sys:user:edit")
@RequestMapping(value = "delete")
public String delete(User user, RedirectAttributes redirectAttributes) {
if(Global.isDemoMode()){
addMessage(redirectAttributes, "演示模式，不允许操作！");
return "redirect:" + adminPath + "/sys/user/list?repage";
}
if (UserUtils.getUser().getId().equals(user.getId())){
addMessage(redirectAttributes, "删除用户失败, 不允许删除当前用户");
}else if (User.isAdmin(user.getId())){
addMessage(redirectAttributes, "删除用户失败, 不允许删除超级管理员用户");
}else{
systemService.deleteUser(user);
addMessage(redirectAttributes, "删除用户成功");
}
return "redirect:" + adminPath + "/sys/user/list?repage";
}
/**
* 导出用户数据
* @param user
* @param request
* @param response
* @param redirectAttributes
* @return
*/
@RequiresPermissions("sys:user:view")
@RequestMapping(value = "export", method=RequestMethod.POST)
public String exportFile(User user, HttpServletRequest request, HttpServletResponse response, RedirectAttributes redirectAttributes) {
try {
String fileName = "用户数据"+DateUtils.getDate("yyyyMMddHHmmss")+".xlsx";
Page<User> page = systemService.findUser(new Page<User>(request, response, -1), user);
new ExportExcel("用户数据", User.class).setDataList(page.getList()).write(response, fileName).dispose();
return null;
} catch (Exception e) {
addMessage(redirectAttributes, "导出用户失败！失败信息："+e.getMessage());
}
return "redirect:" + adminPath + "/sys/user/list?repage";
}
/**
* 导入用户数据
* @param file
* @param redirectAttributes
* @return
*/
@RequiresPermissions("sys:user:edit")
@RequestMapping(value = "import", method=RequestMethod.POST)
public String importFile(MultipartFile file, RedirectAttributes redirectAttributes) {
if(Global.isDemoMode()){
addMessage(redirectAttributes, "演示模式，不允许操作！");
return "redirect:" + adminPath + "/sys/user/list?repage";
}
try {
int successNum = 0;
int failureNum = 0;
StringBuilder failureMsg = new StringBuilder();
ImportExcel ei = new ImportExcel(file, 1, 0);
List<User> list = ei.getDataList(User.class);
for (User user : list){
try{
if ("true".equals(checkLoginName("", user.getLoginName()))){
user.setPassword(SystemService.entryptPassword("123456"));
BeanValidators.validateWithException(validator, user);
systemService.saveUser(user);
successNum++;
}else{
failureMsg.append("<br/>登录名 "+user.getLoginName()+" 已存在; ");
failureNum++;
}
}catch(ConstraintViolationException ex){
failureMsg.append("<br/>登录名 "+user.getLoginName()+" 导入失败：");
List<String> messageList = BeanValidators.extractPropertyAndMessageAsList(ex, ": ");
for (String message : messageList){
failureMsg.append(message+"; ");
failureNum++;
}
}catch (Exception ex) {
failureMsg.append("<br/>登录名 "+user.getLoginName()+" 导入失败："+ex.getMessage());
}
}
if (failureNum>0){
failureMsg.insert(0, "，失败 "+failureNum+" 条用户，导入信息如下：");
}
addMessage(redirectAttributes, "已成功导入 "+successNum+" 条用户"+failureMsg);
} catch (Exception e) {
addMessage(redirectAttributes, "导入用户失败！失败信息："+e.getMessage());
}
return "redirect:" + adminPath + "/sys/user/list?repage";
}
/**
* 下载导入用户数据模板
* @param response
* @param redirectAttributes
* @return
*/
@RequiresPermissions("sys:user:view")
@RequestMapping(value = "import/template")
public String importFileTemplate(HttpServletResponse response, RedirectAttributes redirectAttributes) {
try {
String fileName = "用户数据导入模板.xlsx";
List<User> list = Lists.newArrayList(); list.add(UserUtils.getUser());
new ExportExcel("用户数据", User.class, 2).setDataList(list).write(response, fileName).dispose();
return null;
} catch (Exception e) {
addMessage(redirectAttributes, "导入模板下载失败！失败信息："+e.getMessage());
}
return "redirect:" + adminPath + "/sys/user/list?repage";
}
/**
* 验证登录名是否有效
* @param oldLoginName
* @param loginName
* @return
*/
@ResponseBody
@RequiresPermissions("sys:user:edit")
@RequestMapping(value = "checkLoginName")
public String checkLoginName(String oldLoginName, String loginName) {
if (loginName !=null && loginName.equals(oldLoginName)) {
return "true";
} else if (loginName !=null && systemService.getUserByLoginName(loginName) == null) {
return "true";
}
return "false";
}
/**
* 用户信息显示及保存
* @param user
* @param model
* @return
*/
@RequiresPermissions("user")
@RequestMapping(value = "info")
public String info(User user, HttpServletResponse response, Model model) {
User currentUser = UserUtils.getUser();
if (StringUtils.isNotBlank(user.getName())){
if(Global.isDemoMode()){
model.addAttribute("message", "演示模式，不允许操作！");
return "modules/sys/userInfo";
}
currentUser.setEmail(user.getEmail());
currentUser.setPhone(user.getPhone());
currentUser.setMobile(user.getMobile());
currentUser.setRemarks(user.getRemarks());
currentUser.setPhoto(user.getPhoto());
systemService.updateUserInfo(currentUser);
model.addAttribute("message", "保存用户信息成功");
}
model.addAttribute("user", currentUser);
model.addAttribute("Global", new Global());
return "modules/sys/userInfo";
}
/**
* 返回用户信息
* @return
*/
@RequiresPermissions("user")
@ResponseBody
@RequestMapping(value = "infoData")
public User infoData() {
return UserUtils.getUser();
}
/**
* 修改个人用户密码
* @param oldPassword
* @param newPassword
* @param model
* @return
*/
@RequiresPermissions("user")
@RequestMapping(value = "modifyPwd")
public String modifyPwd(String oldPassword, String newPassword, Model model) {
User user = UserUtils.getUser();
if (StringUtils.isNotBlank(oldPassword) && StringUtils.isNotBlank(newPassword)){
if(Global.isDemoMode()){
model.addAttribute("message", "演示模式，不允许操作！");
return "modules/sys/userModifyPwd";
}
if (SystemService.validatePassword(oldPassword, user.getPassword())){
systemService.updatePasswordById(user.getId(), user.getLoginName(), newPassword);
model.addAttribute("message", "修改密码成功");
}else{
model.addAttribute("message", "修改密码失败，旧密码错误");
}
}
model.addAttribute("user", user);
return "modules/sys/userModifyPwd";
}
@RequiresPermissions("user")
@ResponseBody
@RequestMapping(value = "treeData")
public List<Map<String, Object>> treeData(@RequestParam(required=false) String officeId, HttpServletResponse response) {
List<Map<String, Object>> mapList = Lists.newArrayList();
List<User> list = systemService.findUserByOfficeId(officeId);
for (int i=0; i<list.size(); i++){
User e = list.get(i);
Map<String, Object> map = Maps.newHashMap();
map.put("id", "u_"+e.getId());
map.put("pId", officeId);
map.put("name", StringUtils.replace(e.getName(), " ", ""));
mapList.add(map);
}
return mapList;
}
// @InitBinder
// public void initBinder(WebDataBinder b) {
// b.registerCustomEditor(List.class, "roleList", new PropertyEditorSupport(){
// @Autowired
// private SystemService systemService;
// @Override
// public void setAsText(String text) throws IllegalArgumentException {
// String[] ids = StringUtils.split(text, ",");
// List<Role> roles = new ArrayList<Role>();
// for (String id : ids) {
// Role role = systemService.getRole(Long.valueOf(id));
// roles.add(role);
// }
// setValue(roles);
// }
// @Override
// public String getAsText() {
// return Collections3.extractToString((List) getValue(), "id", ",");
// }
// });
// }
}