java解析excel--并反射到实体类
丌官盛
今天分享一下使用java解析Excel03版和07版
参考了原文链接
使用maven需要添加两个依赖：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.11-beta1</version>
</dependency>
<!--07-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.11-beta1</version>
</dependency>首先新建一个Excel文件，文件第一行为列名，在程序中忽略，但是列名的顺序必须和bean类中的属性顺序一致，否则反射会出错。
所有的数据类型一定要参考bean属性的方式插入。
直接上代码：
bean类
public class Users {
private String name;
private int age;
private double mm;
private String sex;
@Override
public String toString() {
return "Users [name=" + name + ", age=" + age + ", sex=" + sex
+ ", mm=" + mm + "]";
}
public double getMm() {
return mm;
}
public void setMm(double mm) {
this.mm = mm;
}
public String getName() {
return name;
}
public void setName(String name) {
System.err.println(name);
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
}解析excel类03：
public class XslPaese {
@SuppressWarnings("rawtypes")
public LinkedList<Object> parse(String filePath , Class classObject) throws FileNotFoundException, IOException, IntrospectionException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, InstantiationException{
LinkedList<Object> list = new LinkedList<Object>();
Reflect reflect = new Reflect();
File file = new File(filePath);//获取文件
//解析xsl文件
POIFSFileSystem pfs = new POIFSFileSystem(new FileInputStream(file));
HSSFWorkbook hwb = new HSSFWorkbook(pfs);
HSSFSheet hs = hwb.getSheetAt(0);//获取第0个sheet页的数据
int rowStart = hs.getFirstRowNum();//获取第一行的行数 0
int rowEnd = hs.getLastRowNum();//获取最后一行的行数-1
for(int i = rowStart+1; i <= rowEnd ; i++){//行的遍历
Object obj = classObject.newInstance();
HSSFRow hr = hs.getRow(i);//获取行
if(hr == null){
continue;
}
int cellStart = hr.getFirstCellNum();//获取第一列
int cellEnd = hr.getLastCellNum();//获取列的总数
for(int k = cellStart ; k < cellEnd ; k++ ){//列的遍历
HSSFCell hc = hr.getCell(k);//获取列
if(hc == null || hc.equals("")){
continue;
}
switch(hc.getCellType()){
case HSSFCell.CELL_TYPE_STRING://字符串
obj = reflect.reflect(obj, k, hc.getStringCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN://boolean类型
obj = reflect.reflect(obj, k, hc.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_NUMERIC://数字
obj = reflect.reflect(obj, k, hc.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA://公式
obj = reflect.reflect(obj, k, hc.getCellFormula());
break;
case HSSFCell.CELL_TYPE_BLANK://空值
obj = reflect.reflect(obj, k, null);
break;
case HSSFCell.CELL_TYPE_ERROR://错误
obj = reflect.reflect(obj, k, null);
break;
default:
break;
}
}
list.add(obj);
}
return list;
}
}解析excel类07：
public class XlsxPaese {
@SuppressWarnings("rawtypes")
public LinkedList<Object> parse(String filePath , Class classObject) throws FileNotFoundException, IOException, InvalidFormatException, InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, IntrospectionException{
LinkedList<Object> list = new LinkedList<Object>();
Reflect reflect = new Reflect();
File file = new File(filePath);//获取文件
XSSFWorkbook xw = new XSSFWorkbook(file);
XSSFSheet xs = xw.getSheetAt(0);//获取xsl
int rowStart = xs.getFirstRowNum();//获取第一行的行数 0
int rowEnd = xs.getLastRowNum();//获取最后一行的行数-1
for(int i = rowStart+1; i <= rowEnd ; i++){//行的遍历
Object obj = classObject.newInstance();
XSSFRow hr = xs.getRow(i);//获取行
if(hr == null){
continue;
}
int cellStart = hr.getFirstCellNum();//获取第一列
int cellEnd = hr.getLastCellNum();//获取列的总数
for(int k = cellStart ; k < cellEnd ; k++ ){//列的遍历
XSSFCell hc = hr.getCell(k);//获取列
if(hc == null || hc.equals("")){
continue;
}
switch(hc.getCellType()){
case HSSFCell.CELL_TYPE_STRING://字符串
obj = reflect.reflect(obj, k, hc.getStringCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN://boolean类型
obj = reflect.reflect(obj, k, hc.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_NUMERIC://数字
obj = reflect.reflect(obj, k, hc.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA://公式
obj = reflect.reflect(obj, k, hc.getCellFormula());
break;
case HSSFCell.CELL_TYPE_BLANK://空值
obj = reflect.reflect(obj, k, null);
break;
case HSSFCell.CELL_TYPE_ERROR://错误
obj = reflect.reflect(obj, k, null);
break;
default:
break;
}
}
list.add(obj);
}
return list;
}
}
从上面的代码应该看的出03解析和07解析只是一两个类的区别：
03：
POIFSFileSystem pfs = new POIFSFileSystem(new FileInputStream(file));
HSSFWorkbook hwb = new HSSFWorkbook(pfs);
//获取工作页面
HSSFSheet hs = hwb.getSheetAt(0);//获取第0个sheet页的数据07：
XSSFWorkbook xw = new XSSFWorkbook(file);
XSSFSheet xs = xw.getSheetAt(0);//获取xsl获取行列的类也是有区别的：
03与07其实就是 HSSFRow/HSSFCell改为XSSFRow/XSSFCell而已，其他的逻辑不变
反射类：
public class Reflect {
public Object reflect(Object obj,int paramNumber,Object value) throws InstantiationException, IllegalAccessException, IntrospectionException, IllegalArgumentException, InvocationTargetException{
Field[] fields = obj.getClass().getDeclaredFields();//获取属性名
//返回的是一个参数类型
String type = fields[paramNumber].getGenericType().toString();
//返回的是一个类对象
// Class classType = field.getType();
PropertyDescriptor pd = new PropertyDescriptor(fields[paramNumber].getName(), obj.getClass());
Method setmd = pd.getWriteMethod();//获取某个属性的set方法
if(type.equals("class java.lang.String")){
setmd.invoke(obj, value);//激活
}else if(type.equals("int") || type.equals("class java.lang.Integer")){
String str = value.toString();
if(str.indexOf(".") == -1){
setmd.invoke(obj, value);
}else{
setmd.invoke(obj, Integer.parseInt(str.substring(0, str.indexOf("."))));
}
}else if("double".equals(type) || "class java.lang.Double".equals(type)){
setmd.invoke(obj, value);
}else{
setmd.invoke(obj, value);
}
return obj;
}
}处理类：
为了能方便
public class Handler {
@SuppressWarnings("rawtypes")
public void handle(String filePath,Class clazz) throws FileNotFoundException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, InstantiationException, IOException, IntrospectionException, InvalidFormatException{
String str = filePath.substring(filePath.lastIndexOf(".")+1);
if(str.equals("xls")){
//03
XslPaese xp = new XslPaese();
xp.parse(filePath, clazz);
}else{
//07
XlsxPaese xp = new XlsxPaese();
xp.parse(filePath, clazz);
}
}
}测试类：
public void testApp()
{
Handler hd = new Handler();
try {
hd.handle("C:/Users/seaskylight/Desktop/Book1.xlsx", Users.class);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InvocationTargetException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InvalidFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IntrospectionException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}试试，理解一下，其实这样写还是存在很多的不合理，在实际使用上和目前使用上还是有很大的区别的
类似资料：