import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Type;
import java.util.ArrayList;
import java.util.List;
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;
public class ExcelUtil {
public static void main(String[] args) {
List<TestBean> tbList = new ArrayList<TestBean>();
TestBean tb = new TestBean();
tb.setB((byte) 1);
tb.setBd((byte) 2);
tb.setF(1);
tb.setFd((float) 2);
tb.setI(1);
tb.setId(2);
tb.setL(1l);
tb.setLd(2l);
tb.setF(1.1f);
tb.setFd(2.1f);
tb.setD(1.1d);
tb.setDd(2.1d);
tb.setBo(true);
tb.setBod(false);
tb.setC('a');
tb.setCd('b');
tb.setS("a");
tbList.add(tb);
TestBean tb1 = new TestBean();
tb1.setB((byte) 1);
tb1.setBd((byte) 2);
tb1.setF(1);
tb1.setFd((float) 2);
tb1.setI(1);
tb1.setId(2);
tb1.setL(1l);
tb1.setLd(2l);
tb1.setF(1.1f);
tb1.setFd(2.1f);
tb1.setD(1.1d);
tb1.setDd(2.1d);
tb1.setBo(true);
tb1.setBod(false);
tb1.setC('a');
tb1.setCd('b');
tb1.setS("a");
tbList.add(tb1);
try {
write(tbList, "C:\\Users\\110011\\Desktop\\testBean.xls");
System.out.println("写入完成!!!");
List<Object> objects = read("C:\\Users\\110011\\Desktop\\testBean.xls", TestBean.class);
for (Object object : objects) {
TestBean ms = (TestBean) object;
System.out.println(ms);
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 读取Excel 到对象
* @param path excel 路径
* @param clazz 对象class
* @return
* @throws IllegalArgumentException
* @throws InvocationTargetException
* @throws NoSuchMethodException
* @throws SecurityException
* @throws Exception
*/
public static List<Object> read(String path, Class<?> clazz) throws IllegalArgumentException,
InvocationTargetException, NoSuchMethodException, SecurityException, Exception {
List<Object> list = new ArrayList<Object>();
// 读取.xls文件
Workbook workbook = new HSSFWorkbook(new FileInputStream(new File(path)));
Sheet sheet = workbook.getSheetAt(0);
String[] titles = null;
Field[] fields = clazz.getDeclaredFields();
Object obj = null;
// 行
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
if (i == 0) {
titles = new String[row.getLastCellNum()];
} else {
obj = clazz.newInstance();
}
// 列
for (int j = 0; j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
// i等于0的时候获取title
if (i == 0) {
titles[j] = (String) getValue(cell);
} else {
Type type = null;
for (Field field : fields) {
if (field.getName().equals(titles[j])) {
type = field.getGenericType();
}
}
clazz.getDeclaredMethod("set" + capitalized(titles[j]), getType(type)).invoke(obj,
conversionType(getValue(cell), type));
}
}
if (i != 0) {
list.add(obj);
}
}
return list;
}
/**
* 将类List对象写入Excel文件
*
* @param objs List<T>
* @param path 文件路径
* @throws NoSuchMethodException
* @throws SecurityException
* @throws Exception
*/
public static <T> void write(List<T> objs, String path) throws NoSuchMethodException, SecurityException, Exception {
if (objs != null && objs.size() > 0) {
FileOutputStream fos = null;
HSSFWorkbook workbook = null;
try {
// 创建新工作簿
workbook = new HSSFWorkbook();
// 新建 表名是类名
String className = objs.get(0).getClass().getName();
HSSFSheet sheet = workbook
.createSheet(className.substring(className.lastIndexOf(".") + 1, className.length()));
boolean bool = true;
// 记录行数
int n = 0;
for (int i = 0; i < objs.size(); i++) {
Object obj = objs.get(i);
Class<?> clazz = obj.getClass();
HSSFRow row = sheet.createRow(n);
n++;
// 获取所有的属性
Field[] fields = clazz.getDeclaredFields();
// 第一次进的时候 将excel 第一列设置属性名称
if (i == 0 && bool == true) {
for (int j = 0; j < fields.length; j++) {
Field field = fields[j];
row.createCell(j).setCellValue(field.getName());
}
// 设置完成 i--之后添加数据
i--;
// 执行一次后不再执行。
bool = false;
// 跳过后面代码
continue;
}
// 通过get方法获取数据
for (int j = 0; j < fields.length; j++) {
Field field = fields[j];
row.createCell(j).setCellValue(
setValue(clazz.getDeclaredMethod("get" + capitalized(field.getName())).invoke(obj)));
}
}
// 写入文件
fos = new FileOutputStream(new File(path));
workbook.write(fos);
} finally {
if (workbook != null) {
workbook.close();
}
if (fos != null) {
fos.close();
}
}
}
}
/**
* 获取类型
* @param type
* @return
* @throws ClassNotFoundException
*/
private static Class<?> getType(Type type) throws ClassNotFoundException {
switch (type.getTypeName()) {
case "int":
return int.class;
case "java.lang.Integer":
return Integer.class;
case "byte":
return byte.class;
case "java.lang.Byte":
return Byte.class;
case "short":
return short.class;
case "java.lang.Short":
return Short.class;
case "long":
return long.class;
case "java.lang.Long":
return Long.class;
case "float":
return float.class;
case "java.lang.Float":
return Float.class;
case "double":
return double.class;
case "java.lang.Double":
return Double.class;
case "java.lang.String":
return String.class;
case "boolean":
return boolean.class;
case "java.lang.Boolean":
return Boolean.class;
case "char":
return char.class;
case "java.lang.Character":
return Character.class;
default:
return null;
}
}
/**
* 传入object 判Null 返回字符串
* @param obj
* @return
*/
private static String setValue(Object obj) {
return obj == null ? "" : obj.toString();
}
/**
* 将obj 转换成对应的类型
* @param obj
* @param type
* @return
*/
private static Object conversionType(Object obj, Type type) {
switch (type.getTypeName()) {
case "int":
obj = Integer.valueOf("".equals(obj.toString()) ? "0" : obj.toString());
break;
case "java.lang.Integer":
obj = Integer.valueOf("".equals(obj.toString()) ? "0" : obj.toString());
break;
case "String":
obj = obj.toString();
break;
case "byte":
obj = Byte.valueOf("".equals(obj.toString()) ? "0" : obj.toString());
break;
case "java.lang.Byte":
obj = Byte.valueOf("".equals(obj.toString()) ? "0" : obj.toString());
break;
case "short":
obj = Short.valueOf("".equals(obj.toString()) ? "0" : obj.toString());
break;
case "java.lang.Short":
obj = Short.valueOf("".equals(obj.toString()) ? "0" : obj.toString());
break;
case "long":
obj = Long.valueOf("".equals(obj.toString()) ? "0" : obj.toString());
break;
case "java.lang.Long":
obj = Long.valueOf("".equals(obj.toString()) ? "0" : obj.toString());
break;
case "float":
obj = Float.valueOf("".equals(obj.toString()) ? "0" : obj.toString());
break;
case "java.lang.Float":
obj = Float.valueOf("".equals(obj.toString()) ? "0" : obj.toString());
break;
case "double":
obj = Double.valueOf("".equals(obj.toString()) ? "0" : obj.toString());
break;
case "java.lang.Double":
obj = Double.valueOf("".equals(obj.toString()) ? "0" : obj.toString());
break;
case "boolean":
obj = Boolean.valueOf("".equals(obj.toString()) ? null : obj.toString());
break;
case "java.lang.Boolean":
obj = Boolean.valueOf("".equals(obj.toString()) ? null : obj.toString());
break;
case "char":
obj = Character.valueOf(obj.toString().charAt(0));
break;
case "java.lang.Character":
obj = Character.valueOf(obj.toString().charAt(0));
break;
}
return obj;
}
/**
* 获取Cell值
* @param cell
* @return
*/
private static Object getValue(Cell cell) {
Object obj = null;
switch (cell.getCellTypeEnum()) {
case BOOLEAN:
obj = cell.getBooleanCellValue();
break;
case ERROR:
obj = cell.getErrorCellValue();
break;
case NUMERIC:
obj = cell.getNumericCellValue();
break;
case STRING:
obj = cell.getStringCellValue();
break;
default:
break;
}
return obj;
}
/**
* 字符串首字母大写
* @param fildeName
* @return
* @throws Exception
*/
public static String capitalized(String fildeName) throws Exception {
byte[] items = fildeName.getBytes();
items[0] = (byte) ((char) items[0] - 'a' + 'A');
return new String(items);
}
}