1.加入依赖包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17-beta1</version>
</dependency>
新建Dto类 用于导入 导出
@Data
@Accessors(chain = true)
public class UserExport{
@Excel(name = "姓名",width = 40)
private String name;
@Excel(name = "年龄",width = 25)
private String age;
}
导出
//response 要传入的参数
public void getUpload(HttpServletResponse response,
)throws IOException {
//执行查询的SQL查询出来结果 exports 为封装的实体对象 如 User
//在该对象字段上注释 @Excel(name = "导出文档的列名",width = 15)
List<UserExport> exports = UserMapper.selectUser(param);
ExportParams params = new ExportParams("用户数据" + createDate, "用户数据下载", ExcelType.XSSF);
Workbook sheets = ExcelExportUtil.exportExcel(params, UserExport.class, exports);
String fileName = "用户数据.xlsx";
//取得输出流
OutputStream out = response.getOutputStream();
//清空输出流
response.reset();
response.setCharacterEncoding("UTF-8");
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition",
"attachment;fileName=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));
//写入文件
sheets.write(out);
sheets.close();
//关闭流
out.close();
}
本地文件导入
public void testImport() throws Exception{
ImportParams params = new ImportParams();
//表格标题行数,默认0
params.setTitleRows(0);
//表头行数,默认1
params.setHeadRows(1);
params.setNeedVerfiy(true);
//文件本地所在位置 文件对应 第一行应对应实体的字段名 如 第一列姓名 上面应为 姓名
File file = new File("E:\\root\\xxx.xlsx");
FileInputStream fileInputStream = new FileInputStream(file);
List<UserExport> list = ExcelImportUtil.importExcel(fileInputStream, UserExport.class, params);
for (UserExport export : list) {
UserExport Userexport = userExportMapper.selectOne(new QueryWrapper<UserExport>().lambda()
.eq(UserExport ::getDeleteFlag,0).eq(UserExport ::getId,export.getId));
if(Userexport !=null){
continue;
}
UserExport user= new UserExport ();
user.setId(UUIDUtils.getId());
user.setDeleteFlag(0);
user.setCreatedTime(new Date());
user.setUpdatedTime(new Date());
BeanUtils.copyProperties(export,user);
userExportMapper.insert(user);
}
}
上传文件导入
public Map importExpress(@RequestParam("file") MultipartFile file) throws Exception {
Map<String, Object> rsp = new HashMap<>();
if (file == null || file.isEmpty()) {
log.info("导入失败,缺少file文件");
rsp.put("retCode", -1);
rsp.put("retMessage", "导入失败,缺少file文件");
return rsp;
}
ImportParams param = new ImportParams();
//表格标题行数,默认0
param.setTitleRows(0);
//表头行数,默认1
param.setHeadRows(1);
AtomicInteger failNum = new AtomicInteger(0);
AtomicInteger successNum = new AtomicInteger(0);
InputStream inputStream = file.getInputStream();
List<userExport> list = new ArrayList<>();
try {
list = ExcelImportUtil.importExcel(inputStream, userExport.class, param);
}catch (Exception e){
e.printStackTrace();
} finally {
if (inputStream != null){
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
log.info("导入{}条数据", list.size());
for (userExport userexport : list) {
UserExport Userexport = userExportMapper.selectOne(new QueryWrapper<UserExport>().lambda()
.eq(UserExport ::getDeleteFlag,0).eq(UserExport ::getId,export.getId));
if(Userexport !=null){
failNum.incrementAndGet();
log.info("导入用户信息,用户:{} 已存在",Userexport.getId());
continue;
}
UserExport user= new UserExport ();
user.setId(UUIDUtils.getId());
user.setDeleteFlag(0);
user.setCreatedTime(new Date());
user.setUpdatedTime(new Date());
BeanUtils.copyProperties(export,user);
userExportMapper.insert(user);
successNum.incrementAndGet();
}
rsp.put("retCode", "0");
rsp.put("successNum", successNum);
rsp.put("failNum", failNum);
return rsp;
}