EasyExcel实现导入excel

客官°小女子只卖身不卖艺 2022-01-15 22:23 433阅读 0赞

1.pom.xml配置依赖包

  1. <!-- xls格式excel依赖包 -->
  2. <dependency>
  3.     <groupId>org.apache.poi</groupId>
  4.     <artifactId>poi</artifactId>
  5.     <version>3.17</version>
  6. </dependency>
  7. <!--xlsx格式excel依赖包-->
  8. <dependency>
  9.     <groupId>org.apache.poi</groupId>
  10.     <artifactId>poi-ooxml</artifactId>
  11.     <version>3.17</version>
  12. </dependency>
  13. <!-- easyexcel依赖包 -->
  14. <dependency>
  15.     <groupId>com.alibaba</groupId>
  16.     <artifactId>easyexcel</artifactId>
  17.     <version>1.1.2-beta5</version>
  18. </dependency>

2.编写excel导入对应实体类

  1. import com.alibaba.excel.annotation.ExcelProperty;
  2. import com.alibaba.excel.metadata.BaseRowModel;
  4. public class TestModel extends BaseRowModel {
  6.     @ExcelProperty(value = "姓名", index = 0)
  7.     private String xm;
  8.     @ExcelProperty(value = "微信号", index = 1)
  9.     private String wxh;
  10.     @ExcelProperty(value = "手机号", index = 2)
  11.     private String sjh;
  13.     public String getXm() {
  14.         return xm;
  15.     }
  16.     public void setXm(String xm) {
  17.         this.xm = xm;
  18.     }
  19.     public String getWxh() {
  20.         return wxh;
  21.     }
  22.     public void setWxh(String wxh) {
  23.         this.wxh = wxh;
  24.     }
  25.     public String getSjh() {
  26.         return sjh;
  27.     }
  28.     public void setSjh(String sjh) {
  29.         this.sjh = sjh;
  30.     }
  32. }

3.编写导入监听类

  1. import java.lang.reflect.Field;
  2. import java.util.ArrayList;
  3. import java.util.LinkedHashMap;
  4. import java.util.List;
  5. import java.util.Map;
  7. import com.alibaba.excel.context.AnalysisContext;
  8. import com.alibaba.excel.event.AnalysisEventListener;
  9. import com.alibaba.excel.metadata.ExcelHeadProperty;
  11. public class ExcelListener extends AnalysisEventListener {
  13.     //自定义用于暂时存储data
  14.     private List<Object> datas = new ArrayList<>();
  15.     //导入表头
  16.     private String importHeads = "";
  17.     //模版表头
  18.     private String modelHeads = "";
  20.     /**
  21.      * 通过 AnalysisContext 对象还可以获取当前 sheet，当前行等数据
  22.      */
  23.     @Override
  24.     public void invoke(Object o, AnalysisContext analysisContext) {
  25.         Integer currentRowNum = analysisContext.getCurrentRowNum();
  26.         //获取导入表头，默认第一行为表头
  27.         if(currentRowNum == 0){
  28.             try {
  29.                 Map<String,Object> m = objToMap(o);
  30.                 for (Object v : m.values()) {
  31.                     importHeads += String.valueOf(v).trim() + ",";
  32.                  }
  33.             } catch (Exception e) {
  34.                 e.printStackTrace();
  35.             }
  36.         }else{
  37.             datas.add(o);
  38.         }
  39.     }
  41.     /**
  42.      * 读取完之后的操作
  43.      */
  44.     @Override
  45.     public void doAfterAllAnalysed(AnalysisContext analysisContext) {
  46.         //获取模版表头
  47.         ExcelHeadProperty ehp = analysisContext.getExcelHeadProperty();
  48.         for(List<String> s : ehp.getHead()){
  49.             modelHeads += s.get(0) + ",";
  50.         }
  51.     }
  53.     public List<Object> getDatas() {
  54.         return datas;
  55.     }
  57.     public void setDatas(List<Object> datas) {
  58.         this.datas = datas;
  59.     }
  61.     public String getImportHeads() {
  62.         return importHeads;
  63.     }
  65.     public void setImportHeads(String importHeads) {
  66.         this.importHeads = importHeads;
  67.     }
  69.     public String getModelHeads() {
  70.         return modelHeads;
  71.     }
  73.     public void setModelHeads(String modelHeads) {
  74.         this.modelHeads = modelHeads;
  75.     }
  77.     //Object转换为Map
  78.     public  Map<String,Object> objToMap(Object obj) throws Exception{
  79.         Map<String,Object> map = new LinkedHashMap<String, Object>();
  80.         Field[] fields = obj.getClass().getDeclaredFields();
  81.         for(Field field : fields){
  82.             field.setAccessible(true);
  83.             map.put(field.getName(), field.get(obj));
  84.         }
  85.         return map;
  86.     }
  87. }

