第一次发博客,有点不怎么会写,注释都打上了,自己也是刚入坑的小白,有什么问题希望大家指出来。抱拳了!
话不多说,不要积分。开源小白,里面有部分代码忘了出处哪几个大神,还望见谅。
使用注意点:
1.excel表中的字段要和数据库的字段对应顺序要一样,名字(列)可以不一样。
2.model类型比如是User,那么表名就是t_user。 不想这样用自己修改下反射的代码
3.字段名 比如 title_type 在model中是titleType。 不想这样用自己修改下反射的代码
4.中乱码的处理,假如有个user.xlsx, 新建一个文本,把user.xlsx的内容考进去,保存类型选为utf-8,再把文本的内容复制到user.xlsx
jar包:
使用反射不容易看懂,但是好处就是需要批量导入多个excel时,只需要在doMaim.java中修改一下路径和model类型就行了。
ExcelToModel.java
package com.nuc.test.exceltomodeltosql;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.ss.usermodel.Cell;
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.ss.usermodel.WorkbookFactory;
/***
*
* 把任意的Excel表的数据对应到java的model中
* 注意:需要excel表与属性字段一一对应
*
* @author yx
* @param <T>
*/
public class ExcelToModel<T> {
/**
* 外部调用这个方法开始执行,直接返回一个List<model>;
* @param is
* @param c
* @return
* @throws IOException
* @throws NoSuchMethodException
* @throws SecurityException
* @throws IllegalAccessException
* @throws IllegalArgumentException
* @throws InvocationTargetException
* @throws ParseException
*/
public List<T> getListObject(InputStream is,Class c) throws IOException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, ParseException{
// InputStream is = new FileInputStream("e:\\user.xlsx");
List<T> list = (List<T>)getList(is,c);
return list;
}
/**
* 获取模型类的集合
* @param is 输入流
* @return
* @throws IOException
* @throws SecurityException
* @throws NoSuchMethodException
* @throws InvocationTargetException
* @throws IllegalArgumentException
* @throws IllegalAccessException
* @throws ParseException
*/
public List<T> getList(InputStream is,Class c)
throws IOException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, ParseException {
List<List<String>> list = readExcel(is); //读取excel ,readExcel方法的实现在底下
List<T> listBean = new ArrayList<T>(); //用一个集合存model
System.out.println(list.get(0));//这个应该是表头吧
for (int i = 1; i < list.size(); i++) { // i=1 表头不要跳过 遍历第一行,每一行都是一个model实例
System.out.println("第"+i+"行"+list.get(i));
Object object = null;
try {
object = c.newInstance(); //获取类的实例
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Field[] fields = c.getDeclaredFields(); //获取所有的属性
List<String> listBeanFieldValue = list.get(i); //这一列的值。对应model的属性值
for (int j = 0; j < listBeanFieldValue.size(); j++) { //遍历第一列,每一列都是一个属性值
System.out.println("第"+j+"列"+listBeanFieldValue.get(j));
for (int a = j ; a < fields.length ; a++) { //遍历所有属性
Field field = fields[a]; //a从0开始,filed就是fields[0]就是id,这句说明了属性与excel必须对应否则出错
String fieldName = field.getName(); //获取属性名
Class fieldTypeClass = field.getType(); //获取属性的数据类型
String setMethodName = "set"+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1); //获取set方法名称
Method setMethod = c.getDeclaredMethod(setMethodName, new Class[]{fieldTypeClass}); //获取set方法
String value = listBeanFieldValue.get(j); //获取excel中单元格的值
Object rightValue = transformFormValueToRightDataType(fieldTypeClass , value); //转换类型
setMethod.invoke(object, new Object[]{rightValue}); //反向调用
break; //跳出本次的循环,意思就是id set进去了,该set name了,加入User(id,name,password)
// if(fieldTypeClass == int.class){
// setMethod.invoke(object, new Integer(listBeanFieldValue.get(j)));
// break;
// }
// if(fieldTypeClass == String.class){
// setMethod.invoke(object, new String[]{listBeanFieldValue.get(j)});
// break;
// }
// if(fieldTypeClass == Date.class){
// SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
// sdf.parse(listBeanFieldValue.get(j));
// setMethod.invoke(object, new String[]{listBeanFieldValue.get(j)});
// break;
}
}
listBean.add((T) object); //第一个model设置完毕加入集合
}
return listBean;
}
/**
* 读取Excel表格
* @param is
* @return
* @throws IOException
*/
public static List<List<String>> readExcel(InputStream is)
throws IOException {
Workbook wb = null;
try {
wb = WorkbookFactory.create(is);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
Sheet sheet = wb.getSheetAt(0); //获取第一个sheet
int totalRows = sheet.getPhysicalNumberOfRows(); //获取所有行
//获取所有列
int totalCells = 0;
if (totalRows >= 1 && sheet.getRow(0) != null) {
totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
List<List<String>> dataLst = new ArrayList<List<String>>(); //存列的集合
//循环行
for (int r = 0; r < totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null)
continue;
List<String> rowLst = new ArrayList<String>(); //每一个列
// 循环列
for (int c = 0; c < totalCells; c++) {
Cell cell = row.getCell(c);
String cellValue = "";
if (null != cell) {
HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();
cellValue= hSSFDataFormatter.formatCellValue(cell);
}
rowLst.add(cellValue); //每一行的所有列
}
dataLst.add(rowLst); //所有的行行里包括了列
}
return dataLst;
}
/***
* 转化值为正确的类型
* @param fieldType 属性类型
* @param value 属性值
* @return
*/
public Object transformFormValueToRightDataType(Class fieldType,String value){
if(fieldType == int.class){
return Integer.valueOf(value);
}
if(fieldType == double.class){
return Double.parseDouble(value);
}
if(fieldType == Date.class)
{
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
try {
return sdf.parse(value);
} catch (ParseException e) {
e.printStackTrace();
return null;
}
}
if(fieldType == Timestamp.class)
{
return Timestamp.valueOf(value);
}
return value;
}
}
TestUtilExcelToModel.java
package com.nuc.test.exceltomodeltosql;
import com.nuc.utils.ExcelToModel;
public class TestUtilExcelToModel<T> extends ExcelToModel<T>{
}
doMain.java
以上两个方法就可以实现不同model类对应的excel存入到一个model的集合中,之后的就是批量添加了
package com.nuc.test.exceltomodeltosql;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.text.ParseException;
import java.util.List;
import com.nuc.test.model.Title;
import com.nuc.test.model.User;
public class DoMain {
public static void main(String[] args) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, IOException, ParseException {
TestUtilExcelToModel c = new TestUtilExcelToModel();
InputStream is = null;
try {
//is = new FileInputStream("e:\\title.xlsx"); //导入你的文件路径
is = new FileInputStream("e:\\user.xlsx");
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
/** 开始生成 model的集合 ----------------------------------- */
//List<Title> titles = c.getList(is,Title.class); //传入流,和 model的类型
List<User> titles = c.getList(is,User.class); //传入流,和 model的类型
is.close();
/** model的集合生成完毕 ----------------------------------- */
/** 开始 批量插入 list集合----------------------------------- */
//BatchAdd<Title> batchAdd = new BatchAdd<Title>();
//Title t = new Title();
BatchAdd<User> batchAdd = new BatchAdd<User>();
User t = new User();
batchAdd.batchAdd(titles, t);
/** 批量插入 list集合完毕----------------------------------- */
System.out.println("完成!!!");
}
}
接下来是批量添加到数据库表中,和上面一样,基本是反射实现的
BaseDao.java
package com.nuc.test.exceltomodeltosql;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import com.nuc.utils.DButil;
public class BaseDao<T> {
private static String url = "jdbc:mysql://localhost:3306/db_test?characterEncoding=utf8&rewriteBatchedStatements=true";
private static String user = "root";
private static String password = "123456";
public void batchAdd(List<T> lists,T t){
Connection conn = null;
PreparedStatement pst = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);
Class c = t.getClass(); //获得类
String tableName = getTableNameFromClassName(c.getSimpleName()); //获得表名
Field[] fields = c.getDeclaredFields();// 获得类中所 有属性名
StringBuilder columnNames = new StringBuilder(); //列名 表名后面的值
StringBuilder columnValues = new StringBuilder(); //列值 values()里的值
for (Field field : fields) {
String fieldName = field.getName(); //得到属性名
Class fieldType = field.getType();
String columnName = getCloumnNameFromFieldName(fieldName); //返回列明
if("id".equals(fieldName)){ //如果是id就跳过--自增
columnName = "id";
}
columnNames.append(","+columnName); //,name,password最后是这样的形式
columnValues.append(",?"); //拼接sql
}
//sql拼接完成
String sql = "insert into "+tableName+" ("+columnNames.substring(1)+") values ("+columnValues.substring(1)+")";
System.out.println(sql);
pst = conn.prepareStatement(sql);
Long startTime = System.currentTimeMillis();
for (int i = 0 ; i < lists.size() ; i++) { //遍历所有的model
Field[] fieldss = c.getDeclaredFields(); //拿到本model的属性集
int index = 1; // ?的索引。
int fieldIndex = 0; //field的索引
for (int j = 0 ; j< fieldss.length ; j++) {
T tBean = lists.get(i);
String fieldName = fieldss[fieldIndex].getName(); //得到属性名
Class fieldType = fieldss[fieldIndex].getType();
String getMethodName = "get"+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1);
Method getMethod = c.getDeclaredMethod(getMethodName, new Class[]{});
if(fieldType == int.class){
int a = (Integer) getMethod.invoke(tBean, new Integer[]{});
pst.setInt(index,a);
}
if(fieldType == String.class){
String b = (String) getMethod.invoke(tBean,new String[]{});
pst.setString(index, b);
}
index++;
fieldIndex++;
}
pst.addBatch();
}
pst.executeBatch();
Long endTime = System.currentTimeMillis();
System.out.println("OK,用时:" + (endTime - startTime)+"ms");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SecurityException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InvocationTargetException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (NoSuchMethodException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DButil.freeSource(conn, pst);
}
}
/**
*
* @return 返回表名
*/
//根据类名,获取表名
private static String getTableNameFromClassName(String className){
// String className = clazz.getSimpleName(); //类名
StringBuilder sb = new StringBuilder();
sb.append(" t"); //t_user
char[] chars = className.toCharArray(); //把类名转化为字符数组
//遍历
for (char c : chars) {
int num = (int)c;
//大写转小写拼接成表名
if(num >= 65 && num<=90){
sb.append("_"+(c+"").toLowerCase()); //User --- t_user
}else{
sb.append(c);
}
}
return sb.toString(); //返回表名
}
/**
* 根据属性名拿到列明
* @param fieldName 属性名
* @return 返回的是列明
*/
private static String getCloumnNameFromFieldName(String fieldName){
//比如user_id userId
StringBuilder sb = new StringBuilder();
char[] chars = fieldName.toCharArray(); //属性名转化为字节数组
//遍历
for (char c : chars) {
int num = (int)c;
if(num >= 65 && num<= 90){
sb.append("_"+(c+"").toLowerCase());//userId---user_id
}else{
sb.append(c);
}
}
return sb.toString();
}
}
BatchAdd.java
package com.nuc.test.exceltomodeltosql;
public class BatchAdd<T> extends BaseDao<T>{
}