EasyExcel实现导入excel

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

**1.pom.xml配置依赖包**

<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.17</version>
    </dependency>
    
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.17</version>
    </dependency>
    
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>1.1.2-beta5</version>
    </dependency>

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

import com.alibaba.excel.annotation.ExcelProperty;
    import com.alibaba.excel.metadata.BaseRowModel;
    
    public class TestModel extends BaseRowModel {
    
    	@ExcelProperty(value = "姓名", index = 0)
    	private String xm;
    	@ExcelProperty(value = "微信号", index = 1)
    	private String wxh;
    	@ExcelProperty(value = "手机号", index = 2)
    	private String sjh;
    	
    	public String getXm() {
    		return xm;
    	}
    	public void setXm(String xm) {
    		this.xm = xm;
    	}
    	public String getWxh() {
    		return wxh;
    	}
    	public void setWxh(String wxh) {
    		this.wxh = wxh;
    	}
    	public String getSjh() {
    		return sjh;
    	}
    	public void setSjh(String sjh) {
    		this.sjh = sjh;
    	}
    	
    }

**3.编写导入监听类**

import java.lang.reflect.Field;
    import java.util.ArrayList;
    import java.util.LinkedHashMap;
    import java.util.List;
    import java.util.Map;
    
    import com.alibaba.excel.context.AnalysisContext;
    import com.alibaba.excel.event.AnalysisEventListener;
    import com.alibaba.excel.metadata.ExcelHeadProperty;
    
    public class ExcelListener extends AnalysisEventListener {
    
        //自定义用于暂时存储data
        private List<Object> datas = new ArrayList<>();
        //导入表头
        private String importHeads = "";
        //模版表头
        private String modelHeads = "";
        
        /**
         * 通过 AnalysisContext 对象还可以获取当前 sheet，当前行等数据
         */
        @Override
        public void invoke(Object o, AnalysisContext analysisContext) {
        	Integer currentRowNum = analysisContext.getCurrentRowNum();
        	//获取导入表头，默认第一行为表头
        	if(currentRowNum == 0){
            	try {
            		Map<String,Object> m = objToMap(o);
            		for (Object v : m.values()) {
            			importHeads += String.valueOf(v).trim() + ",";
         			}
        		} catch (Exception e) {
        			e.printStackTrace();
        		}
        	}else{
        		datas.add(o);
        	}
        }
    
        /**
         * 读取完之后的操作
         */
        @Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        	//获取模版表头
        	ExcelHeadProperty ehp = analysisContext.getExcelHeadProperty();
        	for(List<String> s : ehp.getHead()){
        		modelHeads += s.get(0) + ",";
        	}
        }
    
        public List<Object> getDatas() {
            return datas;
        }
    
        public void setDatas(List<Object> datas) {
            this.datas = datas;
        }
    
        public String getImportHeads() {
    	    return importHeads;
        }
    
        public void setImportHeads(String importHeads) {
    	    this.importHeads = importHeads;
        }
    
        public String getModelHeads() {
    	    return modelHeads;
        }
    
        public void setModelHeads(String modelHeads) {
    	    this.modelHeads = modelHeads;
        }
    
        //Object转换为Map
        public  Map<String,Object> objToMap(Object obj) throws Exception{
            Map<String,Object> map = new LinkedHashMap<String, Object>();
            Field[] fields = obj.getClass().getDeclaredFields();
            for(Field field : fields){
                field.setAccessible(true);
                map.put(field.getName(), field.get(obj));
            }
            return map;
        }
    }

**4.编写导入工具类**

