package com.gomsws.util;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.File;
import java.io.FileInputStream;
import java.lang.reflect.Field;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.List;
/**
* Package:com.gomsws.util
* Author:tanyong
* Date: 2016/12/1
* Desc:文件工具类
*/
public class FileUtil {
private static Logger logger = LoggerFactory
.getLogger(FileUtil.class);
/**
* 读取xml
* @param classz
* @param fileSrc
* @return
*/
public static List readExcel(Class classz,String fileSrc){
List list = new ArrayList<>();
try {
File file = new File(fileSrc);
FileInputStream fileInputStream = new FileInputStream(file);
XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
XSSFSheet sheet = workbook.getSheetAt(0);
if(sheet.getLastRowNum() < 1){
return list;
}
XSSFRow headRow = sheet.getRow(0);
for (int i = 1; i < sheet.getLastRowNum() + 1 ; i++){
XSSFRow row = sheet.getRow(i);
Object t = classz.newInstance();
for(int j = 0; j < row.getLastCellNum(); j++){
XSSFCell cellHeadFiled = headRow.getCell(j);
XSSFCell cellFiled = row.getCell(j);
if(cellHeadFiled == null || cellFiled == null){
continue;
}
String cellFiledName = null;
if(cellHeadFiled != null){
cellFiledName = cellHeadFiled.getRichStringCellValue().getString();
}
cellFiled.setCellType(Cell.CELL_TYPE_STRING);
String cellFiledValue = String.valueOf(cellFiled.getRichStringCellValue());
setFiledValue(t, cellFiledName, cellFiledValue);
}
list.add(t);
}
} catch (Exception e) {
e.printStackTrace();
logger.error("解析excel异常");
}
return list;
}
/**
* 设置字段值
* @param object
* @param excelFiledName
* @param value
*/
private static void setFiledValue(Object object,String excelFiledName,String value){
Class classz = object.getClass();
Field[] fields = classz.getDeclaredFields();
for(Field field : fields){
String filedName = field.getName();
ExcelField excelField = field.getAnnotation(ExcelField.class);
if(excelField != null){
filedName = excelField.fieldName();
}
String orgFiledName = field.getType().getName();
String filedTypeName = orgFiledName.toUpperCase();
if(excelFiledName.equalsIgnoreCase(filedName)){
field.setAccessible(true);
try {
if(isNumeric(value)){
NumberFormat numberFormat = NumberFormat.getNumberInstance();
Number number = numberFormat.parse(value);
if(filedTypeName.contains("INT")){
field.set(object, number.intValue());
}else if(filedTypeName.contains("DOUBLE")){
field.set(object, number.doubleValue());
}else if(filedTypeName.contains("FLOAT")){
field.set(object, number.floatValue());
}else if(filedTypeName.contains("LONG")){
field.set(object, number.longValue());
}else if(filedTypeName.contains("SHORT")){
field.set(object, number.shortValue());
}
}else {
if(filedTypeName.contains("BOOLEAN")){
field.set(object,Boolean.valueOf(value));
}else{
field.set(object,value);
}
}
} catch (Exception e) {
e.printStackTrace();
logger.error("暂不支持的数据类型["+orgFiledName+"]");
}
break;
}
}
}
/**
* 判断字符串是否为数字
* @param str
* @return
*/
public static boolean isNumeric(String str){
if(StringUtils.isEmpty(str)){
return false;
}
for (int i = str.length();--i>=0;){
if (!Character.isDigit(str.charAt(i))){
return false;
}
}
return true;
}
public static void main(String[] args) {
String sre = "D://b.csv";
List<Person> persons = readExcel(Person.class, sre);
for(Person person : persons){
System.out.println("姓名:"+person.getName()+"---年龄:"+person.getAge()+"---性别:"+person.getSex());
}
}
}
package com.gomsws.util;
import java.lang.annotation.*;
/**
* Package:com.gomsws.util
* Author:tanyong
* Date: 2016/12/2
* Desc:Excel字段名
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelField {
public String fieldName();
}
package com.gomsws.util;
/**
* Package:com.gomsws.util
* Author:tanyong
* Date: 2016/12/2
* Desc:
*/
public class Person {
@ExcelField(fieldName="名字")
private String name;
private double age;
private String sex;
public Person(){
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getAge() {
return age;
}
public void setAge(double age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
}