java 用反射和泛型操作Excel

 

package xxxx.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 * 数据导入导出Excel基类
 *
 * @author bmladmin
 *
 * @param <T>
 */
public class ExcelUtil<T> {

 private Class<T> entityClass;

 private String[] titles;

 private InputStream input;

 @SuppressWarnings("unused")
 private String excelPath;

 private HSSFWorkbook hsswork;

 // 创建表
 private static HSSFSheet hssheet;

 // 主键是否是自动增长
 private boolean isIncream = true;

 private String ceneterName;

 private int headLength;
 
 private List<T> list;

 /**
  * 导出Excel数据的构造方法
  *
  * @param list要导出的数据
  * @param titles标题
  * @param entityClass实体类
  * @param centerName
  *            excel的名字
  */
 public ExcelUtil(HSSFWorkbook work, List<T> list, String[] titles,
   Class<T> entityClass, String centerName) {
  this.entityClass = entityClass;
  this.titles = titles;
  this.ceneterName = centerName;
  this.list = list;
  this.hsswork = work;
  hssheet = hsswork.createSheet(this.ceneterName);
  this.headLength = this.titles.length;
 }

 /**
  * 导入Excel数据的构造方法
  *
  * @param entityClass
  * @param input
  * @param excelPath
  */
 public ExcelUtil(HSSFWorkbook work, Class<T> entityClass,
   InputStream input, String excelPath, boolean isIncream) {
  this.entityClass = entityClass;
  this.input = input;
  this.excelPath = excelPath;
  this.isIncream = isIncream;
  this.hsswork = work;
  try {
   this.input = new FileInputStream(excelPath);
   hsswork = new HSSFWorkbook(this.input);
  } catch (FileNotFoundException e) {
   e.printStackTrace();
  } catch (IOException e) {
   e.printStackTrace();
  }
  hssheet = hsswork.getSheetAt(0);
 }

 /**
  * 导入Excel数据的重载构造方法
  *
  * @param entityClass
  * @param input
  * @param excelPath
  */
 public ExcelUtil(HSSFWorkbook work, Class<T> entityClass,
   InputStream input, String excelPath) {
  this.entityClass = entityClass;
  this.input = input;
  this.excelPath = excelPath;
  this.hsswork = work;
  try {
   this.input = new FileInputStream(excelPath);
   hsswork = new HSSFWorkbook(this.input);
  } catch (FileNotFoundException e) {
   e.printStackTrace();
  } catch (IOException e) {
   e.printStackTrace();
  }
  hssheet = hsswork.getSheetAt(0);
 }
 
 public ExcelUtil(HSSFWorkbook work, Class<T> entityClass,
   InputStream input) {
  this.entityClass = entityClass;
  this.input = input;
  this.hsswork = work;
  try {
   hsswork = new HSSFWorkbook(this.input);
  } catch (IOException e) {
   e.printStackTrace();
  }
  hssheet = hsswork.getSheetAt(0);
 }
 /**
  * 获取Excel中的数据
  *
  * @return
  */
 public List<T> getCells() {
  List<T> tlist = null;
  int columnlength = hssheet.getPhysicalNumberOfRows();
  if (columnlength > 0) {
   tlist = new ArrayList<T>();
   for (int i = 1; i <= columnlength - 1; i++) {
    Map<Integer, Object> objlist = new HashMap<Integer, Object>();
    HSSFRow row = hssheet.getRow(i);
    int rowlength = row.getPhysicalNumberOfCells();
    for (int j = 0; j < rowlength; j++) {
     HSSFCell cell = row.getCell(j);
     if(cell!=null){
      switch (cell.getCellType()) {
      case HSSFCell.CELL_TYPE_STRING:
       objlist.put(j, cell.getRichStringCellValue());
       break;
      case HSSFCell.CELL_TYPE_NUMERIC:
       objlist.put(j, cell.getNumericCellValue());
       break;
      case HSSFCell.CELL_TYPE_FORMULA:
       objlist.put(j, cell.getCellFormula());
       break;
      }
          }
     }
     

    tlist.add(setT(objlist));
   }
  } else {
   return null;
  }
  return tlist;
 }
 
