基于 Java 泛型和反射 导入 Excel

1，数据迁移，存量备份等功能会有Excel批量导入。如果针对每个excel都做相应的POI解析，并调用javabean的set方法生成事例无疑，将会浪费大量工作量。

本篇文章基于Java泛型和反射技术，封装POI，对excel解析，生成javabean。SpringBoot做TDD

Maven Dependency

<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.3.RELEASE</version>
</parent>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-autoconfigure</artifactId>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.0.3</version>
</dependency>

直接搞代码

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Type;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

@SuppressWarnings(“hiding”)
public class ExcelUtil2 {

private Workbook workbook;
public List<T> importExcel(String sheetName, File f, Class<T> item) {
List<T> list = null;
try {
if(f.getName().endsWith("xlsx")){
workbook = new XSSFWorkbook(new FileInputStream(f));
}else{
workbook = new HSSFWorkbook(new FileInputStream(f));
}
Sheet sheet = workbook.getSheet(sheetName);
if (!"".equals(sheetName.trim())) {
sheet = workbook.getSheet(sheetName);// 如果指定sheet名,则取指定sheet中的内容.
}
if (sheet == null) {
sheet = workbook.getSheetAt(0); // 如果传入的sheet名不存在则默认指向第1个sheet.
}
// 获取数据
list = dispatch(sheet, item);
} catch (IOException e) {
e.printStackTrace();
}
return list;
}
/** 生成实体类的 List<T>
*
由于采取反射技术，所以要传入 实体类的类型 ,例如Entity.class
*
*/
public List<T> dispatch(Sheet sheet, Class<T> clazz) {
List<T> instances = new ArrayList<>();
List<Map<String, String>> sheetValue = parseExcelSheet(sheet);
for (int i = 0; i < sheetValue.size(); i++) {
Map<String, String> map = sheetValue.get(i);
Field[] fields = clazz.getDeclaredFields();
try {
T t2 = clazz.newInstance();
for (Field field : fields) {
field.setAccessible(true);
Type type = field.getType();
field.set(t2, getTypeValue(map.get(field.getName()), type.getTypeName()));
}
instances.add(t2);
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (SecurityException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
}
}
return instances;
}
//赋值判断类型
public Object getTypeValue(String value, String type) {
Object obj = new Object();
if ("int".equals(type) || type.indexOf("Integer") > -1) {
obj = Integer.valueOf(value);
} else if ("short".equals(type) || type.indexOf("Short") > -1) {
obj = Short.valueOf(value);
} else if ("long".equals(type) || type.indexOf("Long") > -1) {
obj = Long.valueOf(value);
} else if ("float".equals(type) || type.indexOf("Float") > -1) {
obj = Float.valueOf(value);
} else if ("double".equals(type) || type.indexOf("Double") > -1) {
obj = Double.valueOf(value);
} else if ("boolean".equals(type) || type.indexOf("Boolean") > -1) {
obj = Boolean.valueOf(value);
} else {
obj = value;
}
return obj;
}
/**
* 解析Excel第一行，并生成以第一行表头为key，每一行的值为value的map
* 例如: excel内容如下
* id
alarm
1
abcd1
2
abcd2
3
abcd3
那么List<Map>就是的内容就是
List: map1( id: 1, alarm: abcd1 ),map2(( id: 2, alarm: abcd2 ).....
*/
public List<Map<String, String>> parseExcelSheet(Sheet sheet) {
List<Map<String, String>> result = new ArrayList<>();
Map<String, String> rowValue = null;
int rows = sheet.getPhysicalNumberOfRows();
String[] headers = getHeaderValue(sheet.getRow(0));
for (int i = 1; i < rows; i++) {
rowValue = new HashMap<>();
Row row = sheet.getRow(i);
for (int kk = 0; kk < headers.length; kk++) {
rowValue.put(headers[kk], String.valueOf(getCellValue(row.getCell(kk))));
}
result.add(rowValue);
}
return result;
}
/** 获取第一行，表头，也就是实体类的字段，支持中英文，及下划线，忽略大小写，但是绝笔不能重复，表头有重复字段则不能解析
**/
private String[] getHeaderValue(Row rowHeader) {
int colNum = rowHeader.getPhysicalNumberOfCells();
String[] headValue = new String[rowHeader.getPhysicalNumberOfCells()];
for (int i = 0; i < colNum; i++) {
headValue[i] = rowHeader.getCell(i).getStringCellValue();
}
return headValue;
}
@SuppressWarnings("deprecation")
public Object getCellValue(Cell cell) {
Object value = null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: // 数字
// 如果为时间格式的内容
if (DateUtil.isCellDateFormatted(cell)) {
// 注：format格式 yyyy-MM-dd hh:mm:ss
// 中小时为12小时制，若要24小时制，则把小h变为H即可，yyyy-MM-dd HH:mm:ss
SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd hh:mm:ss");
value = sdf.format( DateUtil.getJavaDate(cell.getNumericCellValue())).toString();
break;
} else {
value = new DecimalFormat("0").format(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_STRING: // 字符串
value = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN: // Boolean
value = cell.getBooleanCellValue() + "";
break;
case Cell.CELL_TYPE_FORMULA: // 公式
value = cell.getCellFormula() + "";
break;
case Cell.CELL_TYPE_BLANK: // 空值
value = "";
break;
case Cell.CELL_TYPE_ERROR: // 故障
value = "非法字符";
break;
default:
value = "未知类型";
break;
}
return value;
}

}

实体类
public class Alarm implements Serializable {

/**
*/
private int id;
/**
*/
private String alarm;
/**
* 获取 主键
*/
public int getId() {
return id;
}
/**
* 设置 主键
*/
public void setId(int id) {
this.id = id;
}
/**
* 获取
*/
public String getAlarm() {
return alarm;
}
/**
* 设置
*/
public void setAlarm(String alarm) {
this.alarm = alarm;
}
@Override
public String toString() {
return "Alarm [id=" + id + ", alarm=" + alarm + "]";
}

}

测试代码

@Test
public void testImport() {
ExcelUtil2<Alarm> excelUtil = new ExcelUtil2<>() ;
//"alarm"为excel 的 sheet名字，也就是表名，暂时未做批量功能
List<Alarm> acfForms = excelUtil.importExcel("alarm",
new File(path)
, Alarm.class);
for (Alarm a : acfForms) {
System.out.println(a.toString());
}
}

表格
id alarm
1 abcd1
2 abcd2
3 abcd3
4 abcd4
5 abcd5
6 abcd6
7 abcd7

最后

以上就是等待老虎为你收集整理的基于 Java 泛型和反射 导入 Excel的全部内容，希望文章能够帮你解决基于 Java 泛型和反射 导入 Excel所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。