import java.io.BufferedInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.List;
    
    import org.springframework.web.multipart.MultipartFile;
    
    import com.alibaba.excel.ExcelReader;
    import com.alibaba.excel.metadata.BaseRowModel;
    import com.alibaba.excel.metadata.Sheet;
    
    public class EasyExcelUtil {
    	
        /**
         * 读取某个 sheet 的 Excel
         *
         * @param excel    文件
         * @param rowModel 实体类映射，继承 BaseRowModel 类
         * @param sheetNo  sheet 的序号 从1开始
         * @return Excel 数据 list
         */
        public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel) throws IOException {
            return readExcel(excel, rowModel, 1, 1);
        }
    
        /**
         * 读取某个 sheet 的 Excel
         * @param excel       文件
         * @param rowModel    实体类映射，继承 BaseRowModel 类
         * @param sheetNo     sheet 的序号 从1开始
         * @return Excel 数据 list
         */
        public static Map<String,Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo) throws IOException {
        	Map<String,Object> result = new HashMap<>();
        	ExcelListener excelListener = new ExcelListener();
            ExcelReader reader = getReader(excel, excelListener);
            if (reader == null) {
                return null;
            }
            reader.read(new Sheet(sheetNo, 0, rowModel.getClass()));    
            //校验表头
            Boolean flag = false;
            if(excelListener.getImportHeads().equals(excelListener.getModelHeads())){
            	flag = true;
            }
            result.put("flag", flag);
            result.put("datas", excelListener.getDatas());
            return result;
        }
    
        /**
         * 读取某个 sheet 的 Excel
         * @param excel       文件
         * @param rowModel    实体类映射，继承 BaseRowModel 类
         * @param sheetNo     sheet 的序号 从1开始
         * @param headLineNum 表头行数，默认为1
         * @return Excel 数据 list
         */
        public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo, int headLineNum) throws IOException {
            ExcelListener excelListener = new ExcelListener();
            ExcelReader reader = getReader(excel, excelListener);
            if (reader == null) {
                return null;
            }
            reader.read(new Sheet(sheetNo, headLineNum, rowModel.getClass()));
            return excelListener.getDatas();
        }
        
        /**
         * 读取指定sheetName的Excel(多个 sheet)
         * @param excel    文件
         * @param rowModel 实体类映射，继承 BaseRowModel 类
         * @return Excel 数据 list
         * @throws IOException 
         */
        public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel,String sheetName) throws IOException {
            ExcelListener excelListener = new ExcelListener();
            ExcelReader reader = getReader(excel, excelListener);
            if (reader == null) {
                return null;
            }
            for (Sheet sheet : reader.getSheets()) {
                if (rowModel != null) {
                    sheet.setClazz(rowModel.getClass());
                }
                //读取指定名称的sheet
                if(sheet.getSheetName().contains(sheetName)){
                	reader.read(sheet);
                	break;
                }
            }
            return excelListener.getDatas();
        }
    
        /**
         * 返回 ExcelReader
         * @param excel 需要解析的 Excel 文件
         * @param excelListener new ExcelListener()
         * @throws IOException 
         */
        private static ExcelReader getReader(MultipartFile excel,ExcelListener excelListener) throws IOException {
            String filename = excel.getOriginalFilename();
            if(filename != null && (filename.toLowerCase().endsWith(".xls") || filename.toLowerCase().endsWith(".xlsx"))){
                InputStream is = new BufferedInputStream(excel.getInputStream());
                return new ExcelReader(is, null, excelListener, false);
            }else{
                return null;
            }
        }
        
    }

**5.业务层调用**

//导入excel
    @RequestMapping(value = "excelImport", method = {RequestMethod.GET, RequestMethod.POST })
    public String excelImport(HttpServletRequest request,Model model,@RequestParam("uploadFile") MultipartFile[] files) throws Exception {		
        if(files != null && files.length > 0){
            MultipartFile file = files[0];
            Map<String,Object> result = EasyExcelUtil.readExcel(file, new TestModel(),1);
            Boolean flag = (Boolean) result.get("flag");
            if(flag){
                List<Object> list = (List<Object>) result.get("datas");    
        	    if(list != null && list.size() > 0){
        	        for(Object o : list){
        		        TestModel xfxx = (TestModel) o;
        		        System.out.println(xfxx.getXm()+"/"+xfxx.getSjh()+"/"+xfxx.getSjh());
        	        }
        	    }
            }else{
                System.out.println("表头格式错误");
            }
        }	
        return "index";
    }