简陋的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<len; 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<String>的，
	 * 其中的List<String>是按照列存储的，这一列
	 * 的类型存储在这个List<String>的最末尾处。
	 * @return 
	 * @throws BiffException
	 * @throws IOException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws InterruptedException
	 */
	public static List<List<String>> getExcelValue() throws BiffException, IOException, InstantiationException, IllegalAccessException, InterruptedException {
		ReadFromExcelToBeanDemo readBeanDemo = new ReadFromExcelToBeanDemo();
		
		List<String> columnTypeList = new ArrayList<String>();
		
		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<columns; 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<List<String>> columnValuelist = new ArrayList<List<String>>();
		List<String> list = null;
		/*
		for (int row=0; row<rows; row++) {
			list = new ArrayList<String>();
			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<String>();
			for (int row=1; row<rows; 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<len; 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<String> types = null;
	
	private CreateBeanUtil() {}
	/**
	 * 
	 * @param list 类型的list集合，泛型约束为String
	 * @param hasDate 是否使用了时间日期类型
	 * @return
	 * @throws IOException
	 */
	public static boolean createBean(List<String> 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<String> list = new ArrayList<String>();
		list.add("String");
		list.add("double");
		list.add("float");
		list.add("boolean");
		createBean(list, false);
		
	}
}

 

现在因为无法解决，只能全部转成String来存入，List<List<String>>有点low，但是我也很无奈！

 

上面的代码，从excel中读出了数据，下面的代码，是将得到的List<List<String>>存储到数据库中：

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<List<String>> 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秒的样子！