实现从excel读取数据,使用的是jxl.jar(到处都有,请大家随意下载),其中封装好了通过excel提供的接口,对excel中的数据库进行读取的实现;
先为了熟悉其中的方法使用,做了以下的测试:
package test;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
/**
* 注意:
* jxl工具只支持标准的xls表格文件;
* 不支持工作簿类型的xlsx文件!
*
* @author mzy
*
*/
public class Demo03 {
public static void main(String[] args) throws BiffException, IOException {
List list = new ArrayList<>();
// C:\Users\Administrator\Desktop\TestExcel
Workbook workbook = Workbook.getWorkbook(new File("C:/Users/Administrator/Desktop/TestExcel/2016级名册(全)3.7.xls"));
Sheet sheet = workbook.getSheet(0); // 下标从0开始,也可以通过名字去获取
String name = sheet.getName();
int rows = sheet.getRows();
int columns = sheet.getColumns();
System.out.println("rows = "+rows);
System.out.println("cloumns = "+columns);
Cell cell01 = sheet.getCell(0, 1); // 第0+1列,第1+1行
Cell cell02 = sheet.getCell(1, 1); // 第2列,第2行
Cell cell03 = sheet.getCell(2, 1); // ... ...
Cell cell04 = sheet.getCell(3, 1);
Cell cell05 = sheet.getCell(4, 1);
// Date Label Number
System.out.print(cell01.getType()+" ");
System.out.print(cell02.getType()+" ");
System.out.print(cell03.getType()+" ");
System.out.print(cell04.getType()+" ");
System.out.println(cell05.getType()+" ");
Cell cell06 = sheet.getCell(6, 1);
System.out.println(cell06.getType()+" ");
double num = Double.parseDouble(getNumber(cell06.getContents()));
System.out.println(num+" ");
System.out.print(cell01.getContents()+" ");
System.out.print(cell02.getContents()+" ");
System.out.print(cell03.getContents()+" ");
System.out.print(cell04.getContents()+" ");
System.out.println(cell05.getContents()+" ");
}
public static String getNumber(String str) {
int len = str.length();
char word;
StringBuffer sb = new StringBuffer();
for (int i=0; i
word = str.charAt(i);
if (word > 47 && word < 58 || word == 46) { // 48->0,57->9 .->46
sb.append(word);
}
}
return sb.toString();
}
}
关于上面的getNumber方法,为什么要使用getNumber呢?因为其中的Number类型中,包括了所有的数字类型,其中最典型的是货币类型,如果是货币的话,是有货币的标识符的,$或者¥,所以我们要对其中的非法字符进行剔除,变成一个纯净的int或者double类型(但是其实我的想法是不处理浮点类型的,因为我不喜欢在数据库中存储浮点类型:建议大家不要存储浮点类型,全部作为整型去处理,最多把精度右移)
还有,因为jxl.jar包的原因,只能处理标准的文档格式:只能处理xls文件类型,不能处理xlsx文件,所以如果是xlsx这种比较新的格式的话,就需要先把这个表格导出成为xls格式!
其中涵盖的excel数据类型较少,label、number、date 其它的例如计算类的类型,我都没有处理,比较简陋:
package test;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
/**
* 初步写的一个读取的从Excel中读出数据到程序中
* 开始想的是使用动态创建JavaBean的方式,但是
* 动态创建JavaBean之后,加载顺序问题会导致出错!
*
* 所以想,先全部以String的方式读出来!
* @author mzy
*
*/
public class ReadFromExcelToBeanDemo {
private static String sheetName;
public static String getSheetName() {
return sheetName;
}
private ReadFromExcelToBeanDemo() {}
/**
* 关于返回的List中泛型约束是List的,
* 其中的List是按照列存储的,这一列
* 的类型存储在这个List的最末尾处。
* @return
* @throws BiffException
* @throws IOException
* @throws InstantiationException
* @throws IllegalAccessException
* @throws InterruptedException
*/
public static List> getExcelValue() throws BiffException, IOException, InstantiationException, IllegalAccessException, InterruptedException {
ReadFromExcelToBeanDemo readBeanDemo = new ReadFromExcelToBeanDemo();
List columnTypeList = new ArrayList();
Workbook workbook = Workbook.getWorkbook(new File("C:/Users/Administrator/Desktop/TestExcel/2016级名册(全)3.7.xls"));
// Workbook workbook = Workbook.getWorkbook(new File("C:/Users/Administrator/Desktop/TestExcel/入馆.xls"));
// Workbook workbook = Workbook.getWorkbook(new File("C:/Users/Administrator/Desktop/TestExcel/test.xls"));
Sheet sheet = workbook.getSheet(0); // 下标从0开始,也可以通过名字去获取
String name = sheet.getName();
// System.out.println(name);
sheetName = name;
int columns = sheet.getColumns();
int rows = sheet.getRows();
// System.out.println(rows);
if (rows <= 1) {
return null;
}
Cell cell = null;
// 将每一列的数据类型确定
for (int i=0; i
cell = sheet.getCell(i, 1);
String type = cell.getType().toString();
String value = cell.getContents();
if ("Label".equals(type)) {
type = "String";
columnTypeList.add(type);
continue;
}
if ("Number".equals(type)) {
if (value.contains(".")) {
type = "double";
} else {
type = "int";
}
columnTypeList.add(type);
continue;
}
if ("Date".equals(type)) {
type = "Date";
columnTypeList.add(type);
continue;
}
columnTypeList.add(type);
}
// System.out.println(Arrays.toString(columnTypeList.toArray()));
List> columnValuelist = new ArrayList>();
List list = null;
/*
for (int row=0; row
list = new ArrayList();
for (int col=0; col < columns; col++) {
cell = sheet.getCell(col, row);
list.add(cell.getContents()); // 列,行
}
columnValuelist.add(list);
}
*/
boolean isNumber;
String contents;
for (int col=0; col < columns; col++) {
isNumber = false;
if ("int".equals(columnTypeList.get(col)) || "double".equals(columnTypeList.get(col))) {
isNumber = true;
}
list = new ArrayList();
for (int row=1; row
cell = sheet.getCell(col, row);
contents = cell.getContents();
if (isNumber) {
contents = readBeanDemo.getNumber(contents);
}
list.add(contents); // 列,行
}
// list.add(columnTypeList.get(col));
columnValuelist.add(list);
}
/*
* 需求:
* 每次从columnValueList中取出其中的 每一个list的第一个元素
* 所以外层的len在外面,内层的循环中控制,将columnValueList
* 中的list的值一步一步的往下推进!
*/
/*
int valueLen = rows; // 元素的具体的行数
int typeLen = columnTypeList.size(); // 元素的具体列数
for (int row=0; row < valueLen-1; row++) {
for (int col=0; col < typeLen; col++) {
columnValuelist.get(col).get(row);
System.out.print(columnValuelist.get(col).get(row)+"\t");
}
System.out.println();
}
*/
return columnValuelist;
}
/**
* 如果是Number类型:
* 其中有$或者¥符号进行截取
* @param str 需要进行截取的字符串
* @return 返回截取之后的字符串
*/
public String getNumber(String str) {
int len = str.length();
char word;
StringBuffer sb = new StringBuffer();
for (int i=0; i
word = str.charAt(i);
if (word > 47 && word < 58 || word == 46) { // 48->0,57->9 .->46
sb.append(word);
}
}
return sb.toString();
}
}
其中大家可以看到我在注释的部分有一个javaBean封装,最开始我是想自动生成javaBean对象的来做的,通过反射按下标赋值类解决的但是发现类加载顺序的原因,泛型会自动约束为在我本次生成的javaBean之前的javaBean的类型,打算重学了类加载器之后,再思考解决。
自动生成JavaBean代码(因为我对属性的名字没有要求,是通过顺序来进行赋值的,所以名字全是Field,但是如果要外部传入名字也是可以的,这里就顺便贴出生成JavaBean文件的代码):
注意因为项目结构是生成JavaBean的工具在util下面,生成的JavaBean叫做TempBean放在entity下面
package util;
/**
* 思考,一般常用的类型:
*
* String
* int
* double
* date
*
* @author mzy
*
*/
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;
public class CreateBeanUtil {
public static final boolean HASDATE = true;
private static List types = null;
private CreateBeanUtil() {}
/**
*
* @param list 类型的list集合,泛型约束为String
* @param hasDate 是否使用了时间日期类型
* @return
* @throws IOException
*/
public static boolean createBean(List list, boolean hasDate) throws IOException {
types = list;
CreateBeanUtil bean = new CreateBeanUtil();
StringBuffer sb = new StringBuffer();
sb.append("package entity;\r\n\r\n");
if (hasDate)
sb.append("import java.util.Date;\r\n");
sb.append("import java.io.Serializable;\r\n\r\n");
sb.append("public class TempBean implements Serializable {\r\n");
bean.prepareAllAttrs(sb);
bean.prepareAllMethod(sb);
sb.append("}");
URL url = CreateBeanUtil.class.getResource("../entity");
String path = url.getPath();
path += "/TempBean.java";
StringBuffer buf = new StringBuffer(path);
buf.reverse();
path = buf.toString();
path = path.replaceFirst("nib", "crs");
buf = new StringBuffer(path);
buf.reverse();
path = buf.toString();
System.out.println(url.getPath());
System.out.println(path);
/*
* 为什么多了一根斜杠?
* /D:/my_code/JavaEE_eclipse_utf8/Pay_Instance/build/classes/bankUser.xml
*/
FileWriter fw = new FileWriter(path);
PrintWriter pw = new PrintWriter(fw);
pw.println(sb.toString());
pw.flush();
pw.close();
return false;
}
/**
* 解析输出属性
*
* @return
*/
private void prepareAllAttrs(StringBuffer sb) {
sb.append("\tprivate static final long serialVersionUID = 1L;\r\n");
for (int i = 0; i < types.size(); i++) {
sb.append("\tprivate " + types.get(i) + " "
+ "field" + i + ";\r\n");
}
sb.append("\r\n");
}
/**
* 生成所有的方法
*
* @param sb
*/
private void prepareAllMethod(StringBuffer sb) {
for (int i = 0; i < types.size(); i++) {
sb.append("\tpublic void setField" + i + "("
+ types.get(i) + " " + "field" + i
+ ") {\r\n");
sb.append("\t\tthis." + "field" + i + " = " + "field" + i + ";\r\n");
sb.append("\t}\r\n");
sb.append("\tpublic "+types.get(i)+" getField" + i + "("
+ "){\r\n");
sb.append("\t\treturn " + "field" + i + ";\r\n");
sb.append("\t}\r\n");
}
}
public static void main(String[] args) throws IOException {
List list = new ArrayList();
list.add("String");
list.add("double");
list.add("float");
list.add("boolean");
createBean(list, false);
}
}
现在因为无法解决,只能全部转成String来存入,List>有点low,但是我也很无奈!
上面的代码,从excel中读出了数据,下面的代码,是将得到的List>存储到数据库中:
package test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.List;
public class InsertIntoDataBase {
public static void main(String[] args) throws Exception {
long begin = System.currentTimeMillis();
long tmp;
System.err.print("程序开始执行:开始从excel中读出数据...");
List> excelValue = ReadFromExcelToBeanDemo.getExcelValue();
int cols = excelValue.size();
int rows = excelValue.get(0).size();
System.err.print("\t");
tmp = System.currentTimeMillis();
sysUseTime(begin, tmp);
// System.out.println(cols + ", " + rows);
System.err.print("开始动态构建SQL语句");
String sql = "insert into "+ ReadFromExcelToBeanDemo.getSheetName() + " values ";
long createBegin = System.currentTimeMillis();
boolean bingoTime = false; // 3秒为一个.
for (int row = 0; row < rows; row++) {
bingoTime = false;
sql += "(";
for (int col = 0; col < cols; col++) {
sql += "?, ";
}
sql = sql.substring(0, sql.length()-2);
sql += "), ";
tmp = System.currentTimeMillis();
if ((tmp - createBegin) / 1000 >= 2) {
bingoTime = true;
createBegin = tmp;
}
if (bingoTime) {
System.err.print(".");
}
}
sql = sql.substring(0, sql.length()-2);
tmp = System.currentTimeMillis();
System.err.print("\t");
sysUseTime(begin, tmp);
Class.forName("com.mysql.jdbc.Driver");
System.err.print("开始准备连接,以及预加载SQL");
// Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ContactSys", "root", "123456");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
PreparedStatement pstmt = conn.prepareStatement(sql);
System.err.println("\tfinish");
System.err.print("开始为动态SQL语句赋值");
// System.out.println(sql);
int i = 1; // 计数器
long insertBegin = System.currentTimeMillis();
bingoTime = false; // 3秒为一个.
for (int row = 0; row < rows; row++) {
bingoTime = false;
for (int col = 0; col < cols; col++) {
// System.out.print(excelValue.get(col).get(row)+"\t");
pstmt.setObject(i, excelValue.get(col).get(row));
i++;
}
// System.out.println();
tmp = System.currentTimeMillis();
if ((tmp - insertBegin) / 1000 > 3) {
bingoTime = true;
insertBegin = tmp;
}
if (bingoTime) {
System.err.print(".");
}
}
System.err.println("\tfinish");
System.err.println("共计赋值:" + i + "个字段");
int count = pstmt.executeUpdate();
System.err.println("共插入" + count + "行值!");
long end = System.currentTimeMillis();
sysUseTime(begin, end);
}
public static void sysUseTime(long begin, long end) {
long time = end - begin;
if (time < 1000) {
System.err.println("耗时:" + time + "毫秒");
} else {
time = (end-begin) / 1000;
System.err.println("耗时:" + time + "秒");
}
}
}
因为insert into xxx value(), value(), ..., value();比较慢
而使用insert into xxx value(), (), () ,(), ... , ();会快很多(MYSQL官方也推荐这种方式),所以以上的sql语句也是使用的后者进行构造的。
因为这里是纯insert语句,并没有使用存储过程,所以速度比较慢,20000条数据,要18秒的样子!