这个例子中用了反射,泛型实现了通用的excel读写方法。
我们常常遇到要把一个类class写入excel的时候,有时候class的字段非常多,我们不可能一个个的去get写入excel。这里写了一个通用方法。只要传入数据的list和类型,就能很方便的把数据写入excel,也能很方便的读取出来。
例子下载:
java通过class读写excel的例子
首先要引用这两个包
jxl.jar
poi-3.9-20121203.jar
相关代码如下:
package Io;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Locale;
import java.util.Map;
public class BeanRefUtil {
/**
* 取Bean的属性和值对应关系的MAP
*
* @param bean
* @return Map
*/
public static Map<String, String> getFieldValueMap(Object bean) {
Class<?> cls = bean.getClass();
Map<String, String> valueMap = new HashMap<String, String>();
Method[] methods = cls.getDeclaredMethods();
Field[] fields = cls.getDeclaredFields();
for (Field field : fields) {
try {
String fieldType = field.getType().getSimpleName();
String fieldGetName = parGetName(field.getName());
if (!checkGetMet(methods, fieldGetName)) {
continue;
}
Method fieldGetMet = cls.getMethod(fieldGetName, new Class[] {});
Object fieldVal = fieldGetMet.invoke(bean, new Object[] {});
String result = null;
if ("Date".equals(fieldType)) {
result = fmtDate((Date) fieldVal);
} else {
if (null != fieldVal) {
result = String.valueOf(fieldVal);
}
}
// String fieldKeyName = parKeyName(field.getName());
valueMap.put(field.getName(), result);
} catch (Exception e) {
continue;
}
}
return valueMap;
}
/**
* set属性的值到Bean
*
* @param bean
* @param valMap
*/
public static void setFieldValue(Object bean, Map<String, String> valMap) {
Class<?> cls = bean.getClass();
// 取出bean里的所有方法
Method[] methods = cls.getDeclaredMethods();
Field[] fields = cls.getDeclaredFields();
for (Field field : fields) {
try {
String fieldSetName = parSetName(field.getName());
if (!checkSetMet(methods, fieldSetName)) {
continue;
}
Method fieldSetMet = cls.getMethod(fieldSetName,
field.getType());
// String fieldKeyName = parKeyName(field.getName());
String fieldKeyName = field.getName();
String value = valMap.get(fieldKeyName);
if (null != value && !"".equals(value)) {
String fieldType = field.getType().getSimpleName();
if ("String".equals(fieldType)) {
fieldSetMet.invoke(bean, value);
} else if ("Date".equals(fieldType)) {
Date temp = parseDate(value);
fieldSetMet.invoke(bean, temp);
} else if ("Integer".equals(fieldType)
|| "int".equals(fieldType)) {
Integer intval = Integer.parseInt(value);
fieldSetMet.invoke(bean, intval);
} else if ("Long".equalsIgnoreCase(fieldType)) {
Long temp = Long.parseLong(value);
fieldSetMet.invoke(bean, temp);
} else if ("Double".equalsIgnoreCase(fieldType)) {
Double temp = Double.parseDouble(value);
fieldSetMet.invoke(bean, temp);
} else if ("Boolean".equalsIgnoreCase(fieldType)) {
Boolean temp = Boolean.parseBoolean(value);
fieldSetMet.invoke(bean, temp);
} else {
System.out.println("not supper type" + fieldType);
}
}
} catch (Exception e) {
continue;
}
}
}
/**
* 格式化string为Date
*
* @param datestr
* @return date
*/
public static Date parseDate(String datestr) {
if (null == datestr || "".equals(datestr)) {
return null;
}
try {
String fmtstr = null;
if (datestr.indexOf(':') > 0) {
fmtstr = "yyyy-MM-dd HH:mm:ss";
} else {
fmtstr = "yyyy-MM-dd";
}
SimpleDateFormat sdf = new SimpleDateFormat(fmtstr, Locale.UK);
return sdf.parse(datestr);
} catch (Exception e) {
return null;
}
}
/**
* 日期转化为String
*
* @param date
* @return date string
*/
public static String fmtDate(Date date) {
if (null == date) {
return null;
}
try {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss",
Locale.US);
return sdf.format(date);
} catch (Exception e) {
return null;
}
}
/**
* 判断是否存在某属性的 set方法
*
* @param methods
* @param fieldSetMet
* @return boolean
*/
public static boolean checkSetMet(Method[] methods, String fieldSetMet) {
for (Method met : methods) {
if (fieldSetMet.equals(met.getName())) {
return true;
}
}
return false;
}
/**
* 判断是否存在某属性的 get方法
*
* @param methods
* @param fieldGetMet
* @return boolean
*/
public static boolean checkGetMet(Method[] methods, String fieldGetMet) {
for (Method met : methods) {
if (fieldGetMet.equals(met.getName())) {
return true;
}
}
return false;
}
/**
* 拼接某属性的 get方法
*
* @param fieldName
* @return String
*/
public static String parGetName(String fieldName) {
if (null == fieldName || "".equals(fieldName)) {
return null;
}
int startIndex = 0;
if (fieldName.charAt(0) == '_')
startIndex = 1;
return "get"
+ fieldName.substring(startIndex, startIndex + 1).toUpperCase()
+ fieldName.substring(startIndex + 1);
}
/**
* 拼接在某属性的 set方法
*
* @param fieldName
* @return String
*/
public static String parSetName(String fieldName) {
if (null == fieldName || "".equals(fieldName)) {
return null;
}
int startIndex = 0;
if (fieldName.charAt(0) == '_')
startIndex = 1;
return "set"
+ fieldName.substring(startIndex, startIndex + 1).toUpperCase()
+ fieldName.substring(startIndex + 1);
}
/**
* 获取存储的键名称(调用parGetName)
*
* @param fieldName
* @return 去掉开头的get
*/
public static String parKeyName(String fieldName) {
String fieldGetName = parGetName(fieldName);
if (fieldGetName != null && fieldGetName.trim() != ""
&& fieldGetName.length() > 3) {
return fieldGetName.substring(3);
}
return fieldGetName;
}
}
package Io;
public class User {
private String name;
private String password;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
package Io;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;
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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
/**
* EXCEL IO操作
*
* @作者 joe
* @版本 V1.0
* @更新时间 2014-4-18 下午4:28:01
*/
public class ExcelIo {
private static <T> String[][] parseLand(List<T> objects,Class<T> clazz) {
int size = objects.size();
Field[] fields = clazz.getDeclaredFields();
String[][] content = new String[size + 1][fields.length];
for (int i = 0; i < fields.length; i++) {
Field f = fields[i];
content[0][i] = f.getName();
}
for (int i = 0 ; i < size; i++) {
T school = objects.get(i);
for (int j = 0; j < fields.length; j++) {
Field f = fields[j];
f.setAccessible(true);
try {
String value = "";
if (f.get(school) != null) {
value = f.get(school).toString();
}
content[i+1][j] = value;
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
}
return content;
}
public static String writeFile(String fileName, String[][] content) {
WritableWorkbook wwb = null;
String filePath = "c:\\" + fileName +".xls";
try {
wwb = Workbook.createWorkbook(new File(filePath));
} catch (IOException e) {
e.printStackTrace();
}
if (wwb != null) {
WritableSheet ws = wwb.createSheet(fileName, 1);
for (int row = 0; row < content.length; row++) {
for (int j = 0; j < content[row].length; j++) {
Label labelC = new Label(j, row, content[row][j]);
try {
ws.addCell(labelC);
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
}
try {
wwb.write();
wwb.close();
return filePath;
} catch (IOException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
return null;
}
public static <T> List<T> excelToClass(File file,Class<T> clazz){
List<T> lands = new ArrayList<T>();
try {
FileInputStream fileInputStream = new FileInputStream(file);
POIFSFileSystem poifs = new POIFSFileSystem(fileInputStream);
HSSFWorkbook workbook = new HSSFWorkbook(poifs);
HSSFSheet sheet = workbook.getSheetAt(0);
Iterator<Row> rows = sheet.rowIterator();
int index = 0;
List<String> keys = new ArrayList<String>();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
if(index == 0){
// int num = row.getRowNum();
int num = row.getLastCellNum();
for(int i = 0 ; i < num;i++ ){
HSSFCell cell = row.getCell(i);
if(cell!=null){
String value = getStringCellValue(cell);
keys.add(value);
}
}
}
if(index>=1){ //决定从哪一行开始提取,这里从第一行
Map<String,String> map = new HashMap<String, String>();
int num = row.getLastCellNum();
for(int i = 0 ; i < num;i++ ){
HSSFCell cell = row.getCell(i);
if(cell!=null){
map.put(keys.get(i), getStringCellValue(cell));
}
}
try {
T land = newTclass(clazz);
BeanRefUtil.setFieldValue(land, map);
lands.add(land);
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
index++;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return lands;
}
private static <T> T newTclass(Class<T> clazz) throws InstantiationException, IllegalAccessException{
T a=clazz.newInstance();
return a;
}
private static String getStringCellValue(HSSFCell cell) {// 获取单元格数据内容为字符串类型的数据
String strCell = "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
strCell = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
strCell = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
strCell = "";
break;
default:
strCell = "";
break;
}
if (strCell.equals("") || strCell == null) {
return "";
}
return strCell;
}
private static String getUUID() {
UUID uuid = UUID.randomUUID();
String str = uuid.toString();
return str.substring(0, 8);
}
public static void main(String[] args) {
//写入测试
List<User> users=new ArrayList<User>();
User u=new User();
u.setName("joe");
u.setPassword("123");
users.add(u);
User s=new User();
s.setName("sophia");
s.setPassword("123456");
users.add(s);
String[][] content = parseLand(users,User.class);
String uuid = getUUID();
writeFile(uuid, content);
System.out.println("写入完成");
// //读取测试
User temp=new User();
File file = new File("c:\\" + uuid +".xls");
List<User> us=excelToClass(file,User.class);
for(User i:us)
{
System.out.println(i.getName());
System.out.println(i.getPassword());
}
System.out.println("读取完成");
}
}
结果: