导出
//表格导出接口
@GetMapping("/export")
public void export(HttpServletResponse response) throws IOException {
//查询所有用户
List<User> list= userService.list();
//在内存操作,写到浏览器
ExcelWriter writer= ExcelUtil.getWriter(true);
//自定义标题别名
writer.addHeaderAlias("username","用户名");
writer.addHeaderAlias("password","密码");
writer.addHeaderAlias("nickname","昵称");
writer.addHeaderAlias("email","邮箱");
writer.addHeaderAlias("phone","电话");
writer.addHeaderAlias("address","地址");
writer.addHeaderAlias("createTime","创建时间");
//默认配置
writer.write(list,true);
//设置content—type
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset:utf-8");
//设置标题
String fileName= URLEncoder.encode("用户信息","UTF-8");
//Content-disposition是MIME协议的扩展,MIME协议指示MIME用户代理如何显示附加的文件。
response.setHeader("Content-Disposition","attachment;filename="+fileName+".xlsx");
ServletOutputStream outputStream= response.getOutputStream();
//将Writer刷新到OutPut
writer.flush(outputStream,true);
outputStream.close();
writer.close();
}
导入
/**
* 导入excel
* @param file
*/
@PostMapping("/import")
public void importExcel(MultipartFile file) throws IOException {
//1.第一种 头必须和实体(英文)一样
//文件处理成io流
InputStream in = file.getInputStream();
// //io流给ExcelReader
ExcelReader excelReader=ExcelUtil.getReader(in);
// //读取数据且转化为list
// List<User> list = excelReader.readAll(User.class);
//2.第二种导入方式
//忽略第一行头(第一行是中文的情况),直接读取表的内容
List<List<Object>> list = excelReader.read(1);
List<User> listUser = CollUtil.newArrayList();
for (List<Object> row: list) {
User user=new User();
user.setUsername(row.get(0).toString());
user.setPassword(row.get(1).toString());
user.setNickname(row.get(2).toString());
user.setNickname(row.get(3).toString());
user.setPhone(row.get(4).toString());
user.setAddress(row.get(5).toString());
listUser.add(user);
// ****类似一一对应****
}
//批量注册进数据库
userService.saveBatch(listUser);
//3.第三种导入方式
InputStream stream = file.getInputStream();
List<Object> excelTitleList =Lists.newArrayList();
List<BankInfo> list = CollUtil.newLinkedList();
ExcelUtil.readBySax(stream, 0, (sheetIndex, rowIndex, rowList) -> {
if (rowIndex == 0) {
excelTitleList.addAll(rowList);
}
list.add(BeanUtil.toBean(IterUtil.toMap(ExcelConstants.BANK_IMPORT.keySet(), rowList), BankInfo.class));
});
//获取表头,校验模板非法
if (CollectionUtil.isEmpty(excelTitleList)) {
return R.failed("Upload file template illegal", CommonConstant._1);
}
List<String> titleList = Lists.newArrayList(ExcelConstants.BANK_IMPORT.values());
if (titleList.size() != excelTitleList.size()) {
return R.failed("Upload file template illegal", CommonConstant._1);
}
for (int i = 0; i < titleList.size(); i++) {
if (!titleList.get(i).equals(excelTitleList.get(i).toString().trim())) {
return R.failed("Upload file template illegal", CommonConstant._1);
}
}
}