1.工作类代码
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.beans.PropertyDescriptor;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
public class ImportExcelByReflect {
/**
* getDatasByReflect 使用反射动态获取Excel数据并保存至数据库
* @param beginRow 开始读取的行
* @param beanpros 需要导入的实体名和对应的EXCEL下标
* @param classPathName 需要反射的实体对象(必须要有set 和 get 方法)
* @param inputStream 从spring获取的文件流
* @param sheetIndex 需要读取的sheet下标
* @param fileName 文件的名称,用于判断使用哪种方式读取
* @return List<Object> 返回一个读取的对象结果集
*/
public static List<Object> getDatasByReflect(int beginRow, Map<Integer,String> beanpros,
String classPathName, InputStream inputStream,
int sheetIndex,String fileName){
List<Object> results = null;
Workbook workbook = null;
// 获取Excel后缀名
String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
//选择读取方式
try {
if (fileType.equalsIgnoreCase("XLS")) {
workbook = new HSSFWorkbook(inputStream);
} else if (fileType.equalsIgnoreCase("XLSX")) {
workbook = new XSSFWorkbook(inputStream);
}
} catch (IOException e) {
e.printStackTrace();
}
//读取那个sheet页
Sheet sheet = workbook.getSheetAt(sheetIndex);
try {
results = new ArrayList<Object>();
//获取class的属性
Class clazz = Class.forName(classPathName);
//
Set<Integer> set = beanpros.keySet();
//从第几行开始读取
Row row = sheet.getRow(beginRow);
//获取的行数据如果不是为空,则开始读取
while(row!=null) {
//实例化一个对象
Object obj = clazz.newInstance() ;
//循环查询出来的字段
for(Integer key:set){
String value = "" ;
if(row!=null){
//根据循环的下标获取cell值
Cell cell = row.getCell(key) ;
//如果获取的cell不是为空
if(cell!=null){
//获取cell的数据类型
value = convertCellValueToString(workbook,cell);
}
}
//获取bean的某个属性的描述符(这里是使用下标进行描述的)
PropertyDescriptor pd = new PropertyDescriptor(beanpros.get(key),clazz);
//获取用于写入的属性方法
Method method = pd.getWriteMethod();
//获取方法参数类型
Class<?>[] cla = method.getParameterTypes();
/*System.out.println("ssss:"+cla[0]);*/
//写入属性值
method.invoke(obj, new Object[]{getClassTypeValue(cla[0], value)});
/*method.invoke(obj, value);*/
}
//把写入完的对象添加到集合中
results.add(obj);
//多行连续读取
row = sheet.getRow(++beginRow);
}
} catch (Exception e) {
e.printStackTrace();
return null;
}
return results ;
}
/**
* convertCellValueToString 转换单元格参数类型
* @param workbook
* @param cell 单元格值
* @return
*/
public static String convertCellValueToString(Workbook workbook, Cell cell) {
//判断是否为null或空串
if (cell==null || cell.toString().trim().equals("")) {
return "";
}
String cellValue = "";
int type = cell.getCellType() ;
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
if (type == Cell.CELL_TYPE_FORMULA) { // 表达式类型
type = evaluator.evaluate(cell).getCellType();
}
switch (type) {
// 数字
case Cell.CELL_TYPE_NUMERIC:
short format = cell.getCellStyle().getDataFormat();
if (DateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = null;
//System.out.println("cell.getCellStyle().getDataFormat()="+cell.getCellStyle().getDataFormat());
if (format == 20 || format == 32) {
sdf = new SimpleDateFormat("HH:mm");
} else if (format == 14 || format == 31 || format == 57 || format == 58) {
// 处理自定义日期格式:m月d日(通过判断单元格的格式id解决,id的值是58)
sdf = new SimpleDateFormat("yyyy-MM-dd");
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil
.getJavaDate(value);
cellValue = sdf.format(date);
}else {// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
}
try {
cellValue = sdf.format(cell.getDateCellValue());// 日期
} catch (Exception e) {
try {
throw new Exception("exception on get date data !".concat(e.toString()));
} catch (Exception e1) {
e1.printStackTrace();
}
}finally{
sdf = null;
}
} else {
BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
cellValue = bd.toPlainString();// 数值 这种用BigDecimal包装再获取plainString,可以防止获取到科学计数值
}
break;
case Cell.CELL_TYPE_STRING: // 字符串
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN: // Boolean
cellValue = cell.getBooleanCellValue()+"";;
break;
case Cell.CELL_TYPE_FORMULA: // 公式
cellValue = cell.getCellFormula();
break;
case Cell.CELL_TYPE_BLANK: // 空值
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR: // 故障
cellValue = "ERROR VALUE";
break;
default:
cellValue = "UNKNOW VALUE";
break;
}
return cellValue;
}
/**
* getClassTypeValue 转换方法类型的参数
* @param typeClass 方法参数的类型
* @param value
* @return Object 返回一个对象
*/
private static Object getClassTypeValue(Class<?> typeClass, String value) throws ParseException {
if(typeClass == String.class){
return String.valueOf(value);
}else if (typeClass == Integer.class) {
if (value.equals("")) {
return 0;
}
value = value.replace(".0", "");
return Integer.parseInt(value);
} else if (typeClass == Date.class) {
Date date= null;
if(value!=null&&!value.equals("")){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
date = sdf.parse(value);
}
return date;
} else if (typeClass == Short.class) {
if (value.equals("")) {
return 0;
}
return Short.parseShort(value);
} else if (typeClass == Byte.class) {
if (value.equals("")) {
return 0;
}
return Byte.parseByte(value);
} else if (typeClass == Double.class) {
if (value.equals("")) {
return 0;
}
return Double.parseDouble(value);
} else if (typeClass == Boolean.class) {
if (value.equals("")) {
return false;
}
return Boolean.parseBoolean(value);
} else if (typeClass == Float.class) {
if (value.equals("")) {
return 0;
}
return Float.parseFloat(value);
} else if (typeClass == Long.class) {
if (value.equals("")) {
return 0;
}
return Long.parseLong(value);
} else {
return typeClass.cast(value);
}
}
}
2.测试类
public static final char UNDERLINE_CHAR = '_';
@Test
public void test222(){
try {
InputStream inputStream = new FileInputStream(new File("D:/入境人员快速填报.xlsx"));
Map<Integer,String> beanpros = new HashMap<Integer, String>();
beanpros.put(0, underline2Camel("CHAR10_S01"));
beanpros.put(1, underline2Camel("CHAR100_S01"));
List<Object> results = ImportExcelByReflect.getDatasByReflect(1, beanpros, "com.modules.metadata.entity.Metadata"
,inputStream,0,"入境人员快速填报.xlsx");
if(results!=null){
for(Object obj:results){
Metadata bean = (Metadata) obj;
System.out.println("编号:"+bean.getChar10S01()+"姓名:"+bean.getChar100S01());
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 下划线转驼峰
*
* @param underlineStr
* @return
*/
public static String underline2Camel(String underlineStr) {
if (StringUtils.isEmpty(underlineStr)) {
return StringUtils.EMPTY;
}
int len = underlineStr.length();
StringBuilder strb = new StringBuilder(len);
for (int i = 0; i < len; i++) {
char c = underlineStr.charAt(i);
if(i==0){
char a = underlineStr.charAt(i);
String x = String.valueOf(a);
strb.append(x.toUpperCase());
}else if(c == UNDERLINE_CHAR && (++i) < len){
char cc = underlineStr.charAt(i);
String xx = String.valueOf(cc);
strb.append(xx.toUpperCase());
}else{
char last = underlineStr.charAt(i);
String over = String.valueOf(last);
strb.append(over.toLowerCase());
}
}
return strb.toString();
}