1. 实体类字段上加 @Excel(name = "xxx"), 表示要导入的字段
@Excel(name = "用户名称")
private String nickName;
2. controller (post请求)
/**
* 导入用户数据
*
* @param file 文件
* @param updateSupport 是否更新支持,如果已存在,则进行更新数据
* @return 结果
*/
@ApiOperation("导入用户数据")
@PostMapping("/importData")
public AjaxResult importData(MultipartFile file, boolean updateSupport) throws Exception
{
ExcelUtil<SysUser> util = new ExcelUtil<SysUser>(SysUser.class);
List<SysUser> userList = util.importExcel(file.getInputStream());
String operName = getUsername();
String message = userService.importUser(userList, updateSupport, operName);
return AjaxResult.success(message);
}
3. service
/**
* 导入用户数据
*
* @param userList 用户数据列表
* @param isUpdateSupport 是否更新支持,如果已存在,则进行更新数据
* @param operName 操作用户
* @return 结果
*/
@Override
public String importUser(List<SysUser> userList, Boolean isUpdateSupport, String operName)
{
if (StringUtils.isNull(userList) || userList.size() == 0)
{
throw new ServiceException("导入用户数据不能为空!");
}
int successNum = 0;
int failureNum = 0;
StringBuilder successMsg = new StringBuilder();
StringBuilder failureMsg = new StringBuilder();
String password = configService.selectConfigByKey("sys.user.initPassword");
for (SysUser user : userList)
{
try
{
// 验证是否存在这个用户
SysUser u = userMapper.selectUserByUserName(user.getUserName());
if (StringUtils.isNull(u))
{
BeanValidators.validateWithException(validator, user);
user.setPassword(SecurityUtils.encryptPassword(password));
user.setCreateBy(operName);
this.insertUser(user);
successNum++;
successMsg.append("<br/>" + successNum + "、账号 " + user.getUserName() + " 导入成功");
}
else if (isUpdateSupport)
{
BeanValidators.validateWithException(validator, user);
user.setUpdateBy(operName);
this.updateUser(user);
successNum++;
successMsg.append("<br/>" + successNum + "、账号 " + user.getUserName() + " 更新成功");
}
else
{
failureNum++;
failureMsg.append("<br/>" + failureNum + "、账号 " + user.getUserName() + " 已存在");
}
}
catch (Exception e)
{
failureNum++;
String msg = "<br/>" + failureNum + "、账号 " + user.getUserName() + " 导入失败:";
failureMsg.append(msg + e.getMessage());
log.error(msg, e);
}
}
if (failureNum > 0)
{
failureMsg.insert(0, "很抱歉,导入失败!共 " + failureNum + " 条数据格式不正确,错误如下:");
throw new ServiceException(failureMsg.toString());
}
else
{
successMsg.insert(0, "恭喜您,数据已全部导入成功!共 " + successNum + " 条,数据如下:");
}
return successMsg.toString();
}
注意事项 (正确例子):
@Excel注解设置的name名称要和表头名称一致, 不能多也不能少。否则会导入失败
@Data
public class ZqBookInfoVo extends BaseEntity {
private static final long serialVersionUID = 1L;
@Excel(name = "*书籍ID")
private Long bookId;
@Excel(name = "*书名")
private String name;
@Excel(name = "*作者名")
private String author;
@Excel(name = "*书籍类型")
private String bookType;
@Excel(name = "*是否内刊")
private String isMagazine;
@Excel(name = "*cp名称")
private String cpName;
@Excel(name = "播音员(书籍类型为有声时必填)")
private String announcer;
@Excel(name = "*一级分类")
private String primaryClassification;
@Excel(name = "*二级分类")
private String twoLevelClassification;
@Excel(name = "内容标签")
private String contentTags;
@Excel(name = "行业标签")
private String industryTags;
@Excel(name = "销售标签")
private String saleTags;
@Excel(name = "编辑推荐")
private String recommend;
@Excel(name = "*操作码(C--新增(默认), U--修改,D--删除,E--上架,L--下架)")
private String code;
@Excel(name = "到期时间", width = 30, dateFormat = "yyyy-MM-dd")
private Date expirationTime;
}