使用java技术将Excel表格内容导入mysql数据库
1、添加POI jar包到项目的lib目录下-
2、Excel文件目录:d://excel.xls-
3、数据库字段为:num1 num2 num3 num4 num5 num6-
4、数据库名：blog-
5、表名:test-
6、编写类：连接mysql的字符串方法、插入的方法、实体类--
1 import java.io.FileInputStream;-
2 import java.io.FileNotFoundException;-
3 import java.io.IOException;-
4 import org.apache.commons.logging.Log;-
5 import org.apache.commons.logging.LogFactory;-
6 import org.apache.poi.hssf.usermodel.HSSFCell;-
7 import org.apache.poi.hssf.usermodel.HSSFRow;-
8 import org.apache.poi.hssf.usermodel.HSSFSheet;-
9 import org.apache.poi.hssf.usermodel.HSSFWorkbook;-
10
11 public class TestExcel {-
12 //记录类的输出信息-
13 static Log log = LogFactory.getLog(TestExcel.class); -
14 //获取Excel文档的路径-
15 public static String filePath = "D://excel.xls";-
16 public static void main(String[] args) {-
17 try {-
18 // 创建对Excel工作簿文件的引用-
19 HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(filePath));-
21 // 在Excel文档中，第一张工作表的缺省索引是0
22 // 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);-
23 HSSFSheet sheet = wookbook.getSheet("Sheet1");-
25 //获取到Excel文件中的所有行数-
26 int rows = sheet.getPhysicalNumberOfRows();
28 //遍历行-
29 for (int i = 0; i < rows; i++) {-
30 // 读取左上端单元格-
31 HSSFRow row = sheet.getRow(i);-
32 // 行不为空-
33 if (row != null) {-
34 //获取到Excel文件中的所有的列-
35 int cells = row.getPhysicalNumberOfCells();-
36 String value = ""; -
37 //遍历列-
38 for (int j = 0; j < cells; j++) {-
39 //获取到列的值-
40 HSSFCell cell = row.getCell(j);-
41 if (cell != null) {-
42 switch (cell.getCellType()) {-
43 case HSSFCell.CELL_TYPE_FORMULA:-
44 break;-
45 case HSSFCell.CELL_TYPE_NUMERIC:-
46 value += cell.getNumericCellValue() + ","; -
47 break; -
48 case HSSFCell.CELL_TYPE_STRING:-
49 value += cell.getStringCellValue() + ",";-
50 break;-
51 default:-
52 value += "0";-
53 break;-
54 }-
55 }
56 }-
57 // 将数据插入到mysql数据库中-
58 String[] val = value.split(",");-
59 TestEntity entity = new TestEntity();-
60 entity.setNum1(val[0]);-
61 entity.setNum2(val[1]);-
62 entity.setNum3(val[2]);-
63 entity.setNum4(val[3]);-
64 entity.setNum5(val[4]);-
65 entity.setNum6(val[5]);-
66 TestMethod method = new TestMethod();-
67 method.Add(entity);-
68 }-
69 }-
70 } catch (FileNotFoundException e) {-
71 e.printStackTrace();-
72 } catch (IOException e) {-
73 e.printStackTrace();-
74 }-
75 }-
76 }-