4.编写导入工具类

  1. import java.io.BufferedInputStream;
  2. import java.io.IOException;
  3. import java.io.InputStream;
  4. import java.util.List;
  6. import org.springframework.web.multipart.MultipartFile;
  8. import com.alibaba.excel.ExcelReader;
  9. import com.alibaba.excel.metadata.BaseRowModel;
  10. import com.alibaba.excel.metadata.Sheet;
  12. public class EasyExcelUtil {
  14.     /**
  15.      * 读取某个 sheet 的 Excel
  16.      *
  17.      * @param excel    文件
  18.      * @param rowModel 实体类映射，继承 BaseRowModel 类
  19.      * @param sheetNo  sheet 的序号 从1开始
  20.      * @return Excel 数据 list
  21.      */
  22.     public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel) throws IOException {
  23.         return readExcel(excel, rowModel, 1, 1);
  24.     }
  26.     /**
  27.      * 读取某个 sheet 的 Excel
  28.      * @param excel       文件
  29.      * @param rowModel    实体类映射，继承 BaseRowModel 类
  30.      * @param sheetNo     sheet 的序号 从1开始
  31.      * @return Excel 数据 list
  32.      */
  33.     public static Map<String,Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo) throws IOException {
  34.         Map<String,Object> result = new HashMap<>();
  35.         ExcelListener excelListener = new ExcelListener();
  36.         ExcelReader reader = getReader(excel, excelListener);
  37.         if (reader == null) {
  38.             return null;
  39.         }
  40.         reader.read(new Sheet(sheetNo, 0, rowModel.getClass()));    
  41.         //校验表头
  42.         Boolean flag = false;
  43.         if(excelListener.getImportHeads().equals(excelListener.getModelHeads())){
  44.             flag = true;
  45.         }
  46.         result.put("flag", flag);
  47.         result.put("datas", excelListener.getDatas());
  48.         return result;
  49.     }
  51.     /**
  52.      * 读取某个 sheet 的 Excel
  53.      * @param excel       文件
  54.      * @param rowModel    实体类映射，继承 BaseRowModel 类
  55.      * @param sheetNo     sheet 的序号 从1开始
  56.      * @param headLineNum 表头行数，默认为1
  57.      * @return Excel 数据 list
  58.      */
  59.     public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo, int headLineNum) throws IOException {
  60.         ExcelListener excelListener = new ExcelListener();
  61.         ExcelReader reader = getReader(excel, excelListener);
  62.         if (reader == null) {
  63.             return null;
  64.         }
  65.         reader.read(new Sheet(sheetNo, headLineNum, rowModel.getClass()));
  66.         return excelListener.getDatas();
  67.     }
  69.     /**
  70.      * 读取指定sheetName的Excel(多个 sheet)
  71.      * @param excel    文件
  72.      * @param rowModel 实体类映射，继承 BaseRowModel 类
  73.      * @return Excel 数据 list
  74.      * @throws IOException 
  75.      */
  76.     public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel,String sheetName) throws IOException {
  77.         ExcelListener excelListener = new ExcelListener();
  78.         ExcelReader reader = getReader(excel, excelListener);
  79.         if (reader == null) {
  80.             return null;
  81.         }
  82.         for (Sheet sheet : reader.getSheets()) {
  83.             if (rowModel != null) {
  84.                 sheet.setClazz(rowModel.getClass());
  85.             }
  86.             //读取指定名称的sheet
  87.             if(sheet.getSheetName().contains(sheetName)){
  88.                 reader.read(sheet);
  89.                 break;
  90.             }
  91.         }
  92.         return excelListener.getDatas();
  93.     }
  95.     /**
  96.      * 返回 ExcelReader
  97.      * @param excel 需要解析的 Excel 文件
  98.      * @param excelListener new ExcelListener()
  99.      * @throws IOException 
  100.      */
  101.     private static ExcelReader getReader(MultipartFile excel,ExcelListener excelListener) throws IOException {
  102.         String filename = excel.getOriginalFilename();
  103.         if(filename != null && (filename.toLowerCase().endsWith(".xls") || filename.toLowerCase().endsWith(".xlsx"))){
  104.             InputStream is = new BufferedInputStream(excel.getInputStream());
  105.             return new ExcelReader(is, null, excelListener, false);
  106.         }else{
  107.             return null;
  108.         }
  109.     }
  111. }

5.业务层调用

  1. //导入excel
  2. @RequestMapping(value = "excelImport", method = {RequestMethod.GET, RequestMethod.POST })
  3. public String excelImport(HttpServletRequest request,Model model,@RequestParam("uploadFile") MultipartFile[] files) throws Exception {        
  4.     if(files != null && files.length > 0){
  5.         MultipartFile file = files[0];
  6.         Map<String,Object> result = EasyExcelUtil.readExcel(file, new TestModel(),1);
  7.         Boolean flag = (Boolean) result.get("flag");
  8.         if(flag){
  9.             List<Object> list = (List<Object>) result.get("datas");    
  10.             if(list != null && list.size() > 0){
  11.                 for(Object o : list){
  12.                     TestModel xfxx = (TestModel) o;
  13.                     System.out.println(xfxx.getXm()+"/"+xfxx.getSjh()+"/"+xfxx.getSjh());
  14.                 }
  15.             }
  16.         }else{
  17.             System.out.println("表头格式错误");
  18.         }
  19.     }    
  20.     return "index";
  21. }

发表评论

表情：
评论列表 （有 0 条评论，433人围观）

还没有评论，来说两句吧...

相关阅读