使用EasyExcel导入用户数据
导入依赖包
```java
compile 'com.alibaba:easyexcel:2.2.6'
```
代码（依赖于springboot）
提供数据导入接口
```java
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Date;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.io.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.io.FileSystemResource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
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.RestController;
import org.springframework.web.multipart.MultipartFile;
import com.alibaba.excel.EasyExcel;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
@Api(value="用户导入相关接口",tags= {"UserImport API"})
@RestController
@RequestMapping("/api/uc/import")
public class UcImportRs {
public static Logger logger = LoggerFactory.getLogger(UcImportRs.class);
String path;
@Autowired(required=false)
UcService _ucService;
/**
* 文件上传类
* 文件会自动绑定到MultipartFile中
* @param request 获取请求信息
* @param description 文件描述
* @param file 上传的文件
* @return 上传成功或失败结果
* @throws IOException
* @throws IllegalStateException
*/
@ApiOperation(value="excel文件上传")
@RequestMapping(value = "/uploadExcel",method = {RequestMethod.POST})
public String upload(HttpServletRequest request, @RequestParam("file") MultipartFile file) throws IllegalStateException, IOException {
// 测试MultipartFile接口的各个方法
System.out.println("文件类型ContentType=" + file.getContentType());
System.out.println("文件组件名称Name=" + file.getName());
System.out.println("文件原名称OriginalFileName=" + file.getOriginalFilename());
System.out.println("文件大小Size=" + file.getSize()/1024 + "KB");
// 如果文件不为空，写入上传路径，进行文件上传
if (!file.isEmpty()) {
//String restult = importUc(file.getInputStream());
// 构建上传文件的存放路径
System.out.println("path = " + path);
// 获取上传的文件名称，并结合存放路径，构建新的文件名称
String filename = file.getOriginalFilename();
File filepath = new File(path, filename);
// 判断路径是否存在，不存在则新创建一个
if (!filepath.getParentFile().exists()) {
filepath.getParentFile().mkdirs();
}
// 将上传文件保存到目标文件目录
file.transferTo(new File(path + File.separator + filename));
logger.debug("文件上传成功：路径：{},开始导入用户",filepath.getAbsolutePath());
String result = importUc(filepath);
return result;
} else {
return "error";
}
}
//导入用户
public String importUc(InputStream in) {
UserDataListener listener = new UserDataListener();
EasyExcel.read(in, UserData.class,listener).sheet().doRead();
List<UserData> datas = listener.getDatas();
return _ucService.addUc(datas);
}
//导入用户
public String importUc(File filepath) {
UserDataListener listener = new UserDataListener();
EasyExcel.read(filepath.getAbsolutePath(), UserData.class,listener).sheet().doRead();
List<UserData> datas = listener.getDatas();
return _ucService.addUc(datas);
}
@ApiOperation(value="获取用户信息导入模板")
@RequestMapping(value = "/getExcelTemplate",method = {RequestMethod.GET})
public void download( HttpServletRequest request, HttpServletResponse response) throws Exception {
try (
InputStream inputStream = new FileInputStream(new File("./config/用户信息导入表.xlsx"));
OutputStream outputStream = response.getOutputStream();
)
{
String fileName = java.net.URLEncoder.encode("用户信息导入表.xlsx", "UTF8");
response.setCharacterEncoding("UTF-8");
response.setContentType("application/x-download;charset=utf8");
response.addHeader("Content-Disposition", "attachment;filename="+fileName);
IOUtils.copy(inputStream, outputStream);
outputStream.flush();
}
}
public ResponseEntity<FileSystemResource> getExcelTemplate() throws Throwable {
File file = new File("./config/用户信息导入表.xlsx");
return export(file);
}
public ResponseEntity<FileSystemResource> export(File file) {
if (file == null) {
return null;
}
HttpHeaders headers = new HttpHeaders();
headers.add("Cache-Control", "no-cache, no-store, must-revalidate");
headers.add("Content-Disposition", "attachment; filename=用户信息导入模板.xlsx");
headers.add("Pragma", "no-cache");
headers.add("Expires", "0");
headers.add("Last-Modified", new Date().toString());
headers.add("ETag", String.valueOf(System.currentTimeMillis()));
return ResponseEntity
.ok()
.headers(headers)
.contentLength(file.length())
.contentType(MediaType.parseMediaType("application/octet-stream"))
.body(new FileSystemResource(file));
}
}
```
监听类：
```css
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.alibaba.dubbo.common.json.JSON;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.CellExtra;
public class UserDataListener extends AnalysisEventListener<UserData> {
public static Logger LOGGER = LoggerFactory.getLogger(UserDataListener.class);
private List<UserData> datas = new ArrayList<>();
public List<UserData> getDatas() {
return datas;
}
public void setDatas(List<UserData> datas) {
this.datas = datas;
}
@Override
public void invoke(UserData data, AnalysisContext context) {
//当前行
LOGGER.debug("扫描到用户：{}",data.getUserName());
if (data != null) {
datas.add(data);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
System.out.println("解析结束");
}
/**
* 这里会一行行的返回头
*
* @param headMap
* @param context
*/
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
/*try {
System.out.println("解析到一条头数据:{}" + JSON.json(headMap));
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}*/
}
@Override
public void extra(CellExtra extra, AnalysisContext context) {
try {
System.out.println("读取到了一条额外信息:{}"+ JSON.json(extra));
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
switch (extra.getType()) {
case COMMENT:
LOGGER.info("额外信息是批注,在rowIndex:{},columnIndex;{},内容是:{}", extra.getRowIndex(), extra.getColumnIndex(),
extra.getText());
break;
case HYPERLINK:
if ("Sheet1!A1".equals(extra.getText())) {
LOGGER.info("额外信息是超链接,在rowIndex:{},columnIndex;{},内容是:{}", extra.getRowIndex(), extra.getColumnIndex(),
extra.getText());
} else if ("Sheet2!A1".equals(extra.getText())) {
LOGGER.info(
"额外信息是超链接,而且覆盖了一个区间,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{},lastColumnIndex:{},"
+ "内容是:{}",
extra.getFirstRowIndex(), extra.getFirstColumnIndex(), extra.getLastRowIndex(),
extra.getLastColumnIndex(), extra.getText());
} else {
}
break;
case MERGE:
LOGGER.info("额外信息是合并单元格,而且覆盖了一个区间,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{},lastColumnIndex:{}",
extra.getFirstRowIndex(), extra.getFirstColumnIndex(), extra.getLastRowIndex(),
extra.getLastColumnIndex());
break;
default:
}
}
}
```
模型：
```java
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
public class UserData {
@ExcelProperty("一级部门")
private String depatment1Name;
@ExcelProperty("二级部门")
private String depatment2Name;
@ExcelProperty("用户名")
private String userName;
@ExcelProperty("用户ID")
private String userId;
@ExcelProperty("电话")
private String telphone;
}
```
userService:
```java
@Component
public class UcService {
public String addUc(List<UserData> datas) {
//do something
}
}
```
以上！
