package test.excelTest;

 import java.io.File;

 import java.io.FileInputStream;

 import java.lang.reflect.Field;

 import java.text.NumberFormat;

 import java.util.ArrayList;

 import java.util.List;

 import org.apache.commons.lang.StringUtils;

 import org.apache.poi.hssf.usermodel.HSSFCell;

 import org.apache.poi.hssf.usermodel.HSSFRow;

 import org.apache.poi.hssf.usermodel.HSSFSheet;

 import org.apache.poi.hssf.usermodel.HSSFWorkbook;

 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.XSSFCell;

 import org.apache.poi.xssf.usermodel.XSSFRow;

 import org.apache.poi.xssf.usermodel.XSSFSheet;

 import org.apache.poi.xssf.usermodel.XSSFWorkbook;

 import org.slf4j.Logger;

 import org.slf4j.LoggerFactory;

 /**

  * Desc：文件工具类

  */

 public class FileUtil {

     private static Logger logger = LoggerFactory

             .getLogger(FileUtil.class);

     /**

      * 读取xml

      * @param classz

      * @param fileSrc

      * @return

      */

     public static Object readExcel(Class classz,String fileSrc){

         List list = new ArrayList<>();

         int x = 0;//记录出错位置

         int y = 0;

         try {

             Workbook wb = null;

             if (isExcel2003(fileSrc)){

                 wb = new HSSFWorkbook(new FileInputStream(new File(fileSrc)));

             }else if(isExcel2007(fileSrc)){

                 wb = new XSSFWorkbook(new FileInputStream(new File(fileSrc)));

             }

             Sheet sheet = wb.getSheetAt(0);

             if(sheet.getLastRowNum() < 1){

                 return list;

             }

             Row headRow = sheet.getRow(0);

             for (int i = 1; i < sheet.getLastRowNum() + 1 ; i++){

                 x = i;

                 Row row = sheet.getRow(i);

                 Object t = classz.newInstance();

                 for(int j = 0; j < row.getLastCellNum(); j++){

                     y = j;

                     Cell cellHeadFiled = headRow.getCell(j);

                     Cell cellFiled = row.getCell(j);

                     if(cellHeadFiled == null || cellFiled == null){

                         continue;

                     }

                     String cellFiledName = null;

                     if(cellHeadFiled != null){

                         cellFiledName = cellHeadFiled.getRichStringCellValue().getString();

                     }

                     cellFiled.setCellType(Cell.CELL_TYPE_STRING);

                     String cellFiledValue = String.valueOf(cellFiled.getRichStringCellValue());

                     setFiledValue(t, cellFiledName, cellFiledValue, x, y);

                 }

                 list.add(t);

             }

         } catch (Exception e) {

             e.printStackTrace();

             logger.error("解析excel异常。请检查文件内容是否正确,第"+y+"行,"+(x+1)+"列出错");;

         }

         return list;

     }

     /**

      * 设置字段值

      * @param object

      * @param excelFiledName

      * @param value

      */

     private static void setFiledValue(Object object,String excelFiledName,String value, int x, int y){

         Class classz = object.getClass();

         Field[] fields = classz.getDeclaredFields();

         for(Field field : fields){

             String filedName = field.getName();

             ExcelField excelField = field.getAnnotation(ExcelField.class);

             if(excelField != null){

                 filedName = excelField.fieldName();

             }

             String orgFiledName = field.getType().getName();

             String filedTypeName = orgFiledName.toUpperCase();

             if(excelFiledName.equalsIgnoreCase(filedName)){

                 field.setAccessible(true);

                 try {

                     if(isNumeric(value)){

                         NumberFormat numberFormat = NumberFormat.getNumberInstance();

                         Number number = numberFormat.parse(value);

                         if(filedTypeName.contains("INT")){

                             field.set(object, number.intValue());

                         }else if(filedTypeName.contains("DOUBLE")){

                             field.set(object, number.doubleValue());

                         }else if(filedTypeName.contains("FLOAT")){

                             field.set(object, number.floatValue());

                         }else if(filedTypeName.contains("LONG")){

                             field.set(object, number.longValue());

                         }else if(filedTypeName.contains("SHORT")){

                             field.set(object, number.shortValue());

                         }

                     }else {

                         if(filedTypeName.contains("BOOLEAN")){

                             field.set(object,Boolean.valueOf(value));

                         }else{

                             field.set(object,value);

                         }

                     }

                 }  catch (Exception e) {

                     e.printStackTrace();

                     logger.error("暂不支持的数据类型["+orgFiledName+"]。请检查文件内容是否正确,第"+y+"行,"+(x+1)+"列出错");

                 }

                 break;

             }

         }

     }

     /**

      * 判断字符串是否为数字

      * @param str

      * @return

      */

     public static boolean isNumeric(String str){

         if(StringUtils.isEmpty(str)){

             return false;

         }

         for (int i = str.length();--i>=0;){

             if (!Character.isDigit(str.charAt(i))){

                 return false;

             }

         }

         return true;

     }

     public static boolean isExcel2003(String filePath)

     {

         return filePath.matches("^.+\\.(?i)(xls)$");

     }

     public static boolean isExcel2007(String filePath)

     {

         return filePath.matches("^.+\\.(?i)(xlsx)$");

     }

     public static void main(String[] args) throws Exception {

         String sre = "D://b.xls";

         Object result = readExcel(Person.class, sre);

         List<Person> list = (List<Person>) result;

         for(Person person : list){

             System.out.println("姓名:"+person.getName()+"---年龄:"+person.getAge()+"---性别:"+person.getSex());

         }

     }

 }

 package test.excelTest;

 import java.lang.annotation.*;  

 /**

  * Desc：Excel字段名

  */

 @Target(ElementType.FIELD)

 @Retention(RetentionPolicy.RUNTIME)

 @Documented

 public @interface ExcelField {  

     public String fieldName();

 }  

 package test.excelTest;

 /**

  * Desc： module

  */

 public class Person {  

     @ExcelField(fieldName="名字")

     private String name;  

     @ExcelField(fieldName="年龄")

     private double age;  

     @ExcelField(fieldName="性别")

     private String sex;  

     public Person(){  

     }  

     public String getName() {

         return name;

     }  

     public void setName(String name) {

         this.name = name;

     }  

     public double getAge() {

         return age;

     }  

     public void setAge(double age) {

         this.age = age;

     }  

     public String getSex() {

         return sex;

     }  

     public void setSex(String sex) {

         this.sex = sex;

     }

 }