1 定义注解
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelAnnotation {
String headName();
int order();
String datePattern() default "yyyyMMdd HH:mm:ss";
enum DataType {
String,Number,Date,
}
/**
* 数据类型,可以是String,Number(数字型),Date等类型
* @return
*/
DataType type() default DataType.String;
}
2 编写工具类
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.poi.ss.usermodel.*;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.util.*;
public class ExcelUtils {
private static ExcelUtils instance;
private ExcelUtils() {
}
/**
* 单例模式
*
* @return
*/
public static ExcelUtils getInstance() {
if (instance == null) {
instance = new ExcelUtils();
}
return instance;
}
/**
* excel的导入
*
* @param inputStream
* @param clazz
* @return
* @throws IOException
* @throws InstantiationException
* @throws IllegalAccessException
* @throws InvocationTargetException
* @throws NoSuchMethodException
*/
public static List<?> importExcel(InputStream inputStream, Class<?> clazz)
throws IOException, InstantiationException, IllegalAccessException, InvocationTargetException, NoSuchMethodException {
Workbook workbook = WorkbookFactory.create(inputStream);
Sheet sheet = workbook.getSheetAt(0);
Row titleCell = sheet.getRow(0);
List<Object> dataList = new ArrayList<>(sheet.getLastRowNum());
Object datum;
Map<String, Field> fieldMap = getFieldMap(clazz);
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
datum = clazz.newInstance();
int minCell = row.getFirstCellNum();
int maxCell = row.getLastCellNum();
for (int cellNum = minCell; cellNum <= maxCell; cellNum++) {
Cell title = titleCell.getCell(cellNum);
if (title == null) {
continue;
}
String tag = title.getStringCellValue();
Field field = fieldMap.get(tag);
if (field == null) {
continue;
}
Class<?> type = field.getType();
Object value = null;
Cell cell = row.getCell(cellNum);
if (cell == null) {
continue;
}
if (type.equals(Date.class)) {
value = cell.getDateCellValue();
} else {
value = cell.getStringCellValue();
}
PropertyUtils.setProperty(datum, field.getName(), value);
}
dataList.add(datum);
}
return dataList;
}
/**
* key :headName val:该名称对应的字段
*
* @param clazz
* @param <T>
* @return
*/
private static <T> Map<String, Field> getFieldMap(Class<T> clazz) {
Field[] fields = clazz.getDeclaredFields();
Map<String, Field> fieldMap = new HashMap<>();
for (Field field : fields) {
if (field.isAnnotationPresent(ExcelAnnotation.class)) {
ExcelAnnotation annotation = field.getAnnotation(ExcelAnnotation.class);
fieldMap.put(annotation.headName(), field);
}
}
return fieldMap;
}
}
3 被转换的目标对象
public class Car {
@ExcelAnnotation(headName = "单位名称",order = 1)
private String departmentName;
public String getDepartmentName() {
return departmentName;
}
public void setDepartmentName(String departmentName) {
this.departmentName = departmentName;
}
@Override
public String toString() {
return "Car{" +
"departmentName='" + departmentName + '\'' +
'}';
}
}
4 Main方法
public static void main(String[] args) throws Exception{
String filepath = "C:\\Users\\chenhao86\\Desktop\\car.xls";
File file = new File(filepath);
List<?> objects = ExcelUtils.importExcel(new FileInputStream(file), Car.class);
System.out.println(objects);
}
转换成List后就可以遍历并存入数据库了