excel配合java和mysql_简陋的Excel到MYSQL的数据传输JAVA实现

实现从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秒的样子！