 /**
  * 获取Excel中的数据
  *
  * @return
  */
 public List<Map<String,Object>> getCells_Title() {
  List<Map<String,Object>>  list=new ArrayList<Map<String,Object>>();
  int columnlength = hssheet.getPhysicalNumberOfRows();
  Map<String, Object> objlist = new HashMap<String, Object>();
  if (columnlength > 0) {
   for (int i = 1; i <= columnlength - 1; i++) {
    HSSFRow row = hssheet.getRow(i);
    int rowlength = row.getPhysicalNumberOfCells();
    for (int j = 0; j < rowlength; j++) {
     HSSFCell cell = row.getCell(j);
     String title=this.getCellTitleIndex(j);
     if(cell!=null){
      switch (cell.getCellType()) {
      case HSSFCell.CELL_TYPE_STRING:
       objlist.put(title, cell.getRichStringCellValue());
       break;
      case HSSFCell.CELL_TYPE_NUMERIC:
       objlist.put(title, cell.getNumericCellValue());
       break;
      case HSSFCell.CELL_TYPE_FORMULA:
       objlist.put(title, cell.getCellFormula());
       break;
      }
      list.add(objlist);
          }
     }
   }
  } else {
   return null;
  }
  return list;
 }
 
 /**
  * 获取Excel中的所有的标题
  *
  * @return
  */
 public List<String> getCellTitle() {
        List<String> titlelist=new ArrayList<String>();
            HSSFRow row=hssheet.getRow(0);
            int s=row.getPhysicalNumberOfCells();
            for(int i=0;i<s;i++){
             HSSFCell cell=row.getCell(i);
             String title=cell.getRichStringCellValue().toString();
             int end=title.lastIndexOf("*");
             if(end>0){
              titlelist.add(title.substring(0,end));
             }else{
              titlelist.add(title);
             }
             
            }
 
     return titlelist;
 }
 /**
  * 获取Excel中的制定的标题
  *
  * @return
  */
 public String getCellTitleIndex(int index) {
                HSSFRow row=hssheet.getRow(0);
             HSSFCell cell=row.getCell(index);
             String title=cell.getRichStringCellValue().toString();
             int end=title.lastIndexOf("*");
             if(end>0){
              return title.substring(0,end);
             }else{
              return title;
             }
   }

 /**
  * 从excel里读取数据后封装为制定的实体类后返回
  *
  * @param objlist
  * @return
  */
 @SuppressWarnings("unchecked")
 public T setT(Map<Integer, Object> objlist) {
  try {
   T tobj = entityClass.newInstance();
   Class cl = tobj.getClass();
   Field[] field = cl.getDeclaredFields();
   for (int i = 0; i < field.length; i++) {
    if(objlist.get(i)!=null){
    String propertyName = field[i].getName();
    String methodName = "set"
      + propertyName.substring(0, 1).toUpperCase()
      + propertyName.substring(1, propertyName.length());
    Class typeclass = field[i].getType();
    Method method = cl.getMethod(methodName,
      new Class[] { typeclass });
    if (typeclass == String.class) {
     method.invoke(tobj, new Object[] {objlist.get(i)
       .toString().trim() });
    } else if (typeclass == Integer.class) {
     if (isIncream == false) {
      String intVar = objlist.get(i).toString();
      intVar = intVar.replace(".", "#");
      String[] netBefor = intVar.split("#");
      if (netBefor.length > 1) {
       method.invoke(tobj, new Object[] { Integer
         .parseInt(netBefor[0]) });
      } else {
       method.invoke(tobj,
         new Object[] { Integer.parseInt(objlist
           .get(i).toString().trim()) });
      }
     }
    } else if (typeclass == Double.class) {
     method.invoke(tobj, new Object[] { Double
       .parseDouble(objlist.get(i).toString()) });
    } else if (typeclass == Float.class) {
     method.invoke(tobj, new Object[] { Float.parseFloat(objlist
       .get(i).toString()) });
    } else if (typeclass == Date.class) {
     method.invoke(tobj, new Object[] { new SimpleDateFormat(
       "yyyy-mm-dd").parse(objlist.get(i).toString()) });
    }else if(typeclass==String[].class){
     
    }
   }
  }
   return tobj;
  } catch (InstantiationException e) {
   e.printStackTrace();
  } catch (IllegalAccessException e) {
   e.printStackTrace();
  } catch (SecurityException e) {
   e.printStackTrace();
  } catch (NoSuchMethodException e) {
   e.printStackTrace();
  } catch (IllegalArgumentException e) {
   e.printStackTrace();
  } catch (InvocationTargetException e) {
   e.printStackTrace();
  } catch (ParseException e) {
   e.printStackTrace();
  }
  return null;
 }
 /**
  * 导出Excel
  *
  * @param work
  * @throws IOException
  */
 public void exportExcel(OutputStream os) throws IOException {
  hssheet.setGridsPrinted(true);
  HSSFFooter footer = hssheet.getFooter();
  footer.setRight("page" + HSSFFooter.page() + "of"
    + HSSFFooter.numPages());
  hsswork.write(os);
 }

