Java导入Excel数据

墨蓝 2022-05-13 03:38 339阅读 0赞

**例如导入的excel所在目录为D:/cs/test.xls，测试数据如下图所示：**

![70][]

**1.创建与excel表头对应的xml模版**

<?xml version="1.0" encoding="UTF-8"?>
    
    <execelImport>
    	<field>姓名</field>
    	<field>性别</field>
    	<field>手机号</field>
    	<field>邮箱</field>
    	<field>住址</field>
    </execelImport>

**2.创建导入数据对应实体类**

public class Persion {
    
    	private String name;
    	private String sex;
    	private String phone;
    	private String email;
    	private String address;
    	
    	public String getName() {
    		return name;
    	}
    	public void setName(String name) {
    		this.name = name;
    	}
    	public String getSex() {
    		return sex;
    	}
    	public void setSex(String sex) {
    		this.sex = sex;
    	}
    	public String getPhone() {
    		return phone;
    	}
    	public void setPhone(String phone) {
    		this.phone = phone;
    	}
    	public String getEmail() {
    		return email;
    	}
    	public void setEmail(String email) {
    		this.email = email;
    	}
    	public String getAddress() {
    		return address;
    	}
    	public void setAddress(String address) {
    		this.address = address;
    	}
    	
    	public void setValues(String[] values) throws Exception{
    		this.name = values[0] ==null?"":values[0].trim();
    		this.sex = values[1] ==null?"":values[1].trim();
    		this.phone = values[2] ==null?"":values[2].trim();
    		this.email = values[3] ==null?"":values[3].trim();
    		this.address = values[4] ==null?"":values[4].trim();
    	}
    	
    }

**3.创建导入数据工具类**

