---
title: "java 用反射和泛型操作Excel"
author: "bmladmin"
date: "2012-03-23T15:39:28Z"
tags: []
categories: ["编程语言"]
slug: "java-excel"
draft: false
lastmod: "2012-03-23T15:40:19Z"
id: 1462533
url: "https://scjava.iteye.com/blog/1462533"
---

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 &lt;T&gt;  
 \*/  
public class ExcelUtil&lt;T&gt; {

 private Class&lt;T&gt; 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&lt;T&gt; list;

 /\**  
  * 导出Excel数据的构造方法  
  *  
  * @param list要导出的数据  
  * @param titles标题  
  * @param entityClass实体类  
  * @param centerName  
  *            excel的名字  
  \*/  
 public ExcelUtil(HSSFWorkbook work, List&lt;T&gt; list, String\[] titles,  
   Class&lt;T&gt; 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&lt;T&gt; 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&lt;T&gt; 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&lt;T&gt; 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&lt;T&gt; getCells() {  
  List&lt;T&gt; tlist = null;  
  int columnlength = hssheet.getPhysicalNumberOfRows();  
  if (columnlength &gt; 0) {  
   tlist = new ArrayList&lt;T&gt;();  
   for (int i = 1; i &lt;= columnlength - 1; i++) {  
    Map&lt;Integer, Object&gt; objlist = new HashMap&lt;Integer, Object&gt;();  
    HSSFRow row = hssheet.getRow(i);  
    int rowlength = row.getPhysicalNumberOfCells();  
    for (int j = 0; j &lt; 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&lt;Map&lt;String,Object&gt;&gt; getCells\_Title() {  
  List&lt;Map&lt;String,Object&gt;&gt;  list=new ArrayList&lt;Map&lt;String,Object&gt;&gt;();  
  int columnlength = hssheet.getPhysicalNumberOfRows();  
  Map&lt;String, Object&gt; objlist = new HashMap&lt;String, Object&gt;();  
  if (columnlength &gt; 0) {  
   for (int i = 1; i &lt;= columnlength - 1; i++) {  
    HSSFRow row = hssheet.getRow(i);  
    int rowlength = row.getPhysicalNumberOfCells();  
    for (int j = 0; j &lt; 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&lt;String&gt; getCellTitle() {  
        List&lt;String&gt; titlelist=new ArrayList&lt;String&gt;();  
            HSSFRow row=hssheet.getRow(0);  
            int s=row.getPhysicalNumberOfCells();  
            for(int i=0;i&lt;s;i++){  
             HSSFCell cell=row.getCell(i);  
             String title=cell.getRichStringCellValue().toString();  
             int end=title.lastIndexOf("\*");  
             if(end&gt;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&gt;0){  
              return title.substring(0,end);  
             }else{  
              return title;  
             }  
   }

 /\**  
  * 从excel里读取数据后封装为制定的实体类后返回  
  *  
  * @param objlist  
  * @return  
  \*/  
 @SuppressWarnings("unchecked")  
 public T setT(Map&lt;Integer, Object&gt; objlist) {  
  try {  
   T tobj = entityClass.newInstance();  
   Class cl = tobj.getClass();  
   Field\[] field = cl.getDeclaredFields();  
   for (int i = 0; i &lt; 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 &gt; 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 &lt; 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&lt;Object&gt; rowList, int rowIndex) {  
  HSSFRow row = hssheet.createRow(rowIndex);  
  for (int i = 0; i &lt; 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 &lt; list.size(); i++) {  
   T t = list.get(i);  
   try {  
    List&lt;Object&gt; 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&lt;Object&gt; getT(Object obj) throws InstantiationException,  
   IllegalAccessException, SecurityException, NoSuchMethodException {  
  List&lt;Object&gt; ectlist = null;  
  T t = entityClass.newInstance();  
  Class ec = t.getClass();  
  Field\[] fd = ec.getDeclaredFields();  
  if (obj != null) {  
   ectlist = new ArrayList&lt;Object&gt;();  
  }  
  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&lt;?&gt; 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;  
  }  
 }  
}