1 package test.excelTest;
2
3 import java.io.File;
4 import java.io.FileInputStream;
5 import java.lang.reflect.Field;
6 import java.text.NumberFormat;
7 import java.util.ArrayList;
8 import java.util.List;
9
10 import org.apache.commons.lang.StringUtils;
11 import org.apache.poi.hssf.usermodel.HSSFCell;
12 import org.apache.poi.hssf.usermodel.HSSFRow;
13 import org.apache.poi.hssf.usermodel.HSSFSheet;
14 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
15 import org.apache.poi.ss.usermodel.Cell;
16 import org.apache.poi.ss.usermodel.Row;
17 import org.apache.poi.ss.usermodel.Sheet;
18 import org.apache.poi.ss.usermodel.Workbook;
19 import org.apache.poi.xssf.usermodel.XSSFCell;
20 import org.apache.poi.xssf.usermodel.XSSFRow;
21 import org.apache.poi.xssf.usermodel.XSSFSheet;
22 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
23 import org.slf4j.Logger;
24 import org.slf4j.LoggerFactory;
25
26 /**
27 * Desc：文件工具类
28 */
29 public class FileUtil {
30 private static Logger logger = LoggerFactory
31 .getLogger(FileUtil.class);
32
33 /**
34 * 读取xml
35 * @param classz
36 * @param fileSrc
37 * @return
38 */
39 public static Object readExcel(Class classz,String fileSrc){
40 List list = new ArrayList<>();
41 int x = 0;//记录出错位置
42 int y = 0;
43
44 try {
45 Workbook wb = null;
46 if (isExcel2003(fileSrc)){
47 wb = new HSSFWorkbook(new FileInputStream(new File(fileSrc)));
48 }else if(isExcel2007(fileSrc)){
49 wb = new XSSFWorkbook(new FileInputStream(new File(fileSrc)));
50 }
51 Sheet sheet = wb.getSheetAt(0);
52
53 if(sheet.getLastRowNum() < 1){
54 return list;
55 }
56
57 Row headRow = sheet.getRow(0);
58
59 for (int i = 1; i < sheet.getLastRowNum() + 1 ; i++){
60 x = i;
61 Row row = sheet.getRow(i);
62 Object t = classz.newInstance();
63
64 for(int j = 0; j < row.getLastCellNum(); j++){
65 y = j;
66 Cell cellHeadFiled = headRow.getCell(j);
67 Cell cellFiled = row.getCell(j);
68 if(cellHeadFiled == null || cellFiled == null){
69 continue;
70 }
71
72 String cellFiledName = null;
73 if(cellHeadFiled != null){
74 cellFiledName = cellHeadFiled.getRichStringCellValue().getString();
75 }
76
77 cellFiled.setCellType(Cell.CELL_TYPE_STRING);
78 String cellFiledValue = String.valueOf(cellFiled.getRichStringCellValue());
79
80 setFiledValue(t, cellFiledName, cellFiledValue, x, y);
81 }
82
83 list.add(t);
84 }
85 } catch (Exception e) {
86 e.printStackTrace();
87 logger.error("解析excel异常。请检查文件内容是否正确,第"+y+"行,"+(x+1)+"列出错");;
88 }
89
90 return list;
91 }
92
93 /**
94 * 设置字段值
95 * @param object
96 * @param excelFiledName
97 * @param value
98 */
99 private static void setFiledValue(Object object,String excelFiledName,String value, int x, int y){
100 Class classz = object.getClass();
101 Field[] fields = classz.getDeclaredFields();
102
103 for(Field field : fields){
104 String filedName = field.getName();
105 ExcelField excelField = field.getAnnotation(ExcelField.class);
106 if(excelField != null){
107 filedName = excelField.fieldName();
108 }
109
110 String orgFiledName = field.getType().getName();
111 String filedTypeName = orgFiledName.toUpperCase();
112
113 if(excelFiledName.equalsIgnoreCase(filedName)){
114 field.setAccessible(true);
115 try {
116 if(isNumeric(value)){
117 NumberFormat numberFormat = NumberFormat.getNumberInstance();
118 Number number = numberFormat.parse(value);
119
120 if(filedTypeName.contains("INT")){
121 field.set(object, number.intValue());
122 }else if(filedTypeName.contains("DOUBLE")){
123 field.set(object, number.doubleValue());
124 }else if(filedTypeName.contains("FLOAT")){
125 field.set(object, number.floatValue());
126 }else if(filedTypeName.contains("LONG")){
127 field.set(object, number.longValue());
128 }else if(filedTypeName.contains("SHORT")){
129 field.set(object, number.shortValue());
130 }
131 }else {
132 if(filedTypeName.contains("BOOLEAN")){
133 field.set(object,Boolean.valueOf(value));
134 }else{
135 field.set(object,value);
136 }
137 }
138
139 } catch (Exception e) {
140 e.printStackTrace();
141 logger.error("暂不支持的数据类型["+orgFiledName+"]。请检查文件内容是否正确,第"+y+"行,"+(x+1)+"列出错");
142 }
143 break;
144 }
145 }
146 }
147
148 /**
149 * 判断字符串是否为数字
150 * @param str
151 * @return
152 */
153 public static boolean isNumeric(String str){
154 if(StringUtils.isEmpty(str)){
155 return false;
156 }
157
158 for (int i = str.length();--i>=0;){
159 if (!Character.isDigit(str.charAt(i))){
160 return false;
161 }
162 }
163 return true;
164 }
165
166 public static boolean isExcel2003(String filePath)
167 {
168
169 return filePath.matches("^.+\\.(?i)(xls)$");
170
171 }
172 public static boolean isExcel2007(String filePath)
173 {
174
175 return filePath.matches("^.+\\.(?i)(xlsx)$");
176
177 }
178
179 public static void main(String[] args) throws Exception {
180 String sre = "D://b.xls";
181 Object result = readExcel(Person.class, sre);
182 List<Person> list = (List<Person>) result;
183 for(Person person : list){
184 System.out.println("姓名:"+person.getName()+"---年龄:"+person.getAge()+"---性别:"+person.getSex());
185 }
186
187 }
188
189 }
1 package test.excelTest;
2
3 import java.lang.annotation.*;
4
5 /**
6 * Desc：Excel字段名
7 */
8 @Target(ElementType.FIELD)
9 @Retention(RetentionPolicy.RUNTIME)
10 @Documented
11 public @interface ExcelField {
12
13 public String fieldName();
14 }
1 package test.excelTest;
2
3 /**
4 * Desc： module
5 */
6 public class Person {
7
8 @ExcelField(fieldName="名字")
9 private String name;
10
11 @ExcelField(fieldName="年龄")
12 private double age;
13
14 @ExcelField(fieldName="性别")
15 private String sex;
16
17 public Person(){
18
19 }
20
21 public String getName() {
22 return name;
23 }
24
25 public void setName(String name) {
26 this.name = name;
27 }
28
29 public double getAge() {
30 return age;
31 }
32
33 public void setAge(double age) {
34 this.age = age;
35 }
36
37 public String getSex() {
38 return sex;
39 }
40
41 public void setSex(String sex) {
42 this.sex = sex;
43 }
44 }
