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;
??}
?}
}
?
?