 /**
  * 创建表头
  *
  */
 private void createHead() {
  HSSFHeader head = hssheet.getHeader();
  head.setCenter("Excel数据");
  HSSFRow row = hssheet.createRow(0);
  for (int i = 0; i < this.headLength; i++) {
   HSSFCell cell = row.createCell(i);
   cell.setCellType(HSSFCell.ENCODING_UTF_16);
   HSSFRichTextString richText = new HSSFRichTextString(titles[i]);
   cell.setCellValue(richText);
  }
 }

 /**
  * 创建行
  *
  * @param rowList
  * @param rowIndex
  */
 private void createRow(List<Object> rowList, int rowIndex) {
  HSSFRow row = hssheet.createRow(rowIndex);
  for (int i = 0; i < rowList.size(); i++) {
   HSSFCell cell = row.createCell(i);
   cell.setCellType(HSSFCell.ENCODING_UTF_16);
   // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
   HSSFRichTextString richText;
   System.out.println(rowList.get(i));
   if (rowList.get(i) != null) {
    richText = new HSSFRichTextString(rowList.get(i).toString());
   } else {
    richText = new HSSFRichTextString("".toString());
   }
   cell.setCellValue(richText);
  }
 }

 /**
  * 创建整个Excel
  *
  * @throws IllegalAccessException
  * @throws InstantiationException
  *
  */
 @SuppressWarnings("unused")
 public void createExcelSheeet() throws InstantiationException,
   IllegalAccessException {
  createHead();
  int tag = 1;
  if(list!=null){
  for (int i = 0; i < list.size(); i++) {
   T t = list.get(i);
   try {
    List<Object> ectlist = getT(t);
    createRow(ectlist, tag);
    tag++;
   } catch (SecurityException e) {
    e.printStackTrace();
   } catch (NoSuchMethodException e) {
    e.printStackTrace();
   }
  }
   }
 }

 /**
  * 根据类信息获取值
  *
  * @param tclass
  * @return
  * @throws IllegalAccessException
  * @throws InstantiationException
  * @throws NoSuchMethodException
  * @throws SecurityException
  */
 @SuppressWarnings("unused")
 private List<Object> getT(Object obj) throws InstantiationException,
   IllegalAccessException, SecurityException, NoSuchMethodException {
  List<Object> ectlist = null;
  T t = entityClass.newInstance();
  Class ec = t.getClass();
  Field[] fd = ec.getDeclaredFields();
  if (obj != null) {
   ectlist = new ArrayList<Object>();
  }
  for (Field ld : fd) {
   String propertyName = ld.getName();
   propertyName = "get" + propertyName.substring(0, 1).toUpperCase()
     + propertyName.substring(1, propertyName.length());
   Method method = ec.getMethod(propertyName, new Class[] {});
   try {
    ectlist.add(method.invoke(obj, new Object[] {}));
   } catch (IllegalArgumentException e) {
    e.printStackTrace();
   } catch (InvocationTargetException e) {
    e.printStackTrace();
   }
  }
  return ectlist;
 }

 private Object getTChildObject(Class<?> obj_class) {
  Object obj = null;
  try {
   obj = obj_class.newInstance();
  } catch (InstantiationException e1) {
   e1.printStackTrace();
  } catch (IllegalAccessException e1) {
   e1.printStackTrace();
  }
  String name = obj_class.getName();
  if (isModel(name)) {
   Field[] filed = obj_class.getDeclaredFields();
   for (Field ld : filed) {
    String propertyName = ld.getName();
    propertyName = "get"
      + propertyName.substring(0, 1).toUpperCase()
      + propertyName.substring(1, propertyName.length());
    try {
     Method method = obj_class.getMethod(propertyName,
       new Class[] {});
     try {
      return method.invoke(obj, new Object[] {});
     } catch (IllegalArgumentException e) {
      e.printStackTrace();
     } catch (IllegalAccessException e) {
      e.printStackTrace();
     } catch (InvocationTargetException e) {
      e.printStackTrace();
     }
    } catch (SecurityException e) {
     e.printStackTrace();
    } catch (NoSuchMethodException e) {
     e.printStackTrace();
    }

   }

  }
  return null;
 }
 
 private boolean isModel(String modelStr){
  if (modelStr != "java.lang.String" && modelStr != "java.lang.Integer"
   && modelStr != "java.lang.Boolean" && modelStr != "java.lang.Double"
   && modelStr != "java.lang.Float") {
   return true;
  }else{
   return false;
  }
 }
}