导入依赖包 ```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 }
} ``` 以上！
