简陋的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 + "秒"); } } }