import java.io.File;
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.UnsupportedEncodingException;
    import java.net.URLDecoder;
    import java.text.DateFormat;
    import java.text.DecimalFormat;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.Arrays;
    import java.util.Date;
    import java.util.HashMap;
    import java.util.Iterator;
    import java.util.List;
    import java.util.Set;
    
    import javax.validation.ConstraintViolation;
    import javax.validation.Validation;
    import javax.validation.Validator;
    import javax.validation.ValidatorFactory;
    
    import org.apache.log4j.Logger;
    import org.apache.poi.hssf.usermodel.HSSFDateUtil;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.hssf.util.CellReference;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.dom4j.Document;
    import org.dom4j.DocumentException;
    import org.dom4j.Element;
    import org.dom4j.io.SAXReader;
    import org.springframework.expression.EvaluationException;
    import org.springframework.expression.Expression;
    import org.springframework.expression.ExpressionParser;
    import org.springframework.expression.spel.standard.SpelExpressionParser;
    
    /**
     * @Desc 对execel文件的校验并且解析
     */
    @SuppressWarnings("rawtypes")
    public class ExecelUtil {
    	Logger logger = Logger.getLogger(this.getClass());
    
    	private String description = "";// 如果校验失败，将会给出详细提示信息
    	private Sheet sheet;// execel 对象
        private List<String> fieldList;//从xml读取到的execel表格信息
        private int rowIndex = 0;//当前操作行
        private Object objectBean;//每一行数据封装 
        private Cell cellStart;// 数据的开始单元格
        private Class clazz; //需要封装的类
        private Validator validator; //hibernate 的校验器
        private String[] fieldVals ; //从execel读到的某一行的数据
        private int fieldSize = 0;  //有效数据的列数
        private DateFormat format = new SimpleDateFormat("yyyy-MM-dd"); // 针对日期的默认转换形式
        private Expression exp ;//EL 解析器
        private ExpressionParser parser;
        
        private DecimalFormat df = new DecimalFormat("#");
        
    	public String getDescription() {
    		return description;
    	}
    
    	public Object getObjectBean() {
    		return objectBean;
    	}
    	/**
    	 * 
    	 * @param execelFilename
    	 *            execel文件名
    	 * @param xmlFilename
    	 *            execel文件所对应的校验文件
    	 * @param calzz 需要封装的类
    	 */
    	public ExecelUtil(InputStream execelIS,String xmlFilename,Class clazz,String suffix) throws ValidationExeception{
    		// 打开execel工作簿		
    		 Workbook wb = null;
    		try {
    			if(suffix.equals(".xls")){
    				wb = new HSSFWorkbook(execelIS);
    			}else if(suffix.equals(".xlsx")){
    				wb = new XSSFWorkbook(execelIS);
    			}
    			execelIS.close();
    		} catch (IOException e) {
    			logger.error(e);
    			throw new ValidationExeception("","加载文件失败,请确保是否是Execel表格");
    		}		
    		sheet = wb.getSheetAt(0);// 默认取第一个工作簿
    		//读配置文件，获取所有的属性列描述
            fieldList = this.readFieldsFromXML(getAbsolutePath(xmlFilename));
            //个数
            fieldSize = fieldList.size();
            
    		//找到有效数据的开始单元格
    		cellStart = this.findStartCell();
    		if(cellStart == null){			
    			throw new ValidationExeception("",this.description);
    		}
    		
    		//每次读取一行execel数据，rowIndex每次增1
    		rowIndex = cellStart.getRowIndex()+1;
    		
    		//需要封装的对象类
    		this.clazz = clazz;
    		//初始化校验器
    		ValidatorFactory factory = Validation.buildDefaultValidatorFactory();
    		validator = factory.getValidator();
    		//初始化EL解析器
    		parser = new SpelExpressionParser();
    		exp = parser.parseExpression("values");
    	}
    	
    	//是否还有数据
        public boolean hasNext(){
        	Row row = sheet.getRow(rowIndex++);
        	if(row == null)
        		return false;
        	fieldVals = this.getRowValues(row, cellStart.getColumnIndex());
            if(Arrays.asList(fieldVals).indexOf("") != -1){
            	for(String s :fieldVals)//如果每个字段都是空的，则返回false 否则true
            		if(!s.equals(""))
            			return true;
            	return false;
            }
        	return true;
        }
        //校验
        public boolean validate(){
            try {        
            	objectBean = Class.forName(clazz.getName()).newInstance();
    		} catch (Exception e) {
    			logger.error(e);
    		}
    		
    		try{	
    		    exp.setValue(objectBean, fieldVals);// 给objectBean的属性赋值
    		    
    		}catch(EvaluationException e){//由于所有的数据类型转换都有objectBean里面来处理，故可能有异常，需要进行相应的处理
    			System.out.println(e);
    			List exList = Arrays.asList("ParseException","NumberFormatException");//一般可能发生的异常
    			Throwable t = e.getCause();
    			while(t!=null){
    				String causeClazz = t.getClass().getSimpleName();
    				if(exList.contains(causeClazz)){
                           this.description = "第" +rowIndex+"行，类型转换失败："+t.getMessage();
                           return false;
    				}else if(causeClazz.equals("ValidationExeception")){//自定义异常
    					this.description = "第" +rowIndex+"行,"+t.getMessage();
    					return false;
    				}else
    					t = t.getCause();
    			}			 
    			//this.description = parser.parseExpression("sales").getValue(objectBean,String.class);
    			this.description = "数据错误";
    			return false;
    		}
    		//校验，校验规则是配置在objectBean对象里面
    		Set<ConstraintViolation<Object>> constraintViolations = validator.validate(objectBean);
    		
    		if(constraintViolations.size() >0){//校验失败时，提示相应信息
    			this.description = "第" +rowIndex+"行，校验出错：";
    			for (ConstraintViolation<Object> vl : constraintViolations) {
    				this.description = this.description + vl.getMessage()+" ;  ";
    			}				
    			return false;
    		}
    		return true;
        }
        
        private String[] getRowValues(Row row,int columnStartIndex){
            String[] values = new String[fieldSize];
        	for(int j = columnStartIndex,t=0;t<fieldSize;j++,t++){
        		Cell c = row.getCell(j);
        		if(c==null){
        			values[t] = "";
        			continue;
        		}
        		switch(c.getCellType()){
        		case Cell.CELL_TYPE_BLANK:
        			values[t] = "";
        			break;
        		case Cell.CELL_TYPE_BOOLEAN:
        			values[t] = String.valueOf(c.getBooleanCellValue());
        			break;
        		case Cell.CELL_TYPE_NUMERIC:
        			
    				if (HSSFDateUtil.isCellDateFormatted(c)) {
    					values[t] = format.format(c.getDateCellValue());
    				} else if(c.getCellStyle().getDataFormat() == 58) {
    					// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)  
    	                SimpleDateFormat sdf = new SimpleDateFormat("MM/dd");  
    	                double value = c.getNumericCellValue();  
    	                Date date = org.apache.poi.ss.usermodel.DateUtil  
    	                        .getJavaDate(value);  
    	                
    	                values[t] = sdf.format(date); 
    				}
    				else {
    					values[t] = new DecimalFormat("#").format(c.getNumericCellValue()); 
    				}
        			break;
        		case Cell.CELL_TYPE_STRING:
        			values[t] = String.valueOf(c.getStringCellValue());
        			break;
        		
        		default:
        			values[t] = "";
        			break;
        		}
        	}
        	return values;
        }
    	// 根据某一个单元格，得到更人性化的显示,例如“A4”
    	private String getCellRef(Cell cell) {
    		return CellReference.convertNumToColString(cell.getColumnIndex()) + (cell.getRowIndex() + 1);
    	}
    	private  String getAbsolutePath(String file) throws ValidationExeception {
    		try {
    			file = this.getClass().getClassLoader().getResource(file).getFile();
    
    		} catch (NullPointerException e) {
    			throw new ValidationExeception(file, "文件不存在");
    		}
    		try {
    			// 解决当出现中文路径时不能解析的bug
    			file = URLDecoder.decode(file, "UTF-8");
    		} catch (UnsupportedEncodingException e) {
    			throw new ValidationExeception(file, "解码失败");
    		}
    		return file;
    	}
    	private List<String> readFieldsFromXML(String xmlFilename)throws ValidationExeception{
    		SAXReader reader = new SAXReader();
    		Document document = null;
    		
    		try {
    			document = reader.read(new File(xmlFilename));// 加载配置文件
    		} catch (DocumentException e) {
    			e.printStackTrace();
    			this.description = "IO 异常，读取配置文件失败";
    			throw new ValidationExeception(xmlFilename,"IO 异常，读取配置文件失败");
    		}
    		
    		Element root = document.getRootElement();
    		List<String> fields = new ArrayList<String>();
    		for (Iterator iter = root.elementIterator("field"); iter.hasNext();) {
    			Element field = (Element) iter.next();
                fields.add(field.getTextTrim());
    		}
    		
    		return fields;
    	}
    	/**
    	 * 从execel表中找到数据开始的单元格
    	 * @return
    	 */
    	private  Cell findStartCell(){
    		String firstFieldDesc = this.fieldList.get(0);
    		int endRow = sheet.getLastRowNum()>100?100:sheet.getLastRowNum();
    		for(int i = 0;i<=endRow;i++){
    			Row r = sheet.getRow(i);
    			if (r == null)
    				continue;
    			for(int j = 0;j < r.getLastCellNum();j++){
    				Cell c = r.getCell(j);
    				if( c == null)
    					continue;
    				if(c.getCellType() == Cell.CELL_TYPE_STRING){
    					if(c.getStringCellValue().trim().equals(firstFieldDesc)){//找到第一个符合要求的字段，接下来判断它相邻的字段是否都符合要求
                            if(fieldList.size()>r.getLastCellNum()-j){
                            	this.description = "execel表格与所给配置描述不符，请下载模板文件";
                            	return null;
                            }
    						for(int k=j+1,t=1;k<=j+fieldList.size()-1;k++,t++){
    							Cell c2 = r.getCell(k);
    							if(c2 == null){
    								this.description = "请确保单元格"+this.getCellRef(c2)+"内容是\""+fieldList.get(t)+"\"";
    								return null;
    							}
    							if(c2.getCellType() == Cell.CELL_TYPE_STRING){
    								if(c2.getStringCellValue().contains(fieldList.get(t)))
    									continue;
    								else{
    										this.description = "请确保单元格"+this.getCellRef(c2)+"内容是\""+fieldList.get(t)+"\"";
    										return null;	
    								}
    							}
    							 
    						}
    						return c;
    					}else
    						continue;
    				}else
    					continue;
    			}
    		}
    		this.description = "找不到\""+fieldList.get(0)+"\"这一列";
    		return null;
    	}
    
    	public int getRowIndex() {
    		return rowIndex;
    	}
    
    	public DateFormat getFormat() {
    		return format;
    	}
    
    	
    	public String createExcelIncludeFailReason(InputStream execelIS,String path,String newExcelName, HashMap<Integer,String> errHash) {
    		FileOutputStream file;
    		try {
    			file = new FileOutputStream(path+"/"+newExcelName);
    			Workbook workbook=this.getWorkbook(execelIS);
    			Sheet s=workbook.getSheetAt(0);			
    		    
    		    //int endRow = sheet.getLastRowNum()>100?100:sheet.getLastRowNum();
    			int endRow = sheet.getLastRowNum();
    		    for(int i=1;i<=endRow;i++){		    	
    		    	if(errHash.get(i)!=null){
    		    		Row rowkk = s.getRow(i);	    			
    			    	Cell error_cell = rowkk.createCell(fieldList.size());
    			    	error_cell.setCellValue(errHash.get(i));
    		    	}		    	
    		    }
    		    workbook.write(file);
    			file.close();
    		} catch (FileNotFoundException e) {
    			
    			e.printStackTrace();
    		} catch (IOException e) {
    			
    			e.printStackTrace();
    		} catch (ValidationExeception e) {
    			
    			e.printStackTrace();
    		}
    
    		return newExcelName;
    	}
    	
    
    	public String getFile(InputStream execelIS,String path,String appPath, List<Integer> listF, boolean b) {
    		FileOutputStream file;
    		try {
    			file = new FileOutputStream(path+"/"+appPath);
    			Workbook workbook=this.getWorkbook(execelIS);
    			Sheet s=workbook.getSheetAt(0);			
    		    Row row=null;
    		   //int endRow = sheet.getLastRowNum()>100?100:sheet.getLastRowNum();
    		    int endRow = sheet.getLastRowNum();
    		    for(int i=1;i<=endRow;i++){
    		    	
    		    	Row rowkk = s.getRow(i);	    			
    		    	Cell info_cell = rowkk.createCell(fieldList.size());
    		    	info_cell.setCellValue("sss");
    		    	
    		    	//删除错误行
    		    	if(b&& listF.contains(i)){
    		    		row=s.getRow(i);		    		
    		    		//System.out.println("错误行删除"+i);
    		    		if(row!=null)s.removeRow(row);
    		    	}
    		    	//删除正确行
    		    	if(!b && !listF.contains(i)){
    		    		row=s.getRow(i);
    		    		//System.out.println("正确行删除"+i);
    		    		if(row!=null)s.removeRow(row);
    		    	}
    		    }
    		    workbook.write(file);
    			file.close();
    		} catch (FileNotFoundException e) {
    			
    			e.printStackTrace();
    		} catch (IOException e) {
    			
    			e.printStackTrace();
    		} catch (ValidationExeception e) {
    			
    			e.printStackTrace();
    		}
    
    		return appPath;
    	}
    	public  Workbook  getWorkbook(InputStream execelIS) throws ValidationExeception{
    		Workbook wb = null;
    		try {
    			wb = new HSSFWorkbook(execelIS);
    		} catch (IOException e) {
    			logger.error(e);
    			throw new ValidationExeception("","加载文件失败,请确保是否是Execel表格");
    		}
    		return wb;
    	}
    	 
    }

**4.测试取出数据**

public class Test {
    
    	public static void main(String[] args) throws Exception {
    		InputStream is = new FileInputStream("D:/cs/test.xls");
    		ExecelUtil eu = new ExecelUtil(is, "excel/import_test.xml", Persion.class,".xls");
    		Persion cp = null;
    		while (eu.hasNext()) {
    			if (eu.validate()) {
    				cp = (Persion) eu.getObjectBean();				
    				System.out.println(cp.getName()+"/"+cp.getSex()+"/"+cp.getPhone()+"/"+cp.getEmail()+"/"+cp.getAddress());
    			}
    		}
    	}
    }

[70]: /images/20220513/1f20fa3649734e24b3bfaa8e6319b7db.png