excel转换成实体

package com.cinc.ecmp.utils;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
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.ss.usermodel.WorkbookFactory;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import com.cinc.ecmp.annotation.ExcelField;
import com.cinc.ecmp.enums.BackResultEnum;
import com.cinc.ecmp.exception.BasException;

import lombok.extern.slf4j.Slf4j;

/**
 * @author j
 * 操作方式: 
 * 根据Excel格式编写vo，vo编写方式参照com.cinc.ecmp.demo.vo.ExcelMaterialVo
 */
@Slf4j
public class ExcelParseUtils {

	public static <T> List<T> parse(MultipartFile file,Class<T> clazz){
		Workbook hwb=null;
		InputStream in = null;
		try{
			if(null == file){
				throw new BasException(BackResultEnum.DATAPARSEERR);
			}
			in = file.getInputStream();
			hwb=WorkbookFactory.create(in);
			Sheet sheet=hwb.getSheetAt(0);
			if(sheet==null) {
				return null;
			}
			List<T> excelVoList=new ArrayList<T>();
			Field[] fields=clazz.getDeclaredFields();
			for(int i=sheet.getFirstRowNum()+1;i<sheet.getPhysicalNumberOfRows();i++) {
				Row row = sheet.getRow(i);
				if(null==row.getCell(1)||row.getCell(1).getCellType()==CellType.BLANK) {
	            	break;//如果这一行的第一列为空，则终止解析
	            }
				T object = clazz.newInstance();
				for(Field field:fields) {
					//获取该字段的列号
					ExcelField excelField=field.getAnnotation(ExcelField.class);
					if(null == excelField) {
						continue;
					}
					int index = excelField.index();
					String value="";
					Cell cell = row.getCell(index);
					if(null == cell) {
						continue;
					}
					CellType cellType = cell.getCellType();
					switch(cellType) {
						case STRING:
							value = String.valueOf(cell.getRichStringCellValue());
							break;
						case FORMULA:
							try {
								value = String.valueOf(cell.getNumericCellValue());
							}catch (Exception e) {
								value = String.valueOf(cell.getRichStringCellValue());
							}
							break;
						case NUMERIC:
							if(HSSFDateUtil.isCellDateFormatted(cell)) {
								Date date=HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
								value=DateUtil.getDateTimeStringToDb(date);
							}else {
								DecimalFormat df = new DecimalFormat();
								value = df.format(cell.getNumericCellValue());
							}
							break;
						case BLANK:
							break;
						default:
							throw new BasException(BackResultEnum.DATAPARSEERR);
					}
					//非空判断
					if(excelField.required()&&StringUtils.isEmpty(value)) {
						log.error("【{}不能为空】",excelField.desc());
						throw new BasException(BackResultEnum.DATAPARSEERR);
					}
					//数据合法性判断
					String[] dataScopes=excelField.inList();
					if(!CollectionUtils.isEmpty(Arrays.asList(dataScopes))&&!Arrays.asList(dataScopes).contains(value)) {
						log.error("【{}不合法】",excelField.desc());
						throw new BasException(BackResultEnum.DATAPARSEERR);
					}
					//字段类型设置
					Class<?> typeClass=field.getType();
					Method method = clazz.getMethod("set" + Character.toUpperCase(field.getName().charAt(0)) + field.getName().substring(1), typeClass);
					//将字段值设置到对象中
					try {
						if(typeClass==Integer.class) {
							method.invoke(object, value.indexOf(".")>0?Integer.parseInt(value.substring(0, value.indexOf("."))):Integer.parseInt(value));
						}else if(typeClass==BigDecimal.class) {
							method.invoke(object, new BigDecimal(value.indexOf(",")>0?value.replaceAll(",", ""):value));
						}else {
							method.invoke(object, value.indexOf(",")>0?value.replaceAll(",", ""):value);
						}
					}catch (Exception e) {
						log.error("【{}设置错误】",excelField.desc(),e);
						throw new BasException(BackResultEnum.DATAPARSEERR);
					}
				}
				if(excelVoList.contains(object)) {
					log.error("【导入失败，数据存在重复】");
					throw new BasException(BackResultEnum.DATAREPEATERR);
				}
				excelVoList.add(object);
			}
			return excelVoList;
		}catch (Exception e) {
			log.error("【文件上传失败】",e);
			throw new BasException(BackResultEnum.FILEUPLOADERR);
		}finally {
			if(null != hwb){
				try {
					hwb.close();
				} catch (IOException e) {
					log.error("【文件流关闭失败】",e);
				}
			}
            if(null != in){
				try {
					in.close();
				} catch (IOException e) {
					log.error("【文件流关闭失败】",e);
				}
			}
		}
	}
}

　　vo实体类的编写：

package com.cinc.ecmp.demo.vo;

import java.math.BigDecimal;

import com.cinc.ecmp.annotation.ExcelField;

import lombok.Data;

/** 
 * @author 
 * @time   2019年7月2日 下午7:59:26 
 */
@Data
public class ExcelMaterialVo {

	@ExcelField(index=0,required=true,desc="材料名称")
	private String name;
	
	@ExcelField(index=1,required=true,desc="价格")
	private BigDecimal price;
	
	@ExcelField(index=2,required=true,desc="采购数量")
	private Integer count;
}

注解的编写：

package com.cinc.ecmp.annotation;

import static java.lang.annotation.ElementType.FIELD;
import static java.lang.annotation.RetentionPolicy.RUNTIME;

import java.lang.annotation.Inherited;
import java.lang.annotation.Retention;
import java.lang.annotation.Target;

@Retention(RUNTIME)
@Target(FIELD)
@Inherited
public @interface ExcelField {

	/**
	 * Excel表格列顺序，起始为0
	 * @return
	 */
	int index();
	
	/***
     * 数据范围
     * @return
     */
    String[] inList() default {};

    /***
     * 正则
     * @return
     */
    String pattern() default "";
    
    /***
     * 字段描述
     * @return
     */
    String desc() default "";

    /**
     * 是否是必须的
     *
     * @return
     */
    boolean required() default false;
}

　　

@Override
public List<ExcelMaterialVo> upload(MultipartFile file){
   List<ExcelMaterialVo> excelMaterialVoList=ExcelParseUtils.parse(file, ExcelMaterialVo.class);
   return excelMaterialVoList;
}